Skip to Content
Product Information
Author's profile photo Maximilian Paul Gander

Introducing the Bi-Directional Integration of SAP Data Warehouse Cloud and SAP Analytics Cloud for Planning

Introduction

SAP Data Warehouse Cloud and SAP Analytics Cloud are better together, and we are proud to announce another milestone in their integration journey.

First, SAP Data Warehouse Cloud and SAP Analytics Cloud are already today the best suited couple for Analytical use-cases. This is mostly due to the well-known live-connection and the re-use of model semantics (such as currency conversion, multi-language support, etc.).

Second, we are about to establish a strong Planning integration between the two solutions. Therefore, we recently delivered a first important milestone in this direction: the bi-directional integration between SAP Data Warehouse Cloud and SAP Analytics Cloud.

Scenarios

Our new release enables you to load fact data and master data from Data Warehouse Cloud to SAP Analytics Cloud. Similarly, you can seamlessly retract fact data, master data, and audit data from SAP Analytics Cloud models and use it in SAP Data Warehouse Cloud.

Most importantly, this enables you to use actual data from SAP Data Warehouse Cloud in your planning tables in SAP Analytics Cloud. Additionally, you can join plan and actual data from multiple sources in common views in SAP Data Warehouse Cloud that you can then use for live reporting or any other kind of downstream processing of your plan data. You can also meet corporate requirements to store all steering-relevant data in one data warehouse as a single source of truth.

How to

Architecture

It is important to understand that the use case of bidirectional integration is enabled by two public APIs:

  • SAP Data Warehouse Cloud provides a public OData API to pull data from DWC and integrate it into SAP Analytics Cloud using the OData Services connection.
  • SAP Analytics Cloud provides the Data Export Service (DES) API (generally available with QRC2.2022) to pull data from SAC and make it available in Data Warehouse Cloud using remote tables or data flows.

In the following paragraphs, we will dive into the details of both APIs and how they can be used to transfer data between both solutions.

The following image already gives a good high-level overview of our scenario:

Data transfer from SAP Data Warehouse Cloud to SAP Analytics Cloud

SAP Data Warehouse Cloud Public API for Data Consumption

The SAP Data Warehouse Cloud Public API can be used to replicate fact and master data from SAP Data Warehouse Cloud into SAP Analytics Cloud for planning purposes. By using this API, you can authenticate against SAP Data Warehouse Cloud and get access to its data.

In particular, the API has the following key characteristics:

  • It supports deployed Data Layer entities which are marked as “expose for consumption”. This includes views of semantic type Analytical Dataset and Dimension.
  • It supports standard OData v4 query parameters ($select, $filter, $top, $skip, $orderBy, $count, etc.).
  • It supports business user access and authentication via OAuth 2.0 authorization c

Moreover, we strongly recommend that the data in question is either replicated (via remote table replication or data flow) or the respective view’s data is snapshotted in SAP Data Warehouse Cloud.

More details on the API can be found here.

Technical set-up

To transfer data from SAP Data Warehouse Cloud to SAP Analytics Cloud, the following steps are required:

  1. It all starts with a Data Layer artefact of your choice, and which contains data J. Make sure it is deployed and marked as “Exposed for Consumption”.
  2. In SAP Data Warehouse Cloud, create an OAuth Client with the following specifications:
    • Purpose: Interactive Usage
    • Redirect URI: Copy-paste the re-direct URI which is highlighted in the OData Service connection creation dialog in SAP Analytics Cloud (hence our recommendation to start simultaneously with step 3).

  1. In SAP Analytics Cloud, create an OData Service connection with the following specifications:
    • Data Service URL: https://<your-dwc-tenant-url>.cloud.sap/api/v1/dwc/consumption/relational/<space-id>/<asset-id>/
    • Authentication Type: OAuth 2.0 Authorization Code
    • OAuth Client ID, Secret, Token URL and Authentication URL: Need to be filled with the OAuth Client information from step 2.

  1. Now that the connection is successfully established, there are multiple options for acquiring the data into SAP Analytics Cloud:
    • Option1: Load data into an existing model
    • Option 2: Create a model from scratch via the OData Service connection

