Let's familiarize ourselves with the most important settings of a cube view.
Watch the video to get started:
The video highlights some of the most important elements of the cube view, but let's cover them again and also introduce some new ones.
Basic Settings of the Cube View
A CDS view with the header annotation @Analytics.dataCategory: #CUBE defines a cube view. This means it can be consumed by a CDS Analytical Projection View.
The data source defined in the select from clause of a cube view is usually a CDS view that provides transactional data. In the context of a star schema, transactional data is often referred to as facts. Facts can be any data that represents a business event, such as a financial posting, a delivery of a product, recruitment of an employee, and so on. In other words, something that happened.
The fields of a cube view can play the role of either a dimension or a measure.
The key property doesn’t play a role in the cube view. However, it is good practice to define the business semantic key fields with the setting key as this might improve query performance in case extensions are added later to the cube view that are based on those fields.
The cube view must include the header annotation @AccessControl.authorizationCheck. In most cases, business data must be protected so the setting is usually #MANDATORY. Therefore a data control language (DCL) object must also be provided to specify the authorizations for each business user.
Note
Typically in CDS, consumption views are protected by DCL. In analytics, the consumption view is the analytical query, but it is actually the cube view that must be protected and not the analytical query. This means that an analytical query can be developed on top of a cube without the developer / business user having to be concerned with defining data authorizations.
Specify Dimension Fields
The most important annotations for dimensions in the context of analytics are:
@ObjectModel.foreignKey.association: Add a foreign key association that points to a dimension view. This foreign key association adds more content to the dimension value. For example, by providing additional attributes and texts.
- The on condition of the association must contain all key fields of the dimension view which are not technical key fields such as date-to or date-from. Furthermore, the on condition of the field must be to the representative key field of the dimension.
@ObjectModel.text.association: If no dimension view is available but only a text view you should add this annotation. It works similar to @ObjectModel.foreignKey.association. You don’t have to add both annotations to a field. If a dimension view is available you should use @ObjectModel.foreignKey.association.
@Semantics.calendar, or @Semantics.fiscal: Fields with time semantics should be assigned one of these annotations. It is not necessary for fields of type DATS or DATN. These fields are automatically treated as calendar days. If there are multiple fields and they all depend on each other (for example, FlightDate, FlightYearMonth, FlightYear), you should mark this dependency with the annotation @ObjectModel.value.derivedFrom. The least granular time characteristics should refer to the most granular time field. For example, FlightYear and FlightYearMonth should refer to FlightDate. It is not necessary for FlightYear to refer to FlightYearMonth.
If the source view that the cube consumes contains a granular time field, such as date, but does not contain coarser time fields such as quarter or year, you can create an association to the standard SAP-provided CDS view I_CalendarDate to make the coarser fields available.
Specify Measures
For measures, the following annotations are relevant:
@Aggregation.default There are three possible values:
- #SUM (if we have values 3,7,9 then result is 19)
- #MIN (if we have values 3,7,9 then the result is 3)
- #MAX (if we have values 3,7,9 then the result is 9)
@Semantics.amount.currencyCode: If the measure is of type currency, then you have to reference the field which stores the currency key.
@Semantics.quantity.UnitOfMeasure: References the unit key of a quantity measure.
Handling Unspecified Fields
For fields without @Aggregation.default, @ObjectModel.foreignKey.association, @ObjectModel.text.association or @Semantics - time annotation, the following applies:
If the data type of such a field is supported for a dimension, then the field becomes a dimension in the cube. If the data type is not supported for a dimension (for example CURR, DEC) then the field becomes a measure with the special NO2 aggregation setting.
Note
The NO2 aggregation setting means that if there is just one record, then the value of this record is displayed. If there is more than one record which must be aggregated, then the measure shows an error-value.
It is not recommended to have such measures in a cube view. In a dimension view, these measures make more sense. For example, if you have a product which has a numerical value such as length, width, height or price, it doesn’t make sense to SUM these values. If there is only one value possible, then defining these fields as cube measures would be fine.