Question on Dimension schema change

View previous topic View next topic Go down

Question on Dimension schema change

Post  mru22 on Tue Jul 05, 2011 11:10 am

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,

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Question on Dimension schema change

Post  VHF on Tue Jul 05, 2011 12:44 pm

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.


VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Question on Dimension schema change

Post  ngalemmo on Tue Jul 05, 2011 3:46 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Question on Dimension schema change

Post  mru22 on Tue Jul 05, 2011 5:47 pm

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.

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Question on Dimension schema change

Post  VHF on Wed Jul 06, 2011 9:22 am

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

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Question on Dimension schema change

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum