Sales Orders – fact table or dimension table or two tables(one fact and one dimension)?

View previous topic View next topic Go down

Sales Orders – fact table or dimension table or two tables(one fact and one dimension)?

Post  SnowShine429 on Thu May 08, 2014 11:38 am

Hi all,

I am building a DW and I have a table called Sales Orders -this table stores item, order number, customer, number, order date, amount, quantity, discount, unit price etc. I had encountered this scenario before and I created two tables in the DW – one fact table and other dimension table. The fact tables has all the measures and the keys and the dimension table stores attributes.

I have seen some designs where everything was thrown into the fact table - this design doesn’t make sense to me in my scenario because SSAS treats dimension tables and fact tables differently.

However, the problem with creating a separate fact and dimension table is that we end up with a huge dimension table.

How do you guys deal with this and what are your recommendations?

Thanks in advance for your help.

SnowShine429

Posts : 36
Join date : 2013-02-16

View user profile

Back to top Go down

Re: Sales Orders – fact table or dimension table or two tables(one fact and one dimension)?

Post  ngalemmo on Thu May 08, 2014 12:18 pm

What do you mean by "everything was thrown into the fact table"?

Normally 'Sales Order' is not a dimension. Common practice is to break down the context information into appropriate dimensions that are carried in the fact. Things like customer, dates, terms, shipping address, etc… Any other left over attributes are placed into 'junk' dimensions.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Sales Orders – fact table or dimension table or two tables(one fact and one dimension)?

Post  SnowShine429 on Thu May 08, 2014 1:03 pm

thanks for your reply,
can you please provide more details?

As an example, where should I keep the order number, tracking number etc? If you suggest putting these in the fact table, SSAS can't let us use these fields unless we create a dimension off of this fact table in SSAS. Please advise.

SnowShine429

Posts : 36
Join date : 2013-02-16

View user profile

Back to top Go down

Re: Sales Orders – fact table or dimension table or two tables(one fact and one dimension)?

Post  ngalemmo on Thu May 08, 2014 3:42 pm

I can't speak for SSAS, but standard practice is to place them as degenerate dimensions on the fact. I would seem to me if these columns were defined as VARCHAR SSAS would assume them to be dimensions rather than measures.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Sales Orders – fact table or dimension table or two tables(one fact and one dimension)?

Post  hang on Mon May 12, 2014 8:45 pm

In SSAS term, Degenerate Dimension (DD) is also referred to as fact dimension. As Negalemmo suggested, order and tracking number are definitely DD and they should all be used as dimensions in dimensional modelling, and of course in SSAS as well. Even for a numeric attribute or a metric, you can configure it into a dimension in SSAS if you like. Google the term 'Fact Dimension' in SSAS, it should be pretty easy to implement.

hang

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

View user profile

Back to top Go down

Re: Sales Orders – fact table or dimension table or two tables(one fact and one dimension)?

Post  OrionPax on Fri May 16, 2014 4:02 am

These is a nice design tip that explains this scenario.

Design Tip 46

OrionPax

Posts : 2
Join date : 2014-05-16

View user profile

Back to top Go down

Re: Sales Orders – fact table or dimension table or two tables(one fact and one dimension)?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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