Need help to Connect SAP Datasphere view with Prompts to Power BI using Direct Query

[ayush_15]

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

[ayush_15]

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.

[v-sshirivolu]

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/overview

DirectQuery 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

[v-sshirivolu]

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.

[ayush_15]

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.

[v-sshirivolu]

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.

[ayush_15]

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"

[v-sshirivolu · Accepted solution]

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.

[v-sshirivolu]

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

[ayush_15]

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.

[v-sshirivolu]

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.

[v-sshirivolu]

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-folding