Modeling goals which may slowly change over time

[aklebenow]

I did a quick search on goal modeling and didn't seem to find an existing scenario that matched the one I am posting about. I have a DW modeled against data from a time-charging application within an IT department (ie everyone must record time spent on teach project assigned to them). This data is fed into an SSAS cube that allows members of management to query the data. The DW focuses on three types of projects that management would like to track time charged to. For one of those types, management had initially established a goal: time charged by the department should at least be X% of this project type. KPI's and other indicators were built against that goal percent in the cube and not included in the DW.

Now that goal percentage has recently been changed by management and now we would like to incorporate the goal into the DW (it should not change that often). Time charged before the goal percent change should still be measured against the original goal. Time charged after the goal change should be against the new percent. Management likes to see trends of how the department was doing against the goal over time.

Quick model overview:
DimProject - lists all projects
DimUser - lists all users within the application
DimHoursWorkedDate - standard time dimension
FactWorkedHours - grain is user + project + date worked + hours worked

The team had initally thought of three ways to dimensionally model the goal (and be able to easily bring it into the cube). Looking for your insight on which might be the best or if you have any other suggestions.

1. Create a new field within DimHoursWorkedDate: each date record would have a goal associated with it with the new goal starting on the first date it was implemented.

2. Create a new field within FactWorkedHours: each worked hours record would have a goal associated with it based on the date the hours were worked.

3. Model the goal as a new dimension somehow? With Goal 1 being X and then Goal 2 being the new value?

Thanks so much for your help!

[hang]

I think the goal should go with DimProject which should be a slowly changing dimension (SCD) to cater for project specific goals as well. I imagine you would have some other attributes related to the project plan and budget, such as planed start date and complete date which could also be SCD attributes if they are subject to change and you need to keep track of the changes.

Once your dimensional model covers the goal values, you can remove your hardcoded goal from the KPI and build the data driven goals into your MDX.