title: How to handle late arriving dimension and NULL business key in Kimball Data Mart?
body: <p>I'm trying to implement Kimball data mart which uses -1 and -2 rows in dimension table for late arriving dimension and for NULL business key. I have an example code below which creates one staging table for fact and dimension data, two dimension tables and one fact table for data mart. Here is my example code with data in SQL:</p>
  
  <pre><code>--drop table stg_sales
  --go
  CREATE TABLE dbo.stg_sales
    (
       stg_sales_id       INT IDENTITY(1, 1) NOT NULL,
       sales_number       INT NOT NULL,
       sales_amt          INT NULL,
       cust_number        INT NULL,
       cust_firstname     NVARCHAR(50) NULL,
       cust_lastname      NVARCHAR(100) NULL,
       cust_address       NVARCHAR(500) NULL,
       salesperson_number INT NULL,
       CONSTRAINT pk_stg_sales PRIMARY KEY (stg_sales_id)
    )
  
  go
  
  INSERT stg_sales
         (sales_number,
          sales_amt,
          cust_number,
          cust_firstname,
          cust_lastname,
          cust_address,
          salesperson_number)
  VALUES (123,
          434,
          2342,
          'Jim',
          'Moriaty',
          'something',
          23)
  
  INSERT stg_sales
         (sales_number,
          sales_amt,
          cust_number,
          cust_firstname,
          cust_lastname,
          cust_address,
          salesperson_number)
  VALUES (124,
          234,
          2342,
          'Jim',
          'Moriaty',
          'something',
          23)
  
  INSERT stg_sales
         (sales_number,
          sales_amt,
          cust_number,
          cust_firstname,
          cust_lastname,
          cust_address,
          salesperson_number)
  VALUES (125,
          434,
          4545,
          'Joe',
          'Esk',
          'someother',
          24)
  
  INSERT stg_sales
         (sales_number,
          sales_amt,
          cust_number,
          cust_firstname,
          cust_lastname,
          cust_address,
          salesperson_number)
  VALUES (126,
          434,
          5555,
          'Daniel',
          'Hart',
          'Someaddr',
          NULL) --salesperson_number business key missing here
  
  INSERT stg_sales
         (sales_number,
          sales_amt,
          cust_number,
          cust_firstname,
          cust_lastname,
          cust_address,
          salesperson_number)
  VALUES (127,
          333,
          4444,
          'Pat',
          'Smith',
          'Someaddr',
          30)
  
  SELECT *
  FROM   stg_sales
  
  --create a dimension and fact tables
  --drop table dbo.dim_customer
  --go
  CREATE TABLE dbo.dim_customer
    (
       customer_wid   INT IDENTITY(1, 1) NOT NULL,
       cust_number    INT NULL,
       cust_firstname NVARCHAR(50) NULL,
       cust_lastname  NVARCHAR(100) NULL,
       cust_address   NVARCHAR(500) NULL,
       date_insert    DATETIME2 NOT NULL DEFAULT (Getdate()),
       date_update    DATETIME2 NULL,
       is_current     BIT NOT NULL
       CONSTRAINT pk_dim_customer PRIMARY KEY (customer_wid)
       CONSTRAINT chk_is_current CHECK (is_current IN (0, 1))
    )
  
  go
  
  SET IDENTITY_INSERT dbo.dim_customer ON
  
  INSERT dbo.dim_customer
         (customer_wid,
          cust_number,
          cust_firstname,
          cust_lastname,
          cust_address,
          date_insert,
          date_update,
          is_current)
  VALUES (-1,
          -1,
          'unknown',
          'unknown',
          'unknown',
          Getdate(),
          Getdate(),
          1)
  
  INSERT dbo.dim_customer
         (customer_wid,
          cust_number,
          cust_firstname,
          cust_lastname,
          cust_address,
          date_insert,
          date_update,
          is_current)
  VALUES (-2,
          -2,
          'Error',
          'Error',
          'Error',
          Getdate(),
          Getdate(),
          1)
  
  SET IDENTITY_INSERT dbo.dim_customer OFF
  
  --insert data into dimension table
  INSERT dbo.dim_customer
         (cust_number,
          cust_firstname,
          cust_lastname,
          cust_address,
          is_current)
  SELECT DISTINCT cust_number,
                  cust_firstname,
                  cust_lastname,
                  cust_address,
                  1 AS is_current
  FROM   dbo.stg_sales
  WHERE  cust_number &lt;&gt; 4444 --left one record off to simulate the situation where you don't have corrensponding row in dim table (late arriving dimension)
  SELECT *
  FROM   dbo.dim_customer
  
  DROP TABLE dbo.dim_salesperson
  
  --create salesperson table
  CREATE TABLE dbo.dim_salesperson
    (
       salesperson_wid       INT IDENTITY(1, 1) NOT NULL,
       salesperson_number    INT NULL,
       salesperson_firstname NVARCHAR(50) NULL,
       salesperson_lastname  NVARCHAR(100) NULL,
       salesperson_address   NVARCHAR(500) NULL,
       date_insert           DATETIME2 NOT NULL DEFAULT (Getdate()),
       date_update           DATETIME2 NULL,
       is_current            BIT NOT NULL
       CONSTRAINT pk_dim_salesperson PRIMARY KEY (salesperson_wid)
       CONSTRAINT chk_dim_salesperson_is_current CHECK (is_current IN (0, 1))
    )
  
  go
  
  SET IDENTITY_INSERT dbo.dim_salesperson ON
  
  INSERT dbo.dim_salesperson
         (salesperson_wid,
          salesperson_number,
          salesperson_firstname,
          salesperson_lastname,
          salesperson_address,
          date_insert,
          date_update,
          is_current)
  VALUES (-1,
          -1,
          'Not available',
          'Not available',
          'Not available',
          Getdate(),
          Getdate(),
          1)
  
  INSERT dbo.dim_salesperson
         (salesperson_wid,
          salesperson_number,
          salesperson_firstname,
          salesperson_lastname,
          salesperson_address,
          date_insert,
          date_update,
          is_current)
  VALUES (-2,
          -2,
          'Error',
          'Error',
          'Error',
          Getdate(),
          Getdate(),
          1)
  
  SET IDENTITY_INSERT dbo.dim_salesperson OFF
  
  --insert data into salesperson
  INSERT dbo.dim_salesperson
         (salesperson_number,
          salesperson_firstname,
          salesperson_lastname,
          salesperson_address,
          is_current)
  VALUES (23,
          'John',
          'Fox',
          'something',
          1)
  
  INSERT dbo.dim_salesperson
         (salesperson_number,
          salesperson_firstname,
          salesperson_lastname,
          salesperson_address,
          is_current)
  VALUES (24,
          'Hadley',
          'Fox',
          'something',
          1)
  
  INSERT dbo.dim_salesperson
         (salesperson_number,
          salesperson_firstname,
          salesperson_lastname,
          salesperson_address,
          is_current)
  VALUES (30,
          'Ashley',
          'Fox',
          'something',
          1)
  
  SELECT *
  FROM   dbo.dim_salesperson
  
  SELECT *
  FROM   dbo.stg_sales
  
  --create and populate the fact table
  --drop table dbo.f_sales
  --go
  CREATE TABLE dbo.f_sales
    (
       sales_number    INT NOT NULL,
       customer_wid    INT NOT NULL,
       salesperson_wid INT NOT NULL,
       sales_amt       INT NULL
       CONSTRAINT pk_f_sales PRIMARY KEY (sales_number)
       CONSTRAINT fk_customer_wid FOREIGN KEY (customer_wid) REFERENCES
       dbo.dim_customer(customer_wid),
       CONSTRAINT fk_salesperson_wid FOREIGN KEY (salesperson_wid) REFERENCES
       dbo.dim_salesperson(salesperson_wid)
    )
  
  --populate the fact table 
  INSERT dbo.f_sales
         (sales_number,
          customer_wid,
          salesperson_wid,
          sales_amt)
  SELECT stg.sales_number,
         Isnull(dimcust.customer_wid, -1)  AS customer_wid,
         --this is maybe correct way to assign -1 foreign key when there is no corresponding dimension row in the dim table
         Isnull(dimsp.salesperson_wid, -2) AS salesperson_wid,
         --NOT CORRECT, how to assign -2 foreign key when the business key is NULL in the source?
         stg.sales_amt
  FROM   dbo.stg_sales AS stg
         LEFT JOIN dbo.dim_customer AS dimcust
                ON stg.cust_number = dimcust.cust_number
         LEFT JOIN dbo.dim_salesperson AS dimsp
                ON stg.salesperson_number = dimsp.salesperson_number
  
  SELECT *
  FROM   dbo.f_sales
  </code></pre>
  
  <p>How can I assign -2 for the rows in which the business key is missing in the source system. You can read more about the theory behind this implementation from Kimball:</p>
  
  <ul>
  <li><a href="http://www.kimballgroup.com/2010/10/06/design-tip-128-selecting-default-values-for-nulls/" rel="nofollow">Kimball Design tip 128</a>  </li>
  <li><a href="http://www.kimballgroup.com/2006/04/01/design-tip-78-late-arriving-dimension-rows/" rel="nofollow">Kimball Design tip 78</a></li>
  </ul>
  
  <p>This is basically what I'm trying to achieve:</p>
  
  <p><a href="http://bifuture.blogspot.fi/2011/11/handling-null-in-dimensions-and.html" rel="nofollow">Handling NULL in dimensions and measures</a></p>
  
  <p>Edit:</p>
  
  <p>I think I can use a <code>COALESCE</code> or <code>ISNULL</code> in the left join, it seems to produce the correct result:</p>
  
  <pre><code>INSERT dbo.f_sales
         (sales_number,
          customer_wid,
          salesperson_wid,
          sales_amt)
  SELECT stg.sales_number,
         Isnull(dimcust.customer_wid, -1)  AS customer_wid,
         --this is maybe correct way to assign -1 foreign key when there is no corresponding dimension row in the dim table
         dimsp.salesperson_wid,
         stg.sales_amt
  FROM   dbo.stg_sales AS stg
         LEFT JOIN dbo.dim_customer AS dimcust
                ON COALESCE(stg.cust_number, -2) = dimcust.cust_number
         LEFT JOIN dbo.dim_salesperson AS dimsp
                ON COALESCE(stg.salesperson_number, -2) = dimsp.salesperson_number
  </code></pre>