I have a dimension called Dim_DueAnalysis in a multidimensional cube.
The underlying table looks like this (only a subset of the data is here):
DueAnalysis_Key DueAnalysisID DueAnalysisDays DueAnalysisIntervalWeeks DueAnalysisInterval DueAnalysisDueOverdue
1052 26 Overdue by 26 days Overdue by 3 weeks Overdue by 1-30 days Overdue
1053 27 Overdue by 27 days Overdue by 3 weeks Overdue by 1-30 days Overdue
1054 28 Overdue by 28 days Overdue by 4 weeks Overdue by 1-30 days Overdue
1055 29 Overdue by 29 days Overdue by 4 weeks Overdue by 1-30 days Overdue
1056 30 Overdue by 30 days Overdue by 4 weeks Overdue by 1-30 days Overdue
1057 31 Overdue by 31 days Overdue by 4 weeks Overdue by 31-60 days Overdue
This table looks fine and dandy.
My dimension in the cube has a hierarchy:
Due Analysis Key => Due Analysis Days => Due Analysis Interval Weeks => Due Analysis Interval => Due Analysis Due Overdue
=> Due Analysis Day Number => Due Analysis ID sort
All my Relationship Types are Flexible. But still my dimension looks like this when I browse it in my Analysis Database:
Overdue
- Overdue by 1-30 days
...
- Overdue by 3 weeks
- Overdue by 21 days
...
- Overdue by 27 days
- Overdue by 31-60 days
- Overdue by 4 weeks
- Overdue by 28 days
- Overdue by 29 days
...
As you might have noticed, days 28-30 are in the 31-60 days interval.
What I want is that part of "Overdue by 4 weeks" (days 28-30) is in the Overdue in 1-30 Days hierarchy and not in the Overdue by 31-60 days. But "Overdue by 4 weeks" should also be present in the Overdue by 31-60 days as days 31-34 should be present there.
I thought that it was just a matter of setting my relationship type to "Flexible" instead of "Rigid" but that has not solved my problem.
I am not sure what further information is needed in order to solve this case so please ask about how certain properties etc are.
Ah of course - I had to create another column specifying which week AND day interval my data was in. By using that column I got my hierarchy correct.