ABAP CDS in SAP BW
Introduction
ABAP Core Data Services (CDS) Views were introduced with SAP ABAP 7.40 SP05. CDS allows the creation of rather complex views that by far exceed the capabilities of database views created in SE11. Besides their role in the ABAP Programming Language, CDS Views are the technology behind the virtual data model on top of the SAP S/4HANA application tables which, e.g. is used in SAP S/4HANA Analytics.
This aspect – the role of CDS in SAP S/4HANA Analytics – frequently brings up the question whether or how CDS might be used in the context of SAP BW powered by SAP HANA or SAP BW/4HANA.
The purpose of this blog is to clarify the role of CDS in SAP BW.
ABAP CDS in SAP BW – the reporting perspective
Since CDS is used to build the Virtual Data Model in SAP S/4HANA, a frequently asked question is, whether CDS Views could also be used to build a virtual layer on top of BW InfoProviders, most prominently Advanced DSOs, which could be consumed in analytic front end tools like in the SAP S/4HANA environment. Or, whether CDS could be used to enhance a SAP BW data model with data from a Z-table. The clear answer to this is – for a number of reasons: No.
The SAP BW data model
Anyone familiar with the way SAP BW generates database tables from its high level (InfoObject based) data model, knows that SAP BW entities typically are split into multiple tables with implicit relationships managed by the BW application. In addition, many of these tables contain technical fields which are of critical importance when writing to or reading from the object. All the logic that is required to correctly interpret these fields and deliver correct and consistent query results, is part of the BW application which creates the correct JOINs and filters at query runtime. All the SAP BW modeling artefacts (e.g. CompositeProviders, Advanced DSOs) are built such that they are aware of all these aspects and relieve the user from a detailed understanding.
The SAP BW security concept
SAP BW secures data from un-authorized access via Analysis Authorizations. This is again a concept specifically designed with the SAP BW data model in mind and it differs significantly from standard filter-based database security concepts. And just as with the data model aspects above, the evaluation and interpretation of Analysis Authorizations is done in the SAP BW application. Therefore, accessing SAP BW data via SQL or OpenSQL (which is the standard way to consume CDS views), would completely bypass the security layers of SAP BW and open the way for un-authorized access, which is prohibitive in pretty much any real world scenario.
Mixed Scenarios
These aspects have since long been the main reasons why SAP does not support direct consumption of SAP BW generated tables. As you may be aware, SAP BW powered by SAP HANA or SAP BW/4HANA provide capabilities addressing specifically this point – opening up SAP BW for SQL consumption. The approach taken here is to generate SAP HANA Calculation Views from the SAP BW data model that incorporate all the dependencies within the data model and the correct interpretation of all the technical fields and creating SAP HANA Analytic Privileges from SAP BW Analysis Authorizations to ensure security.
Conversely, when it comes to integrating non-BW data (e.g. in a Z-table or Z-view) with the SAP BW data model, this is done via Open ODS Views, which – conceptually – put a “SAP BW-shell” around a table or view.
Thus, also in this environment, CDS does not play a role in SAP BW.
SAP BW Technical Content
Tracking and monitoring the operations of a SAP BW system is one of the areas, where CDS in fact is playing a role. For example, SAP BW comes with tables containing statistics on loading and querying data, status information about load requests, etc. With SAP BW 7.5 SP04, CDS based technical content is shipped that provides administrators/modelers with such information. Being built on CDS, this content is much leaner and simpler than traditional SAP BW technical content, does not require loading of the data to a SAP BW InfoProvider, or even activation of SAP BW objects.
Details about the scope of this technical content can be found here.
Consuming CDS Views in SAP BW
SAP BW can, however, leverage content built using CDS Views – most prominently SAP S/4HANA Analytics. For one, there is a dedicated Source System type ODP-CDS in SAP BW (since release 7.4) that allows to load data from a CDS View into SAP BW. This integration currently does not provide all services known from the classic extractor based data provisioning, but in certain scenarios it can be a valid option. Secondly, SAP S/4HANA Analytics content can be virtually consumed and combined with data in SAP BW via Open ODS Views. More details on this can be found here.
ABAP CDS in SAP BW – the ETL perspective
One specific use case where CDS views can play a role are the SQL expert routines in SAP BW Transformations. To simplify, for example, a complex look-up on customer specific Z-tables in a transformation, it can be very useful to define a CDS view on top of these tables and use that CDS view within the SQL export routine.
Conclusion
To summarize, CDS and SAP BW modeling have only very few touch points. For operational reporting scenarios directly on application tables – as in the case of SAP S/4HANA Analytics, but also when monitoring a SAP BW system – the CDS approach is the way to go. When it comes to building a SAP BW data model, especially a virtual data model, then SAP BW’s CompositeProviders are the right approach, possibly in combination with Open ODS View when data outside of SAP BW has to be integrated.
Thanks for the nice blog !! Very Good info !!
Hi Ulrich,
Nice blog which clarified few things. I have few questions with regards to BW/4 HANA and real time reporting in ECC envrionment (and not S/4 HANA case). In most cases, one will have EDW thus will need BW which is now on BW/4HANA. Another condition, with native HANA modelling and SLT, business are able to get real time reporting. So if one visualizes BW/4 HANA case, there also one will aspire real time reporting. So question is does BW/4 HANA with upcoming extractors, will it have real time extraction and reporting possible. It has two parts 1) real time extraction from source i.e. ECC, BW source >>> BW/4HANA system 2) real time on the fly query time logic and transformation execution for final data set to report.
I am under impression with ABAP CDS technology it could be possible. I think BW InfoProviders can be based on ABAP CDS and these base ABAP CDS (say CDS_REPORT) can be based on another set of CDS view (say CDS_ECC) or just DB table populated by BW/4 HANA with upcoming real time extractors or SLT.
Thus we can have real time BI data application and can re-use CDS_REPORT kind of CDS views in other data application. Additionally, other benefit of CDS and BW will be utilized like push down, security, life cycling etc.
Please help me understand above. In short, I want to understand what’s the way to go to build real time reporting based on ECC data (and other data source like other BW) using BW/4 HANA extraction from source and transformation from raw to infoprovider level is all real time. Is it going to be
Option 1 -> ECC >>> standard extraction ..hopefully real time >>> BW objects/infoprovider >> reporting
OR
Option 2 -> ECC >>> standard extraction ..hopefully real time >> DB table/BW object/CDS view >>> CDS view >>> BW objects/infoprovider >> reporting
OR
Option 3 > ECC >>> standard extraction ..hopefully real time >> DB table/BW object/CDS view >>> CDS view >>> reporting
I don’t think option 3 is very good as then will miss benefits of BW.
Option 4 > ECC >>> standard extraction ..hopefully real time >>>> BW object >> Transformation BW logic >> infoprovider >>> reporting
I think option 4 we loses CDS and its benefits of re-using one data model in variety of data application.
Regards,
Nitin
Hi,
I really don't understand lack of support for models built directly on tables.
Argument about BW tables can change ids or logic (that is fully on customer shoulders to assure model consistent) is irrational. Authorization? We map fields to IO consuming models in composites.
I suspects that is different reason for such restriction. Generated hana internal views from BW perspective are indexed differently than native tables, that is just assumption to justify such limitation (SIDs are being used to index on hana site???).
We have some stability issues with our development and SAP support refuse to analyze our issues. Models are compiled but don't work properly.
When you have enterprise licence, you can model on "raw" tables, what is difference.
Hope SAP will change it mind, for now we have to reconsider our models and put on hold further "virtual" transformation.
Regards,
Dominik
Dear Ulrich,
Thank you for the blog.
We got to it, because we are discussing internally which should be the way to implement inside BW, virtual transformation layer, like on the fly calculations (at query runtime).
If I understand you correctly, you are positioning the CDS view as the reporting layer, but what about if it is connected to an Open ODS view to force BW security to be used? Also, ADSO can be reduced to 1 single table, so I don’t see the difference to S4H approach.
What object do you propose to build in BW real-time transformations/logic to be executed at runtime (at query execution for instance)?
I have few comments regarding my question:
Thank you very much.Alfonso
Best regards,
Hi Alfonso,
I am currently facing the same challenge: I am thinking about virtual transformation layer in order to enable on the fly calculations. Did you implement already a solution? I would be interested to hear about your experiences and the selected approach.
Thank you very much and best Regads Thomas
Thanks, Ulrich It gave me some insights regarding where CDS views going to be used where the scope is narrower. From Nov-2016 is there CDS views with context to BW scope Changed ?
Hi Ulrich!
Nice Blog!! Could you tell me if we can extract CDS views built on top of Cluster tables in to BW?I built one on CDHDR and CDPOS but could not find it in BW.Please suggest.