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

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

4 posters

Go down

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

Post  SnowShine429 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

Back to top Go down

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

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

Post  SnowShine429 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

Back to top Go down

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

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

Post  hang 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

Back to top Go down

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

Post  OrionPax 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

Back to top Go down

Sales Orders – fact table or dimension table or two tables(one fact and one dimension)? Empty 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

Back to top

- Similar topics

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