Data model for Sales Order and Sales

View previous topic View next topic Go down

Data model for Sales Order and Sales

Post  pthirum on Sun Nov 30, 2014 12:38 pm

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


pthirum

Posts : 8
Join date : 2014-11-30

View user profile

Back to top Go down

Re: Data model for Sales Order and Sales

Post  nick_white on Mon Dec 01, 2014 7:41 am

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.

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Data model for Sales Order and Sales

Post  pthirum on Mon Dec 01, 2014 9:31 am

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

pthirum

Posts : 8
Join date : 2014-11-30

View user profile

Back to top Go down

Re: Data model for Sales Order and Sales

Post  ngalemmo on Mon Dec 01, 2014 3:08 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Data model for Sales Order and Sales

Post  pthirum on Tue Dec 02, 2014 3:47 am

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

pthirum

Posts : 8
Join date : 2014-11-30

View user profile

Back to top Go down

Re: Data model for Sales Order and Sales

Post  BoxesAndLines on Tue Dec 02, 2014 11:07 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Data model for Sales Order and Sales

Post  pthirum on Tue Dec 02, 2014 12:43 pm

Ok, Thanks all for your inputs.

pthirum

Posts : 8
Join date : 2014-11-30

View user profile

Back to top Go down

Re: Data model for Sales Order and Sales

Post  pthirum on Tue Dec 02, 2014 12:59 pm

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

pthirum

Posts : 8
Join date : 2014-11-30

View user profile

Back to top Go down

Re: Data model for Sales Order and Sales

Post  ngalemmo on Tue Dec 02, 2014 2:17 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Data model for Sales Order and Sales

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