Sales Orders fact table or dimension table or two tables(one fact and one dimension)?
4 posters
Page 1 of 1
Sales Orders fact table or dimension table or two tables(one fact and one dimension)?
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 doesnt 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.
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 doesnt 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
Re: Sales Orders fact table or dimension table or two tables(one fact and one dimension)?
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.
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.
Re: Sales Orders fact table or dimension table or two tables(one fact and one dimension)?
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.
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
Re: Sales Orders fact table or dimension table or two tables(one fact and one dimension)?
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.
Re: Sales Orders fact table or dimension table or two tables(one fact and one dimension)?
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
Re: Sales Orders fact table or dimension table or two tables(one fact and one dimension)?
These is a nice design tip that explains this scenario.
Design Tip 46
Design Tip 46
OrionPax- Posts : 2
Join date : 2014-05-16
Similar topics
» Modelling cutomer dimension and sales orders fact
» Relationship between fact table and dimension tables
» Sales Rep <--> Customer relationship with Sales Fact Table
» Purchase orders Fact Table Design
» Dimensional modelling for Sales and Marketing (opportunity, quotes, sales orders)
» Relationship between fact table and dimension tables
» Sales Rep <--> Customer relationship with Sales Fact Table
» Purchase orders Fact Table Design
» Dimensional modelling for Sales and Marketing (opportunity, quotes, sales orders)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum