1014-1016

Previous Table of Contents Next

Page 1014

• Matching column names: This option can be dangerous. If column names match in two folders (tables or views), a join condition is automatically mapped.
• List of values for items: Unique values are generated for listboxes for users in worksheets. (Worksheets are the areas where the end users of Discoverer will build their reports and structure their data.)
• Date hierarchies using: All date items use the default date hierarchy. You can view this hierarchy by double-clicking the Hierarchies folder and then clicking the plus sign (+) icon to the left of the Date Hierarchy Templates option.

Now you can click the Finish key and will have reached the final step in the creation of your business area.

Moving Around in the Land of Folders and Items

Now that you have created your initial Video Land business, you have a powerful data-dictionary tool. At this point, you probably see just a single icon for Video Land. If you click the plus sign (+) next to Video Land, you expand one level out to view all the folders that are tables or views in your business database. Again, you can expand out another level by clicking the plus-sign icon to the left of the Sales Fact folder. To drill back up to a higher level, click on the minus sign of that level.

After you have an object at any level that you want to view, just click it. You can click the Dollar Sales item to see a pop-up box that describes the attributes of that item, for example. I decided to change the format mask because it was money to 99999D99. A format mask determines how a data element will appear in OLAP reports.

Click the plus sign by the Time folder and click all the time elements that are too complex and awkward. You might want to go through each window after you click each item and change its name to a more meaningful business description. For example, I invoked the Item Properties work area by clicking the Date DD field transaction. Because DD represents the format of a day in Oracle SQL formatting, I changed the name of this awkward column to the name day.

The Administration Tasklist: Don't Enter the
Dimensions Without It!

You probably will like the Administration Tasklist of Discoverer 3.0. Without it, you see all these funny -looking folders and strange icons; one is a funnel-shaped thing. You can hold your mouse cursor over each item to see a description of its function in the status bar at the bottom of the screen. Instead of this exploration, you can use the Administration Tasklist to view all the major areas of Discoverer 3.0. Just choose ViewTasklist.

Page 1015

TIP

If you are working on a large screen, you might want to drag the Administration Tasklist to the side so that your main work area view is not impeded.

Now take a closer look at this tasklist; it lists the core functions that Discoverer 3.0 administrators need to understand to complete their tasks . Most of the complexities of setting up a multidimensional database are left under the hood. Discoverer administrators need to know a great deal less about multidimensional database theory than Oracle RDBMS database administrators need to know about relational theory. Oracle hides many of its storage methods from Discoverer administrators; you would need to understand the relational star schema of the end- user layer database owner to understand how Oracle implements OLAP and its multidimensional storage foundation.

Another reason for this hidden complexity is that many times Discoverer is simply used as an aggregate reporting tool because it has many rich formatting functions.

NOTE

One area that isn't addressed in the Administration Tasklist is the tablespace strategy you will use to create and store your data cubes, which are in the form of relational tables using the star schema strategy.
Tablespace strategy involves putting the intensive default temporary tablespace of the end-user layer table owner on a separate disk or redundant array of inexpensive disks (RAID) system. This becomes more critical if you are going to be OLAPing a great deal of data. You also must consider where to place the multidimensional data tables themselves . The owner of the end-user layer table's default tablespace should be large enough to handle the storage requirements of different types of data cubes. As of this release, I wasn't able to get the product to give me any estimates as to size when it created a data cube using the star schema strategy.

The tasklist contains the modular set of methods Discoverer 3.0 users need to implement OLAP technology (aside from storage issues). In the following subsections, you'll play around with each of these methods and examine how each affects the way in which Oracle implements OLAP and the multidimensional database strategy.

Create Business Areas (Mandatory)

Because a business area was created when you entered the first menu of the product, you might not need to use this function. You might use this option to create a second, on-the-fly business area to test out a new economic theory or a hunch about your data and its relationships, though.

Page 1016

Edit Item Properties

This function is another way for you to choose an item (you also can choose an item by zooming in and out of folders in the work area). If you want to zoom in on the Product folder to the Product_Id item, for example, click the plus (+) icon next to the folder. To zoom back out, click the minus (-) icon above. By clicking on the folder and then on the item itself, you get the functionality of this option in the Administration Tasklist.

Create Joins

In almost all business analysis of an existing normalized database, you will need to relate different groups of data together. For example, you might have data on sales that you want to relate to data on employees . Doing this in SQL is called a join. In Discoverer it is also called a join, but you are joining business folders that can be thought of as views of original table data.

Oracle automatically creates join conditions in the User Edition, which I suspect is simply dynamic SQL. Oracle also enables you to build join definitions on columns that are more permanent, but only by using the Administrative Edition. To explicitly define a join between two tables or views, you will enter a set of menus after you choose the Create Joins option from the Administration Tasklist. Through a hand-held process, you create a join condition between the two tables.

NOTE

Even if this task is simple for you, it is still a good idea to read up on basic join theory, or you might have a problem with the Cartesian product. A problem like this will result in an explosion of data when you join two non-unique columns. Also, you might want to examine the outer join using the Allow master option. Oracle recognizes the power and need for outer joins versus the more common inner joins.

Use the simple product_key to link a product that is sold in one table, SALES_FACT, but is described in a separate product table. This is typical normalization. You could think of the product table as simply a lookup table for the sale to describe the product. But, in actuality, this table also contains many built-in hierarchies such as Product_Category, Product_Type, Brand, and Product_ID, which will become dimensional lengths in Discoverer 3.0's OLAP multidimensional database. Therefore, both tables are equally important for OLAP analysis.

Suppose that you choose the product table as the parent. Later, you want to reverse that choice and make the Sales_Fact table the parent, but you don't see a way to edit join conditions. In this case you need to go into the work area and delete a faulty join condition and then re-create a new one. The join condition is dangling deep in the drill-down under the items for the a given folder. The label for this small join icon is Product -> Sales_Fact.

Previous Table of Contents Next