1011-1013

Previous Table of Contents Next

Page 1011

Creating the Sample Database

Now you're ready to create your sample business database. If you were creating an actual production implementation of OLAP, this already would be completed or would be a separate, complex task. In this case, you have the dump file, video.dmp; therefore, your business will be video rentals. First you need to create the owner for your VIDEO business data. Log on to SQL*Plus and type the following:

 SQL> start <ORACLE_HOME>\discvr30\demo\sql\demoinst.sql 

For me, <ORACLE_HOME> is d:\orawin95, so I type the following:

 SQL> start d:\orawin95\discvr30\demo\sql\demoinst.sql 

You then are asked for the following information:

  • SYSTEM password: Just type it in!
  • Database connection: Just press Return unless you need to access a particular SQL*Net database. In this case you will type in the SQL*Net Database Identifier, which is referred to as db_name in the Oracle manuals. Get this from your DBA if you need to.
  • Password: Just enter VIDEO here, because the owner name also is VIDEO.
  • Tablespace name: You are going to use the tablespace you just created ”in this case, VIDEO.
  • Temporary tablespace: Enter the tablespace on which you want Oracle to perform its sorts. The default tablespace on most systems is TEMP. Do not use the SYSTEM tablespace.

At this point, you have created the OLAP database for Discoverer and the database for the business data. You need to fill the business database with data, though, so you need to export the business data from the file video.dmp. The path for this file follows :

 <ORACLE_HOME>\discvr30\demo\video.dmp 

Enter MS-DOS mode and type either of the following lines:

 cd oracle_home\discvr30\demo 

(Remember to fill in the value for oracle_home. In my case, this is d:\orawin95.)

To load our huge data warehouse, we will use Oracle's Import utility like this:

 imp video/video@<db_name> file=video.dmp charset=<charset> full=y 

If you are working on a standalone system, or you are not using multiple database servers when you connect, just skip the @<db_name> part of the command. You should log on here the same way you logged on to SQL*Plus.

Page 1012

If you receive an error, you need more information on Oracle's Import utility to solve the problem. Remember that all you are trying to do is import video.dmp into the user VIDEO's set of owned tables. Someone who knows nothing about Discoverer can help you.

You now have created your first multidimensional database. (I had some trouble getting through this, because the readme files and manuals had differences in the 3.07 limited production release that I was using.) When in doubt, it is safe to follow your common sense and then, if that fails, the readme file and not the hard copy manual.

Mapping Data From a Database into Discoverer:
The Load Wizard

When you create an end-user layer of data from one of your databases that you want to analyze with Discoverer, you need to map all database objects you are interested in to a business area. Because you have loaded the basic structures that will give the TUTORIAL user the capability to map data into an OLAP format, you just need to click the Discoverer 3.0 Administrative Edition icon to create your first business area.

A business area should be a clear set of data elements that define a type of problem you want to look at. It is a snapshot of an aspect of your business. This eliminates the noise and complexity of users needing to sift through tables to find the area of business that they need to describe.

After you bring up the product and log on as TUTORIAL, you are asked to perform one of the following tasks :

  • Create a new business area
  • Open an existing business area

Because you are just starting out, choose to create a new business area. You will be using the online dictionary ”the Oracle data dictionary ”as your repository of existing tables. Notice that there also is an option to use Designer 2000. If you were building a large data warehouse with your OLAP development, for example, you would choose this option if you were using Designer 2000 as your tool to logically and physically design the new data warehouse.

Instead, simply click Next to move to the next screen. This screen lists every user in the Oracle RDBMS instance onto which you are logged. Choose the table owner for the database you want to create a business area for. Simply scroll down the list by clicking the arrow until you reach the VIDEO user. Click this username.

Because the VIDEO owner actually owns the data import you just finished, choose the Tables owned by user option. You can choose from three types of table restrictions:

  • Public tables: The owner grants access to PUBLIC for these tables. This means that anyone can look at your OLAP tables.
  • Partially restricted: Only certain users have access to these tables.
  • Private: The tables are available only to the owner of the data.

Page 1013

Because you haven't specified any grants in your new VIDEO user account, choose the Private option.

TIP
You can use the box at the left of the screen to select tables that fit a certain pattern. This makes things quicker. If you want only tables with the prefix PROD_ before the table name, for example, you enter PROD_% to pick up only those tables. For the example here, you don't need to restrict tables based on any pattern, so just keep the wildcard operator % in the box.

Click Next again. You now see a screen that shows all the table names in the VIDEO database on the left and nothing on the right. The right side of the screen represents your empty multidimensional database and business area. Now you can move a table to the right of the screen just by clicking a table name on the left and dragging it to the right. Move the following four tables to the right of the screen:

 PRODUCT SALES_FACT STORE TIME 

By dragging the table names to the right, you have created four folders that make up your business area. Now that you have selected these four tables as your basis for the OLAP business area you are in, the four tables off to the right.

Click Next to move to the final screen. (You can tell that it is the final screen because the Next button is grayed out and doesn't perform any action if you click it.) You now are asked to specify a name for your business area. Enter Video Land or another favorite name.

Note that you also have the following options that you can enable or disable using radio buttons :

  • Capitalize names: Capitalizes only the first character of each folder. The folder TIME appears as Time, for example.
  • Replace underscores with spaces: Makes things more readable for the analyst. Your folder SALES_FACT appears as SALES FACT if you enable this checkbox.
  • Remove all column prefixes: Suppose that your STORE table has the word STORE before each column name. You can enable this option to remove that annoying naming convention, which might have been used only to help reduce complexities for back-end programmers.
  • Primary/foreign key constraints: Joins are mapped if columns are related by primary and foreign key constraints that are found in their create table statements or a subsequent alter table command.
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