Implementing sales adjustments in DW,
2 posters
Page 1 of 1
Implementing sales adjustments in DW,
We have a need to include sales adjustments in our data warehouse. For example, our sales reps have to make certain sales quotas every month and for one reason or another we may include adjustments so that some of their sales count for the previous month - say one of our sales reps made a sale on the 2nd of Feb but we decide to include this sale in his/her Jan sales so that he/she meets the quota. In this case, in our source system we'll add the sales amount in our adjustment field for January. These adjustments are assigned on a monthly basis but there is no rule when they'll be added - they could be added on the 1st of the month, on the 15th or on the 27th.
We already have a transactional fact table that is designed at the order line/item level and I don't see how I could include adjustment amounts in this fact as they are at completely different level. I was thinking about creating a new fact table, a periodic snapshot fact table, that would include SalesRepKey and PeriodKey as a PK (for right now) and TotalOrderAmount and Adjustments facts (maybe some other metrics as well).
1. Would this be the right way to do this?
2. What do I do if say we load data warehouse twice a week and adjustment is posted in the source system on the 25th of the month? For the first 6 loads or so in the month the adjustment will be 0 but then it'll update to whatever the amount of adjustment is. Do I update the adjustment field in my fact table or do I insert a new record? Is there a scenario where updating the facts on a regular basis is permissible or even recommended? We will be running reports against this fact table on a daily basis.
We already have a transactional fact table that is designed at the order line/item level and I don't see how I could include adjustment amounts in this fact as they are at completely different level. I was thinking about creating a new fact table, a periodic snapshot fact table, that would include SalesRepKey and PeriodKey as a PK (for right now) and TotalOrderAmount and Adjustments facts (maybe some other metrics as well).
1. Would this be the right way to do this?
2. What do I do if say we load data warehouse twice a week and adjustment is posted in the source system on the 25th of the month? For the first 6 loads or so in the month the adjustment will be 0 but then it'll update to whatever the amount of adjustment is. Do I update the adjustment field in my fact table or do I insert a new record? Is there a scenario where updating the facts on a regular basis is permissible or even recommended? We will be running reports against this fact table on a daily basis.
dk2014- Posts : 15
Join date : 2014-11-10
Re: Implementing sales adjustments in DW,
I assume you would prefer to have all this data in one fact table so that when you report against it the reports take into account the adjustments - rather than having to query two fact tables and combine the results?
Given that, I would just create the adjustments as dummy order/line data in your fact table. Create Order(s)/Line(s) as necessary - hard code the date to either the first/last day of the month (or whatever makes sense for you); if your Order Lines have a Product then create a dummy product of "Adjustment", etc.
Given that, I would just create the adjustments as dummy order/line data in your fact table. Create Order(s)/Line(s) as necessary - hard code the date to either the first/last day of the month (or whatever makes sense for you); if your Order Lines have a Product then create a dummy product of "Adjustment", etc.
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Implementing sales adjustments in DW,
I've never thought about doing that but it seems that it may actually work! If it does that would solve some of the issues in our current system that I didn't even think could be fixed!
Thanks a lot nick_white!!!
Thanks a lot nick_white!!!
dk2014- Posts : 15
Join date : 2014-11-10
Similar topics
» Dimensional modelling for Sales and Marketing (opportunity, quotes, sales orders)
» Modeling Invoice Level Sales With a Volatile Sales Org
» Sales Rep <--> Customer relationship with Sales Fact Table
» Trying to design Sales to Promos to Sales Relationship help Please.
» Data model for Sales Order and Sales
» Modeling Invoice Level Sales With a Volatile Sales Org
» Sales Rep <--> Customer relationship with Sales Fact Table
» Trying to design Sales to Promos to Sales Relationship help Please.
» Data model for Sales Order and Sales
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum