Read an Excerpt
1. Warehouse: What Is It, Who Needs It, and Why?
A data warehouse is a database of data gathered from many systems and intended to support management reporting and decision making. More broadly, though, when people talk about data warehousing, they are actually referring to those data stores and tools that, together, form modern reporting environments.In the two years since we wrote Oracle8 Data Warehousing, a number of changes have occurred in the field of warehousing and in Oracle8. Now it's called Oracle8i, but it could just as easily be called Oracle8dw. The warehousing field and its products continue to mature as companies gain experience with new technologies and adopt the best architectures for their needs. The old battles between "normalized" versus "denormalized" data models, while not completely resolved, seem much closer to resolution. Data mining-a form of analysis intended to create predictive models that was on the cutting edge two years ago-is moving more and more into the technology mainstream.
Still, the basic goals of data warehousing remain the same:
As you have probably guessed (or know if you've kept up with your technical education-you have, haven't you?), Oracle's support for powerful warehouses has also greatly improved over the past few years. Oracle's improvements are visible throughout their product line. From improvements in their DiscovererQuery Tool and their construction of new ETL (Extract, Transform, and Load) technologies to huge improvements in the basic database engine and data dictionary (as we will see later in this chapter and, indeed, throughout this book), Oracle is clearly committed to the field of data warehousing.
Ever use Excel? Spreadsheets such as Excel and Lotus 1-2-3 may have provided your first taste of "what if?" processing on the desktop. In spreadsheets, you could assemble data and view it in a number of ways: as a graph, as a table, assuming a 20% increase each year, assuming a 10% decrease each year, and so on. To a great extent, this is what data warehousing is all about-using information your business has gathered to help it operate better, smarter, quicker, and more efficiently.
Contrary to popular belief, most companies do not build data warehouses simply to test the patience of their technical staff. Data warehouses are, instead, a key component of well-reasoned business intelligence architectures. This chapter will help you understand what users require from a business intelligence system (BIS) and why a data warehouse is often necessary to satisfy these demands. We'll answer the following questions:
A Particularly Bad Day
Well, you've finally made it-chief information officer (CIO). Who would have thought when you started 18 years ago as an accountant turned COBOL programmer that you would be sitting in such a key spot? Who would have thought that your organization would someday put you in a strategic role? Who would have thought that all your jokes about CIOs spending their days at expensive vendor junkets or on the golf course were close to the mark? Who knew?
So, flush with the knowledge that you shot a 76 yesterday (and will likely shoot a 74 today), you check the value of your new stock options on the Internet. Suddenly, pandemonium breaks loose. You hear the angry voices outside your office. "Hey," you think, "that sounds like John Valjon, our vice president of sales, and Norman Richelieu, our chief financial officer." You listen closely to the near screaming. "I don't care if he's busy, I'm seeing him NOW! Henchard just chewed us out and I want some answers!"
You think, "Now Richelieu is a bit uppity, but I've never heard Valjon so agitated. I wonder what's up?" In storm the two gentlemen, flushed with anger, beads of sweat gathering on their foreheads.
Richelieu starts, "What the heck is up with you folks?! We spent $27 million on computers last year alone. Why is it that when John and I go into a meeting with the big man, our reports don't even match? How can it be that John thinks he sold 45,000 units in October while my reports show 42,500? How could your people possibly get such a simple thing wrong?"
John continues, "And that's just a touch of the problems I'm having with you folks. Last week I had a meeting with Alice Kramden, the purchasing chief at FutureChef Industries. I'm sure I don't have to remind you that FutureChef ranks third in our top ten target accounts. I know they're having a problem with late deliveries from their current vendor. It would have been great to show up at the meeting with a report showing our delivery record to each of the cities where they have facilities. You know our logistics department is among the best and this report could be critical to winning the account.
"Anyway, the appointment was scheduled for 3:00 P.m. I called your guys at 8:00 A.M. and requested the report. Do you know what they told me? Since my request was obviously important, they would push it to the 'top of their stack.' It would only take four days to write the programs and deliver the report. Four days! What good is that? I can't imagine how long it takes a non-VP to get a critical report.
"Beyond that, we have two sales systems-one for wholesale sales and the other for retail sales. Sometimes I'm interested in looking at just wholesale accounts or retail accounts. But, believe it or not, there are times when I need information about sales of particular products, regardless of where they occurred. It wastes my time and the company's money for me to have to go to two different systems, find the related figures, and plug them into a spreadsheet to figure out what the sales of product X were."
Then, it was Richelieu's turn again. "And another thing. How the heck (Richelieu has always been fond of saying, 'how the heck' and 'what the heck' and 'who the heck.' Regretfully, our publishers will not allow him to use language that is a bit more 'descriptive.' Please substitute a word of your choice for 'heck.') can I possibly forecast our cash needs when our general ledger system keeps only six months of history online? With the seasonality in our industry, even two years wouldn't be enough.
"And hey, I'm a finance guy. I live in spreadsheets. If I can't sort it, summarize it, or chart it, I'm cranky. So why is it that you can only give me data in paper reports? My salary costs the company over 300K per year. I'm spending half my time typing numbers from your paper reports into my spreadsheet. Does this make sense? Frankly, I'm getting a bit tired of this."
You start thinking, "300K .... My deal is good, but it's not that strong. Mental note: Renegotiate employment contract."
"Well guys," you say, "let me chew on this for a while. I'll get back to you in a few days after I've figured it all out." The truth is, you're scared. After all, how secure is a CIO's job? You're the fourth that your company has had in the past six years. You never really asked why the others were "shown the door." Could it have been related to these same problems? Have there always been reporting problems here? How wise was it to take out a $450,000 mortgage last week?
Problems with the Current Reporting Architecture
After thinking about the heated conversation, you start analyzing the issues. What were your users really saying? Their concerns seem to break into a few categories.
Frequently, warehouse projects are initiated because users are concerned about the following:
It's not hard to see that such concerns bring into question the competence of the IS (information systems) department. Even more importantly, answering such concerns can have a huge impact on the profitability of the company. How much, for instance, would it be worth to the company to have been able to provide Valjon with the report that could have won the FutureChef account? Do you think that was the only opportunity to leverage reporting to grow the company? In all probability, similar concerns arise all the time. Support this type of request and you build a new future for your company.
The Goal: Business Intelligence
As we noted earlier, the data warehouse is simply one component of modern reporting architectures. The real goal of reporting systems is decision supportor its modern equivalent, business intelligence-to help people make better, more intelligent decisions.
While you might not realize it, virtually all computer systems have some business intelligence component. That component is the reports produced by these systems. People take these reports and make decisions based on the information that they contain.
Since the introduction of computer systems, decision support has evolved greatly. Originally, computer systems produced paper reports. Users generally received information on a periodic basis via daily, weekly, monthly, or annual reports. To receive custom reports, users would contact the IS department and have a programmer assigned to write a program to create that report. This task would take anywhere from a few hours to, in extreme cases, months. We've heard stories about companies that had an 18-month turnaround time on report requests. Eighteen months! ...