0

My org has a need to build a data warehouse in SQL Server (and eventually a cube in SSAS) where one of the fact tables is for sale target / budget data.

Till date, I've only handled the opposite of this problem, whereby sum of children's values is less than sum of parent's value.

Here the question is how to model fact table where sum of children's value being n-times sum of parent's value.

The following table representation is the best way I can describe the problem.

Every employee has a sale target / budget amount, as given below:

EmployeeId | EmployeeBudgetAmount$
-----------+----------------------
Emp1       |   100
Emp2       |   200
Emp3       |   500

The above budget value at employee level is split into the practices (an organizational term equivalent to legal practices, such as immigration, corporate, etc.) as given below:

EmployeeId | PracticeTypeId | PracticeId | EmployeePracticeBudgetAmount$
Emp1 | PracType1 | Prac1 | 20
Emp1 | PracType1 | Prac2 | 80
Emp1 | PracType2 | Prac10 | 40
Emp1 | PracType2 | Prac20 | 60
Emp1 | PracType3 | Prac100 | 30
Emp1 | PracType3 | Prac200 | 70

Emp2 | PracType1 | Prac1 | 160
Emp2 | PracType1 | Prac2 | 40
Emp2 | PracType2 | Prac10 | 80
Emp2 | PracType2 | Prac20 | 120
Emp2 | PracType3 | Prac100 | 140
Emp2 | PracType3 | Prac200 | 60

Emp3 | PracType1 | Prac1 | 0
Emp3 | PracType1 | Prac2 | 500
Emp3 | PracType2 | Prac10 | 100
Emp3 | PracType2 | Prac20 | 400
Emp3 | PracType3 | Prac100 | 200
Emp3 | PracType3 | Prac200 | 300

An employee's total budget is met across every practice type's budget for that employee.

Emp1's total budget = SUM(Emp1's practice budget) for PracType1
Emp1's total budget = SUM(Emp1's practice budget) for PracType2
Emp1's total budget = SUM(Emp1's practice budget) for PracType3

...and so on.

If the practice budget table (2nd table above) is used for the fact table, then aggregating all EmployeePracticeBudgetAmount for an employee results in a budget amount that's 3 (number of practice types available) times the budget of an employee.

Any pointers / ideas on how to model this as a fact table?

Thanks in advance.

NOTE: There are other dimensions such as fiscal month and fiscal year, above the level of employee, across which the budget amount would need to be summarized. I've not presented those in the above question for brevity reason.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dakaujunk
  • 13
  • 3
  • 1
    If your `EmployeeBudgetAmount$` value is really a budget amount *per practice type*, then you are right where you need to be. Otherwise, that is not the case. Then you just need to change how you spread that budget value over all the records in the second table. How you do that is really up to you. Am I missing something? – R. Richards Mar 19 '17 at 22:20
  • @R.Richards, thanks for your response. EmployeeBudgetAmount$ is the budget amount per employee and also the budget amount per practice type. But as i stated in the question, if i use the 2nd table that I've got with the EmployeePracticeBudgetAmount$ as the fact's table, then performing a SUM at the employee level shows budget more than what the budget for the employee is. For e.g., Emp1's budget according to the 2nd table would be 300, not 100 (100 is the correct amount). EDIT: Also, both 1st and 2nd table are actual OLTP tables, nothing to do with the data warehouse itself. – dakaujunk Mar 19 '17 at 22:23
  • 1
    The `EmployeeBudgetAmount$` is the budget amount per employee *and also* the budget amount per practice type? How can it be both? Right now, it is the later. If you want to get the same number as what you list in your first table, group by both the `EmployeeId` and `PracticeTypeId` from the second table. Emp1 will have 100 *per practice type*. – R. Richards Mar 19 '17 at 23:07
  • What exactly is your end goal? To check that each employee's budget per practice is less than or equal to their allowed budget? To prevent people from entering things over their budget? Something else? – ZLK Mar 20 '17 at 03:23

1 Answers1

0

As you're putting the employee's budget in three times for each of the three practice types, you will (as you say) get 3X the total if you group by/sum just by employee.

If a fact table was created like the second table, the budget would be 'semi additive' in that it doesn't make sense to sum across practice types.

You could handle it by educating the users (ensuring people know about this when constructing their queries/reports). For SSAS, Enterprise Edition comes with the ability to define semi additive measures and you can work around this for Standard Edition with more work (and googling).

If you wanted to definitely have employee budgets that sum up without this complication, you could have a separate fact table for this, but this sounds optional.

So this would be one fact table for employee budgets, one for practice budgets, and one dimension for employees and one for practices (incorporating the practice type).

Rich
  • 2,207
  • 1
  • 23
  • 27