Introduction
Welcome to this blog where I will provide an overview of the following key aspects in relation to creating an ODBC connection between Datasphere and PowerBI.
- Create a Database user in Datasphere
- Install an ODBC Driver for Third-party BI Client Access
- Add your IP address to IP allowlist
- Ensure entities in SAP Datasphere are consumable
- Connect Microsoft PowerBI to SAP Datasphere
- Refreshing the Data
- F&Qs
Create a Database user in Datasphere
To connect SAP Datasphere with PowerBI, the first step involves creating a dedicated database user in Datasphere. This user will serve as the bridge for data access between Datasphere and your BI tool. Below, we outline the process to set up a database user, ensuring you have the necessary credentials for a smooth integration.
1. In Datasphere, go to Space Management then go to Database Access.
2. Click on create Database User
3. Give your Database User a name. You will get the following details:
Take note of the Database User Name, Host Name, Port and Password:
| Title | Info |
| User Name | TEST_SPACE#TEST_SPACE |
| Host Name | **** |
| Port | **** |
| Password | **** |
Installing and Configuring the ODBC Driver
To enable your third-party BI tool to connect with SAP Datasphere, you need to set up an ODBC (Open Database Connectivity) driver on your Windows system. This driver acts as a bridge between your BI tool and the HANA database within Datasphere. In this section, we’ll guide you through the steps to install and configure the ODBC driver, ensuring that your data connection is properly established and ready for use. Follow the steps below to download, install, and set up the ODBC driver, allowing seamless integration with your BI tools.
1. Download the ODBC Driver
- Begin by searching for “ODBC” in the Windows search bar. Every Windows system comes with an ODBC Manager pre-installed. Depending on the version of the BI tool you are using, choose between the 32-bit or 64-bit ODBC Manager.
- Navigate to the HANA developer tools website here to find and download the ODBC driver suitable for connecting to the HANA database. Be sure to save the download to your local laptop drive for easy access..
2. Install the ODBC Driver
- Once the download is complete, locate and run the hdbsetup.exe file from your local drive.
- Follow the installation wizard to complete the setup. This will install the necessary ODBC client on your system.
3. Configure the ODBC Data Source
- Open the ODBC Data Sources application on your system. You should now see a new entry labelled “HDBODBC.”
- Go to the “User DSN” tab and click on “Add…”
- Select the HDBODBC (HANA Database ODBC) driver from the list and click “Finish.”
- Enter a data source name, description, and the host address. You can find the host address in Datasphere by navigating to Space Management, selecting your space, going to Database Access, and checking the details of your database user.
4. Finalize the Setup
- Click “OK” to save your configuration. You have now successfully created an ODBC data source on your Windows system
Add your IP address to IP allowlist
To allow an external BI client, like PowerBI, in your local network to connect to the database of Datasphere, you need to add the external (public) IPv4 address of the client to an allowlist.
1. Open Command prompt and enter:
curl ifcfg.me
2. Copy the external IPV4 address
3. Within Datasphere, go to Configuration >> IP Allowlist >> click on Add and enter the IPv4 address of your system to add it to the allowlist.
Ensure entities in SAP Datasphere are consumable
Before you can utilize data from SAP Datasphere in your BI tool, it’s essential to ensure that the data entities and models you wish to access are properly configured for external use. This involves adjusting settings within Datasphere to make your data available for consumption.
- Go to Datasphere
- When you create model or data entity you need to make sure ‘Expose for Consumption’ is turned on.
Connect Microsoft PowerBi to SAP Datasphere
To leverage the power of SAP Datasphere data within Microsoft Power BI, you need to establish a connection using the ODBC driver you previously set up. This process allows you to seamlessly import and visualize your Datasphere data in Power BI for in-depth analysis and reporting. Follow these steps to connect Power BI to your SAP Datasphere data:
1. Open the Microsoft Power BI and click on the Get Data icon.
2. Search for and select ODBC
3. From the dropdown, select the data source you created
4. Once you select OK, you’ll be asked to get the username and password. These can be found back in Datasphere (Space Management >> Database Access >> Database Users >> Find your user and select the info icon)
5. Once the username and password is successful, you’ll get a list of the data models and entities within your selected test space. In this scenario, we are going to use ‘TELCO_CUSTOMERCHURN_VIEW’
6. Select the view you’d like to use in Power BI and click Load.
7. You can now create a dashboard based on the data from DSP.
Refreshing the Data
There are 2 ways to refresh your data in PowerBI:
- Manually
- Scheduled
1. The first steps into refreshing your data, you need to publish your dashboard.
2. Once you publish your dashboard, it will bring you to the PowerBI browser. Click on the “My Workspace” app on the left-hand side.
Manually
To manually refresh your data, simply navigate to ‘My Workspace’ and click the refresh now icon attached to your model.
Scheduled
1. You then need to click on the “Schedule refresh” icon on your Semantic model.
2. You’ll be presented with a window like this
3. Scroll down to the “Gateway connections”. If you have no gateways installed like this. You must select “Install now” to install a data gateway.
4. Accept the T&Cs and click “Install”
5. Once the gateway finishes installing, enter an email address and click “Sign in”
6. Once you have signed in, click on Close.
7. Dropdown the ‘Gateway and cloud connections’ header and you’ll be able to see your personal gateway
8. Dropdown the ‘Refresh’ header. Here you can now schedule a data refresh.
9. When you go back to your workspace, you can see when the next refresh is scheduled to occur.
Conclusions
In summary, connecting SAP Datasphere to Power BI through an ODBC connection enables powerful data integration and visualization capabilities. By following the outlined steps, you can seamlessly import and analyse your Datasphere data within Power BI. Additionally, the ability to refresh your data either manually or on a scheduled basis ensures that your reports and dashboards reflect the most current information.
