Introduction
Microsoft Power BI is a Self-service Business Intelligence and Analytics solution, both available as a Desktop version and a cloud-distributed version, that can be used to generate reports and dashboards to gain quick insights into data. Development is done using Power BI Desktop, which is a free application that can be installed on a PC and allows connecting to, transforming, and visualizing data.
Power BI supports connections to all major databases including SQL, Oracle, IBM DB2, MYSQL, Amazon Redshift and Google BigQuery and several others. With respect to SAP data sources, Power BI can connect to SAP HANA calculation views, SAP Business Warehouse application server (cubes, Bex queries), SAP BPC cubes, SAP BW4/HANA and SAP Business warehouse message service. When connecting to SAP BW server, Power BI supports both direct and import connections to BW cubes, the more recent ADSO (advanced DataStore Objects) and Bex Queries.
This insight will discuss the various methods to connect Power BI to SAP data sources (BW, BPC and HANA), their pre-requisites and also compares the limitations of each connection type.
Power BI Connectors
Installation for connection to SAP BW, BPC, BW4/HANA (cubes/queries) & HANA (calculation views)
There are 2 types of connectors available from Power BI, namely, the Implementation 1.0 and Implementation 2.0. The 1.0 connector requires the installation of the SAP Netweaver RFC library that can be downloaded from the SAP Launchpad. One note to make when installing, is that the Bit (32 bit/64 bit) version of Power BI must match the RFC library version.
On the other hand, the Implementation 2.0 requires the SAP .NET connector ( Download SAP .NET connector ) that can be downloaded from the SAP website. Like in 1.0, care must be taken to match the Power BI bit version with the .Net connector version. In addition the .NET framework version must match the one present on the PC where Power BI desktop is installed.
For the connection of Power BI to SAP HANA (calculation views), the only pre-requisite is the installation of ODBC drivers. These drivers can be installed by searching for HANA client on the SAP Launchpad and subsequently downloading and installing the drivers. Please note that you need a valid SAP S-user to download the files from SAP launchpad. If desired the HANA client can also be downloaded from the developer community ( HANA client Download ) which is free but requires a registration. During the installation of the HANA client, several drivers can be selected to be installed, the minimum required selection is “ODBC drivers”.
Power BI Connection Types
There are 2 connection modes possible when connecting from Power BI to the on-premise SAP systems, namely, Direct Query and Import Connection.
When using an "Import Connection", as the name suggests, the selected tables and columns are imported into Power BI and the modeling and visualization in Power BI uses the imported data. In order to see any changes to the underlying data, a refresh must be done which imports the data set again to reveal any changes since the last import or refresh. Data refresh events can also be scheduled by using the “Schedule refresh” option in the data set screen.
Direct Query is a 2nd connection type which -when used- does not import any data but a connection is maintained to the underlying on-premise system and the available columns are shown in the Power BI fields list. When interacting with tables or visualizations, Power BI queries the underlying data which means that the user is always viewing live data.
Limitations by connection type (BW cubes / Queries)
Both connection types have their own pros and cons.
However, in general, using an import connection gives much more features in Power BI than Direct Query.
A comparison of the features and significant limitations when using the connection types in the context of SAP data BW data sources is shown below.
Item | Import Connection | Direct Query |
Field list | A flattened list of all fields or selected in the underlying data source are shown in Power BI fields list. | Well organized dimension structure from the underlying data source is retained in the field list. |
Data Volume | Limited to 1 GB of data. | Limited to 1 million rows. |
Query Editor | Enabled and hence all data transformations possible. Additionally data from multiple | Not available and hence transformations are not possible or are limited. |
Calculated Columns | Calculated columns can be created. | Disabled; consequently Grouping and clustering are also not available. Calculated measures are |
Multiple Hierarchies | Multiple hierarchies selection from the same dimension are not allowed. | |
Bex Queries |
| |
Char attributes | Attributes of a characteristic are available in the properties of a dimension. | Not available. |
Security | Roles need to be maintained in Power BI. | When SSO is configured for the data source, queries execute under the identity of the user interacting with Power BI. |
Looking at the limitations above, it can fairly be concluded that Direct Query method is less flexible than the import method. Direct Query connection is more suited if viewing live data is a prime requirement and there are no specific requirements for manipulation of data on Power BI. Use of the import method gives more flexibility to transform data before the reporting is done. It can also be concluded that use of Bex queries as a data source for Power BI imposes quite some restrictions like “Customer exit variables” which may be required in many cases.
Limitations by connection type (HANA)
As was explained previously, the pre-requisite for connecting to an on-premise SAP HANA database is the installation of ODBC drivers. The 2 main connection methods, namely, the "import connection" and "direct query connection" are also available when connecting to a HANA database. Using one of the above methods, Power BI can connect to both HANA calculation views.
Note that Power BI does not allow access to the catalog folder and hence cannot connect to the database tables directly. However, the direct SQL feature can be used (SQL code; example select on specific tables) to bring data from HANA tables.
When connecting from Power BI to a SAP HANA database, in the direct query connection, 2 modes are supported (Power BI setting), namely, "Multi dimensional source mode" and "Relational source mode" which can be changed in the settings of Power BI.
When connecting using the "multi dimensional source setting", Power BI allows the selection of only a single calculation view and all the measures, hierarchies and attributes of the selected view will be added and are available in the Power BI field list. When visuals are created in Power BI, the aggregate data will always be retrieved on the fly from SAP HANA.
On the other hand, when using the relational source setting, Power BI will treat SAP HANA as a relational source and hence offers greater flexibility. However, extreme care must be taken to ensure that measures are aggregated as expected and in a way that performance issues can be avoided. The most significant limitations when using any of the connection methods above are shown and explained in the table below.
Item | Import | Direct Query | Direct Query |
# Data sources |
| Single calculation view | Multiple Views |
Data Types |
| ||
Catalog access | No access to catalog folder (schemas) on HANA database | ||
SQL Statement | Additional SQL statements can be defined and hence indirect access to the HANA tables possible | SQL Statements not supported | Additional SQL statements can be defined and hence indirect access to the HANA tables possible |
Calculated columns |
| Not possible |
|
Hierarchies |
| Parent-Child hierarchies will | |
SSL | You cannot connect to SAP HANA instances configured to use SSL | ||
It should also be noted that Power BI reports built using one connection type, in general cannot be changed to other connection types.
Additionally, Power BI has -at the moment of writing this insight- a known issue when connecting to a HANA data source, namely that incorrect data can be shown when connecting from to an analytical view and including a counter measure along with a ratio measure. In order to have a secure connection between Power BI Cloud & the on premise SAP system, a gateway has to be installed.
Conclusion
Power BI is one of the most popular BI tools in the market today. In this article we have described the difference between the Implementation 1.0 and 2.0 when connecting from Power BI to any on-premise data source. The pre-requisites and the method to install them for both implementation 1.0 and 2.0 were explained. The various connection types possible in Power BI were explained along with their limitations in the context of connecting to SAP data sources (BW & HANA). The following main conclusions can be drawn:
- A few pre-requisites are needed to be installed before being able to connect to a SAP data source; RFC library (for 1.0 connection), SAP .NET connector (for implementation 2.0) and ODBC drivers when connecting to SAP HANA database.
- Implementation 2.0 bring several improvements over 1.0, mainly in connection times and speed of execution particularly with large data sets.
- It is fair to conclude in general the features are trimmed when using a direct query connection as opposed to an import connection.
- A few significant drawbacks when connecting to BeX queries as many functions are not exposed by SAP through a public API and hence cannot be accessed by Power BI.
- When connecting to a HANA database, 2 variants of direct Query possible which have their own pros and cons.
Want to know more about connecting PowerBI to SAP data sources, do not hesitate to drop us an email and let us assist you !
