Skip to Content
Product Information
Author's profile photo Avinash Darapureddy

Connect to sap HANA Database from Microsoft power BI(Business Intelligence)

Introduction: For Building dashboards in Microsoft Power BI(Business Intelligence) tool  using Hana database as a source we need to enable the ODBC driver in the system to connect to Hana database. Please follow below steps to configure and build dashboards on Hana database(Calculation views)

 

Step 1 

Install HDB client software in system where you want to connect Hana Database. Hana client software enables the ODBC Driver as below. 

 

https://tools.hana.ondemand.com/#hanatools 

 

Type  odbc in windows search bar and click  ODBC data source 32/64 bit

ODBC

ODBC

Step 2:

Click add on dBase

Hana%20ODBC

Hana ODBC

Step 3:

Double click on HDBODBC

 

ODBC%20Config

ODBC Config

Enter below details and save the connection.

 

Host : Hana database host
Instance number  as example 00
And tenant database would be Hana system name

 

Step4: go to power BI and go to Hana database connection.

 

PBI-hana%20database

PBI-hana database

Enter username and password of SAP Hana Database

 

connection%20parameters

connection parameters

 

Conclusion: With following above steps We can connect to SAP Hana Database from Microsoft Power BI and we can use import or direct query connection to consume Hana calculation views or Hana reporting objects

 

Note: For Direct queries we need to configure certificate so that refresh of data can happen on the live

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo ofra dar
      ofra dar

      I enjoy working with Microsoft Power BI.This free program can recognize and connect to over 70 data sources. For example, xlsx, csv files, txt, data from SQL databases. It can also cleanse the data or process it and bring the entire million tablets to a single data model. Or you can define your own custom metrics that are used specifically in your company. By the way, I recently started using power bi integration with jira  . This gives me the ability to quickly export the data I need to build my models. Convenient, simple and fast

      Author's profile photo Sampath K Redyam
      Sampath K Redyam

      Hi Avinash,

       

      Power BI desktop not fetching all the HANA calculation views

       

      I successfully connected to the SAP HANA database from Power BI Desktop application, but Power BI desktop not showing all the content from the HANA. Any reason why and how this can be solved?

       

      Power Bi version - 2.93.981.0 64-bit (May 2021)

      Author's profile photo Ahmed Ali Khan
      Ahmed Ali Khan

      Hi Avinash,

      Thanks for sharing, I just want to ask from where I can find out the host and port name, Also it's tenant database name if I am using SAP HANA XSA ?

      Author's profile photo Krishna Avinash Darapureddy
      Krishna Avinash Darapureddy

      Hi Ahmed,

      You can check with your Basis team to find the exact host and port details. Even i had issues with the ones I tried getting from  login to sap launchpad and go to system on the top and click parameters or services where we can get host name, Not sure how se can get in HANA XSA.

      Regards,

      Avinash

      Author's profile photo Sachin B G
      Sachin B G

      Hi Avinash Darapureddy

       

      Can you please help on the user level configuration to be done ie., privilege's to be provided and roles to be assigned for the user in HANA, which is being used to connect to Power BI,

      We have an issue where all the Schemas are getting reflecting in the Power BI when we connect we require only selected schema to be shown.

       

       

       

      Author's profile photo Krishna Avinash Darapureddy
      Krishna Avinash Darapureddy

      Hi Sachin,

      We also had issues with security. We created everything on composite providers so that sap Access is cascaded to Power BI. Other option for you would be to create roles in Power BI and assign to users. Few links might be useful

      https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

      https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-rls

      https://docs.microsoft.com/en-us/power-bi/admin/service-admin-power-bi-security.

      Regards,

      Avinash

      Author's profile photo Cristian Julián Cedeño Guzman
      Cristian Julián Cedeño Guzman

      I dear Avinash Darapureddy thanks for sharing.

       

      I have a issue when use a CALL in the import option on PBI:

       

      1. This is my SP:

      SP%20Sintax

      SP Sintax

       

       2. This is the result of the SP when I run it in HANA:

      Result%20ok

      Result ok

        3. Call on PBI:

      SP%20Call%20on%20PBI

      SP Call on PBI

         4. Call error in PBI: 

      Call%20error%20in%20PBI

      Call error in PBI

      Strangely, when I call a stored procedure, it always throws this syntaxys error in PBI, however, when I make a direct Select to the tables instead of a Call to the procedure, it works, do you know why?. Are there any product limitations that prevent me from making CALLs to SPs?

      My ODBC is configured according to your guide and it works perfect if I make an ODBC type connection to PBI, but when I try to do the procedure described here, I always get the same error no matter what I do.

      1. Direct select in PBI import option:

      Direct%20Select%20in%20PBI

      Direct Select in PBI

         2. Data load ok:

      Data%20Ok

      Data Ok

         3. PBI report with select data:

      Thank you in advance for your response.

       

       

      Author's profile photo Krishna Darapureddy
      Krishna Darapureddy

      Hi Cristian Julián Cedeño Guzman

      I have tried and getting the same error. How ever it worked with below ODBC driver.

      Please try and let me know if it works.

      Regards,

      Avinash

      Author's profile photo Cristian Julián Cedeño Guzman
      Cristian Julián Cedeño Guzman

      Thank you very much dear @Krishna Darapureddy , certainly when I do the same CALL using the ODBC connection it works perfect, in fact I made a tutorial explaining how to make a CALL to a SP using the ODBC connection: https://www.youtube.com/watch?v=yxr7Gmpw59g&ab_channel=CristianJulian

      However, that I want is to understand the reason why, if I use the HANA connector, I cannot perform the CALL, but I can perform the SELECT. It is really strange, and I would like to know if it is a problem with SAP, PBI, if there is any way to make it work, if CALLs are not supported in PBI, if there is any official documentation regarding this, etc. I have searched a lot in PBI and SAP forums, but I have not been able to find a definitive answer. The solutions always lead to doing it through ODBC.

      Author's profile photo Michael Hill
      Michael Hill

      Ya'll I have some folks who when they try to connect .... in the "port" there is single-container 30015 and multi-container 30013. Now we all know that users dont need to connect to the systemdb ( 30013 ). There doesnt appear to be another- selection in the pull-down.

      Is there some .ini file or related config where a custom config is stored?

      Mike

      Author's profile photo John Jairo Moreno Bohórquez
      John Jairo Moreno Bohórquez

      nice help, very detailed