We are Business Intelligence, and Discoverer experts. You'll wish you'd found us sooner.

Global Widgets Database Instructions


Version 5 - February, 2010

Introduction

This page contains full instructions for creating the Global Widgets database that is used in the Oracle Press book " Oracle Discoverer Handbook". This handbook was written to teach Discoverer 3.1 but is also applicable to Discoverer 4.1. The principle tool used is Desktop.

These instructions are broken into the following eight sections:

Section 1: ERD Diagram
Section 2: Explanation of Directory structure
Section 3: Schema creation script
Section 4: Edit the LOAD.BAT
Section 5: Creating the tables
Section 6: Loading the data
Section 7: Creating the Foreign Keys
Section 8: Contacting us

Section 1: ERD Diagram



An Entity-Relationship Diagram for this database can be found here.

The 12 tables that will be created are:

Table Description
gs_channel Sales Channel data
gs_city City data
gs_customer Customer data
gs_day Fiscal Day data
gs_district District data
gs_month Fiscal Month  data
gs_prodline Product Line data
gs_products Product data
gs_quarter Fiscal Quarter data
gs_region Region data
gs_sales Sales Fact data
gs_year Fiscal Year data


Top of Page

Section 2: Explanation of Directory structure


Download and unzip the file called DataDefs.zip from our website at http://ascbi.com/downloads/schema. After it is unzipped you should see a folder called datadefs.

When you open this folder you will see the following 3 sub-folders:

Sub-Folder Description
CreateTables Contains a SQL script that creates the 12 tables
ForeignKeys Contains a SQL script that creates all foreign keys
LoadData Contains a DOS Batch file that loads the data into the tables


In the CreateTables folder you will see 13 files.

File File Name Description
1 create.sql The master script that calls the other scripts in turn
2 gs_channel.sql Script creates GS_CHANNEL table
3 gs_city.sql Script creates GS_CITY table
4 gs_customer.sql Script creates GS_CUSTOMER table
5 gs_day.sql Script creates GS_DAY table
6 gs_district.sql Script creates GS_DISTRICT table
7 gs_month.sql Script creates GS_MONTH table
8 gs_prodline.sql Script creates GS_PRODLINE table
9 gs_products.sql Script creates GS_PRODUCTS table
10 gs_quarter.sql Script creates GS_QUARTER table
11 gs_region.sql Script creates GS_REGION table
12 gs_sales.sql Script creates GS_SALES table
13 gs_year.sql Script creates GS_YEAR table

In the ForeignKeys folder you will see one file:

File File Name Description
1 fkeys.sql This script creates all of the foreign keys


In the LoadData folder you will see 25 files:

File      File Name Description
1 load.sql A DOS Batch file that loads the data
2-13 load1.ctl to load12.ctl Control files for loading the data
14-25 load1.dat to load12.dat The data that is to be loaded

Top of Page

Section 3: Schema creation script

If you are new to databases and wish to create the same user OOT_SCH on your database that we used on ours, and we recommend that you do so if you can, you must log on to your database as the SYSTEM user (default password MANAGER) and then run the following SQL script:

create user OOT_SCH identified by OOT_SCH
default tablespace TOOLS
temporary tablespace TEMP
quota unlimited on TOOLS;
grant connect, resource, execute any procedure, select any table to OOT_SCH;
grant execute on DBMS_JOB to OOT_SCH;
grant create procedure, create table, create view to OOT_SCH;

In addition, if you want to assign DBA privileges you can do this using this grant:

grant DBA to OOT_SCH;

Note: If you do not wish to use the OOT_SCH, create or ask your DBA to create a user for you. Please make sure that the user has the following required privileges:

connect
resource
execute any procedure
select any table
execute on DBMS_JOB
create procedure
create table
create view

Top of Page

Section 4: Edit the LOAD.BAT

The database used in the book was called IASDB and the schema is owned by the user account OOT_SCH using a password of OOT_SCH. The batch file LOAD.BAT makes reference to this database and schema. If you are not using the OOT_SCH schema you must edit the LOAD.BAT file contained in the LoadData folder and replace the schema name with the name of a user that you have created on your database.

In the batch file you should also check the password and your database SID. At the very least, you will probably want to change the database name to match the name (SID) of the database you want to load the data in to.

Each line of the batch file looks like this:

    sqlldr userid=" oot_sch/oot_sch@iasdb", control=load.ctl, data=load.dat, log=load.log

You only need to edit the userid clause. This clause logs on to the database by passing a username and password, separated by a forward slash. For example, to change the above login to a user called WAREHOUSE with a password of SMITH on the database called GLOBAL, use the following:

sqlldr userid=" warehouse/smith@global", control=load.ctl, data=load.dat, log=load.log


