SSAS 'Fact Dimension' v 'Regular Dimension' your views

Hi,

I have been a user of this forum for a while but haven't registered until now. First of all, hello fellow BI developers :-)

I have put a similar post on the MSDN BI Section but thought I would post here as I could get different views/answers from other BI developers. I have general design question regarding the use of a 'Fact Dimension' table relationships as can be specified in SSAS 2005+ versus seperate Fact and Dimension tables (i.e. Regular Dimensions).

I'm finding that a fair amount of my transaction data has several attributes specific to the transaction only (degenerate dimensions 'DD' I beleive is the term). Is it ever worth spliting this data into two tables (Fact and Dimension) when dealing with 'DD' or is this only necessary for regular dimensions which span multiple facts? I get the impression there are performance gains for doing this when the DD has large string values or does SSAS take care of this when you speficy the measures and dimensions from the one table.

In summary, when do people choose to use one design versus the other?

Thanks

Maybe I should have posted this in the Technical Architecture topic area...

My view is when dealing with fact tables that have degenerate dimension attributes I should create a 'Fact Dimension' in SSAS which contains the DD attributes only and it will share the same physical table as the fact data measures. Any atributes which are deemed as 'Regular Dimensions' (such as the ProductID in your example) should have it's own discrete dimension table which has the benefit of being able to implement slowy changing dimension functionality. This highlights a problem with 'Fact Dimensions' in that they can't implement SCD2 since they are directly bound to fact in which case you are better of treating them as 'Regular Dimension'

Fact dimension (aka. DD) in SSAS is natural key by itself, so the concept of SCD2 is not applicable to DD. Think of it this way, suppose you want to store it in a single attribute dimension table and have FK in the fact table, your dimension can be nothing but type1 dimension.

Thanks hang, I have given you my take on your other post in this topic area :-)