Skip to Content
Technical Articles
Author's profile photo Jai Gupta

SAP Datasphere: PowerBI Direct Query via HANA Cloud Calculation Views


As part of this blog, I will share the steps on how Calculation Views(CV) deployed in underlying HANA Cloud instance of SAP Datasphere can be consumed in Power BI Desktop using Direct Query via SAP HANA Database connector.

Also, please use the Power BI Desktop release equal or greater Sept 2023 release for using this feature, as summarized in link below.

Power BI September 2023 Feature Summary | Blog di Microsoft Power BI | Microsoft Power BI


First, please make sure you have followed the steps recommended by the Microsoft in the link mentioned below for using the SAP HANA Database connector in Power BI Desktop.

Second, you should have already built a Calculation View(CV) and deployed in the underlying HANA Cloud tenant of Datasphere using Business Application Studio(BAS). If you need help with this, then please refer to documentation or the existing blogs as mentioned below.

Once you have completed above prerequisites, then proceed to next section and follow the steps required for consuming the data out of CV in Power BI Desktop via Direct Query using SAP HANA Database Connector.


1. Here, I am using the calculation view CV_CUSTSALES and the data in in this CV looks like below:


Fig1: CV Data


2. Then, use or create hdbrole DWC_CONSUMPTION_ROLE in BAS which contains object privileges on the Calculation view, as shown below.                                                                    Fig1%3A%20HDBROLE%20DefinitionFig2: HDBROLE Definition


3. Next, from SAP_HANA_PROJECTS open the HDI Container in the DB explorer, as shown below.


Fig3: Open HDI Container


4. Here, choose your container and right click to select Open SQL ADMIN console from the context menu.


Fig4: SQL Admin Console


5. In the opened SQL Console, please check the CURRENT USER by running below SQL, and confirm it is indeed HDI Design time user, as shown below.



Fig5: SQL Current User


6. Next, create DB user in the space management of Datasphere, as shown below. Here, note the details of the Host, Port, Database User Name, and Password which we will be required later when connecting to Power BI Desktop.


Fig6: DB User in Datasphere


Fig7: DB User, Host,Port Details


7. For granting the privileges on the roles within the container, run below statements to grant DB user the required privileges from the role DWC_CONSUMPTION_ROLE.








Fig8: SQL to grant privilege on container


8. Once that is done, go to Power BI Desktop, choose Get Data and select SAP HANA Database Connector.


Fig9: Power BI SAP HANA DB Connector


9. Here, use the details from step 6 to fill the Server, port details. Then, choose DirectQuery and click OK.


Fig10: Add DS HANA Cloud server details

10. Then, provide Database user credentials from step 6.


Fig11: Add DS DB User credentials

11. After clicking next, in the graphical UI, expand the container and select the corresponding CV to create a direct query connection.


Fig12: Choose CV from HDI Container


12. Next, click on Load to create the direct query connection.

Fig13: DirectQuery Connection to DS HANA Cloud


13. At last, build your visualizations in Power BI Desktop using the direct query connection, as shown below.


Fig14: Demo Power BI Visualizations

Known issues

  • Direct Query Graphical UI doesn’t work with relational mode.


Fig15: PowerBI HANA Relational Access

  • Here, Advanced Editor for DirectQuery can be used with SQL instead of Graphical UI, as shown below. Also, Import mode can be used instead of DirectQuery.


Fig16: DirectQuery Advanced editor Workaround



This blog introduced you to the step by step process of using the DirectQuery mode in Power BI Desktop for Calculation View created in underlying HANA Cloud tenant of Datasphere via SAP HANA Database connector.

Thanks for reading! I hope you find this blog helpful. For any questions or feedback just leave a comment.

Best wishes,

Jai Gupta

Find more information and related blog posts on the topic page for SAP Datasphere .


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Mel Calucin
      Mel Calucin

      Is there is an automated way to do user provisioning and security from the Datasphere application layer into the Datasphere HANA layer?



      Mel Calucin

      Author's profile photo Jai Gupta
      Jai Gupta
      Blog Post Author

      Hi Mel,


      For now, Datasphere CLI can be one option for automated provisioning of Datasphere users.

      Also, I would recommend you to check the Datasphere roadmap plan, System for cross-domain identity management API,  where SCIM API is planned to released which will help with GRC integration scenarios in future.



      Author's profile photo Giulia Felappi
      Giulia Felappi

      Hi Jai Gupta

      great blog, thanks for sharing!

      I have a couple of questions:

      1. are Calculation Views the only objects that can be consumed with this method? Let's say we have Views, Tables, Analytic models, Perspectives...
      2. do you know if it is possible to use SSO for database login instead of Basic Authentication with DB user?

      Thank you!


      Author's profile photo Jai Gupta
      Jai Gupta
      Blog Post Author

      Hi Giulia,

      For direct query, only calculation views are supported in Datasphere with no SSO option for now.

      Also there are other ways to connect using ODBC and ODATA directly to Datasphere but it doesn't support direct query.



      Author's profile photo Giulia Felappi
      Giulia Felappi

      Hi Jai Gupta

      thank you so much for your answer!

      For my scenario we would like to use Import instead of DirectQuery.

      Regarding ODBC, it's the method I'm using right now.

      While ODATA, I suppose you are referring to this custom connector ( SAP has been promising for a while to complete this connector, but it is still kind of a Beta version.
      This works with SSO, but I would not trust using it for production usage (in case of issues there is no support from SAP, the only way is to report it on the Github repository hoping someone will fix it).

      My goal is to connect to Datasphere's HANA Cloud instance from PowerBI Desktop, through ODBC connection, with SSO with Azure (SSO with Azure is already configured for Datapshere).

      Do you have any suggestion on how to achieve this setup?

      Thank you