The pfx_dw
schema is a fairly textbook "star schema", with things like jobs and worker usage in fact tables, and the various things that you might use in a SQL WHERE
clause in dimension tables.
The job dimension table, a typical "fact" table.
For example, to get reports about various jobs over time, you'll be querying the pfx_dw.job_fact table:
"*_sk" columns can be used to do INNER JOINs to a similarly named dimension table
Any column that is named with an _sk
suffix is a Synthetic Key that points to a corresponding dimension table, named with the part of the column before the _sk
; the dimension table will have a _dim
suffix in the name. This way, it's easy to write the JOIN
's, the column name is a clue to the dimension table, which will have a column of the same name. Almost every dimension table will consist of a *_sk
PRIMARY KEY
and a name column.
The jobstatus dimension table
The pfx_dw.jobstatus_dim table is one of the few exceptions to the normal dimension table structure; it provides a mapping between the integer and human-readable status values.
For example, the *user_sk* column can be used to do a SQL `INNER JOIN` to the `user_dim` table.
The time dimension table
The pfx_dw.time_dim table is provided so that you don't have to perform date/time operations on every row in a fact table (since they can run into the 100's of millions of rows), instead you do a SQL INNER JOIN
to it and use the values in the time_dim table in your WHERE
clause. The time_sk column in every fact table has an identical value in the time_dim
table which has a single row with a primary key time_sk. The time_sk
value is actually the unix epoch time in seconds:
```
```
mysql> SELECT
user.name
, time.year
, time.month_name
, status.status_char
, COUNT(*) as "job count"
FROM
job_fact AS fact
INNER JOIN
user_dim AS user
ON
fact.user_sk=user.user_sk
INNER JOIN
time_dim AS time
ON
fact.time_sk=time.time_sk
INNER JOIN
jobstatus_dim as status
ON
fact.jobstatus_sk=status.jobstatus_sk
WHERE
time.year = 2013
GROUP BY
user.name
, time.month
, status.status_int
ORDER BY
user.name
, time.month
,status.status_char
;
+---------------+------+------------+-------------+-----------+
| name | year | month_name | status_char | job count |
+---------------+------+------------+-------------+-----------+
| Administrator | 2013 | January | failed | 4 |
| bar | 2013 | July | complete | 1 |
| foo | 2013 | July | complete | 2 |
| foobar | 2013 | July | complete | 1 |
| foobar | 2013 | September | complete | 2 |
| fubar | 2013 | July | complete | 1 |
| jburk | 2013 | March | complete | 1 |
| jburk | 2013 | May | complete | 100 |
| jburk | 2013 | May | failed | 11 |
| jburk | 2013 | May | killed | 12 |
| jburk | 2013 | June | complete | 4 |
| jburk | 2013 | June | failed | 20 |
| jburk | 2013 | July | complete | 134 |
| jburk | 2013 | July | failed | 33 |
| jburk | 2013 | July | killed | 53 |
| jburk | 2013 | August | complete | 67 |
| jburk | 2013 | August | failed | 25 |
| jburk | 2013 | August | killed | 48 |
| jburk | 2013 | September | complete | 38 |
| jburk | 2013 | September | failed | 17 |
| jburk | 2013 | September | killed | 13 |
| jburk | 2013 | October | complete | 450 |
| jburk | 2013 | October | failed | 61 |
| jburk | 2013 | October | killed | 49 |
| jburk | 2013 | November | complete | 38 |
| jburk | 2013 | November | failed | 12 |
| jburk | 2013 | November | killed | 9 |
| jburk | 2013 | December | complete | 3812 |
| jburk | 2013 | December | failed | 627 |
| jburk | 2013 | December | killed | 28 |
| perforce | 2013 | January | failed | 46 |
| perforce | 2013 | January | killed | 82 |
| perforce | 2013 | February | complete | 3 |
| perforce | 2013 | February | killed | 1 |
| root | 2013 | January | complete | 24 |
| root | 2013 | January | failed | 6 |
| root | 2013 | January | killed | 1 |
| root | 2013 | February | complete | 34 |
| root | 2013 | February | killed | 3 |
| root | 2013 | March | complete | 7 |
| root | 2013 | April | complete | 9 |
| root | 2013 | April | failed | 4 |
| root | 2013 | April | killed | 4 |
| root | 2013 | May | complete | 6 |
| root | 2013 | May | killed | 1 |
| root | 2013 | June | complete | 1 |
| root | 2013 | July | complete | 7 |
| root | 2013 | July | failed | 1 |
| root | 2013 | July | killed | 3 |
| root | 2013 | September | complete | 1 |
| root | 2013 | September | failed | 1 |
| root | 2013 | December | complete | 2 |
| shinya | 2013 | January | complete | 4 |
| shinya | 2013 | January | failed | 7 |
| shinya | 2013 | January | killed | 12 |
| shinya | 2013 | February | complete | 60 |
| shinya | 2013 | February | failed | 2 |
| shinya | 2013 | February | killed | 7 |
| shinya | 2013 | March | complete | 2 |
| shinya | 2013 | March | failed | 2 |
| shinya | 2013 | March | killed | 3 |
| shinya | 2013 | April | complete | 16 |
| shinya | 2013 | April | killed | 4 |
| shinya | 2013 | May | killed | 2 |
+---------------+------+------------+-------------+-----------+
64 rows in set (0.42 sec)
```