Datasphere: Consume a Generic OData Source to Create a Fact (Analytical Dataset)
In today’s data-driven business landscape, the ability to seamlessly integrate and analyze vast amounts of data has become paramount for organizations seeking to unlock valuable insights. SAP Datasphere (DSP) emerges as a powerful solution, providing a unified and scalable platform for data warehousing and analytics. To fully leverage the potential of DSP, establishing an efficient connections to external data sources is crucial. One such method is utilizing the OData protocol, a widely adopted standard for data exchange and integration.
This technical article aims to demystify the process of creating and consuming OData connections within SAP Datasphere. By following our step-by-step guide, you will gain the knowledge and confidence to create and deploy a consumable a Fact (aka Analytical Dataset) in DSP based on a generic OData connection.
1. Select the Datasource
From you desired datasource, you need the following details:
- Host URL of the remote OData Service
- OData version: V2, V3 or V4
- Authentication type and details
For our test-case scenario, we’ll use the well known Northwind OData test service.
2. Create the Connection
In DSP, go to Connections -> Create -> search for “odata”
Then enter the details as the following
Hint: note that the default Northwind service URL is of version 3. We needed to change this manually in the URL to V2, since V3 is not supported in Datashpere. Otherwise, the entities would be recognized inside the connection, but no data could be fetched:
3. Consume the Connection in Data Builder
If we try to create a Graphical View using the created OData connection, no entities can be read from the connection. To get more details, we validate the connection in the Connections tile, and we get the following warning:
So the reason is that the Graphical View tried to access the data remotely, which faces an SSL issue because the requested service will be accessed through HTTPS (https://services.OData.com), as described in this help page.
Solution-1: Security Certificate
Download the certificate of the OData service and upload it in DSP as given in the previous given help page. However, you will need for this the DW Administrator role. So if don’t have it, move to the 2nd solution.
Solution-2: Create a Data Flow First
The Data Flow has an ETL behaviour in replicating data. So the connection’s entities and their data can be accessed and consumed successfully.
After applying the operators that you need (join, project, ..) as part of the Data Flow, you need to add a target table, Create & Deploy it, and finally Run the flow.
4. Now, you can create your Fact (Analytical Dataset)
The created and deployed table NW_Orders (visible on the previous Figure) can now be found while creating a Graphical View, and it be can consumed with its data to create a Fact.