SAP BW/4HANA Connectivity Options in Microsoft Power BI

[nvytla]

Introduction

SAP BW∕4HANA is a data warehouse solution which is highly optimized for the SAP HANA platform. It does not include front-end solutions anymore, as was the case with SAP BW 7.x (BEx front ends). Please refer my previous blog - SAP BW/4HANA – Data Warehouse with Three Approach Strategy for Business Intelligence Reporting and A.... In this blog, will focus on Microsoft Power BI as front end tool.

 

Microsoft Power BI

Microsoft Power BI is a business analytics service that gives you insights using live dashboards, create rich interactive reports and access your data on the go, from your mobile devices. Main components of Power Bi are displayed in below picture.

• The Power BI service provides access for business users to interact with their data. You can build dashboards that provide a single consolidated view of business. Business users can enjoy the benefits of up-to-date data with real-time, automatic and scheduled refreshes.


• Power BI Desktop enables you to acquire and prepare data using its built-in query capability. It can connect to SAP BW/4 or SAP HANA environments. Moreover, you can connect to your data sources in two different ways: you can import a copy of the data into Power BI Desktop, or you can connect directly to the data in its original source repository, which is known as DirectQuery and is further outlined below.


• Power BI gateway acts as a bridge, providing quick and secure data transfer between SAP data, and the Power BI service. With the data gateway, you can keep your dashboards, reports and data sets refreshed, on an ad-hoc basis or a scheduled interval.

 

SAP BW/4HANA Connectivity Options in Power BI

Two built-in SAP connectors available in Microsoft Power BI (Sep 2018)

• SAP HANA Database Connector


• SAP Business Warehouse Application/Message Server Connector

 

SAP HANA Database Connector

The Power BI connector for SAP HANA uses the SAP ODBC driver. It supports both DirectQuery and Import options. Power BI supports HANA information models (such as Analytic and Calculation views) and has optimized navigation. It also supports SAP HANA Variables and Input parameters.

SAP HANA in DirectQuery mode is supported in Power BI Desktop and the Power BI service. There are two options when connecting to SAP HANA in DirectQuery mode:

• Treat SAP HANA as a multi-dimensional source (default): In this case, the behavior will be similar to when Power BI connects to other multi-dimensional sources like SAP Business Warehouse, or Analysis Services. When connecting to SAP HANA using this setting, a single analytic or calculation view is selected, and all the measures, hierarchies and attributes of that view will be available in the field list. As visuals are created, the aggregate data will always be retrieved from SAP HANA. It's not possible to change the aggregation for a column on a visual, and it is always Do Not Summarize. It's not possible to add calculated columns, or to combine data from multiple SAP HANA views within the same report. Treating SAP HANA as a multi-dimensional source does not offer the greater flexibility provided by the alternative relational approach, but it is simpler, and ensures correct aggregate values when dealing with more complex SAP HANA measures, and generally results in higher performance. This is the recommended approach, and is the default for new DirectQuery reports over SAP HANA.


• Treat SAP HANA as a relational source: In this case, Power BI treats SAP HANA as a relational source. This offers greater flexibility, but care must be taken to ensure that measures are aggregated as expected, and to avoid performance issues. For example, you can create calculated columns, include data from multiple SAP HANA views, and create relationships between the resulting tables. the query defined in Get Data or Query Editor will determine the data available, and then any subsequent aggregation in a visual is over that data. Attention must be paid to any further aggregation performed in visuals, whenever the measure in SAP HANA is non-additive (for example, not a simple Sum, Min, or Max). In Get Data or Query Editor, only the required columns should be included to retrieve the necessary data, reflecting the fact that the result will be a query, that must be a reasonable query that can be sent to SAP HANA. For example, if dozens of columns were selected, with the thought that they might be needed on subsequent visuals, then even for DirectQuery a simple visual will mean the aggregate query used in the sub-select will contain those dozens of columns, which will generally perform very poorly.

Please note two approaches (multi-dimensional and relational source) constitute very different behavior, and it's not possible to switch an existing report from one approach to the other.

Similar to DirectQuery mode, SAP HANA in Import mode is supported in Power BI Desktop and the Power BI service. Above two options with limitations are available when connecting to SAP HANA in Import mode. If the data is being imported into Power BI (versus using DirectQuery), the following would result:

• The data is imported at the level of aggregation defined by the query created in Get Data or Query Editor. The set of tables selected each define a query that will return a set of data (those queries can be edited prior to loading the data, for example to apply filters, or aggregate the data, or join different tables).


• Upon load, all of the data defined by those queries will be imported into the Power BI cache. Any changes to the underlying data will not be reflected in any visuals. It is necessary to Refresh (using Power BI gateway), whereupon the data will be re-imported.

