Data Warehouse in Summary

This chapter introduced a lot of concepts about the purpose and function of a data warehouse. These principles apply to any decision-support data structure, ranging from a central corporate data warehouse to a department or subject-specific data mart. You learned that a data warehouse is a relational database designed specifically to support fast queries and reports. As such, the rules of normal form, typically applied in transactional database design to reduce redundancy and enforce data integrity, generally don't apply.

Dimension tables contain descriptive information about the business facts but not the measurable facts themselves. Dimensions are usually organized into hierarchal levels. A fact table contains detail rows, usually aggregated to the lowest level of needed detail across all of the related dimensions. Each fact table describes a specific business fact to help simplify design and to optimize query performance. Fact tables contain only foreign keys related to the dimension tables and measure values used in aggregation. The design characteristics of your data warehouse should support pre-defined business needs such as how future changes to dimension and fact data should be handled. This can be accomplished by storing each change as a separate archived record or by simply discarding previous values and updating a single record.

To design a complete data warehouse, there are many other details to consider, but this chapter has introduced the essential concepts and issues. This should serve as a foundation on which you can build a relational decision-support database to be used for reporting or to populate OLAP cubes for in-depth analysis.

Reset the Relationships for is2005sbsDW

Note 

The final step is to set the is2005sbsDW database back to its previous state with no relationships. This step is necessary to repeat the exercises for previous chapters.

1. Open the script file named  Chap12 - is2005sbsDW Remove Relationships.sql, using the File > Open > File menu in SSIS.

2. Execute the script, using the Exclamation button on the toolbar.

3. Close the script file when execution is completed.