Working with Common View Design Features

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

After completing this lesson, you will be able to:

  • Use common features to design calculation views
  • Describe the purpose of each node in a calculation view

Semantics Node

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 data category of the 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 handle the columns appropriately.

One of the most important settings for each column is its column type. You can choose between attribute or measure.

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.

Semantic Attributes

Semantic types for attributes can be defined as:

  • Amount with Currency Code

  • Quantity with Unit of Measures

  • Currency Code

  • Unit of Measure

  • Date

  • Date – Business Date From

  • Date – Business Date To

  • Geo Location - Longitude

  • Geo Location - Latitude

  • Geo Location - Carto ID

  • Geo Location – Normalized Name

  • Hyperlink

Semantic Measures

Semantic types for measures can be defined as:

  • Amount with currency code

  • Quantity with unit of measure

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

  • 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

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?

Column Properties in the Semantics Node

In the Semantics node, you can define and review the properties assigned to columns that are in the output of the top node.

Column Properties

Property NameSee Lesson
Aggregation TypeSee lesson Aggregation node
Conversion FunctionConversion functions used for inbound/outbound input parameters values (optional)
Data MaskingSee lesson Masking Sensitive Data
Data TypeSee Same Lesson
HiddenSee Same Lesson
LabelColumn Label
Label ColumnSee Same Lesson
NameColumn name
Related Attributes 
SemanticsSee lesson Conversion, Same Lesson
Sort DirectionSee Same Lesson
TypeSee Same Lesson
UnconvertedSee lesson Conversion
VariableSee lesson Using variables and input parameters
Default ValueSee lesson Using variables and input parameters
Display FolderOrganize attributes into folders for BI metadata
Drill-Down EnablementProperty for calculated Columns
Hierarchy Default MemberSee lesson Using Hierarchies
Info Object 
Keep FlagSee lesson Aggregation node
KeyIdentifies key attributes, especially in DIMENSION calculation views
MappingOrigin of the column (origin node name, column name)
NotesFree text
Null HandlingWhether Null values in columns should be replaced with a specific default value
Presentation ScaleWhether a presentation scale must be applied
Transparent FilterSee lesson Aggregation node

Base Table Alias

In some cases, you need to use the same base table more than once in the same calculation view. In this case, you can define a table alias for any additional instance of the same source table.

Note
The SAP Business Application Studio for SAP HANA Cloud automatically suggests an alias, but you can choose your own.

Hidden Columns

Watch this video to learn about the Hidden and Label Columns and Hidden Attributes.

Note

Only a column that is added to the output of the top node of a calculation view can be defined as a Label Column.

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.
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 Views

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

Calculation view properties are organized with two tabs of the semantics node that have the labels General and Advanced.

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.
History Input ParameterSpecifies which input parameter must be used to specify the timestamp in time travel queries.

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.
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.
Cache Invalidation PeriodIf the view data are cached, this determines whether the cache must be deleted on a daily or hourly basis, or after each transaction that modifies any of the underlying tables.
Execution HintsThis property is used to specify how the SAP HANA engines must handle the calculation view at runtime.

Create a Simple CUBE Calculation View

Specific Features to Enhance Flexibility of Calculation View Design

Because calculation views offer a lot of flexibility, in particular the possibility to have a large number of nodes in a view, the SAP Business Application Studio provides advanced functionality to manage nodes and semantics. The following features are available:

FeaturePurpose
Switching Node TypesSwitch one node type with another without losing the link to the data source or the upper and lower nodes.
Replacing a Data SourceReplace one node source with another without losing the output columns, calculated columns, and so on, throughout all of the upper nodes.
Extract SemanticsApply the semantics from an underlying node or data source to the Semantics node of the calculation view.
Propagate to SemanticsMap columns to the output across all upper nodes, up to the top node of the calculation view.
Previewing the Output of Intermediate NodesTo troubleshoot problems in view design, preview the data of an intermediate node rather than the whole calculation view.
Map Input Parameters between nodesMap input parameters of a view to input parameters defined in underlying (source) views of other nodes.

