June 2002 MDX Puzzle Solution

One of the most common measurements of group or individual performance in a consulting agency is utilization. Decision makers in consulting groups calculate utilization by dividing the total number of hours billed by the total number of business hours available (e.g., 8 hours for each business day). Having a high percentage of utilization is good because it means you're effectively deploying available resources to generate revenue. Use the cube structure that Figure 1 in "Tracking Time," June 2002, InstantDoc ID 24769, shows to create the MDX for a measure that calculates utilization for a selected employee and time period.

Answer: The query in Listing A calculates utilization as a percentage of hours worked. The meat of the formula is in the definition of the calculated measure, AvailableHours. AvailableHours multiplies the number of work days in the selected time period by 8 hours. You get the total number of work days by eliminating weekend days and holidays from the total number of calendar days. The Utilization measure then divides the total work hours by the available work hours to get a percentage. The result is a percentage that can be more than 100 percent if the average number of work hours exceeds 8 hours per day.