IBM Tivoli Monitoring, Version 6.3 Fix Pack 2

Creating and populating the time dimensions tables

Preparing the Tivoli® Data Warehouse for Tivoli Common Reporting includes creating the IBM_TRAM dimensions, which are required for running the Cognos® reports and using the data models.

About this task

The following dimensions tables are created by this procedure:

IBM_TRAM Schema
TIME_DIMENSION table
MONTH_LOOKUP table
WEEKDAY_LOOKUP table
Other dimensions
Other dimensions conforming to the Tivoli Common Data Model, such as ComputerSystem, BusinessService, and SiteInfo.

You will need the database scripts included in the extracted reports package under the db_scripts directory.

If reports are distributed with an installer, the following manual procedures can be handled automatically by the report installer. See your agent-specific user's guide for information on automated TRAM creation.

When installing multiple report packages, the following steps need only be completed once. When installing multiple report packages the same TIME_DIMENSION tables are used. If you want to reset granularity or begin and end times, you can repeat the procedure.

Procedure

IBM® DB2®
1. Copy the database scripts (.db2 files) from the reports package to a location where they can be run against the Tivoli Data Warehouse. The scripts are in the db_scripts branch of the directory where the reports package was extracted to.
2. Log in as db2admin. Your user ID must have administrator access to create the IBM_TRAM schema.
3. Connect to the database that you want to create the dimension tables for. This is your Tivoli Data Warehouse.
```
db2 connect to WAREHOUS
```
4. If you have an older version of the database scripts already installed clean up the database:
```
db2 -tf clean.db2
```
5. Create the schema and tables:
```
db2 -tf create_schema_IBM_TRAM.db2
```
  After the command completes successfully, several tables are shown under IBM_TRAM: TIME_DIMENSION, MONTH_LOOKUP, WEEKDAY_LOOKUP, ComputerSystem, BusinessService, SiteInfo, and so on.
6. Create the stored procedure for generating the time dimension:
```
db2 -td@ -vf gen_time_dim_granularity_min.db2
```
7. To populate TIME_DIMENSION table, call the time dimension stored procedure with dates and granularity to generate the timestamps. You can generate up to five years at a time or have the data regenerated every day.
```
db2 "call IBM_TRAM.CREATE_TIME_DIMENSION('start_date',
'end_date', granularity_of_data)"
```
  where start_date and end_date are in this format YYYY-MM-DD-HH.MM.SS.MILSEC and granularity_of_data is the frequency in minutes. For example, the following command extracts data from 1/1/2010 to 1/1/2015 with 60-minute granularity.
```
db2 "call IBM_TRAM.CREATE_TIME_DIMENSION('2010-01-01-00.00.00.000000',
'2015-01-01-00.00.00.000000', 60)"
```
  Tip: When populating the time dimension use the following guidelines:
  - To view yearly data, you must provide the first day of the year, as seen in the preceding example.
  - Specify the end date far enough into the future so that new incoming data can map to and be displayed correctly in the reports.
  - Best practice is to use a value of 60-minute granularity.
Microsoft SQL Server
1. Copy the database scripts (.sql files) from the reports package to a location where they can be run against the Tivoli Data Warehouse. The scripts are in the db_scripts branch of the directory where the reports package was extracted.
2. Customize the provided scripts by changing the default database name in the use statement (replace USE IBM_TRAM) if it is different from the default. If the name of your Tivoli Data Warehouse is "warehouse," the statement is USE warehouse:
  1. If you have an older version of the database scripts already installed, clean up the database using the clean.sql command..
  2. Run the createSchema.sql command.
  3. Run the createProcedure.sql command.
  4. Run the populateTimeDimension.sql command. Also, modify the boundary parameters for the time dimension and granularity, for example,
```
 @startDate    = '2010-01-01 00:00:00',
 @endDate      = '2012-12-31 00:00:00',
 @granularity  = 60,
```
    If Monday must be the first day of the week, add the fourth parameter equal to 1; otherwise, release three parameters.
```
 @weekday      = 7
```
3. If you have an older version of the database scripts already installed, clean up the database.
```
sqlcmd -i clean.sql [-U username -P password] [-S hostname]
```
4. Run the scripts at the MS SQL command line in this order:
```
sqlcmd -i createSchema.sql [-U username -P password] [-S host]
```
```
sqlcmd -i createProcedure.sql [-U username -P password] [-S host]
```
```
sqlcmd -i populateTimeDimension.sql [-U username -P password] [-S host]
```
Oracle manual installation
1. Copy the database scripts (.sql files) from the reports package to a location where they can be run against the Tivoli Data Warehouse. The scripts are in the db_scripts branch of the directory where the reports package was extracted to.
2. Start an SQL *Plus session if it is not already running.
3. Check that you can access remotely as sys user.
4. If you have an older version of the database scripts already installed clean up the database (the procedure must be called by the sys user):
```
clean.sql
```
5. Take one of the following steps:
  - If you can access remotely as sys user, run this command and provide all the information that the script requires:
```
@MY_PATH\setup_IBM_TRAM.sql
```
  - If you cannot access remotely as sys user, run this command locally at the Oracle server and provide all the information that the script requires:
```
@MY_PATH\local_setup_IBM_TRAM.sql
```
Oracle batch installation
1. Copy the database scripts (.sql files) from the reports package to a location where they can be run against the Tivoli Data Warehouse. The scripts are in the db_scripts branch of the directory where the reports package was extracted to.
2. Start an SQL *Plus session if it is not already running.
3. If you have an older version of the database scripts already installed clean up the database (the procedure must be called by the sys user):
```
clean.sql
```
4. Create user IBM_TRAM (the script must be called by a user with system rights, such as SYS/SYSTEM):
```
@MY_PATH\create_IBM_TRAM.sql TCR_PASS USER_TBSPC TEMPORARY_TBSPC 
```
  where TCR_PASS is the password for the new user, USER_TBSPC is the default user tablespaces name (must exist), and TEMPORARY_TBSPC is the default temporary tablespaces name (must exist)
5. Create the IBM_TRAM tables (the script must be called by the IBM_TRAM user created in the previous step):
```
@MY_PATH\create_schema.sql USER_TBSPC
```
  where USER_TBSPC is the default user tablespaces name (must exist)
6. Grant privileges to the user, such as ITMUSER (the script must be called by the IBM_TRAM user):
```
@MY_PATH\grant_IBM_TRAM.sql USER
```
  where USER is the name of the user to grant privileges to.
7. Create the procedure (the script must be called by the IBM_TRAM user):
```
@MY_PATH\gen_time_dim_granularity_hr.sql
```
8. Load the lookup data (the script must be called by the IBM_TRAM user):
```
@MY_PATH\populateLookup.sql
```
9. Generate the time dimension (the procedure must be called by the IBM_TRAM user):
```
@MY_PATH\populateTimeDimension.sql StartDate EndDate Granularity
```
  where StartDate is the start date in the format 'yyyy-mm-dd HH:MM', EndDate is the end date in the format 'yyyy-mm-dd HH:MM', and Granularity is the number of minutes. Example:
```
@MY_PATH\populateTimeDimension.sql '2010-01-01 00:00' '2012-   
            12-31 00:00' '60'
```

Results

The time dimension tables are complete.

Troubleshooting

If the DB2 commands are failing with the following error:
UDA-SQL-0107 A general exception has occurred during the operation "prepare".[IBM][CLI Driver][DB2/NT64] SQL0551N "ITMUSER" does not have the required authorization or privilege to perform operation "SELECT" on object "IBM_TRAM......

Execute the following commands to resolve the issue:

Connect to the Tivoli Data Warehouse as a user with DB2 privileges.
Issue the following grants:
- Grant select on IBM_TRAM."ComputerSystem" to ITMUSER
- Grant select on IBM_TRAM.MONTH _LOOKUP to ITMUSER
- Grant select on IBM_TRAM.TIMEZONE_ DIMENSION to ITMUSER
- Grant select on IBM_TRAM.TIME_DIMENSION to ITMUSER
- Grant select on IBM_TRAM.WEEKDAY_LOOKUP to ITMUSER
- Grant execute on procedure IBM_TRAM.CREATE_TIME_DIMENSION to ITMUSER

Feedback