Summary

We began this chapter by discussing various options for configuring your DW/BI system. System sizing is challenging because it depends on so many factorssome of which you wont have much information on until your system is in production. The easy factors to predict are data volumes and system availability requirements. Its harder to guess how many simultaneous users youll have, and how many of them will be performing challenging ad hoc queries. Nonetheless, we conclude that the vast majority of systems will use one or several commodity 4-way 64-bit servers. High-end systems will use one or more 8-way, 16-way, or even larger 64-bit servers. The 64-bit architecture is appealing because it allows so much more addressable memory. All the BI software components love memory.

We discussed storage architecture, and came down firmly on the side of using a Storage Area Network with some level of hardware-controlled redundant RAID.

We briefly described the SQL Server software installation issues for the development, test, and production servers. General installation and setup issues are well documented in SQL Server Books Online. This chapter is long enough that it makes no sense to repeat that information here. Instead, we focused on issues that are specific to data warehousing.

The second half of this chapter discussed setup and design issues for the relational data warehouse database. We described how to convert the logical data model into a physical data model in the relational database. We discussed indexing and foreign key enforcement. Finally, we described relational table partitioning, which can greatly improve the manageability of large fact tables.