Chapter 6: Building FoundationsCreating and Populating Data Marts

He who has not first laid his foundations may be able with great ability to lay them afterwards, but they will be laid with trouble to the architect and danger to the building.
The Prince
—Niccolò Machiavelli

Overview

Now that you have been introduced to the business intelligence and database management tools in SQL Server 2005, it is time to get to work. The first thing we need to do is lay some foundations. We need to examine our data sources and determine if we can use the Unified Dimensional Model (UDM) to pull the information directly from each source or if we need to create one or more data marts.

We look at the criteria to use when determining whether a data mart is needed. Next, we walk through the steps necessary for designing a data mart. Finally, we look at two different methods for creating data marts: using the SQL Server Management Studio and using the Business Intelligence Development Studio.

Throughout this chapter and the rest of the book, we use our sample company, Maximum Miniatures, Incorporated (Max Min, Inc.), to provide concrete examples of each concept. In this chapter, we again examine the transactional systems used by Max Min, Inc. and determine if they need to create any data marts to fulfill their business intelligence needs. We then walk through step-by-step Learn By Doing exercises to help you better understand each concept.