10.1 TMTP and Tivoli Enterprise Data Warehouse

 < Day Day Up > 



10.1 TMTP and Tivoli Enterprise Data Warehouse

One of the important features of IBM Tivoli Monitoring for Transaction Performance Version 5.2 is the integration of the common Tivoli repository for historical data, that is, Tivoli Enterprise Data Warehouse. Both the Enterprise- and the Web Transaction Performance features provide these capabilities by supplying functions to extract historical data from the TMTP database.

The Tivoli Enterprise Data Warehouse (TEDW) is used to collect and manage data from various Tivoli and non-Tivoli system management applications. The data is imported into the TEDW databases through specialized extract, transform, and load (ETL) programs, from the management application databases, and further processed for historical analysis and evaluation. It is Tivoli's strategy to provide ETLs for most Tivoli components so the TEDW databases can be populated with meaningful systems management data. IBM Tivoli Monitoring for Transaction Performance is but one of many products to leverage and use TEDW.

10.1.1 Tivoli Enterprise Data Warehouse overview

Having access to historical data regarding the performance and availability of IT resources is very useful in various ways, such as:

  • TEDW collects historical data from many applications into one central place.

    TEDW collects the underlying data about the network devices/connections, desktops/servers, applications/software, problems and activities that manage the infrastructure. This allows for the construction of an end-to-end view of the enterprise and viewing of the related resource data independent of the specific applications used to monitor and control the resources.

  • TEDW adds value to raw data.

    TEDW performs data aggregation based on user specified periods, such as daily or weekly, and allows for restricting the amount of data stored in the central data TEDW repository. The data is also cleaned and consolidated in order to allow the data model of the central repository to share common dimensions. For example, TEDW ensures that the time, host name, and IP address are the same dimensions across all the applications.

  • TEDW allows for correlation of information from many Tivoli applications.

    TEDW can also be used to derive added value by correlating data from many Tivoli applications. It allows reports to be written, which correlate cross application data.

  • TEDW uses open, proven interfaces for extracting, storing, and sharing the data.

    TEDW can extract data from any application (Tivoli and non-Tivoli) and store it in a common, central database. TEDW also provides transparent access for third-party Business Intelligence (BI) solutions using the CWM standard, such as IBM DB2 OLAP, Crystal Decisions, Cognos, BusinessObjects, Brio Technology, and Microsoft OLAP Server. CWM stands for Common Warehouse Metadata, an industry standard specification for metadata interchange defined by the Object Management Group (see http://www.omg.org). TEDW provides a Web-based reporting front end called the Reporting Interface, but the open architecture provided by the TEDW allows other BI front ends to be used to access the data in the central warehouse. The value here is flexibility. Customers can use the reporting application of their choice; they are not limited to any specific one.

  • TEDW provides a robust security mechanism.

    TEDW provides a robust security mechanism by allowing data marts to be built with data from subsets of managed resources; by providing database level authorization to access those data marts, TEDW can address most of the security requirements related to limiting access to specific data to those customers/business units with a need to know.

  • TEDW provides a scalable architecture.

    Since TEDW depends on the proven and industry standard RDBMS technology, it provides a scalable architecture for storing and retrieving the data.

Tivoli Enterprise Data Warehouse concepts and components

This section discusses the key concepts and the various components of TEDW in the logical order that the measurement data flows: from the monitors collecting raw data to the final detailed report. Figure 10-1 on page 378 depicts a typical Tivoli Enterprise Data Warehouse configuration that will be used throughout this section

click to expand
Figure 10-1: A typical TEDW environment

It is common for enterprises to have various distributed performance and availability monitoring applications deployed that collect some sort of measurement data and provide some type of threshold management, central event management, and other basic monitoring functions. These applications are referred as source applications.

The first step to obtaining management data is to enable the source applications. This means providing all the tools and castigation necessary to import the source operational data into the TEDW central data warehouse. All components needed for that task are collected in so-called warehouse modules for each source application. In this publication, IBM Tivoli Monitoring for Web Infrastructure is the source application providing management data for Web server and Application server data warehouse modules.

One important part of the warehouse modules are the Extract, Transform, and Load data programs, or simply ETL programs. In general, ETL programs process data in three steps.

  1. First they extract the data from a source application database, called the data source.

  2. Then the data is validated, transformed, aggregated, and/or cleansed so that it fits the format and needs of the data target.

  3. Finally, the data is loaded into the target database.

In TEDW, there are two types of ETLs: central data warehouse ETL and data mart ETL:

Central data warehouse ETL

The central data warehouse ETL pulls the data from the source applications and loads it into the central data warehouse, as shown in Figure 10-1 on page 378. The central data warehouse ETL is also often referred to as the source ETL or ETL1.

Data mart ETL

As shown in Figure 10-1 on page 378, the data mart ETL extracts a subset of historical data from the central data warehouse that contains data tailored to and optimized for a specific reporting or analysis task. This subset of data is used to populate data marts. The data mart ETL is also known as target ETL or ETL2.

As a generic concept, a data warehouse is a structured, extensible database environment designed for the analysis of consistent data. The data that is inserted in a data warehouse is logically and physically transformed from multiple source applications, updated, and maintained for a long time period of time, and summarized for quick analysis. The Tivoli Enterprise Data Warehouse Central Data Warehouse (CDW) is the database that contains all enterprise-wide historical data, with an hour as the lowest granularity. This data store is optimized for the efficient storage of large amounts of data and has a documented format that makes the data accessible to many analysis solutions. The database is organized in a very flexible way, which lets you store data from new applications without adding or changing tables.

The TEDW server is an IBM DB2 Universal Database Enterprise Edition server that hosts the TEDW Central Data Warehouse databases. These databases are populated with operational data from Tivoli and/or other third-party applications for historical analyses.

A data mart is a subset of the historical data that satisfies the needs of a specific department, team, or customer. A data mart is optimized for interactive reporting and data analysis. The format of a data mart is specific to the reporting or analysis tool you plan to use. Each application that provides a data mart ETL creates its data marts in the appropriate format.

TEDW provides a Report Interface (RI) that creates static two-dimensional reports of your data using the data marts. The Report Interface is a role-based Web interface that can be accessed with a simple Web browser without any additional software installed on the client. You can also use other tools to perform OLAP analysis, business intelligence reporting, or data mining.

The TEDW Control Center is the IBM DB2 Universal Database Enterprise Edition server containing the TEDW control database that manages your TEDW environment. From the TEDW Control Center, you can also manage all source applications databases in your environment. The default internal name for the TEDW control database is TWH_MD. The TEDW Control Center also manages the communication between the various components, such as the TEDW Central Data Warehouse, the data marts, and the Report Interfaces. The TEDW Control Center uses the DB2 Data Warehouse Center utility to define, maintain, schedule, and monitor the ETL processes.

The TEDW stores raw historical data from all Tivoli and third-party application databases in the TEDW Central Data Warehouse database. The internal name of the TEDW Central Data Warehouse database is TWH_CDW. Once the data has been inserted into the TWH_CDW database, it is available for either the TEDW ETLs to load to the TEDW Data Mart database (the internal name of the TEDW Data Mart database is TWH_MART) or to any other application-specific ETL to process the data and load the application-specific data mart database.

10.1.2 TMTP Version 5.2 Warehouse Enablement Pack overview

IBM Tivoli Monitoring for Transaction Performance Version 5.2 has the ability to display the detailed transaction process information as real-time reports. The data is stored in the TMTP database that runs on either DB2 or Oracle database management products. This database is regarded as the source database for the warehouse pack.

When the TMTP real time reporting data is stored in the source database, the central data warehouse database ETL periodically processes (normally once a day) and extracts data from the source database to the central data warehouse database, TWH_CDW. Once in the central database, the data is converted to the TMTP warehouse pack data model shown in Figure 10-2 on page 381. This data model allows the TMTP reporting data to fit into the general schema of Tivoli Enterprise Data Warehouse Version 1.1.

click to expand
Figure 10-2: TMTP Version 5.2 warehouse data model

After the central data warehouse ETL processes are complete, the data mart ETL processes load data from the central data warehouse database into the data mart database. In the data mart database, fact tables, dimension tables, and helper tables are created in the BWM schema. Data from the central data warehouse database are filled into these dimension and fact tables in the data mart database. You can then use the hourly, daily, weekly, and monthly star schemes of the dimension and fact tables to generate reports in the TEDW report interface.

In addition, the TMTP warehouse pack includes the migration processes for IBM Tivoli Monitoring for Transaction Performance Version 5.1, which enables upgrading existing historical data collected by the IBM Tivoli Monitoring for Transaction Performance Version 5.1 central data warehouse ETL.

IBM Tivoli Monitoring for Transaction Performance does not use resource models; thus, the IBM Tivoli Monitoring warehouse pack and its tables are not required for the TMTP warehouse pack.

10.1.3 The monitoring process data flow

In this section, we will discuss how the warehouse features of both IBM Tivoli Monitoring for Transaction Performance modules interact with the Tivoli Enterprise Data Warehouse. We will also describe the various components that make up the IBM Tivoli Monitoring for Transaction Performance warehouse components. We will demonstrate how the data is collected from the endpoint and how it reaches the data warehouse database, as shown in Figure 10-3. The ETLs used by the warehouse components are explained in Table 10-3 on page 401 and Table 10-4 on page 404.

click to expand
Figure 10-3: ITMTP- Enterprise Transaction Performance data flow

The TMTP upload component is responsible for moving data from the Management Agent to the database. The TMTP ETL1 is then used to collect data from the TMTP database for any module and transform and load these to the staging area tables and dynamic data tables in the central data warehouse (TWH_CDH).

Before going into details of how to install and configure the Tivoli Enterprise Data Warehouse Enablement Packs to extract and store data from the IBM Tivoli Monitoring for Transaction Performance components, the environment used for TEDW in the ITSO lab is presented. This can be used as a starting point for setting up the data gathering process. We assume no preexisting components will be used and describe the steps of a brand new installation.

As shown in Figure 10-4, our Tivoli Enterprise Data Warehouse environment is a small, distributed environment composed of three machines:

  1. A Tivoli Enterprise Data Warehouse server machine hosting the central Warehouse and the Warehouse Data Mart databases.

  2. A Tivoli Enterprise Data Warehouse Control Center machine hosting the Warehouse meta data database and handling all the ETLs executions.

  3. A Tivoli Enterprise Data Warehouse Reporting Interface machine allowing end users to obtain reports from data stored in the data marts.

click to expand
Figure 10-4: Tivoli Enterprise Data Warehouse installation scenario

10.1.4 Setting up the TMTP Warehouse Enablement Packs

The following sections describe the procedures that need to be performed in order to install, configure, and schedule the warehouse modules for the IBM Tivoli Monitoring for Transaction Performance product. The description of the installation steps is based on our lab environment scenario described in Figure 10-4.

It is assumed that the Tivoli Enterprise Data Warehouse environment Version 1.1 is already installed and operational. Details for achieving this can be found in the redbook Introduction to Tivoli Enterprise Data Warehouse, SG24-6607.

Throughout the following sections, the Warehouse Enablement Pack for IBM Tivoli Monitoring for Transaction Performance: Enterprise Transaction Performance will be used to demonstrate the tasks that needs to be performed, and the changes needed to implement the Warehouse Enablement Pack for IBM Tivoli Monitoring for Transaction Performance: Web Transaction Performance will be noted at the end of the walkthrough.

The installation and configuration of the Warehouse Enablement Packs is a four step process that consists of:

Pre-installation steps

These steps have to be performed to make sure that the TEDW environment is ready to receive the TMTP Warehouse Enablement Packs.

Installation

The actual transferral of code from the installation images to the TEDW server, and registration of the TMTP ETLs in the TEDW registry.

Post-installation steps

Provides additional configuration information to ensure the correct function of the TMTP Warehouse Enablement Packs.

Activation

Includes scheduling and transfer to production mode of the TMTP specific ETL tasks.

Pre-installation steps

Prior to the installation of the Warehouse modules, you must perform the following tasks:

  1. Upgrade to DB2 UDB Server Version 7.2 FixPack 6 or higher.

  2. Apply TEDW FixPack 1.1-TDW-002 or higher.

  3. Update the TEDW environment to FixPack 1-1-TDW-FP01a.

  4. Ensure adequate heap size of the TWH_CDW database.

You are only required to perform these steps once, since they apply to the general TWDW environment and not to any specific ETLs.

Upgrade to DB2 UDB Server Version 7.2 FixPack 6 or higher

Upgrade IBM DB2 Universal Database Enterprise Edition Version 7.2 to at least FixPack 6 on your Tivoli Enterprise Data Warehouse environment.

FixPack 6 for IBM DB2 Universal Database Enterprise Edition can be download from the official IBM DB2 technical support Web site:

http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7fphist.d2w/report

Apply TEDW FixPack 1.1-TDW-002 or higher

Apply the FixPack 1.1-TDW-0002 on every database server in your TEDW environment

FixPack 1.1-TDW-0002 for Tivoli Enterprise Data Warehouse can be downloaded from the IBM Tivoli Software support Web site, under the Tivoli Enterprise Data Warehouse category:

http://www.ibm.com/software/sysmgmt/products/support/

Update the TEDW environment to FixPack 1-1-TDW-FP01a

FixPack 1-1-TDW-FP01a for Tivoli Enterprise Data Warehouse can be downloaded from the IBM Tivoli Software support Web site, under the Tivoli Enterprise Data Warehouse category:

http://www.ibm.com/software/sysmgmt/products/support/

The documentation that accompanies the FixPacks details the steps for installation in greater detail.

Ensure adequate heap size of the TWH_CDW database

The applications control heap size on the TWH_CDW database needs to be set to at least 512 as follows:

  1. Log on using the DB2 administrator user ID to your TEDW Server machine (in our case, db2admin), and connect to the TWH_CDW database:

     db2 connect to TWH_CDW user db2admin using <db2pw> 

    where <db2pw> is the database administrator password.

  2. In order to determine the actual heap size issue:

     db2 get db cfg for TWH_CDW | grep CTL_HEAP 

    The output should be similar to what is shown in Example 10-1.

    Example 10-1: Current applications control heap size on the TWH_CDW database

    start example
     Max appl. control heap size (4KB)    (APP_CTL_HEAP_SZ) = 128 
    end example

  3. If the heap size is less that 512, perform:

     db2 update db cfg for TWH_CDW using APP_CTL_HEAP_SZ 512 

    The output should be similar what is shown in Example 10-2 on page 386.

    Example 10-2: Output from db2 update db cfg for TWH_CDW

    start example
     DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully. DB21026I  For most configuration parameters, all applications must disconnect from this database before the changes become effective. 
    end example

  4. You should now restart DB2 by issuing the following series of commands:

     db2 disconnect THW_CDW db2 force application all db2 terminate db2stop db2admin stop db2admin start db2start 

Limitations

This warehouse pack must be installed using the user db2. If that is not the user name used when installing the Tivoli Enterprise Data Warehouse core application, you must create a temporary user table space for use by the installation program. The temporary user table space that is created in each central data warehouse database and data mart database during the installation of Tivoli Enterprise Data Warehouse is accessible only to the user that performed the installation. If you are installing the warehouse pack using the same database user that installed Tivoli Enterprise Data Warehouse, or if your database user has access to another user temporary table space in the target databases, no additional action is required. If you do not know the user name that was used to install Tivoli Enterprise Data Warehouse, you can determine whether the table space is accessible by attempting to declare a temporary table while connected to each database as the user that will install the warehouse pack. The commands in Example 10-3 are one way to achieve this.

Example 10-3: How to connect TWH_CDW

start example
 db2 "connect to TWH_CDW user <installing_user> using <password> " db2 "declare global temporary table t1 (c1 char(1))with replace on commit preserve rows not logged" db2 "disconnect TWH_CDW" db2 "connect to TWH_MART user installing_user using password " db2 "declare global temporary table t1 (c1 char(1))with replace on commit preserve rows not logged" db2 "disconnect TWH_MART" 
end example

Where:

installing_user

Identifies the database user that will install the warehouse pack.

password

Specifies the password for the installing user.

Installing the Warehouse Enablement Packs

The IBM Tivoli Monitoring for Transaction Performance Warehouse Enablement Pack extracts data from the ITMTP: Enterprise Transaction Performance RIM database (TAPM) and the Web Services Courier database, respectively, and loads it into the TEDW Central Data Warehouse database (TWH_CDW). The two modules acts as a source ETLs.

All TEDW ETL programs follow a naming convention using a three letter application-specific product code known as measurement source code. Table 10-1 shows the measurement codes used for the TMTP Warehouse Enablement Packs.

Table 10-1: Measurement codes

Warehouse module name

Measurement code

IBM Tivoli Monitoring Transaction and Performance 5.2: WTP

BWM

The installation can be performed using the TEDW Command Line Interface (CLI) or the Graphical User Interface (GUI) based installation program. Here we describe the process using the GUI method.

The following steps should be performed at the Tivoli Enterprise Data Warehouse Control Center server, once for each of the IBM Tivoli Monitoring for Transaction Performance Warehouse Enablement Packs that are being installed.

Note 

You need both the TEDW and the appropriate IBM Tivoli Monitoring for Transaction Performance products installation media.

  1. Insert the TEDW Installation CD in the CD-ROM drive.

  2. Select Start Run. Type in D:\setup.exe and click OK to start the installation, where D is the CD-ROM drive.

  3. When the Install Shield Wizard dialogue window for TEDW Installation appears (Figure 10-5 on page 388). Click Next.

    click to expand
    Figure 10-5: TEDW installation

  4. The dialog for the type of installation (see Figure 10-6) appears. Select Application installation only and the directory name where the TEDW components are installed. We used C:\TWH. Click Next to continue.

    click to expand
    Figure 10-6: TEDW installation type

  5. The host name dialog appears, as shown in Figure 10-6. Verify that this is the correct host name for the TEDW Control Center server. Click Next

  6. The local system DB2 configuration dialog is displayed. It should be similar to what is shown in Figure 10-7 on page 389. The installation process asks for a valid DB2 user ID. Enter the valid DB2 user ID and password that were created during the DB2 installation on your local system. In our case, we used db2admin. Click Next.

    click to expand
    Figure 10-7: TEDW installation- DB2 configuration

  7. The path to the installation media for the application packages dialog appears next, as shown in Figure 10-8.

    click to expand
    Figure 10-8: Path to the installation media for the ITM Generic ETL1 program

    You should provide the location of the appropriate IBM Tivoli Monitoring for Transaction Performance ETL1 program. Change the TEDW CD in the CD-ROM drive with the desired installation CD. Specify the path to the installation file named twh_app_install_list.cfg.

    If you use the Tivoli product CDs, the paths to the installation files for the ETP and TMTP installation files are:

    TMTP

    <CDROM-drive>:\tedw_apps_etl

    Leave the Now option checked (prevents typing errors) to verify that the source directory is immediately accessible and that it contains the correct files. Click Next.

  8. Before starting the installation, do not select to install additional modules when prompted (Figure 10-9). Press Next.

    click to expand
    Figure 10-9: TEDW installation- Additional modules

  9. The overview of selected features dialogue window appears, as shown in Figure 10-10. Click Install to start the installation.

    click to expand
    Figure 10-10: TMTP ETL1 and ETL2 program installation

  10. During the installation, the panel shown in Figure 10-11, will be displayed. Wait for successful completion.


    Figure 10-11: TEDW installation- Installation running

  11. Once the installation is finished, the Installation summary dialog appears, as shown in Figure 10-12.

    click to expand
    Figure 10-12: Installation summary window

    If the installation was not successful, check the TWHApp.log file for any errors. This log file is located in the <TWH_inst_dir>\apps\AMX\, where <TWH_inst_dir> is the TEDW installation directory.

Existing TMTP warehouse pack installation

Use the following installation steps to install an existing IBM Tivoli Monitoring for Transaction Performance: Web Transaction Performance, Version 5.1.0 warehouse pack Version 1.1.0:

  1. Back up the TWH_CDW database before you perform the upgrade.

  2. Go to the <TWH_DIR>\install\bin directory.

  3. Run the command sh tedw_wpack_patchadm.sh to generate a configuration template file. The default file name for the configuration file is <USER_HOME>/LOCALS~1/Temp/twh_app_patcher.cfg. Skip this step if this file already exists.

  4. Edit the configuration file to set the parameters to match your installation environment, media location, and user and password settings.

  5. Run the sh tedw_wpack_patchadm.sh command a second time to install the patch scripts and programs.

  6. Open the DB2 Data Warehouse Center.

  7. Locate the BWM_c05_Upgrade_Processes group under Subject Areas.

  8. Set the schedule for this processes group as execute One Time Only and set the schedule to run immediately. The upgrade process only needs to run once.

  9. The upgrade processes defined in this group begin automatically. You can execute the upgrade process without any IBM Tivoli Monitoring for Transaction Performance: Web Transaction Performance Version 5.1.0 historical data. In this case, no data is added into IBM Tivoli Monitoring for Transaction Performance Version 5.2 historical data.

    Set the Version 5.2 central data warehouse ETL and data mart ETL scripts to the Test status to temporarily disable the Version 5.2 central data warehouse ETL processes in the DB2 data warehouse center. This prevents the scripts from automatically executing during the upgrade

  10. After the upgrade processes are complete, view the <script_file_name>.log files in the <DB2_HOME>/logging directory to ensure that every script completed successfully.

    A completed message at the end of the log file indicates that the script was successfully performed. If any errors occur, restore the TWH_CDW database from the backup and rerun the processes after problems are located and corrected. A successful upgrade will complete silently and a failed upgrade can stop with or without pop-up error messages in the DB2 data warehouse center. Always check the log files to confirm the upgrade status.

  11. Run TMTP data mart ETL processes to extract and load newly upgraded data into the data mart database.

  12. Update the user name and password for the Warehouse Sources and Targets in the DB2 Data Warehouse Center.

Note 

The BWM_TMTP_DATA_SOURCE must reflect the database where the TMTP Management Server uploads its data. For details on how to update sources and targets, see the Tivoli Enterprise Data Warehouse Installing and Configuring Guide Version 1.1, GC32-0744.

Post-installation steps

After successful installation, the following activities must be completed in order to make TEDW suit your particular environment:

  1. Creating an ODBC connection to the TMTP source databases

  2. Defining user authority to the Warehouse sources and targets

  3. Modifying the schema information

  4. Customizing your TEDW environment

Creating an ODBC connection to the TMTP source databases

The TEDW Control Center server hosts all the ETLs. This server needs to have access to the various databases accessed by the SQL scripts imbedded in the ETLs. TEDW uses ODBC connections to access all databases, so the TMTP source databases needs to be cataloged at the TEDW DB2 server as ODBC system data sources.

The ETL programs provided with the IBM Tivoli Monitoring for Transaction Performance: Enterprise Transaction Performance Warehouse Enablement Packs require specific logical names of the data sources to be used. Table 10-2 shows the values to be used for each of the data sources.

Table 10-2: Source database names used by the TMTP ETLs

Warehouse Enablement Pack

Source database

ETL source database name

TMTP Version 5.2:WTP

TMTP

TMTP_DB_Src

At the TEDW Control Center server, using a DB2 command line window, issue the following commands (in case your source databases are implemented on DB2 RDBMS systems) for each of the source databases:

 db2 catalog tcpip node <nodename> remote <hostname> server <db2_port> db2 catalog database <alias> as <database> at node <nodename> ODBC db2 catalog system odbc data source <alias> 

Where:

<nodename>

A logical name you assign to the remote DB2 server.

<hostname>

The TCP/IP host name of the remote DB2 server.

<db2_port>

The TCP/IP port used by DB2 (default is 50000).

<alias>

The logical name assigned to the source database. Use the values for the TMTP databases provided in Table 10-2 on page 393.

<database>

The name of the database, as it is known at the DB2 server hosting the database. The values are most likely TMTP for Management Server.

Note 

If the source databases are implemented using other RDBMS systems (such as Oracle), the commands vary. Instead of using the db2 command line interface, you may use the GUI of the DB2 Client Assistant to catalog the appropriate ODBC data sources. This method may also be used for DB2 hosted source databases.

Defining user authority to the Warehouse sources and targets

You should inform the TEDW Control Center server of user access information for every source and target ETL process installed by the IBM Tivoli Monitoring for Transaction Performance ETL. The following steps should be followed:

  1. Start the IBM DB2 Control Center utility by selecting Start Programs IBM DB2 Control Center.

  2. On the IBM DB2 Control Center utility, start the IBM DB2 Data Warehouse Center utility by selecting Tools Data Warehouse Center. The Data Warehouse Center logon window appears.

  3. Log in to the IBM DB2 Data Warehouse Center utility using the local DB2 administrator user ID, in our case, db2admin.

  4. In the Data Warehouse Center window, expand the Warehouse Sources and Warehouse Targets folder. As shown in Figure 10-13 on page 395, there are three entries for the IBM Tivoli Monitoring for Transaction Performance ETL programs that need to be configured:

    • Warehouse Source

      • BWM_TMTP_DATA_SOURCE

      • BWM_TWH_CDW_Source

      • BWH_TWH_MART_Source

    • Warehouse Target:

      • BWM_TWH_CDW_Target

      • BWM_TWH_MART_Target

      • BWH_TWH_MD_Target

    click to expand
    Figure 10-13: TMTP ETL Source and Target

    Edit the properties of each one of the entries above.

    In order to edit the properties of the ETL sources, right-click on the actual object and select Properties from the pop-up menu. Then select the Data Source tab. Fill in the database instance owner user ID information. For our environment, the values are shown in Figure 10-14 on page 396, using the BWM_TMTP_DATA_SOURCE as an example.

    click to expand
    Figure 10-14: BWB_TMTP_DATA_SOURCE user ID information

    Set the user ID and password of Data Source for every BWM Warehouse Source and Target ETL.

Modifying the schema information

In order for the ETLs to successfully access the data within the sources defined, an extra step is needed to make sure that the table names referenced by the ETLs match those found in the source databases.

For all the tables used in the IBM Tivoli Monitoring for Transaction Performance Warehouse source (BWM_TMTP_DATA_SOURCE) it should be verified that the schema information is filled out, and that the table names do not contain creator information. This is, unfortunately, the default situation immediately after installation, as shown in Figure 10-15 on page 397, where you should note that the table names all include the creator information (the part before the period) and the schema field has been left blank.

click to expand
Figure 10-15: Warehouse source table properties

To provide TEDW with the correct schema and table information, follow the following procedure for every table in each of the IBM Tivoli Monitoring for Transaction Performance ETL sources:

  1. On the TEDW Control Center server using Data Warehouse Center window, expand Warehouse Sources.

  2. Select the appropriate source, for example, BWM_TMTP_DATA_SOURCE, and explode it to be able to see the sub-folders.

  3. Open the Tables folder.

  4. Right-click on each table that appears in the right pane of the Data Warehouse Center window, and select Properties. The properties dialog shown in Figure 10-15 appears.

    Note that TEDW inserts a default name in the TableSchema field, and that TableName contains the fully qualified name of the table (enclosed in quotes).

  5. Type the name of the table creator (or schema) to be used in the TableSchema field, and remove the creator information (including periods and quotes) from the TableName field. The values used in our case are shown in Figure 10-16 on page 398.

    click to expand
    Figure 10-16: TableSchema and TableName for TMTP Warehouse sources

These steps should be performed for all the tables referenced by the two IBM Tivoli Monitoring for Transaction Performance Warehouse sources (BWM_TMTP_DATA_SOURCE). Upon completion, the list of tables displayed in the right pane of the Data Warehouse Center window should look similar to the one shown in Figure 10-17, where all the schema information is filled out, and no table names include the creator information.

click to expand
Figure 10-17: Warehouse source table names changed

click to expand
Figure 10-18: Warehouse source table names immediately after installation

Customizing your TEDW environment

After installation of the warehouse enablement pack, use the procedures described in the Tivoli Enterprise Data Warehouse Installing and Configuring Guide Version 1.1, GC32-0744 manual to use the Data Warehouse Center to perform the following configuration tasks for data sources and targets:

  1. Make sure the control database is set to TWH_MD.

    1. Specify the properties for the BWM_TMTP_DATA_SOURCE data source, ODBC Source.

    2. Set Data Source Name (DSN) to the name of the ODBC connection for the BWM_TMTP_DATA_SOURCE. The default value is DM.

    3. Set the User ID field to the Instance name for the configuration repository. The default value is db2admin.

    4. Set the Password field to the password used to access the BWM_TMTP_DATA_SOURCE.

  2. Specify the properties for the target BWM_TWH_CDW_SOURCE.

    1. In the User ID field, type the user ID used to access the Tivoli Enterprise Data Warehouse central data warehouse database. The default value is db2admin.

    2. In the Password field, type the password used to access the central data warehouse database.

    3. Do not change the value of the Data Source field. It must be TWH_CDW.

  3. Specify the following properties for the target BWM_TWH_MART_SOURCE.

    1. In the User ID field, type the user ID used to access the data mart database. The default value is db2admin.

    2. In the Password field, type the password used to access the data mart database.

    3. Do not change the value of the Data Source field. It must be TWH_MART.

  4. Specify the properties for the warehouse target BWM_TWH_CDW_TARGET.

    1. In the User ID field, type the user ID used to access the central data warehouse database. The default value is db2admin.

    2. In the Password field, type the password used to access the central data warehouse database.

    3. Do not change the value of the Data Source field. It must be TWH_CDW.

  5. Specify the following properties for the target BWM_TWH_MART_TARGET.

    1. In the User ID field, type the user ID used to access the data mart database. The default value is db2admin.

    2. In the Password field, type the password used to access the data mart database.

    3. Do not change the value of the Data Source field. It must be TWH_MART.

  6. Specify the properties for the target BWM_TWH_MD_TARGET.

    1. In the User ID field, type the user ID used to access the control database. The default value is db2admin.

    2. In the Password field, type the password used to access the central data warehouse database.

    3. Do not change the value of the Data Source field. It must be TWH_MD.

Specify dependencies between the ETL processes and schedule processes that are to run automatically. The processes for this warehouse pack are located in the BWM_Tivoli_Monitoring_for_Transaction_Performance_v5.2.0 subject area. The processes should be run in the following order:

  • BWM_c05_Upgrade51_Process

  • BWM_c10_CDW_Process

  • BWM_m05_Mart_Process

Attention: 

Only run the BWM_c05_Upgrade51_Process process if you are migrating from Version 5.1.0 to Version 5.2.

Activating ETLs

Before the newly defined ETLs can start extracting data from the source databases into the TEDW environment, they must be activated. This implies that a schedule must be defined for each of the main processes of the ETLs. After having provided a schedule, it is also necessary to change the operation mode of the all the related ETL components to production in order for TEDW to start processing the ETLs according to the specified schedule.

Scheduling the ETL processes

In order to get data extracted periodically from the source database into the data warehouse, a schedule must be specified for all the periodic processes. This is also the case for one-time processes that have to be run to initiate the data warehouse environment for each application area such as TMTP or ITM.

Table 10-3 lists the process that needs to be scheduled for the IBM Tivoli Monitoring for Transaction Performance ETLs to run.

Table 10-3: Warehouse processes

Warehouse enablement pack

Process

Frequency

TMTP:ETL1

BWM_c10_CDW_Process

periodically

TMTP:ETL2

BWM_m05_Mart_Process

periodically

To schedule a process, no matter if it has to run once or multiple times, the same basic steps need to be completed. The only difference between one-time and periodically executed processes is the schedule provided. The following provides a brief walk-trough using the process BWM_c10_CDW_Process to describe the required steps:

  1. On the TEDW Control Center server, using the Data Warehouse Center window, expand Subject Areas.

  2. Select the appropriate Subject Area, for example, BWM_Tivoli_Monitoring_for_Transaction_Performance_v5.2.0_Subject_Are a, and explode it to see the processes.

  3. Right-click on the process to schedule (in our example, BWM_c10_CDW_Process) and choose Schedule, as shown in Figure 10-19 on page 402.

    click to expand
    Figure 10-19: Scheduling source ETL process

  4. Provide the appropriate scheduling information as it applies to your environment. As shown in Figure 10-20 on page 403, we scheduled the BWM_c10_CDW_Process to run every day at 6 AM.

    click to expand
    Figure 10-20: Scheduling soure ETL process periodically

    Note 

    To check if the schedule works properly with every process in the source and target ETLS, use the interval setting One time only. It may also be used to clear out all previously imported historical information

    Figure 10-20 shows an interval of Daily. In general, data import should be scheduled to take place when management activity is low, for example, every night from 2 to 7 AM with a 24 hour interval, or with a very short interval (for example, 15 minutes) to ensure that only small amounts of data have to be processed. The usage pattern (requirements for up-to-date data) of the data in the data warehouse should be used to determine which strategy to follow.

    Note 

    Since TEDW does not allow you to change the schedule once the operation mode has been set to Production, you need to demote the mode of the processes to Development or Test if you want to change the schedule, and do not forget to promote the mode of the processes back to Production to activate the new schedule.

Changing the ETL status to Production

All IBM Tivoli Monitoring for Transaction Performance ETL processes are composed by components that have the Development status set by default. In order for them to run, their status need to be changed from Development to Production.

The following steps must be performed for all processes corresponding to your Warehouse Enablement Pack. Table 10-4 provides the complete list. In the following steps, we use BWM_c10_CDW_Process as an example to describe the process.

Table 10-4: Warehouse processes and components

Warehouse enablement pack

Process

Components

TMTP

BWM_c10_CDW_Process

BWM_c10_s010_pre_extract

BWM_c10_s020_extract

BWM_c10_s030_transform_load

BWM_m05_Mart_Proces

BWM_m05_s005_prepare_stage

BWM_m05_s010_mart_pre_extrat

BWM_m05_s020_mart_extract

BWM_m05_s030_mart_load

BWM_m05_s040_mart_rollup

BWM_m05_s050_mart_prune

On the TEDW Control Center server, using the Data Warehouse Center window, select the desired components and right-click on them. Choose Mode Production, as shown in Figure 10-21 on page 405.

click to expand
Figure 10-21: Source ETL scheduled processes to Production status

As demonstrated in Figure 10-21, it is possible to select multiple processes and set the desired mode for all of them at the same time.

Now all the process are ready and scheduled to be run in production mode. When the data collection and ETL1 and ETL2 processes are executed, historical data from IBM Tivoli Monitoring for Transaction Performance is available to TMTP Version 5.2 data mart, and you will be ready to generate reports, as described in 10.3.2, "Sample TMTP Version 5.2 reports with data mart" on page 408.



 < Day Day Up > 



End-to-End E-business Transaction Management Made Easy
End-To-End E-Business Transaction Management Made Easy
ISBN: 0738499323
EAN: 2147483647
Year: 2003
Pages: 105
Authors: IBM Redbooks

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net