title: Slowly Changing Dimensions in SSAS and SSRS
body: <p>I have a project where establishments are inspected anything from once every 6 months to once every 3 years and the results of the inspection scorecard are recorded as a record in a type 2 slowly changing dimension table <code>[tblInspections]</code>, using <code>StartDate</code> and <code>EndDate</code> to cover the period between inspections for which this scorecard is valid.  The inspections table is linked to [tblEstablishments] which contains other details about other fixed dimensions such as  location and business type.</p>
  
  <p>So currently,  we are providing aggregated reports of current situation (<code>where EndDate is null</code>) and also audit reports for the history of any one establishment (On <code>EstablishmentID</code>)<br>
  My next task is to provide more detailed analysis reports of trends of the scorecard results and I need to provide historical aggregated results of the situation on the last day of each month.</p>
  
  <p>My problem is that despite knowing exactly what I want, I am now unsure how to get there.</p>
  
  <p>1)  Do I start by writing ETL process to build a cube based on all the historical results working out what all the aggregates would have been at the end of each month?</p>
  
  <p>2)  Am I then able to just process the current records at the end of each month effectively add a new slice onto the end of an existing cube without reprocessing from scratch? (if so how?)</p>
  
  <p>3)  Is there another way of doing this? Does Analysis Services have better ways of dealing with SCDs automatically when determining historical status at any point in time by selecting the correct record from multiple records with start and end date?</p>
  
  <p>Any advice and pointers to tutorials related to this would be much appreciated.</p>