Cube design by Day

[Mems]

Hey All,

I am designing a Sales cube that gets populated per day.
*Part of the deliverable is to implement spreading functionality.*

On Sundays they have no Sales and Saturdays it is almost double.

I have explored some white papers. Has anybody implemented the: '3 Time dimensions' before:
Year,
Day,
day of the week.

Can you spread year on year with this setup. I have tried but without success. Is there another way?

Thanks,

[lotsaram]

Hey All,

I am designing a Sales cube that gets populated per day.
*Part of the deliverable is to implement spreading functionality.*

On Sundays they have no Sales and Saturdays it is almost double.

I have explored some white papers. Has anybody implemented the: '3 Time dimensions' before:
Year,
Day,
day of the week.

Can you spread year on year with this setup. I have tried but without success. Is there another way?

Thanks,

How exactly would your 3 proposed time dimensions work and how much have you thought it through?
- Year (well that's obvious)
- Day (1 to 365 with rollups to months or sales weeks or both? Hardly intuitive or easy to use for the user and also creates significant spartity)
- Day of Week (1 to 7 or Mon to Sun)

My first suggestion would be that you would be better served using a month or week dimension in addition E.G.
- Year
- Month (1 to 12)
- Day in Month (1 to 31)
- Day in Week (1 to 7 or Mon to Sun)
OR
- Year
- Week in Year (1 to 53)
- Day in Week (1 to 7 or Mon to Sun)

Note in the 1st option day in week is strictly optional from a data storage POV as it adds no additional information as the weekday can be determined from the date as there is a 1:1 relationship between year/month/day in month and weekday. However I would include it as it does add context and reporting richness and ability for users to analyse data that wouldn't be available otherwise. Especially in a retail sales cube context intra-week sales trends can be very important. This design also has the advantage that the date of transaction is as per the location in the cube as it is given by the year/month/day in month coordinate. The main disadvantage of this design is that if weekly analysis is important there is no way to do this easily.

The 2nd option let's you do weekly analysis but has 2 disadvantages
1/ the transaction date isn't natively encoded in the cell coordinates. You would need to also have a time mapping cube to hold the date at the year/week in year/day in week intersection which you could then use in an Excel report to add date context (or even pull the date from the mapping cube into a "date" measure in the main cube)
2/ you would more than likely need to maintain separate week into month rollups for each year

Of course there's also a 3rd option of including both month and week in year dimensions in the same cube which then let's you do everything. However I would argue against this as it adds complexity for the users in needing to select "all weeks in year" to use the "option 1 configuration" or combination of "all months in year" and "all days in month" to use the "option 2" configuration. Because of this IMO it is better to have 2 cubes and duplicate the data should both of these views of the sales data be needed.

[NOTE: there's also another option (I guess we are up to "option 4") which also lets you do both the monthly and weekly analysis together in the same cube which it to have 3 time dimensions of Year, date in year (date without year), and weekday. Similarly the transaction date is natively encoded but you can still have alternate rollups for month and week in year in this dimension. Still probably need to have alternate week in year rollups for each year. Maybe this is what you meant with your proposed design??]

For your other question yes you can spread (with either option). The way spreading works in from member to single (leaf) member. So in any design where you have a separate year dimension you can spread from one year to the next no problem. If you have a date, week-year or month-year dimension that rolls up to year as a consolidation then you can't spread from one year to the next as from a database perspective TM1 has no concept of which individual leaves to match up and copy values across. HOWEVER although the spread itself will work without issue as long as there is a separate year dimension I com back to the point of the database having no knowledge of how to match elements the copy is 1:1 for all populated leaves in the same pattern therefore for the 1st option for example same date is copied to same date in the next year regardless of the day of week being different. There's no way to get around this except by replacing manual spread with a TI process which can handle to logic of the data vs. day of week mapping and offsets from one year to the next.

Follow?

[Mems]

lotsarem,

Mate you are a lifesaver, thanks for the detail discussion it is very much appreciated. The pilot kicks off today, I will give you feedback on what I have chosen.

Still probably need to have alternate week in year rollups for each year. Maybe this is what you meant with your proposed design??]

- precisely.

Once again, I am much obliged and I tip my hat to you old wise one

Regards,