Creating a Graphical Calculation View

6 Information Views

6.4 Calculation Views

6.4.2 Creating a Graphical Calculation View

You can create a calculation view to depict a complex business scenario that has layers of calculation logic and include measures sourced from multiple source tables using the graphical modeling features of the SAP HANA Modeler.

Context

You can set the calculation view property Data Category to Cube or Dimension based on the following requirement:

● Cube - if you want to define a calculation view that is visible in the reporting tools. You must define at least one measure and the default node is Aggregation or Star Join (based on your selection in the creation wizard).

The Star Join node provides the platform to join the descriptive data that is, dimensions from the calculation views of type dimension with the fact data from the lower nodes. This way you are logically creating a star schema where, the join is created from the central entity to the other entities. You can however, create a snowflake schema by joining views to the central entity.

In a Star Join having calculation views with data category as dimension are treated as shared dimensions. All the attributes and hierarchies of these shared dimensions are added to the output of the calculation view.

During deployment, the Star Join is always deployed with an aggregation node on top of it. The Star Join is deployed first with a series of joins and then the aggregation logic is deployed.

● Dimension - if you want to define a simple SQL like calculation view, which, for example, is used to fill simple list user interfaces where recurring attribute values are not a problem, but are desired. To define this type of view, you do not define any measure. If you to define a view like this, its behaviour is as follows:

○ The output node does not offer any measures (or hierarchies), just attributes which can be numerical data types

○ The calculation view is not available for reporting

○ The calculation view is only consumable via SQL

○ The default node is Projection

62 P U B L I C

SAP HANA Modeling Guide Information Views

For the Aggregation node, at runtime, the measures are automatically aggregated on the level defined by the group, by clause. In this case, the output node is deployed as an aggregation node into the run-time model that is created during deployment. Moreover, the model information is written into the BI metadata consumption tables that is, made available to the BI clients of SAP HANA for reporting.

Note

If the default node is Aggregation and the Always Aggregate Result property of the Semantics node is set to True then the output of measures always appears aggregated.

You can switch the execution of the calculation view to the SQL Engine rather that the Column Engine for better performance by selecting the SQL Engine value in the Execute In property. When this property is set to SQL Engine, the calculation engine generates SQL code for each node at runtime and combines them into one big SQL statement which is then passed to the SQL interface. This might be beneficial since the SQL interface also involves standard SQL optimizer which implements a lot more optimization rules than the Calculation Engine. For example, the most important rule is join ordering determination.

This option is only efficient if every node is translated so that one big SQL statement can be generated. To gain optimal SQL generation, the following rules should be followed:

● The calculation view should only contain tables and a calculation view as data source. If a calculation view is added, the mentioned rules must be followed for it as well. Explicitly do not add any Analytic View, Attribute View, or Scripted Calculation View. This might break any optimization.

● Only use supported operations: projection, union, join, and aggregation.

● Do not use special column store functions in filter or calculated attribute expressions because they might not be convertible. For example, the functions date() and time() only work on the column store data types and cannot be converted.

● The NULL handling is different in the calculation and the SQL engine. In the calculation engine, the expression 2 + null results in 2, whereas in SQL engine it results in null. So if you switch on the SQL engine execution you might get different results than before. We suggest using the function sqladd instead. This behaves as the + in SQL.

● If a currency conversion or counter measure needs to be defined, it has to be defined at the output node of the main scenario. If a currency conversion or counter measure is modeled into an included scenario, SQL optimization would exclude that part because these measures can be handled more efficiently in the Calculation Engine.

You can further fine tune the behavior of the calculation view by:

● Creating variables, and bind them to attributes for filtering data. The values you provide for the variables at runtime determine which data records are selected for consumption.

● Creating input parameters to parameterize the view execution.

● Creating hierarchies to arrange view attributes that helps you to visualize and analyze the data in a hierarchical fashion.

● Grouping the related measures in display folders.

● Hiding attributes and measures that are not required for client consumption by assigning value true to the property Hidden in the Properties pane. The attributes or measures marked as hidden are not available for joins, input parameters, variables, consumers, and other views that are build on the top of this view. For existing models (before SPS06), if the hidden attribute is already used, you can either unhide the element or remove the references.

● Setting the cardinality of a join as required from the join properties. By default, the cardinality of a join is empty. The empty cardinality can be specified in cases when you are not sure about the right cardinality. If you specify the empty cardinality, system during the join evaluation phase diagnose the best suited

SAP HANA Modeling Guide Information Views

P U B L I C

cardinality and execute the join. Wrong selection of cardinality can lead to erroneous data and out of memory issues.

Procedure

1. Choose one of the following options from the Tools Palette:

View Node Description

Union Used to combine the result set of two or more data

