IBM Tivoli Software IBM Tivoli Software

[ Bottom of Page | Previous Page | Next Page | Contents | Index ]


Data mart schema information

The following sections contain the definition of star schemas, metric dimension tables, and data marts provided with the warehouse pack. This Tivoli Enterprise Console(R) warehouse pack uses one star schema for all of the reports. The following sections contain the definition of the star schema. This chapter is intended primarily for report designers and warehouse pack creators. For information about reports, see Reports.

Although event correlation relationship information is stored in the central data warehouse, this information is not inserted into the Tivoli Enterprise Console 3.9 data mart.

EC2 Event data mart

This data mart uses the EC2 event star schema.

Star schema

Before using this section, read about the star schemas in Enabling an Application for Tivoli(R) 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.

Event star schema

The following table defines the star schema.

Description of star schema (in IWH_STARSCHEMA)
Name of fact tables
  • EC2.F_EVENT_PIT
  • EC2.F_EVENT_MSG
  • EC2.F_EVENT_ACL
  • EC2.F_EVENT_EXTSLOT
  • EC2.F_EVENT_EXTS_NM
Name of metric dimension table
  • EC2.D_EVTTYP_METRIC
Name of other dimension tables
  • EC2.D_SEVERITY
  • EC2.D_STATUS

The following data mart tables are used to create all of the predefined reports:

EC2.F_EVENT_PIT fact table

The EC2.F_EVENT_PIT table contains all of the events and base event Tivoli Enterprise Console attribute information for all events that are stored in the central data warehouse, except the acl, message attributes, and extended attributes.

Table 24. EC2.F_EVENT_PIT table
Column name Data type Description

Event_ID

BIGINT

This is the warehouse identifier for the event record. It is an incrementing counter in the warehouse.

EventTyp_ID

INTEGER

This is a foreign key relationship to the EC2.D_EVTTYP_METRIC table. It references the event class of the event.

EventStatus_ID

INTEGER

This is a foreign key relationship to the EC2.D_STATUS table. It references the event status.

EventSev_ID

INTEGER

This is a foreign key relationship to the EC2.D_SEVERITY table. It references the event severity.

Server_ID

INTEGER

Tivoli Enterprise Console server handle of the event.

Event_DtTm

TIMESTAMP

This is the time the original event occurred.

Repeat_Cnt

INTEGER

Repeat count of the event.

Source

VARCHAR(128)

Base attribute of Tivoli Enterprise Console event

Origin

VARCHAR(128)

Base attribute of Tivoli Enterprise Console event

Hostname

VARCHAR(255)

Base attribute of Tivoli Enterprise Console event

Adapter_Host

VARCHAR(255)

Base attribute of Tivoli Enterprise Console event

Administrator

VARCHAR(128)

Base attribute of Tivoli Enterprise Console event

Date_Event

VARCHAR(30)

Base attribute of Tivoli Enterprise Console event

Duration

DOUBLE

Base attribute of Tivoli Enterprise Console event

Msg_Index

INTEGER

Base attribute of Tivoli Enterprise Console event

Num_Actions

INTEGER

Base attribute of Tivoli Enterprise Console event

Credibility

INTEGER

Base attribute of Tivoli Enterprise Console event

Sub_Origin

VARCHAR(128)

Base attribute of Tivoli Enterprise Console event

Sub_Source

VARCHAR(128)

Base attribute of Tivoli Enterprise Console event

Msg_Catalog

VARCHAR(64)

Base attribute of Tivoli Enterprise Console event

Fqhostname

VARCHAR(255)

Base attribute of Tivoli Enterprise Console event

Centr_Cd

CHAR(6)

Center from where original event came.

Cust_ID

INTEGER

Customer from where original event came.

EC2.F_Event_MSG fact table

The EC2.F_Event_MSG table contains all of the messages for the events that are contained in the EC2.F_EVENT_PIT table.

Table 25. EC2.F_EVENT_MSG table
Column name Data type Description
Event_ID BIGINT

This is the warehouse identifier for the event record. It is an incrementing counter in the warehouse.

MSG VARCHAR(3500)

Base attribute of Tivoli Enterprise Console event

EC2.F_Event_ACL fact table

The EC2.F_Event_ACL table contains all of the access control lists (ACLs) for the events that are contained in the EC2.F_EVENT_PIT table.

Table 26. EC2.F_EVENT_ACL table
Column name Data type Description
Event_ID BIGINT

This is the warehouse identifier for the event record. It is an incrementing counter in the warehouse.

ACL VARCHAR(3500)

Base attribute of Tivoli Enterprise Console event

EC2.F_EVENT_EXTSLOT fact table

The EC2.F_EVENT_EXTSLOT table contains all the extended attribute values for the events that are contained in the EC2.F_EVENT_PIT table.

Table 27. EC2.F_EVENT_EXTSLOT table
Column name Data type Description

Event_ID

BIGINT

This is the warehouse identifier for the event record. It is an incrementing counter in the warehouse.

Extslot_ID

INTEGER

This is a foreign key relationship to the EC2.F_EVENT_EXTS_NM table. It references the extended attribute name.

