Chapter 5. Creating the Relational Data Warehouse

Where the rubber starts to meet the road.

This chapter is about instantiating the target relational dimensional model in a SQL Server database. Now that you have your servers set up, software installed, development environment in place, dimensional model designed and well documented, and, of course, your business requirements and priorities clearly defined, you can actually start writing some code in this chapter, if T-SQL counts as code in your book. Figure 5-1 provides a graphical example of the lifecycle context just described for this chapter.

Our goal in this lifecycle step is to get the physical structures in place so they can be populated by the ETL process in the next step. Creating the database and building the tables is a task for the DBA role. Folks who have prior SQL Server DBA experience should have no problem with this chapter. Those of you who are not really DBAs, but ended up with this role because no one else volunteered shouldn't panic. Basic DBA work in SQL Server is generally straightforward. Although this chapter is not a tutorial, we will tell you the key tasks and provide some examples to point you in the right direction.

In this chapter, you will learn:

  • How to deal with specific table and column implementation decisions such as surrogate keys, string data types, Unicode, NULLs, and default values

  • How to use table and column extended properties to capture descriptive metadata

  • About adding housekeeping columns that ...

Get The Microsoft® Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft® Business Intelligence Toolset, Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.