Data Warehousing in Microsoft SQL Server 2008

10/29/2008

Contents

The Importance of Data Warehousing

Today, data warehousing in some form has become a given, a must, for running an enterprise of any significant size. At its best, it enables actual competitive advantage, but even when focused more tactically or departmentally, it is now considered essential to being competitive—as basic and essential as the general ledger or payroll system. While it is often difficult to quantify the benefits of data warehousing in terms of return on investment (ROI), no one these days seriously questions its value and necessity. As a database developer, you are likely to be involved with data warehousing in one way or another—if not directly, at least in interfacing to a data warehouse. So it’s important for you to understand what data warehousing is all about.

Developing a data warehouse is in some ways a very different undertaking from traditional online transactional processing (OLTP) database development, with which you are probably more familiar. Two of the most notable differences are that data warehousing essentially emphasizes data and its relationships—as opposed to the emphasis on process found in the typical OLTP application—and that hard experience by practitioners has evolved specialized ways of modeling data that are particularly useful in achieving the goals of data warehousing.

Even if your role is primarily technical, you will be able to do a much better job of building or interfacing to a data warehouse if you know something about these differences from OLTP and the reasons for them. This will also help you appreciate the perspective of decision makers who rely on accurate data storage and analysis (see the next chapter), which will be very likely different from that of typical OLTP application stakeholders.

Data warehousing is an essential foundation for what has come to be known as business intelligence (BI). We’ll learn more about the close relationship between data warehousing and BI later in this chapter, but for now, appreciate that they are not synonymous. At the same time, in keeping with our earlier observation, mentally substitute enterprise when you hear business.

The remainder of this chapter consists of five sections that build upon one another as we progress through our treatment of data warehousing. Instead of immediately focusing on technical details and step-by-step procedures in SQL Server 2008, we review the history leading up to why data warehousing is today a distinct practice and how SQL Server 2008 represents an excellent data warehousing platform.

The first section, “What Preceded Data Warehousing,” focuses on the origins of data warehousing to help you appreciate why data warehousing emerged as a distinct practice responding to industry issues. The second section, “Data Warehouse Design,” describes the two principal approaches to data warehouse design. The third section, “What Data Warehousing Is Not,” considers various terms often confused with data warehousing and gives them distinct definitions. The fourth section, “Practical Advice About Data Warehousing,” alerts you to various common but nonobvious issues that you might encounter when building a data warehouse. Last, the fifth section, “SQL Server 2008 and Data Warehousing,” discusses SQL Server 2008–specific details as they relate to data warehousing.

With this ambitious agenda to cover in just a single chapter, we will not actually tell you much about “how” to build the perfect data warehouse—dozens of entire books are available for that. Rather, what we aim to provide is a unique combination of background, clarification of terms, identification of tricky spots, and finally some technical details about the specific data warehousing platform offered by SQL Server 2008.

Data vs. Information

At the risk of sounding pedantic, fully appreciating why data warehousing is valuable requires drawing the distinction between data and information. Data consists of recorded, characterized “facts”—for example, sale amounts initiated by customer A at store B on date C, paid for with credit card D. These facts are the amounts of the sale (numbers), while the characteristics give these numbers meaning or context. This is the sort of transactional data typically captured by an operational application.

Such characterized facts are essential, but information involves interpreting facts, identifying the relationships between them, and finding the more abstract “meaning” (if it exists) implied by them. Each characteristic, such as customer, store, date, and so on, could serve as a predicate in a query. For example, what is the pattern of sales vs. store for this customer? Or what stores have the highest sales by date? Of course, there are countless others. These sorts of questions are higher order, or value adding, because their answers enable informed decision making for the future, as opposed to mere question answering of the sort that a customer service representative might do from the facts themselves (for example, when answering the question, “what is this charge on my statement that I don’t recognize?”).

This might not seem an important distinction, but historically, it often simply wasn’t technically feasible to assemble the available data in a form suitable for informed decision making. Often, what passed for that instead was instinct and educated guesswork. In contrast, data warehousing emphasizes organizing, standardizing, and formatting facts in such a way as to enable deriving such “information” from them. Building on that, BI is then concerned with defining, extracting, delivering, and acting on that information.

Save to your account