Tracker schema

Query the tracker schema to obtain useful information in the tracker database tables detailed here in a schema diagram.

TeamForge constructs the state or field values of the artifact during each update. The schema addresses both activity queries and total count queries.

TeamForge includes information for the activities in fixed fields, default artifact fields, and flex fields. This information includes artifact create, move, update for fixed-value changes, delete, open_to_close, and close_to_open.

Tracker schema diagram

Description of tracker schema

etl_job
Used to track the ETL run status. There is one record per ETL run for a job, for example, Tracker ETL or User ETL. etl_job has a 1-to-many relationship with audit_dimension since a job may update more than one fact table. All report generation queries must "join" the etl_job table with the condition etl_job.status=1, thereby discarding data from incomplete ETL runs.
audit_dimension
Holds metadata about fact table records. There is one record per fact table for an ETL run.
date_dimension
Conformed dimension used for all transaction times.
user_dimension
Used for string user attributes and is a "slowly changing dimension of type 2 (SCD-2)." is_super_user, status, and license_type are the SCD-2 fields.
project_dimension
Used for storing project attributes and is a "slowly changing dimension of type 2 (SCD-2)." is_deleted and project_access_level are the SCD-2 fields.
pf_dimension
The planning folder dimension for storing planning folder attributes. Use this table to generate reports without planning folder hierarchy.
pf_bridge

A table for the planning folder hierarchy containing end-of-day status. pf_bridge is a "slowly changing dimension of type 2 (SCD-2)." You must limit queries to a given parent planning folder while joining with the pf_bridge table. Use pf_bridge to generate reports around planning folder hierarchy by joining with parent tables such as artifact_daily_snapshot_fact or artifact_transaction_fact.

Joining pf_bridge with artifact_transaction_fact generates correct results only if end-of-day in queries is set to "12:00 a.m.". While formulating queries with pf_bridge, please note:
  • parent and child fields — Contain values from pf_dimension.pf_key; a depth of 0 indicates self.
  • leaf field — Is true if the child is a leaf node, otherwise false. Every planning folder will have an entry here with a depth of 0; every parent planning folder will have entries for all of its children, recursively, up to the leaf node of all branches.
  • effective_from and effective_till fields — Indicate the period when the parent-child relationship is correct, and can be used in queries to get the hierarchy for a specified time period.
tracker_dimension
Used for storing tracker attributes and is a "slowly changing dimension of type 2 (SCD-2)." is_deleted is the changing field that act as a filter for reports .
artifact_dimension
Used for storing artifact data and is a "slowly changing dimension of type 1."
group_dimension
Used for holding values of the TeamForge tracker field "group". group_dimension has values for all artifacts from all Trackers.
status_dimension
Used for holding values of the TeamForge tracker field "status". The status_value_class field represents the meta-status of an artifact and has values "Open" and "Close". status_dimension has values for all artifacts from all Trackers.
category_dimension
Used for holding values of the TeamForge tracker field "category". category_dimension has values for all artifacts from all Trackers.
customer_dimension
Used for holding values of the TeamForge tracker field "customer". customer_dimension has values for all artifacts from all Trackers.
release_dimension
Used for holding values of the TeamForge tracker fields "Reported in Release" and "Fixed in Release". release_dimension has values for all artifacts from all Trackers.
artifact_transaction_fact
Every change in fixed or default artifact field values, or to project or tracker artifacts, results in a row inserted to artifact_transaction_fact. Changes to flex-field values, adding comments, attachments, and so on do not add a row to the table. The artifact_dimension.date_last_modified field has the time in the source tracker at the time of the ETL run. artifact transaction fact can be used to generate reports around activities such as create, update, delete and move, and for intra-day reports. artifact_transaction_fact has a "transaction" granularity.
artifact_daily_snapshot_fact
An aggregate table that holds the daily snapshot data or end-of-day status. artifact_daily_snapshot_fact can be used to generate reports for artifact close & re-open counts. It is recommended to use this table for end-of-day reports as it has fewer rows compared to artifact_transaction_fact. artifact_daily_snapshot_fact has a "daily" granularity.
team_dimension
A dimension for holding values of Teams. This is a "slowly changing dimension of type 2 (SCD-2)". Use this table to generate reports without team hierarchy.
team_membership_dimension
This is more or less a factless fact table that holds the changes in terms of Team memberships. This is a "slowly changing dimension of type 2 (SCD-2)".
team_bridge

