SCM schema

Query the SCM schema to obtain useful commit information in the SCM tables detailed here in a schema diagram.

SCM schema diagram

Description of SCM 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.
activity_dimension
Conformed dimension that stores the activity or transaction names for various activities being tracked.
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.
repository_dimension
Used for storing repository attributes and is a "slowly changing dimension of type 1."
scm_transaction_fact
The fact table for SCM activities with "transaction" granularity. TeamForge inserts a row in this table for every SCM activity that it processes in a transaction.
  • TeamForge object id , if available.
  • Number of files added, deleted, modified, moved, copied, if applicable.

Sample queries

You can obtain useful SCM information by querying the SCM database. For example:
  • Number of SCM commits, sorted by date:
    select b.date_of_trans as Date, 
                count(a.scm_transaction_fact_key) as NumCommits
                from scm_transaction_fact a, date_dimension b
                where a.trans_date_key=b.date_key
                group by b.date_of_trans
    
  • Number of SCM commits, with quarterly trend:
     select 'Q'||b.quarter as Quarter, 
                count(a.scm_transaction_fact_key) as NumCommits
                from scm_transaction_fact a, date_dimension b
                where a.trans_date_key=b.date_key
                group by b.quarter 
    
  • List of users who made commits.
     select b.username as UserName, 
                count(a.scm_transaction_fact_key) as NumCommits
                from scm_transaction_fact a, user_dimension b
                where a.user_key=b.user_key
                group by b.username
    
  • Project-wise commit data:
     select b.id as ProjectId, b.title as ProjectName, 
                count(a.scm_transaction_fact_key) as NumCommits
                from scm_transaction_fact a, project_dimension b
                where a.project_key=b.project_key
                group by b.id, b.title
    
  • Commits by date, in a specific project:
     select c.date_of_trans as Date, b.id as ProjectId, b.title as ProjectName, 
                count(a.scm_transaction_fact_key) as NumCommits
                from scm_transaction_fact a, project_dimension b, date_dimension c
                where a.project_key=b.project_key and a.trans_date_key=c.date_key
                  and b.id='proj1008'
                group by c.date_of_trans, b.id, b.title