SAP Analytics Cloud: Expand Live Data Source Options via SAP Data Warehouse Cloud
I have worked with many customers implementing their use cases for both SAP Analytics Cloud and more recently Data Warehouse Cloud. For SAP Analytics Cloud BI functionality, many customers want to use Live Connections instead of Importing Data into SAP Analytics Cloud.
SAP Data Warehouse Cloud is a powerful platform to incorporate into any Data Strategy. It is a perfect complement with SAP Analytics Cloud as well as 3rd party reporting tools to expand the sources of data that can be used for live connections as well as provide a mechanism for easy data federation using multiple data sources.
This blog comes with an accompanying How-to guide which you can access with this link: https://d.dam.sap.com/a/9YG3cah
What is Live vs Imported Data Connections in SAP Analytics Cloud?
Data is referred to as “live” when the data is retrieved from the on-premise or cloud data source upon opening or refresh of a story in SAP Analytics Cloud. Imported data, on the other hand, is replicating data from an on-premise or cloud data source into SAP Analytics Cloud.
The main advantages of using Live data are:
- There is no movement of data. It remains in the source system therefore eliminating the need to schedule or manage data in an additional application.
- Some companies have strict policies on where data can reside so live data connectivity adheres to these policies by only bringing over metadata which is transferred to a browser and encrypted in memory. No master or transactional data gets replicated from a data source.
How SAP Data Warehouse Cloud Can Bridge the Gap
Currently, SAP Analytics Cloud can connect to six types of live data sources: SAP HANA, SAP BW, SAP S/4HANA, SAP Universe, SAP BPC Embedded, and SAP Data Warehouse Cloud. (This may expand in future, but this is current state when this blog has been created.)
The following shows the Overall Data Warehouse architecture:
- The Live connection from SAP Analytics Cloud to Data Warehouse Cloud and the Remote connection options in Data Warehouse Cloud to a vast array of both SAP and non-SAP data sources provides a perfect partnership with SAC. Companies can include Data Warehouse Cloud into their overall Data Strategy to increase the number of live connections for SAP Analytics Cloud to include both SAP connections such as SAP Successfactors and non-SAP sources such as MSSQL Servers, Google BigQuery etc.
For a list of connections (and pre-requisites) that Data Warehouse Cloud currently connects to please see the following link: https://help.sap.com/viewer/9f804b8efa8043539289f42f372c4862/cloud/en-US/bffbd58c15784a62af0520f171018ded.html
- Data Warehouse Cloud also provides data federation capability in models to join SAP and non-SAP Data as well as remote data with persisted data. This would reduce the need to set up blending in SAC in each story.
- The same models built in DWC can also be utilized for 3rd party reporting tools therefore providing options to business users.
- Data Warehouse Cloud provides a user-friendly graphical SQL interface which allows non-technical business users to deploy their own custom models, including customizing hierarchies, calculations, and aggregations. They can also optionally use the Business Semantic Layer for more Business Scenario definitions.
- Other features in Data Warehouse Cloud include (not a complete list):
- An embedded Data Lake and options to connect to 3rd party data lakes
- Data Flow capability for ETL functions
- Pre-Delivered Business Content which include connections, tables, views and E/R models, as well as SAP Analytics Cloud stories. New Business Content is being added with new versions of DWC and SAC.
- DWC allows switching between remote to persisted data without changing underlying models and stories.
How to guide for an End-to-End Scenario
The following pdf document has been created to provide step by step guidance on getting started with Data Warehouse Cloud using a live MSSQL Server 2017 scenario for SAP Analytics Cloud stories.
Access to the How to Guide: https://d.dam.sap.com/a/9YG3cah
The workflow will be as follows:
It will cover the following:
- Provide information on pre-requisites for specific data sources in Data Warehouse Cloud and an example of how to implement the pre-requisites for using MSSQL Service 2017.
- Show how to create dimension and relational views and then join objects into an Analytical view that is Enabled for Consumption by SAC and 3rd Party reporting tools. Though only MSSQL Server will be used as a data source with multiple tables in the same schema, this scenario would work for objects from different data sources as well.
- Provide links with instructions on how to set up a connection from SAC to Data Warehouse Cloud.
- Provide step by step example of creating variance and additional bar charts in SAP Analytics Cloud as a final step for the end-to-end scenario.
- Show an example of monitoring the calls to the data source systems through Remote Query Monitor.
It is important to note that there are some limitations when using the SAC to DWC connection. Currently Planning scenarios are not supported either live or for acquiring data from DWC however Planning is on the future roadmap. For details on more limitations, please see the following link:
- Note: There are some scenarios and data sources which would be more suitable for persisting the data due to architecture and the volume of data which could influence story performance.