Suppose you have Year to Year Current Month / Year to Date Comparison reporting requirement. Calculation View should take Month as an input parameter and calculate Current Month, Last Year Current Month, Year to Date and Last Year to Date Net Sales on Country level.

Input Parameter.jpgY2Y_CM_YTD.jpg

Using Table Functions makes modeling for this requirement easy. Calculation Model uses 4 Table Functions e.g. one for each measure (CM, LYCM, CYTD and LYTD Net Sales). Each Table Functions takes IP_CALMONTH as an input and returns a set of dates. Table Functions are used in Join Nodes to limit sales data respectively to CM, LYCM, CYTD or LYTD Net Sales.

Most important modeling aspect are listed below:

Generate Time Data with Day Granularity

Generate Time Data 1 of 2.jpg

In our case from 2013 to 2014

Generate Time Data 2 of 2.jpg



Define Table Functions

CM:

FUNCTION “WORKSHOP”.“workshop.00::CM” ( IN_CALMONTH VARCHAR(6) )

RETURNS TABLE ( DATE_SQL DATE )

      LANGUAGE SQLSCRIPT

      SQL SECURITY INVOKER AS

BEGIN

  RETURN SELECT DATE_SQL FROM “_SYS_BI”.“M_TIME_DIMENSION” WHERE CALMONTH = :IN_CALMONTH;

END;

CM.jpg

LYCM:

FUNCTION “WORKSHOP”.workshop.00::LYCM ( IN_CALMONTH VARCHAR(6) )

RETURNS TABLE ( DATE_SQL DATE )

      LANGUAGE SQLSCRIPT

      SQL SECURITY INVOKER AS

BEGIN

  DECLARE CALMONTH VARCHAR(6);

  DECLARE MONTH VARCHAR(2);

  DECLARE YEAR VARCHAR(4);

  YEAR := LEFT(IN_CALMONTH,4);

  YEAR := YEAR – 1;

  MONTH := RIGHT(IN_CALMONTH,2); 

  CALMONTH := CONCAT(:YEAR,:MONTH);

  

  RETURN SELECT DATE_SQL FROM “_SYS_BI”.“M_TIME_DIMENSION” WHERE CALMONTH = :CALMONTH;

END;

LYCM.jpg

CYTD:

FUNCTION “WORKSHOP”.“workshop.00::CYTD” ( IN_CALMONTH VARCHAR(6) )

RETURNS TABLE ( DATE_SQL DATE )

      LANGUAGE SQLSCRIPT

      SQL SECURITY INVOKER AS

BEGIN

  DECLARE CALMONTH VARCHAR(6);

  DECLARE YEAR VARCHAR(4);

  YEAR := LEFT(IN_CALMONTH,4);

  CALMONTH := CONCAT(:YEAR,’01’);

  

  RETURN SELECT DATE_SQL FROM “_SYS_BI”.“M_TIME_DIMENSION” WHERE CALMONTH BETWEEN :CALMONTH AND :IN_CALMONTH;

END;

CYTD.jpg

LYTD:

FUNCTION “WORKSHOP”.workshop.00::LYTD ( IN_CALMONTH VARCHAR(6) )

RETURNS TABLE ( DATE_SQL DATE )

      LANGUAGE SQLSCRIPT

      SQL SECURITY INVOKER AS

BEGIN

  DECLARE CALMONTH_FROM VARCHAR(6);

  DECLARE CALMONTH_TO VARCHAR(6);

  DECLARE YEAR VARCHAR(4);

  DECLARE MONTH VARCHAR(2);

  YEAR := LEFT(IN_CALMONTH,4);

  YEAR := YEAR – 1;

  MONTH := RIGHT(IN_CALMONTH,2);

  CALMONTH_FROM := CONCAT(:YEAR,’01’);

  CALMONTH_TO   := CONCAT(:YEAR,:MONTH);

  

  RETURN SELECT DATE_SQL FROM “_SYS_BI”.“M_TIME_DIMENSION” WHERE CALMONTH BETWEEN :CALMONTH_FROM AND :CALMONTH_TO;

END;

LYTD.jpg

Use Table Functions in Joins Nodes to limit data

Join_SO_CM 1 of 2.jpg

Join_SO_CM 2 of 2.jpg

Similary defined Join Nodes for LYCM, CYTD and LYTD Measures

Map Calculation View Input Parameter into Table Functions Input Parameters

Manage Mappings.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