Microsoft Power BI on SAP Data Sources
Power BI (PBI) is a cloud based analytics service that helps develop quick insights into data and helps businesses take data driven decisions. Power BI consists of 3 main elements that help create, publish, consume and share reports/dashboards effectively:
- Power BI Desktop – The primary IDE, an intuitive and easy to use visual data exploration and reporting tool for business analysts.
- Power BI Service – cloud-based, online service where Power BI content can be published (through on-premise PBI Gateway) and consumed. PBI service can also be used for report editing (lighter than PBI desktop; limited in data sources and modelling) and collaboration for teams and corporations.
- Power BI Mobile – A native, interactive mobile app for business users to visualize and analyse reports on the go (available for iOS, Android and Windows mobile devices)
Additionally, PBI report server is an on-premise solution where PBI desktop reports can be published (report server content can easily be moved to the cloud if required).
Power BI on SAP Data sources
In the last few years, the number of data sources have increased exponentially driving the need for the BI solutions to be able to integrate with a wide variety of data sources. SAP BW is one of the key Enterprise data sources used by several customers. SAP BW contains data flows that integrate both SAP transactional data (coming for example from an ERP system) and other data sources in a cleansed, transformed and modelled form that is ready for reporting needs of an organization. PBI supports connecting to several SAP data sources; SAP HANA calculation views, SAP BW (cubes, Bex queries), SAP BPC cubes, SAP BW4/HANA and SAP Business warehouse message service.
There are 2 connection modes possible when connecting from PBI to the on-premise SAP systems, namely, Direct Query (connection maintained to get data) and Import Connection (imports data locally). Both the connection types are possible when connecting to SAP data sources and come with their own pros and cons. In general, when using an import connection, greater flexibility is retained on the PBI desktop. When using a direct query connection, several modeling restrictions (example: query editor; single data source) are imposed in PBI desktop.
PBI can also connect to SAP HANA calculation views in both import and direct query mode. When connecting to a calculation view using a direct query connection, additionally it is possible to connect in a relational source mode or multi dimensional mode. PBI however, does not currently support direct access to HANA tables (catalog folder).
Once the reports/dashboards are built on Power BI Desktop, they can be published to PBI server. The connection to BW data source from PBI server can be maintained using the on-premise data gateway. The Gateway acts as a bridge for secure data transfer between the BW/HANA server and PBI. The data gateway supports both import and direct query connection and in case of import connection, the data refresh can also be scheduled.
Power BI supports SSO connectivity for both BW (Kerberos) and HANA databases (Kerberos and SAML). When a Power BI user interacts with a report (built on direct query) in the PBI Service, one or more queries are generated and executed live on the on-premise system. With SSO configured, these queries take into account the executing user credentials. Hence, Users see the data which they are entitled to (permissions on the underlying data source).
In conclusion, Power BI is an easy to use, relatively lower cost BI tool that can easily be integrated with on-premise SAP Data sources; mainly, BW (cubes, queries) and HANA (calculation views).
Contact us for more information on PBI on SAP Data sources.