User schema

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