Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

2 posters

Go down

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

Post  riafan 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

Back to top Go down

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

Post  ngalemmo Mon Nov 16, 2009 11:55 am

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

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

http://aginity.com

Back to top Go down

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

Post  riafan 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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  riafan 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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