Best Practices for Data Warehousing with SQL Server 2008

Writers: Mark Whitehorn, Solid Quality Mentors; Keith Burns, Microsoft

Summary: There is considerable evidence that successful data warehousing projects often produce a very high return on investment. Over the years a great deal of information has been collected about the factors that lead to a successful implementation versus an unsuccessful one. These are encapsulated here into a set of best practices, which are presented with particular reference to the features in SQL Server 2008. The application of best practices to a data warehouse project is one of the best investments you can make toward the establishment of a successful Business Intelligence infrastructure.

Introduction 

Microsoft SQL Server 2008 represents an excellent choice for the construction and maintenance of data warehouses in enterprises of all sizes.

The term Business Intelligence (BI) describes the process of extracting information from data. The operational data in most enterprises is held in transaction-based systems with specific functions (HR, Sales, Finance, and so on). Frequently the information requested by decision makers within the enterprise requires data from several of the operational systems. Indeed, the more general the question, such as “What is our current profit?” the more operational systems are likely to be involved in providing data.

An integral part of any BI system is the data warehouse—a central repository of data that is regularly refreshed from the source systems. The new data is transferred at regular intervals (often nightly) by extract, transform, and load (ETL) processes.