This blog gives 6 key areas to focus on when using the SAP BW and SAP HANA Connectors in Power BI. These tips and best practices can greatly improve troubleshooting and performance when building Power BI Dashboards based on SAP environments.
SAP BW Connector Overview
The SAP BW Connector for Power BI supports BW version 7.x and above. It allows Power BI to access SAP BW data sources. We do have options to connect to both the Application Server and the Message Server and the Connector works seamlessly for Import and Direct Query modes. We recommend the Import Query mode when dashboard performance is critical.
Version 2.0 and above of the BW Connector supports retrieval of millions of rows of data along with much-improved exception handling.
SAP HANA Connector Overview
The SAP HANA Connector helps us access SAP HANA data sources for Power BI report generation. The recommended approach is to use the Multidimensional connector while working with SAP HANA data sources. The SAP HANA Connector supports both Import and Direct Query Modes. Other available features relating to the Connector include SSL, multiple HANA input variables and SAML based Single Sign-On (SSO).
Tips and Best Practices
1. Query Folding
- The idea behind this best practice approach is to push data processing to the data source server while working with a Power BI Report. The Query Folding option facilitates data processing on the SAP HANA server.
- However, the addition of a custom column moves the data processing from the server side to Power BI. This can be seen in the screenshots below:
Query folding option is present when fetching the data as-is from source:
The addition of a custom column will convert from Query folding to processing the query within Power BI.
2. Column Selection
- Select only the columns required for reporting.
- Always avoid pulling in the complete column list, which will hamper performance.
- Create a star schema using Import mode to facilitate ease of reporting.
- Create a flat view or table with only the columns that are required for reporting.
- Reduce the number of Joins in the underlying data source.
Star schema example:
3. Variables in Queries
- Restricting the volume of data coming into the report will enable good performance. We can do this by using variables in the backend source queries used by the report.
- Variables defined in queries are available to edit only in Power BI desktop for now and not in the Power BI service.
4. Handling Memory Issues
- To handle out of memory issues, we could fine tune the Batch size setting, in version 2.0 of the BW connector.
- In version 1.0 of the BW connector, we might encounter RFC errors while handling large datasets. The solution here, is to partition datasets into smaller chunks such as weekly, monthly or yearly.
- It is important to ensure we reduce the amount of stress on the SAP Server.
5. Advanced Tracing Features
- We can trace MDX statements generated by BW, by enabling environment variables. This enhances logging by including the MDX statements, which will be useful for debugging. This can be enabled or disabled as needed.
- MDX statements generated can be analyzed in the SAP GUI by using MDXTEST transaction code.
- Environment variable PBI_EnableSapBwTracing should be set to True.
- CPIC_TRACE environment variable should be set to 3 for CPIC tracing (debug authentication issues).
6. SQL Profiler for Tracing
- When using the HANA Connector, the SQL Profiler can help trace query statements.
- We could make use of Events like Query Begin, Query End, Direct Query Begin and Direct Query End, to write statements into trace files.
- Connect SQL Profiler to Power BI Desktop to get required query traces for analysis.
- Deep-dive analysis can help understand the query path flow and performance of the query statements.
If you would like our team to walk you through the detailed findings, you can reach out to us here.