Deploying the Rational Insight Warehouse on an Oracle Database

Content

1. Creating the database

1.1. Database character set

The Rational Insight warehouse supports storing data from different languages. The Oracle database that the Rational Insight schemas and objects will reside in must have the 'character set' set to AL32UTF8 and the 'national character set' set to UTF-8. Therefore, if you must create the database outside of the automated or manual process already provided by Rational Insight make sure it is a database that has the above settings prior to deploying and creating the schemas and objects.

2. Table spaces

2.1. Operational data store

There are two table spaces allocated to the operational data store. One for the regular tables called VNF_32K and the other for the indexes called VNF_IDX. The script that creates the table spaces for Rational Insight on Oracle is called tbspace.sql and is located in the ddl folder in the [rational_insight_installation_directory]/datawarehouse/dw installation directory. It is a parameterized script, where the system account password, fully qualified path for the location of the table space files and ORACLE_SID are passed as arguments to the script. If you need to follow some other naming convention for the table space names, keep in mind that you will also need to change the DDL scripts that reference these two table spaces. The scripts that reference the table spaces are located in the ddl folder in the [rational_insight_installation_directory]/datawarehouse/dw directory. These scripts are:
· createUsers.sql
· createSTGCFG.ddl
· createNF.ddl


2.2. Data mart

There are two table spaces allocated to the data mart. One for the regular tables called VSTR_32K and the other for the indexes called VSTR_IDX. The script that creates the table spaces for Rational Insight on Oracle is called tbspace.sql and is located in the ddl folder in the [rational_insight_installation_directory]/datawarehouse/dw installation directory. It is a parameterized script, where the system account password, fully qualified path for the location of the table space files and ORACLE_SID are passed as arguments to the script. If you need to follow some other naming convention for the table space names, keep in mind that you will also need to change the DDL scripts that make reference to these two table spaces. The scripts that reference the tables spaces are located in the ddl folder in the [rational_insight_installation_directory]/datawarehouse/dw directory. These scripts are:
· createUsers.sql
· createSTAR.ddl

Additionally, there is another temporary table space created called RIS_TEMP that is common to both the ODS and DM and is the default temporary space for the Rational Insight Oracle user accounts.

The parameters specified in tbspace.sql are:

· System account password – parameter->sysPassword
· Base directory for location of the data files – parameter -> DATA_HOME
· Oracle SID of the Rational Insight database – parameter -> ORACLE_SID

3. User accounts

3.1. Accounts

The number of Oracle accounts that are created and required by the Rational Insight database are five. However, only two are created as active accounts, the other three are inactive expired accounts used only to qualify and demarcate the three physical areas in the database. The script used to create the database user accounts is located in the ddl folder in the data warehouse installation directory and is called createUsers.sq. Any default roles and database privileges are granted to the users in this script. It is a parameterized script where the following are arguments to the script:
· System account password – parameter->sysPassword
· DBA account (active account with DBA role granted) –
parameter->DBA_USER
· DBA account password – parameter->DBA_PASSWORD
· Operational data store account (dormant account used only to group and qualify objects) – parameter-> NF_SCHEMA, default -> RIODS
· Data mart account (dormant account used only to group and qualify objects) – parameter -> STAR_SCHEMA, default -> RIDW
· Report user account (active account limited to select privileges against the data mart tables and views) – parameter->REP_USER, default -> RPTUSER
· Report user password – parameter -> REP_PASSWORD, default ->Rat10nal

The fifth account created is the configuration account called CONFIG which is not parameterized and is a dormant account used only to group and qualify objects. Objects created under this schema are used in the administrative and control of database objects and extract, transform and load audit/processing. This account name should not be changed.

For the items in the list above where a default is specified, these are the defaults assumed with an automated installation and these are the defaults assumed in the report metadata (Framework Manager model, Cognos BI reports). Unless otherwise constrained by an IT process, use these defaults, otherwise additional changes will be required down the line in the DDL scripts, framework model and report packages. For consumers of Rational Common Reporting (RCR), these defaults (with the exception of the report user password) must be used and no deviation should be allowed since the reporting solution will otherwise not work.

4. Schemas

From the list of accounts specified in sec 3.1, only three have database objects related to them hence three schemas. Those schemas and associated accounts are:
· Configuration schema: CONFIG account
· Operational data store schema: Default RIODS account
· Data mart schema: Default RIDW account

4.1. Configuration schema: CONFIG

The objects associated with this schema can be found in the createSTGCFG.ddl script located in the ddl folder under the [rational_insight_installation_directory]/datawarehouse/dw directory. Additionally, some of the tables in this schema need to be initialized. The initialization records are found in a separate script called initCONFIG.sql located in the scripts folder under the [rational_insight_installation_directory]/datawarehouse/dw directory. These scripts are not parameterized. If the table space names are not those provided by default in the installation package (sec. 2.1 ), then additional modification of this script is required to replace any reference to VNF_IDX and VNF_32K with the corresponding table space names.

4.2. Operational data store schema: RIODS

The objects associated with this schema can be found in the createNF.ddl script in the ddl folder under the [rational_insight_installation_directory]/datawarehouse/dw directory. Additionally, the tables in this schema need to be initialized. The initialization records are found in a separate script called initNF.sql located in the scripts folder under the [rational_insight_installation_directory]/datawarehouse/dw directory. Both these scripts are parameterized. Only one argument is required, that of the operational data store schema name. The parameter in the scripts is called NF_SCHEMA which by default should be set to RIODS (sec. 3.1 ). If the table space names are not those provided by default in the installation package (sec. 2.1 ), then additional modification of this script is required to replace any reference to VNF_IDX and VNF_32K with the corresponding table space names in the above scripts.

4.3. Data mart schema: RIDW

The objects associated with this schema can be found in the createSTAR.ddl, createSTARViews.ddl script in the ddl folder under the [rational_insight_installation_directory]/datawarehouse/dw directory. Additionally, the tables in this schema need to be initialized. The initialization records are found in a separate script called initSTAR.sql located in the scripts folder under the [rational_insight_installation_directory]/datawarehouse/dw directory. All these scripts are parameterized. Only one argument is required for createSTAR.ddl, createSTARViews.ddl and initSTAR.sql that of the data mart schema name. The parameter in the scripts is called STAR_SCHEMA which by default should be set to RIDW (sec. 3.1 ). The createSTARViews.ddl scripts requires two parameters that of the schema name used for the data mart and the schema name used for the operational data store. Those parameters are represented by STAR_SCHEMA which should be set to RIDW and NF_SCHEMA which should be set to RIODS (sec. 3.1 ). If the table space names are not those provided by default in the installation package (sec. 2.1 ), then additional modification of this script is required to replace any reference to VSTR_IDX and VSTR_32K with the corresponding table space names in the above scripts.

The data mart schema has additional objects, a package, procedures and functions. One script is used to grant privileges to the report user that is used to connect and execute queries against the database on behalf of the report execution. The package is used to store constants based on a supported language which is used by the procedures and functions. The procedures aid in populating the date dimension in the data mart and ETL processing.

The Oracle language packages are located in scripts/ascii (windows) and scripts/utf_8 (unix) folders under the [rational_insight_installation_directory]/datawarehouse/dw directory. Only one language will apply to your environment. All of the package scripts are parameterized, they contain only one parameter represented by STAR_SCHEMA which should be set to RIDW by default (sec. 3.1 ).

The procedures and functions that aid in populating the date dimension in the data mart and aid in the ETL processing, are in the dtPopulate.sql script in the scripts folder under the [rational_insight_installation_directory]/datawarehouse/dw directory. The script is parameterized, it contains only one parameter represented by STAR_SCHEMA which should be set to RIDW by default (sec. 3.1 ).

The script that grants SELECT privileges to the report user account (sec. 3.1 ) for the data mart schema RIDW (sec. 3.1 ) tables and views is called star_grants.sql and is located in the scripts folder under the [rational_insight_installation_directory]/datawarehouse/dw directory. It is a parameterized script that accepts two arguments, the data mart schema name represented by STAR_SCHEMA which should be set by default to RIDW (sec. 3.1 ) and the report account user name represented by REP_USER (sec. 3.1 ).

5. Oracle account for ETL execution

For simplicity, no additional or separate account is created for the ETL execution. If the account with DBA role is not allowed to execute the ETL, then another account needs to be created that will have authority to do the following:

· Insert, update, select, delete, truncate from all the tables and views in the data mart schema RIDW, the operational data store schema RIODS and the configuration schema CONFIG (sec. 3.1 ,4 )
· Privileges on the table spaces with no space quotas (sec. 2 )
· Privileges to execute procedures, function and access packages in the data mart schema RIDW (sec. 3.1 ,4.3 )

6. Bringing it all together

The scripts mentioned in section 2 to 5 are all set up for execution in one script called buildDW.sql located in the [rational_insight_installation_directory]/datawarehouse/dw. If the Oracle database is already set up to host the Rational Insight schemas, then this script could be used to deploy the schemas even on a remote Oracle database (append the connect command with @&&ORACLE_SID for remote execution in the script). The script expects to work from a directory called work_sp. Create this directory under [rational_insight_installation_directory]/datawarehouse/dw and copy the ddl scripts folder and the buildDW.sql file located in [rational_insight_installation_directory]/datawarehouse/dw directory into the newly created work_spc folder.

