Question on Dimension schema change

Currently I have A dimension that captures Division Information and Performance Goals for each division as shown below:

Divisionkey
DivisionId
DivisionGoalId
DivisionName
DivisionGoalYear
DivisionGoalAccidentRate
DivisionGoalInjuryRate

In the source database Division and DivisionGoal Are separate tables where DivisionId is a foreign key into the divisiongoal table.

The Division Goal has a unique constraint on DivisionId and Year. So each division has two rates which may change each year.

I was considering moving DivisionGoal into its own dimension but wasnt sure how I would handle not having the DivisionId in the table. Would the new table now have DivisionGoalId and Year as the Unique constraint instead of DivisionId and Year ?

My only problem is I want an SCD so I do not think that would work.

Thanks,

The two most common solutions would be:

(1.) Leave them in the Division dimension as SCD2 attribute fields. Note this will require a new dimension record with a new DivisionKey each year for each division. This would work well if you want to compare each year's accidents with the goals for that year.

(2.) Move them into a DivisionGoals fact table. The goals become measures, which seems appropriate as they are numeric. The fact table would point to the Division dimension and a GoalYear dimension. It would be a snapshot fact table with a new snapshot each year. To compare actual accidents with goals you would "drill across" using a conformed Year attribute (found in your GoalYear dimension as well as your standard Date dimension.)

Note that if you choose (2.) you could also put SCD1 fields for CurrentGoalYear, CurrentGoalAccidentRate, etc. into the Division dimension if it would faciliate reporting.

As VHF points out, goals (like budgets) are best represented as fact tables.

Thanks for the good info.

I was leaning toward option 1 but now I will have to think twice about option 2. I thought the fact table might not be useful since all the measures were not additive. They are decimal values representing rates but maybe thats ok in this case.

In a periodic snapshot fact table the measures usually are not additive over time.