Transformation Role Processing

The Transformation Role Processing property is only available from the Transformation Editor. From the Transformation Editor, select Schema Objects and then choose Transformations. Right-click an object from the right pane and select Edit.

The Transformation Role Processing property lets you choose how transformation dates are calculated when there are multiple attributes to transform. The example below considers the common Day, Week, and Month schema setup. The schema has Week and Month as a parent to Day. Week and Month are unrelated. This Month, Week, and Day hierarchy setup is a common scenario where this property makes a difference.

Example

You have a report with Week, Sales, and Last Year Sales on the template, filtered by Month. The default behavior is to calculate the Last Year Sales with the following SQL. Notice that the date transformation is done for Month and Week.

insert into ZZT6T02D01
select a14.DAT_YYYYWW DAT_YYYYWW,
 sum(a11.SALES) SALESLY
from FT1 a11
 join TRANS_DAY a12
   on (a11.DAT_YYYYMMDD = a12.DAT_YYYYMMDD)
 join TRANS_DAY_MON a13
   on (a12.DAT_YYYYYYMM = a13.DAT_LYM)
 join TRANS_DAY_WEEK a14
   on (a12.DAT_YYYYWW = a14.DAT_LYW)
where a13.DAT_YYYYMM in (200311)
group by a14.DAT_YYYYWW

The new behavior applies transformation only to the highest common child when it is applicable to multiple attributes. The SQL is shown in the following syntax. Notice that the date transformation is done only at the Day level, because Day is the highest common child of Week and Month. So the days are transformed, and then you filter for the correct Month, and then Group by Week.

insert into ZZT6T02D01
select a12.DAT_YYYYWW DAT_YYYYWW,
 sum(a11.SALES) SALESLY
from FT1 a11
 join TRANS_DAY a12
   on (a11.DAT_YYYYMMDD = a12.DAT_YYYYMMLYT)
where a12.DAT_YYYYMM in (200311)
group by a12.DAT_YYYYWW