Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]

View previous topic View next topic Go down

Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]

Post  riafan on Mon Nov 16, 2009 11:21 am

Hello,
I'm new to DM, I've picked up Kimball's DW Toolkit and DW ETL Toolkit - both great books.

I'm designing a simple datawarehouse for eduction (K-12) and ran into a modeling question. I'm designing the portion pertaining to [homework] assigments. A teacher creates an assignment and scores each student on the assignment. So my thought was simple, an Assignment Dimension and an Assignment Score Fact.

Here's where I'm hung up... The assignment has references to other dimensions, namely
Teacher
Course
Subject Area
School
Term
AssignedDate
DueDate

What's the best approach? Should Dimensions have FKs to other Dimensions?

Thanks for any help

riafan

Posts : 3
Join date : 2009-11-16

View user profile

Back to top Go down

Re: Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]

Post  ngalemmo on Mon Nov 16, 2009 11:55 am

The assignment fact would have FKs to all pertinent dimensions: Assignment, Teacher, Course, etc...
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]

Post  riafan on Mon Nov 16, 2009 12:01 pm

ngalemmo wrote:The assignment fact would have FKs to all pertinent dimensions: Assignment, Teacher, Course, etc...

Okay, so you'd suggest...

AssigmentDimension
AssignmentKey
AssignmentName
AssignmentType
Addt'l Attribtues (No FKs)

AssignmentFact (Factless fact table)
AssignmentKey [FK to Dimension Table]
TeacherKey [FK]
CourseKey [FK]
Addt'l Dim FKs...

AssignmentScoreFact
AssignmentKey [FK to Dimension Table]
Student [FK]
CourseKey [FK]
Addt'l Dim FKs...
Percentage Score

So I'd have a 1:1 with the AssignmentDimension => AssignmentFact, correct?

Thanks!!!
Josh

riafan

Posts : 3
Join date : 2009-11-16

View user profile

Back to top Go down

Re: Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]

Post  ngalemmo on Mon Nov 16, 2009 2:21 pm

It may be a 1:1 relationship, but what are the attributes of an assignment? If different teachers at different schools give the same assignment, would you not want to track it as such to draw comparatives? Something to think about...
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]

Post  riafan on Mon Nov 16, 2009 3:02 pm

Thanks for the reply!!
[Very] good point. The operational system that the assignments are recorded with doesn't provide associtating assignments across teachers or schools or terms, but you're spot on if it did.

Thanks again
Josh

riafan

Posts : 3
Join date : 2009-11-16

View user profile

Back to top Go down

Re: Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]

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