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.

image from book
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 development.



Expert SQL Server 2005 Integration Services
Expert SQL Server 2005 Integration Services (Programmer to Programmer)
ISBN: 0470134119
EAN: 2147483647
Year: 2004
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net