title: One or multiple fact tables
body: <p>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.</p>
  
  <p>Measures I will need to calculate are:</p>
  
  <p>-Total number of students</p>
  
  <p>-Number of nationalities (and filter by nationality)</p>
  
  <p>-Number of students who passed a course</p>
  
  <p>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.</p>
  
  <p>This gives me two sets of fact data at different granularities.</p>
  
  <ol>
  <li><p>granularity of all students on a course.</p></li>
  <li><p>granularity of students that have completed a course and passed and failed.</p></li>
  </ol>
  
  <p>My options as i see them are:</p>
  
  <ol>
  <li><p>Two seperate fact tables at the different granularities that share a few dimensions.</p></li>
  <li><p>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".</p></li>
  </ol>
  
  <p>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?</p>
  
  <p>Schema showing plan with both fact tables is shown below.</p>
  
  <p>Thanks for any advise </p>
  
  <p><a href="https://i.stack.imgur.com/WpUUZ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WpUUZ.jpg" alt="enter image description here"></a></p>