1 About this guide

Mark all trademarks at the first occurrence as appropriate. To mark a trademark, use Insert -> Symbol, (normal text).

This document describes the warehouse enablement pack, version 1.1 for IBMÒ TivoliÒ Security Compliance Manager 5.1. This warehouse enablement pack is created for Tivoli Data Warehouse, Version 1.2.

With this warehouse enablement pack (hereafter referred to as warehouse pack), you can pull data from the central data warehouse and load it into the IBM Tivoli Security Compliance Manager data mart and provide reports.

1.1 Who should read this guide

This guide is for people who do any of the following activities:

· Plan for and install the warehouse pack

· Use and maintain the warehouse pack and its reports

· Create new reports

· Create additional warehouse packs that use data from this warehouse pack

Administrators and installers should have the following knowledge or experience:

· Basic system administration and file management of the operating systems on which the components of Tivoli Data Warehouse are installed

· An understanding of the basic concepts of relational database management

· Experience administering IBM DB2Ò Universal Database

Additionally, report designers and warehouse pack creators should have the following knowledge or experience:

· An understanding of the source data and application

· Data warehouse information and design, extract, transform, and load (ETL) processes, and online analytical processing (OLAP)

1.2 Publications

Do not change the information about TEDW & DB2 documents. Add information about documents that are important to understanding your warehouse pack, such as product documentation that defines the schema of the operational data.

This section lists publications in the Tivoli Data Warehouse library and other related documents. It also describes how to access Tivoli publications online and how to order Tivoli publications.

The following sets of documentation are available to help you understand, install, and manage this warehouse pack:

· IBM Tivoli Security Compliance Manager

· Tivoli Data Warehouse

· IBM DB2, DB2 Data Warehouse Center, and DB2 Warehouse Manager

· IBM Redbooks

1.2.1 IBM Tivoli Security Compliance Manager 5.1 library

The following documents are available in the Tivoli Security Compliance Manager 5.1 library:

· IBM Tivoli Security Compliance Manager Version 5.1 Release Notes, GI11-4695-00

Provides late-breaking information about IBM Tivoli Security Compliance Manager.

· IBM Tivoli Security Compliance Manager Version 5.1 Read this First, GI11-4696-00

· Provides a list of CDs provided with the product, as well as pointers to documentation and product information on the Web.

· IBM Tivoli Security Compliance Manager Version 5.1 Installation Guide: All Components, GC32-1592-00

Provides information about installing all components of the product.

· IBM Tivoli Security Compliance Manager Version 5.1 Installation Guide: Client component, GC32-1593-00

Provides information about installing the client component of IBM Tivoli Security Compliance Manager 5.1.

· IBM Tivoli Security Compliance Manager Version 5.1 Administration Guide, SC32-1594-00

Provides information about administering and using the product. Also provides troubleshooting information and a reference for collectors.

· IBM Tivoli Security Compliance Manager Version 5.1 Collector Development Guide, SC32-1595-00

Provides information about designing and writing a collector for use with Tivoli Security Compliance Manager.

1.2.2 Tivoli Data Warehouse library

The following documents are available in the Tivoli Data Warehouse library. The library is available online, as described in “Accessing publications online” on page 4.

· Tivoli Data Warehouse Release Notes, SC32-1399

Provides late-breaking information about Tivoli Data Warehouse and lists hardware requirements and software prerequisites.

· Installing and Configuring Tivoli Data Warehouse, GC32-0744

Describes how Tivoli Data Warehouse fits into your enterprise, explains how to plan for its deployment, and gives installation and configuration instructions. It contains maintenance procedures and troubleshooting information.

· Enabling an Application for Tivoli Data Warehouse, GC32-0745

Provides information about connecting an application to Tivoli Data Warehouse. This book is for application programmers who use Tivoli Data Warehouse to store and report on their application data, data warehousing experts who import Tivoli Data Warehouse data into business intelligence applications, and customers who put their local data in Tivoli Data Warehouse. This document is available only from the IBM Web site.

· Tivoli Data Warehouse Messages, SC09-7776

Lists the messages generated by Tivoli Data Warehouse, and describes the corrective actions you should take.

1.2.3 Related publications

The following sections describe additional publications to help you understand and use Tivoli Data Warehouse.

1.2.3.1 IBM Redbooks

IBM Redbooks are developed and published by the IBM International Technical Support Organization, the ITSO. They explore integration, implementation, and operation of realistic customer scenarios. The following Redbooks contain information about Tivoli Data Warehouse:

· Introduction to Tivoli Enterprise Data Warehouse, SG24-6607-00

Provides a broad understanding of Tivoli Data Warehouse. Some of the topics that are covered are concepts, architecture, writing your own extract, transform, and load processes (ETLs), and best practices in creating data marts.

· Planning a Tivoli Enterprise Data Warehouse Project, SG24-6608-00

Describes the necessary planning you must complete before you can deploy Tivoli Data Warehouse. The guide shows how to apply these planning steps in a real-life deployment of a warehouse pack using IBM Tivoli Monitoring. It also contains frequently used Tivoli and DB2 commands and lists troubleshooting tips for Tivoli Data Warehouse.

1.2.3.2 IBM DB2, DB2 Data Warehouse Center, and DB2 Warehouse Manager library

The DB2 library contains important information about the database and data warehousing technology provided by IBM DB2, DB2 Data Warehouse Center, and DB2 Warehouse Manager. Refer to the DB2 library for help in installing, configuring, administering, and troubleshooting DB2, which is available on the IBM Web site:

http://www.ibm.com/software/data/db2/library/

After you install DB2, its library is also available on your system.

The following DB2 documents are particularly relevant for people working with Tivoli Data Warehouse:

· IBM DB2 Universal Database for Windows Quick Beginnings, GC09-2971

Guides you through the planning, installation, migration (if necessary), and setup of a partitioned database system using the IBM DB2 product on Microsoft Windows.

