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

Fact Table Modeling

A fact table is modeled to be the center of the star schema in a data warehouse. It consists of

two primary types of data:

Key columns,



which point to the associated dimension tables

Data columns,



which normally contain additive or numeric data representing a trans-

action or snapshot of a situation at a point in time. Numeric data is more common in

financial or inventory situations.

You need fact tables because they allow you to link the denormalized versions of the dimen-

sion tables and provide a largely, if not completely, numeric table for Analysis Services to

consume and aggregate. In other words, the fact table is the part of the model that holds the

dollars or count type of data that you would want to see rolled up by year, grouped by cate-

gory, or so forth. The fact table holds “just the facts” and the keys to relate the needed dimen-

sion tables. Since many OLAP tools, like Analysis Services, look for a star schema model and

are optimized to work with it, the fact table is a critical piece of the puzzle.

The process of designing your fact table will take several steps:

1. Decide on the data you want to analyze.

Will this be sales data, inventory data, or financial data? Each type comes with its

own design specifics. For instance, you may have to load different amounts of data

based on the type of analysis you’re doing.

2. Once you’ve identified your ...