Employee Performance Monitoring

Hi, I'm somewhat new to dimensional modeling.  I've been reading the Kimball DW toolkit series over the last year and working on a project at work.  Going through the bus matrix, my group is having a difficult time determining the grain for employee performance.  Some of the business processes that we're interested in are:

Bus Operator work time by shift by day
Bus Operator hours driven by shift by day
Bus Operator late reports by day
Bus Operator collisions by shift by day
Bus Operator incidents (going off route for example) by shift by day
Bus Operator passenger boardings by shift by day
Bus Operator scheduled monthly meetings
Bus Operator monthly meetings attended

It would be very easy for us to create a fact table that summarizes all of these measurements by bus operator by day, but then, as you could imagine, much of the business analysis drill down would be lost.  Trying to pull everything together into one fact table without pre-aggregating would cause issues as well.  For example, there could be 3 shifts that a bus operator drives in a day but a late report isn't explicitly correlated with a shift in the data.  A monthly meeting could be part of a bus operator's shift, but it only happens once per month and may not be its own shift.  The business users will want to see all of this information together.  The summary table would work for that, but I'd like to explore techniques for bringing these various elements together without violating the grain.  Any advice, links to similar situations etc would be greatly appreciated!

Thanks

A fact table can only have one grain and all records stored in that fact table must adhere to that grain
You have identified at least 3 grains in your business processes ( 1 - by bus operator, by shift, by day; 2 - by bus operator, by day; 3 - by bus operator by month) so you will need to have at least 3 fact tables.

If I have understood your issue correctly, you seem to be trying to push all this data into one table because "business users will want to see all of this information together" - however this is what your BI tool is for, not your data warehouse. You can use your BI tool (whether that is just some SQL queries, Excel or a proper BI Tool such as SSRS, BO, OBIEE, QlikView, etc) to collate data from multiple tables into a single report or dashboard.

Thanks Nick! Yes, after reading through my bullets that I posted, it became apparent to me that I pretty much defined the grain in the post So I think I'm on the same page about setting up 3 fact tables. The BI tool that we have doesn't have the capability to drill across. It has drill down and drill through (basically a pop up to other reports) but not drill across. I realize the DW's intent isn't for display. But the BI tool won't support what the business user's want to see. As I mentioned in my post, I can aggregate everything at the operator and day and that would meet the business requirement. I'm thinking that I set up 4 fact tables, 3 at the grains you mentioned and then also a rollup aggregate by operator by day based on the 3 facts. This way, we could have the business processes properly identified, but then transition to the summary aggregate by operator by day for the performance analysis reporting needed.

Thanks again for your advice.

You could create an aggregate to support the BI requirement.

This is not unusual. Best practice is to capture business events at the highest level of detail (atomic facts). These can be used as-is, but if the need requires it, you can aggregate multiple facts in order to support the requirement.

Depending on performance requirements, it could be presented as a view, rather than materializing the aggregate.

I was just wondering what BI tool you are using that won't support this - as most of the ones I have at least a little knowledge of would do?
Just an FYI - aggregates only help with performance, you should be able to implement the same report/dashboard regardless of whether you query base tables or aggregates of those base tables. If you think about it, a query you issue at run-time, a query against a View or a query against an aggregate table are logically all the same thinng