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 ...