Many to Many dimensions setting in SSAS 2005/2008

Question

Hello, 

Below is my scenario. I have a cycle ID of a machine. One Cycle ID can have many design IDs at the same time time and Many machine cycle can be operating on the same design at a given time. I have a Fact table called "Fact_Cycles"(CycleID,...). I created a Bridge table having Lookup_Cycle_Design(CYCLEID, DESIGNID, TIME_START, other attributes). So the table looks like below

101 , 1 ,  2012-02-24 20:09:14
101 , 2 ,  2012-02-24 20:09:14 
102 , 1 ,  2012-02-24 20:30:14 
102,  3,   2012-02-24 20:30:14 

I created Dimension based of this two table. Thus now Fact_Cycle and Lookup_CYcle_Design are dimensions. Then in I created a Measure group of "Lookup_Cycle_Design"

In my Dimension usage it looks like this image. I have highlighted the Many to Many usage. What should I do with other dimensions i.e. Date, Machine etc. Should they also have the "Many-to-Many" relation under "lookup cycle design" measure group ?

I did this same structure. But I am not getting intended results. i.e when I drag Design_ID and Date, I should be able to get the cycle count accurately. When I drag the cycleid date and date. I should get the Design IDs accurately.

Answer 1

What should I do with other dimensions i.e. Date, Machine etc. Should they also have the "Many-to-Many" relation under "lookup cycle design" measure group ?
--You need not relate them as you did.

If "Fact DZ Cycle" is your dimension for Cycle - it should have M-M with Fact DZ Cycle measure group using "Lookup Cycle Design" as intermediate fact less fact table

And "Look Cycle Design" cant act as dimension for Design. You need to create another dimension for Design. your design ideally look like below.

Answer 2

In short I have two tables now. I need to create Dim_Cycles and Dim_Design so as to complete my design.

Answer 3

Yes

Thanks

Please mark as answer if it is helpful

Answer 4

Hello,

As per directions given my mcprakash. it worked

I created 2 dimensions and one bridge table

Dim_RT_Design (Design_ID, Design_Name)
Dim_Cycle(CycleID)
Lookup_Cycle_Design(Cycle_ID, Design_ID)

I already had a Fact_DZ_Cycle. Below is the screenshot of the implementation.

Thanks!!