Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nicklarge
Starting Member
40 Posts |
 Posted - 2011-09-22 : 18:13:24
|
| Hello.I am trying to build a bridge table between my sales fact table and Programme dimension in SSAS 2008. Essentially any one sales transaction can fall under multiple programmes. I have created a table called DimProgramme and a table called DimProgrammeGroup as such:DimProgramme: ProgrammeKey int PK, ProgrammeName varchar(150)DimProgrammeGroup: ProgrammeGroupKey int PK, ProgrammeKey int PK, DollarWeighting float, & a unique key that is a varchar.The Programmegroup would be the intermediate table (?fact or dimension?), and Programme the reference dimension. The Programmegroup has a compound key in this because of the M2M, so I cannot use ProgGroup as a regular key because of duplicates ( (1, 1), (1, 2), (2, 1), (2, 3) etc). Am I working this correctly? I have tried creating a reference relationship, but I assume because I cannot link the ProgGrp to the fact with one surrogate that I am unable to achieve this. I have tried to do a M2M, but it tells me that there is no intermediate fact table or dimension. I am stumped, and have googled this for some time but cannot work out how to do this.I understand that the M2M dimension relationship is at a lower grain than the fact table - that is the point to this. Can anyone help?Thanks, Nick. |
|
|
|
|
|
|
|
Bridge table problem.