One of the common HR reporting needs is to determine the Utilization and Availability of employees. These metrics may also be studied at a higher level. For example, checking Workforce Utilization Percentages across a company’s different organizations provides insight into how overstaffed or understaffed each organization is. This blog describes an OBIEE design methodology to support such reporting requirements.
A quick functional overview of how Utilization is calculated
While Utilization % tells how much actual work an employee has completed compared to their overall capacity, Availability indicates the remainder of the time where an employee has been inactive or non-utilizable. For example if the Utilization of someone is 80%, their Availability is 20% (100 – 80).
Utilization is defined as the ratio of Hours Worked over Capacity. Hours Worked is a function of the actual hours entered on a timecard throughout an employee’s workweek. And there may be several variations of what defines Hours Worked depending on the organization’s specific definition of the type of timecard hours that are utilizable. For instance, a consulting firm may include billable hours to a client as utilizable, but not hours spent on non-billable categories such as bench time and vacations. Capacity is typically a standard number of hours an employee is expected to work irrespective of what gets entered on timesheets. For example, an employee who works 8 hour workdays has a capacity of 40 hours a week, whereas a part-time employee who works 3 days a week has a capacity of 24. Capacity usually excludes standard holiday hours as such hours are not expected to be utilizable in the first place.
Following is a summary of the key metrics:
Utilization % = 100 x Hours Worked / Capacity
Availability % = 100 – Utilization %
Hours Worked: Timecard Hours that are considered utilizable
Capacity: Standard Work Schedule Hours – Standard Holiday Hours
Data Model
No matter what transactional system your data is sourced from, Hours Worked and Capacity are most likely going to be stored in different tables in that system. For example, in Oracle E-Business Suite, Hours Worked are sourced from Oracle Time and Labor timecard tables. Whereas, Capacity is sourced from the HR assignment tables that associate employees to their corresponding work schedules and holiday calendars.
In my solution of a data warehouse model that supports Utilization calculations, I use 2 facts: Timecard Fact and Capacity Fact. Not all the dimensions in both star schemas are conforming. For example, the Timecard Fact has dimensions that describe the type of hours whether they are billable or not, vacation hours or project hours, work hours that were performed onsite or remote, etc… Such attributes of a timecard are not relevant when we talk about capacity facts. For this reason, if we were to store both metrics (Hours Worked and Capacity Hours) in the same fact table, we end up with an incorrect capacity as it doesn’t relate to all the timecard dimensions. Following is my schema for both stars where Project, Task and Time Entry Status are non-conforming dimensions:
OBIEE Design
In the RPD business layer, I built 3 logical facts and the same facts are made available in the Presentation layer:
- Timecard Fact: Sourced from the timecard OLAP fact table
- Capacity Fact: Sourced from the capacity OLAP fact table
- Utilization Fact: This fact has no physical data sources as all the metrics are based on the other 2 logical facts.
I am now able to build a simple trend report that shows utilization broken down by Organization. Such a report is straightforward to build since both the Time and Organization dimensions are conforming between both facts: Timecard and Capacity.
A more advanced reporting requirement may ask for utilization to be dynamically re-calculated in the report based on additional prompts on dimensions like Time Entry Status, Project or Task. These dimensions are not conforming and therefore cannot be added as prompts in the typical way. If interested in adding dynamic prompting on timecard-specific dimensions, you can see an example of how that is possible by referring to my other blog: OBIEE Prompting on Non-Conforming Dimensions.