· IBM DB2 Universal Database for UNIX Quick Beginnings, GC09-2970

Guides you through the planning, installation, migration (if necessary), and setup of a partitioned database system using the IBM DB2 product on UNIX.

· IBM DB2 Universal Database Administration Guide: Implementation, SC09-2944

Covers the details of implementing your database design. Topics include creating and altering a database, database security, database recovery, and administration using the Control Center, which is a DB2 graphical user interface.

· IBM DB2 Universal Database Data Warehouse Center Administration Guide, SC26-9993

Provides information on how to build and maintain a data warehouse using the DB2 Data Warehouse Center.

· IBM DB2 Warehouse Manager Installation Guide, GC26-9998

Provides information on how to install the following Warehouse Manager components: Information Catalog Manager, warehouse agents, and warehouse transformers.

· IBM DB2 Universal Database and DB2 Connect Installation and Configuration Supplement, GC09-2957

Provides advanced installation considerations, and guides you through the planning, installation, migration (if necessary), and setup of a platform-specific DB2 client. This supplement also contains information on binding, setting up communications on the server, the DB2 GUI tools, DRDA® AS, distributed installation, the configuration of distributed requests, and accessing heterogeneous data sources.

· IBM DB2 Universal Database Message Reference Volume 1, GC09-2978 and IBM DB2 Universal Database Message Reference Volume 2, GC09-2979

Lists the messages and codes issued by DB2, the Information Catalog Manager, and the DB2 Data Warehouse Center, and describes the actions you should take.

1.2.4 Accessing publications online

IBM posts publications for this and all other Tivoli products, as they become available and whenever they are updated, to the Tivoli Software Information Center Web site. The Tivoli Software Information Center is located at the following Web address:

http://publib.boulder.ibm.com/tividd/td/tdprodlist.html

Note: If you print PDF documents on other than letter-sized paper, select the Fit to page check box in the Adobe Acrobat Print dialog. This option is available when you click File " Print. Fit to page ensures that the full dimensions of a letter-sized page print on the paper that you are using.

1.2.5 Ordering publications

You can order many Tivoli publications online at the following Web site:

http://www.elink.ibmlink.ibm.com/public/applications/publications/cgibin/pbi.cgi

You can also order by telephone by calling one of these numbers:

· In the United States: 800-879-2755

· In Canada: 800-426-4968

· In other countries, for a list of telephone numbers, see the following Web site:

http://www.ibm.com/software/tivoli/order-lit/

1.3 Accessibility

Accessibility features help users with a physical disability, such as restricted mobility or limited vision, to use software products successfully. For the warehouse pack, you use the interfaces of IBM DB2 and the reporting tool. See those documentation sets for accessibility information.

1.4 Contacting software support

If you have a problem with a Tivoli product, refer to the following IBM Software Support Web site:

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

If you want to contact customer support, see the IBM Software Support Guide at the following Web site:

http://techsupport.services.ibm.com/guides/handbook.html

The guide provides information about how to contact IBM Software Support, depending on the severity of your problem, and the following information:

· Registration and eligibility

· Telephone numbers, depending on the country in which you are located

· Information you must have before contacting IBM Software Support

1.5 Typeface conventions

This guide uses the following typeface conventions:

Bold

· Lowercase commands and mixed case commands that are otherwise difficult to distinguish from surrounding text

· Interface controls (check boxes, push buttons, radio buttons, spin buttons, fields, folders, icons, list boxes, items inside list boxes, multicolumn lists, containers, menu choices, menu names, tabs, property sheets), labels (such as Tip and Operating system considerations)

· Column headings in a table

· Keywords and parameters in text

Italic

· Citations (titles of books, diskettes, and CDs)

· Words defined in text

· Emphasis of words (words as words)

· Letters as letters

· New terms in text (except in a definition list)

· Variables and values you must provide

Monospace

· Examples and code examples

· File names, programming keywords, and other elements that are difficult to distinguish from surrounding text

· Message text and prompts addressed to the user

· Text that the user must type

· Values for arguments or command options

2 Overview

The following sections provide an overview of Tivoli Data Warehouse and the warehouse pack for IBM Tivoli Security Compliance Manager (SCM).

2.1 Overview of Tivoli Data Warehouse

The TEDW ID team maintains this overview. Please contact us before changing it.

Tivoli Data Warehouse provides the infrastructure for the following:

· Extract, transform, and load (ETL) processes through the IBM DB2 Data Warehouse Center tool

· Schema generation of the central data warehouse

· Historical reports

As shown in Figure 1, Tivoli Data Warehouse consists of a centralized data store where historical data from many management applications can be stored, aggregated, and correlated.

Graphic image is not available. Refer to PDF version of document.

Figure 1. Tivoli Data Warehouse basic architecture

The central data warehouse uses a generic schema that is the same for all applications. As new components or new applications are added, more data is added to the database; however, no new database objects are added in the schema.

A data mart is a subset of a data warehouse that contains data that is tailored and optimized for the specific reporting needs of a department or team.

The central data warehouse ETL reads the data from the operational data stores of the application that collects it, verifies the data, makes the data conform to the schema, and places the data into the central data warehouse.

The data mart ETL extracts a subset of data from the central data warehouse, transforms it, and loads it into one or more star schemas, which can be included in data marts to answer specific business questions.

A program that provides these ETLs is called a warehouse enablement pack or simply warehouse pack.

The ETLs are typically scheduled to run periodically, usually during non-peak hours. If an ETL encounters data that it cannot correctly transform, it creates an entry in an exception table. Exception tables are described in the IBM Tivoli Risk Manager Warehouse Enablement Pack Version 1.1 Implementation Guide.

2.2 Overview of IBM Tivoli Security Compliance Manager warehouse pack

Give an overview of your warehouse pack. Include information that would be helpful for understanding the overall flow of application-specific data in and out of the central data warehouse and data marts. Data flow diagrams worked well for some products.

