1. Qube
2. A Primer for the pfx_dw Data Warehouse Schema

A Primer for the pfx_dw Data Warehouse Schema

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		  |
+---------------+------+------------+-------------+-----------+