Forum Discussion
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 able to retrieve prompt enabled views in Power BI , i.e. a way to input prompts dynamically from Power BI and get data based on it.
2) Ingest hierachies in Datasphere directly to Power BI.
I am currenlty using the SAP HANA connector in Power B Desktop but it seems not to allow dynamic passing of variables to SAP and does not convert it into a foldable SQL query.
Any help or guidance would be really appriciated.
Regards
Ayush
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.
Below is the working solution for the same .
https://github.com/AyushKaranwal/powerbi-datasphere-directquery-guide
15 Replies
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-foldingHey v-sshirivolu , Thanks for the help.
I am able to bind parameter to the Direct query using Native logic , however things start to fail the moment I try to bind the parameter to user selection .
Scenario :
Report is published. User selects Year as 2022 from the slicers.
Now 2022 must be passed to M query and data should be returned from SAP prompt view only for Year="2022".
This seems to be failing.
What have I done:
1) Created Parameter year
2) Bind to Paramerter in modelling tab in Power BI.Hi ayush_15 ,
Power Query parameters are evaluated during model load or refresh, while slicers apply DAX filters at report interaction time and do not automatically update M parameters once the report is published. Microsoft offers Dynamic M Query Parameters for certain DirectQuery scenarios, but this functionality depends on the connector and query pattern, and is not guaranteed for native queries using Value.NativeQuery with SAP HANA or Datasphere calculation view prompts. When dynamic parameter binding is unavailable, Microsoft advises exposing the filter field (such as Year) in the source view and allowing DirectQuery query folding to push slicer filters to the source system via SQL, rather than binding slicers to M parameters.
I cross referenced the minimum requirements for the views access in Power BI but they don't show up.
My aim is to consume (datasphere views with prompts) in Power BI.
Reason : My datasphere analytical model view has >1 Billion rows hence performance with Power BI DQ is laggy.
I tried using SAP connector but as per our conversation the Dynamic M query isn't foldable hence passsing user interaction on slicer as dynamic parameter to views look unrealistic.
Another approach ,
I should be able to access prompts view directly in Power BI as per your comments using SAP HANA connector.
When I try to access the Datasphere analytical view from datasphere , it accepts the creds and moves on to the data display tab. There it shows no views in the drop down . Just show me contents folder and blank.
I have a SAP client installed in my system , any thoughts on what could be breaking.Hi ayush_15 ,
According to Microsoft, if Datasphere views are not visible in Navigator and only an empty folder shows, it’s likely due to HANA/Datasphere permissions. Power BI displays only objects the user has access to, and views must be exposed for SQL access. Dynamic parameter binding isn’t supported as slicers don’t update M parameters after publishing. The recommended solution is to expose filter columns like Year in the Datasphere view and use DirectQuery with query folding, so slicer filters are pushed to SAP HANA as SQL WHERE conditions.Refer these docs:
SAP HANA connector overview: https://learn.microsoft.com/power-query/connectors/sap-hana/overviewDirectQuery for SAP HANA: https://learn.microsoft.com/power-bi/connect-data/desktop-directquery-sap-hana
Dynamic M Query Parameters: https://learn.microsoft.com/power-bi/connect-data/desktop-dynamic-m-query-parameters
Hi ayush_15 ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Below is the working solution for the same .
https://github.com/AyushKaranwal/powerbi-datasphere-directquery-guideThe SAP HANA connector has limited support for passing Datasphere input parameters dynamically, so this is a known limitation. For hierarchies, a common workaround is to expose them as tables and model them in Power BI. If you share whether you're connecting to a calculation view, analytical model, or SQL view, others may be able to suggest a more suitable approach.