Skip to Content
Technical Articles
Author's profile photo Vignesh S
Vignesh S
November 9, 2023 2 minute read

Consuming data from SAP Datasphere into Power BI via ODBC connector

I wanted to share my proof of concept about consuming data from SAP Datasphere into Power BI via ODBC connection (Open database connection)

Required:

  • Datasphere tenant.
  • Power BI.
  • Need to create a database user for the particular space in the space management.
  • Host name, Port number, Password, Database username.
  • Need to add External IPV4 Address in the IP Allowlist.
  • Hana ODBC client.
  • Need to add credentials in Power BI.

Datasphere part:
Login into Datasphere -> Space Management -> Choose the space and select Edit.

Click Create and Make sure that you have enabled Expose for consumption by default


Copy Database Username, Host name, Port, Password.

Go to System-> Configuration-> IP Allowlist-> Trusted Ips

EXTERNAL IPV4 ADDRESS should be added here, not Internal IPV4

To get External IPV4 Address

  • Open Command prompt and enter curl ifcfg.me
  • Then run curl ifcfg.me in command prompt and add that IPV4 Address in the IP Allowlist.

Add and Save the External ipv4 address here.

ODBC PART:

Need to install SAP HDODBC driver SAP Development Tools (ondemand.com) in the system.

Open ODBC in the system

Click Add

Select HDODBC

  • Give any meaningful name to Data source name, description.
  • Database type: SAP HANA Cloud or SAP HANA Single tenant (both will work fine).
  • Already copied Host URL in datasphere space, Paste the copied Host URL.
  • Click Test connection
  • Paste the Database username in Username and Password.

POWER BI PART:

Power BI-> Get data-> ODBC.

Select the created ODBC Connection

Paste the same Username and Password which copied in datasphere part, click “Connect”.

Successfully Power BI consuming data from SAP Datasphere

Dashboard built in Power BI

From this POC:

  • Data from Datasphere can be consume via OBDC connection 100/100.
  • Since ODBC connection is possible, we can consume data into any third-party reporting tools that we want.

 

THANK YOU !

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jon Humphrey
      Jon Humphrey

      Hi Vignesh, great blog,

       

      Can you consume Datasphere native semantics (Labels) using this method or is it just the technical names that show up?

       

      Thanks

       

      Jon

      Author's profile photo Vignesh S
      Vignesh S
      Blog Post Author

      Hi Jon,

      • For answering your question, Yes it is possible to get native semantics(Labels) for that you should have maintain same format in datasphere and then establish connection to ODBC and Power BI.
      • You can able to rename the columns name in Power BI by using Power Query (ETL Tool available within Power BI)

      Thank you.

      Author's profile photo Jon Humphrey
      Jon Humphrey

      Hi Vignesh,

      Thanks for the reply but with HANA this was all seamless.  The business facing names were maintained in calculation views and were all available for a BI.  The above methodology required the business facing names to be maintained independently in Power BI and Datasphere and this is not very efficient and also likely to become unsynchronised.

      Author's profile photo Vignesh S
      Vignesh S
      Blog Post Author

      Hi Jon,

      Integrating two different platforms has never been easy and seamless. In our case, using SAP Datasphere and Power BI is a game-changer. SAP Datasphere enables data transfer from one source to multiple BI solutions via an ODBC connection.

      As you said  The business facing names were maintained in calculation views and were all available for a BI right? Yeah it is correct,

      • In datasphere, you will do same process in Graphical view/ Data flow that will be available for Power BI.
      • Adding on to the point: Further you want to model again in Power BI for that Power Query  allows you to perform Full Fledged ETL as like Datasphere ETL.

      Thanks.

      Author's profile photo Jon Humphrey
      Jon Humphrey

      Hi Vignesh,

       

      But the whole point of datasphere is centralised metadata management.  Why would I want to create a power query for something that should just be available to any consumer?

       

      Thanks,

       

      Author's profile photo Max Post
      Max Post

      Hi Vignesh,

       

      Thanks for your blog.

      We're currently trying to do the same thing at a customer, but we are having trouble importing measures that we have created in the Analytical Model.

      Do you have any experience with this? We want to manage all data (definitions) in one place (Datasphere).

      Thanks,

      Max

      Author's profile photo Vignesh S
      Vignesh S
      Blog Post Author

      Hi Max,

      What are the source connections for datasphere? From where you are trying to import measures?

      Yeah it is possible to manage all data (definitions) in one place (Datasphere).

      Thanks.