Architecture Considerations when Connecting Power BI to S/4HANA with CDS views and OData
On my analytics journeys I've been involved in many discussions involving S/4HANA and Power BI.
SAP has introduced a powerful concept of ABAP Core Data Services aka CDS Views in S/4HANA where you can leverage the power of the virtual data model to rapidly build or reuse pre-existing analytical queries from database tables. Power BI has made strides in the past few years building a tool that is easily accessible and achieving mass support adoption and adoption from it's passionate user base.
So it might seem like a match made in heaven, Power BI and S/4HANA to deliver some cutting edge analytics... but not so fast! Based on my own experiences here are some perspectives I've developed that will make you reconsider this approach.
🔌 Power BI Connectivity Options to S/4HANA
There are two known methods to connect Power BI to S/4HANA:
- Generated OData service from the CDS Consumption View
- Access the Transient Provider Cube generated by the CDS Composite View
🌐 Access the OData Service generated from your CDS Consumption View
With your CDS views you use the annotation to generate an OData service for Power BI to connect to:
@OData.publish: trueFor more information I recommend checking blogs.sap.com for up to date content on this annotation.
Once you save and activate your CDS consumption view, the OData service can be activated in transaction code /n/IWFND/MAINT_SERVICE. After this step is complete you can start using Power BI to connect to the OData service. For more information check out Microsoft's documentation on the OData connector: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-odata
🧊 Access the Transient Provider Cube generated by a CDS Composite View
When activating the composite CDS View, it creates a transient provider with the naming convention 2C<SQLViewName>. Within Power BI you can actually use the BW connector and through some persistence, actually connect to the transient provider that is generated.
The problem here is you're using the BW connector in a way that it was not intended. The BW connector is designed to work with SAP BW and using it via this unofficial method to S/4HANA leaves you in a position where Microsoft and SAP will not provide you support. Also in a production scenario you run the risk of this connector being changed without notice potentially breaking your report. Hence I do not recommend this approach and it should not be considered at all for these reasons.
🏛 Architectural Considerations when Setting Up Power BI with an S/4HANA
Regardless if you use either of the two options I mentioned above, you'll be faced with the following architectural challenges across the following areas:
- Power BI Gateway and Instratructure setup
- OData Authentication Limitations
- Business Insights are not Real-Time
- CDS Query Data Volumes
🚪 Power BI Gateway and Infrastructure setup
If you want to publish reports to app.powerbi.com Power BI cloud portal, you'll need to setup a Power BI Gateway to connect S/4HANA to the Power BI Cloud. Sometimes depending on corporate infrastructure, this will require coordination between different teams to setup the gateway, modify firewall rules and even SSO certificate generation depending on your data source. The coordination efforts is pretty significant and I'll make a separate blog post about this.
🔐 OData Authentication Limitations
Power BI only supports Basic Authentication with OData and if your S/4HANA system has a security protocol like OAuth2.0, you need to write a custom connector to make this work. Taking this approach can make your architecture immediately more complex than necessary especially when there are simpler options available.
📉 Business Insights are not Real-Time
If you have real-time data requirements with S/4HANA and Power BI, the connectors using DirectQuery and Import have a large number of pros and cons to consider. If you require the most up to date reporting and dashboarding you should consider an S/4HANA Embedded Analytics approach. Reports/Dashboard with large data volumes could possibly be very slow and provide a poor customer experience as well.
📈 Even with CDS Views, be mindful with data query volumes
When designing queries, you have to give careful consideration to retrieving only the fields and rows of data you need. It's essential to be mindful even with CDS of the data volumes you are pulling. For example large queries on tables that contain huge volumes of data in production like ACDOCA can cause CDS views to fail.
💰 License costs for Power BI and SAP users
Cost is associated with both Power BI and SAP usage. Be mindful that if you must have Power BI you'll need to factor in total cost of ownership for both products in your landscape which can increase costs quickly. Keeping your analysis within SAP S/4HANA Embedded Analytics is more cost effective and also more upgrade friendly in the long run.
✅ Recommendation: Consider S/4HANA Embedded Analytics
S/4HANA Embedded Analytics is an incredibly powerful suite of analytics tools that come right out of the box. Rather than trying to build a Power BI dashboard or report, explore the tools that include Multidimensional reports, Smart Business KPI's, Overview Pages and embedded SAP Analytics Cloud. SAP recently introduced a neat training course where you can learn the entire ins and outs of S/4HANA Embedded Analytics with this great course https://open.sap.com/courses/s4h27/
✅ Recommendation: Access S/4HANA data through an intermediate Data Warehouse or Analytical Appliance using supported connectors
Not all hope is lost! Power BI definitely can be used with S/4HANA but with a slight catch. You should go through an intermediate Data Warehouse or Analytical Appliance using supported connectors.
For example if you have S/4HANA sending data to the following SAP systems you can use the supported connectors that are available:
- Enterprise HANA has an official Power BI connector
- SAP BW has an official Power BI connector
Microsoft and SAP have provided and continue to provide robust support platforms with plenty of solid documentation. Recently Microsoft put together a really nice training video to help get users started quickly here: https://docs.microsoft.com/en-us/events/sap-on-azure-training-videos/power-bi-to-sap-connectivity
🙋🏻♂️ I might need additional help!
If your organization needs a helping hand on analytics architecture with Power BI and S/4HANA, please feel free to reach out to me at ben@ben-kwong.com