Designing and Modeling HANA information views in better way –

While designing the information views in HANA person wise the design pattern and implementing approaches changes. In this blog I am going to discuss about the better approaches which of design and implementation pattern of HANA information view.

Approaches:

1. Sub modular and reusable approach:

While designing a HANA information view (Calculation view) instead of consuming all the tables on projection or aggregation node in a single view create sub models (sub View) which will be finally consume by the main calculation view. In this approach there is some advantages –

• We can reduce the data volume but subset the data by joining with the larger table which can cause a time out error if we join directly with the no of node in the main view.
• In this approach we will avoid single data source (or node) feeding multiple nodes in calculation view.

Example:  

We created a sub model WorkCentre and squeeze the data inside the sub model and then Merge in the main model.

Consuming in the main model –

2.Instead of Join use Union if possible:

Union is much faster than join. So if possible as per the requirement scenario where need to use join, instead of join we will use UNION. UNION will be used as inner, left outer, right outer, inner and full join. Please find the below example how we can use it.

Create a calculation view using UNION of two projection node instead of join and create constant flag 1=2 and flag 2=3 on each node (Projecttion_1 and Projecttion_2 respectively). Merge them into third constant FLAG in the UNION.

• For left outer :

Set Filter on Aggregation node i.e.” FLAG”=2  to get the left outer set of the result –

• For Right Outer Join-

Set Filter on Aggregation node i.e.” FLAG”=3  to get the left outer set of the result –

• For Inner Join:

Set Filter on Aggregation node i.e.” FLAG”=3  to get the left outer set of the result –

3. Use aggregation node in on top of large table:

When the table size is large and that to consume from a projection node of a calculation view, need to use aggregation node (to aggregate the duplicate row in the transactional table) on top of the consumable projection node. In the way the row number will be reduced and the subset size will be less before joining to the main information view.

4.Prefer Graphical View:

While design always prefer Graphical view instead of SQL script view. In Graphical view HANA provide the facility to optimize the query in the execution plan automatically. HANA itself maintain that. We do not need to take care of the optimization.

But SQL script view does not provide this facility.

5. Use of Star join:

When there are multiple Dimension table that need to be join with fact table, always use calculation view with star join to get the optimize performance.

6. Use input parameter for data pruning in lower level of a calculation view:

Always try to design information view in which we can push the input parameter at the lower level of the view and apply as a filer on the lower level to reduce the dataset. It will improve the performance.

7. Join in proper manner:

While joining to nodes use join between Integer or BIGINT. Joins on calculated columns/fields with NVARCHAR or DECIMAL as data types, might create performance issues.

8. Splitting the table:

If the size of the consuming table more than 2 billion we need to partition the table using HASH and RANGE portioning. That partition need to be done before consuming the table in information view.