Exploring the Visualisation of SAP Data using Microsoft Power BI

According to Gartner’s 2019 Worldwide Report on Market Share¹, SAP is a global leader in ERP platforms through S/4HANA and BW/4HANA and in the analytics space it is in the Visionaries quadrant thanks to SAP Analytics Cloud (SAC). All of these tools are subject to continuous updates with improved functionality appearing at regular intervals.

However, many of our customers now have an enterprise landscape comprising a mixed architectural environment, in which they utilise SAP, Microsoft (Azure), Amazon and various other providers and platforms across their business functions. When it comes to analytics, many of them are looking for a single platform for data visualisation and reporting across their company, but they also have many questions about how to integrate this with data across multiple disparate platforms.

Gartner’s 2020 BI Magic Quadrant² – puts Microsoft Power BI as the market leader amongst all available analytics suites. It has excellent customer satisfaction ratings and strong interconnectivity with Microsoft Office 365.

Many BI tools have a comparable set of features and functionalities, but the main questions for the enterprise are around integration. We’ll focus on data connectivity, connection modes and security features between Power BI and SAP.

At a basic level, Power BI Desktop is a desktop client, downloaded by report developers, and is used for data connectivity, transformation, visualisation & report creation. Once built, reports are published to the cloud-based Power BI Service for consumption by the business, with the ability to create interactive dashboard-style reports and self-service analytics. The Power BI On-Premise Data Gateway (OPDG) is installed to provide connectivity between the data warehouse and the Power BI Service.

¹ https://www.gartner.com/en/documents/3985627/market-share-analysis-erp-software-worldwide-2019

² https://info.microsoft.com/ww-landing-2020-gartner-magic-quadrant-for-analytics-and-business-intelligence.html?LCID=EN-US

Power BI currently has two types of connectors to provide SAP BW connectivity: the SAP Business Warehouse Application server and the SAP Business Warehouse Message server. The Application server is a direct connection to BW, whilst the Message server provides a load balancing facility.

There are two connectivity modes available in Power BI to connect to SAP BW. The Import mode will take an instance of data, upload it and store it in the Power BI document and cloud, if uploaded. It will take up user storage and can be used from the Power BI Service without refresh (although data can be refreshed with a re-upload). Direct Query mode is a live connection that will use Remote Function Calls (RFC) to the database for each data refresh and change. It does not store a physical dataset with the document.

The Import Mode allows data import from multiple sources, which can be blended, and provides a wide range of DAX functions – DAX being the language of the Power BI suite – as well as a high-performance query engine to process it. However, there is a data limit of 1gb and row-level security, if required, needs to be done at the Power BI level.

Direct Query retrieves data from a single source, such as a BW Query or Composite Provider, but this method has limited data transformation and DAX capability. Modelling should be pushed down to the data warehouse to take advantage of OLAP functionality. Security is inherited from the data warehouse analysis authorisations and individual users can be mapped between BW and Power BI.

Power BI has one dedicated connector to the SAP HANA database, which supports both Import and Direct Query modes. Alternatively, a SQL Statement can also be written to query SAP HANA. Attribute, Analytical, Calculation and CDS views are all exposed to Power BI. This connector does not connect to S4/HANA or ECC tables.

There are two options for connecting to SAP HANA in Direct Query mode. The Multi-dimensional model is similar to connecting to BW, where only one Calculation View can be selected. It also supports a parent-child hierarchy. Aggregations of non-cumulative measures are performed by SAP HANA, not by Power BI.

The Relational model connects to multiple views and relationships and blends can be created in Power BI. It offers greater flexibility, allowing you to create calculated columns, DAX formulas, new aggregations etc. It does not, however, support a parent-child hierarchy.

Data security is always a concern. Let’s explore the data governance approach in Power BI. There are several levels where restrictions can be applied. The HANA/BW database is the first level of restriction, where data is retrieved based on the user’s credentials in BW/HANA. This happens through analytical privileges or BW authorisation objects. In the second level of restriction in Power BI, the dataset is pulled based on direct pull or import of calculation views based on database credentials. The third level of restriction in Power BI service is row level security (RLS) at the PBI level. The data administrator will define roles and allocate workspace level access such as Admin, Contributor, Member and Viewer based on required access to data and reporting. Authorisations based on a Hierarchy do not work in Power BI for SAP HANA and BW.

Single-Sign On (SSO) can be implemented in Power BI for SAP HANA & BW. When users execute Power BI queries, the their security role level credentials are used to get the necessary data. There are two types of SSO implementation in Power BI for SAP databases: Kerberos is the recommended approach for SAP BW, whilst SAP HANA supports Kerberos and Security Assertion Markup Language (SAML).

SSO works based on User Principal Name (UPN). The Power BI service passes the UPN string for each report query/refresh triggered by a Power BI Azure Active Directory (AD) user. The UPN string is mapped to an Azure AD user account when Azure AD Dirsync is configured.

SAP Analytics Cloud (SAC) is the preferred front-end product to connect to SAP data, with SAP BusinessObjects (BO) providing on-premise reporting. If you are using Power BI with live data from SAP, then be sure to take care in choosing which connection mode to use, based on your data, modelling and security requirements. Hopefully this blog has given you a good grounding in the ways that Power BI can connect to SAP data and the issues that require discussion in an implementation. To get the benefit of even more of our experience, do get in touch and we can help you to make the most of your Power BI platform on SAP.

Amlan Datta

Senior Solution Arc

Amlan Atta is a Senior Solution Architect in SAP BI in the UK I&D team. With 15+ years of SAP consulting experience in SAP BW on HANA, Native HANA, SAP Business Object, and ABAP in various implementation and support projects in Pharma, FMCG, manufacturing, retail, banking, financial services sectors.