Power BI import from S4HANA Analytical Query CDS view - is there anyone who is using it?

[BartVW]

Hi all,

We would like to import data into Power BI from a SAP S4HANA system that exposes data through CDS views. We have it working with OData, but would like to see if it works better if we get data directly from the underlying CDS consumption view (using Analytics.query: true annotation). 

We are able to connect to the CDS view using the SAP BW connector, but the filter variables defined are not being interpreted correctly.  E.g. for a date field it is defined like this:

@Consumption.filter: {
selectionType: #INTERVAL,
multipleSelections: true,
mandatory: false
}

And this works fine in a SAP tool like Analysis for Office, but in Power BI it shows as single date selections (allowing selection of multiple single dates).

When I try to put the filter on the dates in Power Query in a subsequent step, it does fold but the MDX seems suboptimal (throwing 'too much data' errors while only pulling in 200 rows.

So I am curious to see if there is anyone out there using this succesfully, to get some learnings/best practices if any.

Many thanks for reading

Bart

[SacheeTh]

Hi @BartVW,
There are some best pactices I have seen few years back when our teams used the SAP connector, I'll share few here hope this may help you, try these & pls let me know if this helped.
Best Practices and Solutions for connecting SAP CDS

1. Optimizing the CDS View Design 

• Adjust Annotations: Ensure the @Consumption.filter annotations in the CDS view align with Power BI's handling: (The @Consumption.filter annotation in SAP CDS views specifies how the field is filtered in tools like Analysis for Office or Power BI. By default, Power BI may interpret it differently, so fine-tuning is necessary.)

  • Use @Consumption.defaultFilterValue to pre-set default filter values for dates.
  • For intervals, you might need to experiment with:

@Consumption.filter: {
    selectionType: #INTERVAL,
    multipleSelections: true,
    mandatory: false
}

@Consumption.filter: { selectionType: #SINGLE, mandatory: false }

While this compromises on the interval functionality, it makes it easier to implement in Power BI.

• Consider Aggregation: Use aggregation in the CDS view to limit the granularity of data pulled into Power BI.

---

Few best pactices found on the documentation, pasting a summary of it. may help you.

Leveraging the SAP BW Connector

• Use Parameters in Power BI:
  • Define parameters in Power BI for date ranges or other filters.
  • Use the parameters in the SAP BW Connector dialog to limit data at the source.
• MDX Optimization:
  • Ensure the CDS view has been properly exposed as an OLAP source (analytics.query: true).
  • If you experience suboptimal MDX queries, work with your SAP Basis team to test and optimize the query performance at the CDS level.

---

Alternative Connectors

• SAP HANA Database Connector:

  • If possible, connect directly to the SAP HANA database tables or calculation views instead of CDS views. These tend to perform better and allow more flexible query folding in Power BI.

• SAP OData Connector:

  • Use OData only if absolutely necessary. While it works, it is less performant and more prone to data size limitations compared to other connectors.

---

Filtering Data in Power Query

• Apply filters early in Power Query to ensure the data pulled is minimal. Use Transform > Keep Rows or Filter Rows steps to pre-limit the data.

---

 Reducing the Data Volume

• Subset Data by Partitioning:
  • Divide the data at the source into manageable chunks by year, region, or another field.
  • Create separate CDS views or use input parameters to reduce the amount of data queried.

---

Troubleshooting "Too Much Data" Errors

• Increase the memory or row limits in the SAP gateway settings (with help from your SAP Basis team).
• Verify the S/4HANA system settings for maximum rows returned via MDX queries.
• Consider narrowing the dataset at the source by refining the CDS view or introducing filters.

---

7. Testing in a Staging Area

• If direct connections remain problematic, consider extracting data from the CDS view into a staging database or file system, then import the data into Power BI.

[BartVW]

Thank you for your input @SacheeTh . I will continue to try out a few of these things, but in short term revert back to OData as this works ok except for some time outs I need to investigate with your basis team.

I was hoping someone would know from experience that there is a way for the @Consumption.filter to integrate correctly with Power BI, but so far from my research I have not seen a case where it was.