As a self-service reporting tool, Microsoft Power BI offers many useful features for the quick and easy creation of graphical analyses and dashboards.
The integration into the Office 365 Suite also provides flexible deployment options.
Power BI supports a variety of different data sources. Especially for companies that have SAP BW in use, however, the connection of this is in the foreground.
This blog post deals with the various options for connecting SAP BW to Microsoft Power BI.
The Power BI Architecture - Overview
Power BI provides two different connection paths for accessing SAP BW – / SAP HANA – systems. In the first variant, the source data relevant for the reports is imported completely into Power BI. In the second, the source system is accessed directly; the report data is thus re-extracted at runtime.
In both cases, a so-called dataset is created during report generation and uploaded to the Power BI server.
In the case of the import solution, the underlying data is included in the dataset. In the case of direct data access, on the other hand, it only contains the metadata, such as the connection information, to establish the connection to the respective SAP BW / SAP HANA. The following figure shows the two types of connectivity schematically.
While with the import variant the report requests can be served directly from the dataset, with “direct access” they are forwarded to the underlying SAP system; the report result is thus fed directly with current data.
From an architectural perspective, data provisioning in Power BI, when using direct access (via Direct Query), is based on the following principle:
- Power BI creates a query to the on-premise data source and sends it, with encrypted credentials, to the Gateway Cloud Service.
- The Gateway Cloud Service parses the query and transmits the request to Power BI’s own message management service.
- The Azure Service Bus message management service manages the request queue and ultimately passes the requests to the on-premise Data Gateway.
- The on-premise Data Gateway decrypts the credentials and uses these credentials to connect to the respective SAP data source.
- The data query is executed in the source; the results are then returned to Power BI via the on-premise Data Gateway and Azure Service Bus, where they are used to update reports.
In contrast to the Direct Query Variant, the Import option does not involve a continuous data exchange with the source system. Instead, a snapshot of the most recently uploaded data (previously uploaded to the Power BI server) is accessed in the Dashboards and reports.
However, it is possible to automatically update the imported data by directly accessing the source system at certain time intervals. For this purpose, the dataset is linked to a local Data Gateway that contains the concrete system connection parameters.
Setting up a local Data Gateway
The setup of an on-premise Data Gateway is always required if an automated data exchange is to take place between Power BI and an SAP source system. This applies to both the Direct-Query and the Import variant. With the latter option, it is also possible to manually upload the data to the Power BI server again. However, this method is not recommended for more complex applications (with different source data and high data update requirements).Advantages and disadvantages of the data connectivity variants
In the previous sections, the most important difference between the two data connectivity variants was already discussed – the storage of the data in or outside the Cloud. If the data is stored directly on the Power BI server, Power BI and the underlying VertiPaq engine can show their strengths to the full – especially in the area of performance. The navigation within the Dashboards and reports runs noticeably smoother!
In addition to performance, however, there are a number of other aspects that should be taken into consideration when comparing the two variants. For example, the import variant also has some not insignificant restrictions. The following table provides an overview of the most important aspects that should be weighed up before making a design decision. The table refers to the use of native BW objects (query, InfoCube, etc.).
Conclusion
After a detailed analysis of the two connectivity options, we draw the following conclusion:
While the direct query connection has three major strengths: high data timeliness, local storage of source data, and reusability of BW authorization logics, it has a lot of restrictions in terms of report generation. A major deficit is that only one BW query can be connected per application. In addition, there are many other minor aspects, such as the fact that only texts and not key values can be displayed for characteristics. The report creation options are thus limited in a certain way with this variant. If an overall score had to be given, the features and capabilities would therefore be placed in the midfield.
In contrast, the import variant fully supports many of the features that make up Power BI (data modeling and transformation, multisourcing and fast performance). From a reporting perspective, this is clearly the better variant! However, due to the deficits in terms of data timeliness and the limited reusability of established BW logics (authorizations, time-dependent hierarchies, multilingualism), there can be no top grade here either. In the end, however, the evaluation is always very much dependent on the respective reporting requirements. A thorough analysis of the advantages and disadvantages should therefore always be the prelude to a decision on the introduction of Power BI as a reporting tool.
Outlook
In addition to using BW objects to provide data, it is also possible to directly use the data of the underlying HANA database. An analysis, as well as advantages and disadvantages, can be found in the next blog entry.Arrange now your Expert Call. We are glad to hear from you.