Working with Common Features of Calculation Views

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Describe features that are common to all types of calculation view

Defining Column Semantics

Every calculation view has a Semantics node. You do not add this, it is already present and will always be the top node, regardless of the type of calculation view. In this node, you assign semantics to each column in order to define its behavior and meaning. This is important information used by consuming clients so that they are able to display and process the columns appropriately.

One of the most important and mandatory settings for each column is its column type. You can choose between attribute or measure. This defines the basic role of the column.

In the semantic node, you can also optionally assign a semantic type to each column. A semantic type describes the specific meaning of each column and this can be helpful to any client that consumes the calculation view by enabling it to then represent the columns in the appropriate format. For example, if you define a column as a date semantic type, the front-end application can then format the value with separators rather that a simple string. The key point is that it is the responsibility of the front-end application or consuming client to make use of this additional information provided by the semantic type.

  • Assigning a description column to another column - for example, assigning the product id column to a product description column so a user sees a value that is more meaningful.

  • Hiding a column - can be used if a column is only used in a calculation, or is an internal value that should not be shown, for example, hiding the unhelpful product id when we have assigned a description column that should be shown in its place.

  • Assigning a variable - allowing a user to select a filter value at runtime for the attribute

Apart from the semantic type there are other important settings that can be defined for each column such as the following:

One of the most frequently maintained values in the Semantics node is the name and label of the column. It is possible to define an alternative name and label to any column so that it will make more sense to a user than what was originally proposed from the data source. For example, who wants to see the word MATNR in a business report column heading when we really should be seeing the words Material Number?

Data Source Aliases

In some cases, you need to use the same data source more than once in a calculation view. Although it is possible to include the same data source multiple times in the same calculation view, each instance needs a unique name in order to identify it.

In order to support this scenario, you define an alias for any additional instance of the same data source. The alias is found under Properties once you click on the header of the data source.

Note
The SAP Business Application Studio automatically suggests an alias by adding an incremental number to the end of the original data source name. We recommend choosing your own alias name to provide something more meaningful, by overwriting the proposal.

Hiding Columns

Hiding Columns

You can choose to hide columns that are not required for client consumption.

Sorting the Data Set

SAP HANA Cloud has the possibility to sort the result set (output) of any calculation view. This enables you to define a sort order that will apply when none is specified by the client query that is executed on top of the calculation view, thus guaranteeing a stable result order.

It can also be useful when previewing the data for the purpose of testing your calculation views.

Note
It is technically possible to include a hidden column in the list of columns used to sort a result set. However, this generally does not make sense as the business user will not see how the data set is sorted.
Caution
When the client query defines itself a sort order on different columns, or only a part of the sort columns used in the calculation view design to order the result set, you cannot be sure that the original order defined in the calculation view on other columns is applied. For example, if the calculation view sorts the result set by SO_ID then PRODUCT_ID and a front-end tool executes a query ordered by PRODUCT_ID, you will not necessarily have your result set actually sorted by PRODUCT_ID then SO_ID. To get this result, the front-end query needs to specify the two columns in the ORDER BY clause.

Null Values

Columns, both attributes and measures can contain undefined values or null values. You can handle such cases by defining default values that replaces the null values in reporting tools.

For example, you can replace the column values that would usually appear with the null value representation of '?' with a default value 'Null', or 'Empty' or with any other user defined value that you prefer.

  1. Select the Semantics node
  2. Choose the Columns tab
  3. Select a measure or attribute
  4. Select the 'Null Handling' checkbox
  5. Optionally, in the Default Value text field, provide a default value
Note
If you have enabled null handling for columns and if you have not provided any default value, then the tool considers the integer 0 as the default value for columns. However, for columns of data type NVARCHAR, if you have not defined a default value after enabling null handling, the tool displays an empty string, (which means blank), as the default value.

General Properties of Calculation Views

For each calculation view, you can define a number of properties in the View Properties tab of the Semantics node. Depending on the type of calculation view, the list of available properties may vary.

Calculation view properties are organized with four tabs of the semantics node that have the labels General, Advanced, Static Cache and Snapshots.

The two following tables list the General and Advanced properties of views and give a short description. A number of these properties will be described in more detail in the following lessons.

Properties of Views — General

PropertyDescription
Data CategoryFor calculation views, this determines whether the view supports multidimensional reporting.
TypeStandard or Time
Run WithDefines how to apply security when executing a script-based calculation view.
Default ClientDefines how to filter data by SAP CLIENT (aka MANDT).
Apply PrivilegesSpecifies whether analytic privileges must apply when executing a view (SAP Business Application Studio for SAP HANA modeling supports SQL analytic privileges only).
Default MemberDefines the default member to be used for all the hierarchies of this calculation view.
DeprecateIdentifies views that are not recommended for reuse, though still supported in SAP HANA Modeler.
Enable Hierarchies for SQL accessIn a calculation view of type CUBE with star join, determines whether the hierarchies defined in shared dimension views can be queried via SQL.
End User ViewSpecify whether to offer this calculation view as a source to reporting tools.

Properties of Views — Advanced

PropertyDescription
Propagate instantiation to SQL ViewsIf the calculation view is referenced by an SQL view or a Core Data Services (CDS) view, determines whether the calculation view must be instantiated (for example, by pruning columns that are not selected by the above queries) or executed exactly as it is defined.
Analytic View Compatibility ModeIf this setting is activated, the join engine ignores joins with cardinality n..m defined in the star join node when no column at all is queried from one of the two joined tables.
Ignore Multiple Outputs For FilterOptimization setting to push down filters even if a node is used in more than one other node.
Evaluate Session Variables EarlyOptimize union pruning by considering session variable values during pruning decision
Pruning Configuration TableIdentifies which table contains the settings to prune Union nodes.
Execute inDetermines whether the model must be executed by the SQL engine or column engine.
Execution HintsThis property is used to specify how the SAP HANA engines must handle the calculation view at runtime.

Save progress to your learning plan by logging in or creating an account