Data model for Sales Order and Sales
4 posters
Page 1 of 1
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
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 : 20141130
Re: Data model for Sales Order and Sales
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.
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 : 364
Join date : 20140106
Location : London
Re: Data model for Sales Order and Sales
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
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 : 20141130
Re: Data model for Sales Order and Sales
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.
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.
Re: Data model for Sales Order and Sales
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
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 : 20141130
Re: Data model for Sales Order and Sales
No it's not possible. You need to use a transaction fact table.
BoxesAndLines Posts : 1212
Join date : 20090203
Location : USA
Re: Data model for Sales Order and Sales
Ok, Thanks all for your inputs.
pthirum Posts : 8
Join date : 20141130
Re: Data model for Sales Order and Sales
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 = 105= 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
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 = 105= 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 : 20141130
Re: Data model for Sales Order and Sales
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.
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.
Similar topics
» Modeling a Sales Order to Billing to Shipping consolidated data model
» Is it possible to get a distinct order count with a transaction line sales fact table?
» How best to model Timesheet facts against Sales Order facts
» How to model comparible store sales for retail data
» Accumulating Snapshot Fact Table Data Model (Order Management)
» Is it possible to get a distinct order count with a transaction line sales fact table?
» How best to model Timesheet facts against Sales Order facts
» How to model comparible store sales for retail data
» Accumulating Snapshot Fact Table Data Model (Order Management)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum

