Historical database schema

The Historical database tables are divided into two types. They are the static tables that are created at WebSphere® Business Monitor installation time, and the dynamic tables that are created for each imported business measures model.

The description of both types of the Historical database tables and the corresponding mapping of each column to the business measures model is listed in the following tables.
Note:
  • Nullable: means this column can either accept or not accept null values
  • Descriptor: describes the mapping between a column and the business measures model definitions. Each column does not have to have a descriptor.

Static database tables

DIM_TIME

The time dimension table.

Column name Column Type Column Description Nullable
SURROGATE_KEY INTEGER This is the primary key N
DAY SMALLINT Represents the day N
MONTH SMALLINT Represents the month N
YEAR INTEGER Represents the year N

Dynamic database tables

The Historical database implements a star schema structure with a central Fact table surrounded by multiple Dimension "leaf" tables. The fact table is similar to the context table in the State and Runtime databases. There is one star for the context, and one for the context's corresponding activities. For example, there can be one context instance table, and one activity instance table per context in the State and Runtime databases.

The Context Fact table

Naming convention: FCT_<machine generated name of context>

The columns that always exist are:

Column name Column Type Column Description Nullable
MCI_MCIID DECIMAL(19,0) Unique identifier of the activity instance and also the primary key of the table. N
PARENT_MCIID DECIMAL(19,0) Unique identifier of the parent process instance, if any. Y
SK_<machine generated name of the dimension> INTEGER Foreign key pointing to a dimension table. FK relationship is defined. One of these columns is defined for each dimension present in the context. Y
GMT_<machine generated name of metric> TIMESTAMP A timestamp value used to store the GMT time value of any timestamp metric data types. (This column is created only when the time metric is marked as a Dimension. When Time metrics are marked as dimensions, they are only stored with day, month, year granularity, so this column provides the ability to see the exact time value of these metrics.) Y

The following three column types are used when metrics are marked as Facts (not dimensions).

Column types that are used when metrics are marked as Facts
Column name Column Type Column Description Nullable
M_<machine generated name> Data type varies with the data type defined in the business measures model. Used to represent a metric or Keydefinition value. Y
C_<machine generated name> BIGINT Used to represent counters. Y
T1_<machine generated name> BIGINT Used to represent accumulated time for timers. (Timers are represented with a single column in the Historical database; they use multiple columns in the State database.) Y
The Dimension table

Zero or more dimension tables will be defined for each context, depending how many dimensions the context defines. Typically there will be at least a Time dimension.

Naming Convention: DIM_<machine generated name of dimension>

The columns which are always defined:

Column name Column Type Column Description Nullable
SURROGATE_KEY INTEGER Machine generated primary key value for this dimension row. PK is defined. N

These are the columns that are definition based. The dimension table contains a column for each metric that is defined as part of this dimension.

The columns that are definition based
Column name Column Type Column Description Nullable
M_<machine generated name> Data type varies with the data type defined in the business measures model. Used to represent a metric or key definition value. Y
C_<machine generated name> BIGINT Used to represent counters. Y
T1_<machine generated name> BIGINT Used to represent accumulated time for timers. (Timers are represented with a single column in the Historical database, whereas they use multiple columns in the State database). Y

The activity star schema follows the same conventions, except that the tables are named AFC_ and ADM_ respectively.


Copyright IBM Corporation 2005, 2006. All Rights Reserved.