Order or Order Status Dimension

View previous topic View next topic Go down

Order or Order Status Dimension

Post  cjrinpdx on Mon Aug 22, 2011 5:16 pm

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.

cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: Order or Order Status Dimension

Post  thomaszhwang on Mon Aug 22, 2011 5:21 pm

I think you just need an Order Status dimension table.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: Order or Order Status Dimension

Post  ngalemmo on Mon Aug 22, 2011 5:45 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Order or Order Status Dimension

Post  cjrinpdx on Tue Aug 23, 2011 2:56 pm

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?

cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: Order or Order Status Dimension

Post  ngalemmo on Tue Aug 23, 2011 3:44 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Order or Order Status Dimension

Post  adypoko on Wed Oct 24, 2012 1:23 pm

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

adypoko

Posts : 5
Join date : 2012-10-24

View user profile

Back to top Go down

Re: Order or Order Status Dimension

Post  Dave Jermy on Thu Oct 25, 2012 8:30 am

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 DateActivity TypeStatusSalesmanOrder Amount
10101/15/2012New OrderOpenJ Bloggs$100
10102/24/2012ShippingOpenJ Bloggs-$100
10102/24/2012ShippingShippedJ Bloggs$100
10103/17/2012Price AdjustmentShippedJ 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

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Order or Order Status Dimension

Post  adypoko on Thu Oct 25, 2012 10:01 am

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 DateActivity TypeStatusSalesmanOrder Amount
10101/15/2012New OrderOpenJ Bloggs$100
10102/24/2012ShippingOpenJ Bloggs-$100
10102/24/2012ShippingShippedJ Bloggs$100
10103/17/2012Price AdjustmentShippedJ 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.

adypoko

Posts : 5
Join date : 2012-10-24

View user profile

Back to top Go down

Re: Order or Order Status Dimension

Post  Dave Jermy on Fri Oct 26, 2012 4:43 am

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

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Order or Order Status Dimension

Post  adypoko on Fri Oct 26, 2012 10:52 am

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.

adypoko

Posts : 5
Join date : 2012-10-24

View user profile

Back to top Go down

Re: Order or Order Status Dimension

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