Connecting Power BI to SAP Datasphere

Both SAP and Microsoft have Data and Analytics Platforms in their portfolios, and it is not uncommon for organisations to have a mix of technologies.

In this blog we will look at the different ways that Microsoft Power BI can be connected to, and consume data from SAP Datasphere.

Datasphere is SAP’s Data Warehouse in the Cloud solution.  Power BI is Microsoft’s toolset for connecting to data and producing dashboards, reports and insights.

OData Connection

The first connection method we will look at is OData, or Open Data Protocol, and this is an open data access protocol, which allows us to utilise REST APIs (application programme interfaces).  In the case of Datasphere, these APIs enable us to connect and consume data from Views and Analytic Models.

Setup

Olaf Fischer, from SAP, has created a custom OData connection package which resides on github at the following link:

https://github.com/SAP-samples/data-warehouse-cloud-connectors

This is the package I have used for this section of the blog but please note under the README where it states that it is not covered by any SLA, and is used “at your own risk”.  All the instructions are included within the package, but the basic steps are as follows:

1. On Datasphere, create an oauth client under \System\Administration\App Settings. Note, you will need the Client ID and Secret later in the configuration.

 

2. Download the custom connector folder from github. You can either replicate the github repository, or simply download the folder.  I’ve done the latter, by navigating to …

https://download-directory.github.io/

… and entering the name of the folder to download …

https://github.com/SAP-samples/data-warehouse-cloud-connectors/tree/main/power-bi

This creates a Zip file in your Downloads folder, which you can then Extract into a file folder.

3. In that file folder you will need to configure the connections.json file. There are settings for both DWC and SAC, but for the purpose of this blog we will just configure the DWC settings.  The file should contain the following for DWC only:

[

  {

    “product”: “DWC”,

    “host”: “<host name of the tenant – without leading https://>”,

 

    “client_id”: “<client id>”,

   “client_secret”: “<client secret>”,

 

    “auth_token_url”: “<token URL></oauth/token>”,

    “auth_authorize_url”: “<authorize URL></oauth/authorize>”

  }

]

 Update the file for your configuration:

• Host (tenant host name without https://)
• Client ID (from the earlier oauth client setup)
• Client Secret (from the earlier oauth client setup)
• Auth Token URL (available in DWC under Settings\Administration\App Integration)
• Auth Authorize URL (available in DWC under Settings\Administration\App Integration)

4. Build and deploy the connector. Zip the files into a file called SAP_DWC_01.zip, and then rename this to SAP_DWC_01.mez (take a copy if you like).  Place this .mez file into the following folder (or create the folder if it is not there already):

\Documents\Power BI Desktop\Custom Connectors

5. When you start Power BI Desktop it may or may not recognise that you have a custom connector file. If it does not, you may need to adjust the settings for Data Extensions under File\Options and Settings\Options\Global\Security.

With all of the above steps completed, the connector is ready to use.  The connector is actually 3 connectors in 1, providing options for:

• OData URL
• Relational Objects (Catalog and Data access, incudes relational views and analytical datasets)
• Analytic Objects (Catalog and Data access, includes analytical datasets and analytic models)

In Power BI, select Get Data and search for SAP connectors.  The OData connectors should show up as below:

For the purpose of this blog, we will focus on the Data connector with the below dialog box:

Populate the details as requested.  The Data Access Type options are relational or analytic (these are separate options as the API calls are different between the two).

At this point, you will be asked to enter your Datasphere credentials.  Once authenticated, the Navigator will display and you can select your source and Load/Transform as required.

Behaviour and Supported Features

OData extract from Relational Dataset (View)

In Datasphere we have a base Sales view, with an Association to a Region Lookup on the State column.  This Lookup contains a level-based hierarchy, Region-State.

When loaded into Power BI, the data looks like the below:

Key things to note:

• Associations are not supported – the data from Region Lookup is not included.
• Hierarchies are not supported.
• Aggregation is carried out within the Datasphere View, so data remains at the aggregated level.

OData extract from Analytic Model

In Datasphere we have a base Sales Fact Source, with an Association to a Region Lookup on the State column.  This Lookup contains a level-based hierarchy, Region-State.

When loaded into Power BI, the data looks like the below:

• Associations are supported – the columns from Region Lookup are included.
• Hierarchies are not supported.
• Aggregation from the Analytic Model is supported.

ODBC Connection

ODBC, Open Database Connectivity, provides an API for accessing databases, typically using an ODBC driver provided by the database vendor.  For SAP Datasphere, this is the SAP HANA ODBC driver.

Setup

Datasphere

For the ODBC connection to SAP Datasphere, there are a couple of things which need to be configured within Datasphere first.

1. Create a database user (for ODBC authentication)

a) Within Datasphere, go to \Space Management and select the Space you want to connect to. Then select the Database Access tab, Database Users and Create.

b) Enter a suffix name for the database user (user will be saved as [Space Name]#[Suffix Name], and select Enable Read Access. That is all that should be required for basic read access to the data.

c) Within the User Details, make a note of the Host, Port and Password, as these will be required to configure the ODBC connection.

2. Add the IP of the ODBC client to the Datasphere IP allowlist

a) Within Datasphere, go to \System\Configuration. Then select the IP Allowlist tab and Trusted IPs, followed by Add.

b) Enter the IP Address in the CIDR box, and an appropriate Description.

ODBC Driver

With Datasphere configured, we turn our attentions to the client ODBC configuration.

1.  Download and install the SAP HANA Client, selecting the appropriate component for the client OS (e.g. Windows). This can be found at:

https://tools.eu1.hana.ondemand.com/#hanatools

2.  Check the install by opening the ODBC Data Source Administrator and selecting Drivers. The HDBODBC driver should now be visible.

 

3. Add a User or System DSN. Use the Host and Port Number noted down when creating the Datasphere Database User in an earlier step.

4.  Test the connection. Enter the User ID as [Space Name]#[Suffix Name] and the Password generated when creating the Datasphere Database User in an earlier step.

5. In Power BI, select Get Data and search for the ODBC connector. Select the relevant DSN, and enter the Datasphere credentials when prompted.  As when performing the ODBC test connection, enter User ID as [Space Name]#[Suffix Name] and the Password generated when creating the Datasphere Database User in an earlier step.

 

Behaviour and Supported Features

ODBC extract from Relational Dataset (View)

Using the same data source in Datasphere that we used for OData – we have a base Sales view, with an Association to a Region Lookup on the State column.  This Lookup contains a level-based hierarchy, Region-State.

When loaded into Power BI, the data looks like the below:

Key things to note:

• Associations are not supported, additional columns from Region Lookup are not brought through.
• Hierarchies are not supported.
• Aggregation is carried out within the Datasphere View, so data remains at the aggregated level.

ODBC extract from Analytic Model

Using the same data source in Datasphere that we used for OData – in the Datasphere Analytic Model we have a base Sales Fact Source, with an Association to a Region Lookup on the State column.  This Lookup contains a level-based hierarchy, Region-State.

When loaded into Power BI, the data looks like the below:

Key things to note:

• Associations are not supported, additional columns from Region Lookup are not brought through.
• Hierarchies are not supported.
• Analytic Model Aggregation is not supported (data has been “de-aggregated”).

Conclusions

Although both OData and ODBC are viable connection options for SAP Datasphere, there are a few key considerations:

1.  Are there any technical and/or access restrictions in your scenario which would rule out one or the other?

2.  Association support – If you need this, then only OData supports it.

3.  With Analytic Model now being the “go-to” for Datasphere consumption, OData supports Aggregation, ODBC does not (de-aggregates).

If you would like to discuss anything covered further please reach out here