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
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.
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 v-sshirivolu
Im facing the issue where when connecting the Datasphere with Power BI using SAP hana connector im only getting the Blank folder, i have already provided the read & write privilage but still cant see anything. since u have mentioned about Hana/Datasphere permission, can you please help me here on how i can enable this permission? your response is much apperciated.
Thank you in advance...Hi Experts,
can you please help me with the solution for above issue raised by me for direct query in power BI with Datasphere. Your help will be much apperciated.
Thanks
