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 .