DW design question

I'm in the very early stages of design for a DW where I have state standardized scores for a school.  The typical hierarchy is: A school reports to a district which reports to an ISD.  There are some exceptions where there is no district, but the the school directly reports to the ISD (skipping the district layer).  I think I can handle this by adding an ISD_Acting_as_District flag and adding the ISD info into the District dimension.

I have my FCT_Scores table.  My question is:

Do I create three separate dimensions (DIM_SCHOOL, DIM_DISTRICT & DIM_ISD) or do I combine these into one combined dimension?  I would want to be able to roll up results at the school, district or ISD level.

Any thoughts?

Thanks, Rob

You can do it either way.  Personally I would put them all in one dimension.  Using one dimension will create the more classic STAR rather than a SNOWFLAKE dimensional model.

I agree with Daniel here. You can bring them in as seperate tables in your DSV if you've already got them that way and bring them together in the dimension or you can bring them together in a view and then bring the view into your dsv.

These are all brand new tables, so I'll go with them combined.

Thanks!!