User schema

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

User schema contains user login information; TeamForge captures one fact row for each user that is logged in during the day.

User schema diagram

Description of user 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.
activity_dimension
Conformed dimension that stores the activity or transaction names for various activities being tracked.
user_transaction_fact
A fact-less fact table with user data of "daily" granularity.

Sample queries

You can obtain useful user information by querying the user database, and further refine the results by using filters on the "date", "user type" (admin or non), "status", and "license type" fields. For example:
  • Number of users who are logged in, by day, over a period of time:
    SELECT c.date_of_trans as Date, count(distinct(b.id)) as NumUsers
                
    FROM user_transaction_fact a, user_dimension b, date_dimension c, etl_job d
                
    WHERE a.user_key=b.user_key and a.trans_date_key=c.date_key and a.job_key=d.job_key
                    
    and d.status=1 and c.date_of_trans >= '2012-12-17' and c.date_of_trans <= '2012-12-21'
                
    GROUP BY c.date_of_trans
    
  • List of users who have logged in:
    SELECT c.date_of_trans as Date, b.username as UserName
            FROM user_transaction_fact a, user_dimension b, date_dimension c, etl_job d
            WHERE a.user_key=b.user_key and a.trans_date_key=c.date_key and a.job_key=d.job_key
                and d.status=1 and c.date_of_trans >= '2012-12-17' and c.date_of_trans <= '2012-12-21'
            GROUP BY c.date_of_trans, b.username