1007-1010

Previous Table of Contents Next

Page 1007

CHAPTER 44

Using Discoverer 3.0

IN THIS CHAPTER

• Creating the Tutorial and Video Databases 1009
• Creating the OLAP Database 1010
• Creating the Sample Database 1011
• Mapping Data From a Database into Discoverer: The Load Wizard 1012
• Moving Around in the Land of Folders and Items 1014
• The Administration Tasklist: Don't Enter the Dimensions Without It! 1014
• The Discoverer 3.0 User Edition 1022
• Multidimensional Databases: The Possibilities Are Endless, Spock! 1024
• OLAP of the Mind 1026

Page 1008

This chapter takes you on a wild ride with Oracle's new Discoverer Release 3.0.7 for NT and Windows 95. You will load the demo database Oracle provides and look at this product as an OLAP tool, a multidimensional database, and a reporting tool. As you view specific functions of the product, you will see what is really happening in the "missing dimensions" of the database.

When you purchase Discoverer 3.0, you can buy the Administrative Edition or the User Edition. You should buy one Administrative Edition for each user who will be creating and maintaining the online analytical processing (OLAP) multidimensional business areas that you will be using. This can be simply one copy for one OLAP administrator or many copies for different departments within your company and their OLAP administrators.

NOTE

This chapter uses the term OLAP interchangeably with multidimensional database because OLAP requires a multidimensional database, and multidimensional databases are used primarily as OLAP back ends. OLAP vendors also use the multidimensional database when implementing OLAP technology. It is easy to think of the multidimensional database as simply the back end of the OLAP architecture.
Although there is some debate as to just exactly what type of tool Discoverer is, because it generates Sta Schema tables, which are how data cubes are generally stored in a realational database, Discoverer could be considered more than just an analytical tool.

The architecture of Discoverer 3.0 is simply both the User and Administrative Editions working behind an end-user layer that translates the relational data that is being studied and puts it into a multidimensional database star schema for processing. The end-user layer hides the complexities of this process from the user, as you will soon see.

The release I am using, 3.0.7, is called "limited production" in the tutorial readme file; it is the Administrative Edition of the product. You can find the file in your oracle_home directory under the subdirectory discver3.0\demo\demoinst.wri.

The path is usually \orawin95\discvr30\demo\demoinst.wri.

NOTE

It is important that you look at the readme file in addition to the instructions in the manual regarding the tutorial; changes may have been made or mistakes corrected since the printing of the manual.

Load the demo data to use for the demonstration in this chapter. The demo database is helpful because it is already populated with data and has certain multidimensional aspects that are

Page 1009

discussed later in this chapter. The sample database depicts a very simple video rental store. As you look at the specifics of the video store and how OLAP processing is used, you will consider these aspects in a broader sense of looking at Discoverer as a multidimensional database.

Creating the Tutorial and Video Databases

In this section, you'll create two databases. To run the tutorial, you need a database of sample business data; this will be your first database. Oracle supplies you with the import file for the data in this database in the oracle_home subdirectory discvr30\demo\video.dmp. You also will create a new user called VIDEO to own the tables of this first database.

The second database you need to create is the multidimensional database that enables Discoverer 3.0 to perform the OLAP analysis. This database also will have a new owner called TUTORIAL. It is helpful to sometimes think of this multidimensional database as the data dictionary for the Discoverer 3.0 product. In this database schema, data definitions, summarization rules, and business areas are defined. After you create the TUTORIAL database, you can log on to SQL*Plus and issue this command:

 select table_name from user_tables; 

NOTE

Although the Tutorial is not a multi-dimensional database itself, it does contain information on multidimensional databases which determine how it stores data and executes queries.

You then see several EUL$<table_name> tables that are similar to the <table_name>$ tables owned by SYS that make up the data dictionary for the Oracle RDBMS. Usually, not even administrators need to understand these tables in Discoverer, because all object-management functions are automated.

It might seem silly that you need to create two users to run the tutorial, but it isn't. Step back for a minute and ask yourself what Discoverer does. It is a multidimensional database and OLAP tool that can analyze existing data, such as a data warehouse. In this example, your massive data warehouse is the 2MB video.dmp file. In the real world, this data would be in place or might be in the middle of the design process with a tool such as Designer/2000 (which you can interface directly with Discoverer). In a production environment, the only database you would need to create is the TUTORIAL database because you would not need a fictional VIDEO database. This database is also the owner of the tables that store the definitions for the end-user layer of the Discoverer 3.0 architecture.

Page 1010

NOTE

In terms of logical design, you do not need to create two Oracle database instances to run the tutorial. In a production system, though, creating two instances on separate machines or at least separating the data tablespace of the end-user layer from the actual business data speeds up the OLAP system.

After you create this demo database and end-user layer, you need to use the Administrative Edition of the product because you will be creating and modifying a new multidimensional database. But first, run the scripts that Oracle supplies; to do this, log on to SQL*Plus as SYSTEM. You then need to create the tablespace that will house the new Discoverer 3.0 tables. In this example, the tablespace is called VIDEO, because you also will populate this tablespace with the business database. To create this tablespace, enter the following command in SQL*Plus:

 SQL> create tablespace video datafile `d:\orawin95\database\video.ora' size 20M; 

Here, you should change your datafile path to the path where your VIDEO database will reside. In a production system, you probably already will have a business database, so you will create this tablespace only to store the new Discoverer 3.0 end-user layer for the data. You really don't need 20MB, but you might want to be safe in case you want to play around with the data and press Discoverer to its limits!

Creating the OLAP Database

First, you need to create the new OLAP database. You need to run eulins.sql, which exists in the <oracle_home>\discvr30\demo\sql\eulins.sql directory. You can do this in Windows 95 by choosing ProgramsOracle Discoverer 3.0Install End User Layer Tables. You then are prompted for the following information:

• SYSTEM password: Just type it in! Don't worry ”there is no echo.
• Username: You will call the owner of the end-user layer TUTORIAL.
• Password: Just call this TUTORIAL.
• Tablespace name: This is the name of the tablespace you just created: VIDEO.
• Public end-user layer: Enter N for no, as instructed. This option really doesn't matter unless you need to practice granting business areas to other users, which are logical groupings of data that define a specific business function. If you are creating this tutorial for many users who want to learn the system, it is safer to enter Y for yes so that users will have instant access.

As the eulins.sql script runs, it creates the complete set of EUL$ tables, indexes, and sequences for the new multidimensional database.

Previous Table of Contents Next