Using Table Functions to Model Y2Y CM / YTD Comparison Calculation View

[uladzislau_pralat]

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.

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
• Define Table Functions
• Use Table Functions in Joins Nodes to limit data
• Map Calculation View Input Parameter into Table Functions Input Parameters

Generate Time Data with Day Granularity

In our case from 2013 to 2014

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;

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;

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;

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;

Use Table Functions in Joins Nodes to limit data

Similary defined Join Nodes for LYCM, CYTD and LYTD Measures

Map Calculation View Input Parameter into Table Functions Input Parameters