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)
|
