Tod means Fox

This article is part of a series discussing the Kimball Group’s “34 Subsystems of ETL“. The Subsystems are a group of “Best Practices” for delivering a BI/DW solution. In my articles, I discuss how each Subsystem can be implemented in SSIS or hand coded in Visual FoxPro.

This subsystem mirrors the previous one I discussed: ETL Subsystem 17: Dimension Manager. It exists to ensure that fact tables are properly maintained and delivered to the organization. The functionality to accomplish this is done through ETL (with some help by other technologies, such as replication). The functions include (but are not limited to) the following significant tasks:

• Linking facts to dimensions by looking up surrogate keys
• Distribute facts throughout the organization (using the same techniques described for dimension distribution)
• Periodic snapshots, and rolling facts
• Generating aggregates

Note: I’ll discuss Aggregates more in my next post.

SQL Server 2005 Integration Services (SSIS)

Fact distribution can be done using ETL or through replication. While other methods exist (RPCs, Web Services, and other SOA-type solutions for example), ETL and replication are by far the most widespread and documented. SSIS works in both conditions just fine. For more thoughts on this, refer back to ETL Subsytem 17.

Hand Coding with Visual FoxPro (VFP9)

Nothing new to report! VFP is limited, without a native replication feature, on its own. It can move data in distributed environments quite well though. So using ETL as a solution is certainly high on the list. If replication is a must, then the VFP database backend will need to be replaced with SQL Server or similar RDBMS product.

From Here

As stated, in the next subsystem, I’ll talk about aggregates. I love aggregates (my wife wants a divorce), so my next post looks to be quite interesting!