Implementation of WTD, MTD, YTD in HANA using Input Parameters Derived from Table

Please note that the following model has been implemented in SPS06 (revision 60 and onwards). I am not sure about the feasibility of this approach on the earlier revisions. The observations on the below model are based on my personal experience and opinion.

This approach outlines the functionality of Derived From Table property of Input parameters. With this functionality the SELECT FROM logic in Scripted Calculation view can be replaced eliminating the need to create Scripted Calc view for data derivation required for the Input Parameter value ranges.

Projections with Constant columns for effective query pruning for performance

Input Parameters for Filter pushdown

Derived Input parameters to implement data derivation logic

To implement the data derivation logic, you may have to use the mapping table which contains the required data already mapped and persisted in the system. The derivation is based on the data of this table. The Derived Input Parameter would provide the logic similar to:

SELECT mapping_value FROM mapping_table

WHERE lookup_value = user_input_value;

In the case to derive the data range for WTD, MTD and YTD, a mapping table is defined which contains the Week Start Date, Month Start Date and Year Start date mapped to each of the Calendar Day. The Input parameter with Derived from Table performs lookup on this mapping table to derive the required dates for the date ranges.

The mapping table can be defined as follows:

The mapping table has mapping for each of the date with the required Start dates for Week, Month and Year.

The input parameters can be defined as follows:

The input parameter can be used in the Projection filter as specified below:

The query on the model can be executed without impacting the performance.

The execution plan shows that the filter has been pushed down and the projection filter has been implemented.

In my personal opinion, this approach is most recommended. It provides all the positive features of Graphical Calc views like:

Ease of development using Graphical option

Projection pruning with Constant columns

Filter Pushdown with Input Parameters

Derivation logic in the Input Parameters

The mapping table to derive the data is mostly available in the application model. In case, the derivation logic is complicated, then it may require to simplify the logic and persist the data in the system.

What I meant was, mostly you don’t need to create the table specially for this purpose. In my case, such mapping table was already available for some of the lookups required in the transaction data flow.

But in case, even if you need to create a new table, it is still worth creating as it may be one time effort. Or as you mentioned, it can be populated using a procedure as and when the mapping data changes.

When I seen the title of this document, I thought someone replied to your old document but today I realized when I opened this. Input parameters derived from table is something new to me. Really a good one. Keep up the good work.

So is it the case that only the first row value of the table is picked up in the input parameter? Meaning, if i want to see the entire list of values from the table in the input parameters, how can it be achieved?

Im trying to create a dynamic time dimension and struggle with NULL values in the start schema. I always see a NULL value that shows the aggregated vault of the records Im not interested in. Here is what I have done:

Imported a time table

Created two calculated view with script view, one for all the dates and one for the current day.

Created a calculated view to run union on the two views created in step 2.

Created a start join view and joined the fact table to the date field that has all the dates.

Here is the result… Got any tips for removing the NULL value in HANA? I know I can do it in Lumira but it’s not so user friendly…..