Provide customer-centric rationale on what benefit customers obtain from installing the warehouse pack. Discuss report capabilities generically and insert a cross reference to the reports section.

If the warehouse pack does not provide reports, describe how other Tivoli applications provide value to your product data. Here is an example statement for warehouse packs that are to be used by Tivoli Service Level Advisor: The warehouse pack for Tivoli SAN Manager extracts data from the Tivoli SAN Manager Enterprise Repository database. The warehouse pack then loads that data into the central data warehouse. The collection of data from Tivoli products into one central data warehouse lets you see trends in operation, resource usage, and cross-product interoperability. The historical data from Tivoli SAN Manager is provided to use by Tivoli Service Level Advisor and Tivoli Storage Manager.

The warehouse pack for IBM Tivoli Security Compliance Manager 5.1 uses the Tivoli Risk Manager 4.2 pack to extract and load the Tivoli Security Compliance Manager policy violation data into the central data warehouse. The collection of data from Tivoli products into the central data warehouse lets you see trends in operation, resource usage, and cross-product interoperability. The warehouse pack for Tivoli Security Compliance Manager requires that the warehouse pack for the Tivoli Risk Manager be installed first. The following diagram illustrates how the Tivoli Risk Manager warehouse pack is used.

Graphic is not available. Refer to PDF version of document.

Graphic is not available. Refer to PDF version of the document.

Figure 2. Tivoli Security Compliance Manager Tivoli Data Warehouse integration architecture

The Tivoli Security Compliance Manager policy violation data will be sent to the Tivoli Risk Manager operational database (Tivoli Risk Manager archive table) through the Tivoli Risk Manager adapter as shown in figure 2. The Tivoli Risk Manager warehouse pack contains both central data warehouse ETL and data mart ETL. The central data warehouse ETL of the Tivoli Risk Manager warehouse pack extracts the Tivoli Security Compliance Manager violation data from the archive table and transforms and loads the data into the central data warehouse. The Tivoli Security Compliance Manager warehouse pack contains only data mart ETL. The data mart ETL of the Tivoli Security Compliance Manager warehouse pack will extract the Tivoli Security Compliance Manager policy violation data from central data warehouse and then transform and load the data into the Tivoli Security Compliance Manager data mart. The collection of historical data of Tivoli Security Compliance Manager violations in the Tivoli Security Compliance Manager data mart lets the user see trends in violations by day/week/month or violations by system/sub network using reports. There are currently three pre-built reports. The reports are described in Section 3 on page 10.

The warehouse pack for IBM Tivoli Risk Manager aggregates events based on:

· Event category (CLASS_CAT)

· Event Source token (SRC_TOKEN), hostname (SRC_HOSTNAME) or IP address (SRC_IPADDR)

· Event target hostname (DST_HOSTNAME) or IP address (DST_IPADDR)

The CLASS_CAT attribute will have a value “SecPolViolation” for SCM policy violations. Refer to the IBM Tivoli Risk Manager Version 4.1, Warehouse Enablement pack version 1.1 for details on central data warehouse ETL of the Tivoli Risk Manager Warehouse Pack.

Applications should also describe their data source in this section. This includes the generic names for any operational data sources from which the warehouse pack extracts data.

The following example uses TEC as an example. The name for the operational data source database for TEC is “event database.” Replace the example information with your application information.

The source of violation data for the Tivoli Security Compliance Manager warehouse pack is the central data warehouse. The name of the ODBC driver for the TWH_CDW is TWH_CDW. Specifically, warehouse tables, TWG.MsmtTyp, TWG.MSrc, TWHG.Msmt, TWG.Comp, TWG.CompAttr, private table HRM.ClassCatDesc and staging table HCV.Event_Metric are the tables of the TWH_CDW that are defined as sources for the warehouse pack. The events with the CLASS_CAT of “SecPolViolation” will be extracted from the central data warehouse by this warehouse pack because those are the Tivoli Security Compliance Manager policy violation events.

3 Reports

This section provides information about the predefined reports provided by the warehouse pack.

The following information is provided:

· A list of the reports

· A description of the information contained in the reports

· The names of the data mart tables that are used to create the reports

· SQL queries for modifying a report or creating a new report based on this one

Report 1: Compliance Violation Trends (violations per day/week/month)

Description column: Describe in detail the purpose of the report as well as the content of the report. Describe any complex navigation that the report has such, as drill down or subreport capabilities. Describe any information that differentiates the report from others.

Table names column: Provide the names of the tables used to create the reports

SQL queries column: Document the SQL query corresponding to each table or chart in the report.

Report name

Description

Table names

Compliance Violation Trends (per day, per week, or per month)

This report shows the total number of policy compliance violations per day, per week, or per month on a single bar chart. The time range for the report can be specified as a parameter.

HCV.F_EVENT_DAY

Figure 3 is a graphical representation of the Compliance Violation Trends report.

Graphic is not available. Refer to PDF version of document.

Figure 3. Compliance Violation Trends

Report 2: Policy Violations by System (per day/week/month)

Report name

Description

Table names

Policy violations by system per day, per week, or per month

This report ranks on a bar chart all the systems by the number of policy compliance violations per day, per week, or per month. The time range for the report can be specified as a parameter.

HCV.F_EVENT_DAY,

HCV.D_DST_HOST

Figure 4 is a graphical representation of the Policy Violations by System report.

Graphic is not available. Refer to PDF version of the document.

Figure 4. Policy Violations By System

Report 3: Policy Violations by subnet (per day/week/month)

Report name

Description

Table names

Policy violations by subnet per day, per week, or per month

This summary report breaks down all the policy compliance violations by subnetworks in the enterprise per day, per week, or per month. The time range for the report can be specified as a parameter.

HCV.F_EVENT_DAY,

HCV.D_DST_HOST

Figure 5 is a graphical representation of the Policy Violations by Subnetwork report.

Graphic is not available. Refer to PDF version of the document.

Figure 5. Policy Violations By Subnet

WRITERS: Optionally, provide a screen shot of the final report. If you put several graphics in Word, eventually Word will crash. So you can put some representative report screen shots instead of every one.

DEVELOPERS: To send your report requirements to the report vendor, be sure to include a graphic representation of each report that you would like the report vendor to create. If Word starts to crash because you have 10 or more graphics imported into the template, put the graphics in a separate document so that they can be sent with the template to the enabling team.

4 Installing and configuring the warehouse pack

This section describes the installation and configuration of the warehouse pack.

4.1 Prerequisite hardware and software

Every prerequisite application and version number must be listed either below or in your product document that contains the full list of software/hardware prerequisites. This list must include version numbers for supported databases, fix packs for databases, version of Tivoli Data Warehouse, fix packs/interim fixes for Tivoli Data Warehouse, and fix packs for your product.

Before installing the warehouse pack for IBM Tivoli Security Compliance Manager, you must install the following software:

· IBM Tivoli Security Compliance Manager 5.1

· IBM Tivoli Risk Manager 4.1, Event Server component

· IBM DB2 Universal Database, Version 7.2

· Fix pack 8e, 9, 10, or 10a for IBM DB2 Universal Database, Version 7.2

· Tivoli Data Warehouse, Version 1.2

· Crystal Enterprise and its prerequisites

· IBM Tivoli Risk Manager 4.1 Warehouse Enablement Pack Version 1.1

If your product specifies in the twh_install_props.cfg file whether each central data warehouse or data mart can be specifically on z/OS, distributed or both, document the number of systems and locations for the data marts and central data warehouses.

This warehouse pack supports central data warehouses on DB2 Universal Database for Windows and UNIX systems. Also, this warehouse pack supports data marts on DB2 UDB for Windows and UNIX systems.

Refer to the Tivoli Data Warehouse Release Notes and IBM Tivoli Security Compliance Manager Release Notes for specific information about hardware prerequisites, database and operating system support, and product prerequisites. For late-breaking news about prerequisites, refer to the following IBM Software Support Web site:

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

4.2 Product notes and limitations

None.

Describe restrictions, limitations, known bugs, etc. in this section. Use section heads to indicate each item, product note, or limitation.

4.3 Database-sizing considerations

Accurate database sizings are important for capacity planning purposes. Customers need to know the number of rows inserted into key tables to calculate throughput and determine how long the ETLs will run. Customers also need the base size and daily growth size to calculate the amount of disk space required for their databases.

Describe the database-sizing considerations, which include:

·In the central data warehouse, space for the historical data. See below for an example showing how the user calculates that value.

·In the data mart, space for summarized data.

·In the control database on the control server, space for metadata defining data marts and reports.

·In your application's operational data sources, space for tables that are created by the warehouse pack. (Not all applications do this.)

·In <specify database>, space for staging tables. The staging tables usually are approximately equivalent to 1 day’s worth of database space.

·Any other space requirements you have.

·If possible, provide formulas and worksheets.

In summary what is really needed for each database (TWH_CDW, TWH_MART and TWH_MD) is:

·An estimate of the number of rows of data in key tables based on size of the environment (Comp, Comptyp, Msmtyp)

·A formula to calculate the base database size.

·An estimate of the number of rows of data inserted into key tables based on the number of Measurements per day (i.e Msmt)

·A formula for calculating the daily size growth of the database.

Ensure that you have sufficient space in the central data warehouse for the historical data collected by the warehouse pack. Refer to the Tivoli Risk Manager warehouse pack for database sizing.

The Measurements per day is the sum of the (number of components) % (the number of measurements per component) % (24 for hourly measurements or 1 for daily measurements).

To calculate the daily increase in TWH_CDW database size for one day’s measurements and the total database size use the following formulas:

· One row in the measurement table is approximately 98 bytes (data + index + overhead)

· Number of measurements per day (480,000) % 98 = 47 MB per day

· Number of days retention (90) + 1 (for daily staging tables) = 91

· Total storage required + 20% contingency = 47 MB % 91 days % 1.2 contingency = 4.7 GB

Note: Use the correct mathematical symbol for multiplication: Select Insert -- > Symbol. Select Math B font. Then select the symbol that looks like an “%”.

Here is an example of a storage summary chart. Delete this after reviewing.

Allocate at least 5 MB per <server> per <month> for the central data warehouse.

Allocate at least 7 MB per <server> per <month> for the data mart.

Servers = 4 month = 3

4.4 Pre-installation procedures

· Make sure that the Tivoli Enterprise Data Warehouse is installed. For instructions on installing the Tivoli Enterprise Data Warehouse, refer to Installing and Configuring Tivoli Enterprise Data Warehouse.

· Make sure that the IBM Tivoli Risk Manager is installed and that the RMDB data source is available.

· Make sure that the IBM Tivoli Risk Manager Warehouse Enablement Pack is installed.

· Make sure that the IBM Tivoli Security Compliance Manager is installed and that the Tivoli Risk Manager adapter for Tivoli Security Compliance Manager is installed and configured.

· Follow the instructions in the IBM Tivoli Risk Manager 4.1 Warehouse Enablement Pack 1.1 Read me and make sure you can run central data warehouse ETL (ETL1) in test mode.

List any steps the customer must do before running the warehouse pack installation program. For example, this might include tasks such as creating additional tables in an existing database or establishing an ODBC connection.

If there are none, say: This warehouse pack requires no pre-installation procedures.

4.5 Installation of the warehouse pack

Before installing the warehouse pack, record the user IDs, passwords, and server name, alias, or file path. You need this information to follow the installation procedures that are described in Installing and Configuration Tivoli Data Warehouse. The default data source names for the ODBC connections are HCV_TWH_CDW_Source and HCV_TWH_MART_Target.

