Build bridges: SAP BW meets Microsoft Power BI

Build bridges: SAP BW meets Microsoft Power BI

You’re probably in the same boat as many of our clients facing a challenge: how best to integrate SAP BW (SAP Business Warehouse) as the data source for Microsoft Power BI (Power BI). It’s not always an easy task, since it requires due attention to diverse factors, potential challenges, and possible ways to tune performance.

We have compiled a thorough guide of best practices and limits, underscored by our extensive in-depth experience into integrating Power BI with SAP BW. Now we offer you a brief insight in this blog.

1. Synopsis – Integrating SAP BW into Power BI

This blog focuses on SAP BW as the data source for Power BI. BW’s main advantage is its seamless integration into other SAP systems and the content it delivers, especially as the source for SAP ERP and SAP Analytics Cloud. For many users, Power BI is their introduction to the BI world to visualize, analyze, and distribute company data. For this purpose, SAP BW offers numerous options to deliver data.

The current challenge is to connect both worlds to achieve the best possible results. There are two standard connectors: a) the SAP BW connector and b) the SAP HANA connector for SAP BW on HANA or BW/4HANA. Both these connectors support the import or DirectQuery modus, but each has its pros and cons. The figure below is a simplified illustration of the options.

This blog focuses on using MS Power BI in conjunction with SAP BW via standard connectors for SAP BW and SAP HANA.

2. Use Cases

Power BI offers multiple options to access SAP data sources. Basically, we have two connectors. One is the SAP BW connector that enables traditional access to SAP BW’s application interface, and the other is the SAP HANA connector for direct access to the HANA database. The latter is accessible only if SAP BW is available on HANA or a BW/4HANA system.

To load data, you can select the import or DirectQuery mode for either connector. For practical purposes, the connector and data load modus lead to the following four combinations of use cases:

Import via the SAP BW connector
DirectQuery via the SAP BW connector
Import via the SAP HANA connector
DirectQuery via the SAP HANA connector

Each of these use cases has pros and cons and deployment scenarios that need careful evaluation.

The import mode via the SAP BW connector is particularly useful if you plan to primarily use the SAP BW query functions, while paying attention to specific limitations of the connector. This configuration is recommended only for cases involving low data volumes, because its performance and loading times can worsen significantly with increasing amounts of data.

On the other hand, the DirectQuery mode with SAP BW is especially suited for rapid data checks and ad-hoc analyses. This mode’s advantage is that you can use larger data volumes and apply both the security rules and access limitations defined in SAP BW. However, the data cannot be processed further in Power BI, and a limit of one million lines means a significant loss of function. Moreover, performance is highly dependent on the data source, and noncompliance with hardware requirements and best practices could lead to major setbacks.

3. Limitations

You must be aware of certain limitations when dealing with SAP BW as Power BI’s data source. Performance and processing of large data volumes are key issues that can be challenging when integrating Power BI into the SAP BW landscape. Please note that Power BI does not support all SAP BW functions. Differences in the interface between the two tools quickly create a performance issue, possibly leading to long load times. Also, hefty data volumes can cause errors and breakdowns during loading. Consequently, reports do not function properly and error messages appear.

The general factors and aspects to be considered fall into the following three categories:

Performance (primarily in the DirectQuery mode)
Burden on the underlying system resources
Some or no function support

The main challenges are performance and utilization of hardware resources for processing large data volumes. Expect massive load time delays for mid-six figure or more data sets.

Currency conversion in SAP BW, based on the exchange rates stored in the data model, is one of several examples of functions that are not supported or only partly. SAP BW also allows one to define code scaling (e.g., 1000) and to reverse the number prefix – but since open API does not provide such functions, they are not available in Power BI.

4. Best Practices

For Power BI and SAP BW, we basically need to differentiate between recommended optimization and best practices for each of them. Although there are common recommendations for both, they are treated and addressed separately in different chapters.

In the SAP BW chapter, our recommendations and best practices are geared to the LSA++ (layered scalable architecture) applied in SAP BW. The emphasis here is on the virtual data marts layer (VDM), which contains the biggest adjustments to optimize performance based on the connection to Power BI. The underlying layers of DAL (data acquisition layer), EDW (enterprise data warehouse propagation layer), and ADM (architected data marts layer) are all considered as a unit.

A structured approach from the start is decisive when applying Power BI to SAP BW. Aspects of quality and guaranteed clean modeling and reusability of the data model should be considered – to ensure that data sets and models best fulfil their purpose. Here too, we offer recommendations and orientation guidelines to help our clients consider key factors for modeling and visualization using Power BI with SAP BW.

Improvements to the interface between Power BI and SAP BW are limited. We ask our clients to first run the Power BI QuickCheck with us, in order to note requirements and use cases and to comprehend the overall situation.

As the next step, we offer comprehensive consultations concerning interactions among diverse tools. This advice is specially designed to match the use cases and requirements identified earlier, and to assure ideal support. We are in a position to recommend customized solutions to our clients, thanks to our multifaceted expertise in various technology stacks of hyperscalers and market leaders, including Microsoft, Google, SAP, Databricks, Snowflake, and AWS.

5. The b.telligent Guide

Based on our experiences and knowledge, our experts have compiled a comprehensive guide, starting at the stage described above. The aim is to enable BW consultants, Power BI and key users to get an overview of possible ways to connect Power BI and SAP BW. Our guide focuses on optimizing performance for data exchanges between the systems. Moreover, we illustrate key limits, the optimization potential, and best practices when applying Power BI with SAP BW as the data source. Give us a call, we’d be happy to assist you!

Feel free to call us for more information on our guide, integration, and/or interaction between the MS Power BI and SAP BW. We’ll gladly answer your questions on these tools. Or how about attending our variety of workshops and training sessions to delve deeper into this field!