Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Data Federation Between SAP Datasphere and Azure Synapse

karishma_kapur
Employee
Employee
‎11-23-2022 8:13 PM
Background

With data being spread across multiple hyperscalers, it becomes hard to derive insights on your combined data sources. However, SAP Datasphere 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 Datasphere provides customers and users the ability to federate their data from different sources in real time into virtual tables in SAP Datasphere 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 Datasphere 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 Datasphere’s analytical capabilities to derive useful insights through SAP Analytics Cloud.


Azure Synapse Federation ARD


 

Pre-requisite Steps to Integrate Azure Synapse with SAP Datasphere

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

 

Steps to Integrate Azure Synapse with SAP Datasphere

  1. Create an on-premise agent in SAP Datasphere.

    • In SAP Datasphere, 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.






 

  1. 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.









 

  1. 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 Datasphere and perform these commands:


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



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








 

  1. 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.





 

  1. Set up the connection to SAP Datasphere 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 Datasphere 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.







 

  1. 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.”





 

  1. Next, we have to register the MssqlLogReagerAdapter. To do this, please go to the on-premise agent screen in your SAP Datasphere instance where we were earlier.

    • Find the agent you created, and click edit.




    • Check MssqlLogReaderAdapter and Save the Agent.




 

  1. Now, we can create the connection in SAP Datasphere by using the Microsoft SQL Server Connection tile.

    • Please Navigate to the connections tab in SAP Datasphere 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”






 

  1. Now that the connection is created, we can create a view in SAP Datasphere and analyze the data in SAP Analytics Cloud.

    • Import and deploy the remote table. Then, create a view in SAP Datasphere.




    • Visualize the data in SAP Analytics Cloud







 

Conclusion

Congratulations! You have now successfully visualized data from Azure Synapse in SAP Analytics Cloud through SAP Datasphere 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.

Thank you 🙂
4 Comments
dwinnix
Member
‎04-15-2023 6:09 AM
0 Kudos
karishma_kapur , does the data federation work both ways?  Your diagram seems to show one-way arrow, i.e., from Azure Synapse to SAP Datasphere.  If it is only one way, is there a way for Synapse to pull data from Datasphere?
karishma_kapur
Employee
Employee
‎04-17-2023 6:23 PM
0 Kudos
This architecture is from SAP’s point of view on federating data from Azure Synapse into SAP Datasphere. We are not aware of Azure’s architectural capabilities for federation in the other direction.
andreabisconti
Discoverer
‎05-31-2023 5:35 PM
Hi Karishma,

thanks for the guide.

After following it we get an error if we try to create a dataflow, getting this error:


 

Could you help me?

Thanks
karishma_kapur
Employee
Employee
‎06-20-2023 5:32 PM
0 Kudos
Hi Andrea,

Thank you for your comment. This blog was written in context for data federation architectures, so it was only tested for remote, not data flows.

Thank you,

Karishma Kapur
Labels in this area
Popular Blog Posts