Provide the location of the installation media. You do not need to explain how to install Tivoli Data Warehouse.

Install the warehouse pack as described in Installing and Configuring Tivoli Data Warehouse, using the installation properties file (twh_install_props.cfg file):

Location of the twh_install_props.cfg file

tdw_weps/HCV/v110 in the Tivoli Security Compliance Manager warehouse pack

4.6 Post-installation procedures

For each process, you need to document the default linkages between processes, if those exist. For example, if the tag file specifies a link to run a data mart process immediately after an central data warehouse process.

List any configuration steps the customer must perform after running the warehouse pack installation program. For example, this might include tasks such as configuring warehouse sources and targets.

If there are none, say: This warehouse pack requires no post-installation procedures.

After you install the warehouse pack, you must determine how you want to schedule the ETLs. See the information about installing warehouse packs in Installing and Configuring Tivoli Data Warehouse for the procedure to schedule ETLs using these processes:

Initialization process HCV_m05_Build_mart_Process

Process dependencies Located in the HCV_TivoliSecurityComplianceManager_V5.1.0_Subject_Area

The processes should be scheduled to run after the central data warehouse ETL (ETL1) step, “HRM_c05_s040_Load_Msmt”, in the Tivoli Risk Manager warehouse pack is run.

1. HCV_m05_Build_Mart_Process.

For this warehouse pack, it is sufficient to schedule the first step in the process. All subsequent steps will execute automatically, after the first step succeeds. But it is important that the Tivoli Security Compliance Manager violation data from Tivoli Risk Manager archive table is extracted and loaded into the central data warehouse by the HRM_c05_s010_Extract step of the Tivoli Risk Manager warehouse pack before this step is scheduled.

If your warehouse pack doesn't have any dependencies between processes, delete that part of the step.

4.7 Uninstallation of the warehouse pack

If your warehouse pack requires the user to perform additional steps beyond what is documented in the Installing and Configuring Tivoli Data Warehouse guide, then list them in order. If your warehouse pack requires a different uninstall order, reflect that here. If there are no additional steps, delete the steps.

Perform the following steps to uninstall the warehouse pack:

1. Run the script hcv_cdw_reset_etl2_extctl.generic (described below) to remove all the Tivoli Security Compliance Manager data from the Tivoli Security Compliance Manager data mart.

2. Uninstall the warehouse pack as described in Installing and Configuring Tivoli Data Warehouse.

hcv_cdw_reset_etl2_extctl.generic : This script must be run against the central data warehouse database (TWH_CDW) because that database is where the extract control parameters are stored. To execute a script, you must first open a DB2 command window. From the Windows Start menu, select Programs -> IBM DB2 -> Command Window. Next, you must connect to the desired database (TWH_CDW), using the following command (user ID and password may be different):

db2 connect to TWH_CDW user db2admin using password

To execute the script, enter the following command from the DB2 command window:

db2 –tvf hcv_cdw_reset_etl2_extctl.generic

You will see each statement of the script displayed in the window, followed by a DB2 message indicating success or failure of the statement. If you see any errors, refer to IBM DB2 Universal Database Message Reference, Volume 1.

When the warehouse pack is uninstalled, the following Tivoli Security Compliance Manager specific tables in the data mart are removed, but the data in the central data warehouse remains and is still useable by other applications:

· hcv.d_classcat

· hcv.d_dst_host

· hcv.d_Event_metric

· hcv.d_src_host

· hcv.ext_dst_host

· hcv.f_event_day

· hcv.f_event_hour

· hcv.f_event_month

· hcv.f_event_week

· hcv.prune_mart_control

· hcv.prune_mart_log

· hcv.stage_classcat

· hcv.stage_dst_host

· hcv.stage_f_event_hour

· hcv.stage_starschema

· hcv.t_dst_host

· hcv.t_event_metric

· hcv.t_src_host

4.8 Multiple data centers

If your application and warehouse pack do not use fully qualified host names and Centr_Cd, then you need to provide examples and description of how the customer will create the lookup table for multiple data centers.

This warehouse pack does not provide support for multiple data centers.

5 Maintenance and problem determination

Describe any maintenance tasks required for this warehouse pack.

This section describes maintenance tasks for the warehouse pack.

5.1.1 Data mart

Pruning data from the fact tables is implemented in the HCV_m05_s050_Mart_Prune step of HCV_m05_Load_Mart_Process. The prune mart control table HCV.Prune_Mart_Control governs which data is pruned and contains a date duration value for all the HCV_F_Event_Hour, HCV_F_Event_Day, HCV_F_Event_Week, and HCV_F_Event_Month fact tables. By default, all hourly data is pruned after 3 months, daily and weekly fact data is pruned after 1 year, and monthly fact data is pruned after 3 years when the process step runs. The HCV.Prune_Mart_Log table keeps a history of data pruning.

5.2 Extraction control (table Extract_Control)

The extraction control table assists you in incrementally extracting data from a source database. For an example of incremental extraction, see the Enabling an Application for Tivoli Data Warehouse guide.

ExtCtl_Source VARCHAR (120)	ExtCtl_Target VARCHAR (120)	ExtCtl_From_RawSeq CHAR (10)	ExtCtl_to_RawSeq CHAR (10)	ExtCtl_From_IntSeq BIGINT	ExtCtl_To_IntSeq BIGINT	ExtCtl_From_DtTm TIMESTAMP	ExtCtl_To_DtTm TIMESTAMP	MSrc_Corr_Cd CHAR (6)

If you have additional maintenance tasks, describe them in additional sections. Please notify the TDW ID team so that they can consider whether that new maintenance task is common enough to be added to this template.

5.3 Problem determination

If you have any additional troubleshooting information, describe it here. Before documenting a problem/solution, check the Installing and Configuring Tivoli Data Warehouse guide to see if the topic is already documented there.

For common problems and solutions, see the Installing and Configuring Tivoli Data Warehouse guide.