A table for handling team hierarchy containing end-of-day status. team_bridge is a "slowly changing dimension of type 2 (SCD-2)." You must limit queries to a given parent team while joining with the team_bridge table. Use team_bridge to generate reports around team hierarchy (ex: a report on all artifacts assigned to Team 1 and its child teams) by joining with parent tables such as artifact_daily_snapshot_fact or artifact_transaction_fact.

Joining team_bridge with artifact_transaction_fact generates correct results only if end-of-day in queries is set to "12:00 a.m.". While formulating queries with team_bridge, please note:
  • parent_surrogate_id and child_surrogate_id fields — Contain values from team_dimension.surrogate_id; a depth of 0 indicates self.
  • leaf field — Is true if the child is a leaf node, otherwise false. Every team will have an entry here with a depth of 0; every parent team will have entries for all of its children, recursively, up to the leaf node of all branches .
  • effective_from and effective_till fields — Indicate the period when the parent-child relationship is correct, and can be used in queries to get the hierarchy for a specified time period.
    Note: team_dimension is a "slowly changing dimension of type 2". So, make sure that the JOIN clause in queries that join team_bridge and team_dimension includes the date and time as well.
flex_field_dimension
Used for storing information about flex fields and is a "slowly changing dimension of type 2 (SCD-2)".
flex_field_value_dimension
Used for storing information about flex field values and is a "slowly changing dimension of type-2 (SCD-2)".
artifact_dependency_dimension
Used for storing relationship between artifacts and is a "slowly changing dimension of type-2 (SCD-2)".
artifact_dependency_bridge
A table for the artifacts hierarchy containing end-of-day status. This is a "slowly changing dimension of type-2 (SCD-2). Use this table to generate reports around artifact hierarchy.
artifact_flex_fields
A table to bind the flex field updates on artifacts into a well formed XML, which stores the 'field key', 'value' and 'type' of the flex field.

Schema diagram for XML to non-XML conversion

Description of schema used for XML to non-XML conversion

flex field dimension
Used for storing information about flex fields and is a "slowly changing dimension of type 2 (SCD-2)".
flex field value dimension
Used for storing information about flex field values and is a "slowly changing dimension of type-2 (SCD-2)".
artifact flex fields
A table to bind the flex field updates on artifacts into a well formed XML, which stores the 'field key', 'value' and 'type' of the flex field.
artifact daily snapshot fact
An aggregate table that holds the daily snapshot data or end-of-day status. artifact_daily_snapshot_fact can be used to generate reports for artifact close & re-open counts. It is recommended to use this table for end-of-day reports as it has fewer rows compared to artifact_transaction_fact. artifact_daily_snapshot_fact has a "daily" granularity.
artifact transaction fact
Every change in fixed or default artifact field values, or to project or tracker artifacts, results in a row inserted to artifact_transaction_fact. Changes to flex-field values, adding comments, attachments, and so on do not add a row to the table. The artifact_dimension.date_last_modified field has the time in the source tracker at the time of the ETL run. artifact transaction fact can be used to generate reports around activities such as create, update, delete and move, and for intra-day reports. artifact_transaction_fact has a "transaction" granularity.
tracker dimension
Used for storing tracker attributes and is a "slowly changing dimension of type 2 (SCD-2)." is_deleted is the changing field that act as a filter for reports .
ss_flex_fields_bridge
A table to hold details about single select flex field assigned to an artifact. To get the details of the assigned artifact, the "ss_flex_fields_bridge" table must be joined with the "artifact_transaction_fact" table and the "artifact_dimension" table.
user_flex_fields_bridge
A table to hold details about user flex field assigned to an artifact. To get the details of the assigned artifact, the "user_flex_fields_bridge" table must be joined with the "artifact_transaction_fact" table and the "artifact_dimension" table.
text_flex_fields_bridge
A table to hold details about text flex field assigned to an artifact. To get the details of the assigned artifact, the "text_flex_fields_bridge" table must be joined with the "artifact_transaction_fact" table and the "artifact_dimension" table.
date_flex_fields_bridge
A table to hold details about date flex field assigned to an artifact. To get the details of the assigned artifact, the "date_flex_fields_bridge" table must be joined with the "artifact_transaction_fact" table and the "artifact_dimension" table.
ms_flex_fields_bridge
A table to hold details about multi select flex field assigned to an artifact. To get the details of the assigned artifact, the "ms_flex_fields_bridge" table must be joined with the "artifact_transaction_fact" table and the "artifact_dimension" table.

