Expert SQL Server™ 2005 Integration Services

5.5. Advanced Fact Table ETL Concepts

The advanced topics in fact table ETL involve changing data grain from the sources to the fact table, handling missing dimension records, and dealing with late-arriving facts.

5.5.1. Managing Fact Table Grain

Many fact table scenarios require data grain changes. This means that the source data coming into the ETL process must be modified to match the record detail in the fact table. For example, you may have a source feed with a certain level of data like product sales at a store for a day. This is called the grain of the data. The fact table destination for the same data coming into the process might be at a different level, such as rolled-up to the week level. In this case, the data grain has changed from day to week. Rolling up data may not always be based on time. For example, a product may be rolled up to a sub-category or category level.

5.5.1.1. Data Grain Change Scenarios

Grain changes come in many different forms, including some of these common scenarios:

• Consolidating sources, such as combining multiple tables or files together or joining sources to produce a complete set of business keys needed for the fact table. A common situation is when multiple locations within an organization use different copies of the same system. To report off of a combined fact table, these sources must be consolidated together.

• Breaking out sources may be required if you have a single table that populates multiple fact tables. For example, an ERP system ...