Time Tracking Data Model

Modified on Wed, 29 Jul at 2:07 AM

Model Summary

The QQube Time Tracking Data Model analyzes QuickBooks time records using worker billing, customer:job price level, and service-item sales rates.

Model Type	Time, billing-rate, and job-cost analysis
Record Coverage	QuickBooks Time Tracking records
Available Fields	More than 300
Time Measurement	Minutes
Primary Use	Analyze hours, billable status, rates, and calculated values

Best Used For

Reviewing time-tracking quantities and job-cost information.
Comparing billed, unbilled, and actual values.
Analyzing time by job, job dates, representative, class, or name.
Reviewing time by service item or payroll item.
Comparing worker billing, customer:job price level, and item sales rates.

Key Capabilities

Separates total, billable, billed, unbilled, and non-billable hours.
Includes worker billing, customer:job price level, and item sales rates.
Calculates values using each available rate.
Supports job, employee, vendor, customer, class, and service-item analysis.
Provides prepared DAX measures for Power BI and Excel Power Pivot.

Rate Calculations

Item Rate	Service item sales price multiplied by time quantity
Worker Billing Rate	Billing rate assigned to the employee or vendor and item, multiplied by time quantity
Customer:Job Price Level	Price level assigned to the customer:job and item, multiplied by time quantity

Time is stored in minutes.

QQube uses the recorded time quantity with the applicable rate to calculate the related value.

Hours, Rates, and Value Fields

The native field contains the total recorded hours. QQube field extensions separate the hours by billable status and calculate values using the available rates.

Native Fields (1)

Hours Fields
Total Hours of Any Status

QQube Field Extensions (17)

Field Name
Total Hours Not Billable
Total Billable Hours
Total Billable Hours (Billed)
Total Billable Hours (Unbilled)
Rate [Worker Billing Rate]
Rate [Customer:Job Price Level]
Rate [Item Sales Price]
Billable Hours [Worker Rate] Value
Billable Hours [Worker Rate] Value (Not Billable)
Billable Hours [Worker Rate] Value (Billed)
Billable Hours [Worker Rate] Value (Unbilled)
Billable Hours [Job Rate] Value
Billable Hours [Job Rate] Value (Billed)
Billable Hours [Job Rate] Value (Unbilled)
Billable Hours [Item Rate] Value
Billable Hours [Item Rate] Value (Billed)
Billable Hours [Item Rate] Value (Unbilled)

Power BI and Excel Power Pivot Measures

The prepared Time Tracking examples include 149 visible DAX measures. Hidden measures are not included.

Measure Section	Visible Measures
Power BI Company-Filter Measures	2
.Base Measures	17
Calendar Pattern Measures	49
Hours Billable Measures	44
Hours Billed Measures	14
Hours Not Billable Measures	7
Hours Total Measures	16

Power BI company filtering

The Job and Source Name slicers use bridge tables and two support measures to show only values associated with the selected company. Excel Power Pivot handles this filtering automatically.

Included Dimensions

Calendars

Transaction Date	Job End Date	Job Projected End Date
Job Start Date	Calendar Overview

Document

Line Attributes

Lists

Class	Company	Customer
Customer Rep	Employee	Job
Job Rep	Other Name	Payroll Item
Service Item	Source Name	Vendor

Technical Details

Model Structure	One Time Tracking FACT table with related dimensions and relationships already included
Time Measurement	Minutes
Rate Options	Worker Billing Rate, Customer:Job Price Level, and Item Sales Price
DAX Availability	Power BI and Excel Power Pivot examples

Expected Result

Open the prepared example and analyze time by billable status, job, employee, vendor, service item, rate, or calculated value.

The FACT table, dimensions, relationships, fields, QQube extensions, and measures are already included.