Switching Between Certain Types of Node

It is possible to convert a Projection node into an Aggregation node and the other way around without losing the reference to the data sources of the node.

For the top node, it is also possible to switch to a Star Join node.

Replacing the Data Sources of a Node

Depending of the type of node and view configuration, you can replace the data source of a node (for example, a table, a Calculation view, or another node) by another data source (table, Calculation View).

This functionality is useful, for example, when you want to have the columns still propagated to all the upper nodes. You can also keep the existing calculated columns defined in this node working despite the change of data source.

When you do the change, a column mapping dialog box appears. It helps you assign the columns of the new data source to the output columns previously defined in the node.

Extracting Semantics

It is possible to extract the semantics from the underlying data sources of the nodes, especially when these data sources are information views with rich semantics, and to propagate them to the semantics of the calculation view. In SAP HANA 2.0 SPS03, it is also possible to extract hierarchies and variable information from the included calculation views. To allow this, the corresponding columns must be available in the current view (the "extracting" view).

Propagate to Semantics

This feature can be used in views with multiple stacked nodes. The purpose is to map one or several columns, already in the output of a node, to the output of all upper nodes and up to the Semantics node, or in other words, the top node.

This is an alternative approach, faster than adding successively the same column(s) to the output of several stacked nodes.

To do this, select one or several columns in the output of a node. Then right-click the selection and choose Propagate to Semantics.

Previewing the Output of Intermediate Nodes

To understand and fine-tune the different steps of a calculation view, each node can be previewed independently from the calculation view itself.

To achieve this preview, the view must have already been successfully built.

Map Input Parameters between Nodes

You can map input parameters (and also variables) defined in a calculation view to the input parameters and variables defined in the source views. You can do this to pass the parameter value itself, but also to pass the relevant list of values to the input parameter dialog box at runtime (when the view is executed).

Note

In the Manage Mappings view, you can even copy and map an input parameter from a source view.

That is, you do not need to create a new input parameter in the calculation view (and define all its settings) before mapping it. This task can be done automatically in just one operation.

Copy and Paste Part of a Scenario

It is possible to copy and paste a node within the Scenario pane of the same calculation view. If this node uses other nodes as its data sources, it is possible to decide whether you want to copy this node only, or the node and all nodes below, together with the connections between them.

Insert a Node between Two Other Nodes

In the Calculation View scenario, you can insert a new node between two existing nodes that are already linked. By doing this, you keep the columns consistent along the entire scenario.

Note that this is only available as a graphical feature: You must drag the new node from the palette and drop it exactly onto the existing link between the existing nodes.

For example, if you have started designing a CUBE calculation view where the aggregation node uses a join node as its data source. You have already mapped the required columns, defined the aggregate functions, semantics, and so on. Then you realize you need a projection to calculate a column before aggregating. In that case, by adding the new Projection between the Join node and the Aggregation node (instead of basically removing the Join node from the aggregation node and adding it to a new projection node), you keep most of the work you already made and save time.

Navigating to Source Calculation Views

It is possible to open a calculation view directly from calculation scenario of another view that consumes it. To do this, in the Scenario pane, right-click the data source (Calculation View) in the node that consumes it, and choose Open.

Analyzing a Column Lineage

The Show Lineage functionality enables you to track the origin of a column along the calculation scenario and down to the first node where it appears. This node can be a bottom-level node of the view where the column is selected from a source, such as another information view, a column table, and so on. It can also be an intermediate node where the column is calculated.

Show Lineage is a great feature for locating the origin of columns that might have been renamed during the flow.

Overview of the Possible Node Types

A calculation view can use as many nodes as you need. Each node has its own capabilities and behavior. The following figures show the types of nodes in the graphical calculation views and their use cases.

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