Order dimension vs. order degenerate dimensions in the fact table!?

View previous topic View next topic Go down

Order dimension vs. order degenerate dimensions in the fact table!?

Post  dk2014 on Mon Nov 10, 2014 4:35 pm

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!

dk2014

Posts : 15
Join date : 2014-11-10

View user profile

Back to top Go down

Re: Order dimension vs. order degenerate dimensions in the fact table!?

Post  BoxesAndLines on Mon Nov 10, 2014 7:36 pm

Have you considered an order status dimension? 1-1 relationships between a fact and dimension are not liked because they do not perform well.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Order dimension vs. order degenerate dimensions in the fact table!?

Post  dk2014 on Mon Nov 10, 2014 9:20 pm

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!

dk2014

Posts : 15
Join date : 2014-11-10

View user profile

Back to top Go down

Re: Order dimension vs. order degenerate dimensions in the fact table!?

Post  BoxesAndLines on Mon Nov 10, 2014 10:25 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Order dimension vs. order degenerate dimensions in the fact table!?

Post  dk2014 on Mon Nov 10, 2014 11:00 pm

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!

dk2014

Posts : 15
Join date : 2014-11-10

View user profile

Back to top Go down

Re: Order dimension vs. order degenerate dimensions in the fact table!?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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