Data model for Sales Order and Sales

Hi All,

This is my first post in this forum. I am trying to do dimensional modeling for Sales Order process and Sales process. I have some questions

1. Do I need to look at Sales Order and Sales (Invoiced) as 2 different separate process and model them into 2 different fact tables.

2. For each Sales Order and Sales , there will be Sales Order Adjustments and Sales Adjustments, How do I capture these ? Do I need to maintain 4 different fact table one each for Sales Order, Sales Order Adjustments, Sales , and Sales Adjustments as the dates on which these process occur will vary. For example : Sales order occurs on one day and the adjustment to the sales order will happen some other day. Also multiple adjustments could happen for a given Sales Order on different dates or no adjustments can happen for a Sales Order.

Thanks
Thirumalai

Hi,

1. It depends on what your reporting requirements are: you may need to model them as separate fact tables and/or a single combined fact table. Given that joining across fact tables (on a record by record basis) is not that efficient, if you have reporting requirements that cover both Sales and Invoice data then having a single combined fact table may make sense - but that does not also stop you having individual tables if you also have reporting requirements that are more easily satisfied by having such tables

2. What's the grain of your fact table? Assuming it is at the Order Line level then including Adjustments as extra rows in the table is simple - you just need to make sure you can identify which rows are adjustments. You could use a row type dimension or, assuming you already use a Product Dim, you could just add "Adjustment" as a row in your Product Dim and then use that.

Hi Nick,

Thanks for your reply.

The grain of the fact table would be Order line level , i.e. all the items for particular Sales Order. We will be having an Item dimension.

For example : A Sales order was placed on day 1 for item X for a quantity of 10 items. On Day 2, there is a adjustment for the same Sales Order, where the quantity of item X is increased to another 5.

I am not sure, if for the same Sales Order, there is an new order adjustment for item Y for 10 units. How would you model this ?

If I am incorporating Adjustment as an dimension in the Item dimension, then How would I track which items got adjusted ?

Thanks
Thirumalai

In general, invoicing and sales are two separate processes and hence two separate facts. The invoice would contain a references back to the sales order via the sales order number and line degenerate dimensions.

Adjustments are usually in the same fact table. Ideally you want to design the fact so it is transactional (net change) in nature so an adjustment is simply an insert of a new row with the difference. This is normal for invoice adjustments, but may need some work for sales order adjustments (where systems typically do an update in place). For sales order facts you could consider an accumulating snapshot if you want change history, update in place if you don't, or calculate deltas and store the data transactionally.

Hi,

If I am using an accumulating snapshot table for Sales Order and Sales Order Adjustments , How would I track all the adjustments for a Sales Order, Is that possible using an accumulating snapshot ?

Thanks
Thirumalai

No it's not possible. You need to use a transaction fact table.

Ok, Thanks all for your inputs.

Hi,

I am planning to having a transaction fact table, tracking all the orders and its corresponding adjustments. Can this transaction fact be accompanied with a periodic snapshot table ? If yes , what could be the data stored that will be useful for the analysis ?

I am thinking that it could be the Sales order fact at the item level with all the adjustments adjusted in a single row. Any thoughts ?

For example :

Sales Order for Item X on Day 1= 10 units
Sales Order Adjustment for Item X on Day 2= -5 units

So, I would store the summarized Sales Order = 10-5= 5 units on this periodic snapshot table ?

What would be the level of data ? I cannot store it at the day level, since the Order and Adjustment days are different. So it could be weekly or monthly basis ?

Thanks
Thirumalai

While you could, the question is, do you need to?

If you have transactional data, you can easily produce a snapshot at any point in time by simply restricting the query based on an 'as of' date (the timestamp of the transaction). A snapshot would be an aggregate of such data, and the question you raise outlines the crux of the problem in doing so.

You resort to aggregates when there is a pressing need to improve query response time. It is questionable wither a snapshot would help. For one, the snapshot will be considerably larger than the transactional facts. Also, the filtering required could be significantly more complex to locate the version of the order desired. Add to that the time it takes to build and maintain such a snapshot on a daily basis.