many to many relationship question

hi,
i have a few tables in my data warehouse that need to be set up with a many to many relationship in my cube. The tables relate to institutions being members of one or more institution groups for different periods of time. so i have a DimInstitution table with InstitutionKey and InstitutionName, a DimInstitutionGroup table with InstitutionGroupKey and InstitutionGroupName, and i have a 'bridge' table that relates the Institutions to InstitutionGroups called DimInstitutionGroupMembers with InstitutionGroupKey , InstitutionKey, StartDate (not nullable), EndDate (nullable, if null means institution is still a member of this group, if the end date is set it means the institution was a member of the group from start date to end date). A unique constraint is set up on the table also with the InstitutionGroupKey, InstitutionKey and StartDate (to allow for an institution to be a member of a group and become a member again at a later date with a gap in the middle).

Our users need to see the group membership as at specific dates, so if they are querying data reported by a group of institutions in May 2011, they want to see only the data for those institutions that were members of the group at that point in time.

I am very new to this cube design (it's my first data warehouse, etl, cube work at all) and am pretty sure i need to use a many to many relationship and have seen many examples of how to do this. but the versions is confusing me. does anybody know where i might find an example of a many to many relationship that uses start and end dates also??

Hi,
In star shema you can denormalize your tables, your Institution Dimension will look like:

­ SurrogateKey
­ Original Institution Key,
­ Institution Name,
­ Original Group Key,
­ Group Name,
­ Start Date
­ End Date

Let’s see an example:
Original Institution
1 A
2 B
3 C
4 D

Original Group
10 X
11 Y
12 Z

Intersection ( I wrote just years)
1 10 2010 2011
2 10 2010 2011
3 11 2010
1 12 2011
2 11 2011

And the denormalized new Dimension table based on the original intersection table having the Names from the Institute and Group (plus the Surr Key)

Surr Key Ins Key Ins Name GroupKey GroupName Start End
1 1 A 10 X 2010 2011
2 2 B 10 X 2010 2011
3 3 C 11 Y 2010
4 1 A 12 Z 2011
5 2 B 11 Y 2011

Groups in 2010: X,Y
Groups in 2011: Y,Z

thanks gvarga for your reply. i'm still confused. my inst group mem table already has inst key / group key / start date / end date so the only difference with the one u suggested is the addition of the names and surrogate key.
taking your example institution names: institution A and institution B report data for January 2011. institution A is in groups X and Z for those dates. the user might want to view all institutions in group X in one query and in group Z in another query. my fact table has the institution A and date January 2011 as dimensions.
how do i set up this relationship in ssas that allows a user to select group X and see my fact record for institution A january 2011, or select group Z and see the same record?

OK, I forgot to write that you have to change the relationship between Fact table and the new Dim table: instead of Inst Key you will have to relate your fact data to the new Dimension by the SurrKey. That means all your fact rows will be connected with the actual institution and ACTUAL group. ( Actual means in time of the transaction)

hi gvarga sorry to be a pain, and i may be misinterpreting your answer. but if my fact table has a surrogate key referring to one record on the dim table then it does not answer my problem. for the exact same date / time an institution can be in any number of different groups. And a user might want to see the fact record as a result of querying group X and also see that very same fact record as a result of querying group Z.
So an institution might be in group X "AmandasInstitutions" but might also be in another group Z at the exact same time called "AllLargeInstitutions". A user might want to query a figure as reported by any institution that happens to be a member of "AllLargeInstitutions" which might have institutions A,B,C,D,E,F in it, for January 2011. Institution A might have reported a figure of 11million dollars for January 2011. Institutions B, C, D, E and F all reported figures too. The user wants to see all the figures reported by these institutions. The user might then want to see the data reported for the group "AmandasInstitutions" which could contain a mixture of large instittutions and small institutions, might have A, C, D, Q, R in it. They want to see that 11million dollars January 2011 figure in this query also (along with the figures for the other institutions in the group for that date also). If the surrogate key is on the fact table it would have to be either the surrogate key corresponding to the A / Z group membership or the A / X group membership wouldn't it? But I want to be see both group memberships for the same fact. and potentially more...

Ok, I understood now that there are more relationship at a time .

There is a suggestion to store the M:N info in Institution dimension

Inst Key
Name
A groupKey
A group Name
Start Date
End Date
B groupKey
B group Name
Start date
End date etc

So you will have 4xnumber of groups column to store the M:N relationship
If there will be a new group added, you have to maintain your dim table.

gosh this is going to be tricky - because the group membership can change really whenever ... and its stored on another system and maintained by the users so we wouldn't necessarily be told about new groups that institutions might be added to (of course the fact that the group might be missing from the cube would prompt them to ask of course). but there would be no way to design up front for all the possible groups an institution might be a member of. We might have ten fields for Group Membership 1 Start Date 1 End Date 1... Group Membership 10, Start Date 10 and End Date 10, but then the users might add an institution to an eleventh group and our database design would have to change. There is probably a high enough number above which our users wouldn't likely go in fairness...

If I did choose this design how would i associate the fields on the institution dimension table "A Group Key", "B Group Key" etc to a 'Group' dimension in the SSAS project?

Thanks

Sorry, I work in relational db, where this can work perfectly.
But dimensions change in real word, so it has to work in multidimensional dbs too.

Amanda,
you might want to have a look at this paper.

http://www.sqlbi.com/Default.aspx?tabid=80

It covers a few many-to-many design patterns which may help.

You probably need to build two cubes, one for institutions (no group level attributes) and another for groups (no instution level attributes). Cubes cannot handle many-to-many relationships.

For the group level cube, you may be able to introduce a drill-through option if users want to see indiviual institutions in the group.

ponzie wrote:Amanda,
you might want to have a look at this paper.

http://www.sqlbi.com/Default.aspx?tabid=80

It covers a few many-to-many design patterns which may help.

Hi Amanda

I also highly recommend reviewing that paper - it will open your eyes to a whole new world showing how cubes can model many-to-many relationships and present the data naturally, without needing to compromise your design.

Good luck!
Mike

I guess you could have a snapshot factless fact table to reflect the historical m-m relationship between the groups and institutions. It's very similar to your DimInstitutionGroupMembers table but with a snapshot date key instead of start date and end date. This type of table is also called coverage fact table and goes in synch with other snapshot fact tables. In the cube, a single date dimension can properly delineate related facts in different fact tables if they share the common snapshot dates.

thanks all - i think the snapshot factless fact table sounds the best fit for my problem. a table with all possible combinations of inst / inst group / reporting date (this solution was also just recommended to me by a teacher of a course i recently attended).
thanks all!