Hands-On Microsoft SQL Server 2008 Integration Services part 57. Deploy and manage high-performance data transformation solutions across your enterprise using the step-by-step techniques in this fully revised guide. Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition explains the tools and methods necessary to extract conclusive business intelligence from disparate corporate data. Learn how to build and secure packages, load and cleanse data, establish workflow, and optimize performance. Real-world examples, detailed illustrations, and hands-on exercises are included throughout this practical resource. . | 538 Hands-On Microsoft SQL Server 2008 Integration Services of an ODS cannot provide answers to such complex questions because either the data is not available in the ODS systems or the data model doesn t support such analysis. This results into creation of a data warehouse or a decision support system. A data warehouse or a DSS collects data from OLTP or ODS systems and might keep it in multiple forms that is in its most granular form and in aggregated form. A data warehouse keeps data for longer periods of time generally spread across several years even after it has been deleted from the source systems. Data Warehouse Design Approaches Now we know a bit about a data warehouse that it keeps years of history that it keeps data in granular as well as aggregated format and that the primary function of a data warehouse is to do business intelligence or analytical reporting. So the next question is how we design a data warehouse. There are two primary schools of thought top-down and bottom-up approaches and both are good and relevant to their own applications but also have associated risks involved. Top-Down Design Approach Bill Inmon who is best known as the father of data warehousing has defined this approach in which he suggests a data warehouse to be at the center of the Corporate Information Factory CIF designed using a normalized data model. The Corporate Information Factory approach takes the holistic view of the enterprise and its informational needs. In such a data warehouse data is collected from most of the organization s operational systems and is held at the atomic level that is at the lowest level of detail. Further the subject-oriented dimensional data marts containing aggregated data are built from the central atomic data warehouse to serve the departmental needs. As the data warehouse becomes a source system for all the analytical data marts and organizational reporting this creates a consistent view a single version of truth across the enterprise. It
