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

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
}

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.