title: How to design a fact table for delivery data
body: <p>I'm building a data warehouse that includes delivery information for restaurants. The data is stored in SQL Server 2005 and is then put into a SQL Server Analysis Services 2005 cube.</p>
  
  <p>The Deliveries information consists of the following tables:</p>
  
  <p><strong>FactDeliveres</strong></p>
  
  <ul>
  <li>BranchKey</li>
  <li>DeliveryDateKey</li>
  <li>ProductKey</li>
  <li>InvoiceNumber (DD: degenerate dimension)</li>
  <li>Quantity</li>
  <li>UnitCosT</li>
  <li>Linecost</li>
  </ul>
  
  <p><strong>Note:</strong> </p>
  
  <ul>
  <li>The granularity of FactDeliveres is each line on the invoice</li>
  <li>The Product dimension include supplier information</li>
  </ul>
  
  <p><strong>And the problem:</strong> there is no primary key for the fact table. The primary key should be something that uniquely identifies each delivery plus the ProductKey. But I have no way to uniquely identify a delivery.</p>
  
  <p>In the source OLTP database there is a DeliveryID that is unique for every delivery, but that is an internal ID that meaningless to users. The InvoiceNumber is the suppliers' invoices number -- this is typed in manually and so we get duplicates.</p>
  
  <p>In the cube, I created a dimension based only on the InvoiceNumber field in FactDeliveres. That does mean that when you group by InvoiceNumber, you might get 2 deliveries combined only because they (mistakenly) have the same InvoiceNumber.</p>
  
  <p>I feel that I need to include the DeliveryID (to be called DeliveryKey), but I'm not sure how. </p>
  
  <p><strong>So, do I:</strong> </p>
  
  <ol>
  <li>Use that as the underlying key for the InvoiceNumber dimension?</li>
  <li>Create a DimDelivery that grows every time there is a new delivery? That could mean that some attributes come out of FactDeliveries and go into DimDelivery, like DeliveryDate,Supplier, InvoiceNumber.</li>
  </ol>
  
  <p>After all that, I could just ask you: how do I create a Deliveries cube when I have the following information in my source database</p>
  
  <p><strong>DeliveryHeaders</strong></p>
  
  <ul>
  <li>DeliveryID (PK)</li>
  <li>DeliveryDate</li>
  <li>SupplierID (FK)</li>
  <li>InvoiceNumber (typed in manually)</li>
  </ul>
  
  <p><strong>DeliveryDetails</strong></p>
  
  <ul>
  <li>DeliveryID (PK)</li>
  <li>ProductID (PK)</li>
  <li>Quantity</li>
  <li>UnitCosT</li>
  </ul>