Datasphere to Power BI via an ODBC Connection

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 DatasphereInstall an ODBC Driver for Third-party BI Client AccessAdd your IP address to IP allowlistEnsure entities in SAP Datasphere are consumableConnect Microsoft PowerBI to SAP DatasphereRefreshing the DataF&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.

1. Go to Datasphere

2. 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:

ManuallyScheduled

 

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.

 

FAQs

Question

Answer

What does ODBC stand for?

Open Database Connectivity

What does an ODBC driver do?

An ODBC driver uses the ODBC interface by Microsoft that allows applications to access data in DBMS using SQL as a standard for accessing the data.

What databases are supported by ODBC drivers?

ODBC drivers are available for a wide range of databases, including SQL Server, MySQL, PostgreSQL, Oracle, IBM Db2, and many others.

What is the purpose of creating a Database user in Datasphere?

Creating a Database user in Datasphere allows you to establish a secure connection between Datasphere and external tools like Power BI. The Database user credentials are used for authentication when connecting to the database.

How do I know which ODBC driver version to install (32-bit or 64-bit)?

The version of the ODBC driver you need depends on the version of the BI tool you’re using. For Power BI Desktop, which is 64-bit, you should install the 64-bit version of the ODBC driver.

Where can I find the ODBC driver for Datasphere?

You can download the ODBC driver from the HANA developer tools website. Follow the instructions provided to select and install the appropriate driver for your system. https://tools.eu1.hana.ondemand.com/#hanatools

Why do I need to add my IP address to the IP allowlist in Datasphere?

Adding your IP address to the allowlist ensures that external applications, like Power BI, can securely access the Datasphere database. This is a security measure to prevent unauthorized access.

How can I find my public IP address to add to the allowlist?

You can find your public IP address by using the command curl ifconfig.me in the command prompt

What is a Power BI Gateway?

It is a tool that controls access to data on an on-premises network. When you want to reach this data from a cloud app like Datasphere, your request goes through this tool.

 

​ IntroductionWelcome 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 DatasphereInstall an ODBC Driver for Third-party BI Client AccessAdd your IP address to IP allowlistEnsure entities in SAP Datasphere are consumableConnect Microsoft PowerBI to SAP DatasphereRefreshing the DataF&Qs Create a Database user in DatasphereTo 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:TitleInfoUser NameTEST_SPACE#TEST_SPACEHost Name****Port****Password****  Installing and Configuring the ODBC DriverTo 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 DriverBegin 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 DriverOnce 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 SourceOpen 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 SetupClick “OK” to save your configuration. You have now successfully created an ODBC data source on your Windows system Add your IP address to IP allowlistTo 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.me2. Copy the external IPV4 address3. 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 consumableBefore 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.1. Go to Datasphere2. When you create model or data entity you need to make sure ‘Expose for Consumption’ is turned on.  Connect Microsoft PowerBi to SAP DatasphereTo 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 DataThere are 2 ways to refresh your data in PowerBI:ManuallyScheduled 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. Scheduled1. 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.  ConclusionsIn 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. FAQsQuestionAnswerWhat does ODBC stand for?Open Database ConnectivityWhat does an ODBC driver do?An ODBC driver uses the ODBC interface by Microsoft that allows applications to access data in DBMS using SQL as a standard for accessing the data.What databases are supported by ODBC drivers?ODBC drivers are available for a wide range of databases, including SQL Server, MySQL, PostgreSQL, Oracle, IBM Db2, and many others.What is the purpose of creating a Database user in Datasphere?Creating a Database user in Datasphere allows you to establish a secure connection between Datasphere and external tools like Power BI. The Database user credentials are used for authentication when connecting to the database.How do I know which ODBC driver version to install (32-bit or 64-bit)?The version of the ODBC driver you need depends on the version of the BI tool you’re using. For Power BI Desktop, which is 64-bit, you should install the 64-bit version of the ODBC driver.Where can I find the ODBC driver for Datasphere?You can download the ODBC driver from the HANA developer tools website. Follow the instructions provided to select and install the appropriate driver for your system. https://tools.eu1.hana.ondemand.com/#hanatoolsWhy do I need to add my IP address to the IP allowlist in Datasphere?Adding your IP address to the allowlist ensures that external applications, like Power BI, can securely access the Datasphere database. This is a security measure to prevent unauthorized access.How can I find my public IP address to add to the allowlist?You can find your public IP address by using the command curl ifconfig.me in the command promptWhat is a Power BI Gateway?It is a tool that controls access to data on an on-premises network. When you want to reach this data from a cloud app like Datasphere, your request goes through this tool.   Read More Technology Blogs by Members articles 

#SAP

#SAPTechnologyblog