Introduction

The steps provided here are an indication of the normal process required to install and configure WhereScape RED. In some environments, additional processes will be required.

1. Install the Oracle 10g, 11g or higher database client software on a suitable PC.
2. Install the WhereScape RED software onto your PC. Refer to Installing RED for Oracle for details.
3. Add your license key. Refer to Adding your License for details.
4. Check your Windows version. Refer to Checking your Windows Version for details.
5. Check your ODBC Drivers. Refer to Checking your ODBC Drivers for details.
6. Designate a database that is to be used as the data warehouse database. For testing or evaluation purposes this may be a PC based database. WhereScape RED requires the Oracle database version 10.1 or greater as the data warehouse database. Record the SID for this database. Create the database user (schema) that is to be used to hold the metadata and data warehouse tables. Allocate the privileges required by WhereScape RED. Refer to Creating an Oracle DSS User for details.
7. Establish the Oracle Net connections required to allow access to the data warehouse database from the PC. Record the  Server  name for the connection.
8. Add the definition of an ODBC data source to connect to the data warehouse database. Refer to Configuring an Oracle Metadata Connection (DSN) for details.
9. Create the WhereScape metadata repository under the data warehouse schema that you created. Refer to Creating a Metadata Repository for Oracle for details.
10. Install a Windows Scheduler. Refer to Installing a Windows Scheduler for Oracle for details.
11. Install a Unix Scheduler. Refer to Installing a Unix Scheduler for Oracle for details.
12. If required, load the WhereScape tutorial tables into a separate database. It is highly recommended that the tutorials be undertaken. Refer to Creating Tutorial Data for details.

Problem Solving

If problems occur in your install that cannot be resolved proceed as follows when running WhereScape's Setup Administrator utility:

1. Create a log file by selecting  Tools > Start Logging  from the menu bar. Enter a file name.
2. Step through at least:
   • Computer Setup > Check Windows version.
   • Computer Setup > Check ODBC drivers.
   • Computer Setup > Validate/Add Odbc source.
   • Oracle> Validate Oracle user privileges (if an Oracle DataWarehouse).
   • Create/Validate Meta Repository > Validate meta repository.
3. Select  Tools > Stop Logging  to close off the log
4. Edit the log and remove any passwords that appear.
5. Send your log file to support@wherescape.com.

Installing RED for Oracle

Installation Prerequisites
WhereScape RED requires certain client and server components.
Client Prerequisites

• Windows 10 and Windows 11.
• 2GB available disk space.
• ODBC Version 3.0 (normally shipped as part of the Operating System).
• For building a data warehouse, a version 11.2 database client or greater is required. A 32-bit or 64-bit Oracle client installation will be necessary (64-bit recommended).

Server Prerequisites

• One of the following data warehouse database platforms is required: Oracle version 11.2 or higher.
• To use the WhereScape RED Scheduler, a UNIX, Linux or Windows based server is also required (this may be the database server). WhereScape RED only supports sh and ksh under UNIX and bash under Linux.

Installation of WhereScape RED
To install WhereScape RED, refer to Installing WhereScape RED for details.

Creating an Oracle User

The creation of the Oracle database user that will be used to administer and maintain the data warehouse must be performed outside this utility. The SQL Admin utility can be used to execute the sql required to create such a user, or the database administrator should be contacted.
A list of the required privileges is displayed when this option is selected. Many of these privileges must be allocated as specific privileges and cannot be granted as part of a role. For example, if a 'grant dba to dssuser' command is executed WhereScape RED will still need these privileges specifically granted as 'dba' is a roll. 

For our data warehouse, or decision support system, we will create a user, 'dssdemo'. An example sql script to create this 'dssdemo' user is shipped and can be found in the WhereScape RED program directory:

• ..\OracleRecreate_dssdemo.sql

1. The dss user created is dssdemo in this case:

drop user dssdemo cascade;
create user dssdemo identified by wsl;
alter user dssdemo default tablespace DEMODATA;
alter user dssdemo temporary tablespace TEMP;
 
