November 23, 2004 at 5:27 am
Hello
I'm building a data warehouse and are using SQL server 2000 and Analysis Services.
I have a situation with a many-to-many relation between one dimension table and the fact table. I know that there are several different solutions to this problem but I have not found anyone that is specific for Analysis Services. (On Microsoft’s site I’ve only found one-to-many relations between the dimension and fact table, which is quite straightforward)
For example, Kimball uses a solution with a bridge table with a weighting factor. But how does this work when building cubes with Analysis Services ?
I guess an other solution is to lower the grain of the fact table to the grain of the dimensional table, which I guess would work just fine with Analysis Services. But I don’t know if this is the "common" way of doing it or if there are other ways that are better.
Do you have any recommendations on how to solve this using Analysis Services? I would appreciate your input on how you solve this.
//Matte
November 24, 2004 at 6:50 am
If you have a many-to-many, then your design is wrong. Follow Kimball's design and you will not have a problem with the cube.
November 24, 2004 at 8:41 am
There's a very good reason you've only found one-to-many examples. Many-to-many would prove very problamatic. One solution may be using MDX for working out "custom member formula" and unary oprerators on the dimension in question. BOL should be a fair starting point to understanding custom member formulas.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply