Implementing sales adjustments in DW,

View previous topic View next topic Go down

Implementing sales adjustments in DW,

Post  dk2014 on Thu Feb 12, 2015 11:39 am

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.

dk2014

Posts : 15
Join date : 2014-11-10

View user profile

Back to top Go down

Re: Implementing sales adjustments in DW,

Post  nick_white on Fri Feb 13, 2015 12:12 pm

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.


nick_white

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

View user profile

Back to top Go down

Re: Implementing sales adjustments in DW,

Post  dk2014 on Fri Feb 13, 2015 4:59 pm

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!!!

dk2014

Posts : 15
Join date : 2014-11-10

View user profile

Back to top Go down

Re: Implementing sales adjustments in DW,

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum