Consuming SAP HANA Express Edition information models in Microsoft Power BI using live connection
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.
Initially I created my account at https://powerbi.microsoft.com. I also subscribed for the Microsoft Power BI YouTube channel.
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:
| Chart | Calculation View |
| 1 & 2 | SALES_ORDER_RANKING |
| 3 | CUSTOMER_DISCOUNT_BY_RANKING_AND_REGION |
| 4 | SALESORDER_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 |
| 3 | Slicer | CUSTOMER_DISCOUNT_BY_RANKING_AND_REGION | Region |
| 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.
Cheers,
Fernando
Hi Fernando, I am currently trying to make Power BI to work with SPA HANA Express. However, when I enter connection information in Power BI Get Data→Database→From SAP Hana Database screen, application stops responding without error message.
I do not have SHINE installed, could this be the reason of this behavior? Any suggestions how to solve this?
Hello Reinis, I assume you are using Power BI Desktop. so, under Get Data --> More..--> Database --> SAP HANA Database . you should get and screen asking for the Server and Database Connectivity : Import or DirectQuery.
if your device hangs I would suggest you to install Power BI Desktop on another device under your network. In my case I have a PC desktop with 32GB with SAP HXE VM. and I use Power BI Desktop on my Asus Laptop . (layout above). My Asus laptop has only 6GB and Power BI Desktop works just fine. However, if I try to use Power BI Desktop on the same PC desktop I have SAP HXE VM my system does not respond. if think that would be your issue.
Hello, I have installed SHINE in my Express VM - and can open/view the Sales Dashboard. I am now trying to 1) find the tables used by Shine via Hana Studio, and 2) figure out how to connect from Power BI Desktop (as you have done). In Power BI, I've tried using the login SYSTEM - but I only see a Contents folder (as described here by someone else - https://community.powerbi.com/t5/Desktop/SAP-HANA-Navigator/td-p/177954. If I try to login using XSA_SHINE, I can see what appears to be the same folder - but I can't proceed because I keep getting prompted to re-enter credentials (ODBC error - "General error: 258 insufficient privilege: Not authorized.").
Can you provide a bit more detail on the specifics of how you connect in Power BI Desktop?