6 ETL processes

The warehouse pack has the following process:

· HCV_m05_Build_Mart_Process – builds a data mart with four star schemas based on the Tivoli Security Compliance Manager violation event counts aggregated hourly, daily, weekly and monthly.

6.1 HCV_m05_Build_Mart_Process

This process extracts all new Tivoli Security Compliance Manager policy violation event data measurements from the central data warehouse and converts the measurements to facts in the Tivoli Security Compliance Manager data mart. The process also extracts new Tivoli Risk Manager host and event components, plus their attributes, from the central data warehouse and converts the data to dimensions in the Tivoli Security Compliance Manager data mart. It also prunes old facts from the data mart. The process runs in five steps that are described below. The process is configured to start with the first step and run each succeeding step if and only if the preceding step succeeds.

Repeat this section for each process.

Explain the function of the process, list its process steps, and describe the function of each step. If steps need to be linked together, explain how they connect. Installing and Configuring Tivoli Data Warehouse has instructions for linking steps together. You need to tell them if they need to do that and what links are needed.

State whether the process is run once or repeatedly. Explain the circumstances in which it runs, for example, a process might be run once immediately after installing the warehouse pack to configure it. A repeated process might run nightly to extract data from an operational data store or periodically to delete data from the warehouse. If the process is specific to the central data warehouse ETL or the data mart ETL, explain that.

Explain how to recognize when the process fails, and what to do if that happens. A common cause of errors is nonvalid data in the source database. The ETL process creates exception tables. Explain the corrective action to take for common errors.

Run this process once after installing the warehouse pack; and on a regular scheduled basis (typically once a day during off-peak hours) after the running Tivoli Risk Manager central data warehouse ETL.

This p rocess has the following steps:

· HCV_m05_s010_Mart_Pre_Extract

Source: TWH_CDW – tables TWG.MsmtTyp, TWG.MSrc

Target: TWH_CDW – staging table HCV.Event_Metric

This step reinitializes the staging table HCV.Event_Metric in the central data warehouse, creating a row for each Tivoli Security Compliance Manager measurement type. The staging table has the same structure as the metric dimension table in the data mart database. This step by default enables minimum, maximum, average, and total metrics for all measurements. In the hourly fact table, all measurement values for any hour are the same, but in the daily, weekly, and monthly fact tables, the minimum, maximum, and average values might be different from the total value.

· HCV_m05_s020_Extract

Source: TWH_CDW – staging table HCV.Event_Metric, private table HRM.ClassCatDesc, TWG.Msmt, TWG.Comp, TWG.CompAttr tables

Target: TWH_MART – translation dimension tables HCV.T_Event_Metric, HCV.T_Dst_Host, HCV.T_Src_Host, and staging tables HCV.Stage_ClassCat, HCV.Stage_F_Event_Hour

This step adds new Tivoli Security Compliance Manager violation event data from the central data warehouse to staging tables and translation dimension tables in the Tivoli Security Compliance Manager data mart. The staging tables are dropped and re-created each time this step is run. The translation dimension tables are permanent and have a structure exactly the same as their corresponding data mart dimension tables, but in addition they contain information to identify the central data warehouse (in a multi-central data warehouse environment) where the data originated, as well as the original component and measurement IDs. Translation dimension tables are used to track IDs from the original central data warehouse into the star schema so you can tell where the data actually came from when looking at a star schema.

The target staging and translation dimension tables are the following:

HCV.Stage_ClassCat – contains all the event category descriptions defined in central data warehouse table HRM.ClassCatDesc.

HCV.T_Event_Metric – contains all Tivoli Security Compliance Manager measurement types expressed as data mart metrics. Currently, Tivoli Security Compliance Manager has only one metric – Event Count.

HCV.T_Dst_Host – contains one record for each distinct destination host in all the processed Tivoli Security Compliance Manager violation events. Each record maps from an IP_HOST, IP_INTERFACE or HRM_HOST component in the central data warehouse. Each record also stores the hostname and IP address values from the LAST_IP_ADDRESS or HRM_DST_HOSTNAME attributes, if they exist. Finally, the table contains the IP address for each host represented as an integer, plus strings for the subnets represented by each portion of the dotted decimal IP address. Three source database central data warehouse views and several staging/intermediate tables are used in this step to populate this table.

· HCV.T_Src_Host – contains one record for each distinct source host in all the processed Tivoli Security Compliance Manager events. Each record maps from a HRM_EVENT component, and its corresponding HRM_SRC_HOSTNAME or HRM_SRC_IPADDR attribute, in the central data warehouse.

· HCV.Stage_F_Event_Hour – contains one record for each new measurement added to the central data warehouse. Each record contains foreign key integer fields pointing to the metric, event category, destination, and source host translation dimension tables.

Extract control is achieved by using the component and measurement IDs from the central data warehouse tables. The ExtCtl_To_IntSeq value is set to the maximum ID currently in the central data warehouse, as defined by an appropriate view. After the extraction is complete, this value is copied into ExtCtl_From_IntSeq to be used for the next extraction. The following table shows which tables and ID columns are used for extract control.

Target Tables (TWH_MART)	Source Tables (TWH_CDW)	Source Table Views (TWH_CDW)	Source Table Columns used for Extract Control (TWH_CDW)	Source Table Extract Control Views (TWH_CDW)
HCV.T_Event_Metric	HCV.Event_Metric	HCV.VD_Event_Metric	Metric_ID	HCV.VE_Event_Metric
HCV.T_Dst_Host	TWG.Comp, TWG.CompAttr	HRM.VD_Host_Dst_IP, HRM.VD_Host_Dst_NoIP, HRM.VD_Host_Dst_Name	Comp_ID	HRM.VE_Dst_Host
HCV.T_Src_Host	TWG.Comp, TWG.CompAttr	HRM.VD_Host_Src	Comp_ID	HRM.VE_Src_Host
HCV.Stage_F_Event_Hour	TWG.Msmt, TWG.Comp, TWG.CompAttr	HRM.VF_Stg_Evt_Hour	Msmt_ID	HRM.VE_Event_Hour

