445 Fiscal Year end

[osteveo]

I am attempting to put together a daily sales cube with dimensions of Scenario, Organization, Month, Year, Day, Customer, Part with the measures of Quantity, Sales Price and Material Cost.

The problem is that our company is on a 445 week month fiscal year where month 1 and 2 of the quarter have 4 weeks and the third week has 5 weeks. For example June has 5 weeks starting on May 25th and ending on June 29th or 35 days. So I can have 2 days of data on day 29. May 29th is really the 4th day of the month and June 29 is the last day of the month.

I am really getting turned around on how to resolve this. Would I need to add a week dimension, or create a look up cube. It seems if I create a look up cube, I would need to update every year. If I added a week dimension I would probably need to count the days in order to put them into the "right" sequence. I just keep getting lost in how to do this.

As you might have guessed I'm not tremendously experienced, but I didn't find any posts in this or our prior location with something along this line of question. Can anyone give me a tip, or a gentle nudge in the right direction? If this really has been covered before, can you direct me to the post?

Thank you,

Steve

[Alan Kirk]

I am attempting to put together a daily sales cube with dimensions of Scenario, Organization, Month, Year, Day, Customer, Part with the measures of Quantity, Sales Price and Material Cost.

The problem is that our company is on a 445 week month fiscal year where month 1 and 2 of the quarter have 4 weeks and the third week has 5 weeks. For example June has 5 weeks starting on May 25th and ending on June 29th or 35 days. So I can have 2 days of data on day 29. May 29th is really the 4th day of the month and June 29 is the last day of the month.

I am really getting turned around on how to resolve this. Would I need to add a week dimension, or create a look up cube. It seems if I create a look up cube, I would need to update every year. If I added a week dimension I would probably need to count the days in order to put them into the "right" sequence. I just keep getting lost in how to do this.

As you might have guessed I'm not tremendously experienced, but I didn't find any posts in this or our prior location with something along this line of question. Can anyone give me a tip, or a gentle nudge in the right direction? If this really has been covered before, can you direct me to the post?

Thank you,

Steve

I'd dump the idea of having month, year and day as separate dimensions and handle it in one of two ways. Either:
- With a Dates dimension where the N level elements represent single dates which then consolidate into months and years in a hierarchy or, if you want something less sparse;
- Have a Weeks dimension where each N level element represents an accounting week. These then roll up into month consolidations, which roll up into quarters, which roll up into years. Retain the Days dimension to split it out into separate days of week. Thus the 29th of May would be represented by the intersection of the Weeks element WE 01/06/08 and the Day element Thursday.

This also allows you flexibility for the years that you have to create as 53 week years to prevent "date drift". (Since 365 or 366 days doesn't divide evenly into 52 weeks.)

[ScottW]

Steve,

If you want to show sales in fiscal sales periods and not calendar dates then your current design is adequate ...

The problem is that our company is on a 445 week month fiscal year where month 1 and 2 of the quarter have 4 weeks and the third week has 5 weeks. For example June has 5 weeks starting on May 25th and ending on June 29th or 35 days. So I can have 2 days of data on day 29. May 29th is really the 4th day of the month and June 29 is the last day of the month.

If May 29 should show as Y=2008, M=5, D=4 then you have no problem. In an Excel or Web report you can always report the calendar date in line with the fiscal Y-M-D combination by pulling string entries out of a lookup cube. To load the data in just use this principle in reverse with a date dimension with attributes for fiscal year, month and day in month.

Personally I would go for splitting the date dimensions as it provides an easier interface to slice and dice and compare time periods. As a general principal what's best for the end user should guide the developer rather than visa versa. If you want to do any analysis on sales by day of week then you might want to even consider splitting out week (1 - 5) and day (1 - 7) from your current day in month (1 - 35) dimension.

If you want to see fiscal period and calendar period in the same cube then this is more problematic. I think from your post that this is where you are getting caught up. You could tackle this as Alan suggests by having a day dimension with separate fiscal period and calendar period rollups. The other alternative is to introduce an additional dimension to the cube "Time Period" with elements "fiscal" and "calendar". Data for May 29 would be loaded against "fiscal" as 'fiscal','2008','5','4' and against "calendar" as 'calendar','2008','5','29'. Data for either fiscal or calendar could be rule derived from the other OR you could load to both time period measures (which is what I would suggest), either way this approach will lead to duplication of data which may or may not be an issue depending on cube size and memory overhead at your disposal.

There will be more sparsity in a cube with more time dimensions but dealing with data sparsity is one of TM1's greatest strengths so this shouldn't be anything to worry about.

[osteveo]

Thank you both so much. This forum and all the people in it are incredible. I find the willingness to help outstanding.

steve