How to Consume SAP Data Warehouse Cloud in Power BI

Introduction:

SAP Data Warehouse Cloud is a Software as a Service (SaaS) offering and the world’s first enterprise-ready cloud data warehouse, bringing together all of your data sources in one place while retaining data security, trust, and semantic richness.It is the company’s most recent cloud data warehousing offering. Redshift, Google Big Query, Azure Synapse, Snowflake, and other cloud based SAAS Data Warehouse solutions compete with it. With this offering, SAP brings decades of data warehousing innovation to the table.

With SAP Data Warehouse Cloud, analytics and data warehousing are combined in a single cloud solution, allowing for top to bottom data administration as well as quick and reliable decision-making based on enhanced analytics. It also integrates SAP HANA’s sophisticated data management capabilities with SAP Analytics Cloud’s comprehensive advanced analytics capabilities.

SAP Data Warehouse Cloud Empower your business users by offering them access to Spaces, which are visualized working environments with connectivity to your landscape’s systems and models.

Key features of SAP Data warehouse cloud:

• Deployment is simple and quick.
• Data transformation is hassle-free with a couple of clicks.
• All your data in one spot.
• Access data from SAP and third-party sources across cloud and on-premises applications using data modelling, replication, and orchestration.
• Analyse data in real time with SAP HANA Cloud-powered in-memory intelligence

Prerequisites for Connecting SAP Data warehouse cloud with Power BI:

• SAP Data Warehouse Cloud: Trial or Standard License.
• SAP HANA Database Client installed.
• Power BI Latest version installed
• Active Internet Connection.

The primary focus of this blog post is to bridge a connection between SAP Data warehouse cloud and Power BI and consume the modeled data in power bi as a visualization report. So, creation of SAP Data Warehouse Cloud account and space management are excluded in this blog post.

Data Modelling in SAP Data Warehouse Cloud:

The data modelling in the SAP Data Warehouse Cloud is made easy by ‘Data builder’, It is an on-demand service in the SAP Data warehouse cloud,

1.Click ‘Data Builder‘ from the Left side menu.

2.Import the required data set, which you want to model as a supported format.

3.I have imported HR management data.

4.Create a new graphical view to model your data.

5.Select the ‘Repository’ tab in the graphical view’s modelling space; under the ‘Tables‘ folder, click on the one you just imported.

6.To begin, drag and drop an entity ‘CV EMP ACTIVE INACTIVE’ and ‘ZCV_VACATION’ into the modelling area. Accept the system’s request to import the table, so it may deploy the data.Thanks to the SAP DWC’s extensive features, we can now model the data how we like.

7. I’ve joined two tables based on ‘Emp_ID‘ utilizing Inner join.

8.A last container called ‘HR-VIEW’ is produced automatically. It brings a graphical view’s final step to life. We’ll be able to see a preview of the output data.

9.Enable ‘Expose for Consumption‘ to make them available in third-party reporting tools.

10.The model should be saved and deployed. The data modeling phase is now complete, and we’ll dive into how to integrate Data Warehouse Cloud and Power BI.Integrating SAP Data Warehouse Cloud and Power BI

1.Whitelist your client’s public IPv4 address as the initial step in integration. If you’re using a network firewall, make sure to provide the proxy’s external IPv4 address. The IP address you give must be your public internet address2.Head to ‘Configuration’ from the left menu, and select ‘trusted IP’s’ from the ‘IP allowlist’.3.Click ‘Add‘ and enter your public IPv4 address.

Creating and Configuring ODBC Adapter

1.Make sure you have the SAP HANA client 2.0 and the SAP ‘HDBODBC’ driver installed before creating the ODBC adapter.

2.Open ‘ODBC data sources 64 bit’ from the start menu. And click add button to add new adapter.

3.Select the ‘HDBODBC‘ driver as a data source and click finish.4.After selecting the driver, a new page appears, where you must pick the database type ‘SAP HANA Database (Single tenant)‘, Additionally, TLS/SSL must be enabled to maintain data security. The name and description are user-defined.5.The database user’s information dialogue box in SAP Data Warehouse Cloud has the host address and the port. Follow the instructions below to obtain the host and port number.

6.In the SAP Data warehouse cloud, go to space management, choose the working space, and then go to the Database access page to create a new database user.

7.Create a new database user with database user name suffix and make sure to enable Read access (SQL), with grant option and HDI Consumption, and click create.

8.Open the Info dialog box of the created database user to get the following dialog box with host id, port, and credentials.9.Enter the obtained host, port details in the ODBC adapter and test the connection using the credentials.

Configuring Power BI to Establish Connection

1.Select the data source as ODBC in Power BI.

2. Select the Adapter which you have created.

3.On a successful connection, you have to select your SAP Data Warehouse Cloud space and the views that you want to consume in Power BI.

4.After loading you can see your Measures and dimensions have been listed in the power BI.

The HR management Report has been created in Power Bi using SAP Data warehouse cloud as a data source.

Conclusion:

I hope that this blog post has helped you better grasp the SAP Data warehouse cloud and Power BI integration. Please feel free to share any thoughts or recommendations you may have about this blog post in the comments section.

References:

ODBC connectivity from https://developers.sap.com/tutorials/data-warehouse-cloud-bi4-install-odbc.html

SAP Data Warehouse Cloud from https://help.sap.com/doc/aa5cd4412124489490cb864a6139526c/cloud/en-US/SAP_Data_Warehouse_Cloud_Modeler_Guide.pdf