· HCV_m05_s030_Load

Source: TWH_MART – staging tables HCV.Stage_ClassCat, HCV.Stage_F_Event_Hour; translation dimension tables HCV.T_Event_Metric, HCV.T_Dst_Host, HCV.T_Src_Host

Target: TWH_MART – fact table HCV.F_Event_Hour; dimension tables HCV.D_ClassCat, HCV.D_Event_Metric, HCV.D_Dst_Host, HCV.D_Src_Host

This step loads the dimension tables and hourly fact table with Tivoli Security Compliance Manager data from the translation dimension tables and a staging fact table. Translation dimension tables are used to track IDs from the original central data warehouse into the star schema so you can tell where the data actually came from when looking at a star schema. Each insert uses 'where not is' logic to ensure that duplicate dimens ions or facts are not inserted.

· HCV_m05_s040_Rollup

Source: TWH_MART – tables HCV.Stage_F_Event_Hour, HCV.F_Event_Hour; TWH_MD – table IWH.STARSCHEMA

Target: TWH_MD – table RPI.SSUPDATED; TWH_MART – tables HCV.F_Event_Day, HCV.F_Event_Week, HCV.F_Event_Month

This step rolls up new hourly facts from the staging fact table into the daily, weekly, and monthly fact tables. Timestamps for daily, weekly, or monthly facts are converted from GMT to local time for Crystal Reports. If new data is rolled up for any of the above tables, the appropriate star schema (hourly, daily, weekly, monthly) is indicated as updated in table RPI.SSUPDATED, which enables any canned reports based on the updated schema t o be automatically regenerated.

· HCV_m05_s050_Prune

Source: TWH_MART – table HCV.Prune_Mart_Control

Target: TWH_MART – tables HCV.F_Event_Hour, HCV.F_Event_Day, HCV.F_Event_Week, HCV.F_Event_Month, HCV.Prune_Mart_Log

This step deletes facts from the hourly, daily, weekly, and monthly fact tables if the fact date is older than the prune control values. Prune control values are held in table HCV.Prune_Mart_Control and can be adjusted by the customer as desired. For the hourly fact table, adjustment is made for GMT but the other fact tables are in local time. The number of records deleted from each fact table is stored in permanent table HCV.Prune_Mart_Log.

7 Central data warehouse information

This section is in landscape mode on purpose. The tables are too wide for portrait presentation.

This section provides the heart of the application integration information-how the application's operational data maps to the data being stored in the central data warehouse. Information developers should work closely with developers to provide complete and meaningful information. Detailed data descriptions are crucial to effective data sharing.

Information that is saved in the central data warehouse will be visible to the customer in reports, in the Tivoli Service Level Advisor (TSLA) GUI, and other tools. The customer should be able to look at a component name (Comp_Nm) and have some clue what resource is being measured.

While the content of the tables changes from application to application, most applications will have data in all of the tables shown in this section. All columns might not be used and therefore do not contain any information.

Refer to the Tivoli Risk Manager Warehouse Enablement Pack documentation for a description of how the data is stored in the central data warehouse.

8 Data mart schema information

Complete this section if your application provides data marts and reports. Otherwise, state “This warehouse pack does not provide data marts.”

If your warehouse pack creates data marts in another format (such as Cognos cubes), describe them here. If the report information is not relevant to your product, you can delete those sections.

The following sections contain the definition of star schemas, metric dimension tables, and data marts provided with the warehouse pack. This section is intended primarily for report designers and warehouse pack creators. For information about reports, see Section 3, Reports on page 10.

Shaded central data warehouse columns indicate values that source applications must translate and deliver corresponding Java resource bundles. Internationalization of data is described in Enabling an Application for Tivoli Data Warehouse.

Shaded columns in the following tables are translated. These columns are also marked with an asterisk (*) after the column name.

8.1 Data mart HCV_TWH_MART data mart

This data mart uses the following star schemas:

· HCV Hourly Security Compliance Manager event star schema

· HCV Daily Security Compliance Manager event star schema

· HCV Weekly Security Compliance Manager event star schema

· HCM Monthly Security Compliance Manager event star schema

8.2 Star schemas

Before using this section, read about the star schemas in Enabling an Application for Tivoli Data Warehouse. That document defines the content of each table and explains the relationships between the tables in this document.

The warehouse pack provides the following star schemas.

8.2.1.1 HCV Hourly Tivoli Security Compliance Manager event star schema

Repeat this section for each star schema.

The following table defines the star schema. The description of the star schema is translated.

Description of star schema (in IWH_STARSCHEMA)	Tivoli Security Compliance Manager Event Hourly Data
Name of fact table	HCV.F_Event_Hour
Name of metric dimension table	HCV.D_Event_Metric
Names of other dimension tables	HCV.D_Dst_Host
	HCV.D_Src_Host
	HCV.D_ClassCat

Description of star schema (in IWH_STARSCHEMA)	Tivoli Security Compliance Manager Event Daily Data
Name of fact table	HCV.F_Event_Day
Name of metric dimension table	HCV.D_Event_Metric
Names of other dimension tables	HCV.D_Dst_Host
	HCV.D_Src_Host
	HCV.D_ClassCat

Description of star schema (in IWH_STARSCHEMA)	Tivoli Security Compliance Manager Event Weekly Data
Name of fact table	HCV.F_Event_Week
Name of metric dimension table	HCV.F_Event_Metric
Names of other dimension tables	HCV.D_Dst_Host
	HCV.D_Src_Host
	HCV.D_ClassCat

