1017-1019

Previous Table of Contents Next

Page 1017

NOTE
Truly dynamic join conditions probably would be difficult to implement, because these conditions determine certain parameters in the multidimensional data dictionary of Discoverer 3.0 and also may determine the different options when building the data cube. As I stated earlier, I think that one problem with Discoverer 3.0 is that it doesn't give administrators an understanding of the physical and logical levels of the data cube and the star schema. I can't blame Oracle, though; this may be considered very proprietary information.

You might have a question about the screen shown (but, believe me, you shouldn't lose any sleep over it). You might be wondering, "Why do I have to define these relationships now? Why can't they also be defined in the User Edition?" In the User Edition, joins are defined automatically when users bring up a business area. I think limiting explicit join definitions to the Administrative Edition might not be the best path for Oracle to take. The Administrative Edition is powerful enough without this minor functional benefit, and this feature would be helpful in creating ad hoc queries in the User Edition.

Create Calculated Items

From a theory standpoint, the feature to create derived columns gives Oracle a stronger claim for more "OLAPness" when using Discoverer, because one of Codd's original specifications for OLAP includes accessibility. The capability of a multidimensional OLAP database to "map its own logical schema to heterogeneous physical data stores and to perform any conversion necessary" is fundamental in this age of downsizing and client/server computing. In the spirit of Codd, the founder of relational theory, you now will use Discoverer to create an item from the VIDEO logical schema for your OLAP end-user layer. You'll call the new item Profit, because, after all, isn't that what it's all about?

The Create Calculated Items option enables you to hide a great deal of the complexities of SQL functions and arithmetic that would need to exist in every report or calculation if they were not stored as rules by the OLAP database. This feature saves you a great deal of software labor in the development of a data warehouse that supports OLAP.

The screen is very funny ; it's a little calculator you can use to click on columns and expressions. It does save you time typing and making corrections, though. I tried to insert a carriage return in the diagram so that you could see the calculation that also appears in the Tutorial Guide, but it didn't accept the carriage return and gave me an error. The best way to use this feature is just to treat it as an online calculator of database columns.

Create Conditions

Remember that weird little funnel icon I mentioned earlier? You'll see this same icon in the Administration Tasklist under the Create Conditions option. Just as you created formulas to control the appearance of columns that were transformed into friendly business items, you now

Page 1018

can limit row retrieval with this option. By doing this, though, you take away the where clause of the SQL statement from the analyst using the User Edition unless you select the Optional radio button in the lower-left corner, which makes the restriction optional. You restrict your analysis to product_keys with values no greater than 100,000.

Before you choose the Create Conditions option from the Administration Tasklist, select a folder on which to work. Then click the columns to build your restriction by just clicking the down arrows you see as you build the statement. Again, this option has the feeling of a calculator, but it enables you to build restrictions with all the basic SQL operators.

TIP
Remember that you use the Create Conditions option to restrict rows in the database. Suppose that you have a data warehouse set up with 10 years of data, but you only want to create a business area for the last two years. You might use this option, because your multidimensional cube probably would be smaller (containing only two years of data) and take less time to calculate if you have an index on your data warehouse based on the year and can quickly pull out the data.

Create Item and Date Hierarchies

You can use Create Item and Date Hierarchies to view data in natural hierarchical patterns that exist in business. In the VIDEO database, for example, you see the following hierarchy within a product:

  1. Product type: The highest level, describing large groups of products, such as Food, Game, or Movie product types in this example.
  2. Product category: The child of the product type. The product category Candy, for example, belongs in the Food product type and is a subset of those products.
  3. Brand: As you drill down, you hit the Brand level ”the manufacturer of the product, such as Duff for Duff Beer.
  4. Product ID: The primary key specifying the codes that exist as children of a specific brand and defining each unique product offered . For the brand Duff, you know the exact product: Beer. In a more realistic database, you would find each product defined with a universal product code (UPC) and a more specific description, such as Duff 16 oz. Springfield Mountain Kegs.

After you click the Create item and date hierarchies icon in the Administrators Toolkit, you can choose a set of columns and move them over to the window at the right of the screen in their correct order in the hierarchy.

Hierarchies enable end users to drill down and look at Totals Sold by Product Type down to Totals Sold by Product ID. This information will help them understand their businesses. If

Page 1019

you look at the Sales_Fact table, you can see that you would need to perform a series of group -by -SQL statements to see these aggregates that with Discoverer 3.0 are stored in a star schema data cube and don't need to be generated at runtime. Of course, to do this, you also need to define summary folders, which are where the actual data cubes are built.

Create Item Classes

Item classes are ways of relating columns in one table with columns in another table to create a new object, which in this case is called a folder. The analogous SQL statement would be a creation of a view. You could have a view give you a quick way of looking at product codes and their descriptions, for example, so that you could use it as a list of values to help other analysts and developers. In this case, you want a quick lookup of Product ID and Description.

After you select this option from the Administration Tasklist, you see this prompt:

 Select the item that generates the list of values 

This prompt refers to the descriptive item. You enter the description first and then click Next. You then see an identical screen where you can choose the key value of your lookup.

I would prefer that the Discoverer 3.0 product would have combined the two Create Item Class screens into one graphical screen, but the power of this feature enables users to drill down into derived classes to see basic data elements that are descriptive and not just number keys. As an administrator, you now can view all the friendly product names after you click on the product key.

With your derived class describing the product key, you easily can see all the product descriptions after you click on the product key from the work area.

The Create Item Classes option enables users to jump sideways to a related item instead of just drilling down. Even though Description was not part of the earlier product hierarchy you created, you now can jump from the non-descriptive Product ID to the Description as you drill through relationships in the end-user layer. This allows the user to use a meaningful description versus a meaningless number key.

The Create Item Classes feature also enables users to sort by using alternative methods . You could place the product type MOVIE ahead of FOOD, for example, because movies are the main business of the Video Store and need to be displayed first for the executives viewing the Discoverer reports .

Create New Folders

Just as linking items helps users jump sideways in their OLAP analyses, you can create folders so that new business objects can be analyzed with other objects from many different tables. This partially satisfies Codd's condition regarding "heterogeneous data sources," because you could be using Oracle's RDBMS as a distributed database to tap remote data with Discoverer.

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net