slot_value

VARCHAR(3500)

This is the extended attribute value of the Tivoli Enterprise Console event.

EC2.F_EVENT_EXTS_NM fact table

The EC2.F_EVENT_EXTS_NM table contains all the extended attribute names for the events that are contained in the EC2.F_EVENT_PIT table.

Table 28. EC2.F_EVENT_EXTS_NM table
Column name Data type Description

Extslot_ID

INTEGER SEQUENCE

This is the primary key for this table.

Slot_name

VARCHAR(64)

This is the extended attribute name.

Metric dimension table

This section describes the metric dimension table that is used by the event star schema in the warehouse pack.

The EC2.D_EVTTYP_METRIC table contains the event types for each event class in the EC2.F_EVENT_PIT table. These event types are dynamically loaded into the central data warehouse by the central data warehouse ETL. The event type is the same as the Tivoli Enterprise Console event class in the Tivoli Enterprise Console event database.

Table 29. EC2.D_EVTTYP_METRIC table
Column name Data type Description

EventTyp_ID

INTEGER

This is the internal central data warehouse identifier for the event type, or Tivoli Enterprise Console event class.

EventTyp_Nm

VARCHAR(254)

This is the event type ( event class ) name.

Dimension tables

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

EC2.D_STATUS dimension table

The EC2.D_STATUS table contains the status for each of the events in the EC2.F_EVENT_PIT table. These statuses are loaded dynamically by the central data warehouse ETL. The following default status values that are shipped with the Tivoli Enterprise Console product are loaded by the EC2_m05_ETL2_Initialization_Process process:

Usage Notes:
  1. The EventStatus_ID is not the same value as the enumerated field in the Tivoli Enterprise Console event database. The numbers are different because Tivoli Enterprise Console event database values are not stored in the central data warehouse.
  2. The EC2_m05_ETL2_Initialization_Process process loads the base statuses and assigns a number to them to enable sorting in the reports. Any new statuses that you add receive an incrementing number and might not be in the order you expect. The table contains the translated strings for nine languages. Any statuses that you add have the same value in each field.
Table 30. EC2.D_STATUS table
Column name Data type Description

EventStatus_ID

INTEGER

This is an integer value that represents the status of the event. This is used as a foreign key relationship by the EC2.F_EVENT_PIT table to capture the appropriate translated value for the status.

StatusNm_En

VARCHAR(255)

This is the English status value.

StatusNm_De

VARCHAR(255)

This is the German status value.

StatusNm_Es

VARCHAR(255)

This is the Spanish status value.

StatusNm_Fr

VARCHAR(255)

This is the French status value.

StatusNm_It

VARCHAR(255)

This is the Italian status value.

StatusNm_Ja

VARCHAR(255)

This is the Japanese status value.

StatusNm_Ko

VARCHAR(255)

This is the Korean status value.

StatusNm_Pt

VARCHAR(255)

This is the Brazilian Portuguese status value.

StatusNm_CN

VARCHAR(255)

This is the simplified Chinese status value.

StatusNm_TW

VARCHAR(255)

This is the traditional Chinese status value.

EC2.D_SEVERITY dimension table

The EC2.D_SEVERITY table contains all of the severities for the events that stored in the EC2.F_EVENT_PIT table. These severities are dynamically loaded by the central data warehouse ETL; however, the severities are rearranged, so that proper sorting can be accomplished by the reports.

Usage Notes:
  1. The EventSev_ID is not the same value as the enumerated field in the Tivoli Enterprise Console event database. The numbers are different because Tivoli Enterprise Console event database values are not stored in the central data warehouse.
  2. The EC2_m05_ETL2_Initialization_Process process loads the base severities and assigns a number to them to enable sorting in the reports. Any new severities that you add receive an incrementing number and might not be in the order you expect. The table contains the translated strings for nine languages. Any severities that you add have the same value in each field. The following default severity values, listed in order of increasing severity, are shipped with the Tivoli Enterprise Console product and are loaded by the EC2_m05_ETL2_Initialization_Process process:
Table 31. EC2.D_SEVERITY table
Column name Data type Description

EventSev_ID

INTEGER

This is an integer value that represents the severity of the event. This is used as a foreign key relationship by the EC2.F_EVENT_PIT table to capture the appropriate translated value for the severity.

SevNm_En

VARCHAR(255)

This is the English severity value.

SevNm_De

VARCHAR(255)

This is the German severity value.

SevNm_Es

VARCHAR(255)

This is the Spanish severity value.

SevNm_Fr

VARCHAR(255)

This is the French severity value.

SevNm_It

VARCHAR(255)

This is the Italian severity value.

SevNm_Ja

VARCHAR(255)

This is the Japanese severity value.

SevNm_Ko

VARCHAR(255)

This is the Korean severity value.

SevNm_BR

VARCHAR(255)

This is the Brazilian Portuguese severity value.

SevNm_CN

VARCHAR(255)

This is the simplified Chinese severity value.

SevNm_TW

VARCHAR(255)

This is the traditional Chinese severity value.


[ Top of Page | Previous Page | Next Page | Contents | Index ]