Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

2 posters

Go down

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

Post  Ham09 Tue Jul 26, 2011 12:11 pm

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




Ham09

Posts : 8
Join date : 2011-07-26
Location : United Kingdom

Back to top Go down

SSAS 'Fact Dimension' v 'Regular Dimension' your views Empty Re: SSAS 'Fact Dimension' v 'Regular Dimension' your views

Post  Ham09 Wed Jul 27, 2011 11:22 am

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'


Ham09

Posts : 8
Join date : 2011-07-26
Location : United Kingdom

Back to top Go down

SSAS 'Fact Dimension' v 'Regular Dimension' your views Empty Re: SSAS 'Fact Dimension' v 'Regular Dimension' your views

Post  hang Wed Jul 27, 2011 10:03 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

Back to top Go down

SSAS 'Fact Dimension' v 'Regular Dimension' your views Empty Re: SSAS 'Fact Dimension' v 'Regular Dimension' your views

Post  Ham09 Thu Jul 28, 2011 5:35 am

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

Ham09

Posts : 8
Join date : 2011-07-26
Location : United Kingdom

Back to top Go down

SSAS 'Fact Dimension' v 'Regular Dimension' your views Empty Re: SSAS 'Fact Dimension' v 'Regular Dimension' your views

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum