Generating Restricted and Calculated Columns

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

After completing this lesson, you will be able to:

  • Generate a restricted and calculated column

Restricted Columns

The restricted column is one of the column types available in SAP HANA Cloud models, which include the following:

  • Columns

  • Calculated columns

  • Restricted columns

The restricted column is restricted based on one or more attributes. These columns can be anything in the base table or view that the modeler defines to help reporting or further modeling.

Note

The restriction criteria cannot be based on a column defined as a Measure in the semantics. Only columns of type Attribute can be used.

Example Without Using a Restricted Column

With this data, you could restrict your report by filtering by Cost Type = Shipping Cost. If your reporting tool is SAP Business Objects, you could create a data provider with a query restriction where Cost Type is filtered to only display Shipping Cost.

Creating Restricted Columns

Setting the Restriction

Continuing with the example of using SAP Business Objects for reporting, when you have access to this restricted column, you can report on both the total gross amount and the gross amount for flat screens, in the same data provider or query.

If there are different lines in the restriction, all the lines defined on the same column are combined with the logical operator OR, and then, all the sets of restrictions for different columns are combined with the logical operator AND.

Note

This is the case regardless of the order in which you define the lines.

Displaying and Editing Restriction Expressions

Another option to define or edit the restrictions is to use an Expression. This provides more flexibility when the standard operators for the column-based, "graphical", restrictions do not fulfill your needs. The expression can use column names, operators, input parameters. From SAP HANA 2.0 SPS05 onwards, the restricted column expression can also include Functions.

Note
For example, extracting the year YYYY from a date column like YYYY-MM-DDwith a string or date function is now possible.

You can visualize the expression corresponding to your restrictions. This can be useful, for example, when you want to check the precedence of logical operators.

It is also possible to modify this expression, especially in complex scenarios when the features offered in the Column tab do not fulfill your requirements.

The expression can be written using SQL, in addition to the Column Engine syntax. However, for expressions in SQL, SAP HANA Cloud modeling supports a limited list of SQL functions.

Note

The general SQL support within expressions is not specific to restricted measure expressions. SQL can also be used in the following expressions:

  • Calculated columns

  • Filters

  • Default values for variables and input parameters

You will learn about these different types of modeling functions later.

Create a Restricted Column

Calculated Columns

In your Calculation Views, sometimes the measures from the base data are not enough to fulfill your reporting needs, even if you use joins, aggregations, unions, and so on. Besides, you might need to transform one or several attributes. So, you can enhance Calculation Views by creating calculated columns.

When to Use Calculated Columns

When you include calculations in your calculation views using calculated columns, you take advantage of the speed of SAP HANA by letting the database engine perform calculations instead of doing these calculations in your client reporting tool.

Having ready-defined calculations in calculation views can also help simplify reporting by unifying calculations. Besides, you can also secure some complex calculation scenarios by storing them in the calculation view.

Hint

It is recommended to create calculated columns only when there is a specific reporting need. Indeed, even if the calculation engine benefits from regular improvements in terms of performance, processing a calculated column can sometimes be costly, especially when it is complex or involves a lot of data.

The Calculated Columns Wizard

When creating a calculated column, the first step is to choose the column type, measure, or attribute.

Consider Granularity When Creating Calculated Columns

In the figure, Consider Granularity when Creating Calculated Columns, in the sum line highlighted in red, the units as well as the price have been aggregated. Multiplying these to aggregates does not give a meaningful result.

Triggering Calculations at the Right Level

By analyzing your reporting requirements, you can arrive at a decision at which stage the calculation should be performed.

Caution

Try to minimize calculations before aggregation; for example, when calculations include multiplication or division.

If the calculations are just additions or subtractions, it is not required. It will also slow down the execution of the view.

Calculation Before Aggregation

When data is calculated at the correct level of granularity, the total sales measure is correctly calculated; that is, 98,750.

Calculated Columns and Persistence

Creating calculated columns in an SAP HANA calculation view does not mean the calculation is persisted; it is simply projected through the generated column view.

The calculated column is available only during the runtime of the model and can be displayed in a report, or consumed by another view.

By contrast, if you need to keep the result of a calculation over time, you can apply one of the following methods:

  • Create an ad-hoc SQL artifact that updates and inserts the calculated column into a table.

    You can use a write-enabled procedure.

  • Use the transformation and calculation features provided by an ETL tool, such as SAP Smart Data Integration or SAP Data Services.

    You can pre-calculate columns during the data provisioning phase.

Expression Language and Validation

In SAP HANA Cloud, you can specify which of the two following languages the expression of a calculated column uses:

  • SQL

    The expression only uses plain SQL.

  • Column Engine

    The expression uses any valid SQLScript expression.

Validating an Expression Against a Specific Language: Key Benefits

  • It helps to validate the syntax of calculated columns according to the specified language.

    For example, an expression with an IF condition (supported only with SQLScript) will not validate if it is defined as SQL.

  • In turn, it helps the modeler to optimize the calculation execution.

    Indeed, plain SQL expressions enable a better optimization process, compared to SQLScript. So, by validating the expression against SQL, you can make sure that it is fully optimized.

Client Side Aggregation

Watch this video to learn about the Client Side Aggregation.

Consider an example where you have created the measure MAXIMUM_GROSS_AMOUNT, giving you the maximum value of the gross amount. By selecting the Enable client side aggregation checkbox, you propose to the reporting client to also apply a maximum aggregation on the client side as defined in the Client Aggregation drop-down list.

Generate a Calculated Column

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