Using Decision Table to Route Calculation View to Hot and/or Cold Storage – Part 2

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

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’)

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

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

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

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

Excecuted Plan indicates that the Calcuation View run against Remote Table

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

Excecuted Plan indicates that the Calcuation View run against HANA table

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

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