1149
Chapter 51: Business Intelligence Database Design
51
FIGURE 51-4
Fact load using SSIS.Loading Dimensions
The source data has a tremendous effect on which method will be used when loading the
data into the data warehouse. If you encounter a situation in which all the fact and dimen-
sion data has relatable column, or more preferably foreign, keys, the process is straightfor-
ward. To load a dimension you would compare the rows in the dimension table to the rows
that are contained in the incoming source data. As a best practice, you should copy the
source data into staging tables instead of loading data directly from your source system.
This should eliminate the possibility of contention during the loading of a dimension table.
The following example illustrates the use of a LEFT OUTER JOIN to detect the new rows
that will be inserted into the DimProduct table:INSERT INTO Warehouse.dbo.DimProduct
(ProductAK, ProductName, ProductNumber, StandardCost, ListPrice)
SELECT
ProductID,
Name,
ProductNumber,
StandardCost,
ListPrice
FROM Stage.dbo.DimProduct sp
LEFT OUTER JOIN Warehouse.dbo.DimProduct p
ON sp.ProductID = p.ProductID
WHERE
sp.ProductID IS NULLThis is a simple approach. This assumes that you have imported the source data into a stag-
ing table and that you are not tracking history about data changes and focus only on new
row additions. In this case your query needs to scan only the existing dimension table.c51.indd 1149c51.indd 1149 7/31/2012 10:28:42 AM7/31/2012 10:28:42 AM
http://www.it-ebooks.info