Expert SQL Server™ 2005 Integration Services

5.3. Fact Table ETL Challenge

Fact Table ETL is similar to dimension ETL in that the source system may come in a different structure than the destination dimensional fact structures. Figure 5-4 shows (on the left) the transactional tables that participate in the sales activity for AdventureWorks. On the right, it shows the table structures that are involved in the dimensional model.

Figure 5-4. AdventureWorks transactional tables (left) and table structures involved in the dimensional model (right)

As you can see, the tables are different. There are two sales transaction tables in the source: a header and a detail. In the dimensional model, there are several fact tables, the two primary fact tables being the Internet sales fact and the reseller sales fact. Essentially, to perform the ETL, the header and detail transactional tables are combined, and then broken out based on whether the sale was direct-to-consumer (through the Internet) or sold to a reseller. The ETL performs both consolidation and a breakout (or partitioning) of the data. The challenge is to do this in a way that is scalable, manageable, and flexible.

To address the fact table ETL with SSIS, this discussion has been broken into the ETL basics of fact table processing and the advanced fact table ETL concepts. In all, this examination provides the tools and methods needed to move from requirements to SSIS package ...