Sample queries

You can obtain useful tracker information by querying the tracker database. For example:
  • Number of artifacts created in a tracker, sorted by date:
    SELECT b.date_of_trans, count(a.artifact_key) 
            FROM artifact_transaction_fact a, date_dimension b, tracker_dimension c 
            WHERE a.trans_date_key=b.date_key and a.tracker_key=c.tracker_key 
            and a.activity='Create' and c.title='Tracker-1' 
            and b.date_of_trans >= '2011-10-31' and b.date_of_trans <= '2011-11-07' 
            GROUP BY b.date_of_trans 
            ORDER BY b.date_of_trans
  • Number of artifacts created in a tracker, sorted by date and priority:
    SELECT b.date_of_trans, d.title as Tracker, 'P'||a.priority as Priority,c.id ArtifactId 
            FROM artifact_transaction_fact a, date_dimension b, artifact_dimension c, tracker_dimension d 
            WHERE a.trans_date_key=b.date_key and a.artifact_key=c.artifact_key and a. tracker_key=d.tracker_key 
            and a.activity='Create' and d.title='Tracker-1' 
            and b.date_of_trans >= '2011-10-31' and b.date_of_trans <= '2011-11-07' 
            ORDER BY b.date_of_trans, d.title, Priority
  • Number of artifacts created on a particular day in a particular planning folder:
    SELECT b.date_of_trans, c.id ArtifactId, c.title 
            FROM artifact_transaction_fact a, date_dimension b, artifact_dimension c, pf_dimension d, pf_bridge e 
            WHERE a.trans_date_key=b.date_key and a.artifact_key=c.artifact_key and a.pf_key=e.child 
            and d.pf_key=e.parent and a.activity='Create' and d.title='Pf-1' 
            and '2011-10-31' >= e.effective_from and '2011-10-31' < e.effective_till 
            and b.date_of_trans = '2011-10-31' 
            ORDER BY b.date_of_trans
  • Number of closed tracker artifacts, sorted by day:
    SELECT b.date_of_trans, count(a.artifact_key) 
            FROM artifact_daily_snapshot_fact a, date_dimension b, tracker_dimension c 
            WHERE a.trans_date_key=b.date_key and a.tracker_key=c.tracker_key and a.closed_today_flag='true' 
            and c.title='Tracker-1' and b.date_of_trans >= '2011-10-31' and b.date_of_trans <= '2011-11-07' 
            GROUP BY b.date_of_trans 
            ORDER BY b.date_of_trans
  • List of artifacts in the "Open" state, sorted by day:
    SELECT a.date_of_trans, c.id, c.title 
            FROM date_dimension a inner join artifact_daily_snapshot_fact b 
            on a.date_of_trans >= date(b.effective_from) and a.date_of_trans < date(b.effective_till) 
            inner join artifact_dimension c on b.artifact_key=c.artifact_key 
            inner join status_dimension d on b.status_key=d.status_key 
            WHERE d.status_value_class='Open' and a.date_of_trans >= '2011-10-31' 
            and a.date_of_trans <= '2011-11-02' 
            ORDER BY a.date_of_trans
  • List of artifacts assigned to a team (including child teams) on a given day:
    select ad.id as artifact_id
           from artifact_transaction_fact fact 
           inner join date_dimension dd on (dd.date_of_trans + 1) between fact.effective_from and fact.effective_till
           inner join team_dimension child_td on child_td.team_key = fact.team_key
           inner join team_bridge tb on tb.child_surrogate_id = child_td.surrogate_id and 
                 (dd.date_of_trans + 1) between tb.effective_from and tb.effective_till
           inner join team_dimension parent_td on tb.parent_surrogate_id = parent_td.surrogate_id and 
                 (dd.date_of_trans + 1) between parent_td.effective_from and parent_td.effective_till
           inner join artifact_dimension ad on ad.artifact_key = fact.artifact_key
           inner join etl_job ej on ej.job_key = fact.job_key and ej.status = 1
           where dd.date_of_trans = '2015-01-01' and parent_td.id = 'team1002'
           order by artifact_id
Parent topic: Datamart schemas