Chapter 6. Developing the ETL System
What could possibly be more fun?
The Extract, Transformation, and Load (ETL) application is the foundation of the DW/BI system. Business users don't see the ETL system, and they don't care about it. That is, they don't care unless the data isn't available first thing in the morning. They don't care unless the data is unreliable. If the business users care about ETL, it's probably bad news for the DW/BI team.
Building an ETL system is largely a custom process. ETL tools, like SQL Server 2005 Integration Services, provide a huge productivity boost over hand-coding. But there's no getting around the fact that companies have diverse source systems. Integrating and aligning the data from those systems is an exercise in accounting for idiosyncrasies.
When Microsoft decided to rebuild Data Transformation Services (DTS), it took an "If it ain't broke, don't fix it" approach. Comparing DTS with Integration Services, we can infer that the pale yellow background in the designer wasn't broken. Other than that, Integration Services is astonishingly different from DTS. Integration Services is a real ETL tool, and you can use it to build real, enterprise-class ETL systems. This chapter describes how to do exactly that.
Integration Services can be used in a wide variety of scenarios beyond ETL. In this chapter, we focus exclusively on how to implement the ETL system for a Kimball Method data warehouse. We come back to other Integration Services features and ...
Get The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
