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.
Fact Tables in the Data Cube
The following table shows the fact tables contained in the Team System relational database.
| Table | Description |
|---|---|
Build Changeset |
Contains one row for each changeset included in this build but not included in a previous build of the same type. This enables build information to be correlated with check-ins, and also with the work items that are associated with the check-ins. |
Build Coverage |
Contains one row for each time code when metrics were gathered for a build. |
Build Details |
Contains one row for each time a build is performed. |
Build Project |
Contains one row for each time a project within a build is built. |
Code Churn |
Contains one new row for each revision of any file. |
Current Work Item |
Contains one row for each work item currently in the system. |
Load Test Counter |
Summary information for each performance counter value read for each load test executed. Contains the counter ID, value, and the computer from which it came. |
Load Test Details |
Contains one row for each time a load test is executed. |
Load Test Page Summary |
Contains one row for each URL visited during each load test. Summarizes information for each top-level page, but does not include detailed information for dependent requests; for instance, images. |
Load Test Summary |
Contains one row for each test run as part of a load test. Contains the number of times the test ran, how many times it failed, how long it took to run on average, and so forth. |
Load Test Transaction |
Contains the average time for each transaction. For example, if unit tests are run under load, timers in the tests are reported here as the average time taken for each transaction. |
Run Coverage |
Contains one row for each time a test run is executed that collects code coverage metrics. |
Test Result |
Contains one row for each execution of each test. Contains the outcome of the test, start and end times, and metadata about the test (category, CSS nodes, and so forth.) |
Work Item Changeset |
Contains one row for each relationship between a work item revision and a changeset. |
Work Item History |
Versioned file of work items using transition count and record count to aggregate information at a point in time. |
There are five fields that appear in all fact tables:
ID
Logical Tracking ID
LastUpdatedTime
LastUpdatedBy
TrackingId
In addition, there are a set of foreign keys that link into 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 Count |
Each time a change occurs to a record (for example, when the priority of a bug changes), two 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 Change Count |
Because state changes are an important aspect upon which to report, each time the state of a work item, or the outcome of a test result changes, a special flag, called “State Change Count” is set to true. State Change Count provides the basis for showing activity for a day. |
See Also
Concepts
Fact Table Relationships
Dimension Tables