1150
Part IX: Business Intelligence
Unfortunately, this is not always the case. You may encounter a scenario in which you need
to extract dimension data from a table that contains other data besides that data specifi c
to that dimension. Assume your source data is mingled with product data and sales data,
and you are required to extract the product data from the single table. The following query
illustrates the T-SQL code:INSERT INTO dbo.DimProduct(ProductAK, ProductName, ProductNumber,
StandardCost, ListPrice)
SELECT
DISTINCT
ProductID,
Name,
ProductNumber,
StandardCost,
ListPrice
FROM stage.Sales sp
LEFT OUTER JOIN DimProduct p
ON sp.ProductID = p.ProductID
WHERE
sp.ProductID IS NULLThe primary difference is the use of the DISTINCT keyword to ensure that you pull a single
row for each product.Loading Fact Tables
The next stop in the data warehouse, after the dimensions are loaded, is to load the
facts. As mentioned earlier, fact tables typically contain two types of columns: measures
and surrogate keys. The following is a script that creates a fact table that references the
customer and product dimensions via surrogate keys from those dimensions and one
measure — TotalDue:CREATE TABLE FastSales
(
CustomerSK int
CONSTRAINT UQ_FactSales_FactSalesID UNIQUE,CONSTRAINT FK_FactSales_To_DimCustomer_On_CusotmerSK
FOREIGN KEY (CustomerSK)
REFERENCES dbo.DimCustomer(CustomerSK),
ProductSK int,
Constraint FK_FactSales_To_DimProduct_On_ProductSK
FOREIGN KEY (ProductSK)
REFERENCES dbo.DimProduct(ProductSK),
TotalDue money
)c51.indd 1150c51.indd 1150 7/31/2012 10:28:42 AM7/31/2012 10:28:42 AM
http://www.it-ebooks.info