With data being spread across multiple hyperscalers, it becomes hard to derive insights on your combined data sources. However, SAP Data Warehouse Cloud bridges this gap through data federation. Data federation is the process of aggregating data from different sources into a virtual database. As a result, SAP’s Data Warehouse Cloud provides customers and users the ability to federate their data from different sources in real time into virtual tables in SAP Data Warehouse Cloud without duplicating the data. This allows data to be combined resulting in more insightful analytics and business intelligence.

Goal and Architecture

In this blog, I will show you how to federate your data from Azure Synapse into SAP Data Warehouse Cloud using SAP HANA^® smart data integration data provisioning agent and the MSSQL Log Reader Adapter. I will also show you how you can leverage SAP Data Warehouse Cloud’s analytical capabilities to derive useful insights through SAP Analytics Cloud.

Azure Synapse Federation ARD

Pre-requisite Steps to Integrate Azure Synapse with SAP Data Warehouse Cloud

The prerequisites for this connection are as follows. You will need:

• to download and install the latest SAP HANA^® smart integration data provisioning agent on a machine with a Linux operating system,
• an Azure Synapse Workspace properly set up,
• a SQL Pool (either dedicated or serverless), and
• tables created in Azure Synapse.

Steps to Integrate Azure Synapse with SAP Data Warehouse Cloud

1. Create an on-premise agent in SAP Data Warehouse Cloud.
   • In SAP Data Warehouse Cloud, you will first need to navigate to the on-premise agent screen through the System -> configuration tab.
     • 
   • Then you will need to click on the plus (+) tile to create a new on-premise agent.
     • 
   • After clicking on the +, it will ask you for an agent name. Please provide an agent name here. After clicking create, the agent settings will appear. Please take note of the agent name, HANA server, port number, HANA user name for agent messaging, and the HANA user password for agent messaging.
     • If you have lost the password, you can open the settings for the on-premise agent using the three dots on the agent’s tile, and then click on request new password and a new one will be provided to you.

2. Gather the Azure Synapse Information Needed for the Connection
   • In the Azure Portal, navigate to the Azure Synapse Workspace. Please open the Workspace web URL.
     • 
   • Once you’re in the Workspace Studio, please go to the Manage Tab and click on SQL Pools.
     • 
   • Please click the pool for which you want to federate data from. This will open up the pool’s properties.
     • Change connection strings to JDBC (SQL authentication) and note down the connection string it gives you.
       • 

3. Now we need to create an SQL Database contained database user.
   • Open up the Develop tab in Synapse Studio.
   • Create a new SQL script and make sure you are connected to the correct SQL Pool you want to use.
   • While connected to the master database, please perform the following command.

     • CREATE LOGIN <username here> WITH PASSWORD = '<your password here>';

     • 
   • Next, create a new SQL script and connect to the database for which you want access to in SAP Data Warehouse Cloud and perform these commands:

     • CREATE USER <username here> FROM LOGIN <username here>;

     • GRANT CONTROL ON DATABASE::<database name> to <username here>;

     • 

4. Download the Microsoft JDBC Driver and copy it into the <DPA_install_dir>/lib folder on the SAP HANA^® smart data integration data provisioning agent’s server.
   • 

5. Set up the connection to SAP Data Warehouse Cloud using the DPA’s configuration tool.
   • Navigate to the <DPA_install_dir>/bin folder and run:

     • ./agentcli.sh –-configAgent

   • Enter the number corresponding to ‘SAP HANA Connection” and then the number corresponding to “Connect to SAP Data Warehouse Cloud via JDBC”
   • It will then ask you for the agent name, host name, port, HANA user name for agent messaging, and the HANA user password for agent messaging that we noted down earlier. For “Use encrypted JDBC connection”, I have entered true and for “Use Proxy Server” I have entered false.
     • 

6. Once the connection is finished setting up, you can check the status of the agent to ensure it is running by first entering ‘b’ into the command line and then 1 for “Agent Status.”
   • 

7. Next, we have to register the MssqlLogReagerAdapter. To do this, please go to the on-premise agent screen in your SAP Data Warehouse Cloud instance where we were earlier.
   • Find the agent you created, and click edit.
     • 
   • Check MssqlLogReaderAdapter and Save the Agent.

8. Now, we can create the connection in SAP Data Warehouse Cloud by using the Microsoft SQL Server Connection tile.
   • Please Navigate to the connections tab in SAP Data Warehouse Cloud and click on “create” on the top right.
     • 
   • Next, please find the Microsoft SQL Server Connection Type and enter in the details.
     • 
     • If you recall from the synapse workspace, we had noted down the JDBC connection string. If we parse the string we can get the server name, port, database name, and host name in certificate. For example, if my jdbc connection string was as follows:
       • jdbc:sqlserver://<workspacename>.sql.azuresynapse.net:1433;database=test;user=undefined@<workspacename>.;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;
         • my server name would be <workspacename>.sql.azuresynapse.net
         • port 1433;
         • database test
         • host name in certificate =*.sql.azuresynapse.net
     • The username and password is the username and password we created for the SQL database user earlier.
     • Finally, you’ll want to select the data provisioning agent which we used for the connection. Then click next step and provide a name for the connection. Once finished, click “Create Connection”

9. Now that the connection is created, we can create a view in SAP Data Warehouse Cloud and analyze the data in SAP Analytics Cloud.
   • Import and deploy the remote table. Then, create a view in SAP Data Warehouse Cloud.
     • 
   • Visualize the data in SAP Analytics Cloud
     • 

Conclusion

Congratulations! You have now successfully visualized data from Azure Synapse in SAP Analytics Cloud through SAP Data Warehouse Cloud using the SAP HANA^® smart data integration data provisioning agent.

I hope this information helped you! If it did, please consider giving us a like above.

If you have any questions or would like more information, please leave a comment below or email us at paa@sap.com.