Description of star schema (in IWH_STARSCHEMA)	Tivoli Security Compliance Manager Event Monthly Data
Name of fact table	HCV.F_Event_Month
Name of metric dimension table	HCV.F_Event_Metric
Names of other dimension tables	HCV.D_Dst_Host
	HCV.D_Src_Host
	HCV.D_ClassCat

8.2.1.2 Fact table HCV.F_Event_Hour

HCV Hourly Tivoli Security Compliance Manager Event Star Schema

You should expand the number of foreign key columns in this section to the amount needed for your application.

The following columns are used in the fact table:

· Metric_ID INTEGER

· Src_Host_ID INTEGER

· Dst_Host_ID INTEGER

· ClassCat_ID INTEGER

· Meas_hour TIMESTAMP

· Min_value DOUBLE

· Max_value DOUBLE

· Avg_value DOUBLE

· Total_value DOUBLE

· Sample_count DOUBLE

· Fact_ID INTEGER

· CDW_ID INTEGER

8.2.1.3 Fact table HCV.F_Event_Day

HCV Daily Tivoli Security Compliance Manager Event Star Schema

You should expand the number of foreign key columns in this section to the amount needed for your application.

The following columns are used in the fact table:

· Metric_ID INTEGER

· Src_Host_ID INTEGER

· Dst_Host_ID INTEGER

· ClassCat_ID INTEGER

· Meas_date TIMESTAMP

· Min_value DOUBLE

· Max_value DOUBLE

· Avg_value DOUBLE

· Total_value DOUBLE

· Sample_count DOUBLE

· Fact_ID INTEGER

· CDW_ID INTEGER

8.2.1.4 Fact table HCV.F_Event_Week

HCV Weekly Tivoli Security Compliance Manager Event Star Schema

You should expand the number of foreign key columns in this section to the amount needed for your application.

The following columns are used in the fact table:

· Metric_ID INTEGER

· Src_Host_ID INTEGER

· Dst_Host_ID INTEGER

· ClassCat_ID INTEGER

· Meas_date TIMESTAMP

· Min_value DOUBLE

· Max_value DOUBLE

· Avg_value DOUBLE

· Total_value DOUBLE

· Sample_count DOUBLE

· Fact_ID INTEGER

· CDW_ID INTEGER

8.2.1.5 Fact table HCV.F_Event_Month

HCV Monthly Tivoli Security Compliance Manager Event Star Schema

You should expand the number of foreign key columns in this section to the amount needed for your application.

The following columns are used in the fact table:

· Metric_ID INTEGER

· Src_Host_ID INTEGER

· Dst_Host_ID INTEGER

· ClassCat_ID INTEGER

· Meas_date TIMESTAMP

· Min_value DOUBLE

· Max_value DOUBLE

· Avg_value DOUBLE

· Total_value DOUBLE

· Sample_count DOUBLE

· Fact_ID INTEGER

· CDW_ID INTEGER

8.3 Metric dimension tables

This section describes the metric dimension tables used by the star schemas in the warehouse pack. Shaded columns indicate text that is translated. These column headings are also marked with an asterisk (*).

8.3.1 HCV.D_Event_METRIC

Repeat this section for each metric dimension table in the star schemas for your warehouse pack.

Metric_ID INTEGER	*Met_Category VARCHAR (254)**	*Met_Desc VARCHAR (254)**	*Met_Name VARCHAR (254)**	*Met_Units VARCHAR (254)**	Min_Exists CHAR (1)	Max_Exists CHAR (1)	Avg_Exists CHAR (1)	Total Exists CHAR (1)	*Msrc_Nm VARCHAR (254)**
1	N/A	Number of Tivoli Security Compliance Manager Violation Events	Event Count	QTY	Y	Y	Y	Y	IBM Tivoli Security Compliance Manager
* This column is translated.

Metric_ID INTEGER

Met_Category * VARCHAR (254)

Met_Desc * VARCHAR (254)

Met_Name * VARCHAR (254)

Met_Units * VARCHAR (254)

Min_Exists CHAR (1)

Max_Exists CHAR (1)

Avg_Exists CHAR (1)

Total Exists CHAR (1)

Msrc_Nm * VARCHAR (254)

N/A

Number of Tivoli

Security Compliance

Manager

Violation Events

Event

Count

QTY

IBM Tivoli Security

Compliance Manager

* This column is translated.

8.4 Dimension tables

The following sections describe the dimension tables (other than metric dimension tables) used by the star schemas in the warehouse pack.

8.4.1 Dimension table HCV.D_CLASSCAT

Repeat this section for each dimension table other than a metric dimension table. Provide only displayable columns (that is, columns that are not a key and do not start with XQ).

The following columns are used in this dimension table:

· Class_Cat_ID INTEGER

· Class_Cat_Name VARCHAR(16)

· Class_Cat_Desc VARCHAR(64)

8.4.2 Dimension table HCV.D_DST_HOST

Repeat this section for each dimension table other than a metric dimension table. In this table, show only displayable columns (that is, columns that are not a key and do not start with XQ). Because there are typically many columns, this table is presented sideways.

This dimension table contains attributes for each host identified as the target of a Tivoli Security Compliance Manager event.

The following columns are defined in this dimension table.

· Comp_ID INTEGER not null – foreign key to component table

· Comp_Name VARCHAR(120)

· Customer_Name VARCHAR(120)

· Center_Name VARCHAR(120)

· Hostname VARCHAR(128)

· IP_Address VARCHAR(32)

· IP_Number BIGINT default 0 – for sorting by IP address

· IP_A_Subnet VARCHAR(32)

· IP_B_Subnet VARCHAR(32)

· IP_C_Subnet VARCHAR(32)

8.4.3 Dimension table HCV.D_SRC_HOST

This dimension table contains attributes for each host identified as the source of a Tivoli SCM event.

The following columns are defined in this dimension table.

· Comp_ID INTEGER not null – foreign key to component table

· Comp_Name VARCHAR(120)

· Hostname VARCHAR(128)

· IP_Address VARCHAR(32)