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.
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 |
Power BI and Excel Power Pivot Measures
The prepared Payroll examples include 275 visible DAX measures. Hidden measures are not included.
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
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 |
| Prepared DAX Measures | 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.
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article