title: Efficient way of populating Data Warehouse Dimensions from a staging table?
body: <p>I have a SSIS ETL package that loads a CSV file into a staging table that has 100+ columns of accounting data, we are creating a datawarehouse from this data.</p>
  
  <p>From the 100+ columns of data, below we can identify the columns needed to create our "product" dimension...</p>
  
  <p>Example data..</p>
  
  <pre><code>product_id,product_name,product_description
  '1','computer','power pc'
  '2','case','atx case'
  '1','computer','power pc'
  '3','cpu'intel'
  '3','cpu'intel'
  </code></pre>
  
  <p>So, what I did is created a DIM.PRODUCT table, that is...</p>
  
  <pre><code>CREATE TABLE DIM.OFFICE ( 
  "product_key" int IDENTITY (1,1) NOT NULL, -- Surrogate Key
  "product_id" varchar(150) NOT NULL, -- Business Key
  "product_name" varchar(150) NOT NULL,
  "product_Description" varchar(150) NOT NULL,
  )
  </code></pre>
  
  <p>We know that the "product id" from the example data uniquely identifies the product, so a product id of #1 will always have a product_name = computer, and product_description = power pc.</p>
  
  <p>The staging table has 3 million records, how do I populate my dimension from the staging table, so that later I can look it up when populating the fact table.</p>
  
  <p>The dimensions are a type 1 dimension, product id #1 will always be "computer","power pc".</p>
  
  <p>Previously I have been using SELECT DISTINCT "product id","product_name","product_description" to only get the distinct values from the staging table, and load that into the dimension, but I am finding this method to be taxing the system of resources when populating the dimensions.</p>
  
  <p>Is there a better, more efficient (performance wise) way of doing this? (using TSQL or SSIS)..</p>
  
  <p>We are running SQL 2008 SP3.. with plans to upgrade to SQL 2012 shortly.</p>