Datasphere: Consume a Generic OData Source to Create a Fact (Analytical Dataset)

0. Introduction

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:

1. Host URL of the remote OData Service
2. OData version: V2, V3 or V4
3. 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.