grant create session to dssdemo;
grant resource to dssdemo;
grant create table to dssdemo;
grant create view to dssdemo;
grant create sequence to dssdemo;
grant create procedure to dssdemo;
grant select any table to dssdemo;
grant create materialized view to dssdemo;
grant create database link to dssdemo;
grant query rewrite to dssdemo;
grant select on sys.v_$database to dssdemo;
grant alter session to dssdemo;
grant select on sys.v_$session to dssdemo;
grant execute on sys.dbms_lock to dssdemo;
grant execute on sys.dbms_sql to dssdemo;
grant create any view to dssdemo;
grant unlimited tablespace to dssdemo;
grant alter system to dssdemo;
grant select_catalog_role to dssdemo;

2. To use object placement  across multiple schemas  in WhereScape RED, the RED user also needs to be granted the following privileges:

grant select any table to dssdemo;
grant create any view to dssdemo;
grant drop any view to dssdemo;
grant create any table to dssdemo; 
grant drop any table to dssdemo; 
grant delete any table to dssdemo; 
grant insert any table to dssdemo; 
grant update any table to dssdemo; 
grant alter any table to dssdemo; 
grant global query rewrite to dssdemo; 
grant create any materialized view to dssdemo; 
grant drop any materialized view to dssdemo; 
grant alter any materialized view to dssdemo; 
grant create any index to dssdemo; 
grant drop any index to dssdemo; 
grant alter any index to dssdemo; 
grant select any sequence to dssdemo; 
grant create any sequence to dssdemo; 
grant drop any sequence to dssdemo; 
grant analyze any to dssdemo; 
grant comment any table to dssdemo;
 
grant select_catalog_role to dssdemo;

3. To do loads from  Hadoop  using multiple schemas, the RED user also needs to be granted the following privilege:

grant select_catalog_role to dssdemo;

4. To use the Data Vault 2 templates, the RED user also needs to be granted the following privilege:

grant execute on dbms_crypto to dssdemo;

Creating Oracle Individual Users

Oracle users that require each developer to log into Oracle (using their own username and password), can use the  Oracle Individual User Logon  Method.
The Oracle Individual User option allows a user, with the appropriate permissions, to log into WhereScape RED and then operate as the RED schema.
This method enables customers to configure RED ensuring that users logged in via this method cannot edit the metadata user password in the connections, and ensures that the password is encrypted in  Tools > Options.
To authorize users to log in to RED using the Oracle Individual User method, a DBA needs to grant users select on the  ws_dbc_connect  table, as well as the  CREATE SESSION  system privilege.
This method is also useful for tracking individual WhereScape RED user activity in the Oracle database. Their authenticating user name is now stored in the CLIENT_IDENTIFIER field of v$session which can be used by Oracle's auditing, functioning to track individual developer activity.
Oracle individual users will need to be created and granted certain privileges. Alternatively, for existing Oracle database users, they just need to be granted the following privileges below:

grant create session to oracle_user1 grant select on dssdemo.ws_dbc_connect to oracle_user1

1. Once logged in to RED, the metadata user needs to do the following in  Tools > Options > Repository Privacy settings:
   • Check the option to enable the  Mask Extract User Password.
   • Deselect the option to deactivate the  Extract User Password Editing  (this will lock down the metadata user password in the connections).
   • Check the option to enable the  Mask Admin User Password.
   • Deselect the option to deactivate the  Enable Admin User Password Editing  (this will lock down the Admin/Dss User Password in the connection properties).

2. Multiple users can then log in to RED using their own credentials by:

• • Selecting the  Oracle Individual User  option from the  Logon Method  drop-down menu.
  • Entering their user name and password.
  • Entering the RED schema.

3. The  Repository Privacy Settings  is completely unavailable for users that log in to RED via the Oracle Individual User method.

grant select on sys.v_$sql to dssdemo; 
grant select on sys.v_$sess_io to dssdemo; 
grant select on sys.v_$locked_object to dssdemo; 
grant select on sys.v_$lock to dssdemo;

Configuring an Oracle Metadata Connection (DSN)

WhereScape RED supports the  Oracle  ODBC driver, follow the instructions below for setting up this driver.
To begin adding an ODBC Source from WhereScape's Setup Administrator, refer to Creating an ODBC Connection for details.
Setting up an Oracle ODBC Driver

