This a simple guide showing how to consume SAP HXE information models in Microsoft Power BI using live connection (aka Direct Query ).
I have been studying Power BI for the past 4 weeks. I confess I am very impressed how Power BI is easy to work with.
so, I decided to play around connecting Power BI with my SAP HXE instance. Generally speaking there are two ways of connecting and consuming SAP HANA information models in Power BI: Import and DirectQuery. For this post, I will be showing my SAP HANA live data connection with Power BI (DirectQuery).
Requirements needed for this tutorial:
- SAP HANA 2.0, Express Edition (my installation is based on Virtual Machine). more details here.
- SAP HANA Studio
- Microsoft Power BI Desktop
- Microsoft Power BI On-premises data Gateway up and running
- SAP HANA Interactive Education (SHINE), which is part of the SAP HANA Express Edition
- Power BI App from Itunes and Google Play store
My Private Network layout:
Test case scenario
My test case consists in replicating one of the SHINE Dashboard into Power BI for learning proposes only. This scenario will use continuous connection with my SAP HANA database.
SAP HXE SHINE (Interactive Education)
The following screenshot is from you SAP HXE installation: SHINE. Sales Dashboard:
The following are the Calculation Views used as source for Sales Dashboard:
|1 & 2||SALES_ORDER_RANKING|
The Calculation Views above belong to the SAP HANA Democontent package, EPM Models.
Power BI Desktop
now, down here is the same SHINE Sales Dashboard but this time built in Power BI Desktop:
Interpretation of the Charts as per Power BI Desktop:
|Chart||Type||Table (Information Model in SAP HANA)||Fields|
|1||Pie Chart||SALES_ORDER_RANKING||Region, NetAmount|
|2||Clustered Column Chart||SALES_ORDER_RANKING||Country, NetAmount|
|4||Donut Chart||CUSTOMER_DISCOUNT_BY_RANKING_AND_REGION||Company Name, Discount|
|5||Scatter Chart||SALESORDER_RANKING||Customer_Name, Sales, Sales Rank, and Orders|
it took me very little to build this report, considering my knowledge of Power BI: (roughly a month)
Power BI Desktop: Connection with SAP HXE database
When selecting the SAP HANA database connectivity, make sure to choose DirectQuery:
Power BI On-premises Data Gateway
Considering the understanding of the previous steps, I would suggest the following sequence:
- Download the On-Premises Data Gateway from powerbi.microsoft.com
- Install it and make sure the installation as well as the connection are successful
- Go back to powerbi.microsoft.com and setup the datasource (connection) for the SAP HXE
- From Power BI Desktop client tool, publish your report into Power BI
For steps 1 and 2: The final results must be like this:
For step 3: the Datasource (or connection) MUST be as same as the one used in the Power BI Desktop when connecting with the SAP HXE database:
Tip: The Username and Password here is the one you created in step 2 when installing and setting up On-Premises Gateway. it has nothing to do with the SAP HANA database (from experience)
Step 3: Power BI Desktop: Finally publishing the report into Power BI by Choosing “Publish” from the extreme top-right corner. Choose the Workspace to be published to and hit Select:
After the completion the status will show up:
done. pretty easy to setup.
Using Power BI Mobile App
Now I can just jump into my Power BI mobile App and interact with my Dashboard. Screenshot from my Iphone 7:
That’s all folks. I hope you find this article useful.