FACT Table Usage

Modified on Tue, 28 Jul at 2:54 AM

FACT Table Summary

QQube FACT tables contain the measures used for analysis and the links to the dimensions that provide context. Each ready-made QQube data model contains one primary FACT table and is designed to operate as a complete model.

Model Structure

One primary FACT table per data model

FACT Table Contains

Measures and links to included dimensions

Model Design

Ready-made and self-contained

Normal Usage

Use one data model for each analysis

Combining FACT tables

FACT tables should not normally be connected to one another. A cross-model design is only appropriate for a custom requirement that cannot be represented by a single QQube data model because QuickBooks does not store the needed information together.

Primary Data Model FACT Tables

The following tables replace the legacy image with searchable customer-facing FACT table names and their physical QQube views.

Customers and Receivables

Data Model	Customer-Facing FACT Table	Physical View
Sales	SALES VALUES	`vf_SALESDETAIL`
Open Sales Orders	OPEN SALES ORDER VALUES	`vf_OPENSO`
Accounts Receivable	ACCOUNTS RECEIVABLE VALUES	`vf_OPENAR`

Vendors and Payables

Data Model	Customer-Facing FACT Table	Physical View
Purchases	PURCHASES VALUES	`vf_PURCHASINGDETAIL`
Open Purchase Orders	OPEN PURCHASE ORDER VALUES	`vf_OPENPO`
Accounts Payable	ACCOUNTS PAYABLE VALUES	`vf_OPENAP`

Jobs, Time, and Mileage

Data Model	Customer-Facing FACT Table	Physical View
Job Cost Details	JOB COST VALUES	`vf_JOBCOSTDETAIL`
Time Tracking	TIME TRACKING VALUES	`vf_TIMETRACKING`
Vehicle Mileage	VEHICLE MILEAGE VALUES	`vf_VEHICLEMILEAGE`
Profit and Loss by Job	PROFIT AND LOSS BY JOB	`vf_FINANCIALFLEXJOB`
Profit and Loss by Job by Class	PROFIT AND LOSS BY JOB BY CLASS	`vf_FINANCIALFLEXJOBCLASS`

Inventory

Data Model	Customer-Facing FACT Table	Physical View
Inventory	INVENTORY VALUES	`vf_INVENTORY`

Payroll and Human Resources

Data Model	Customer-Facing FACT Table	Physical View
Payroll	PAYROLL VALUES	`vf_PAYROLL`

Financial Statements

Data Model	Customer-Facing FACT Table	Physical View
Balance Sheet - Standard	BALANCE SHEET - STANDARD	`vf_FINANCIALBS`
Profit and Loss - Standard	PROFIT AND LOSS - STANDARD	`vf_FINANCIALPL`
Statement of Cash Flows	STATEMENT OF CASH FLOWS	`vf_FINANCIALCASH`
Profit and Loss Actual/Budget/Forecast	PROFIT AND LOSS ACTUAL/BUDGET/FORECAST	`vf_FINANCIALABF`
Profit and Loss Freestyle	PROFIT AND LOSS FREESTYLE	`vf_FINANCIALPLFREESTYLE`

Financial Statements by Class

Data Model	Customer-Facing FACT Table	Physical View
Balance Sheet by Class - Standard	BALANCE SHEET BY CLASS - STANDARD	`vf_FINANCIALBSCLASS`
Profit and Loss by Class - Standard	PROFIT AND LOSS BY CLASS - STANDARD	`vf_FINANCIALPLCLASS`
Statement of Cash Flows by Class	STATEMENT OF CASH FLOWS BY CLASS	`vf_FINANCIALCASHCLASS`
Profit and Loss by Class Actual/Budget/Forecast	PROFIT AND LOSS BY CLASS ACTUAL/BUDGET/FORECAST	`vf_FINANCIALABFCLASS`
Profit and Loss by Class Freestyle	PROFIT AND LOSS BY CLASS FREESTYLE	`vf_FINANCIALPLFREESTYLECLASS`