DirectQuery is recommended when Data is changing frequently, and near ‘real-time’ reporting is needed. Handling very large data, without the need to pre-aggregate.

 

SAP Business Warehouse Application/Message Server Connector

From a technical point of view, the integration between Power BI Desktop and SAP BW/4 is based on the so-called OLAP BAPIs (for Business Application Programming Interfaces). The OLAP BAPIs are delivered with SAP BW/4 and provide 3rd-parties and developers with standardized interfaces that enables them to access the data and metadata of SAP BW/4 with their own front-end tools. The OLAP BAPIs provide Power BI Desktop with access to InfoProviders (including HANA views) and BW Queries.

Power BI Desktop offers two connection types for SAP BW/4 – Application Server connections and Message Server connections. When you create a connection from Power BI Desktop to an SAP BW/4 server, the logon parameters that you need to provide are very similar to when an SAP user is logging on to SAP BW/4 via the SAPGUI client tool.

• With the connection type Application Server, Power BI will always connect to the specified SAP BW/4 server (host name or IP address) in your SAP landscape. This requires the server to have sufficient resources available at any time to support the connection and process its work load, concurrently to all other connections or work processes that may exist at the same time.


• With the connection type Message Server, Power BI will query a message server that has been configured in your SAP system landscape. The message server will check which BW application server has the best load statistics or the fewest users at that time, and Power BI will make a connection to the most favorable server based on that information. In addition to the message server, your SAP Administrator has configured a Logon Group which comprises two or more application servers that will participate in logon load balancing.

SAP Business Warehouse Application/Message Server Connector can either import data into Power BI, or you can connect directly to data in the source repository, which is known as DirectQuery.

For SAP BW connections in import mode, you can select an InfoProvider (including HANA views) or BW query, expand its key figures and dimensions and select specific key figures, characteristics, attributes (properties) or hierarchies to be included in your query. The selection defines a query that will return a flattened data set consisting of columns and rows. The selected characteristics levels, properties and key figures will be represented in the data set as columns. The key figures are aggregated according to the selected characteristics and their levels. You can edit these queries in Power BI Desktop prior to loading the data, for example to apply filters, or aggregate the data, or join different tables. When the data defined by the queries is loaded, it will be imported into the Power BI in-memory cache. As you start creating your visuals in Power BI Desktop, the imported data in the cache will be queried. The querying of cached data is very fast and changes to the visuals will be reflected immediately.

You can also split a larger SAP BW/4 data set into smaller data-sets and join them together in Power BI Desktop. Also you can combine bring together data from different SAP sources. This opens many interesting scenarios for reporting and analytics on top of your SAP BW/4 data.

The navigation experience is slightly different when connecting to an SAP BW source in DirectQuery mode. The Navigator will still display a list of available InfoProviders and BW queries in SAP BW/4, however no Power BI query is defined in the process. You will simply select the source object itself, i.e. the InfoProvider or BW query, and see the field list with the characteristics and key figures once you connect. For BW queries with variables, you will be able to enter or select values as parameters of the query. Select the Apply button, to include the specified Parameters in the query.

When you select a BW query or HANA view with characteristic restrictions, you will see the variables displayed as Parameters. Filters based on parameter values get processed in the SAP BW data source or HANA database, not in Power BI. This can have performance advantages when loading or refreshing data into Power BI, in particular for larger data-sets.

Recommendations

You should import data to Power BI whenever possible. This takes advantage of the high-performance query engine of Power BI, and provides a highly interactive and fully featured experience over your data.

However, DirectQuery provides the following advantages when connecting to SAP BW/4HANA:

• Ensures that the latest data can easily be seen, even if it is changing frequently in the underlying SAP BW/4HANA source.


• Ensures that complex measures can easily be handled, where the source SAP BW or HANA is always queried for the aggregate data, with no risk of unintended and misleading aggregates over imported caches of the data.


• Provides the ability to access SAP BW data using SSO, to ensure that security defined in the underlying SAP BW source is always applied. When accessing SAP BW using SSO, the user’s data access permissions in SAP will apply, which may produce different results for different users. Data that a user is not authorized to view will be trimmed by SAP BW.


• Avoids caches of data being extracted and published, that might violate data sovereignty or security policies that apply.

Using DirectQuery with Relational Source is highly flexible and generally only feasible when the underlying data source can provide interactive queries for the typical aggregate query within seconds, and is able to handle the query load that will be generated.

In addition, many of the general best practices described in Using DirectQuery in Power BI apply equally when using DirectQuery over SAP BW/4HANA. Additional details specific to SAP BW or HANA connectors are described in https://docs.microsoft.com/en-us/power-bi/desktop-directquery-sap-bw and https://docs.microsoft.com/en-us/power-bi/desktop-directquery-sap-hana