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
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.
HI ayush_15 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions