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:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=403083
And you can only have one fact dimension per cube but with many different attributes. If you create fact dimensions as regular you do not have this limit. I was told that making single large tables with both facts and complete dimension information was a general approach in many OLAP servers 10-15 years ago. The starschema was not always supported.
Cool. I always wondered what\’s the difference.Still, I wonder how the client tools use this data
There were several original reasons for a fact dimension, but most of them turned out to be impractical — so eventually the primary reason for defining a fact dimension was to get better performance in DRILLTHROUGH scenarios (and of course the indicator for client applications that you mentioned).I believe that if a dimension is a fact ROLAP dimension then a query to the measure group through NON EMPTY that includes the ROLAP dimension — the primary purpose of this is drillthrough — will result in the SQL being generated a little differently. The SQL query will include not just the partition columns, but also the columns for the fact dimension — this results in just one query to the fact table and avoids the typical SELECT DISTINCT style queries to a fact table which will very probably be expensive.If the dimension is not marked a fact dimension, then first there will be querie(s) to the ROLAP dimension to process the dimension members, and then a separate query to the partition table.Of course, it\’s been a while since I looked at this, so I might be mistaken! :)Thanks,Akshai
TO Akshai: this explains me the same behavior I observed using drillthrough on a ROLAP dimension: the fact relationship avoid a heavy SELECT DISTINCT before sending the real drillthrough query to SQL Server. Thus, it is better to use the Fact relationship for fact dimension.
I have a column in 2 tables with all negative numbers populated (-1,-2,-3) in it, can we use this field to build relationships between those 2 tables in tabular model?
Yes, you can