Order or Order Status Dimension

[cjrinpdx]

I have a fact table names FactOrder. The users would like to slice the data in the fact table by order status. I could create an order dimension containing order status, but I have over four million orders. This seem to be a bit much to get just the order status (the order has no other attributes at this time that the users are interested in). Should I just create an order status dimension that would contain about ten records? Thanks.

[thomaszhwang]

I think you just need an Order Status dimension table.

[ngalemmo]

It is very uncommon to have an Order dimension. However, before you build your order status dimension, I suggest you take a good look at what information you have about orders and make sure you are not missing something. Attributes about an order should be covered by existing dimensions (such as date, customer, etc...) or new dimensions. In most cases, a junk dimension is a good vehicle to capture miscellaneous attributes that don't have anywhere else to go.

Try to capture all attributes that make sense. They may want status today, but they will most likely want additional attributes later. It is a real pain to add new dimensions to a fact table, particularly if it needs to be retroactive.

[cjrinpdx]

Thank you for your posts. I agree, but for some reason I have the urge to create an order dimension with OrderKey, BKOrderId, and OrderStatus and have OrderStatus be a type 2 SCD. I guess it depends on the business need; do they want to see the history of the order’s status, or just know the order status at the time the order line item was created?

[ngalemmo]

Resist the urge.

The thing is, orders change all the time and just about anything on the order will change. Rather than just trying to capture attribute change history in type 2 dimensions, you are better off capturing any change using a transactional fact table. When anything changes, just add one or two new fact rows reflecting the net change to the line (one row if there are only changes to the measures, two rows (a negative and a positive) when there are changes to dimensions). It will allow you to recreate an image of the order at any point in time. There is a fairly straight foward process to generate the new rows.