0

I am designing a data model for reporting against People who take courses. Im trying to figure out if i should use one or two fact tables.

Measures I will need to calculate are:

-Total number of students

-Number of nationalities (and filter by nationality)

-Number of students who passed a course

Note that just because a student is on a course, does not mean they have sat exams so they may not yet have a course result.

This gives me two sets of fact data at different granularities.

  1. granularity of all students on a course.

  2. granularity of students that have completed a course and passed and failed.

My options as i see them are:

  1. Two seperate fact tables at the different granularities that share a few dimensions.

  2. One fact table, but for any students that that have not passed/failed, they will be linked to an entry in the DimResult that has a description of "No Result Yet".

Is there a set of guidelines for use when mixing granularities or is it a case that both answers will work and its up to me to decide?

Schema showing plan with both fact tables is shown below.

Thanks for any advise

enter image description here

JD_Sudz
  • 194
  • 1
  • 12

1 Answers1

0

I can't see any advantage in having two separate tables for enrolment and result, at least in the way as shown in your diagram.

  • It will be difficult to make sure that a student only has results for courses in which she is also enrolled.

  • Queries relating enrolement and result data will always have to evaluate both tables on the same compound key.

If a student has at most one result for each enrolled course, you can add result and award date to the enrolment entity, with these two possibly being null.

If you have use cases in which only parts of the facts are relevant, you can easily simulate the two table scenario using two database views. In contrast to two tables, two views won't present a consistency problem

If a student may have more than one result, possibly by repeating a failed exam, the result entity should be 1:n-linked to the enrolment entity.

TAM
  • 1,731
  • 13
  • 18
  • i was thinking of if this is made available to business users, it will be easier for them to play with the data in a pivot table if each fact table has only one granularity. is that justification enough? i now its extra ETL effort but that's not a problem for me. – wilson_smyth Apr 10 '16 at 18:56
  • Will these users play directly on the database tables? If so, they should be proficient enough to interpret the data structure the right way. If not, you can still provide them some user interface that reflects the difference between the two types of facts. Please note my addition to the answer concerning database views. – TAM Apr 10 '16 at 20:28