Order dimension vs. order degenerate dimensions in the fact table!?
2 posters
Page 1 of 1
Order dimension vs. order degenerate dimensions in the fact table!?
Hello,
I have a dilemma about a couple order attributes. I have following order attributes that I need in our data warehouse but am not sure how I should go about including them in the data warehouse:
- OrderDate (role-playing dimension implemented through a view)
- OrderNumber (degenerate dimension)
- OrderStatus (have not implemented yet)
I was wondering what the best approach would be when it comes to including these order attributes.
Should I include OrderStatus in my fact table as another degenerate dimension or should I create a new dimension, Dim_Order, and include in it OrderNumber, OrderStatus, and potentially another 1 or 2 order attributes such as OrderMarket (US/Canada/MX...)? What is the best practice in a situation like this?
From reading some older posts here I've seen that most of the times it is not recommended and fairly uncommon to create a separate Order Dimension but I wasn't able to find why. On the other hand, having an Order Dimension would allow me to implement SCD on OrderStatus (and some other attributes if needed).
Does anyone have a recommendation?
Thanks!
I have a dilemma about a couple order attributes. I have following order attributes that I need in our data warehouse but am not sure how I should go about including them in the data warehouse:
- OrderDate (role-playing dimension implemented through a view)
- OrderNumber (degenerate dimension)
- OrderStatus (have not implemented yet)
I was wondering what the best approach would be when it comes to including these order attributes.
Should I include OrderStatus in my fact table as another degenerate dimension or should I create a new dimension, Dim_Order, and include in it OrderNumber, OrderStatus, and potentially another 1 or 2 order attributes such as OrderMarket (US/Canada/MX...)? What is the best practice in a situation like this?
From reading some older posts here I've seen that most of the times it is not recommended and fairly uncommon to create a separate Order Dimension but I wasn't able to find why. On the other hand, having an Order Dimension would allow me to implement SCD on OrderStatus (and some other attributes if needed).
Does anyone have a recommendation?
Thanks!
dk2014- Posts : 15
Join date : 2014-11-10
Re: Order dimension vs. order degenerate dimensions in the fact table!?
Have you considered an order status dimension? 1-1 relationships between a fact and dimension are not liked because they do not perform well.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Order dimension vs. order degenerate dimensions in the fact table!?
I did see that as a suggestion in some of the older posts.
I guess I was just thinking that in that case I would have a dimension with one or say at most 2-3 attributes and just a few rows in it (depending on how many different status values there are) and that was confusing me. Is that a common scenario? I always thought of dimensions as very wide tables.
In any case, thanks for your reply!
I guess I was just thinking that in that case I would have a dimension with one or say at most 2-3 attributes and just a few rows in it (depending on how many different status values there are) and that was confusing me. Is that a common scenario? I always thought of dimensions as very wide tables.
In any case, thanks for your reply!
dk2014- Posts : 15
Join date : 2014-11-10
Re: Order dimension vs. order degenerate dimensions in the fact table!?
Yes it is common and not all dimensions are wide. Order status seems like a common drill down dimension so the BI folks will like it better than trying to do distinct selects on a potentially huge dimension. It will also work much better than a 20M row table join to another 20M row table.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Order dimension vs. order degenerate dimensions in the fact table!?
It makes sense. Also, I just found another article that supports your opinion - http://dwbi1.wordpress.com/2010/03/11/a-dimension-with-only-one-attribute/
Thanks for the explanation!
Thanks for the explanation!
dk2014- Posts : 15
Join date : 2014-11-10
Similar topics
» Work Order / Customer Order Design - Dimension or Fact
» Need to merge fact tables
» Order Dimension and Order Fact
» Order Line Details and Order Status Dimension
» Combining master and history tables to fact
» Need to merge fact tables
» Order Dimension and Order Fact
» Order Line Details and Order Status Dimension
» Combining master and history tables to fact
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum