SAP Data Warehouse Cloud (DWC) is known for being optimally aligned with SAP Analytics Cloud (SAC). However, even companies that decide against SAP Analytics Cloud for cost reasons or due to a preference for other reporting tools can use SAP Data Warehouse Cloud as a modeling tool for their business intelligence use cases. Using the ODBC adapter, 3rd party tools, for example Microsoft Power BI, Tableau, Microsoft Excel or Microsoft Access, can be fed with data from the SAP Data Warehouse Cloud.
How to connect SAP Data Warehouse Cloud with Power BI and Tableau
Follow the traces: Audit logging in SAP Data Warehouse Cloud
But how exactly does the connection work? First of all, it must be ensured that the ODBC data source administrator is installed on the device. It is displayed in the Windows search as “ODBC data sources”. Another prerequisite is the installation of the SAP HANA Client 2.0. If necessary, the “DigiCert Global Root CA” certificate must also be downloaded if it is not already on the device.
The goal of the DWC connection is to free up a space for data consumption and to display certain views in the 3rd party reporting tools. To do this, a database user must be created in the space. After clicking on “Create” a mask opens to define the suffix for the database user name and the access permissions. The permissions can be adjusted at any time. The host and port, which are important for the further course of the setup, are created automatically and can be viewed via the info dialog box.
The next step is to release the views for consumption. This works the same way as with SAP Analytics Cloud: simply navigate to the desired view and check the “Expose for consumption” option.
Finally, it is still necessary to expose the public IP address for the SAP Data Warehouse Cloud. To do this, the user navigates in SAP DWC to System/Configuration/IP Allowlist, enters the IP address and then saves the list. By the way: Longer waiting times are normal when performing this step.
To create the adapter, the ODBC data source administrator must be opened and a new user DSN must be created. If the SAP HANA Client 2.0 is present on the device, the system will now suggest the “HDBODBC” driver from SAP. After selecting this driver, a new screen opens in which the database type “SAP HANA Database (Single-tenant)” must be selected. In addition, TLS/SSL must be enabled to ensure the security of the data. The name and the description are freely selectable.
Furthermore, the host and the port must be entered. For the host, the tenant URL is not specified. The correct host address and also the port are in the info dialog box for the database user in SAP Data Warehouse Cloud. With a click on “OK” the adapter is created.
To complete the SAP Data Warehouse Cloud connection, the adapter must be selected as a data source at the 3rd party application and the database user’s credentials must be specified. Microsoft Power BI is used as an example to illustrate the integration – the process is similar for Tableau and other tools.
The first thing to do is to store the credentials in Power BI. To do this, the user navigates to File/Options and Settings and clicks on Data Source Settings. If the adapter is not displayed, the user must select it under “Change source”. If the adapter is displayed, click on “Edit permissions”. Now a new mask opens for editing the credentials. The user name and password can be found, as well as the host and port, in the info dialog box for the database user in SAP Data Warehouse Cloud. To see the password, a new password must be requested.
In Power BI, data sources can be integrated via the “Retrieve data” button. There you can select “ODBC” as data source. In the next mask a dropdown menu opens, which shows the created adapter. By selecting it and then confirming, the import of the views should work. In the process, the data can also be transformed. Now nothing stands in the way of using SAP Data Warehouse Cloud in combination with non-SAP solutions.