CDS based data extraction – Part I Overview
Anyone interested in the state of the art extracting data in the SAP universe and beyond? This blog (series) is meant to provide a comprehensive overview of the topic, highlight the main features but also make sure you understand the meaning and implication of all available annotations related to the topic. I am aware of the fact that various documents/blogs have been published on the topic already, but for sake of completeness I will repeat parts here which you might already know.
I will update this blog series upon new developments, changes etc., so you will always get the latest news.
Quickly jump to
Part 2 (delta handling) here
Part 3 (misc topics like hierarchy extraction, testing and FAQ) here
Let’s get started…
While this blog post is focused on the Core Data Services (CDS) flavor based extraction from SAP S/4HANA Cloud Edition (CE) to SAP BW and SAP BW/4HANA please, be aware that the examples explained here are meant to be seen in a wider scope, finally provisioning all SAP solutions, may it be cloud or on-premise, with data warehouse grade extraction capabilities. This is happening in the context of the SAP Cloud Data Integration (CDI)
Possible Sources (aka Data providers):
- SAP S/4HANA (CE/OP)
- SAP Marketing Cloud
- SAP Fieldglass
Possible Targets (aka Data consumers):
- SAP Analytics Cloud
- SAP BW/4HANA, SAP BW
- SAP Data Intelligence (fka SAP Data Hub)
- SAP Data Warehouse Cloud
Here we go…
If you are familiar with an SAP S/4HANA system you surely will have come across CDS views as part of the Virtual Data Model (VDM), may it be in the context of operational analytics, (planning) transactions or other. To harnish the power of SAP HANA under the SAP S/4HANA, SAP has introduced CDS modeling and has spent a great deal of effort into creating CDS data models on top of all applications consistently reflecting and exposing all entities.
Why not use this as a base for data extraction, similarly to the way in which extractors have worked before?
Some time ago SAP has released CDS view annotations which enable you to use a CDS view as an extractor aka DataSource on BW side.
You can find them in the @Analytics section in the SAP help documentation. Starting with SAP S/4HANA CE1905, SAP has started rolling out extraction enabled standard CDS views and has been extending the coverage since.
In an SAP S/4HANA system CDS based extraction is handled by the Operational Data Provisioning (ODP) framework, the context is called ODP – ABAP CDS Views (ODP_CDS). More info on ODP can be found in Rudolf Henneckes blog
On BW side your source system will need to be connected in context ABAP CDS Views (ODP_CDS).
Below you can see the architecture in detail
The enablement of ABAP CDS views for extraction has been available since SAP S/4HANA OP1808
Technically speaking this requires…
on provider side:
- SAP S/4HANA (with integrated software component SAP BW 7.50 >= SP5)
- SAP BW/4HANA 1.0 >= SP01 (loading from a “BW” to a “BW” sytem)
- SAP BW 7.50 >= SP5 (loading from a “BW” to a “BW” sytem)
on consumer side (using the ODP Source System with Context ODP_CDS)
- SAP BW > 7.3x
- SAP BW/4HANA 1.0 >= SP00
You can find more info on the ODP availability in SAP note 2481315.
One last sidetracking before we start to get into the details:
“How can I find out, which CDS views are available for data extraction?” As I heard this question more than once and the answer had been well hidden in part III of this blog series, I will add it here as well.
Starting from SAP S/4HANA 2020 and SAP S/4HANA Cloud Edition you can use the CDS view I_DataExtractionEnabledView to identify CDS views available for data extraction.
Let’s dive into the details now.
A CDS view can be enabled for data extraction by just adding the following annotation
@Analytics.dataExtraction.enabled : true
After adding this annotation, the CDS view is available for extraction by the ODP framework and visible for its data consumers.
System set up and Authorizations
Well one step back, it will not be visible by default for the data consumers. You first will have to have the proper authorizations in place for the communication arrangement / remote user.
SAP S/4HANA CE
In case of an SAP S/4HANA CE system, this comes out of the box when setting up the communication arrangement ( SAP_COM_0042) for the source system connection as described here -> Details -> Set-up instructions.
Please note: contrary to the SAP S/4HANA OP edition, in SAP S/4HANA CE you are (for now) only able to consume CDS views enabled for extraction by SAP and are not able to create custom CDS views enabled for extraction or extraction enable SAP delivered CDS views. In case you are missing a particular view that you need to have enabled for extraction, please make use of the SAP S/4HANA Cloud for CDS View Extraction Enablement campaign.
SAP S/4HANA OP
In case of an SAP S/4HANA OP system, please follow the instructions as described in SAP note 2716363 for your remote connection user (“ALEREMOTE” if you will). For the big picture concerning ODP authorizations, please have a look at note 2855052 as well. One side note regarding the authorizations for the remote connection user. The standard CDS views, enabled for extraction, that SAP delivers are bound to stability contract Contract for system-internal use (C1). You can find out more on the stability contracts for CDS views in the SAP help documentation here.
You need to make sure to set the authorizations for the connection user accordingly to only have these “C1 released”-CDS views displayed and be used as DataSources.
For customer created CDS views the authorizations need to be maintained in a similar way. It is highly recommended
- to apply naming conventions for custom CDS views
- to work with the API release state for these CDS views as described here
This way you will be able to only expose the CDS views to consumers that are meant for extraction.
SAP Data Intelligence (fka SAP Data Hub)
As depicted in the architecture above, leveraging SAP Data Intelligence as a consuming client is possible as well. For details please have a look at the detailed blog on this scenario by my colleague Phillip Parkinson: Part 1 on the setup and Part 2 on the CDS view consumption.
Classification of CDS views
Furthermore you are encouraged to classify your CDS view, i.e. are we talking about transactional data, attributes, texts, or hierarchies? This will also help the consuming applications to correctly use the view.
specifying a value for
- #FACT for transaction data
- #DIMENSION for master data attributes
- #CUBE (only to be used in exceptional cases)
or specifiying a value for
- #TEXT for text views
- #HIERARCHY for hierarchy views
With these steps in place you have a first easy extraction enabled CDS view, in full mode.
In full mode, all available data is extracted in one data extraction run. For potentially smaller data volumes (~ < 1,000,000 data records) without any complex logic and load time considerations, a full extraction can cover the most straightforward cases.
Apart from the number of records, the breadth of the CDS view needs to be taken into consideration as well, as both factors will determine load volume and duration. Full extraction can mostly be applied for master data and text loads. For the update mode full extraction, no further annotations need to be assigned to the CDS view.
Please note that CDS views with (input) parameters only support full extraction.
After that you will be able to find this CDS view on SAP BW side, i.e. create a DataSource. Please note that you will need to search for the technical SQL view name (@AbapCatalog.sqlViewName) of the CDS view, not the CDS view name.
(Update October 2022): With the advent of CDS view entities some time ago already with all new shiny features, please be aware that CDS view entities do not need to carry the @AbapCatalog.sqlViewName anymore. Hence in case of a CDS view entity the name to search for will be the CDS view name itself. For classic CDS views it will be the @AbapCatalog.sqlViewName.
In case of the classic CDS view for Sales Organization, you will need to search for CSDSALESORGDX and not for C_SalesOrganizationDEX.
CDS View in source system:
Data Source view on SAP BW(/4HANA) side:
Right-click on your ODP_CDS source system in the SAP BW modeling tools. In case you do not see the New -> DataSource option please make sure you have the latest version of the BW Modeling tools installed .
Choose option Proposal from Operational Data Provider
Search for the sqlViewName of your CDS view
Provide an application component and a Description. The DataSource Type is derived from the @Analytics.dataCategory/@ObjectModel.dataCategory defined in the CDS view.
Voilá, there is your DataSource.
Let me guess your next question: “What about delta handling?” As you could already spot in the architecture overview, there is inbuilt delta handling support, namely:
- Delta based on Date/Timestamp
- Delta based on Change Data Capture (CDC)
To be able to cover all the details about delta handling in a digestible manner, I have put the delta topic into a separate blog post.