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

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

Back to top

- Similar topics

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