1344-1345
Page 1344
you quickly can get into some very complex queries that are hard to write and difficult for the database to process. Vendors have designed formats other than traditional rows and columns in a table to store this information (multidimensional databases, for example). These structures can perform some of the complex drill- downs associated with certain types of analytical processing ( especially some what-if modeling). Oracle has a series of products (its OLAP suite) that stores information in these alternative formats. Although these other database formats have some advantages when it comes to processing queries, they generally still are fed their data from relational databases. Therefore, you usually can see one of these formats as an adjunct to your basic data warehouse instead of a total replacement for it.
| NOTE |
Even though data warehouses are just another use of the Oracle RDBMS, they present their own series of challenges. They usually are larger than most of the other databases you use. The queries can involve a large amount of processing and use large amounts of server memory. Therefore, you need to carefully think out your architecture and needs before implementing a data warehouse. |
Typical Uses of Data Warehouses
Very few companies have the luxury of storing data just for the sake of having it. Also, most OLTP systems provide some basic reporting mechanisms geared mainly toward obtaining data on a particular transaction or information such as the inventory levels of products in a warehouse. So why would anyone build a data warehouse? Some of the more common responses to this question follow:
- I need to analyze information that currently is scattered across multiple OLTP systems and host computers.
- I need to access information that is locked up in an extremely complicated format that requires a highly trained programmer to write applications to access it.
- I want to start performing more detailed, routine analyses to understand how my business is performing, but I cannot afford to slow down my OLTP systems with these reports .
Most of the data warehouses I have worked with were designed to allow end users to analyze their business areas so that they can make better decisions in the future. To access these warehouses, they use a series of standard reports designed to provide a flexible range of sorting and selection criteria. One report can be used to show sales totals by store for a particular product, for example, or sales totals by region for a given period. These standard reports enable users to fill in some data on a report control screen and then run the report knowing that a professional programmer has taken care of the programming logic. At times, users need something special,
Page 1345
so they have ad-hoc reporting tools available. These are powerful tools that enable users to adapt to new situations, but it takes much more time for them to build the report and verify that it is correct.
The business areas I have seen that most often use data warehouses are sales, marketing, and purchasing. It seems that people who are closest to the money have an easier time justifying the cost savings and other benefits of implementing a data warehouse. This is actually a good use of a data warehouse. Whereas engineers deal with systems that can be analyzed in a laboratory; sales, marketing, and purchasing analysts have to consider the desires of a large group of people (the customers). To understand what people are willing to buy, you usually look at what they have bought in the past to look for trends. Therefore, you need to pour through mountains of sales information to try to forecast the future. This is not an easy task, but you can significantly increase profitability if you have the right products in stores at the right time. Personally, I like the cold, analytical world of the engineer, but I guess that is why I am in computers instead of sales.
Data warehouses have a wide range of other uses. Any time you need to look through a large volume of information from a variety of sources, you should consider a data warehouse. It still is relatively impractical to fuse information from multiple databases connected over a network by using a single query. True, SQL*Net and Net/3 enable you to link to other databases using a network. If your query returns a large number of rows, however, you'll saturate your network and have some very slow reports. The rest of this chapter covers some of the implementation details of a data warehouse. As you will see, there are a few tricks to building these systems if you are going to store very large amounts of information, but it is possible to get a reasonable size instance with a reasonable amount of effort.
Designing a Data Warehouse
Many design philosophies are out there in the programming world. Some emphasize analyzing the processes of a business from which you can derive data requirements needed to support those processes. Others key in on the data requirements of the user community and then figure out the processes based on these information needs. The object-oriented development world sees data and the methods associated with this data as a single entity referred to as a class or object. Although all these philosophies can get the job done, I concentrate on a data-centric philosophy for this section because data tends to be the basis of a data warehouse. The reports and other queries come afterward if you have a sound foundation of the data available.
Determining the Information Needed
I am not implying that reports are totally useless in the design process. On the contrary ”existing reports give you an insight into the kind of information your users want to analyze in the new data warehouse system. Your first step in creating a data warehouse is to determine the information your users want to access. Some of the techniques you might use in this process follow:
- Look at the existing reports produced by users to determine the information content you need to have in your data warehouse.
EAN: 2147483647
Pages: 391