SAP Datasphere: PowerBI Direct Query via HANA Cloud Calculation Views

[jaigupta]

Introduction

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

Prerequisites

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.

https://learn.microsoft.com/en-us/power-query/connectors/sap-hana/overview

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 help.sap documentation or the existing blogs as mentioned below.

https://blogs.sap.com/2023/04/05/access-sap-hana-cloud-underneath-of-sap-datasphere/

https://blogs.sap.com/2022/10/19/hybrid-developments-using-sap-hana-cloud-and-sap-data-warehouse-clo...

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.

Steps

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.                                                                    Fig2: 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.

SELECT CURRENT_USER FROM DUMMY;

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.

 

CREATE LOCAL TEMPORARY COLUMN TABLE #ROLES LIKE _SYS_DI.TT_SCHEMA_ROLES;



INSERT INTO #ROLES ( ROLE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) VALUES ( '<HDBROLENAME>', '', '<DBUSERNAME>' );



CALL <CONTAINERNAME>#DI.GRANT_CONTAINER_SCHEMA_ROLES(#ROLES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);



DROP TABLE #ROLES;

 

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

 

Conclusion

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 .

 

[mcalucin]

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

Thanks.

Regards,

Mel Calucin

[giulia-felappi]

Hi jaigupta28

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!
Regards

Giulia

[jaigupta]

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.

Regards,

Jai

[jaigupta]

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.

Regards,

Jai

[giulia-felappi]

Hi jaigupta28

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 (https://github.com/SAP-samples/data-warehouse-cloud-connectors). 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
Regards

[chrisesco]

Hi Jai, great blog.

I want to ask you.

Is there any way to hide the calculated views that database users see when connecting to SAP HANA from Power BI?

I would like to not show some views to a second database user that I have created. Can I do this by creating another DWC_CONSUMPTION_ROLE file ?

Thank you.

Regards

[jaigupta]

Hi @chrisesco ,

Yes, you can create a new hdbrole and restrict it with the CV's you want to expose for consumption. Then, you can assign the created hdbrole to the DB user.

Regards,

Jai

[jafp]

Hi Jai,

thank you for the post! I was wondering if it is possible to use a hana view with input parameters and this should be pass to the power bi query?

Thanks in advance!

José Antonio Funes Pérez

[jaigupta]

Hi @jafp ,

Yes, it supports input parameters.

Regards,

Jai

[jafp]

Thanks you so much @jaigupta !

Do you know how this could be done? Is there a blog or whatever? I've been looking for information for a long time but I can't find anything.

Thanks for your answer!

José Antonio Funes Pérez