I am developing a sales dimensional model that must have these facts: Gross Sales, Gross Revenue and Quantity of Sold Coupons per Sales Executive, City, Coordinator and Manager.
Problem is that for each level of this hierarchy (Sales Executive, City, Coordinator and Manager) there is it's own goal, but they can't be summed.
Example:
Manager Goal: 1000
- Coordinator 1 Goal: 400
-- City 1 Goal: 150
--- SalesExecutive 1 Goal: 80
--- SalesExecutive 2 Goal: 50
-- City 2 Goal: 150
--- SalesExecutive 1 Goal: 100
--- SalesExecutive 2 Goal: 20
- Coordinator 2 Goal: 400
-- City 1 Goal: 350
--- SalesExecutive 1 Goal: 80
--- SalesExecutive 2 Goal: 200
1 ) That means that the sum of the goals of the child level is not equal to it's parent level, but the facts are the sum.
2) I need to calculate the Achievement of each level and for that I need to divide each level goal per it's Gross Revenue (Gross Revenue can be summed, so if two Sales Execute sums 500 of Gross Revenue, the City has 500 as Gross Revenue)
My problem is that I can't figure out where to put the Goal Value. I can't put that on the staff dimension and if I put into the Fact I can't create drill-downs.
By the way, Goal Values change each month, of course.
Could someone help me to find a way to design this data model? I really can't figure it out.
Thanks in advance to all.
