Forum Discussion

BartVW's avatar
BartVW
Icon for Helper I rankHelper I
1 year ago

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

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

 

 

 

 

Need Help

3 Replies

  • SacheeTh's avatar
    SacheeTh
    Icon for Resolver II rankResolver II
    1 year ago

    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's avatar
      BartVW
      Icon for Helper I rankHelper I
      1 year ago

      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.

  • metrica's avatar
    metrica
    Icon for Post Prodigy rankPost Prodigy
    2 months ago

    Hi BartVW,

     

    Based on what you described, returning to OData seems reasonable, since the SAP BW connector is not handling the interval variable as expected and the generated MDX is causing data volume errors.

     

    You may also want to evaluate Power BI Connector for SAP, available on SAP Store: https://www.sap.com/products/data-cloud/partners/metrica-software-inc-power-bi-connector-for-sap.html

    It provides an OData-based connection to Power BI and supports SAP-side filtering. It can also work with parametrized CDS and OData entities when the parameters are exposed in the service metadata. This may help reduce the volume of data transferred to Power BI. However, timeouts originating in the underlying CDS view, SAP Gateway, or OData service would still need to be investigated on the SAP side. A 30-day free trial is available.

     

    Documentation: https://metricasoftware.com/docs/sap/

    Support: https://metricasoftware.com/docs/sap/contact-support-sap/

     

    We would be happy to arrange a demo and review whether the connector is compatible with your CDS and OData setup.

     

    Cheers,
    Metrica Team