Table of Contents
Analytic (Query) Annotations
In this page we want to provide simple examples for the most important annotations used by the Analytic Engine. S ee also Embedded Reporting on ABAP CDS views for more information to the CDS Views used in BW. Please note that also some non-analytics annotations are used by the OLAP Engine as you can see below.
Two important analytic annotations:
- CDS views with the annotation @Analytics.query: true are transient queries which can be interpreted by the Analytic Engine.
- CDS views with the analytic annotation @Analytics.dataCategory:#CUBE are transient BW providers.
BW InfoProvider: @Analytic.dataCatgegory: #CUBE
The analytic annotation Analytics.dataCategory can be used to create an BW provider by using the value #CUBE, see Simple BW Provider based on CDS View.
Data Extraction
- @Analytics.dataExtraction.enabled: If set to true, the view (CUBE) suitable for data replication. This is the default setting. See also Delta enabled extraction from CDS views. Application Component for @Analytics.dataExtraction: BC-BW-ODP.
Aggregation
- @Aggregation.default or @DefaultAggregation: When the DefaultAggregation annotation has been specified for an element, the corresponding elements are used as so called measures (elements that can be aggregated) in analytical scenarios. Only SUM,MAX and MIN are allowed, the analytic manager doesn't support the values AVG, COUNT or COUNT_DISTINCT. It needs to be defined on the cube layer. On query level, basically the annotation @DefaultAggregation is restricted to the values #FORMULA which indicates that calculated elements are calculated after the aggregation. Review the following wiki page for a detailed discussion of this annotation: Default Aggregation of key figures in CDS Views
Currency Key Figures
- @Semantics.currencyCode: This annotation tags a field containing a currency code
- @Semantics.amount.currencyCode: The annotated field contains a monetary amount, and the corresponding currency code is contained in the referenced field.
- See Simple BW Provider based on CDS View, key figure FlightPrice(text:Airfare). See also BW InfoObjects modeled by CDS Views.
Quantity Key Figures
- @Semantics.unitOfMeasure: This annotation tags a field containing a unit of measure.
- @Semantics.quantity.unitOfMeasure: The annotated field contains a quantity, and the corresponding unit of measure is contained in the referenced field.
- See also BW InfoObjects modeled by CDS Views.
Association to DIMENSION(BW InfoObject)
- @ObjectModel.foreignKey.association: see CUBE with association to DIMENSION.
- Review note 2835242 'More Values in Cube CDS View than in Dimension View'
- See also BW InfoObjects modeled by CDS Views and CDS cube views.
DB Hints
- @Consumption.dbHints can be defined on InfoProvider view to add more DB hints. It will affect all queries defined on it. For example: @Consumption.dbHints: ['DOUBLE_PREAGGR_BEFORE_JOIN', 'NO_USE_OLAP_PLAN']
Access Control/Authorization Check
- It is possible to restrict the access to certain values of fields/InfoObjects by using CDS access controls which are based on CDS roles. The annotation @AccessControl.authorizationCheck: #CHECK can be used for data category CUBE and DIMENSION( a query ignores such a annotation). See CDS Access Controls for further details.
BW InfoObject: @Analytic.dataCatgegory: #DIMENSION
Corresponds to a BW InfoObject, see
- BW InfoObjects modeled by CDS Views
- CUBE with association to DIMENSION.
- Time Dimensions and Virtual Time Hierarchies
- Review note 2835242 'More Values in Cube CDS View than in Dimension View'
BW Hierarchy: @ObjectModel.dataCategory: #HIERARCHY
Corresponds to a BW Presentation Hierarchy. A Hierarchy is defined on InfoProvider CDS, however they are only interpreted by the Analytic Engine when it is used in a CDS query with @AnalyticsDetails.query.
BW Query: @Analytics.query: true
In order to specify which views will be exposed to the analytic engine, the following annotation is used: @Analytics.query: true. The query views are built on top of views classified as #CUBE or #DIMENSION (master data reporting). They inherit the dimensions, measures and the hierarchies from the reuse view.
Variables
Filter variables of the BW query can be expressed with @Consumption.filter annotations in CDS. As an alternative to the filter annotation, you can introduce a parameter and use it in the where clause. See below. Variables and Filters are always so called hard filters (characteristic restrictions, see OLAP Filter Handling).
- Review Example.
- Text variables can be defined with @Consumption.dynamicLabel,sSee Example. @EndUserText.label only uses static text.
- With @Consumption.filter:{selectionType: #RANGE or #INTERVAL, multipleSelections:true}, a selection option variable is generated. With this, exclude or contain patterns are possible. See Example and Further Details to @Consumption.filter.
- Variable derivation troubleshooting, see KBA 2588590 Error 'Variable xxx could not be replaced' in a CDS Query (BRAIN 641).
- Variables merge: see rules in Note 2994457.
- @Consumption.filter.defaultValue only supports a single value, use @Consumption.derivation to fill in multiple default values: see Example.
Filters/Variables by using Parameters
As an alternative to the filter annotation, you can introduce a parameter and use it in the where clause. Note that only parameters representing single values are supported. The parameters are mandatory.
- Review Example (single value and interval restriction).
- In case a @Filter annotation is used for an element that is filtered in the WHERE condition as well, the filter annotation will be ignored! See Example (at the very bottom).
- Parameter variable can only be single value variable, wildcard function is not supported. Wildcard function is only supported in contain patterns in a selection option variable.
- For a single value variable without master data, value 'A*' is treated as literally value 'A*'.
- For a single value variable with master data, if value 'A*' is not in master data, variable check will raise invalid value error (BRAIN_DEV 643).
Sort by Characteristic
If no additional annotation is used, the characteristic values are sorted as defined in filters. Use annotation: @AnalyticsDetails.query.sortDirection with #ASC, #DESC.
- Time fields are always sorted by Key
- UUID fields are always sorted by TEXT
Decimal Places
Use annotation: @AnalyticsDetails.query.decimals
Calculated Elements
the annotation @AnalyticsDetails.query.formula will only be interpreted by the BW query (BADI). It contains an expression representing a numerical value. This expression can contain the following
- Cube measures and/or other numeric elements of the select list
- Arithmetic expressions using operators + - * /
- Functions NDIV0, NODIM, SQRT, SUMGT, SUMCT and SUMRT, which represent the corresponding BW formula functions
- CASE expressions with at most a single THEN clause (will be translated into BW IF operator)
- WHEN clause can contain conditional or boolean expressions of measures and/or other numeric elements of the select list
- ELSE clause is optional (defaulted to ELSE 0 )
- Examples
Restricted Measures
The filters for restricted measures will be modeled via the standard SQL case statement. So far, a restricted measure filtered by a hierarchy node in case statement is not supported yet.
Hidden Key Figures
In order to hide measures initially but allow a user to unhide it - annotation @AnalyticsDetails.query.hidden can be used. In order to hide e.g. intermediate calculated fields permanently, the domain specific annotation @Consumption.hidden can be used.
Currency Conversion
Currency conversion can be defined by using a CDS currency_conversion function expression in a select list entry. See the following examples and SAP Online Help: ABAP CDS - Conversion Functions for Units and Currencies
Multidimensional Layout
Attributes and measures can be put on several axes. For this the following annotation can be used:
- @AnalyticsDetails.query.axis: #ROWS
- @AnalyticsDetails.query.axis: #COLUMNS
- @AnalyticsDetails.query.axis: #FREE (default)
The default value is the free axis. All measures need to be on the same axis. The default for the measures is to place them on the columns. If the measures should be put on the rows they all need to be annotated to be placed on the rows:
Zero Suppression
Use annotation: @Analytics.settings.zeroValues, see Example.
Formula Variables
Numeric view parameters can be used as BW formula variables, see Example.
Value Help for Variables/Parameters
- There is no annotation to control the F4 mode (see F4 Modes) of a variable in a CDS view. M mode is used, see below.
- In query result (SF - see F4 Modes), Q and M mode are supported. The mode can be set in front end and annotation @AnalyticsDetails.resultValuesSource, see below. If there is no setting specified, Q mode is used by default.
- When a dimension view is defined as DCL-0 (with annotation @AccessControl.authorizationCheck: #PRIVILEGED_ONLY), F4 help for field based on this characteristic in the variable screen will get empty value list since the values are protected from end users. In this case, use annotation @Consumption.valueHelpDefinition to define a Value Help CDS view which contains fields that allows to see for this dimension view. Example
- Regarding the display of the initial value (#) in the F4 help review note 2584290. See also CDS Query: Compounded Dimensions and Initial Values in F4 Help.
- Key/Text display in F4 can be controlled by @AnalyticsDetails.query.display (see Note 3002995)
- It is possible to define a value help for a variable based which depends on the value entered in another variable, the main corresponding annotation is @Consumption.valueHelpDefinition.additionalBinding. See CDS Views: ValueHelp(F4) and Binding for details.
Value Help(F4) for Variables
- Values of F4 help of a variable are retrieved from the corresponding Dimension, see Example. BW F4 mode D(see F4 Modes) is not possible. Only M mode supported.
Value Help(F4) for Parameters
- Values of F4 help of a variable are retrieved from the corresponding Dimension, see Example. BW F4 mode D(see F4 Modes) is not possible. Only M mode supported.
Value Help in Query Result
See also SF in F4 Modes and displaying Unposted Values(see Access Type for Result Values) in Result Set. Annotation @AnalyticsDetails.query.resultValuesSource controls the list of values which should be taken into account for a specific characteristic: it is used in the value help at query runtime and for displaying the values in the query result. Following values exist:
#CUBE (default): corresponds to Query-Mode Q for value help and shows only posted values in query result.
#DIMENSION: corresponds to Master Data mode M for value help and all master data(also unposted values) are shown in the query result(see Access Type for Result Values).
- Example I
- Example II: Unposted and Display Hierarchy
Define and Translate Texts for Fields
Define your own label for a characteristic or variable by using annotation @EnduserText.label, see Example.
Technical Names of CDS Fields in BW
CDS: Technical Names and Descritions of Fields
Display Attributes and Texts
Display Attributes and texts can be added by using associations of the cube in the projection list. Elements (keys) and their label elements (texts) are combined by the engine into one field. Whether the analytic engine shows key/text or text/key depends on the order in the projection list
- Display Attributes: see CUBE with association to DIMENSION.
- Texts: see Text in CDS and Cube with association to DIMENSION and TEXT
Text Variables
By using annotation @Consumption.dynamicLabel, the values of parameters can be used to defined dynamic descriptions for key figures. See Example.
Exception Aggregation
For calculated elements (not for Restricted Measures), exception aggregation can be defined for CDS queries using the annotations
@AnalyticsDetails: {
- exceptionAggregationSteps.exceptionAggregationBehavior: #Aggregation,
- exceptionAggregationSteps.exceptionAggregationElements: ['FieldName'] }
The default aggregation behavior cannot be defined in the query. It needs to be defined on the cube layer. Therefore, usage of @DefaultAggregation is restricted to the values #FORMULA for formulas and #NONE for other select list entries.
- Example
- See also " Data Aging and HANA Pushdown" below and CDS Query: Restricted Measures with Exception Aggregation.
Hierarchies
With the annotation @AnalyticsDetails.query.displayHierarchy you can specify a display hierarchy for an element. This annotation is an enumeration with the following values:
- #OFF: No display hierarchy
- #ON: With display hierarchy
- #FILTER: use the display hierarchy specified in the filter
Which hierarchy is taken is defined by @AnalyticsDetails.query.hierarchyBinding. If only one hierarchy exists, the hierarchy binding can be omitted. A CDS hierarchy can be defined using the annotation @ObjectModel.dataCategory: #HIERARCHY.
- See Using Hierarchy in CDS for details.
Structures with Hierarchical Display
With the annotation @AnalyticsDetails.query.elementHierarchy you can maintain hierarchical relationships for structural components in a structure.
BW Conditions
It is not possible to define a BW Condition(see 'Local calculations'& Conditions ) for a CDS query by using a corresponding annotation. However, if the frontend supports this feature, it can be used to define Conditions on the fly and also save then this navigation state e.g. in a bookmark or AO workbook. Of course it is also possible to define a classic BW query based on a CDS Cube(see below, last point) with Conditions.
OData Service / ODataQuery
Using the annotation "@OData.publish: true" generates an OData Service when activating the CDS view. See BW OData Queries .
OLAP Effort and other Query Properties
With the annotation @Analytics.settings.maxProcessingEffort you can change the query property 'OLAP Effort', see note 2572550 for further details. Note that the default setting for CDS queries is High(so when the Annotation isn't used at all in the CDS query, the system takes the value High). See also Query Properties of CDS Queries.
Data Aging and HANA Pushdown
as explained in BWonHANA: Analytical Engine, there are some OLAP features like a Exception Aggregation which can be processed on the HANA database (called 'pushdown') in order to improve the performance. In case of CDS queries, such a pushdown is supported as of the releases BW 753 and BW/4HANA 2. Due to the default setting(trexops=9) in these releases, a pushdown is carried out if possible for CDS queries. There is no annotation which can be used to specify the trexops mode, however, if necessary(e.g. as a workaround when issues occur during the pushdown) the setting can be changed locally in RSRT(see trexops and Example).
Note that when the annotation @DataAging.noAgingRestriction is set to 'true' (current and historical data is read), a pushdown isn't supported. See Data Aging and notes 2869647, 2657021 for further details. See also
- Query Properties of CDS Queries
- Default Aggregation of key figures in CDS Views (NO2 key figures prevent a pushdown)
CDS Query run in AO, SAC, Cloud Platform, WebI, Power BI or as source of a Fiori App
With the annotation @Analytics.query: true, the CDS query 2C<sqlview name> can directly be executed in transaction RSRT (after CDS view activation). However, to run it in frontends like AO (what also means that it is available in AO query value help / search for a query), it needs to be C1-released. In order to check this, you need to open the CDS view with the HANA Studio (ABAP Development Tool) and check the Properties of the view:
You can use the Change button to set the state to Released:
In addition activate the settings 'Use in SAP Cloud Platform' and 'Use in Key User Apps' when the CDS view should be used in a cloud frontend or Fiori app, respectivelly.
If 'API State' is not available, please apply latest ABAP Development tools by https://tools.hana.ondemand.com/#abap. See SAP Note 2745500. So far, there is no tool to massive release API State to 'C1 Released' yet.
Notes:
- 2733553 CDS InfoArea or CDS Query is missing in Open Query Dialog Box
- 2785573 C DS-based BW queries are not found by input help
- 2788839 CDS-based BW queries are not found by input help
- 2810278 CDS-Based Queries: REPDIR Utilities
- 3025422 Open Dialogs: Slow Performance Expanding Info Provider Tree
- 2994457 Variable Merge in CDS Queries
Extend a CDS Query View
A CDS view can be extended by an extend view to add additional query elements. Please note, an extend view itself can't generate a query by itself. Therefore it is not supported to run a query of 2C<extend_view_sqlview_name>. Please always run the query generated from the original query view. (Note 2950780)
Example: Extend view for CDS query
'Classic' BW Queries based on CDS Cubes
It is possible to create BW queries on top of CDS Cubes with the help of the BW Modeling Tools (Hana Studio). This might be necessary if OLAP features are required which are not supported for Analytic CDS queries. A BW query should never have a name starts with '2C'. If a query created by old designer has such a name, please copy it a valid name and delete the old one.
Example I: BW Query based on CDS Cube with Parameter
SAP Online Documentation
2 Comments
Sreekanth Surampally
Thank you so much for keeping all examples together at one place. CDS code syntax used in these examples are screen shots, is there a possibility of having the code which can be copy and use it in another place.
Peter Stockinger
Hi Sreekanth,
thank you for the feedback. Yes, that is a good idea. At least when the examples are based e.g. on table SFLIGHT it makes sense to offer the possibility of copying the code. I will try to do this at least for the upcoming examples.
best regards
Peter