In this lesson, we will cover recommendations and best practices for nodes in your calculation views.
Column Pruning
Make sure that you do not map columns that are not needed. This might sound like a fairly obvious recommendation, but it is very common for developers to simply map all columns without considering if and why there are used. They are often added 'just in case' they are needed later. Too many attributes can lead to very large, granular data sets especially when only aggregated data is needed.
Optimal Filtering and Calculated Columns
Always try to filter the stack as early as possible. The objective is to prune data as early as possible to avoid large data sets that can harm performance.
Avoid including calculated columns in filter expressions. It is better to first create a new column based on the calculation, and then add a projection node on top to filter the result set based on the column (which is no longer calculated).
Calculate as high as you can in the stack so that you avoid calculating on granular data and instead calculate on aggregated data.
Switch Calculation View Expressions to SQL
Expressions are used in many places within calculation views, including calculated columns, restricted measures, and filter expressions.
For SAP HANA on-premise, it is possible to write expression using either plain SQL or the native HANA language called column engine language. SAP recommends that you only use SQL and not column engine language to ensure best performance (Column Engine language limits query unfolding).
When you build calculation view expressions on SAP HANA Cloud, you can only choose SQL expression language. Column engine language is not available in SAP HANA Cloud.
Calculation views that were imported to SAP HANA Cloud from SAP HANA on-premise and include column engine language expressions will still run, but you should change the expression language to SQL from the column engine, to achieve better performance. When you select SQL language, the language selector is then grayed out so that you cannot return to Column Engine language.
You should be able to recreate column engine expressions to plain SQL. For example, the concatenate operator + is replaced with the plain SQL operator || or you can use the CASE keyword instead of If...Then.
Optimal Aggregations
Always reduce the data set by introducing aggregations early in the modeling stack.
If you consume a calculation view that produces attributes, do not change them to measures and vice versa.
Choose a cube with star join to build dimensional OLAP models. Do not try to create OLPA models using standard join nodes which creates a relational model. These may not perform as well.