CDS analytical projection views are a new type of CDS projection view, available since SAP BTP ABAP Environment 2111. They are designed for modelling analytical queries within a CDS data model and they improve on the previous version of CDS Analytical Queries. Read this blog post to learn about the advantages and usage of CDS analytical projection views.
CDS analytical projection views improve development efficiency when designing analytical queries within a CDS data model. They offer several advantages over the previous version of CDS Analytical Queries:
Before we look at the syntax of CDS analytical projection views, we need to understand the underlying analytical data model. Analytical models are recommended for reporting purposes when you need to aggregate data to expose results across different areas (for example by location, time, and product type). These models are conceived over Facts and Dimensions and these views contain the basic data used to conduct detailed analyses and derive business values.
Imagine as an example a sales report that provides results based on product category, date, and location. The Fact is the product itself and it holds values that we can measure (for example number of sales and total amount of sales). The filters by location, time, and type are the Dimensions and these dimensions can have Attributes or Texts attached (for example address and product description). Each dimension can also have a Hierarchy to map hierarchical structures such as region, country, state, county, and city. These hierarchies allow for roll-up and drill down of aggregated data, for example Toronto and Vancouver can be rolled up as Canada. When we connect all dimensions and hierarchies together, we have a Cube and consequently an analytical model ready for consumption.
Analytical data models can be multi-dimensional and are often called Star schema.
The fields of an analytical cube view are Analytical Measures and Analytical Dimensions.
An Analytical Measure is a field of an analytical cube view that contains quantifiable data that can be calculated and aggregated, for example, the total price of all sales orders. Measures can have a reference to a unit key or currency key.
An Analytical Dimension describes a characteristic of the view's subject that can be used for grouping or filtering. For example, if a view describes a sales order, the following columns can be dimensions: customer, date, region. Dimensions can have a foreign key association to a dimension view.
The following blog post provides an example for how to design an analytical data model in ABAP CDS: Create an analytical model based on ABAP CDS views | SAP Blogs.
Further details on the analytical Star schema are provided in the developer guide for the Business Technology Platform: Analytical Data Modeling & Live Data Connection to SAP Analytics Cloud - SAP Help Portal.
A CDS analytical projection view is built in top of an analytical cube and models an analytical query within the CDS data model. One cube view can serve as basis for multiple CDS projection views.
The following CDS analytical projection view demonstrates the syntax elements of an analytical projection view.
Syntax elements of an analytical projection view:
In analytical projection views, path expressions can be used to include texts, display attributes, or texts belonging to display attributes in the analytical query. In an analytical cube view, such fields might be text associations or foreign key associations. In a CDS dimension view, such fields can be text associations.
Language dependencies are implemented using the keyword LOCALIZED. The annotation @ObjectModel.text.element: '<text_element>' defines the relationship between identifier elements and the respective text. The text is automatically filtered to the current logon language.
An example for a language-dependent text element is provided in the listing above:
For more details on how to get language-dependent text in CDS projection views, see the development guide for the ABAP RESTful Application programming Model Getting Language-Dependent Text in Projection Views - SAP Help Portal.
Temporal dependencies in CDS analytical projection views are implemented in the following format:
A case distinction in the element list of an analytical projection view can either be a selection-related case expression (also called restricted measure) or a formula-related case expression.
A restricted measure allows you to further filter an analytical measure and limit the result set along a certain dimension. In the example above, the cube view defines the field amount_sum as an aggregation of all sales orders:
The restricted measure in the analytical projection view filters this measure to aggregate only sales orders with the lifecycle status set to a value between A and B.
A restricted measure must always have the following structure:
CASE
WHEN cds_cond
THEN result
END ...
There must be exactly one WHEN branch; no ELSE branch is allowed.
The condition cds_cond specified after WHEN can use only fields that are classified as dimension in the underlying cube view. The Boolean operators AND and OR are allowed to combine multiple logical expressions. Identical fields must be combined with OR, different fields must be combined with AND. NOT is not supported.
result specified after THEN must be a field of the underlying cube view specified as an analytical measure, or a literal of a measure-like data type. A literal is allowed only if the element is annotated with AnalyticsDetails.query.onCharacteristicStructure: true and when this annotation is used, a literal is mandatory.
A formula-related case expression allows you to further process an analytical measure. It must always be structured as follows:
@Aggregation.default: #FORMULA
CASE
WHEN cds_cond
THEN result1
ELSE result2
END ...
There must be exactly one WHEN branch, one THEN branch, and one ELSE branch. The annotation @Aggregation.default: #FORMULA is mandatory for formula-related case expressions.
The listing above shows an example for a formula- related case expression:
Calculated units and calculated quantities are now handled natively in ABAP Core Data Services. In an analytical projection view, a calculated unit must be specified using the keyword VIRTUAL, as shown in the example above. The example uses the DDIC data element DD_CDS_CALCULATED_UNIT for typing, but this is not mandatory:
A calculated quantity is always the result type of a calculation. The annotation @Aggregation.default: #FORMULA is mandatory, and a reference to a calculated unit field must be defined via the annotation @Semantics.quantity.unitOfMeasure.
Note: In an analytical projection view, the annotation @ObjectModel.virtualElementCalculatedBy, which connects the virtual element to an ABAP class that calculates a value at runtime, is not supported.
For further details, see the ABAP Keyword Documentation, topic Calculated Quantity with Calculated Unit.
If you have questions, please feel free to leave a comment.
The following sources provide further information:
Contents:
- Motivation and Advantages
- Architecture Overview
- The Analytical Data Model
- Syntax of a CDS Analytical Projection View
Motivation and Advantages
CDS analytical projection views improve development efficiency when designing analytical queries within a CDS data model. They offer several advantages over the previous version of CDS Analytical Queries:
- An analytical projection view is a transient CDS entity. That means that only a runtime object on the ABAP server is generated. No SQL view is created on the database. Therefore, an analytical projection view can be used only by the analytical runtime. An analytical projection view can't be used as data source of other CDS entities. Access with ABAP SQL is not possible.
- CDS projection views (including analytical projection views) are a subcategory of CDS view entities and they bring along the same advantages:
- No DDIC artefact is generated.
- There’s a reduced need for annotations.
- Improved syntax checks are performed.
- They make use of new features of CDS view entities:
- Typed literals
- CDS-native handling of calculated units and calculated quantities
- Reusing expressions from the element list by using $projection as a pointer
- No further queries can be built on top of an analytical projection view.
Architecture Overview
- An analytical projection view must always be based on an analytical cube view. An analytical cube view is a CDS view entity with the annotation @dataCategory: #CUBE.
- Service exposure takes places via the new RAP-based Information Access (InA) service protocol.
- Analytical projection views can then be consumed in SAP Analytics Cloud (SAC) or with an SAP Fiori App.
- The runtime environment for analytical projection views are analytical engines, such as ABAP Analytical Engine.
The Analytical Data Model
Before we look at the syntax of CDS analytical projection views, we need to understand the underlying analytical data model. Analytical models are recommended for reporting purposes when you need to aggregate data to expose results across different areas (for example by location, time, and product type). These models are conceived over Facts and Dimensions and these views contain the basic data used to conduct detailed analyses and derive business values.
Imagine as an example a sales report that provides results based on product category, date, and location. The Fact is the product itself and it holds values that we can measure (for example number of sales and total amount of sales). The filters by location, time, and type are the Dimensions and these dimensions can have Attributes or Texts attached (for example address and product description). Each dimension can also have a Hierarchy to map hierarchical structures such as region, country, state, county, and city. These hierarchies allow for roll-up and drill down of aggregated data, for example Toronto and Vancouver can be rolled up as Canada. When we connect all dimensions and hierarchies together, we have a Cube and consequently an analytical model ready for consumption.
Analytical data models can be multi-dimensional and are often called Star schema.
The fields of an analytical cube view are Analytical Measures and Analytical Dimensions.
An Analytical Measure is a field of an analytical cube view that contains quantifiable data that can be calculated and aggregated, for example, the total price of all sales orders. Measures can have a reference to a unit key or currency key.
An Analytical Dimension describes a characteristic of the view's subject that can be used for grouping or filtering. For example, if a view describes a sales order, the following columns can be dimensions: customer, date, region. Dimensions can have a foreign key association to a dimension view.
The following blog post provides an example for how to design an analytical data model in ABAP CDS: Create an analytical model based on ABAP CDS views | SAP Blogs.
Further details on the analytical Star schema are provided in the developer guide for the Business Technology Platform: Analytical Data Modeling & Live Data Connection to SAP Analytics Cloud - SAP Help Portal.
A CDS analytical projection view is built in top of an analytical cube and models an analytical query within the CDS data model. One cube view can serve as basis for multiple CDS projection views.
Syntax of a CDS Analytical Projection View
The following CDS analytical projection view demonstrates the syntax elements of an analytical projection view.
@EndUserText.label: 'CDS projection view, analytical query'
@AccessControl.authorizationCheck: #NOT_ALLOWED
define transient view entity DEMO_ANALYTICAL_QUERY
provider contract analytical_query
with parameters
p_targetCurrency : abap.cuky
as projection on DEMO_CDS_CUBE_VIEW
{
//ROWS
@AnalyticsDetails.query.axis: #FREE
so_key as SalesOrderKey,
@AnalyticsDetails.query.axis: #ROWS
lifecycle_status as LifecycleStatus,
_lifecycle_status._text.text as LifecyleStatusText : localized,
//COLUMNS
@AnalyticsDetails.query.axis: #COLUMNS
quantity_sum as QuantitySum,
uom_sum as UnitOfQuantity,
//language-dependent text
@ObjectModel.text.element: ['CurrencyDescription']
currency_sum as CurrencyOfAmount,
//typed literal
@Aggregation.default: #FORMULA
abap.decfloat34'0.05' as Discount,
//Formula to define a calculated quantity
@Aggregation.default: #FORMULA
@Semantics.quantity.unitOfMeasure: 'AmPerQuanUnit'
curr_to_decfloat_amount( amount_sum ) /
$projection.QuantitySum as AmountPerQuantity,
//virtual element to define a calculated unit
virtual AmPerQuanUnit : dd_cds_calculated_unit,
virtual targetCurrency : abap.cuky,
//parameter used in currency conversion
@Aggregation.default: #FORMULA
@Semantics.amount.currencyCode: 'targetCurrency'
currency_conversion(
amount => curr_to_decfloat_amount( amount_sum ),
source_currency => currency_sum,
target_currency => $parameters.p_targetCurrency,
exchange_rate_date => created_on,
client => $session.client
) as convertedAmount,
//restricted measure
@Semantics.amount.currencyCode: 'CurrencyofAmount'
case when lifecycle_status between 'A' and 'B'
then amount_sum end as QuantityAB,
//formula-related case expression
@Aggregation.default: #FORMULA
case
when created_on = abap.dats'20200102'
then abap.int8'200'
else abap.int8'700'
end as formula_demo
}
WHERE lifecycle_status = 'A'
Syntax elements of an analytical projection view:
- The header annotation @AccessControl.authorizationCheck must be specified with the value #NOT_ALLOWED. Analytical projection views can't be accessed using ABAP SQL and therefore, no CDS access control can be specified. Instead, the CDS access control of the analytical cube view is considered.
- An analytical projection view is defined using DEFINE TRANSIENT VIEW ENTITY AS PROJECTION ON.
The keyword transient means that only an ABAP Server runtime object is generated, no SQL view on the database. - The provider contract must be set to ANALYTICAL_QUERY to ensure the correct syntax checks are applied.
- Input parameters can be specified in a parameter list.
- The data source of a CDS analytical projection view must be an analytic cube view. An analytic cube view is a CDS view entity with the annotation dataCategory: #CUBE.
- In the element list of an analytical projection view, the following elements are supported:
- Fields from the underlying cube view.
- Path expressions. In an analytical projection view, fields included via path expressions can be texts, display attributes, or texts belonging to display attributes (further details below).
- Arithmetic expressions that specify an analytical formula. The annotation default: #FORMULA is mandatory when an arithmetic expression is included as field in the element list. Arithmetic expressions are evaluated by the analytical engine when the analytical projection view is accessed.
- The following built-in functions are supported:
- CURR_TO_DECFLOAT_AMOUNT
- GET_NUMERIC_VALUE
- DATS_IS_VALID
- DATS_DAYS_BETWEEN
- DATS_ADD_DAYS
- DATS_ADD_MONTHS
- CURRENCY_CONVERSION
- UNIT_CONVERSION
- Case distinctions that specify an analytical formula or a restricted measure (further details below).
- Calculated units, specified with the keyword VIRTUAL (further details below).
- Parameters that have a numeric data type, a unit key, or a currency key.
- The session variables user_date and system_date.
- Typed literals.
Note: Untyped literals are not supported in the element list of analytical projection views. - Cast expressions for casting from one data type to another data type, or for assigning a new data element to a field.
- In a CDS analytical projection view, it is possible to refer to other expressions of the selection list, by using $projection as pointer.
- A WHERE condition can be specified to filter out irrelevant data.
Restrictions
- Key fields cannot be defined in the element list.
- Associations from the projected entity must not be exposed.
Path expressions in analytical projection views
In analytical projection views, path expressions can be used to include texts, display attributes, or texts belonging to display attributes in the analytical query. In an analytical cube view, such fields might be text associations or foreign key associations. In a CDS dimension view, such fields can be text associations.
Language dependencies are implemented using the keyword LOCALIZED. The annotation @ObjectModel.text.element: '<text_element>' defines the relationship between identifier elements and the respective text. The text is automatically filtered to the current logon language.
An example for a language-dependent text element is provided in the listing above:
@AnalyticsDetails.query.axis: #ROWS
lifecycle_status as LifecycleStatus,
_lifecycle_status._text.text as LifecyleStatusText : localized
For more details on how to get language-dependent text in CDS projection views, see the development guide for the ABAP RESTful Application programming Model Getting Language-Dependent Text in Projection Views - SAP Help Portal.
Temporal dependencies in CDS analytical projection views are implemented in the following format:
[1:date_from <= $parameter.key_date && date_to >= $parameter.key_date] Case distinctions in analytical projection views
A case distinction in the element list of an analytical projection view can either be a selection-related case expression (also called restricted measure) or a formula-related case expression.
1. Defining a restricted measure in an analytical projection view
A restricted measure allows you to further filter an analytical measure and limit the result set along a certain dimension. In the example above, the cube view defines the field amount_sum as an aggregation of all sales orders:
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'currency_sum'
SalesOrder.amount_sum
The restricted measure in the analytical projection view filters this measure to aggregate only sales orders with the lifecycle status set to a value between A and B.
//restricted measure
@Semantics.amount.currencyCode: 'currency_sum'
case when lifecycle_status between 'A' and 'B'
then amount_sum end as QuantityAB
A restricted measure must always have the following structure:
CASE
WHEN cds_cond
THEN result
END ...
There must be exactly one WHEN branch; no ELSE branch is allowed.
The condition cds_cond specified after WHEN can use only fields that are classified as dimension in the underlying cube view. The Boolean operators AND and OR are allowed to combine multiple logical expressions. Identical fields must be combined with OR, different fields must be combined with AND. NOT is not supported.
result specified after THEN must be a field of the underlying cube view specified as an analytical measure, or a literal of a measure-like data type. A literal is allowed only if the element is annotated with AnalyticsDetails.query.onCharacteristicStructure: true and when this annotation is used, a literal is mandatory.
2. Defining a formula-related case expression in an analytical projection view
A formula-related case expression allows you to further process an analytical measure. It must always be structured as follows:
@Aggregation.default: #FORMULA
CASE
WHEN cds_cond
THEN result1
ELSE result2
END ...
There must be exactly one WHEN branch, one THEN branch, and one ELSE branch. The annotation @Aggregation.default: #FORMULA is mandatory for formula-related case expressions.
The listing above shows an example for a formula- related case expression:
//formula-related case expression
@Aggregation.default: #FORMULA
case
when created_on = abap.dats'20200102'
then abap.int8'200'
else abap.int8'700'
end as formula_demo
Calculated units in analytical projection views
Calculated units and calculated quantities are now handled natively in ABAP Core Data Services. In an analytical projection view, a calculated unit must be specified using the keyword VIRTUAL, as shown in the example above. The example uses the DDIC data element DD_CDS_CALCULATED_UNIT for typing, but this is not mandatory:
//calculated unit, defined as virtual element
virtual AmPerQuanUnit : dd_cds_calculated_unit
A calculated quantity is always the result type of a calculation. The annotation @Aggregation.default: #FORMULA is mandatory, and a reference to a calculated unit field must be defined via the annotation @Semantics.quantity.unitOfMeasure.
//calculated quantity with calculated unit
@Aggregation.default: #FORMULA
@Semantics.quantity.unitOfMeasure: 'AmPerQuanUnit'
curr_to_decfloat_amount( amount_sum ) /
$projection.QuantitySum as AmountPerQuantityNote: In an analytical projection view, the annotation @ObjectModel.virtualElementCalculatedBy, which connects the virtual element to an ABAP class that calculates a value at runtime, is not supported.
For further details, see the ABAP Keyword Documentation, topic Calculated Quantity with Calculated Unit.
If you have questions, please feel free to leave a comment.
The following sources provide further information:
- ABAP Keyword Documentation (sap.com): CDS Analytical Queries
- Create an analytical model based on ABAP CDS views | SAP Blogs
- Developing for Analytics with the SAP BTP ABAP Environment | SAP Blogs
- Analytical Data Modeling - SAP Help Portal
- DSAG Webinar, Recording: "Analytische CDS Projektionsviews - Das neue Modell analytischer Abfragen" - Zoom
- Devtoberfest 2023, Recording: Introduction to Embedded Analytics with ABAP Cloud
- SAP Managed Tags:
37 Comments
