title: Slowly Changing Fact Table?
body: <p><strong>Background)</strong>
  I've gone through the process of building a fact table for our inventory data that will in theory act as a nightly snapshot of our warehouse. What is recorded is information such as quantity, weights, locations, statuses, etc. The data is very granular and in many cases not specifically related to a single entity (our source database records inventory data as having three primary keys: licenseplate aka pallet, product, and packaging type - so it has essentially 3 business keys and no surrogate key).</p>
  
  <p>The goal is to be able to have a 100% accurate recreation of our warehouse management system's data, that is viewable for any one day in history. So I can look up and see how many pallets of product XYZ was in location 1234 on the 4th of August.</p>
  
  <p><strong>Question 1)</strong> 
  Now, I have built this fact table to structurally look like a Slowly Changing Dimension, Type 2. Is this wrong? I've been reading up a little on accumulating snapshot fact tables and i'm beginning to question my design. What is the best practice in this situation?</p>
  
  <p><strong>Question 2)</strong>
  If my design is ok, how do I configure Analysis services so that it recognizes my DateStart and DateEnd columns in the FACT table? I have found some information on how to configure this for dimensions but it does not seem to work/apply to fact tables.</p>
  
  <p><strong>For reference - My fact table's structure (with added notes about columns):</strong></p>
  
  <pre><code>CREATE TABLE [dbo].[FactInventory](     
  [id] [int] IDENTITY(1,1) NOT NULL,  (fact table only surrogate key)
  [DateStart] [datetime] NULL,    (record begin date)
  [DateEnd] [datetime] NULL,       (record end date)
  [CreateDate] [datetime] NULL,    (create date of the inventory record in src db)
  [CreateDateId] [int] NULL,       (create date dimension key)
  [CreateTimeId] [int] NULL,       (create time dimension key)
  [LicensePlateId] [int] NULL,     (pallet id dimension key)
  [SerialNumberId] [int] NULL,     (serial number id dimension key)
  [PackagedId] [int] NULL,         (packaging type id dimension key)
  [LotId] [int] NULL,          (inventory lot id dimension key)
  [MaterialId] [int] NULL,         (product id dimension key)
  [ProjectId] [int] NULL,          (customer project id dimension key)
  [OwnerId] [int] NULL,        (customer id dimension key)
  [WarehouseId] [int] NULL,     (warehouse id dimension key)
  [LocationId] [int] NULL,      (location id dimension key)
  [LPStatusId] [int] NULL,      (licenseplate status id dimension key)
  [LPTypeId] [int] NULL,    (licenseplate type id dimension key)
  [LPLookupCode] [nvarchar](128) NULL, (licenseplate non-system name)
  [PackagedAmount] [money] NULL,  (inventory amount - measure)
  [netWeight] [money] NULL,   (inventory netWeight - measure)
  [grossWeight] [money] NULL, (inventory grossWeight - measure)
  [Archived] [bit] NULL,  (inventory archived yes/no - dimension)
  [SCDChangeReason] [nvarchar](128) NULL (auditing data for changes)
  </code></pre>