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 v-sshirivolu ,
First thanks a lot for your responses and help.
However , there are few thigns which are still not fitting .
1) SAP Datasphere analytical views are still not available as selection when I use SAP HANA Connector , contents still show blank. There are no options left in SAP end to enable more .
Is there any specific setting or configuration tht needs to be enabled ?
2) As nothing is visible in #1 I am struggling to import SAP Hierarchies into Power BI .
3) Binding slicer selection to Parameters and Parameters to Native SQL , I am able to fetch correct value within Power Query but it fails to fold once I "Close and Apply" and results in error , unable to convert .
Could you shed some light and please let me know if you need additional details.
Hi Khushboobarai ,
If your SAP Datasphere analytical views are not appearing in the Navigator and you only see an empty folder, this is usually due to SQL exposure or permission settings, rather than an issue with Power BI. According to Microsoft documentation, Power BI displays only those objects that are accessible at the SQL layer for the connected user. Please verify that your view is deployed, exposed for SQL access in Datasphere, and that the necessary SELECT and schema privileges are granted. Once these conditions are met, hierarchies defined in the calculation view should appear automatically when connecting via DirectQuery mode.
For the “Unable to convert an M query into a native source query” error, this typically results from a lack of full query folding, which DirectQuery requires. Using Value.NativeQuery, string concatenation, or non-foldable M transformations to pass parameters disrupts folding and can cause visuals to fail after Close & Apply. While Dynamic M Query Parameters are supported with DirectQuery, they only function when folding is maintained. Microsoft recommends exposing filter columns (such as Year) directly in the Datasphere view and using Power BI slicers so filters are automatically pushed down as SQL WHERE clauses. This approach ensures a fully supported solution and prevents native query conversion errors.
Hi v-sshirivolu ,
I will check based on that information , however I am trying to use analytical views not calculation view. Does tht change anything ?
Secondly , apologies but could you further explain what you mean by "Microsoft recommends exposing filter columns (such as Year) directly in the Datasphere view and using Power BI slicers so filters are automatically pushed down as SQL WHERE clauses"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