How to load data from Azure Synapse SQL DW to SAP Analytics Cloud Planning

[kenneth_dalvik]

Introduction

SAP Analytics Cloud planning with its xP&A concept is becoming more popular and gain high market interest as xP&A is becoming a crucial part of a finance transformation and a modern finance platform. In a multi-vendor system landscape, it is quite common that the Financial data warehouses will come from other software vendors than SAP, for example Azure and Azure Synapse.

Can Azure Synapse be a source to SAP Analytics Cloud Planning?

How easy is it to integrate and load reference data from Azure Synapse to SAP Analytics Cloud to be used as starting point in planning?

This blog will guide you how you connect to SAP Analytics Cloud and load data for planning.

How is this done?

A prerequisite is that SAP Analytics Cloud has a SAC agent configured. This agent must have MS SQL server drivers installed. For information on how to install and configure SAP Analytics Cloud agent, please see official help.sap.com 

Once those prerequisites are met it is quite straight forward to connect Azure Synapse SQL DW to SAP Analytics Cloud.

First step is to create a connection. This is done in the section for Connection in the menu.

You can use any of the other MS SQL server drivers. In this example I am using MS SQL server 2008.

Connection to Azure SQL DW

Once the connection is established you are good to go. Below, I will take through step by step on how to load data from an Azure Synapse SQL DW.

Loading data from Azure Synapse SQL DW is like loading from any other source. For is one example where I create a planning model from a Azure Synapse SQL DW source:

 

Step 1: Go to modeler in the menu. Choose model from data source

Model from data source

Step 2: SQL databases

Choose SQL databases

Step 3: Choose connection

Step 4: Build an import query by choosing a table or a view from Azure Synapse SQL DW

Choose table or view from the repository of Azure SQL DW

Rest of the steps are standard create model steps and are not covered by this blog.

 

Why it this important?

As many SAP customers have multi-vendor system landscape, this integration flow will become more and more common, hence the ability to load from Azure components and Azure Synapse is essential. SAP official documentation does not include Azure Synapse SQL DW as source, but the blog shows that it is fully supported and easy to configure.

 

Summary & Conclusion

SAP Analytics Cloud is an open platform for BI, analytics, planning and predictive. As customer move towards a multi-vendor system landscape, connectivity to other sources than SAP is crucial.

This blog is an examples on how open and agile SAP Analytics Cloud is and how easy it is to set up a connection to Azure Synapse SQL DW and load into SAP Analytics Cloud.

Yes,  SAP Analytics Cloud can load data from Azure Synapse SQL DW and it is easy to make it work.

[Vitaliy-R]

Hi Kenneth. Thanks for sharing!

What is "xP&A"? Is it Cross-Planning&Analytics?

[kenneth_dalvik]

Hi Witalij

xP&A stands for eXtended Planning & Analysis meaning more than just financial planning, with connected and integrated plans.

[Sumit_Gupta]

Hello Kenneth,

Is there an option available to make a live connection to Azure Synapse Analytics through SAC?

if not , Is there something on the roadmap in near future.

 

Regards,

Sumit

[kenneth_dalvik]

Hi Sumit

This is on the roadmap as future vision of SAC, see below.

https://roadmaps.sap.com/board?PRODUCT=67838200100800006884&q=live connection&range=CURRENT-LAST#;INNO=000D3A478E771EDB8FF8640245DAB4FE

 

This is available today if you have SAP Data Warehouse Cloud (DWC) as a virtual modeling layer between Azure Synapse Analytics and SAC, since SAP DWC can read Azure Synapse "live" without replicating data. This also allows for combining e.g SAP and Azure without data replication into a single view for analytics. That concept is called Query Federation.

BR

Kenneth

[leandro_paula]

Hello Kenneth,

I need to connect the SAC to a PaaS service (Azure SQL Database). SAP Cloud Connector is already installed and configured.

To Import data connection to a SQL database I followed the procedure: Import Data Connection to an SQL Database (sap.com)

But I'm getting the error:

The database driver is misconfigured. Please contact your on-site SAP Analytics Cloud agent administrator.Correlation ID: 21232707-0723-4250-8897-698383445019

 

How did you configure "DriverConfig.properties" for Azure SQL BW?

 

Best Regards.

--

Leandro G de Paula.

[kenneth_dalvik]

Hi

 

This is outside my competence , I normally do not install driver or install Cloud connector. Please check with a SAP basis resource or similar.

 

Br

Kenneth

[ramumoraboyina]

Dear kenneth,

We are trying create the connection and user and password is correct we can able login in SQL . but we are unable to create the connection.Could you please help is there any setting is missing? Please find the attached screenshot