Note: do not change any of the control, data or log clauses. Having made the necessary changes to LOAD.BAT you are ready to start.
 

Top of Page

Section 5: Creating the tables

All of the table creation scripts refer to a tablespace called USERS. This is the default tablespace as defined in the schema creation script in section 3.

If you do not want to use the USERS tablespace you must edit the 12 SQL files that begin with GS_ in the CreateTables folder and replace the tablespace name USERS with the name of a tablespace that you have created on your database. A simple search and replace on the word USERS will do the trick. You will need to change it two or three times in each script.

As an example, here is the script that creates the channel table:

CREATE TABLE GS_CHANNEL (
CHANNELID NUMBER(2),
NAME VARCHAR2(8 BYTE))
TABLESPACE USERS
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
LOGGING
NOCACHE
NOPARALLEL;


ALTER TABLE GS_CHANNEL ADD (
CONSTRAINT GS_CHANNE_PK PRIMARY KEY (CHANNELID)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1));

ALTER TABLE GS_CHANNEL ADD (
CONSTRAINT GS_CHANNEL_UK UNIQUE (NAME)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1));

To create the tables, follow this workflow:

Step

Task

1 Make sure you have created a user and know the password
2 From the Windows Start button select START | RUN
3 If using Win2000, Win NT, XP or Vista: type CMD and click OK
If using Win98: type COMMAND and click OK
4 Change to the folder containing the create.sql script
E.g.  cd\datadefs\createtables
5 At the command prompt, type sqlplus username/password@database and then press ENTER, where:
       username is the user you have created,
       password is the password you assigned, and
       database is the database SID you are using
6 Having logged in, at the SQL prompt, type @CREATE and press ENTER
7 The 12 tables will now be created
8 When done, type EXIT and press ENTER to leave SQL
9 Type EXIT and press ENTER to close the DOS window


Note: the above script refers to a schema called oot_sch. All of the tables use the same schema. If you do not want to use this schema you must edit the 12 SQL files and replace the schema name with the name of a user that you have created on your database.

Top of Page

Section 6: Loading the data

Having created the 12 tables, you are now ready to load the data. This is done by running the batch file LOAD.BAT that we have provided for you and you edited in section 4.

To load the data, follow this workflow:

Step Task
1 From the Windows Start button select START | RUN
2 If using Win2000, NT, XP or Vista: type CMD and click OK
If using Win98: type COMMAND and click OK
3 Change to the folder containing the load.bat batch file that we want to run
E.g.  cd\datadefs\loaddata
4 At the command prompt, type LOAD and then press ENTER
The file will now run and upload all of the data into the database
5 When done, type EXIT and press ENTER to close the DOS window


To help you verify that the correct amount of data has been loaded into each table, here is a count of how many rows should have been loaded:


Table Count
gs_channel 2
gs_city 23
gs_customer 35
gs_day 1463
gs_district 7
gs_month 48
gs_prodline 3
gs_products 18
gs_quarter 16
gs_region 4
gs_sales 2484
gs_year 4

Top of Page

Section 7: Creating the Foreign Keys

Having loaded the data into the 12 tables, the last step is to create the foreign keys.
 

To create the foreign keys, follow this workflow:

Step Task
1 From the Windows Start button select START | RUN
2 If using Win2000, Win NT, XP or Vista: type CMD and click OK
If using Win98 type COMMAND and click OK
3 Change to the folder containing the fkeys.sql script
E.g.  cd\datadefs\foreignkeys
4 At the command prompt, type sqlplus username/password@database and press ENTER, where
        username is the user you have created,
        password is the password you assigned, and
        database is the database SID you are using
5 Having logged in, at the SQL prompt, type @FKEYS and press ENTER
6 The required foreign keys will now be created
7 When done, type EXIT and press ENTER to leave SQL
8 Type EXIT and press ENTER to close the DOS window


To help you verify that the correct number of foreign keys have been created, here is a list:


Table Foreign Key Joins to
gs_sales gs_channel_fk gs_channel
gs_sales gs_customer_fk gs_customer
gs_sales gs_products_fk gs_products
gs_products gs_prodline_fk gs_prodline
gs_day gs_month_fk gs_month
gs_month gs_quarter_fk gs_quarter
gs_quarter gs_year_fk gs_year
gs_customer gs_city_fk gs_city
gs_city gs_district_fk gs_district
gs_district gs_region_fk gs_region

Top of Page

Section 8: Contacting us


We hope you have fun working with our database. If you have any problems with these instructions or need to contact us, please do so through our website at the following address:


     http://ascbi.com/contact/

or send an email to info@ascbi.com

Best wishes, Michael
February 2010)

 

Home                       Top of Page

 


 

 

Armstrong-Smith Consulting


Copyright 2002-2009 Armstrong-Smith Consulting

You are visitor number