Datasphere and Power BI, best of both worlds! Part 2

In the first part of this blog series, we explored the different connection methods—ODBC and OData—used to integrate SAP Datasphere with Power BI. We discussed their advantages and key differences, providing a solid understanding of which method is best suited for your organization’s needs. 

Now, in the second part, we’ll dive into the step-by-step process for connecting your SAP Datasphere instance to Power BI using both connection types. Whether you choose ODBC for its high-performance access or OData for its flexibility and cloud-friendly nature, we’ll guide you through each setup to get your data flowing seamlessly into Power BI. By the end, you’ll be ready to start building impactful reports and dashboards with your Datasphere data. 

How to connect your Datasphere instance to Power BI

Below, we’ll walk you through the setup process for each connection type, from driver installation to loading data into Power BI, so you can start building insights with your Datasphere data. 

Create a ODBC connection 

To setup the ODBC connection you have to install the ODBC driver, and configure settings in your Datasphere tenant. Let's start with installing the ODBC driver: 

Step 1: Download and Install the SAP HANA ODBC Driver 

Download the SAP HANA client, which includes the ODBC driver, from the SAP Support Portal (SAP Development Tools (ondemand.com)) and choose the download for your operating system. Please note that for the Power BI ODBC Connector you’ll need the 64-bit driver. Once downloaded go you can run the file and click through the wizard to install the client. To make sure the client is installed correctly, go to your ODBC Data Administrator and check if the HDBODBC driver is on the list.  

Please refer to the SAP Tutorial for a more detailed guide: Install an ODBC Driver for Third-Party BI Client Access | SAP Tutorials  

Step 2: Configure your SAP Datasphere tenant   

In SAP Datasphere you have to set up a database user, which you use in the next step to create a user/system DSN. To create the database user you need the SAP Database Administrator role. Go to Space Management, choose the space that contains data that you want to consume in Power BI and open the tab Database access. Select Database Users and click on create. When you select ‘Enable Read Access’ the database user will be able to read data from analytic models and views in the space that have been exposed for consumption. Click create and then deploy the space to save your changes. When you click on the information button behind the database user, you will get the database user details, which you need for the ODBC driver and Power BI connector. 

For more information and detailed steps please refer to: Create a Database User in SAP Datasphere | SAP Tutorials    

Next you need to make sure your IP address is added to the IP allowlist. You can do this by going to Configuration, select IP Allowlist and click ‘Add’. Here you can fill in the IPv4 address of your system. When the ODBC driver is installed, and the Datasphere settings are configured, you are ready to configure the ODBC data source and connect it to Power BI. 

Step 3: Configure the ODBC Data Source 

Open the ODBC Data Source Administrator on your computer (search for “ODBC Data Sources” in your system settings). Under the "System DSN" or "User DSN" tab, click ‘Add’ and select HDBODBC as the driver for SAP HANA. Enter the necessary connection details: 

• Server: Enter the hostname or IP address of the SAP Datasphere instance. 

• Port: Default is 443 for SAP HANA (confirm with your Datasphere settings). 

• Authentication: Use your SAP Datasphere database user credentials for access. 

• Test the connection to verify that ODBC is correctly configured. 

Step 4: Load Data into Power BI 

Open Power BI Desktop and select Get Data > ODBC. Choose the ODBC data source you just configured. Enter your Datasphere database user credentials if prompted and select Connect. Power BI will load the data into the Navigator, allowing you to choose specific tables and fields for analysis. Once loaded, you can start creating reports and visualizations directly from your Datasphere data. 

For a complete overview of the detailed steps see: Connect Microsoft Power BI to SAP Datasphere | SAP Tutorials 

Create a OData connection  

The OData connection method leverages the OData protocol, allowing for more flexible, web-based access to SAP Datasphere data.  

 Step 1: Enable OData Access in SAP Datasphere 

• In SAP Datasphere, locate the data model you want to expose via OData. 

• Enable the OData API for this model by selecting the generate OData Request button. 

• Obtain the OData request URL for the model. This URL will be used to connect Power BI to the data model. 

Step 2: Connect Power BI to the OData Service 

• Open Power BI Desktop and go to Get Data > OData Feed. 

• Enter the OData URL from your Datasphere model. 

• Provide your SAP Datasphere credentials to authenticate. 

• Power BI will retrieve metadata from the OData feed, allowing you to select tables or fields. 

For a complete overview of the detailed steps see: Consume Data via the OData API | SAP Help Portal 

Step 3: Load Data into Power BI

• In the Navigator window, select the tables or fields you need, then click Load. 

• Power BI will import the data, which you can then use to create visualizations. 

• In Power BI, you can configure scheduled refreshes for OData sources, making it an efficient option for reports that need regular data refreshes 

Conclusion 

Connecting SAP Datasphere to Power BI through ODBC or OData gives you the power to unlock rich data insights and build dynamic visualizations for your organization. Whether you opt for ODBC’s direct and efficient data access or the flexibility of OData’s web-based connection, the integration steps are straightforward and easy to follow. 

With the ability to bring in and analyze data from your SAP Datasphere instance into Power BI, you’ll be able to create data-driven reports and make better-informed decisions. By selecting the right connection method and following the setup guide in this post, you can harness the best of both worlds and take full advantage of the combined capabilities of SAP Datasphere and Power BI. 

Questions?

Contact Daphne Vos (daphne.vos@mccoy-partners.com) .