Example fact table and associated dimensions

The fact and dimension tables are built by using the star schema method of data warehouse design. They are stored in the same database repository as the TRIRIGA® applications.

The following diagram shows an example of one of the preconfigured fact tables in TRIRIGA Workplace Performance Management:

Sample Fact and Associated Dimensions

The diagram shows the space fact with five facts, including space capacity, total headcount, remaining capacity, space area, and allocated area. The space fact also references seven dimensions, including space, building, location, geography, space class, building class, and building tenure. The dimensions in the fact table link the facts to the corresponding dimension tables. Some dimensions are hierarchical, such as location and geography and others are not, such as space and building.

Flat hierarchy tables are used to identify the children of a selected business object. Flat hierarchy tables enable the metric query engine to browse hierarchical modules, business objects, and classifications.

The following table shows an example of a flat hierarchy that is based on geography:
Table 1. Geography Flat Hierarchy Example
SPEC_ID Level Number Level 1 SPEC_ID Level 2 SPEC_ID Level 3 SPEC_ID Level 4 SPEC_ID
World 1 World N/A N/A N/A
North America 2 World North America N/A N/A
EMEA 2 World EMEA N/A N/A
APAC 2 World APAC N/A N/A
United States 3 World North America United States N/A
Canada 3 World North America Canada N/A
Nevada 4 World North America United States Nevada
Texas 4 World North America United States Texas

Using the example, if you wanted to identify all of the geographies that are children of North America, you first look up the North America SPEC_ID in the first column of the geography flat example table. Then, you might use the level number for North America, which is 2, to determine the filter column. By using the SPEC_ID and level number, you can identify all the geographies that are children, grandchildren, or any level below North America.