Fact tables

Fact tables store the data that is used to calculate the metrics in metric reports. Fact tables are populated only through ETL transforms. To identify a business object as a fact table, from the Data Modeler set the Externally Managed flag in the fact table business object definition.

Each fact table is implemented in the IBM TRIRIGA Application Platform as a special business object that has some or all of the following elements:

Table 1. Fact tables
Fact table element Description
Hierarchical dimensions Each hierarchical dimension is a locator field to a business object that belongs to a hierarchical module (for example, a Building, Service Cost Code, or City). For each hierarchical dimension, a corresponding hierarchy structure supports metric reports.
A hierarchical dimension can reference any or all business objects within a module. Be as specific as possible. Targeting a specific business object improves the granularity of your reporting.
Each hierarchical dimension must have a corresponding hierarchy structure defined. Hierarchy structures are used for drill paths in metric reports.
Non-hierarchical dimensions Each non-hierarchical dimension is either a list field or a locator field to a business object that belongs to a non-hierarchical module (for example, a Task or Person).
Numeric fact fields Numeric fact fields are standard numeric fields, including or excluding Unit of Measure (UOM) properties. Numeric fact fields can be characterized as one of the following types:
  • Additive – Can be summed across all dimensions.
  • Semi-additive – Can be summed only across some dimensions. For example, the total number of people for a building captured monthly cannot be summed quarterly, since doing so would not yield a total for the quarter, whereas it can be summed by geography. Therefore, this fact is non-additive over time.
  • Non-additive – Cannot be summed across any dimension. For example, a ratio is a non-additive fact, since you cannot sum a ratio. Also, fields that contain values from different grains are non-additive.
UOM fields Unit of measure (UOM) fields (except for Area fields) are captured in their local, entered, UOM.
Area fields Area fields are captured in both Imperial (for example, square feet) and metric (for example, square meters) values.
Currency fields Currency fields are captured by using the base currency. No currency conversion occurs.
Current time period The time period dimension is a special dimension that is used to identify the date/time period for which a single fact record is applicable. This is most likely the time period when the data was captured. For cases when the time period dimension is not used as a drill path or filter, the triCapturePeriodTX field must be populated to indicate the dimension that is used to indicate the capture period. If this field exists, the corresponding business object for that dimension should contain a field that is named triCurrentBL, which is used to flag those dimension records that reflect the current period. These records are then used to filter the result set for the metric report.
Fiscal period The fiscal period classification is used by the ETL process to define the capture period for fact records. This is the primary time period dimension in metric reports.
Because it is possible to have different fact tables that contain data that is based on different capture frequencies with a single record for each level within the hierarchy, each level can be flagged as the current period. For example, if a year/quarter/month hierarchy is created in the fiscal period classification, it is possible to identify the current year, current quarter, and current month. A special ETL job type provides workflow to keep this data synchronized.
As a general rule, all data in a single fact table should be captured at the same time period grain/level (year, quarter, month). If the time period grain/level is changed after data has been captured for a particular fact table, all data in that fact table must either be revised to the correct grain/level or truncated/removed.
Fact table business object To identify a business object as one that will have fact tables supporting it, select the Externally Managed radio button in the business object properties when creating the business object.
Tip: Do not delete or change any of the fact business objects, fact tables, or ETL scripts that are delivered with the standard TRIRIGA® software. Instead, to change an existing one, copy it, rename the copy, and tailor the copy to your needs.