sources. For example, to show the names of all the employees of a store which has different branches each maintaining its own employee records table.

Join Used to query data from two or more data sources, based

on a specified condition. For example, to retrieve the customers details with location based on the postal code present in two tables Customer and Geography where, Customer table has columns - Customer ID, Customer Name,Postal Code, and Geography having columns - Postal Code, Region, Country.

Projection Used to filter or create a subset of the required columns of

a table or view for creating the model. For example, selecting the employee name and sales quantity from a table consisting of many more columns.

Aggregation Used to summarize data of a group of rows by calculating

values in a column. For example, to retrieve total sales of a product in a month. The supported aggregation types are sum, min, and max.

Note

○ The input for union, join, projection, and aggregation view nodes can consist of data sources, union, join, projection, or aggregation view nodes.

○ You can only have one source of input for aggregation and projection view nodes, and two inputs for a join.

○ You can add the view nodes even between the two joined view nodes. If you drop a view node from the Tools Palette to a data source (that is, tables, attribute views, analytic views, and calculation views) of a view node, the data source is replaced by the newly added view node such that, the new view node has the data source as it's input. For example, if you drop a Projection view node on the DS1 datasource of the existing Aggregation view node, the Aggregation view node would now have Projection view node as it's data source and DS1 would be the data source of the Projection node.

2. To add the data sources to a view node like, join, select next to the join node, or drag and drop the data source from the SAP HANA systems view to the join node.

To query data from more than one table, go to the Details panel context menu, choose Create Join, and enter the required details. In the New Join window, choose a left and a right table. To create a join, choose

appropriate columns from each table and set relevant properties. At a time, you can create only a single join.

3. To define the output columns of a view node, select the node in the Scenario panel, and perform the following substeps in the Details view:

64 P U B L I C

SAP HANA Modeling Guide Information Views

a) If you have selected a union view node, drag and drop the required columns from Source to Target. You can also modify the attribute mapping. For more information, see Managing Column Mappings [page 71]

Tip

To create a system generated mapping, choose Auto Map By Name.

The output of a union view node is the attributes that you added to the Target.

b) If you have selected a join view node, join the columns of the data sources.

The output of a join view node is the joined attributes. However, to add additional attributes to the view's output, from the context menu, choose Add to Output.

c) If you have selected a projection or an aggregation view node, select the required data source columns, and in the context menu, choose Add to Output.

You can also create Calculated Columns, and Input Parameters for all the view nodes. In addition, you can also apply filter logic to the non- default aggregation and projection view nodes.

The output of the union, join, aggregation and projection view nodes is stored under the Column node in the Output panel.

Note

You can choose to rename the columns and calculated columns of the view using the Rename button in the Output panel toolbar. However, renaming a field can impact other objects that reuse the field as the new name is not reflected in the other objects.

The Calculation view allows you to rename a node attribute and propagate the new name to all higher nodes in a hierarchy of nodes. For propagating a name, choose Propagate Rename to Above Nodes in the Rename window.

4. If you want to directly add the columns of a view node to the Semantics node including the other view nodes that are in the joined path, perform the following:

a) In case of a Join, Aggregation, or Projection View node, right-click the column in the Details panel.

You can also select the columns in the Output panel of the view node.

b) In the context menu, choose Propagate to Semantics.

c) In case of a Union view node, right-click the column in the Output panel.

d) In the context menu, choose Propagate to Semantics.

The selected columns are propagated to all the view nodes including Semantics. Only those columns which already do not exist in any of the propagation path nodes are only added. If all the columns exist in the propagation nodes, addition will not go through.

5. If you have added the Star Joinnode in the scenario panel, perform the following:

a) Add the required Calculation View with data category as dimension in the Star Join node:

○ Drag the required attribute views present in the Content node to the Logical Join node.

○ Select the Star Join node in the Scenario panel, and choose Add to add the calculation views.

b) Create joins between the views and the fact table (formed by the lower nodes).

6. To add attributes and measures to the calculation view output, perform the following substeps:

a) In the Scenario panel, choose the Semantics node.

b) In the Column panel of the Local tab page, select the columns and change the type as Attribute and Measure.

SAP HANA Modeling Guide Information Views

P U B L I C

Note

○ If the Data Category property of the calculation view is set to Cube, it is mandatory to specify at least one column as measure; otherwise, the view activation fails. Also, if the value is set to Cube, an additional column called Aggregation is available to specify the aggregation type for measures.

○ You can change the type of a measure and perform a currency conversion by selecting it in the Local tab page and changing the Semantic Type property in the properties panel.

○ You can choose to rename the attributes, calculated attributes, measures, and calculated measures of the view using the Rename button in the Output panel toolbar. However, renaming a field can impact other objects that reuse the field as the new name is not reflected in the other objects.

