Forum Discussion

ayush_15's avatar
ayush_15
Frequent Visitor
6 months ago
Solved

Need help to Connect SAP Datasphere view with Prompts to Power BI using Direct Query

Hi Everyone, I have a calculated view sitting in SAP Datasphere. It uses prompts to retrieve data faster as compared to querying entire dataset. I need two things to be achieved : 1) To be ab...
Need Help
  • v-sshirivolu's avatar
    v-sshirivolu
    6 months ago

    Hi ayush_15 ,

    Using an Analytical Model instead of a Calculation View can impact visibility. In SAP Datasphere, Analytical Models are not always automatically available at the SQL layer, and Power BI can only access objects exposed to SQL. If the model is not deployed, exposed to the Open SQL schema, and assigned the appropriate SELECT and schema privileges, it will not appear in the Navigator. Typically, a blank folder indicates issues with SQL exposure or permissions in Datasphere.

    When it comes to “exposing filter columns,” it is advisable to keep fields such as Year as regular columns within the view rather than using prompts. You can then use a slicer in Power BI based on the Year field. Selecting a value like 2022 prompts Power BI to generate a SQL WHERE clause (example -  WHERE Year = 2022), which is pushed down to SAP through query folding. Utilizing slicer values with M parameters or Value.NativeQuery can disrupt query folding in DirectQuery, often resulting in errors after Close & Apply. Therefore, the best practice is to avoid prompts and use slicers for filtering to ensure proper query folding and source execution.

v-sshirivolu's avatar
v-sshirivolu
Community Support
6 months ago

Hi ayush_15 ,

You can meet both requirements using supported capabilities of the SAP HANA connector in Power BI. For prompt-enabled calculation views in SAP Datasphere, use the SAP HANA connector in DirectQuery mode and execute the view through a parameterized native query with 'Value.NativeQuery', binding it to Power Query parameters and enabling query folding - 'EnableFolding=true'. Microsoft confirms that input parameters and variables are supported, and properly structured native queries can fold back to SAP HANA. This ensures filters are pushed down to Datasphere instead of loading the full dataset into Power BI.

For hierarchies, when connecting via the SAP HANA connector in DirectQuery mode, hierarchies defined in SAP HANA calculation views are automatically exposed in the Power BI field list and can be used for drill down in reports. Therefore, the supported solution is to use DirectQuery with parameterized native queries for prompts and consume hierarchies directly through the SAP HANA connector.

 

Below are Microsoft Docs :

https://learn.microsoft.com/power-query/connectors/sap-hana/overview
https://learn.microsoft.com/power-bi/connect-data/desktop-directquery-sap-hana
https://learn.microsoft.com/power-query/native-query-folding