Financials for 52/53 Tax Year

Data Model	Customer-Facing FACT Table	Physical View
Profit and Loss 52/53 Tax Year	PROFIT AND LOSS 52/53 TAX YEAR	`vf_FINANCIAL5253`
Profit and Loss 52/53 Tax Year by Class	PROFIT AND LOSS 52/53 TAX YEAR BY CLASS	`vf_FINANCIAL5253CLASS`

General Ledger and Accountant

Data Model	Customer-Facing FACT Table	Physical View
Trial Balance - Current	TRIAL BALANCE - CURRENT	`vf_FINANCIALTB`
Trial Balance - All Dates	TRIAL BALANCE - ALL DATES	`vf_FINANCIALTB_ALL`
Trial Balance - Activity	TRIAL BALANCE - ACTIVITY	`vf_FINANCIALTBA`
Trial Balance by Class - Activity	TRIAL BALANCE BY CLASS - ACTIVITY	`vf_FINANCIALTBACLASS`
General Ledger Detail	GENERAL LEDGER DETAIL	`vf_GLDETAIL`
Profit and Loss Detail	PROFIT AND LOSS DETAIL	`vf_PANDLDETAIL`
Cash Position	CASH POSITION VALUES	`vf_CASHPOSITION`
Fixed Assets	FIXED ASSET VALUES	`vf_FIXEDASSETS`

Auditing

Data Model	Customer-Facing FACT Table	Physical View
Audit Trail	AUDIT TRAIL VALUES	`vf_AUDITTRAIL`

Legacy Financial Models

Data Model	Customer-Facing FACT Table	Physical View
Profit and Loss by Job (Legacy)	PROFIT AND LOSS BY JOB (Legacy)	`vf_FINANCIALJOB`
Profit and Loss by Job by Class (Legacy)	PROFIT AND LOSS BY JOB BY CLASS (Legacy)	`vf_FINANCIALJOBCLASS`

Linked Transaction FACT Tables

Some ready-made models also include a linked-transactions FACT table for related transaction details. These tables supplement the model and do not replace its single primary FACT table.

Data Model	Customer-Facing FACT Table	Physical View
Sales	SALES LINKED TRANSACTIONS	`vf_SALESLINKEDTXN`
Open Sales Orders	OPEN SALES ORDER LINKED TRANSACTIONS	`vf_OPENSOLINKEDTXN`
Accounts Receivable	ACCOUNTS RECEIVABLE LINKED TRANSACTIONS	`vf_OPENARLINKEDTXN`
Purchases	PURCHASES LINKED TRANSACTIONS	`vf_PURCHASINGLINKEDTXN`
Open Purchase Orders	OPEN PURCHASE ORDER LINKED TRANSACTIONS	`vf_OPENPOLINKEDTXN`
Accounts Payable	OPEN AP LINKED TRANSACTIONS	`vf_OPENAPLINKEDTXN`
Inventory	INVENTORY LINKED TRANSACTIONS	`vf_INVENTORYLINKEDTXN`
Job Cost Details	JOB COST LINKED TRANSACTIONS	`vf_JOBCOSTLINKEDTXN`
General Ledger Detail	GENERAL LEDGER LINKED TRANSACTIONS	`vf_GLLINKEDTXN`
Profit and Loss Detail	PROFIT AND LOSS DETAIL LINKED TRANSACTIONS	`vf_PANDLLINKEDTXN`
Cash Position	CASH POSITION LINKED TRANSACTIONS	`vf_CASHPOSITIONLINKEDTXN`

Lookup FACT Tables

These specialized lookup FACT tables provide analytical relationships that are not represented by a standard transaction FACT table.

Data Model	Customer-Facing FACT Table	Physical View
Billing Rates	Billing Rate Details	`vl_BILLINGRATE`
Bill of Materials	BILL OF MATERIALS	`vl_BOM`
Price Levels	Price Level Details	`vl_PRICELEVEL`