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.
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 help.sap 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:
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.
4. Here, choose your container and right click to select Open SQL ADMIN console from the context menu.
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;
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.
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;
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.
10. Then, provide Database user credentials from step 6.
11. After clicking next, in the graphical UI, expand the container and select the corresponding CV to create a direct query connection.
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.
- Direct Query Graphical UI doesn’t work with relational mode.
- 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.
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.
Find more information and related blog posts on the topic page for SAP Datasphere .