SCM schema

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