Sales Forecast Movements
2 posters
Page 1 of 1
Sales Forecast Movements
First time forum poster, I've read The Data Warehouse Toolkit (very informative) but I'm struggling to conceptualise one of my companies analytic requirements.
We have a sales forecast report that lists which orders are forecasted to drop in a particular time frame. One of the requirements for the data warehouse is to store this information on a month by month/quarter by quarter basis and highlight the movements that occur. For example, in January, Order A was forecasted to drop in June but then in Febuary a sales person changes the expected drop date to July. A user, running the report in Febuary, must be able to see that a movement has occured for that Order.
This is how I was thinking of doing it...
I have a FactOrders table with the columns FactID, OrderID, DropDateID, OrderValue and it would look like this for Order A:
FactID,OrderID,DropDateID,OrderValue
1, OrderA, 2010-06-01, 1000.00
2, OrderA, 2010-07-01, 1000.00
Doing it this way would obviously mess up my monthly totals for June so I was thinking of adding a third row to negate the original, like this:
FactID,OrderID,DropDateID,OrderValue
1, OrderA, 2010-06-01, 1000.00
2, OrderA, 2010-06-01, -1000.00
3, OrderA, 2010-07-01, 1000.00
As I am new at this I am concerned that I am over complicating the whole thing so any input would be much appreciated!
Many thanks,
Kevin
We have a sales forecast report that lists which orders are forecasted to drop in a particular time frame. One of the requirements for the data warehouse is to store this information on a month by month/quarter by quarter basis and highlight the movements that occur. For example, in January, Order A was forecasted to drop in June but then in Febuary a sales person changes the expected drop date to July. A user, running the report in Febuary, must be able to see that a movement has occured for that Order.
This is how I was thinking of doing it...
I have a FactOrders table with the columns FactID, OrderID, DropDateID, OrderValue and it would look like this for Order A:
FactID,OrderID,DropDateID,OrderValue
1, OrderA, 2010-06-01, 1000.00
2, OrderA, 2010-07-01, 1000.00
Doing it this way would obviously mess up my monthly totals for June so I was thinking of adding a third row to negate the original, like this:
FactID,OrderID,DropDateID,OrderValue
1, OrderA, 2010-06-01, 1000.00
2, OrderA, 2010-06-01, -1000.00
3, OrderA, 2010-07-01, 1000.00
As I am new at this I am concerned that I am over complicating the whole thing so any input would be much appreciated!
Many thanks,
Kevin
kyllonnoc- Posts : 1
Join date : 2010-02-02
Re: Sales Forecast Movements
You could do a snapshot fact. Much like checking account balances, the order value would not be additive across snapshots. This approach would depend on the transaction volume.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
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
» Sales facts vs sales goals & calls
» Trying to design Sales to Promos to Sales Relationship help Please.
» Modeling Invoice Level Sales With a Volatile Sales Org
» Sales Rep <--> Customer relationship with Sales Fact Table
» Sales facts vs sales goals & calls
» Trying to design Sales to Promos to Sales Relationship help Please.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum