title: SQL/SSIS DataWareHouse Fact table loading, best practices?
body: <p>I am building my first datawarehouse in SQL 2008/SSIS and I am looking for some best practices around loading the fact tables.</p>
  
  <p>Currently in my DW I have about 20 Dimensions (Offices, Employees, Products, Customer, etc.) that are of Type 1 SCD. In my dw structure, there are a few things I have already applied:</p>
  
  <ul>
  <li>No Nulls (replaced with blank for text or 0 for numeric during staging)</li>
  <li>unknown key members populated in each dimension (SK ID 0)</li>
  <li>UPSERT for SCD Type 1 loading from stage to production table</li>
  <li>SELECT DISTINCT for my loading of dimensions</li>
  </ul>
  
  <p>In my Fact loading SSIS project, the current method I have for loading dimensions is having multiple lookups (20+) to each of the DIMs, then populating the FACT table with the data.</p>
  
  <p>For my lookups I set:</p>
  
  <ul>
  <li>Full Cache</li>
  <li>Ignore Failures for "no matching entries"</li>
  <li>Derived Transformation with "ISNULL(surrogate_idkey) ? 0 : surrogate_idkey" for each SK so that if lookups fail they will default to the SK ID 0 (unknown member).</li>
  <li>Some of my dimension lookups have more than one business key</li>
  </ul>
  
  <p>Is this the best approach? Pictures attached to help with my description above.</p>
  
  <p><img src="https://i.stack.imgur.com/6NHSQ.png" alt="enter image description here">
  <img src="https://i.stack.imgur.com/JWjyc.png" alt="enter image description here">
  <img src="https://i.stack.imgur.com/3QQ71.png" alt="enter image description here"></p>