SAP Datasphere: PowerBI Direct Query via HANA Cloud Calculation Views
2023-11-4 02:8:39 Author: blogs.sap.com(查看原文) 阅读量:25 收藏

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.

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-cloud-e2e-scenario/

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%3A%20CV%20Data

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%3A%20Open%20HDI%20Container

Fig3: Open HDI Container

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

Fig4%3A%20SQL%20Admin%20Console

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%3A%20SQL%20Current%20User

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%3A%20Create%20DB%20User%20in%20Datasphere

Fig6: DB User in Datasphere

Fig7%3A%20DB%20User%2C%20Host%2CPort%20Details

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%3A%20SQL%20to%20grant%20privilege%20on%20container

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%3A%20PowerBI%20SAP%20HANA%20DB%20Connector

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%3A%20Add%20DS%20HANA%20Cloud%20server%20details

Fig10: Add DS HANA Cloud server details

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

Fig11%3A%20Add%20DS%20DB%20User%20credentials

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%3A%20Choose%20CV%20from%20HDI%20Container

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%3A%20Demo%20PowerBI%20Visualizations

Fig14: Demo Power BI Visualizations

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

Fig1%3A%20PowerBI%20HANA%20Relational%20Access

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.

Fig2%3A%20DirectQuery%20Advanced%20editor%20Workaround

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 .


文章来源: https://blogs.sap.com/2023/11/03/sap-datasphere-powerbi-direct-query-via-hana-cloud-calculation-views/
如有侵权请联系:admin#unsafe.sh