How to Consume SAP Data Warehouse Cloud in Power BI
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
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.
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.
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