CDS views provide a very flexible way to create a transient query to run on Analytic Engine. There are some basic rules to follow in data modeling.
Analytics on ABAP CDS View Data Models
The picture below helps to understand the idea behind it:
As CDS query runs on Analytic Engine (in BW, it is called OLAP Engine), the data model should still align with traditional BW assumptions. CDS views just provide a faster, easier, more flexible way to build up such a data model.
The upper part of this picture shows the traditional objects in a typical BW system: To report on a BW system: InfoProivders are built by using Modeling Tool for BW on HANA, or RSA1 for BW on other DBs. Then BW queries are built on these InfoProviders by using Query Designer, either Bex Query Designer or Query Designer in Modeling Tool.
It is similar for a transient query to run on the Analytic Engine in a non-BW system such as S/4HANA. The lower part of the picture shows this: InfoProviders should be built by using CDS views, then CDS queries are built on top of it by using CDS views, but with different annotations. (Note: planning are not supported in customer defined CDS. See Note 2874534)
CDS views are just a new tool to build the data model for Analytic Engine. The only difference is: with traditional tools like RSA1, Query Designer or Modeling Tool, the boundary of different objects are well defined in the tools. The tools guide end users to build a valid model. However, CDS views is much lighter tool with strong capability, by reducing the ETL (Extraction, Transformation, Loading) effort, a data model can be built very conveniently by typing some CDS scripts. But the boundary of the objects are blurred. The explicit restrictions defined in traditional BW tools become implicit rules for users to follow in order to build a valid model. Although there is no terms like 'InfoProvider', 'Query' in the CDS transient objects world, nevertheless users still need to bear this picture in mind while modeling a business scenario. With the concept, you will understand and know: where to define what .
Here are some implicit rules in data modeling, you may get various error when activate the CDS view if they are broken:
- Analytics on ABAP CDS is only supported on HANA system.
- Unions and Joins (including associations, value help) can only be defined in CDS views of InfoProvider layer (with annotation @Analytics.dataCategory:#...). With only one exception: valueHelp association for a CDS query parameter ( which could be unavailable in the provider view) can be defined in CDS query view.
- CDS queries (with annotation @Analytics.query:true) can be extended, but only query elements can be added into the extended view. Additional unions and joins need to be defined in InfoProvider layer.
- Some BEX query features are not supported in CDS query yet. In this case, instead of define a CDS query, you can define a BW query on the CDS InfoProvider in Modeling Tool to use these OLAP features. Read more.
- CDS parameter only supports single value. It is input mandatory. The supported max length of a parameter name is 29.
- Filters should be defined in a query CDS view. An InfoProvider CDS view should not contain parameters except for currency conversion. Parameters on InfoProvider level don't support semantics. Cube CDS views with parameters are mapped to HANA-table functions instead of HANA views. This might lead to significant performance impact .
- Don't use a BW InfoObject generated data element in a CDS view, always use the basic data element which is directly defined in DDIC (SE11). Generated data element will cause problems in life cycle management. Because generated data element is created when activating the InfoObject. If you use the data element n a CDS view and transport the CDS-view and the newly created InfoObject at the same time, an error is raised in the target system. DDIC objects (that is CDS) are activated before the BW Objects activation. So the generated data element will not be available.
- If a join (or an association) is defined on fields with domain using alpha conversion or data type NUMC, it must:
a) make sure both fields in two tables have exactly the same length
b) the data in both fields follows the alpha conversion rule: for example, for a char(2) field, there is no '1' stored in this field, it should be stored as '01'.
Otherwise the data model will run into various issues and get unexpected query results. See Note 2789339.
- Association to a Dimension view or text view with parameters is not supported.
- Don't define 'group by' clause in a CDS cube, dimension or query view. Analytic Engine will automatically generate 'group by' clause in the open SQL during query runtime according to the query navigation status.
CDS reporting needs to define at least 2 CDS views as below:
For data stored in cluster tables or other encrypted data, a tool is needed to extract data into a transparent table for embedded reporting.
InfoProvider CDS Modeling
Here are more details about modeling considerations in different situations:
- InfoObject (@Analytics.dataCategory: #DIMENSION and measures/key figures)
- InfoProvider (@Analytics.dataCategory: #CUBE)