SAP Data Warehouse Cloud is a cloud tool that runs on a HANA database. Although native connectivity with SAP Analytics Cloud exists for reporting and planning, it’s possible to create dashboards with third-party tools (like Power BI) on top of a space in SAP Data Warehouse Cloud.
In this article, we are going to explain how to set-up your SAP Datawarehouse Cloud Space for it to connect with Power BI and enable row level security.
The transactional Data
For the purpose of this demo, we have built a simple sales view in SAP Data Warehouse Cloud. This view contains the sales transactions of a fictive company by Sales Manager, Product and Store. The aim is to create a dashboard in Power BI on this data for Sales Managers to follow-up their own sales, not being able to see the sales of other managers. Only a Sales Director should be able to compare different managers. The data warehouse is SAP Data Warehouse Cloud and the reporting is in Power BI.
The Database Users
Database users are technical users that can connect the underlying SAP HANA Cloud, SAP HANA database to third-party SQL clients and allow data to be received or sent out via JDBC/ODBC. In SAP Data Warehouse Cloud, an open SQL schema is always created when you create a database user. This open SQL schema grants the database user read privileges to the underlying database. Depending on your needs, different privileges can be set for different schemas.
Under Space Management -> Database Access we have created two distinct database users:
The Row Level Security
Row level security is managed using a mapping table. First, we create an authorization table that specifies which database user (principal) should be able to see which Sales Manager ID’s.
If you are using SAP Analytics Cloud as a reporting tool, the principal name should be the email address of the reporting user. In this case, we are using the database users for accessing DWC through Power BI, hence the principal is the database user created in the previous section.
In this example, the Sales Director (SD1) can see data for all Sales Managers, while Sales Manager 1 (SM1) will only be able to view his own sales transactions.
After creating the mapping table, a data access control is created as follows:
The data access control is then mapped in the sales view.
Bring it together in Power BI
For connecting Power BI to SAP Datawarehouse Cloud, please read the following blog post: https://blogs.sap.com/2022/02/23/how-to-consume-sap-data-warehouse-cloud-in-power-bi/ . For the row-level security to work, we created a live ODBC connection connecting to SAP Data Warehouse Cloud.
In Power BI, we created two different reports, a Sales Director Dashboard and a Sales Manager Dashboard.
When we logon with user BUSINESSUNITX#SD1 (the sales director), we are able to see all Sales Managers:
When we logon with user BUSINESSUNITX#SM1 (the sales manager), we only see the info for that particular sales manager:
Conclusion
I hope that this blog posts helps in understanding that SAP Data Warehouse Cloud is not a closed solution that is restricted to the SAP ecosystem. On the contrary, SAP Data Warehouse Cloud can be connected to third-party reporting tools like Power BI, still benefiting from the power of SAP Data Warehouse Cloud.
Please don't hesitate to provide any feedback in the comment section below! If you are interested in more content related to SAP Data Warehouse Cloud, I encourage you to follow this tag: https://blogs.sap.com/tags/73555000100800002141/
References
For the full SAP Data Warehouse Cloud security guide; please refer to https://help.sap.com/doc/ae0dc6e25cfa4ac0929f62f6475e07db/cloud/en-US/SAP_Data_Warehouse_Cloud_Secur...
For creating database users in SAP Data Warehouse Cloud; please refer to: https://developers.sap.com/tutorials/data-warehouse-cloud-intro8-create-databaseuser.html
For more information on consuming SAP Data Warehouse Cloud data in Power BI; please refer to: https://blogs.sap.com/2022/02/23/how-to-consume-sap-data-warehouse-cloud-in-power-bi/
In this article, we are going to explain how to set-up your SAP Datawarehouse Cloud Space for it to connect with Power BI and enable row level security.
The transactional Data
For the purpose of this demo, we have built a simple sales view in SAP Data Warehouse Cloud. This view contains the sales transactions of a fictive company by Sales Manager, Product and Store. The aim is to create a dashboard in Power BI on this data for Sales Managers to follow-up their own sales, not being able to see the sales of other managers. Only a Sales Director should be able to compare different managers. The data warehouse is SAP Data Warehouse Cloud and the reporting is in Power BI.
The Database Users
Database users are technical users that can connect the underlying SAP HANA Cloud, SAP HANA database to third-party SQL clients and allow data to be received or sent out via JDBC/ODBC. In SAP Data Warehouse Cloud, an open SQL schema is always created when you create a database user. This open SQL schema grants the database user read privileges to the underlying database. Depending on your needs, different privileges can be set for different schemas.
Under Space Management -> Database Access we have created two distinct database users:
- SM1: Sales Manager 1. This Sales Manager should only have access to the sales transactions performed by himself.
- SD1: Sales Director 1. This Sales Director should only have access to all the sales transactions and is able to compare the different Sales Managers.
The Row Level Security
Row level security is managed using a mapping table. First, we create an authorization table that specifies which database user (principal) should be able to see which Sales Manager ID’s.
If you are using SAP Analytics Cloud as a reporting tool, the principal name should be the email address of the reporting user. In this case, we are using the database users for accessing DWC through Power BI, hence the principal is the database user created in the previous section.
In this example, the Sales Director (SD1) can see data for all Sales Managers, while Sales Manager 1 (SM1) will only be able to view his own sales transactions.
After creating the mapping table, a data access control is created as follows:
The data access control is then mapped in the sales view.
Bring it together in Power BI
For connecting Power BI to SAP Datawarehouse Cloud, please read the following blog post: https://blogs.sap.com/2022/02/23/how-to-consume-sap-data-warehouse-cloud-in-power-bi/ . For the row-level security to work, we created a live ODBC connection connecting to SAP Data Warehouse Cloud.
In Power BI, we created two different reports, a Sales Director Dashboard and a Sales Manager Dashboard.
When we logon with user BUSINESSUNITX#SD1 (the sales director), we are able to see all Sales Managers:
When we logon with user BUSINESSUNITX#SM1 (the sales manager), we only see the info for that particular sales manager:
Conclusion
I hope that this blog posts helps in understanding that SAP Data Warehouse Cloud is not a closed solution that is restricted to the SAP ecosystem. On the contrary, SAP Data Warehouse Cloud can be connected to third-party reporting tools like Power BI, still benefiting from the power of SAP Data Warehouse Cloud.
Please don't hesitate to provide any feedback in the comment section below! If you are interested in more content related to SAP Data Warehouse Cloud, I encourage you to follow this tag: https://blogs.sap.com/tags/73555000100800002141/
References
For the full SAP Data Warehouse Cloud security guide; please refer to https://help.sap.com/doc/ae0dc6e25cfa4ac0929f62f6475e07db/cloud/en-US/SAP_Data_Warehouse_Cloud_Secur...
For creating database users in SAP Data Warehouse Cloud; please refer to: https://developers.sap.com/tutorials/data-warehouse-cloud-intro8-create-databaseuser.html
For more information on consuming SAP Data Warehouse Cloud data in Power BI; please refer to: https://blogs.sap.com/2022/02/23/how-to-consume-sap-data-warehouse-cloud-in-power-bi/
- SAP Managed Tags:
2 Comments
