Generate Dimension Date

Contents:

Sample of dimension date

For every organisation, date dimension (or even time dimension) seems to be one of the compulsory dimensions. PDI supports sample date dimension generator transformation in its samples. Basically, the sample generates 40000 days' records from 1950-01-01.

Basic modification

It calendar records should be sufficient for most organisation nowadays. Of course, you can modify the number of days by simply changing the number 40000 in the first step, 40000 days: 100+ years. Moreover, you can also modify the start date from the same step.

Add new fields

The above transformation sample is only for basic calendar year date. For some organisation, you also use javascript step to create extra fields, like fiscal calendar and JDE dates.

For example, the standard fiscal year is between 01 July and 30 June. To implement it, you can add javascript like:

//fiscal year from 1/7/year-1 to 30/6/year
var fiscal_year;
if (Month.getInteger()>=7){
fiscal_year=Year.getInteger()+1;
}
else {
fiscal_year=year_number;
}

//fiscal_month_number
var fiscal_month_number;
if (Month.getInteger()>=7){
fiscal_month_number=Month.getInteger()-6;
}
else {
fiscal_month_number=Month.getInteger()+6;
}

Incorrect quarter number in the sample

The calculation of quarter number is incorrect in the transformation. For example, July is in Q2; and October and November are in Q3. So far, we did spent much time to find out the reason of mistake. Perhaps, the error might be related to date type (number, integer) or rounding issue in PDI.

However, we do have a correct edition of date dimension transformation, Generate D_DATE AU style. In the transformation, the calculator step was modified for fixing the bug.

Little tips about Dividing in PDI Calculator

  • Field A divided by Field B
  • Field A and Field B can be either integer or number
  • The result of dividing is always number, even for 1 divided 1.
  • The result can be formatted and cast in integer
  • Field B will be rounded before dividing, if Field A is an integer. In this case, it is a little bit risky. For instance, if 1 is divided by 0.3, 0.3 is going to be rounded to 0 before dividing.

Generate D_DATE AU style

The transformation, Generate D_DATE AU style, is original from PDI Chief Data Integration, Matt Casters, and modified by Breadboard BI and BizCubed Pty Ltd. Like mentioned above, it solve the quarter issue. Besides, it also

  • DAY_DESC format is day-month-year. This is good for Australia (and some other countries as well)
  • Weeks run from Sunday to Saturday

Alternative Quarter method

If you still doubt the output of the quarter number from the transformation, Generate D_DATE AU style, you have another option. The alternative option is, actually, even simpler. What you need to is only creating a mapping table with 12 records, which maps month numbers with quarter numbers

References

Creator: Ming Mu on 2010/02/08 11:31
XWiki Enterprise 1.7.2.16857 - Documentation