7. Assign Semantics to the attributes, measures and calculated columns (optional)

a) To add meaning to the attributes and calculated attributes data perform the following:

1. In the Columns panel toolbar of the Semantics node, choose Assign Semantics.

2. In the Semantics dialog, select the required Semantics Type.

3. If you select Amount with Currency Code as Semantic Type, select an attribute or calculated column that represents the currency code in the Currency dropdown.

Note

Attributes and calculated columns having semantic type as Currency Code are highlighted for Currency selection.

4. If you select Quantity with Unit of Measure as Semantic Type, select an attribute or calculated column that represents the currency code in the Unit dropdown.

Note

Attributes and calculated columns having semantic type as Unit of Measure are highlighted for Unit selection.

The supported semantic types for attributes and calculated attributes are:

○ Amount with Currency Code

○ Quantity with Unit of Measure

○ 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

b) To add meaning to the measures and calculated measures data perform the following:

1. In the Columns panel toolbar of the Semantics node, choose Assign Semantics.

2. In the Semantic Type dialog, choose the required Semantic Type.

66 P U B L I C

SAP HANA Modeling Guide Information Views

3. If you select Amount with Currency Code as Semantic Type, in the Currency field, select a currency code.

Note

You can choose the currency from the system table TCURC or from an attribute of the view based on the currency type as Fixed or Column respectively.

4. If you select Quantity with Unit of Measure as Semantic Type, select a unit of measure in the Unit field.

Note

You can choose the currency from the system table T006 and T006A or from an attribute of the view based on the unit type as Fixed or Column respectively.

5. Choose OK.

8. Create counters to obtain the number of distinct values of an attribute (optional) a) Select the default aggregation node.

b) In the Output pane, right-click Calculated Columns.

c) From the context menu, choose New Counter. Enter a name and description (label).

d) To hide the counter during data preview, select the Hidden checkbox.

e) To add attributes in the Counters panel, choose Add and choose OK.

Remember

You can only create counters on the default aggregation node.

9. Activate the view.

○ If you are in the Modeler perspective, choose one of the following:

○ Save and Activate - to activate the current view and redeploy the affected objects if an active version of the affected object exists. Otherwise, only the current view is activated.

○ Save and Activate All - to activate the current view along with the required and affected objects.

Note

You can also activate the current view by selecting the view in the SAP HANA Systems view and choosing Activate in the context menu. The activation triggers validation check for both the client side and the server side rules. If the object does not meet any validation check, the object activation fails.

○ If you are in the SAP HANA Development perspective, choose one of the following:

1. In the Project Explorer view, select the required object.

2. In the context menu, select Team Activate .

Note

The activation only triggers the validation check for the server side rules. If there are any errors on the client side, they are skipped, and the object activation goes through if no error is found on the server side.

SAP HANA Modeling Guide Information Views

P U B L I C

Note

1. For an active calculation view, you can preview the data of an intermediate node in a calculation view, which helps to debug each level of a complex calculation scenario having join, union, aggregation, projection, and output nodes. The data you preview for a node is for the active version of the

calculation view. If no active version for the object exists then you need to activate the object first. You can choose the Data Preview option from the context menu of a node.

2. You can find the details of the functions available on content assist that is, by pressing Ctrl + Space in the SQL Console while writing procedures in the SAP HANA SQLScript Reference.

10. In the Change Tracking dialog, either create a new ID or select the existing change ID. For more information about Change Tracking, see the section SAP HANA Change Management in the chapter Implementing Lifecycle Management of the SAP HANA Developer Guide

11. Choose Finish.

Related Information

Assigning Variables [page 98]

You can assign variables to a filter at design time for obtaining data based on the values you provide for the variable. At runtime, you can provide different values to the variable to view the corresponding set of attribute data.

Creating Input Parameters [page 95]

You use input parameters to define internal parameterization of the view. Input parameters used in the views enables you to obtain a desired functionality when the view is executed.

Display Folders [page 89]

If your analytic view and calculation view has multiple measures and you want to organize them, for, example, segregate the planned measures with the actual measures, you can group the related measures in folders. These folders are called the display folders.

Description Mapping [page 89]

Description mapping helps you to associate an attribute with another attribute, which describes it in detail. For example, when reporting via Label Column , you can associate Region_ID with Region_Text.

Creating Level Hierarchies [page 91]

You create hierarchies to arrange view attributes that enhances data analysis by displaying attributes according to their defined hierarchical relationships.A level hierarchy is rigid in nature, and the root and the child nodes can only be accessed in a defined order.

Creating Parent-Child Hierarchies [page 93]

In document SAP HANA Modeling Guide En (Page 62-76)