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

View previous topic View next topic Go down

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

Post  developerpete on Mon Nov 05, 2012 6:53 pm

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?

developerpete

Posts : 5
Join date : 2012-11-05

View user profile

Back to top Go down

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

Post  ngalemmo on Mon Nov 05, 2012 7:48 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

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