How to model fact with optional parent fact?

View previous topic View next topic Go down

How to model fact with optional parent fact?

Post  gxclarke on Wed Dec 28, 2011 6:42 pm

Business statement:

  • Within a school district, incidents occur. An incident has an associated incident type, such as bus broke down or fight broke out. Each incident is assigned a number that is unique to each school district.
  • Within a school, student infractions also occur. An infraction has an associated infraction reason, such as argued with teacher or involved in fight.

This leads to the following dimensional model:

  • DimIncidentType
  • FactIncident
  • DimInfractionReason
  • FactStudentInfraction

However, a student infraction may be associated with an incident. This relationship needs to be captured to answer questions such as how many incidents occurred for each infraction reason.

In this case, the incident also behaves like a dimension. Im not sure how to model this dimensionally. If I create both a dimension and a fact table for incident information, I end up with the following:

  • DimIncident (IncidentKey, SchoolDistrictKey, IncidentNumber, IncidentTypeKey, IncidentDateKey)
  • FactIncident (IncidentKey)
  • FactStudentInfraction (StudentKey, SchoolKey, InfractionTypeKey, InfractionDateKey, InfractionReasonKey)

FactIncident contains one less row than DimIncident because there is also a N/A row in the dimension for student infraction facts that are not associated with an incident.

Is this the right approach? The presence of FactIncident with only the single primary key seems odd.

gxclarke

Posts : 2
Join date : 2011-10-08

View user profile

Back to top Go down

Re: How to model fact with optional parent fact?

Post  ngalemmo on Wed Dec 28, 2011 7:24 pm

You would have the following dimensions:
Incident Type
School
School District
Date
Infraction type
Infraction reason
Student
Incident number (degenerate)

The incident fact table would have the following dimension: incident number, incident type, incident date, school district, and others as necessary.

The student infraction fact table would have these dimensions: student, school, infraction date, infraction type, infraction reason, incident number (set to zero if not part of a reported incident), and others as applicable.

You could then combine facts if necessary, or you may include incident related dimensions to the student infraction fact table (i.e. incident type) to avoid the need to combine facts.
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