Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
2 posters
Page 1 of 1
Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
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
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
Re: Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
The assignment fact would have FKs to all pertinent dimensions: Assignment, Teacher, Course, etc...
Re: Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
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
Re: Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
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...
Re: Schema Roadblock: Dimension vs Fact [Dimensions w/FKs to other Dimensions?]
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
[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
Similar topics
» Fact 1:N Dimension with Star Schema
» Can a combination of a Fact and Dimensions constitute a inferred conformed dimension?
» Composite Dimension Natural Keys - Attribute Used In Multiple Dimensions as well as Fact Tables
» Order dimension vs. order degenerate dimensions in the fact table!?
» How to create a schema with unrelated client dimensions
» Can a combination of a Fact and Dimensions constitute a inferred conformed dimension?
» Composite Dimension Natural Keys - Attribute Used In Multiple Dimensions as well as Fact Tables
» Order dimension vs. order degenerate dimensions in the fact table!?
» How to create a schema with unrelated client dimensions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum