extending the education model as shown in Chapter 12 of DWH Toolkit - reposted to correct topic

Hi Kimball group!!
In Chapter 12 of The Data Warehouse Toolkit 2nd edition the example models are built for reporting student related data at a single institution.. For our WH, we have multiple institutions with multiple campuses containing multiple facilities or sites where a student can physically attend a course.

But most of our reporting needs are grouped by institution and term
So our facts would potentially look like…

Term_key
Institution_key
Student_key

Etc.



How should I model the existence of multiple campus locations for a given institution? Separate campus/site dimension or should I try to model the relationships as some sort of variable depth hierarchy within an institution dimension?

Here’s some sample data that shows the campus locations for a single institution..
NAME main campus city STVCAMP_DESC STVCAMP_CITY
Athens Technical College ATHENS Greene County Campus Greensboro
Athens Technical College ATHENS Main Campus Athens
Athens Technical College ATHENS Elbert County Campus Elberton
Athens Technical College ATHENS Walton County Campus Monroe


There is additional complexity because of having to maintain historical merger information.. Maybe it just Monday afternoon brain fog.. but I could use some advice?

I would have two dimensions, institution and site/location. You may have a FK in the site/location dimension to reference the institution, but I would not expose that as a snowflake, only as a aid for ETL and for dimension only reporting.

On site level fact tables, I would carry foreign keys to both the institution and site, while institution level fact tables would only carry an institution FK.