The BI & Analytics team of McCoy & Partners are experts in SAP BI tooling. There are many companies who are working with backend SAP tools like SAP BW, SAP HANA or SAP S/4HANA, but are interested in using non-SAP tooling for their frontend. With this blog we would like to inform you on how you can create a live connection for Microsoft Power BI and Tableau to SAP HANA and Microsoft Power BI to SAP BW.
To connect your Microsoft Power BI to SAP HANA you must install the SAP HANA ODBC driver. This driver must be installed on every computer of the users who is going to use Microsoft Power BI desktop. The driver can be downloaded in combination with the SAP HANA tools via de SAP support portal (S-user required).
Once the driver is installed, the end-user can create a connection to the SAP HANA system in Microsoft Power BI. You can connect via sources > SAP HANA Database:
When connecting to the SAP HANA DB, make sure you select the right port to connect to the server:
The port will depend on your server settings, this could be the standard port 30015 or 30013 another port number selected by the sys-ops during installation.
It’s recommended to connect as a multi-dimensional source (this is the standard setting). You could change the connection setting to relational source via the options menu.
Considerations when connecting to a single analytic or calculation view:
Treat HANA as relational source is a more flexible solution because it allows calculated columns and connections to multiple HANA views.
Considerations:
Within the navigator, you can navigate through the folder structure to the view you want to use in your report. Any variables on the model will be exposed to Microsoft Power BI for selection.
There are a few things to consider during the creation of your SAP HANA views for reporting in Microsoft Power BI.
From a technical point of view, the integration between Power BI Desktop and SAP Business Warehouse (BW) is based on OLAP BAPIs (Business Application Programming Interfaces).
All 3rd Party Clients connect via these BAPIs. The OLAP BAPIs are implemented in SAP BW as RFC-enabled function modules and are invoked by Microsoft Power BI Desktop over SAP’s RFC protocol.
This requires the NetWeaver RFC Library or SAP .NET connector to be installed on your Microsoft Power BI Desktop machine. The OLAP BAPIs provide methods for browsing metadata, master data and passing MDX statements for execution to the MDX processor. The OLAP processor is responsible for retrieving, processing and formatting the data from the SAP BW source objects.
Aside from Microsoft Power BI Desktop, the following software drivers need to be installed when connecting Microsoft Power BI to SAP BW:
When installing the SAP .NET Connector 3.0, make sure you select “Install assemblies to GAC”.
Both drivers can be downloaded via de SAP support portal (S-user required).
Once the drivers are installed, you can connect Microsoft Power BI to SAP BW via the SAP BW Application Server or the SAP BW Message Server. SAP recommends to logon via the message server because of the load balancing arranged by this server.
Note: While connecting via the SAP BW message Server, it is needed to add the message server and port in the services file (c:\windows\system32\drivers\etc\services).
Once the database connection has been chosen, the next step is to choose the data connectivity mode. There are two options:
There are two implementation options:
SAP recommends version 2.0 because of the improved performance. This blog will only explain this option.
In the advanced options, there are some more settings:
The default value is Sap Business Warehouse Execution Mode.BasXmlGzip. Using Sap Business Warehouse Execution Mode.BasXmlGzip may improve performance when experiencing high latency for large datasets.
Note: When connecting to SAP Bex queries, you need to make sure to select the OLE DB for OLAP in the BEQ Query otherwise the query will not be visible in Microsoft Power BI.
Due to the OLAP/Multidimensional nature of SAP BW, there are some important limitations to consider when using Direct Query as connection method.
Limitations in Microsoft Power BI when using Direct Query:
Note: when using data import instead of Direct Query, most of the limitations mentioned above are no longer applicable.
Following BW features are not supported when using Microsoft Power BI:
Tableau has a similar way to connect to SAP HANA as Power BI. Tableau uses the same ODBC drivers as Power BI and uses the SQL engine of the SAP HANA server (the driver must also be installed on the tableau server).
After installing the ODBC driver, you can connect from the Tableau desktop to the SAP HANA DB. Click on adding a new connection via Connect to data and select SAP HANA:
Like Microsoft Power BI, you will get a popup where you can give in the credentials. The only difference is that you need to fill in a port number for both Single- and MultiNode whereas for Microsoft Power BI you have to select custom when your port is different than standard.
Once connected, you must select a schema within your SAP HANA DB. Within the schema you can select the view you want to use for tableau.
In Tableau, you also have to opportunity to connect and join multiple views. This is like “treat as relational source” in Microsoft Power BI. This also requires in-dept knowledge of the data model and a setup of a star model to ensure proper aggregation in the reports. Creating a model on one or mode fact view(s) and join them with master data views could give more flexibility and speed. A best practice here is to have your model set up in a way you can always use inner join to create the model.
When you have selected the views or tables you can use the connection or data source tab to hide or rename dimension or measure headers.
Like Microsoft Power BI, Tableau also doesn’t show parent child hierarchies. These hierarchies can be setup in Microsoft Power BI by making custom hierarchies. These hierarchies are level based and are created in the reports.
Would you like to know more about connecting non-SAP tools on SAP BW or SAP HANA, please contact Roel van Bommel. Roel.Van.Bommel@mccoy-partners.com or (+31) 06 22 69 83 92