Note that the data acquisition limits of SAC apply, which are documented here. In particular, Classic Account Models support up 800k rows per OData model import, while the New Model supports up to 10m rows.

Currently, on SAP Analytics Cloud side, one OData Service connection needs to be set up for each SAP Data Warehouse Cloud view that you want to connect to.

Data transfer from SAC to DWC

SAP Analytics Cloud Data Export Service (DES)

With QRC2.2022, we generally released the new SAP Analytics Cloud Data Export Service (DES) to all SAP Analytics Cloud customers. In a nutshell, this is a generic OData-based pull API that can be triggered from other applications and platforms, including 3rd party tools. There is no dedicated UI for this functionality as the API simply facilitates extraction of SAP Analytics Cloud planning models from an external platform.

The API is comprised of two services: the Administration service and the Provider service. The first extracts a list of models on your SAP Analytics Cloud system, and the second one retrieves information about a specific planning model. It has the following key characteristics:

  • Extracts fact data, master data and audit data.
  • Support of basic data extraction qualities with FULL data extract (delta capability planned for a future release) and basic filtering capabilities
  • Highly performant
  • Business & Technical User Access
  • Available for Cloud Foundry Tenants

The new Data Export Service enables a wide range of possible scenarios. It will mostly be used to simplify the downstream processing of plan data that was generated in SAP Analytics Cloud.

Technical set-up

In SAP Analytics Cloud, you need to navigate to the App Integration Administration to set up the OAuth client as per the following video. This will give you the Token URL, OAuth Client ID and Secret that you will need later on.

In Data Warehouse Cloud, you then access the connection management for your space to create a CDI connection using the details that you obtained from the SAP Analytics Cloud App Integration Administration. Generic OData connections are an option as well, but not recommended for this use case.

 

Important to note is the URL for the Data Export Service: https://your-tenant.url/api/v1/dataexport/administration

As you can see in the video, you have the possibility to use a Data Provisioning Agent to enable the use of Remote Tables. If this is not done/available, you can use data flows to replicate the data into DWC.

You see there are different ways of consuming data. We ask you to consider these recommended options:

1) Remote Table-based snapshot replication

  • 1:1 replication of SAP Analytics Cloud data with option to filter data and project columns
  • Requiring a DP Agent for CDI based connectivity
  • Supports delta export using real-time replication option (available since Q4 2022)

2) Data Flow

  • Full Replication of source data with ability to use ETL functionalites of Data Flow
  • Not requiring a DP Agent forCDI operator-based connectivity

What will be next?

An exciting journey lies ahead of us with the destination to offer live planning in SAP Analytics Cloud on top of SAP Data Warehouse Cloud. So, the future holds real-time steering, continuous automated planning on semantically enriched internal and external data, and drastically simplified modeling efforts for planning.

The bi-directional data transfer is the first step into this direction.

Furthermore, we are working on even better integration of SAP Data Warehouse Cloud and SAP Analytics Cloud for live analytics use cases.

Further information

If this blog post aroused your interest, check out this further information:

 

