Referenced and Many-to-many relationship

Question

Suppose I got some tables with relationship like this in DSV:

fact table (key column teamNo)-> team-alias table (foreigner key teamNo, key column team-alias )-> team table (include  team-alias)

I created a dimension from team table;
created a dimenion from team-alias table;
created a measure group base on fact table;
created a measure group base on team-alias table.

Now I want to join team diemension with fact table, i can set Referenced relationship to fact table with  team-alias  intermedidate table, and it return correct result.

But I can also set many-to-many relationship with fact table with team table, and take  team-alias as intermedidate  measure group. (I created a measure group base on team-alias table)

Now  I want to konw is there any difference between them?

Thanks

Answer 1

Anyone help? thanks!

Answer 2

Hi,

I would think your first approach is the more efficient one (and in fact it might be still more efficient to join team alias and team as oen dimension table). The many-to-many relationship can also handle the case that tehre are several teams per team alias. From your description, I assume this is not the case, and hence AS will not apply optimal algorithms for your case.

Frank

Answer 3

There is some inconsistency in the following information - so some data examples would help:

- "fact table (key column teamNo)-> team-alias table (foreigner key teamNo, key column team-alias)"

- "i can set Referenced relationship to fact table with  team-alias  intermedidate table"

But it seems that the fact table has no foreign key to team-alias - rather, team alias has a teamNo foreign key to the fact table. So how can team-alias serve as a (regular) dimension for the fact table, when the fact table has no foreign key for it? Also, it looks like team-alias has no foreign key for team.

Based on just this info, the only model that makes sense to me would be:

- Create fact dimension for fact table
- Direclty relate this main fact dimension to the team-alias measure group.
- Now you can add a many-to-many relation from main fact to team-alias dimension.
- Create a measure group for team, whose fact dimension is also team (it has regular relation to team-alias dimension).
- Now you can add a many-to-many relation from team-alias measure group to team dimension.
- This will allow a many-to-many relation between the main fact and team dimension to be added.

---

- Deepak

Answer 4

Thank you Deepak and Frank!

 "fact table (key column teamNo)-> team-alias table (foreigner key teamNo, key column team-alias)"
- my mistake! it should be:
 "fact table (foreigner key team-alias)-> team-alias table (foreigner key teamNo, key column team-alias)"

So I cannot jion team dimension with fact table. I can only relate fact table with team-alias.
Now, I set Referenced relationshipe to jion team with fact table. But I can also set many-to-many. Any differences?


Hi Frank, base on your description, it seems many-to-many has bad performance, right?

Answer 5

There is still one point to clarify: team-alias table has a foreign key for team; but does team also have a foreign key for team-alias? If so, then it sounds like there is a 1:1 relation between team-alias and team, and they could be combined in a view.

---

- Deepak

Answer 6

"team-alias table has a foreign key for team; but does team also have a foreign key for team-alias?"

No. In fact, alias means the engineer alias. In my case, one engineer can change the team, So one team has many engineers and one engineer may has many teams. So the table team-alias can gave me the relationship between engineer  and team (include history information.). 

I can combine team table and team-alias with a view. But I don't want to do that for some reason.

For a classic example of Referenced relationship, if i create a measure from the intermedidate dimension, which means I can also use many-to-many, right? Even if it sounds doesn't make sense, because there is no many-to-many here, only  Referenced relationship. But I still want to konw, if I set many-to-many, is there any difference? base on my test, I cannot see the difference here.

thanks!

Answer 7

".. But I still want to konw, if I set many-to-many, is there any difference? .." - there should be no difference in results. As Frank noted, many-to-many might be slower than a materialized reference relation, if the team-alias table is very large - but you can test that.

---

- Deepak

Answer 8

Thank you Deepak, I will check the performance tomorrow.

Now, I have one big issue confused me at last few days. I cannot hit the aggregations I created even in sample cube Adventure Works. I donn't why. Could you give me some suggestion? For example, in sample cube Adventure Works, how to designe aggregation for the query:
select [Measures].[Reseller Order Quantity] on 0
,[Date].[Calendar].members on 1
from [Adventure Works]

I open a trace and clear the cache, but i cannot get the event:
"get the data from the aggregation"

here is the link
http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/e3b3fb73-a95a-4de7-92c3-d4c042f1dbf1