Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

Loading a Fact Table

In most cases, fact tables are much easier to load than dimension tables. Typically, you are

inserting only rows into the fact table and very few updates, if any. The components used to

construct the load are much simpler than those for the Slowly Changing Dimension transform

that you used in Lesson 8.

When loading a fact table, the first thing you will do is look up the primary key or surrogate

from the dimension table using the natural key that is contained within the fact table source

data. The natural key, which is also known as the business or alternate key, is also stored as a

column in each dimension. During the fact load process a lookup transform is used to identify

the surrogate key or the dimensions table’s primary key. This value is stored in the fact table.

You may also be required to include some additional calculations or formulas before you load

the fact table. For example, you may be loading line item data and your source data may con-

tain only a Quantity value and an Item Cost value, although your requirements are to store a

LineCost column. This value is the result of multiplying the quantity and the cost. As a result,

you use a derived column task to apply the formula and create a LineCost column.

You may also encounter a situation in which you need to aggregate data because the data is at a

more granular level than required. Assume that your ...