HANA SDI FlowGraphs - SCD2 Implementation: The Other Way

[narasingha]

I am writing this Blog Post on SDI i.e, smart data integration Flowgraphs on SAP HANA XSA 2.0 SPS 05. This is nothing but an ETL technology but this SDI flowgraphs are available under SAP HANA XS  Appliance and we can create design time artifacts such as SDI flowgraphs or called as HANA flowgraphs.

So what are the various Transformations provided in SDI Flowgraphs. Please see the below Picture :

So most of the transformation are available on any ETL tool but the advantage of SDI flowgraphs is it is inbuilt with SAP HANA Appliance. Customers can use the DB parallely can create a ETL job too. is n't great !

But in any ETL tool when we deal with huge amount of data we get performance issue, long running time of the jobs. This is due to unoptimized SQL generated by the ETL jobs behind the scene. Of Course at tool level we can implement certain hot fixes and in case of HANA Flowgraphs we can increase the number of task partitions etc. But majority of the case if we see the more we push down the SQL to DB level the more in the gain. Having said this means all the calculations, Join operations, expressions, Looks ups, complex calculations are now pushed to DB level rather than on the application layer. That is why SQL based code is fast and efficient.

Am I saying ETL tools are of no use ? no I am not saying that but it differs case to case and scenarios.

Recently while working on a real time scenario we came across a scenario when the Table comparison Transform is not generating an efficient SQL and that was causing a lag in the overall load of HANA flowgraphs.

TC is a transform which is used to implement SCD type 2 and compares the source & target table based on compared columns to stamp the data.

The HANA SDI flowgraphs was generating a full outer join in TC transform and eventually causing delay even if task partitions are increased.

In those scenarios it is better to write custom stored procedure using table types in HANA rather than doing in SDI flowgraphs transformations.

Those who had worked on SAP FSDP there are various read write wrapper procedures which takes that data from run time Table Types and sends the data to target via a output table Type

Here we will implement the same method to implement SCD 2 Transformation by writing a custom stored procedure with the help of Table Types.

Tables Types are nothing but intermediate structures like a table which are used during run time.They do not hold any data but they hold the structure of the table.

Syntax to Create a Table Type 

TYPE "MyDB.db.narasingha.tabletypes::Naren_Input" AS TABLE ( Columns with Data Type etc);

TYPE "MyDB.db.narasingha.tabletypes::Naren_OutPut" AS TABLE ( Columns with Data Type etc);

Create a Wrapper Procedure

PROCEDURE "MyDB.db.Naren.Procedure::Narasingha"

The Design

Here I am going to show you a logic how tSCD 2 can be achieved with a simple procedure and further Map operation of the OUTPUT will do it's job.

 

 

By Looking into this logic we can easily implement SCD 2 scenarios if we know the PKs and columns to be compared with target.

 

Thanks,

Narasingha