In first part of the blog I explained how to define Decision Table which will be used to Route Calculation View to Hot and/or Cold Storage. Now we are all set to define the view itself.

First define IP_DATE_FROM and IP_DATE_TO Direct Input Parameters

Calculation View 1 of 16.jpg

Calculation View 2 of 16.jpg

Calculation View 3 of 16.jpg

Then define IP_SOURCE Input Parameter derived by workshop.exercises:source Routing Wrapper Stored Procedure. Map IP_DATE_FROM and IP_DATE_TO Input Parameters into respective Input Parameters of the Stored Procedure.

Note: IP_SOURCE is the only Routing Input Parameter because Routing Decision Table returns a Source Range (for example, ‘HANA’,’IQ’)

Calculation View 4 of 16.jpg

Calculation View 5 of 16.jpg

Add 2 Projection Nodes: Projection_HANA (Hot Storage) and Projection_IQ (Cold Storage).

Calculation View 6 of 16.jpg

Calculation View 7 of 16.jpg

Add Union Node. Define SOURCE Target Column and make Constant Selection for the Column

Calculation View 8 of 16.jpg

Calculation View 9 of 16.jpg

Calculation View 10 of 16.jpg

Next add an Aggregation Node. This Aggergation Node is needed to apply SOURCE Column restriction which will drive pruning. Also time restriction is applied.

Note: Routing filtering expression is simplier because we have only one IP_SOURCE Routing Input Parameter

Calculation View 11 of 16.jpg

Calculation View 12 of 16.jpg

Let’s run the Calculation View with 2012-2013 data selection from Cold Storage (IQ).

Calculation View 13 of 16.jpg

Calculation View 14 of 16.jpg

Excecuted Plan indicates that the Calcuation View run against Remote Table

Calculation View 15 of 16.jpg

Let’s run the Calculation View with 2014 data selection from Hot Storage (HANA).

Calculation View 16 of 16.jpg

Calculation View 17 of 16.jpg

Excecuted Plan indicates that the Calcuation View run against HANA table

Calculation View 18 of 16.jpg

Let’s run the Calculation View with 2012-2014 data selection from both HOT (HANA) and Cold (IQ) Storages.

Calculation View 19 of 16.jpg

Calculation View 20 of 16.jpg

Excecuted Plan indicates that the Calcuation View run against both HANA table and Remote Table

Calculation View 21 of 16.jpg


To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply