Fact Tables
Each data warehouse includes one or more fact tables. Central to a "star" or "snowflake" schema, a
fact table captures the data that measures the team's operations. Fact tables usually contain large
numbers of rows, especially when they contain one or more years of history for a large team
project.
A key characteristic of a fact table is that it contains numerical data (facts) that can be summarized
to provide information about the history of the operation of the organization. Each fact table also
includes a multipart index that contains, as foreign keys, the primary keys of related dimension
tables and which contain the attributes of the fact records. Fact tables should not contain
descriptive information or any data other than the numerical measurement fields and the index
fields that relate the facts to corresponding entries in the dimension tables.
Tracking History in the Fact Table
Work items and test results each involve facts that change over time. It is valuable to aggregate
information about these items and to view either the trend of the totals over time or the items as
they existed at a single point in time. The Team System data warehouse captures each revision of a
work item, or each run of a test in a manner that enables calculations in the OLAP cube to
aggregate information at any given point in time. The following table describes the two, integer
columns in the relational database that tracks the changes.
Column Description
Record Each time a change occurs to a record (for example, when the priority of a bug changes), two
Count records are written to the database. The first record, called a compensating record, sets the
Record Count column to -1, which cancels out, or compensates for the previous events in time.
The second record records the new values associated with the fact, and sets the record count to
1. In the cube, aggregating all of the records together between two points in time results
effectively in canceling out all but the latest record at that point in time. Record Count provides
the basis for showing running totals for a day.
State Because state changes are an important aspect upon which to report, each time the state of a
Change work item, or the outcome of a test result changes, a special flag, called “State Change Count” is
Count set to true. State Change Count provides the basis for showing activity for a day.
Tracking History in the Fact Table
Work items and test results each involve facts that change over time. It is valuable to aggregate
information about these items and to view either the trend of the totals over time or the items as
they existed at a single point in time. The Team System data warehouse captures each revision of a
work item, or each run of a test in a manner that enables calculations in the OLAP cube to
aggregate information at any given point in time. The following table describes the two, integer
columns in the relational database that tracks the changes.
Column Description
Record Each time a change occurs to a record (for example, when the priority of a bug changes), two
Count records are written to the database. The first record, called a compensating record, sets the
Record Count column to -1, which cancels out, or compensates for the previous events in time.
The second record records the new values associated with the fact, and sets the record count to
1. In the cube, aggregating all of the records together between two points in time results
effectively in canceling out all but the latest record at that point in time. Record Count provides
the basis for showing running totals for a day.
State Because state changes are an important aspect upon which to report, each time the state of a
Change work item, or the outcome of a test result changes, a special flag, called “State Change Count” is
Count set to true. State Change Count provides the basis for showing activity for a day.
