Unleash the power of your SAP BW Data with Power BI
77% of the world’s transaction revenue touches an SAP system. (source: SAP). SAP has been one of the market leaders in enterprise application software, helping all size companies and in varied industries handling business applications like accounting, sales, payroll, finance, production, human resources, etc.
Having this extensive usage of SAP application, enterprises may witness the need of connecting SAP with cloud and other third-party services. To overcome these hassles, Microsoft has introduced out-of-the-box SAP connectors helping enterprises to leverage data present in the SAP systems and to build innovative business applications and make the most of their SAP investments.
This article will explain you how to unleash your SAP BW data with Power BI
Glimpse of Power BI and SAP BW
Power BI is a collection of software services, applications, and connectors that work together to provide faster time to insight. It is used for visualizing, discovering, and extracting insights out of raw data. Power BI brings together data from various sources to bring rich and comprehensive views of business operations.
Also Read: Benefits of Microsoft Power BI
On the other hand, SAP Business Warehouse (BW) is a widely used model-driven data warehousing solution based on the SAP NetWeaver platform. It extracts, transforms, and stores data generated in SAP and non-SAP source systems and make it accessible through built-in reporting as well as third-party reporting software.
Also Read: SAP BW Reporting for Non-Leading Ledger — Extraction and Usage
SAP BW comes with addon tools — the Business Explorer — for enterprise reporting, ad- hoc analysis, and dashboarding. With the BEx tools, business users can design, create, analyze, and distribute data for analyses and decision-making support. Additionally, SAP has several BI reporting and analysis tools under the BusinessObjects (BO) platform which offers pixel-perfect reporting, semantic models, browser-based user experiences, and dashboarding. The BusinessObjects tools can work on top of BEx Queries and can also be used with non-SAP data sources.
Additionally, SAP BEx Queries can be utilized in third party reporting solution as well. There are two ways to connect SAP BW, using Info Providers or BEx queries using Power BI. Using Import function, we can import a copy of the data into Power BI Desktop or using Direct Query option we can directly connect to the data using BEx Queries to Power BI to create the reports and dashboards from the underlying data.
Pre-requisites:
· Install SAP BW Connector — Implementation 2.0
- Implementation 2.0 of the SAP Connector requires the SAP .NET Connector 3.0. One can download the SAP .NET Connector 3.0 from SAP’s website.
- Refer SAP Note# 1025361 for the SAP NetWeaver RFC Library to install the NW RFC library.
Note — It requires valid S-user ID of SAP marketplace to download the connector. The connector comes in 32-bit and 64-bit versions, and users must select the version that is compatible with their Power BI Desktop installation.
Power BI Connection Types for SAP Business Warehouse:
Power BI Desktop offers two connection types for SAP Business Warehouse — Application Server connections and Message Server connections.
· Application Server — Power BI can be connected with specified SAP BW server
(hostname or IP address) from your SAP Landscape. This type of connection requires sufficient resources available at any point of time to support the connection and process its workload, simultaneously for all other connections or work processes that may exist in the environment.
· Message Server — Power BI can be connected to 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 during query execution time and based on this information Power BI will make a connection to the most favorable BW server.
Steps to connect Power BI with SAP BW:
1. Launch Power BI Desktop.
2. From Home tab, select Get Data and go to More. Filter on Database connectors on the left, then select SAP Business Warehouse Application Server from the list and connect.
3. In the SAP Business Warehouse Application Server connection dialog screen, enter the following details and click ok to proceed further.
Server: The application server (hostname or IP address) of the SAP BW system.
System number: The system number, or instance number of the SAP BW server.
Client ID: The specific client of your SAP BW system that you want to connect to.
Import: Using this option, you can import data into Power BI. If any changes are made in the underlying data, it will not reflect automatically in any visualizations. Once we refresh all the tables then only it will get the latest data in power bi visualization.
Direct Query: With this option, you can directly connect SAP BEx query with Power BI. Data will not be imported into Power BI. The queries will be sent to the underlying data source which will retrieve the relevant data from backend. Execution time of the query will depend on the performance of the underlying data source.
Implementation: 2.0 is the default option. Our recommendation is to use Implementation 2.0.
Advance Options: There are several advanced options like MDX, Batch Size, Execution Mode and Language code in same dialog screen.
Language Code You can optionally specify a language code when establishing a connection to the SAP BW server.
The Execution mode option specifies the MDX interface is used to execute queries on the server. The following options are valid:
BasXml: Specifies the bXML flattening mode option for MDX execution in SAP Business Warehouse.
BasXmlGzip: Specifies the Gzip compressed bXML flattening mode option for MDX execution in SAP Business Warehouse. This option is recommended for low latency or high-volume queries. The default value for the execution mode option.
DataStream: Specifies the DataStream flattening mode option for MDX execution in SAP Business Warehouse.
Batch Size Specifies the maximum number of rows to retrieve at a time when executing an MDX statement.
Multi-Dimensional Expressions (MDX) is BI query language developed by Microsoft Inc. It is used to query Multi-dimensional models (cubes) and provides multidimensional result datasets that contains the data available in the underlying info-provider.
4. Enter SAP BW credentials and click on Connect.
Next pop up screen will show the list of available cubes and queries. This will allow you to connect to backend data source that is available on the SAP Business Warehouse system.
Since we have connected SAP BW system now, let’s see how we can connect SAP BEx Queries with Power BI using direct query option.
SAP BEx Queries
The BEx Query Designer is the key tool for designing queries and creating datasets from backend
Info-Provider for analysis. With the Query Designer, you can limit the number of fields that are displayed on a report. It also allows the user to define filters, variables, calculations, and more, that are otherwise not available in the Info-Provider.
· To connect third party tools like Power BI, we first need to enable “Allow External Access to this Query” property from SAP BEx designer for the query you want to expose to the Power BI.
Third Party Tool Integration Architecture
The integration between Power BI Desktop and SAP BW is based on the OLAP BAPIs (for Business Application Programming Interfaces).
The OLAP BAPIs are delivered with SAP BW and provide 3rd-party tools with standardized interfaces that allows them to access the data from SAP BW with front-end tools.
The OLAP BAPIs are implemented in SAP BW as RFC-enabled function modules and used by Power BI Desktop using these RFCs. This requires the NetWeaver RFC Library or SAP .NET Connector to be installed on Power BI Desktop machine (please refer pre-requisite as mentioned earlier in the blog)
The OLAP BAPIs also comes with methods for browsing metadata and master data, as well as for 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, which are further described below.
On the other hand, the SAP Business Explorer tools benefit from a more performant interface to the SAP BW OLAP Processor. The Business Intelligence Consumer Services, or BICS interface, is the suggested way for the SAP BI tools to connect to the underlying data sources. BICS is not available for 3rd party tools.
Steps to consume SAP BEx Queries in Power BI
· Connect to SAP BW system using the connection which we created earlier in the blog and it will open following navigation window,
· The Navigator will display a hierarchical view of objects from the connected SAP BW system. The types of objects are listed here:
- Catalog — either INFOCUBE or an Info Provider
- Info Cube or a BEx Query
- Key figure
- Characteristic
- Characteristic Level
- Property (Attribute)
- Hierarchy
· By expanding the particular cube, we can choose required key figures /measures and characteristics/dimensions. You can collapse the data provider just by clicking on the same. Objects below an Info-Cube or BEx Query node, such as the key figures, characteristics, and properties, are only shown in Import connectivity mode, not in Direct Query mode.
Performance Recommendation
Filters based on parameter values get processed in the SAP BW data source, not in Power BI. This will have performance advantages when refreshing SAP BW data into Power BI for huge datasets.
To reduce the number of columns, it is advisable to select the key figures, characteristics, and properties that you eventually want to see in your report or dashboard.
Likewise, to reduce the number of rows, we can use the existing parameters on the query to narrow the dataset, or to split up a larger dataset into smaller ones, which can be joined together in the Power BI Desktop data model.
Concluding Note
SAP BI/BW is a key data warehousing solution built on top of enterprise data from multiple source systems, and Power BI connectivity with SAP BW systems is a desired solution for enterprise customers who want to unlock insights from their most critical business processes with rich visualizations.
Most of the SAP customers must have deployed SAP BW systems and already invested in data warehousing solution to integrate their transactional SAP data (from SAP ECC, HANA, CRM, SRM, or other source systems) with SAP BW.
Raw and unstructured enterprise data has been cleansed, transformed, and modeled to be used in analytics and planning applications. Further It makes sense that customers want to utilize what they have already invested in and connect Power BI to their underlying data in SAP BW to get the insights.
