Time Dimension Help

[BIinNJ]

Hello All -

I am trying to create a time dimension in Transformer using the following logic for a custom calendar. 

Rules:  The calendar must consist of 52 (or 53 in a leap year) 7 day full weeks.  The year does not always start on the same day.  In 2011 it started on Dec 27, 2010, in 2012 it starts on Dec 26, 2012.  The pattern the calendar follows is the first of the month indicates a new month.  I.e. If the first of an actual calendar month is on a Saturday (such as Jan 1, 2012), in the custom calendar the first day of the first week of the custom 2012 year is Dec 26, 2011.  The 7 day week always starts on a Monday.

The first two weeks of 2012 are 12/26/11 -> 01/08/12.

We do have this data in the datawarehouse, the lowest level in the dimension is the date field.  Although I am just trying to get this working, eventually I'd like to have alternate drill down paths, but one thing at a time.

Is this possible to do in transformer? 

[wyconian]

Hi

I think that is definately too complicated for the date wizard in Transformer to handle.  You can specify the date a year starts on but I think it has to be the same date every year, just for one issue. 

Are you building the transformer model from an FM package?  If so you could possibly do something like this there but because the logic is pretty complex performance might take a hit.

If you've got a datawarehouse the other option (and probably the best) is to send this back into the warehouse.  YOu can then use all kinds of built in functions and write your own logic.

[tjohnson3050]

Instead of using the Date Wizard, you can manually build a dimension in transformer (the same way you build other regular dimensions) that uses the fields in your Date Dimension in the Data Warehouse.  Your date the lowest level will roll up correctly to the weeks and years because that relationship is correctly defined in your Date Dimension Table.