6.1. Preparing the execution of buildw.sql

Any portion of the steps mentioned in sections 2 through 5 that have been executed manually outside the scope of an automated or manual Rational Insight installation, must have the corresponding script references in buildDW.sql commented out prior to executing buildDW.sql. For example, if the table spaces are created outside the scope of the buildDW.sql script, then they would need to comment the line @'&&CD/work_spc/ddl/tbspace.sql' in buildDW.sql by prepending it with two dashes:
--@'&&CD/work_spc/ddl/tbspace.sql'

If we look at the contents of buildDW.sql, we see at the top, the list of arguments and parameters that set up the values for the script executions that follow. In total there are sixteen parameters that are expected. They are:

· ORACLE_HOME: Oracle installation directory
· ORACLE_SID: Oracle SID of the Rational Insight Oracle database
· CD: contains the value of the current directory, the context in which all the scripts are located and from where they will execute
· sysPassword: The password of the system account
· ORACLE_BASE: The Oracle base installation directory
· DBA_USER: The Oracle account with the DBA role assigned (sec. 3.1 )
· DBA_PASSWORD: The password for the DBA account
· NF_SCHEMA: The value of the operational data store account name the default RIODS should be specified (sec. 3.1 )
· STAR_SCHEMA: The value of the data mart account name, the default RIDW should be specified (sec. 3.1 )
· REP_USER: The report user account to use to execute the queries on behalf of the reports (sec. 3.1 )
· ORA_CMD: Set to either ON or OFF for the echoing of the commands as the scripts execute
· START_YEAR: The minimum year number for which the date dimension should contain data
· END_YEAR: The maximum year number for which the date dimension should contain data
· REP_PASSWD: The password of the report user account (sec. 3.1 )
· DATA_HOME: The base directory for the table space data files (sec. 2 )
· SQLVARF: Points to one of the language scripts for the package required by the data mart schema RIDW (sec. 4.3 )

6.2. Executing buidDW.sql

Knowing what the parameters are and their meaning, you can execute buildDW.sql by invoking the script and passing the sixteen arguments to the script as does install.bat and install.sh batch and shell script in the [rational_insight_installation_directory]/datawarehouse/dw directory:

SET ORACLE_SID=RIDW (windows)
export ORACLE_SID=RIDW (unix)
sqlplus /nolog @[rational_insight_installation_directory]/datawarehouse/dw /work_spc/buildDW.sql "param 1" "param 2" "param 3" ... "param 16"

Your other option is to modify the copy of [rational_insight_installation_directory]/datawarehouse/dw /work_spc/buildDW.sql and to set the values accordingly, here is an example of the pre-set information in the buildDW.sql script:

DEFINE ORACLE_HOME=’ C:/oracle11g/product/11.1.0/db_1 '
DEFINE ORACLE_SID='RIDW’
DEFINE CD='C:/Program Files/IBM/RationalInsight/datawarehouse/dw'
DEFINE sysPassword='somepassword'
DEFINE ORACLE_BASE=' C:/oracle11g '
DEFINE DBA_USER='RIDBA'
DEFINE DBA_PASSWORD='dbapassword'
DEFINE NF_SCHEMA='RIODS'
DEFINE STAR_SCHEMA='RIDW'
DEFINE REP_USER='RPTUSER'
DEFINE ORA_CMD='ON'
DEFINE START_YEAR=2005
DEFINE END_YEAR=2012
DEFINE REP_PASSWD='Rat10nal'
DEFINE DATA_HOME='c:/tbs/datafiles'
DEFINE SQLVARF='ascii/vars_en.sql'

Invoking the buildDW.sql script to create the objects is simplified:

SET ORACLE_SID=RIDW (windows)
export ORACLE_SID=RIDW (unix)
sqlplus /nolog @[rational_insight_installation_directory]/datawarehouse/dw /work_spc/buildDW.sql

7. Other considerations

Rational Insight requires four different logical databases with the same character set:

- Rational Insight Warehouse
- Rational Insight Sample Warehouse (optional, not available for RCR)
- Cognos Content Store
- Cognos Data Manager Catalog (not available for RCR)

You can deploy (though not recommended) any of the combinations above into different schemas in the same Oracle database. However, you can not deploy the Rational Insight warehouse and the Rational Insight sample warehouse on the same Oracle database. For any other combination above, make sure that the owners of the objects do not overlap across logical databases. For example, the account/schema used as the owner and creator of the content store database objects should not be the same one used for the catalog.