Is it possible to use a Time dimension as the intermediate dimension in a many-to-many relationship? So far, I have not been able to make it work. When I attempt to setup the relationship, I get a warning on the Define Relationship window that says:
This relationship cannot be defined because no intermediate measure group or dimension table exists.
However, the intermediate measure group and dimension tables do exist. The New Cube... wizard picks up the tables but creates all the relationships as Regular, in effect I have two separate measure groups. Do I have to do something special to define a measure group as intermediate?
The business problem I'm trying to model here is special time periods that can overlap - think college and university semesters. I can have a semester where any given day (the granularity of my time dimension) can exist in multiple semesters. So I am trying to create a many-to-many relationship from my "semesters" to an intermediate mesaure group to time dimension to real measure group.
Any help or guidance is much appreciated!
Hello. Check for intermediate dimensions in BooksOnLine. This is what I think you are trying do do. Many-to-many dimensions are between a normal fact table and a many-to-many artificial fact table with the many-to-many relation with a dimension table.
A good example is an account that is shared between two persons. You have the single account in the dimension and the many-to-many relation between the persons and the account in the artificial fact table.
Here is a link to a Blog about the new dimension types in SSAS2005: http://blogs.conchango.com/christianwade/archive/2005/04/07/1255.aspx
An intermediate dimension is when you do not have a direct relation between a fact table and a dimension. Normally you have a direct relation.
The use for this type is more limited since that you can always enter more dimenion keys into a fact table.
I would think about two new attributes in the time dimension like:
-CollegeSemesterDayFlag
-UniversitySemesterDayFlag
HTH
Thomas Ivarsson
|||Thanks for the response Thomas. I do want a many-to-many dimension, I just didn't explain my business problem clearly. I have potentially multiple, overlapping, arbitrary time spans (again, think college/university semesters), where any given day can exist in multiple semesters. This is very similar to the example you gave above with the accounts shared between multiple people. My problem is that I could have a semester called FALL from Sep1-Dec31 and a semester called FALL_SPECIAL Oct1-Oct31, where the date range Oct1-Oct31 falls into two semesters. Also, from year to year, there will be different semesters - some years won't have a FALL_SPECIAL, but have a WINTER_SPECIAL, etc.
Anyway, I solved my problem and it was because the New Cube... wizard didn't find all the relationships. I am using my time dimension as the intermediate dimension and it is also acting as a role playing dimension. The New Cube... wizard didn't pick up all those relationships, so I had to manually setup to the many-to-many relationship for all five of my role playing time dimensions, only then it recognized the second fact table as an intermediate fact table. I also had to delete a time dimension that showed up in the Dimension Usage tab that didn't belong but the New Cube... wizard put in there.
I haven't explored all the data to make sure it's aggregating correctly, but it appears to be doing what I want and I'm quite impressed I might add! I just need to verify that the ParallelPeriod() and Cousin() functions behave properly.
|||Great that you have solved the problem.
I think that this is no more than standard many-to-many modelling.
You have several colleges and universities on top with their own semester schedule and this means that it will be a many-to many relation with the time dimension.
This means an education institution-semester dimension on top of the time dimension.
Regards
Thomas
sql
No comments:
Post a Comment