Assigned Tags

      13 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Afshin Irani
      Afshin Irani

      Hi Maximilian Gander,

      A big thank you to you and the Product Team, this was very anticipated feature and is a key piece in solving jigsaw of SAC planning.

      Regards

      Afshin

      Author's profile photo Tommaso Ferri
      Tommaso Ferri

      Hello Maximilian,

       

      Very interesting and useful guide!

      I was wondering if you can share any details on how to create a DP agent to connect SAC and DWC.

      I was able to find guides to connect DWC to on-prem HANA systems but nothing on DWC-SAC connection.

      Can you help me with that?

       

      Regards,

      Tommaso

       

      Author's profile photo Maximilian Paul Gander
      Maximilian Paul Gander
      Blog Post Author

      Hi Tommaso,

      The agent actually always is installed on prem. You need to register the CloudDataIntegrationAdapter adapter in the local agent setting.

       

      BR
      Max

      Author's profile photo Frederik Ulf Hansen
      Frederik Ulf Hansen

      Hi @Maximilian Paul Gander

      Thanks for a good blog. As I understand it, it is possible to live connect to ABAP cds views from SAC and use these VDM's in Fiori apps in S4HANA. However, is this also possible using DWC as a source of SAC? Both for live data and imported data. As i understand it this was not possible in 2020 but has this changed now or is it in the roadmap?

       

      Thanks in advance

       

      BR Frederik

      Author's profile photo Maximilian Paul Gander
      Maximilian Paul Gander
      Blog Post Author

      Hi Frederik Ulf Hansen

      I must admit I am not familiar with your use case. Do you have more info on the workflow?

      BR
      Max

      Author's profile photo Ankita P K
      Ankita P K

      Hi Maximilian Paul Gander

      Can you let me know where i can follow next updates for this topic ?

      It seems bi-directional is just the beginning of resolution from SAP.

      BR,

      Vu

      Author's profile photo Maximilian Paul Gander
      Maximilian Paul Gander
      Blog Post Author

      Hi

      Suggest to keep an eye on the SAP Community. Maybe it would make sense to follow me here and connect via LinkedIn.

      BR
      Max

      Author's profile photo Torsten Wirth
      Torsten Wirth

      Hello,

      let me conclude this because I don't get the strategy (if there is any) from SAP behind that.

      SAP had historically two planning solutions which are BW-IP (integrated) and BPC (not integrated) - with several steps they made one of it which is called BPC today (and even consolidation is included). Masterdata has to be only replicated once. So all data was available in one system apart from ERP. So redundandancy was limited. For me that makes much sense.

      After that SAC was introduced as a mixture of some very! basic DWH features and planning, and predictive in a frontend tool (which needs even more replication of masterdate - eg 3 loads for a hierarchy instead one in BW). In the beginning SAC was mostly designed for uploading sort of Excel files and mix it up. Even today there are a lot of feature gaps with live connections also with SAP sources.

      At some point it seems to be that someone at SAP though "Well that functionality is not enough let's introduce a real cloud DWH solution" and DWC came up but "Oh wait now planning is in the SAC and reporting data is in DWC. What a mess. And additionally there are a several functionalities which we had in BW previously which are now in SAC. We will not built them again in DWC."

      And the solution for get things together is an api to do another replication between SAC and DWC?  Really? this reminds me on IT from seventies. playce something at this fileserver, load of from there and so forth. Integration is something completely different.

      So let's recall SAP had a solution with planning, reporting and consolidation at one place (called BW/4 with BPC 2021 today). Now we need three different tools for the same thing which a lot of redundancy, no single point of truth and a lot of replication. Additionally in terms of FI/CO data has to be written back to ACDOC* tables. And the price for licensing that combination is way higher with a lot of feature gaps

      And all three products are running with HANA DB. It should the easiest thing to just load the data do DWC (once) and just access it with SAC without any replication at all (expecially for masterdata - that would be integration, less products and one storage location would be even better).

      To sum it up the abovemnentioned feature is not even needed with the BW/4 + BPC solution which is miles ahead because someone seems to have thought about that before designing it.

      Will we see such an "innovation" in future or will be a lot of syncronization in several ways and redundancy the best level of integration SAP is delivering with the new cloud products?

      Where is the single point of truth? Basically it's a mixture of SAC, DWC, S/4, DWC. I see no concept or plan there.

      In my opinion we have now the following situation:

      • DWC = Warehouse without planning and consolidation and if you migrate BW/4 additionally the bridge so it's a mixture of BW/4 and DWC technology and a lot of feature gaps
      • Central Finance = Basically a DWH for FI / CO
      • SAC = Wannabe Warehouse with good frontend + Planning without reporting Data and lot's of replication
      • S/4 = Old tech Warehouse (7.5) or CDS (which means only live) but only for one system
      • Group Reporting in S/4 as a replacement for consolidation

      So in fact the integration is way worse than before. I see no strategy apart from a lot of tools and no concept and all this products are developed independently (at least at first) without getting all this into sync.

      Author's profile photo Maximilian Paul Gander
      Maximilian Paul Gander
      Blog Post Author

      Hi Torsten,

      Thanks for sharing your opinion and concerns. I think there is one very import paragraph in my blog post:

      "An exciting journey lies ahead of us with the destination to offer live planning in SAP Analytics Cloud on top of SAP Data Warehouse Cloud. So, the future holds real-time steering, continuous automated planning on semantically enriched internal and external data, and drastically simplified modeling efforts for planning."

      The APIs for data replication were just the first step to integrate SAC and DWC for planning use cases. It is our strategy to closely link SAC and DWC so that data does not have to be replicated anymore. This includes both fact and master data. Plan data shall be stored in DWC spaces only and master data from DWC shall be re-used.

      Furthermore, SAC is not supposed to be a data warehouse which is why the integration with DWC is key.

      Max

      Author's profile photo Torsten Wirth
      Torsten Wirth

      Hallo Max,

      Thank you for the reply.

      "Furthermore, SAC is not supposed to be a data warehouse which is why the integration with DWC is key."

      I totally agree on that but at the moment we have consultants in house which are trying to tell us that S/4 + SAC is the future without any DWH which could be true with just one S/4 but not with a landscape of several source systems like we have but that's another topic.

      "An exciting journey lies ahead of us with the destination to offer live planning in SAP Analytics Cloud on top of SAP Data Warehouse Cloud. So, the future holds real-time steering, continuous automated planning on semantically enriched internal and external data, and drastically simplified modeling efforts for planning."

      I noticed that part but that does not clarify what will happen in future and when. In my opinion replication between SAC / DWC is not a good way because it's redundandency with expensive HANA DBs and the risk of ansynchronous data. It would be enough to store the data only in DWC in my opinion (which would be the same concept in BW/4 + BPC) and just use this model in SAC without replicating it (basically the same concept of a live connection but for the model use in SAC).

      It seems to be I have to wait what the journey will bringt but what maybe will come in future does not help very much If you are starting a big greenfield project at the moment like we do and you have to make decisions now. At the moment I still feel more save to build on something which is plannable and at the moment this is still BW/4 + BPC.

      SAC / DWC planning does not seem to be an investment in the future as this is in continious flow at the moment. We are already using SAC but at the moment for reporting and in small parts for live planning connection to BPC.

      Author's profile photo Maximilian Paul Gander
      Maximilian Paul Gander
      Blog Post Author

      Hi Torsten,

      I cannot comment on what is the right architecture for you as I am not aware of your landscape and requirements. Generally, I think that an architecture including a DWH can still make sense if you need to combine data from multiple applications/systems and want to do reporting and planning on combined data.

      Let me also give you more details on what we are currently working on with regards to SAC/DWC integration for planning: In the first step, you shall be able to deploy your SAC planning models in a DWC space. So, modeling (design time) happens in SAC but the runtime artefacts (fact data tables, master data tables...) are stored directly in DWC. So, you have plan data available for reporting in real-time and you can add further compute power etc. to your planning scenario via DWC. This is planned for HY1 2024. We are already building that but there are some dependencies. I think that goes into direction of the ideas you mentioned in your last reply. We are also looking into a migration of existing SAC models to this new architecture but I cannot promise that as of now.

      In subsequent releases (may take another half year or year afterwards, not possible to say anything wrt timeline as of now), we want to reuse DWC dimensions and virtually access other ACT or plan data to create even more value for the planning modeler and make replication rather the exception.

      BR
      Max

       

       

      Author's profile photo Torsten Wirth
      Torsten Wirth

      Hello Max,

      Thank you for the additional information. That is a good base for argue that topic.

      One other thing: This is maybe not your topic (and yeah it's offtopic in this contenct) but if planning will work like you described that is one painpoint less. The only biger issues I still have with DWC:

      • Transports (I really like the change recording in ABAP which will result in synchronized systems with a setup like dev/qa/prod - in SAC we are using DEV/QA and PRD (2 systems) and we have the same problem there - change recording would be very good - as far as I know there is nothing like that
      • 2 System setup with kind of system copy like in on prem systems to get test data (well with a two system setup there are exclusions needed, a three system setup is necessary to avoid most of the eclusions apart from sources. But I think most customer will have max two systems

      BR

      Torsten

      Author's profile photo Maximilian Paul Gander
      Maximilian Paul Gander
      Blog Post Author

      Hi Torsten,

      Thanks, I will forward that internally.

      BR
      Max