Skip to Content
Author's profile photo Ravindra Channe

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

Usual disclaimer:

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.

As mentioned in the earlier blog Implementation of WTD, MTD, YTD in HANA using Script based Calc View calling Graphical Calc view, let’s look into an option to populate the Input parameters for the Graphical Calc view using the enhanced functionality of Input Parameters.

Rules of the game remain same i.e. same business requirements, assumptions, Sample data and Projections with Constant columns for Pruning, as mentioned in the earlier blogs:


Implementation of WTD, MTD, YTD in HANA using Input Parameters only,

Implementation of WTD, MTD, YTD Period Reporting in HANA using Calculated Columns in Projection

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.

The View model can be defined as follows:

View_Model.JPG

The key features of the model are as follows:

  • Graphical Calc View, providing query column pruning (as compared to the Scripted Calc view)
  • 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:

table_data.JPG

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:

input_parameter_setting.JPG

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

Projection_filter.JPG

input_parameters.JPG

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

Query_output.JPG

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

execution_plan.JPG

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.

As always, please feel free to comment on the document.

Thanks for your time and encouragement so far…

Assigned tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Ravi,

      First of all thanks !!!

      I believe this mapping table is filled via a procedure based on as current date, right ?

      I didn't get your point when you mentioned The mapping table to derive the data is mostly available in the application model.

      Thanks & regards,

      Jomy

      Author's profile photo Ravindra Channe
      Ravindra Channe
      Blog Post Author

      Hi Jomy,

      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.

      Regards,

      Ravi

      Author's profile photo Former Member
      Former Member

      Ravi,

           Can we pass one Input parameter as filter condition in another input parameter definition of type derived from table?.

          For eg, When you define INPUT PARAMETER(INPUT_WEEK_DATE_FROM), you have passed $$INPUT_DATE_TO$$ in the filter condition?. Will this work?.

      Lars Breddemann --- I feel, This will be really helpful to everyone who is reading this blog.

      Regards

      Suresh

      Author's profile photo Vivek Singh Bhoj
      Vivek Singh Bhoj

      Good blog Ravi!

      This will be very helpful.

      In addition to the Implementation of WTD, MTD, YTD in HANA, this blog also provides a good approach for improving performance of HANA Model

      Thanks for sharing

      Regards,

      Vivek

      Author's profile photo Ravindra Channe
      Ravindra Channe
      Blog Post Author

      Thanks Vivek. 🙂 .

      I try to write the blogs as informative as yours. Thanks for the inspiration.

      Regards,

      Ravi

      Author's profile photo Vivek Singh Bhoj
      Vivek Singh Bhoj

      Thanks Ravi 🙂

      I have learned a lot from you through your discussions.

      I would say you,Lars and Thomas are pillars of HANA at SCN as you all are always ready to help everyone at SCN by taking time out of your busy schedules.

      Regards,

      Vivek

      Author's profile photo Rama Shankar
      Rama Shankar

      Great Job Ravi - Thanks ! 🙂

      Author's profile photo Raj Kumar Salla
      Raj Kumar Salla

      Hi Ravi,

      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.

      Regards

      Raj

      Author's profile photo Former Member
      Former Member

      Hi Ravi

      Excellent blog thanks.

      Regards

      Pat

      Author's profile photo Former Member
      Former Member

      Hi Ravi,

      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?

      Await for reply, thanks!

      Su

      Author's profile photo Kamal Mehta
      Kamal Mehta

      Hi Ravi,

      Thanks for sharing. Veru useful.

      Is there any Procedure to be run on periodic basis for populating the mapping table . Can you please share that as well.

      Thanks

      Author's profile photo Former Member
      Former Member

      Hi Ravi,

      thanks alot for the valuable blog,while i am creating the above scenario i am getting error that "Calculation instance error",can you please hepl to find this??

      Thanks in advance..

      Madhu.

      Author's profile photo Jesus Gallego
      Jesus Gallego

      hi everyone, this post was very helpful for me.

      I have a question. i want that input_date will be dinamic.

      so this way in design studio i would able to put manually a date then hana view give me dinamically the input_Date and input_month.

      I.E:

      input_date in the view its empty.

      i run this view in design studio, user choose in prompt 30/01/2015 , so all the view is refreshed with new input_date:30/01/2015 and input_date_month: 01/01/2015

      thanks

      Author's profile photo Former Member
      Former Member

      Ravi/Lars,

           Is passing the INPUT PARAMETER($$INPUT_DATE_TO$$) as filter value(Where condition) is still relevant in another input parameter in derived from table parameter type?.

      Lars Breddemann

      Justin Molenaur

      Regards

      Suresh

      Author's profile photo Lars Breddemann
      Lars Breddemann

      Please post a question into the discussion forum instead of the blog comments.

      Besides that, please consider rephrasing your question, since i just don't understand what you want to know...

      Author's profile photo Former Member
      Former Member

      Hi

      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:

       

      1. Imported a time table
      2. Created two calculated view with script view, one for all the dates and one for the current day.
      3. Created a calculated view to run union on the two views created in step 2.
      4. 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.....

       

       

      the union

      the star join