Consuming data from SAP Datasphere into Power BI via ODBC connector

[vignesh3027]

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 nslookup myip.opendns.com resolver1.opendns.com or curl ifcfg.me

• Then run nslookup myip.opendns.com resolver1.opendns.com
   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 !

[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

[vignesh3027]

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.

[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.

[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

[vignesh3027]

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.

[vignesh3027]

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.

[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,

 

[HaraldG]

Great blog, thank you so far!

I was able to import data from SAP DSP to Power BI via ODBC, but I only get the technical names in PBI...

Any idea how this can be resolved?

Thanks & best regards, Harald

[Max_Post]

Hi Vignesh,

I meant that we created all the data definitions in Datasphere and we want to import them in Power BI. However, we seem to be unable to import the measures created in the Analytical model into Power BI.

Thanks,

Max

[nilrod]

Hi Vignesh,

Thank you for the helpful post.

It seems like in Power BI, data from 'datasphere' would be imported. Is there a way to do "Direct Connect" with SAP Datasphere from Power BI or "Import" is the only option? 

Thanks,

Nilrod