Connecting non-SAP tools on SAP HANA and SAP BW
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.
MS Power BI & SAP HANA
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:
All the measures, hierarchies, and attributes of the selected view will be displayed in the field list
It’s not possible to add calculated columns or combine data from multiple HANA views in the same report
Ensures correct aggregation when dealing with more complex HANA measures
Generally has better performance than HANA as relational source
Treat HANA as relational source is a more flexible solution because it allows calculated columns and connections to multiple HANA views.
Considerations:
Attention must be paid to any further aggregation performed in visuals, whenever the measure in SAP HANA is non-additive (for example not a sum, min, or max)
Aggregation is done over all selected columns in the source which may impact performance when selecting too much data.
Limit the number of items/dimensions needed
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.
All objects in your view will be exposed, as well as objects which are set as “hidden”. Therefore, it’s best to only use objects in your view that are needed for your reporting and leave the technical fields out.
Parent child hierarchies will not be visible in Microsoft Power BI. Level based hierarchies such as calendar hierarchies are visible. Level based hierarchies can also be created in Power BI by combining several dimensions (e.g. Year/Month/Day or Product Cat/Product).
MS Power BI & SAP BW
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:
To connect MS Power BI to SAP BW, first you need the SAP BW ODBO driver locally on all computers of the users who are going to use MS Power BI desktop.
The second driver needed is the SAP .NET Connector 3.0. The connector comes in 32-bit and 64-bit versions. Choose the version that matches your Microsoft Power BI Desktop installation.
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:
Import data from SAP BW into Microsoft Power BI. The data is stored in Microsoft Power BI. When using Microsoft Power BI Service, it is possible to schedule the load automatically. There is a limitation to the number of records which can be loaded into Microsoft Power BI.
Use a direct query connection. In that case, the data the data remains in SAP BW. Only the metadata of the visualization is stored in Microsoft Power BI.
There are two implementation options:
Version 1.0 via Netweaver RFC
Version 2.0 via the SAP.Net connector
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:
Execution Modespecifies the MDX interface used to execute queries on the server. The following options are valid:
Sap Business Warehouse Execution Mode.BasXml
Sap Business Warehouse Execution Mode.BasXmlGzip
Sap Business Warehouse Execution Mode.DataStream
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.
Batch Sizespecifies the maximum number of rows to retrieve at a time when executing an MDX statement. A small number translates into more calls to the server while retrieving a large dataset. A large number of rows may improve performance but could cause memory issues on the SAP BW server. The default value is 50.000 rows.
Enable Structuresindicates whether characteristic structures are recognized. The default value for this option is false. Affects the list of objects available for selection. Not supported in Native query mode
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:
Many features of SAP BW are not supported in Microsoft Power BI
Additional model restrictions in Power BI e.g.
No calculated columns
No grouping or Clustering
Limitations for measures (not all DAX expressions are supported e.g. aggregation)
No data view
Not possible to define relationships
Column and measure details are fixed
Additional Visualization Restrictions
Not aggregation on columns; it is always do not summarize
Measure Filtering is disabled
Not possible to select multiple data points in a visual if the points represent values for more than one column (e.g. sales by country and category)
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 & SAP HANA
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.
Interested?
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