Budget On Different Granularity In Organization Hierarchy (which Is SCD)

Hi everyone

I am struggling with adding budget numbers to a cube. The main reason being that the budget is *not* on the finest granularity (employee) with regard to the organization hierarchy but on a coarser one (team).


The organization hierarchy is a "flat" (not parent-child) hierarchy that looks about like that:

employee -> team -> teamgroup -> region -> country


As mentioned I now have budget numbers that are defined on the team-level (not on the employee level as "regular" measures). I would know assume that I could put the budget data into its own table and "link" it with the organization through the "team" attribute. I would do that on the "dimesion usage"-tab.

The problem with this approach is that the organization is changing (SCD type 2). This essentially means that by linking to the "team" attribute the aggregation of the budget data on higher levels of the organization hierarchy can be ambiguous (at least that is what I understand).

Example organization table:



Code Snippet
surrKey busKey empName teamId teamGroupId regionId countryId ... scdStuff
1 1 Raphael 1 1 1 1 ...
2 2 Jeanne 2 2 1 1 ...
3 3 George 3 3 1 1 ...
4 2 Jeanne 2 3 1 1 ...



That would mean that on some point in time team 2 (consisting of one employee, Jeanne) moved from teamgroup 2 to teamgroup 3. Just for the sake of a simple example.

Now, what am I to do with my budget data in this situation? I cannot link it to the teamId, because teamId = 2 for example cannot specify if the value should be aggregated into teamgroup 2 or teamgroup3...


I have a feeling that this got something to do with the design of the organization-table but I am unsure about what the actual problem is. Any hint, pointer or solution would be appreciated. If the question is unclear, please let me know and I will try to clarify.


Kind regards
scherand