How to fit a week into a calendar time hierarchy?

Question

As is usual with cubes, the users want things that don't fit into a hierarchy to be displayed hierarchically. They'd like to see Day > Week > Month > Quarter > Year as the hierarchy, but the problem with weeks is they can be part of 1-2 months, not just 1 month (and by extension part of 2 quarters, semesters, years).

So my question is: how to set up the attribute relationships, and how to set up the hierarchy? Here is what I have, but I know it's not optimal.

Hierarchies (cycle == weeks):

Attribute Relationships:

There is no Cycle -> Year, because it is a many-to-many relationship

Answer 1

There are four types of weeks to concern yourself with:

1. Week of Year (1-53). Hierarchy: Year > Week

   You should decide whether you want Week 1 to begin on Jan 1, or follow the ISO standard

2. Week of Month (1-5). Hierarchy: Year [> Quarter] > Month > Week

   You should decide whether you want Week 1 to begin on the first day of the month, or the first Sunday/Monday of the month.

3. Week of Fiscal Year (1-53). Hierarchy: Fiscal Year > Week

4. Week of Fiscal Month (1-5). Hierarchy: Fiscal Year [> Quarter] > Month > Week

Answer 2

You can leave the design as you have it, or even add the weeks to the "Calendar Time" hierarchy, thus only having one hierarchy as requested by the users. This is just what Microsoft calls a non-natural hierarchy. Analysis Services query response time is much slower for these than for natural hierarchies (those with all child levels having a relationship to their parent level). That is why you get a warning in BIDS. You just will have to test if the performance is good enough for your users. If it is, fine. If it is not, maybe fall back from the one-hierarchy solution to the two hierarchy solution described in your question. Then at least the first hierarchy would be fast, and only the second have bad performance.

There are other solutions, but they are all a bit unnatural: Some companies define months to have exactly four or five weeks so that the relationship can be set, but a reporting month starts or ends a few days before or after the calendar month. See e. g. the 4-4-5 calendar article in Wikipedia.