Product Information
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:
- 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”.
- 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).
- 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.
- 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:
- Blog post – Using the SAC Data Export API with Data Warehouse Cloud and Data Intelligence
- Blog post – Using the Data Warehouse Cloud OData API with SAP Analytics Cloud
- Blog post – SAP Analytics Cloud Export Data API Sample Scripts
- sap.com – Export Data with the Data Export Service Open API
- sap.com – Browse Data via the SAP Data Warehouse Cloud Public API
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
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
Hi Tommaso,
The agent actually always is installed on prem. You need to register the CloudDataIntegrationAdapter adapter in the local agent setting.
BR
Max
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
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
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
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
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 merged solution of this two which is called BPC today (and even consolidation is included). Masterdata has to be only replicated once. All data was available in one system apart from ERP. 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 getting things together is an api to do another replication between SAC and DWC? Really? this reminds me on IT from seventies. place something at this fileserver, load data from the file sever and so forth. Integration is something completely different and does not involve storing the data in x versions at different places which are potentially out of sync.
So let's recall SAP had a solution with planning, reporting and consolidation at one place (called BW/4 with BPC 2021 today). Today we need three different tools for the same thing (with latest SAP tools) 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
All three products (SAC, DWC, Group Reporting) 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 integration "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:
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.
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
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 we have to wait what the journey will bring 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.
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
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:
BR
Torsten
Hi Torsten,
Thanks, I will forward that internally.
BR
Max
Fully right Torsten, the strategy is unclear.
Bi-directional replication jobs are a first step... that customers need to throw away later.
@Max, If I understand your comments correctly, only after H1 2025 (likely 2026) -> the solution is only expected to be more or less in line with desired end-state architecture (full DWC/SAC re-use and sharing of actuals&plan objects).
And no migration of existing SAC planning content can be guaranteed. Isn't it easier to tell customers they should wait 4 years instead of convincing them today & force them into painful migration/re-implementation?
Hi Alex,
Well.. I cannot commit to anything which has not been elaborated technically yet. This is why I am not promising migrations right now. However, as a product manager, I am positioning it as a very important requirement so we will do our best to make the migration as easy as possible.
BR
Max
Hello Max,
one follow up question:
SAC can access data which is stored in Datasphere in future via direct consumtion? Is this only valid for native Datasphere Designed Artefacts or also for data which is stored in bridge?
Or is this working the other way round? The model has to be designed in SAC and afterwards filled with actual data in Datasphere?
E.g. if we would migrate our BW/4 to Dataphere with Bridge all current HCPR / ADSO are in bridge. Which means the data is stored in Datasphere.
Is it planned that SAC can directly access this data in planning at least for actual data in combination with planning data? E.g. allowing comparison of actual vs. plan or copy actual to plan?
If not: Is there a migration to a Datasphere or does this have to be rebuild manually from scratch? I think this is maybe a bigger topic because classic extractors which are working in bridge also have to be replaced by CDS view based extractors in native Datasphere scenarios as far as I know (which do not have the same content coverage than the classic ones which also would lead to other models and a complete redesign).
BR
Torsten
Hi Max,
Live Connection with SAP DWC & SAC doesn't allow usage of Smart Features. With this bi-directional connection, where the data is replicated from SAP DWC to SAC.
Does that mean that it is considered as acquired connection and SAC will be able to use Smart Features on the data being used for SAC Planning?
Linch
Hi Linch,
Yes, with the connection described in this blog post, smart features are supported.
BR
Max
Hi Max,
thanks for the great blog.
I tried to create a new planning model using the OData API as data source. It was succesful with the classic data modell but when I tried to remigrate to the new data modell, create new import setting and updated, it was not possible. The error message suggested that the allocation information for the target dimension A could not be found.
"Import fehlgeschlagen: Es konnten keine Zuordnungsinformationen für die Zieldimension A gefunden werden.
Korrelations-ID: 95925D20-C4D2-4AC6-8180-136DD408C2BB2C"
Have you encountered this problem before and know how I can solve it? Or can you recommend any blogs that can help me with this error?
Sincerely
Chaima
Hi,
It should work for the New Model as well and I have not encountered the error before. I recommend raising an OSS message.
BR
Max
Thank you for the step by step procedure explained to integrate planning data into DWC. However even after following the steps I am getting error while validating the connections: (Data transfer from SAC to DWC)
- Data flows are enabled.
- Remote tables can't be used because of errors in the connection.
Hi,
I cannot tell without seeing the set-up etc. Please open a ticket.
BR
Max
Thanks for the blog.
One quick question. the connection is a system-to-system connection, but how would we configure principal propagation so that users in SAC an only access with their session user instead and only work with data their own users have access to in Datasphere.
Regards,
Haaris
Hi Haaris,
since all data is replicated into the SAP Analytics Cloud model, you have to design the authorization in SAP Analytics Cloud using Data Access Control or model data privacy/roles.
BR
Max