Create a cube to track billable time
Most analysis applications examine historical data but don't let users modify that data. However, SQL Server 2000's Analysis Services lets users write back to the analysis data (the cube). Write-back lets users change the underlying values stored in an OLAP cube. Analysts can use this write-back capability to develop forecasting, modeling, or data-entry systems. Enabling Analysis Services' write-back capability is simple: Right-click a cube in Analysis Manager, and select Write Enable. But developing a complete write-back application is much more complex. A write-back application is valuable because it lets you analyze the present or the future—rather than just the past. For example, you can view your progress in the current month or even predict upcoming sales results. Recently I developed a write-back solution for our software-development organization to track the time that each team member spends on each active project.
The application I wrote is simple but capable, much like the time-tracking applications that consultants use for billing time or tracking time to calculate their Return on Investment (ROI). The difference between this application and many other time-tracking applications is that it's primarily an analysis application. Most other time-tracking applications are primarily billing applications that include simple reporting capabilities. You could extend this application to integrate it with a billing system or produce billing reports, but this application's purpose is to determine ROI, utilization (which is a measurement of the percentage of hours billed), and efficiency.
Step 1: Creating the Cube
The first step I took to create the time-tracking application was to design the cube. Because the cube's design determines what analysis you can do, designing the cube before designing other aspects of the application (e.g., a data-entry screen) makes sense. I wanted to include in the analysis three pieces of information: employees, projects, and time. You can see the cube's dimensions, levels, and member properties in the left pane of the Cube Editor window that Figure 1 shows. The right pane shows the star schema I used to build the cube structure.
The fact table contains a record for each time entry for every combination of employee, project, and date. The Hours measure is summed in the fact table so that each Hours value represents a change to the previous value. For example, if you entered 8 hours for a work day but changed the value to 6 hours, the fact table would contain two rows—one containing 8 hours, the other containing -2 hours. The fact table rows also contain the username of the person who made the time entry and the date and time of the entry. This information is useful for diagnosing problems that might crop up later in the data because, for each cell in the cube, you can see when values were entered and who made the changes.
In the cube I created, the Employee dimension is a two-level dimension even though employees are categorized in three ways: by department, by group, and by name. To make the dimension easier to navigate, I omitted Group Name from the dimension levels and included it as a member property. If you need to know an individual's group, you can inspect the member property without having to navigate groups during analysis. If measuring a group's cost or utilization is important, you might want to include such a level in the Employee dimension.
Note two other details about the Employee dimension. First, the hourly billing rate for an employee is available as a member property. This cube structure doesn't contain enough information to determine historical billing rates for an employee—only the current billing rate is available. This limitation could be a problem if you're viewing a sum of an employee's billings for a time period that includes a billing-rate change. This problem wasn't a concern for my application, but one possible solution is to include a Billing Rate measure in the fact table. Then when an employee makes a time entry, an automated process—such as the Data Transformation Services (DTS) package I describe later—can copy the current billing rate from the Employee dimension to the new fact-table entry. If the billing rate later changes in the Employee dimension, the previous fact-table entries will still contain the older (appropriate) billing rate.
A second thing to note about the Employee dimension is that it includes the employee's email address. Making sure employees keep their time entries current is a constant battle. Because the email address is a member property, the application can run an automated service against the cube to send an email reminder to employees.
The Project dimension includes estimated and actual dates for important project milestones. The milestones that we track for commercial software development are feature complete (FC), release candidate 1 (RC1), and release to manufacturing (RTM). Tracking only one set of these estimated dates is somewhat limiting because, in reality, several revisions of the project schedule exist, and each revision has a set of estimated milestone dates. Each set is the best estimate at a particular point in time. For example, after the project's investigation phase, you have an estimate for when the project will be completed, but that estimate is updated after the project design milestone and again after the FC milestone. To keep this application simple, I tracked only the most recent schedule estimates.
The last dimension to discuss is the Time dimension. I generated a Time dimension that has two hierarchies. The first hierarchy (Time.Project) matches a standard calendar but is organized into weeks rather than quarters and months. The second hierarchy (Time.Fiscal) organizes the days in our company's fiscal calendar, in which first quarter starts April 1. The week organization matches our project schedules and is the most straightforward way for team members to view time when entering their time information. The fiscal calendar is the most natural way to analyze the data because it matches the way our company manages budgets and measures performance.