1. In  ODBC Data Source Administrator, click the  System DSN  tab to view the system wide ODBC entries.

• • Click the 'Add...' button to add a new entry.

2. Select the  Oracle ODBC  driver from the driver list.

• • Click  Finish.

3. Enter the  ODBC Data Source Name  (DSN) details in the  Oracle ODBC Driver Configuration  screen.

4. To test the connection, click  Test Connect, otherwise click  OK.

Validating Oracle User Privileges

1. To validate the Oracle user privileges, click the  Oracle  tab and then click  Validate Oracle User Privileges.

This option tests that an ODBC connection can be made to the Oracle database running the data warehouse.
The diagram below shows some of the layers involved in making up this type of connection.
 
If this connection fails, then we need to isolate where the failure is occurring. The Oracle documentation can aid in resolving the problem. Starting from the bottom and working back up the layers we can perform the following tests:

1. Log on to the host running the database, and check that a direct connection to the database works via a tool, such as sqlplus. This will test that the database is active and receiving connections.
2. Issue a Tnsping from a dos shell on the PC (e.g. tnsping ORCL) where ORCL is the tnsnames connection entry for the data warehouse database. This will check the Oracle network layer. If the Tnsping responds with an  OK, then the database server is visible through the network layer.
3. Connect to the data warehouse database using sqlplus from the PC. Again, this test checks the network layer and a small part of the Oracle client software.
4. Attempt an ODBC connection using the Oracle supplied ODBC tool (normally called  Oracle ODBC Test  and located within the Oracle program menu). This tool is normally loaded as part of the Oracle client. It provides an independent test of the ODBC, Oracle client and network layers. Initially attempt to connect using the Oracle supplied ODBC driver. This will confirm that all layers apart from the ODBC driver are working correctly. If a failure occurs consult the Oracle documentation.

Perform the same test as step (4) but this time using the Microsoft^®  ODBC for Oracle driver.

If you are still unable to connect, record the error message as displayed on connection failure and contact support@wherescape.com.
The following example shows the error message "2800 (Microsoft^®)(ODBC driver for Oracle)ORA_01017: invalid username/password; logon denied".

Creating a Metadata Repository for Oracle

1. In  WhereScape's Setup Administrator, click the  Metadata Repository  tab and then click  Create Metadata Repository.

2. This option creates the WhereScape meta tables, sequences and procedures in the specified schema. It checks to ensure that the schema, database or user does not contain an existing metadata repository before starting the metadata install.

• • Click  Create Metadata Repository.
  • Enter the  ODBC Connect,  User Name  and  Password  and then click  OK.

3. When creating an Oracle metadata repository, an additional window is displayed requesting  Tablespace  information.

• • Select the required Tablespaces and then click  OK.
  • If Tablespaces are not specified, the user's default Tablespace will be used.

4. Depending on the type of license you have, the following dialog may be displayed:

• • If it is displayed, select the type of repository required and then click  OK.

5. The procedure compile dialog is displayed:

• • To force the compilation under the Oracle 8i standard, select the check-box on the right bottom corner of the  Procedure Compile  window. Otherwise, click  OK.

6. If a procedure fails to compile, then scroll to the right in the right pane to see if any of the returned error codes help in ascertaining what the problem is.

For  Oracle  common causes of procedure compilation failures are:

• • Running an Oracle version prior to 11.2.
  • Insufficient room in the SYSTEM table space.
  • Insufficient privileges. (See notes below).
  • Memory parameters set too small in the Oracle instance.

Insufficient privileges on procedure compile (Oracle)

Compiles of the WhereScape procedures may fail where the required privileges have not been granted. The bulk of the WhereScape procedures are used by the scheduler, so they can be left uncompiled, if the scheduler is not to be used. The following procedures however must successfully compile for WhereScape RED to function correctly:
Ws_Procedure_Save, Ws_Procedure_Compile, WsWrkAudit, WsWrkError, WsParameterRead and WsParameterWrite
If one or more procedures fail to compile then use the  Validate Metadata Repository  option to compile the procedures, once the problem has been rectified.