Implement Recommended Modeling Practices

Objective

After completing this lesson, you will be able to implement recommended modeling practices

Understand the Instantiation Process

Before we dive into the detail of how to improve the calculation view performance, it is helpful to learn a little about the way a calculation view responds to a query that calls it.

A schema of the optimization process for queries on stored models. First, the calculation engine instantiates a run-time model for the query, then optimizes it, and finally the SQL optimizer takes over. For details, refer to the following text.

The calculation engine pre-optimizes queries before they are worked on by the SQL optimizer.

A calculation view is instantiated at run-time when a query is executed. During the instantiation process, the calculation engine simplifies the calculation view into a model that fulfills the requirements of the query. This results in a reduced model that can be further optimized by the calculation engine. For example, it considers settings such as dynamic join, join cardinality and union node pruning.

After this, the SQL optimizer applies further optimizations and determines the query execution plan - for example, it determines the optimal sequence of operations, and also those steps that could be run in parallel.

Details of the instantiation process. The query that calls the calculation view generates a pruned calculation view that reads only required parts of the original calculation view. It does not contain attributes or measures that are not part of the query.

The instantiation process transforms an original (one that you define) calculation view into an execution model based on a query that is run on top of a calculation view. The generated view is pruned of unnecessary elements from the original calculation view and is technically a column view that references one specific node of the original calculation view.

During the instantiation process, the query and the original calculation model are combined to build the optimized, execution calculation model.

Best Practices for Modeling

In this lesson, we will cover recommendations and best practices for data modeling with SAP HANA Cloud.

General Design Recommendations

Screen capture of a huge calculation view and how it could be split into smaller chunks. You should identify recurring patterns and then split models into smaller parts to avoid monolithic views.

Break down large models into individual calculation views so that they are easier to understand and also allow you to define reusable components, thus avoiding redundancy and duplicated maintenance as a consequence.

Try to develop calculation views in layers so that each layer consumes the lower layers. Any changes to the lowers layers immediately impacts the higher layers. The lowest layers should provide the foundation calculation views. The higher layers add increasing semantics to provide more business meaning to the views.

Note

Do not be concerned that breaking up large calculation views into individual building blocks will damage overall performance. At runtime, the SQL optimizer attempts to flatten the entire stack of calculation views into a single executable statement. Remember the calculation view definitions, however many there are in the stack, represent a logical view of the data flow. How the optimizer constructs the final SQL query can never be known at the design stage by simply observing the calculation view definitions. Use SQL tools such as SQL Analyzer to check the generated SQL, if required.

Aim for Query Unfolding

You should aim for query unfolding. The idea is that query objects should be directly accessible to the SQL optimizer on a detailed level. The advantage is a faster run-time performance of unfolded plans, but the compilation time of the query may increase.

At runtime, your calculation view is analyzed by the SQL processor. A key aim is to convert each calculation view operation into a corresponding SQL operator to convert the complete data flow graph into a single SQL statement. This is called unfolding. A benefit of unfolding to SQL is that only the SQL engine is needed and no other SAP HANA engine is called, such as the Calculation Engine or other SAP HANA Cloud engines such as Spatial or Graph. However, due to your calculation view design, there might be steps that cannot be translated into SQL operators. If these steps cannot be translated, they will be processed in the specialist SAP HANA engines which generate one or more materialized column views. These materialized column view are then read by SQL. So this means, instead of SQL reading directly from source tables, column views have to be first generated and then the SQL has to read from the interim column views. That would result in a partially unfolded query that might not perform as well as a completely unfolded query due to limited SQL optimizations.

Some nodes and operators cannot be unfolded when there is no equivalent SQL operator for the calculation view feature you are implementing.

It should be emphasized that although unfolding is generally desirable, sometimes unfolding can actually damage overall performance. For example, query compilation time can increase when the query has to be unfolded each time the calculation view is called with new selections. Therefore, it is possible to set a SQL hint that prevents unfolding for individual calculation views. This is found under Semantic Node → Properties → Advanced.

You can set a global parameter to prevent unfolding for all calculation views.

You can check to see if your calculation view is unfolded by using the Analyze > Explain Plan in the SQL Console, where you can see the list of operators and data sources.

How you check unfolding: Screen captures of the SQL Analyzer for an unfolded query and for a folded query. The unfolded query accesses a column table. The folded query accesses column views.

If a column view is used as a data source instead of a table, then you know this is not an unfolded query as the result has been materialized as an interim view and not read directly from a table. A completely unfolded query only accesses tables.

Caution

In some scenarios, the operator name can also be TABLE SCAN, which also reveals that the underlying object "physical" object has been accessed, meaning that this branch of the scenario has been unfolded.

In addition, check the query compilation time versus the execution time to see how unfolding is affecting the overall runtime.

It is possible to identify how many queries were not unfolded. Run a query on system view M_FEATURE_USAGE as follows:

Code Snippet
1234567891011
SELECT
 	FEATURE_NAME,
 	CALL_COUNT 
FROM 
	"M_FEATURE_USAGE" 
WHERE 
	COMPONENT_NAME = 'CALCENGINE'
AND
	FEATURE_NAME 
IN 
  ('QUERY TOTAL','QUERY UNFOLDING')

Use Dedicated Views or Tables for ‘List of Values’

For performance issues, we generally recommend that you create dedicated calculation views or tables to generate the list of help values for variables and input parameters.

Schema of a value help view that reads product master data. The value help view is applied to a view on Sales data and anohter view on Purchases data. Advantages of using value help views are faster value-help dialogs, consistent lists of variables across consuming views, and support for Analytic Privileges in the sense that users see their list of allowed values.

This approach enables a faster display of the list of values without reading the entire view or table on which the calculation view is based.

This is also best practice so that the list of values is reusable and consistent from one calculation view to another, and users see the same selection values.

The list can be driven by simple fixed filters in the help of the calculation view or even by analytic privileges that offer a dynamic list restricted to what each user is allowed to choose from in the interface.

Log in to track your progress & complete quizzes

Learning

SAP FacebookSAP YoutubeSAP LinkedIn