1020-1022

Previous Table of Contents Next

Page 1020

Create New Folders is a rather complicated option to use. Follow these steps:

  1. Create a new work area window by choosing WindowNew Window.
  2. Make this window smaller and move your first work area window in arrangement with the second. (Using auto-arrange makes this task easier; this option is under Window in the top menu.)
  3. Choose one of the items you want to link from the first work area to the second work area. You will notice a new folder in the second work area.
  4. Choose a column that belongs in this new folder from the first work area and choose EditCopy to copy it to the Clipboard.
  5. Choose the second work area and the new folder, and paste the new item into the folder.

Suppose that you map the product key to Dollar Profit. This will show you which products are the most profitable. A screen appears in the second work area window. The product profit folder now contains two data elements from two different sources.

This process is a way of building a more overt join relationship. In this case, we are joining Sales Fact and Product using the Product Key. Now this new folder is treated just like any other and can have any characteristics the original folders in your work area have.

TIP
This is a useful alternative to creating endless views in your business database to support every possible type of folder that an end user might request.

Create Summary Folders

This option is actually the fun part of the Administration Tasklist: the creation of the actual data cube that will store summary information. Essentially, a data cube holds data that usually consists of aggregate values such as summations of row data. These cubes stretch out in N dimensions, depending on how many distinct combinations of data you are viewing. If it is our sales data, you might look at products and stores to see how they are doing in terms of their total sum dollar sales. In the VIDEO database, you might use the variable time to track when certain products are being sold so that you can adjust your store hours or maybe close the food stand an hour early at our fictional video store.

If you wanted to retrieve total dollar sales for store 2 and for only Brand X, you would ask Discoverer to cut out a slice of the data cube.

The good thing about Discoverer is that you need not know anything about data cubes or even care about them. I am only presenting this information because eventually the OLAP guru will

Page 1021

need to understand such things. In the preceding example, breaking things down by product ID would have made a more complex data cube that would take longer for Discoverer to generate in the star schema.

Choose the option for the Summary Wizard and move to the opening screen in this section. From here, click Next because you are creating a new Summary folder.

When you click an item on the left side of the Summary Wizard that is of type number, Discoverer 3.0 immediately assumes that you want to perform an aggregation and gives you that option with the sigma (S) graphic, which is the Greek symbol for a summation. Suppose you select a summation of the Sales Fact - Dollar Sales folder. Because this is a number, Discoverer 3.0 enables you to click all standard SQL aggregate symbols: sum, min, max, avg, and count. In this example, you are selecting the two dimensions product and store for your data cube and the value held in the cell of your data cube. This value will be the dollar sales in the Sales Fact folder, and this figure will be summed for each cell in this dimension.

To enable users to drill through the different levels of the product hierarchy like you just did, you need to click Next to go to the next screen of this wizard, step 3. Here, you fill in the different combinations of data and the way in which you want their aggregates stored.

Suppose that you are generating a report that displays sales figures by product key, brand, store name , or region. You decide to allow for a combination starting off with Region as the highest level, because only three regions exist in the VIDEO database. For this report, you also will sum at each level below Region. You create a diagonal checkbox pattern to allow summing on each level moving down the tree of your report hierarchy.

After this interesting screen, you come to step 4. This is as close to the pulse of the interdimensional database as you will get. Here, you see a table of keys. These keys denote the type of structure at the center of a star in the star schema. Discoverer 3.0 now creates this table, which represents a data cube using the star schema and other structures to maintain the OLAP database.

This table will enable Discoverer to query the sum of sales for any given product just by a simple select statement, such as the following:

 select SUM_Dollar_Sales from TUTORIALEULSUM$....  Where product Key = value; 

In your original VIDEO database, the SQL would be more complex, requiring expensive aggregation. In this example, Discoverer 3.0 created four keys, because you wanted summations on four levels.

Discoverer needs to refresh the summary table as new rows are added to the business database, which in this case is VIDEO. Discoverer 3.0 enables you to control this refresh process through a simple screen. If you decide that this report needs to be updated every day, for example, you can choose Every day as the refresh interval to have Discoverer recalculate the summations for the data cube every day.

Page 1022

Grant Business Area Access (Mandatory)

In this final, imposing option of the Administration Tasklist, labeled Security, you simply grant access to your business area to a number of users. Just as with previous screens, you choose the user you want to grant access to as an end user and click the arrow to add that user to the box at the left of the screen. For example, you have decided to grant access to these Oracle usernames: JAMES, SCOTT, TUTORIAL, and VIDEO.

You can query the list of potential users for your business area separately from Discoverer 3.0 in the dictionary views DBA_USERS and ALL_USERS by invoking the SQL*Plus product and issuing the following statement:

 Select Username from ALL_USERS; 

This tool uses this data dictionary output as a departure point and performs grants to certain users for a specific business area.

The Discoverer 3.0 User Edition

One disappointment I had with the Discoverer 3.0 product was that I had to exit the Administrative Edition to use the User Edition, which has all the reporting capabilities. It would be simpler if administrators could test out reports and graphs with new OLAP data cubes without leaving the Administrative Edition.

When you exit to Windows and then enter the User Edition, you immediately see a prompt to load one of your business areas. To do this, you need a .dis file, which resides in the discvr30 subdirectory of your oracle_home directory. These files store information about each business area created by the Administrative Edition.

After your Business Area is open, you will enter the Workbook Wizard. Here you can choose from a variety of basic report formats, such as table, crosstab, page-detail table, and page-detail crosstab. For this example, choose a simple table diagram. To design a report that hopefully enables you to perform OLAP analysis, you need to create a new workbook. Both options appear in the next screen.

After you enter the workbook, which is similar to the business area for the Administrative Edition, you can use the Tools menu to sort , total, and perform calculations on each of the columns . To format the report, simply click and drag the lines between each column to even them out. In these initial steps, Discoverer 3.0 queries the data for you. If you created a logical data cube that is storing the aggregate values, this query should take much less time than it would to write the same Group By expression using a relational database directly.

In this basic screen, you can even export this file to Microsoft Excel to integrate with other projects on which you're working. You can also graph this data by using the most standard graphical objects, such as pie charts , bar graphs, and scatter plots. Because it is difficult to represent so many product IDs in a single graph, you might want to create a summary table that

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