Order or Order Status Dimension

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.

I think you just need an Order Status dimension table.

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.

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?

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.

ngalemmo wrote: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.

Interesting approach. I am wondering if you're thinking a design like this:

Order#	Activity Date	Activity Type	Status	Order Amount
101	01/15/2012	New Order	Open	$100
101	02/24/2012	Shipping	Open	-$100
101	02/24/2012	Shipping	Shipped	$100
101	03/17/2012	Price Adjustment	Shipped	-$15

This seems to work well for Total Order amount by status by end of month
You would get something like this with the proper SQL:

Select EOM.Date, Status, SUM(Order Amount) TotalAmt
FROM Order
JOIN EndOfMonth EOM on Activity Date <= EOM.Date
GROUP BY EOM.Date, Status

EOM.Date	Status	TotalAmt
01/31/2012	Open	$100
02/29/2012	Open	$0
02/29/2012	Shipped	$100
03/31/2012	Open	$0
03/31/2012	Shipped	$85

But wouldn't a design like this work better:

Order#	Effective Start Date	Effective End Date	Status	Order Amount
101	01/15/2012	02/23/2012	Open	$100
101	02/24/2012	03/16/2012	Shipped	$100
101	03/17/2012	12/31/2099	Shipped	$85

So instead of inserting two records in the fact when a dimension changes (Status) in this example, you insert only one record. So you end up with less records in the fact table. Also, when querying it, same records are processed only one time, unlike in the other solution where the record with the activity date of 01/15/2012 is processed 3 times, once for every EOM.
The SQL would look like this:

Select EOM.Date, Status, SUM(Order Amount) TotalAmt
FROM Order
JOIN EndOfMonth EOM on EOM.Date between Effective Start Date and Effective End Date
GROUP BY EOM.Date, Status

and the result like this:

EOM.Date	Status	TotalAmt
01/31/2012	Open	$100
02/29/2012	Shipped	$100
03/31/2012	Shipped	$85

You're confusing two different fact tables. The solution ngalemmo suggested is for the transactional fact table. Here, the Order Amount column would need to be additive across all the dimensions. So, if you also have a dimension for the salesman that took the order, or any other attribute of the order, you can sum up the Amount by that dimension and reach the correct total at any point in time.

Order#	Activity Date	Activity Type	Status	Salesman	Order Amount
101	01/15/2012	New Order	Open	J Bloggs	$100
101	02/24/2012	Shipping	Open	J Bloggs	-$100
101	02/24/2012	Shipping	Shipped	J Bloggs	$100
101	03/17/2012	Price Adjustment	Shipped	J Bloggs	-$15

In a periodic snapshot, however, such as an end of month summary, the amount isn't additive across different periods, so your row would have all the attribute values and the total order amount as at the end of the time period.

Dave Jermy wrote:You're confusing two different fact tables. The solution ngalemmo suggested is for the transactional fact table. Here, the Order Amount column would need to be additive across all the dimensions. So, if you also have a dimension for the salesman that took the order, or any other attribute of the order, you can sum up the Amount by that dimension and reach the correct total at any point in time.

Order#	Activity Date	Activity Type	Status	Salesman	Order Amount
101	01/15/2012	New Order	Open	J Bloggs	$100
101	02/24/2012	Shipping	Open	J Bloggs	-$100
101	02/24/2012	Shipping	Shipped	J Bloggs	$100
101	03/17/2012	Price Adjustment	Shipped	J Bloggs	-$15

In a periodic snapshot, however, such as an end of month summary, the amount isn't additive across different periods, so your row would have all the attribute values and the total order amount as at the end of the time period.

I think you are confusing what I am confusing. I know that ngalemmo suggested a transactional fact table, and what I suggested is an accumulating snapshot (not periodic). In the end both solutions can accurately reproduce history at any point in time by any dimension, because at any point in time there is only one record per grain effective in the accumulating snapshot fact table. I queried the both tables as of the end of month as an example, but I could have picked any other point in time. Anytime ngalemmo adds 2 records to capture a dimension change, the accumulating snapshot would end date the current record and add one new record to capture the new dimension value.
Do you see any business question that can be answered by the transactional solution and cannot be answered by the accumulating snapshot solution? (other columns could be added to the accumulating snapshot solution, e.g. activity type, Order Date, Shipping Date, Billing Date etc).
One environment that I can think-off where the offsetting transactional table may work better than the accumulating snapshot table is where updates to the table are not feasible.

If all you are interested in is the correct values at a point in time then either solution could suffice, but the accumulating snapshot would make it very difficult to find out answers to question like 'What's the value of orders shipped this week?', which you could do simply with the transactional fact.

Dave Jermy wrote:If all you are interested in is the correct values at a point in time then either solution could suffice, but the accumulating snapshot would make it very difficult to find out answers to question like 'What's the value of orders shipped this week?', which you could do simply with the transactional fact.

Not really. By also having the shipping date in the accumulating snapshot, it would be a matter of:

"where shipping date = last week and last day of the last week between effective start date and effective end date". This will give you all the orders that shipped anytime last week and how they looked like (dimensional and measure wise) at the end of the week.

"where shipping date = last week and shipping date between effective start date and effective end date". This will give you all the orders that shipped anytime last week and how they looked like (dimensional and measure wise) at the shipping time.