Payroll Data Model

Modified on Wed, 29 Jul at 2:55 AM

Model Summary

The QQube Payroll Data Model provides detailed payroll check, wage, payroll-hour, liability, and adjustment information from QuickBooks.

Model Type	Payroll detail and liability analysis
Record Coverage	Detailed payroll checks, posting transactions, and non-posting adjustments
Available Fields	More than 850
Employee Information	Approximately 95% of employee fields through the extended Employee HR Dimension
Primary Use	Analyze payroll amounts, hours, wages, liabilities, adjustments, employees, and jobs

Best Used For

Creating payroll registers and fringe reports.
Reconciling payroll deductions, contributions, accrued liabilities, and paid liabilities.
Reviewing posting and non-posting payroll adjustments.
Analyzing employee wages, burden, overtime, sick pay, vacation pay, commissions, and bonuses.
Comparing payroll hours and amounts by pay period.
Reviewing employee status and payroll activity by job.

Key Capabilities

Includes detailed payroll check data available through the QuickBooks SDK.
Separates wages, non-wage items, liabilities, adjustments, and payroll quantities.
Provides detailed hourly, salary, commission, bonus, sick, vacation, and overtime amounts.
Uses the extended Employee HR Dimension for employment and protected employee information.
Provides prepared DAX measures for Power BI and Excel Power Pivot.

Payroll Data Availability

QuickBooks payroll data is available through the Intuit Software Development Kit.

Accessing the data is not conventional, but QQube provides the detailed payroll check data and approximately 95% of the available employee fields.

See QuickBooks Data Availability in QQube.

The Employee HR Dimension contains protected employee information.

This dimension includes Social Security numbers and employment information for controlled data imports and larger payroll analyses.

Payroll Quantity, Rate, and Amount Fields

The model contains 30 primary payroll fields. Native fields provide the standard payroll quantities, rates, and totals. QQube field extensions separate payroll activity into additional wage, non-wage, liability, and adjustment values.

Native Fields (8)

Quantity Fields	Rate Fields	Amount Fields
Line Payroll Hours Quantity	Line Rate Line Rate Percent	Line Amount Line Wage Amount Line Non-Wage Amount Line Income Subject To Tax Document Total Amount

QQube Field Extensions (22)

Quantity Fields	Amount Fields
Line Other Payroll Item Quantity Line Commissions or Piece Work Quantity	Line Wage Bonus Amount Line Wage Commission Amount Line Wage Hourly Regular Amount Line Wage Hourly Sick Amount Line Wage Hourly Vacation Amount Line Wage Hourly Overtime Amount Line Wage Salary Regular Amount Line Wage Salary Sick Amount Line Wage Salary Vacation Amount Line Non-Wage Tax Amount Line Non-Wage Deduction Amount Line Non-Wage Contribution Amount Line Non-Wage Addition Amount Line Non-Wage Direct Deposit Amount Line Liability Accrued Amount Line Liability Paid Amount Line Adjustment Posting Amount Line Adjustment Non-Posting Amount Line Wage Base Amount Line Wage Base Tips Amount

Quantity Fields

Amount Fields

Line Other Payroll Item Quantity
Line Commissions or Piece Work Quantity

Line Wage Bonus Amount
Line Wage Commission Amount
Line Wage Hourly Regular Amount
Line Wage Hourly Sick Amount
Line Wage Hourly Vacation Amount
Line Wage Hourly Overtime Amount
Line Wage Salary Regular Amount
Line Wage Salary Sick Amount
Line Wage Salary Vacation Amount
Line Non-Wage Tax Amount
Line Non-Wage Deduction Amount
Line Non-Wage Contribution Amount
Line Non-Wage Addition Amount
Line Non-Wage Direct Deposit Amount
Line Liability Accrued Amount
Line Liability Paid Amount
Line Adjustment Posting Amount
Line Adjustment Non-Posting Amount
Line Wage Base Amount
Line Wage Base Tips Amount

Power BI and Excel Power Pivot Measures

The prepared Payroll examples include 275 visible DAX measures. Hidden measures are not included.

Measure Section	Visible Measures
Measures Without a Display Folder	4
.Base Measures	30
Calendar Pattern Measures	124
Commissions/Piece Work Measures	5
Item (Any) Amount Measures	5
Liability Measures	18
Non-Wage Measures	9
Other Payroll Item Qty Measures	8
Payroll Hours Qty Measures	8
Wage Amount Measures	64

Power BI includes four company-filter support measures.

These measures restrict Account, Employee, Job, and Vendor slicers to values associated with the selected company. Excel Power Pivot handles this filtering automatically.

Included Dimensions

Calendars

Effective Payroll Date	Liability Period End Date	Liability Period Start Date
Pay Period End Date	Pay Period Start Date	Transaction Date
Employee Hire Date	Employee Release Date	Job End Date
Job Projected End Date	Job Start Date	More Info on Calendars

Document

Document Attributes

Line Attributes

Lists

Account	Class	Company
Customer	Customer Rep	Employee (HR)
Item	Job	Job Rep
Payroll Item	Service Item	Vendor

Technical Details

Model Structure	One prepared FACT table with the required dimensions and relationships already included
Payroll Coverage	Detailed payroll checks, posting transactions, and non-posting adjustments
Primary Model Fields	30 fields: 8 Native Fields and 22 QQube Field Extensions
Employee Dimension	Extended Employee HR Dimension with protected SSN and employment information
DAX Availability	275 visible measures for Power BI and Excel Power Pivot

Expected Result

Use the prepared Payroll model to analyze payroll checks, wages, hours, liabilities, adjustments, employee information, and job-related payroll activity.

The FACT table, dimensions, relationships, Native Fields, QQube Field Extensions, and DAX measures are already included.