Instructions for creating Global Widgets database ------------------------------------------------- Version 5 - February, 2010 This file contains full instructions for creating the Global Widgets database that is used in the Oracle Press book "Oracle Discoverer Handbook", and will be used in the "Become an expert in Oracle Discoverer Administration" series. 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: Adding the Foreign Keys Section 8: Contacting us Section 1: ERD Diagram ---------------------- An Entity-Relationship Diagram for this database can be found at: http://www.learndiscoverer.com/books/erd_model.htm The 12 tables that will be created are: 1 GS_Channel Sales Channel data 2 GS_City City data 3 GS_Customer Customer data 4 GS_Day Day data 5 GS_District District data 6 GS_Month Fiscal Month data 7 GS_Prodline Product line data 8 GS_Products Product data 9 GS-Quarter Fiscal Quarter data 10 GS_Region Region data 11 GS_Sales Sales fact data 12 GS_Year Fiscal Year data Section 2: Explanation of Directory structure --------------------------------------------- Having downloaded and unzipped the file DataDefs.zip from our website at: http://ascbi.com/downloads/schema you should see a directory called datadefs. When you open the directory called datadefs you will see the following 3 sub-directories: Sub-Directory 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. 1 create.sql A SQL script that calls the other files in turn 2 gs_channel.sql A SQL script to create the GS_CHANNEL table 3 gs_city.sql A SQL script to create the GS_CITY table 4 gs_customer.sql A SQL script to create the GS_CUSTOMER table 5 gs_day.sql A SQL script to create the GS_DAY table 6 gs_district.sql A SQL script to create the GS_DISTRICT table 7 gs_month.sql A SQL script to create the GS_MONTH table 8 gs_prodline.sql A SQL script to create the GS_PRODLINE table 9 gs_products.sql A SQL script to create the GS_PRODUCTS table 10 gs_quarter.sql A SQL script to create the GS_QUARTER table 11 gs_region.sql A SQL script to create the GS_REGION table 12 gs_sales.sql A SQL script to create the GS_SALES fact table 13 gs_year.sql A SQL script to create the GS_YEAR table In the ForeignKeys folder you will see one file: 1 fkeys.sql This SQL script creates all of the foreign keys In the LoadData folder you will see 25 files: 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 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 USERS 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; 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 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. Section 5: Creating the tables ------------------------------ 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. Just do a search and replace on the word USERS. It will be found 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: 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 or Win NT type CMD and click OK If using Win98 type COMMAND and click OK 4 Change to the folder containing the create.sql script Eg... 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 E.g. SQLPLUS oot_sch/oot_sch@iasdb 6 When SQL runs, 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 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: 1 From the Windows Start button select START | RUN 2 If using Win2000 or Win NT type CMD and click OK If using Win98 type COMMAND and click OK 3 Change to the folder containing the batch file you want to run Eg... cd\datadefs\loaddata 4 At the command prompt type LOAD and press ENTER The file will now run and upload all of the data into the database 5 To close the DOS window, type EXIT and press ENTER 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_regions 4 gs_sales 2484 gs_year 4 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: 1 From the Windows Start button select START | RUN 2 If using Win2000 or Win NT type CMD and click OK If using Win98 type COMMAND and click OK 3 Change to the folder containing the fkeys.sql script Eg... cd\datadefs\foreignkeys 4 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 E.g. SQLPLUS oot_sch/oot_sch@iasdb 5 When SQL runs, 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 of the 10 foreign keys: Table Foreign Key Joins to gs_sales gs_channel_fk gs_channel gs_sales gs_customer_fk gs_customer gs_sales gs_product_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 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 January 2010