Few Tips on Joins in SAP HANA 2.0

[pallab_haldar]

In this blog I will discuss about some joins which is little bit unorthodox  but very useful traditional joins. Those concept will hep you when you implementing a complex modelling in HANA.

We all know about the below most used joins in SQL - a. Inner Joins b. left outer c. Right outer d. Referential Join etc. But in this discussion I will discuss about other joins in SAP HANA and their usage.

The main rule for a good modeling or view design is to use star joins for joining dimension and fact tables when possible.

**********************Important note on Star Join***********************************************************

If here is N number of dimension table joined with  fact table in stair join, all the join is executed in parallel not sequentially. This is the main key of faster execution.

****************************************************************************************************************

I can remember I involved in one critical project where only performance improvement required for calculation view. All the tree structure calculation view we converted into star joined calculation view which result large scale of  performance improvement.

If star join not possible with respect to project scenario then only use left outer join. After Start Join Left outer join is the faster one .

**********************Important note on Left Outer Join****************************************************

If two table A and B and A is on the left side out the Left Outer join then if there is no data in right side table it will not execute join with the right table and save the time and optimize the execution plan.

****************************************************************************************************************

 

Let look on the others Joins and Join properties in SAP HANA 2.0 :

## Temporal join : 

When we need to fetch data between time interval temporal join comes in picture. In Hana temporal join used to extract data from fact tables joining with the time interval column of master table.

Steps : Suppose Fact table is A and Dimension table with time dependent master data is B:

• First create a referential or an inner join between the column of the fact table and the dimension table inside a calculation view.

 

• In the Properties panel or from the New Join window, select:
  
  
  
  • The Temporal Column that indicates the single time column in the fact table.
  
  
  • The From Column and the To Column to specify the time interval from the attribute view.
  
  
  • The Temporal Condition which would be considered while executing the join.
  
  
  
  

 

• Then activate the calculation view and preview the data.

## Optimize Join in in Calculation view in HANA 2.0:

While executing the join, by default, the query retrieves join columns from the database even if you don't specify it in the query. The query automatically includes the join columns into the SQL GROUP BY clause without you selecting them in the query.

 

You can avoid this default behavior by using the join property Optimizing Join Columns. When this property for a join is set to True, only the columns specified in the query are retrieved from the database.

 

Optimizing join columns is supported only for left outer joins with cardinality 1:1 or N:1, text joins with cardinality 1:1 or N:1, right outer joins with cardinality 1:1 or 1:N, and referential joins. Optimize join columns are not supported for non equi joins.

 

Prerequisites:

1. The join field is not requested in the query


2. Only fields from one join partner are requested in the query.


3. The join is an outer join, referential, or text join.


4. The cardinality to the join partner from which no fields are requested is set to 1.

 

Text Join :  

In HANA Text Join is used in order to get language-specific data.  In Text join there will be a text table which will have similar key of the original table and additionally an language key field.

Table A is a text table of table B if the key of A comprises the key of B and an additional language key field (field of data type LANG). Table A may therefore contain explanatory text in several languages for each key entry of B. 

 

Hope this will help while  optimize your views and procedures.

In the next sessions I will discuss about the calculation view development using web IDE in HANA 2.0 XSE environment.