1000-1002

Previous Table of Contents Next

Page 1000

In its simplest form, this schema would allow us to populate the tennis_shoe_sales table with a simple count of sales and a composite key for each dimension. The create table statement for a table would be something like this:

 create table tennis_shoe_sales ( units_sold    number, location        varchar2(20), month_of_sale    varchar2(15), unit_price    number(5,2) ); 

The key column of this table makes it multidimensional, but not normalized is units_sold. If our buyers wanted a particular cell of our data cubefor instance, the total units sold in Chicago in the month of July for the price of $69.95we would only have to issue the following query:

 select units_sold from tennis_shoe_sales where location = `CHICAGO' and month_of_sale = `JULY' and unit_price = 69.95; 

This SQL statement, as you notice, does not perform any of the costly aggregation that a SUM operator or a GROUP BY operator would entail using a simple normalized set of tables. This table holds aggregate values broken apart by the different values in each dimension. Therefore, they do not need to be computed.

Oracle's Discoverer 3.0 handles the concept of the star schema even more efficiently than this and leaves the details of storing aggregations out of the hands of the average user . These sophisticated methods of storing values in a star schema usually involve converting much of the table values to indexes and using smaller code tables that point to the star's center. There are also algorithms to collapse large blocks of NULL values, which the simple table structure above does not do for us as efficiently.

As an administrator of Discoverer 3.0, you could choose to not store certain data cubes as aggregations but instead calculate the values on-the-fly . In this case, the OLAP star table tennis_shoe_sales would be something more like a view that pointed to our original Sale table. With a view, aggregation occurs every time a user queries data from the view. More costly computer operations of reading, writing, and sorting the original data must be performed. We could choose to create the center of our star as a view with the following syntax given the structure of our original table called Sale:

 create view tennis_shoe_sales ( units_sold    number, location        varchar2(20), month_of_sale    varchar2(15), unit_price    number(5,2) ) as select 

Page 1001

 sum(quantity_sold), location, to_char(timestamp, `MONTH'), unit_price from sale where item  = `TENNIS SHOES' and to_char(timestamp, `MONTH') in (`JUNE', `JULY','AUGUST') group by location, to_char(timestamp, `MONTH'), unit_price; 

Discoverer will create this type of multidimensional object for you without the user needing to generate the above view; if we look at the complexity and cost of this view, whenever it is executed against a large point-of-purchase sale database, we see the advantage of storing the pre-calculated aggregations, which can be created with Discoverer's Summary Wizard.

A couple questions that an astute observer might ask are, "What happens when a new sale enters the system? Won't the old totals be incorrect?" The answer to latter is affirmative , but Discoverer has mechanisms to update the aggregations whenever the sales data changes. This mechanism is similar to a trigger.

Discoverer as a Tool for Data Warehousing

A data warehouse stores data so that corporate information can be saved and analyzed . It usually is separate from an online system and is the corporate repository of data. Data warehouses are characterized as usually containing a larger row amount of data. Data warehouses usually have more than one feed and usually involve source data from a legacy system. Another characteristic of a data warehouse is that many times data structures are already denormalized for reporting and summaries. The requirements for a data warehouse are analytic in nature. This is why OLAP fits in so well with the data warehouse model.

Oracle's Discoverer product, aside from using the star schema and other advanced algorithms to store summary data as data cubes, is a tool designed to wrap around a data warehouse because it hides users from the simple concepts of tables and gives them the concept of a business area.

The Discoverer administrator defines folders that can be based on one table or a view of many tables. The administrator can also define a business object like profit as really a mathematical function of a group of columns :

profit = total_revenue - labor_costs - material_costs - rent - taxes

In this case, profit is derived from a number of different columns, but in the Discoverer folder it appears as a simple atomic data element. This hides complexity from the user and allows you to redefine profit without affecting existing reports .

Discoverer simplifies the data warehouse front end, not only by sheltering users from table definitions, but by following OLAP standards and providing the user with graphical drill down capabilities between database objects along with the power of default formatting for reports.

Page 1002

Discoverer also provides reactive and predictive query governing that is vital to a data warehouse. Users can be given upper and lower limits to the length of time in which their queries can run, and users can be given the predictive time it will take for an answer. This eliminates the problem of certain users monopolizing the resources on a data warehouse due to their lack of understanding regarding the costs of different types of SQL queries.

Because Discoverer dynamically recalculates values and stores aggregations in a multidimensional schema, the data warehouse schema does not have to include the denormalized and summary tables needed for many types of reporting and analysis. This layer is handled by Discoverer, so home-grown kludges to maintain the derived or aggregated values in a data warehouse need not be undertaken.

Discover also provides a security level within different folders, or data cubes. This allows users to view data related to their business functions, without the need to understand all of the underlying table relationships. Without this, limited security on a larger number of tables would need to be granted so each analyst could build his own view of the data.

Even though you can use Discoverer on systems other than data-warehousing databases, and a data warehouse need not use a multidimensional tool like Discoverer, the tool becomes more valuable when used on larger corporate datasets, and in turn , the data warehouse becomes less costly to create, maintain, and utilize with Discoverer automating many of the most complex aspects of data warehouse management and database implementation.

Discoverer 3.0Features, Functions, and Benefits

If this chapter hasn't sold you on the need for a multidimensional OLAP database or on the need to integrate Discoverer with your existing data warehouse, consider the features of the Discoverer product and how it relates to your data needs. Here are some of the advantages of this product:

• Discoverer is easy to learn. With wizards, cue cards, and the Quick Tour, the training curve for the user addition is very short. The user addition is the software seat or seats that you buy for the business analysts who will use Discoverer on an existing set of folders that are parts of multidimensional databases. The tool also is very simple in its administrative addition. The administrator need only know the basics of relational databases and SQL, not the complex queries demonstrated above to create data cubes and derived values.
• Discoverer has a single interface regardless of what tasks you are performing. This makes the product more comfortable to use because it is integrated and not disparate pieces with different looks and feels.

Previous Table of Contents Next