Forum Discussion
Need help to Connect SAP Datasphere view with Prompts to Power BI using Direct Query
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
Hey 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.