Audit Trail Data Model

Modified on Tue, 28 Jul at 11:59 PM

Model Summary

The QQube Audit Trail Data Model analyzes historical QuickBooks transaction changes. It provides detail that cannot be obtained by exporting the QuickBooks Audit Trail report to Excel.

Model Type	Historical transaction-change analysis
Transaction Coverage	Linked posting and non-posting transactions
Available Fields	More than 1,250
Primary Use	Analyze who changed transactions, when changes occurred, and how values changed

Best Used For

Counting changes by transaction.
Comparing current amounts with original amounts.
Analyzing changes by user and time of day.
Reviewing activity by customer, job, vendor, class, sales representative, or job representative.
Separating new transactions from modified transactions.

Key Capabilities

Tracks historical transaction changes.
Includes linked posting and non-posting transactions.
Provides original, current, and change amounts.
Supports analysis by user, transaction, time, and related dimensions.
Includes prepared DAX measures for Power BI and Excel Power Pivot.

QuickBooks Data Limitations

Use the shortest practical date range.

QuickBooks does not readily make available any direct connection to the Audit Trail data, so it can take considerable time to extract. Include only the dates needed for the analysis.

Quantity, Rate, and Amount Fields

Native fields come from QuickBooks Audit Trail data. QQube field extensions add document-level summary amounts for comparing the original, current, and previous transaction versions.

Native Fields (7)

Quantity and Hours Fields	Rate Fields	Amount Fields
Line Quantity/Hours	Line Rate	Line Amount Line Debit Amount Line Credit Amount Line Paid Amount Line Open Amount

QQube Field Extensions (3)

Quantity and Hours Fields	Rate Fields	Amount Fields
		Document Summary Original Amount Document Summary Current Amount Document Summary Amount Change from Previous

Power BI and Excel Power Pivot Measures

The prepared Audit Trail examples include 13 visible DAX measures in two display folders. Hidden measures are not included.

Measure Folder	Visible Measures
.Base Measures	10
Data Entry Measures	3

Included Dimensions

The prepared Audit Trail model includes the following dimensions.

Calendars

Audit Trail Date	Audit Trail Time	52/53 Tax Year
Billed Date	Due Date	Expected Date
Fiscal Year	Transaction Date	Job End Date
Job Projected End Date	Job Start Date	Calendar Overview

Document

Document Attributes

Line Attributes

Geography

Lists

Account	Account Summary	Bin Location
Class	Company	Customer
Customer Rep	Employee	Item
Item Sales Tax Code	Job	Job Rep
Payment Method	Payroll Item	Sales Rep
Sales Tax Item	Ship Method	Terms
Vendor

Technical Details

Model Structure	One Audit Trail FACT table with related dimensions and relationships already included
Date Scope	The selected Audit Trail extraction date range
DAX Availability	Power BI and Excel Power Pivot examples

Expected Result

Open the prepared Audit Trail example and analyze transaction changes by date, time, user, amount, or any included dimension.

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