Fact dimension relationships
If you build a dimension directly off a fact table (a fact dimension), when you add it to your cube and set the relationship between that dimension and the fact table you have a choice about the type of relationship you can use: you can use either a Fact relationship type or a Regular relationship type. I’ve always used Fact relationships but never known whether there was anything special about them – there was in some of the early Yukon betas but the functionality got dropped before RTM.
I asked someone on the dev team about this a few years ago and they admitted they didn’t know. I finally got round to asking again last week and TK Anand obligingly gave me an answer: fact relationships are essentially the same as Regular relationships, the only difference is that you can see the relationship types in the metadata, and some client tools may choose to display fact dimensions differently. So now you know.
UPDATE: Marco Russo (who I’m working with on… something at the moment), has done more research on this and it turns out there is an advantage to using a fact relationship when it comes to the SQL generated for drillthrough. Unfortunately the SQL generated is only slightly less rubbish than with a regular relationship. Marco has also opened an item on connect asking for drillthrough to be fixed which I urge everyone to vote on! You can find it here: