Modifications in Version 10 | CLEARIFY

Overview of Changes in Version 10

QQube Version 10 includes speed enhancements, the ability to choose a range of data to be extracted from QuickBooks, EBIDTA for financial statements, additional measures in several detail data models, over five thousand DAX measures for Power Pivot and Power BI, and more than 100 additional improvements to cement its legacy as the most complete tool for QuickBooks analytics.

The New QQube Configuration Tool

 

Date and Data Control

You can control the range of data you pull in from QuickBooks:

• This is not available for customers using lot or serial numbers in QBE advanced inventory.
• If there are Open A/R or Open A/P transactions with dates before the beginning date - and have never had anything applied to them - they will not be pulled.

 

More options for default "As Of" dates for A/R and A/P

 

You get to decide what date "Today" is:

This affects any dependent measure or DAX formula in a QQube data model.  For instance, if you define Today is 9/21/2022, but the actual date is 10/01/2022 then current month is based upon the 9/21/2022 date.

Data Model Changes

This guide will list all data model changes from QQube Version 7.9.536 - but does not include the list of bug fixes which are catalogued here.  The data models are listed in order as implemented in the QQube Configuration Tool.

Specific changes to the Account and Calendar Dimensions are explained in separate guides for advanced users.

Company Setup

List Views (those that have vl_ prefixes) will no longer include the company name in the list, and will have a single link (LinkForCompanyID) to the Company Dimension (vd_Company).  They will no longer have links such as ParentID.

The List Views were originally intended as information only views.  However, we know of cases where advanced users (e.g. developers) would use these in place of the actual dimensions - which may, or may not, have the desired effect.

Example for vl_CustomerType:

OLD	NEW

Dimension Changes (Lists)

• Add "Item Preferred Vendor Name" to Item Service Dimension.
• Removed "Item Type" from Item Service Dimension (it is always Service).
• Add "Customer Job Title" and "Customer Preferred Delivery Method" to Customer Dimension.
• Add "Class" to Employee and EmployeeHR Dimensions
• Add "Has Children" to the following dimensions:
  • vd_Class
  • vd_Job
  • vd_Item
  • vd_ItemService
  • ALL vd_Account"X"
• Add "Account Cash Flow Classification Sorting" to Account Cash Dimension
• Add Item DESCRIPTION and NAME/DESCRIPTION hierarchies:
  • vd_Item
  • vd_ItemService

Dimension Changes (Geography)

• Add Vendor Address 1,2,3,4,5 from Bill to the following Data Models
  • Inventory
  • Job Cost
  • Accounts Payable
  • Cash Position
  • Purchases

 

Customers and Receivables

Sales

• Corrected parentheses for three fields:

OLD	NEW

• Corrected spelling for one field in the Sales Geography Dimension:

OLD	NEW

Open Sales Orders

There are three changes to the Sales Order FACT table:

1. Two new fields, highlighted in yellow
2. (Foreign) are now enclosed in parentheses
3. (Foreign) is now consistently placed at the end of the field name

LinkToFiscalDateID was added, but not shown.

OLD	NEW

There are two changes to the Open Sales Order Document Attributes Folder:

1. Five new fields highlighted in yellow below
2. Order of fields - consistent with desired pattern for all related type of views

OLD	NEW

Accounts Receivable

Only one change to the FACT table:

• Added LinkToFiscalDateID

 

Jobs, Time, and Mileage

Job Cost Detail

Added 25 new fields. We corrected - and expanded - the thought process behind job cost and related data models, e.g. vehicle mileage, time tracking with regard to billable status - to better follow the QuickBooks implementation and reporting.

• Billable status is a checked status when a customer:job is present and includes both Billed and Unbilled
• Not billable is an unchecked status when a customer:job is present

OLD	NEW

Time Tracking

Added 3 new fields.  (LinktoFiscalDateID was added, but not shown here).

As discussed in Job Costing:

• Billable status is a checked status when a customer:job is present and includes both Billed and Unbilled
• Not billable is an unchecked status when a customer:job is present

OLD	NEW

Vehicle Mileage

This data model has been completely reworked due to two factors: changes in QuickBooks implementation, and a need for more relevant metrics. 

OLD	NEW

Profit and Loss by Job (Flexible Period)

This replaces the old Profit and Loss by Job - which has been moved to the Legacy section of the QQube Configuration Tool.

This is aggregated by day, and refreshes in 10% of the time from the previous data model it replaces.  It is designed for Power Pivot and Power BI where we have created all of the necessary DAX formulas.

OLD	NEW

Profit and Loss by Job by Class (Flexible Period)

This replaces the old Profit and Loss by Job by Class - which has been moved to the Legacy section of the QQube Configuration Tool.

This is aggregated by day, and refreshes in 10% of the time from the previous data model it replaces.  It is designed for Power Pivot and Power BI where we have created all of the necessary DAX formulas.

OLD	NEW

 

Inventory

Inventory Management

There are no changes to the FACT table or associated document and line attribute dimensions.

Bill of Materials

• Corrections to Fields names in vd_AssemblyBOMMain

OLD	NEW

• Corrections to Fields names in vd_AssemblyBOMSub
• Addition of field "Sub Assembly Has Children"

OLD	NEW

• Corrections to Fields names in vd_AssemblyBOMItem
• Addition of field "Assembly Item Has Children"

OLD	NEW

 

Vendors and Payables

Purchases

Added 6 new fields to allow users to have complete open PO information within the purchasing details data model. Parenthesis added to PurchaseTxn Line Rate (Foreign)

OLD	NEW

Open Purchase Orders

• Parenthesis added to OpenPO Line Rate (Foreign)
• Added SiteID and BinLocationID for dimensions
• Use Account Purchasing Dimension instead of full Account Dimension

OLD	NEW

There are two changes to the Open Purchase Order Document Attributes Folder:

1. One new field highlighted in yellow below
2. Order of fields - consistent with desired pattern for all related type of views

OLD	NEW

Accounts Payable

Only one change to the FACT table:

• Added LinkToFiscalDateID

Removed Shipping Address which does not exist in the SDK

OLD	NEW

Payroll and Human Resources

Payroll

• Added 20 fields
• The Payroll Document Total Amount will now only show for the summary line, and not all lines.

OLD	NEW

Added two fields to Payroll Data Model Line Attributes:

OLD	NEW

 

Financial Statements

Balance Sheet (Standard Monthly Period)

There are no changes other than re-ordering the fields for natural order consistency.

Profit and Loss (Standard Monthly Period)

There are no changes to the FACT table other than re-ordering the fields for natural order consistency. The new EBIDTA function is not available for this data model.

Statement of Cash Flows (Standard Monthly Period)

There are no changes to the FACT table other than re-ordering the fields for natural order consistency.

***Trial Balance and Trial Balance Activity Have Been Moved to General Ledger and Accountant***

These are not considered Financial Statements in the Accounting World.

Profit and Loss with Actual, Budget, Forecast  (Advanced Monthly Period)

This data model is used in the Access, Excel, Tableau, and Crystal Reports examples. It has been replaced in Power Pivot and Power BI by the Flexible Custom Period Profit and Loss.

Access, Excel, Tableau, and Crystal Reports	Power Pivot and Power BI

Profit and Loss (Flexible Period)

This is brand new, and replaces the previous "Profit and Loss Freestyle" which has been moved to the Legacy Section in the QQube Configuration Tool

It uses the new combination calendar/fiscal date calendar dimension, and an account dimension which contains EBIDTA and fixes hierarchy "holes" that existed in previous versions of QQube for PowerPivot and Power BI.

As noted in the previous data model section, it also replaces the Actual, Budget, Forecast Profit and Loss data model for use with Power Pivot and Power BI.

Balance Sheet (Flexible Period)

This is brand new, and replaces the previous "Balance Sheet Freestyle" which has been moved to the Legacy Section in the QQube Configuration Tool

It uses the new combination calendar/fiscal date calendar dimension, and an account dimension which fixes hierarchy "holes" that existed in previous versions of QQube for PowerPivot and Power BI.

Current

 

Financial Statements by Class

Balance Sheet by Class (Standard Monthly Period)

There are no changes other than re-ordering the fields for natural order consistency.

Profit and Loss by Class (Standard Monthly Period)

There are no changes to the FACT table other than re-ordering the fields for natural order consistency. 

Statement of Cash Flows by Class (Standard Monthly Period)

There are no changes to the FACT table other than re-ordering the fields for natural order consistency.

***Trial Balance Activity by Class Has Been Moved to General Ledger and Accountant***

These are not considered Financial Statements in the Accounting World.

Profit and Loss by Class with Actual, Budget, Forecast  (Advanced Monthly Period)

This data model is used in the Access, Excel, Tableau, and Crystal Reports examples. It has been replaced in Power Pivot and Power BI by the Flexible Custom Period Profit and Loss.

Access, Excel, Tableau, and Crystal Reports	Power Pivot and Power BI

Profit and Loss by Class (Flexible Period)

This is brand new, and replaces the previous "Profit and Loss by Class Freestyle" which has been moved to the Legacy Section in the QQube Configuration Tool

It uses the new combination calendar/fiscal date calendar dimension, and an account dimension which fixes hierarchy "holes" that existed in previous versions of QQube for PowerPivot and Power BI.

As noted in the previous data model section, it also replaces the Actual, Budget, Forecast Profit and Loss by Class data model for use with Power Pivot and Power BI.

***Flexible Period Balance Sheet by Class is NOT possible because of how QuickBooks stores A/P and A/R splits***

 

Financials for 52/53 Tax Year

52/53 Periods Profit/Loss

There are no changes other than re-ordering the fields for natural order consistency. 

We intended to have a new model comparable to our other flexible period offerings for profit and loss and balance sheet.  However, we were not able to fit it in our time frame.  This will be included in Version 10.1

52/53 Periods Profit/Loss by Class

There are no changes other than re-ordering the fields for natural order consistency. 

We intended to have a new model comparable to our other flexible period offerings for profit and loss and balance sheet.  However, we were not able to fit it in our time frame.  This will be included in Version 10.1

 

General Ledger and Accountant

Standard Trial Balance (All Dates)

There are no changes and is only available out-of-the-box for Excel as it is intended to test the integrity of a file.

Standard Trial Balance (Current)

There are no changes.

Trial Balance Activity (36 Monthly Periods)

There are no changes, other than re-ordering the fields for natural order consistency.

The data model was moved from Financial Statements.  It still requires the Profit and Loss and Balance Sheet Summary Financials to populate.

Trial Balance Activity by Class (36 Monthly Periods)

There are no changes, other than re-ordering the fields for natural order consistency.

The data model was moved from Financial Statements by Class.  It still requires the Profit and Loss by Class and Balance Sheet Summary by Class Financials to populate.

General Ledger Detail

There is one change,

The Line Service Date Folder had the field named "SalesTxn Line Service Date" and was corrected to "GLTxn Line Service Date".

Fields were re-ordered for natural order consistency.

Profit and Loss Detail

There are no changes, other than re-ordering the fields for natural order consistency.

Cash Position

• Added LinkToFiscalDateID to the FACT table.
• Re-ordered the fields for natural order consistency.
• Change how Cash On Hand is calculated.  Now based upon Trial Balance Date - and not on present Account Balance as was previous.

Fixed Assets

• Added "Fixed Asset Count".
• Removed "Item" from pertinent fields.
• Fixed spelling of "YearEnd" to "Year End"

OLD	NEW

 

• Removed "Item" from pertinent fields in Fixed Assets Document Attributes

OLD	NEW

FIELD NAME CHANGES, FIELD REMOVALS, FIELD ADDITIONS

(CHEAT SHEET)

 

	OLD		NEW
FIELD NAME CHANGES
SALES FACT
	SalesTxn Line Rate Foreign		SalesTxn Line Rate (Foreign)
	SalesTxn Line Estimate Cost Amount Foreign		SalesTxn Line Estimate Cost Amount (Foreign)
	SalesTxn Line Estimate Income Amount Foreign		SalesTxn Line Estimate Income Amount (Foreign)
SALES GEOGRAPHY
	SalesTxn Ship OR Bill Address PostalCode		SalesTxn Ship OR Bill Address Postal Code
OPEN SO FACT
	OpenSO Line Sales Order Original Amount Foreign		OpenSO Line Sales Order Original Amount (Foreign)
	OpenSO Line Sales Order Invoiced Amount Foreign		OpenSO Line Sales Order Invoiced Amount (Foreign)
	OpenSO Line Sales Order Invoiced Amount Foreign Actual		OpenSO Line Sales Order Invoiced Amount Actual (Foreign)
	OpenSO Line Sales Order Open Amount Foreign		OpenSO Line Sales Order Open Amount (Foreign)
	OpenSO Line Sales Order Open Amount Foreign Actual		OpenSO Line Sales Order Open Amount Actual (Foreign)
ASSEMBLY BOM Sub
	MainAssemblyTimeCreated		SubAssemblyTimeCreated
	MainAssemblyTimeModified		SubAssemblyTimeModified
	Main Assembly Class		Sub Assembly Class
ASSEMBLY BOM Item
	ItemTimeCreated		AssemblyItemTimeCreated
	ItemTimeModified		AssemblyItemTimeModified
	Main Assembly Class		Assembly Item Class
PURCHASES FACT
	PurchaseTxn Line Rate Foreign		PurchaseTxn Line Rate (Foreign)
OPEN PO FACT
	OpenPO Line Rate Foreign		OpenPO Line Rate (Foreign)
CASH POSITION DOCUMENT ATTRIBUTES
	OpenARTimeCreated		CashReqTimeCreated
	OpenARTimeModified		CashReqTimeModified
GENERAL LEDGER LINE DATES
	SalesTxn Line Service Date		GLTxn Line Service Date
FIXED ASSETS FACT
	Fixed Asset Item Cost Basis		Fixed Asset Cost Basis
	Fixed Asset Item Sales Expense		Fixed Asset Sales Expense
	Fixed Asset Item YearEnd Accum Depreciation		Fixed Asset YearEnd Accum Depreciation
	Fixed Asset Item YearEnd Book Value		Fixed Asset YearEnd Book Value
FIXED ASSETS DOCUMENT ATTRIBUTES
	Fixed Asset Item Number		Fixed Asset Number
	Fixed Asset Item Serial Number		Fixed Asset Serial Number
	Fixed Asset Item Location		Fixed Asset Location
	Fixed Asset Item Vendor/Payee		Fixed Asset Vendor/Payee
	Fixed Asset Item Notes		Fixed Asset Notes
FIELD REMOVALS
SERVICE ITEM
	Item Type
OPEN PO GEOGRAPHY
	Open AP Shipping Address 1
	Open AP Shipping Address 2
	Open AP Shipping Address 3
	Open AP Shipping Address 4
	Open AP Shipping Address 5
VEHICLE MILEAGE			(REPLACEMENT)
	VehicleMileage Theoretical Miles		Vehicle Mileage Total Miles of Any Status
	VehicleMileage Actual Miles		Vehicle Mileage Total Mle Not Billable
	VehicleMileage Actual Miles Billed		Vehicle Mileage Total Billable Miles
	VehicleMileage Actual Miles Unbilled		Vehicle Mileage Total Billable Miles Billed
	VehicleMileage Standard Mileage Rate		Vehicle Mileage Total Billable Miles Unbilled
	VehicleMileage Standard Mileage Total Amount		Vehicle Mileage Billable Rate
	VehicleMileage Standard Mileage Total Amount Billed		Vehicle Mileage Billable Miles Value
	VehicleMileage Standard Mileage Total Amount Unbilled		Vehicle Mileage Billable Miles Value Billed
			Vehicle Mileage Billable Miles Value Unbilled
			Vehicle Mileage Standard IRS Mileage Rate
			Vehicle Mileage Standard Rate Value for Miles of Any Status
			Vehicle Mileage Standard Rate Value for Miles Not Billable
			Vehicle Mileage Standard Rate Value for Billable Miles
			Vehicle Mileage Standard Rate Value for Billable Miles Billed
			Vehicle Mileage Standard Rate Value for Billable Miles Unbilled
NEW FIELDS
OPEN SO FACT
			OpenSO Line Rate (Foreign)
OPEN SO DOCUMENT ATTRIBUTES
			OpenSO Document Sales Channel Name
			OpenSO Document Sales Store Name
			OpenSO Document Sales Store Type
			OpenSO Document Fulfillment Status
			OpenSO Document Ship Days Past Transaction Date
JOB COST DETAILS FACT
			JobTxn Line Job Cost Quantity Billable
			JobTxn Line Time Tracking Quantity Not Billable
			JobTxn Line Time Tracking Quantity Billable
			JobTxn Line Mileage Quantity Not Billable
			JobTxn Line Mileage Quantity Billable
			JobTxn Line Vehile Standard IRS Mileage Rate
			JobTxn Line Job Cost Amount Billable
			JobTxn Line Job Cost Paid Amount Billable
			JobTxn Line Job Cost Materials Amount Billable
			JobTxn Line Job Cost Labor Amount Billable
			JobTxn Line Job Cost Other Amount Billable
			JobTxn Line Entity Based Billing Amount Billed
			JobTxn Line Entity Based Billing Amount Unbilled
			JobTxn Line Job Based Billing Amount Billed
			JobTxn Line Job Based Billing Amount Unbilled
			JobTxn Line Item Based Billing Amount Billed
			JobTxn Line Item Based Billing Amount Unbilled
			JobTxn Line Time Tracking Entity Based Amount Not Billable
			JobTxn Line Vehicle Mileage All Miles Status Billable Value
			JobTxn Line Vehicle Mileage Not Billable Miles Value
			JobTxn Line Vehicle Mileage Billable Miles Value
			JobTxn Line Vehicle Mileage Billable Miles Value Billed
			JobTxn Line Vehicle Mileage Billable Miles Value Unbilled
			JobTxn Line Vehicle Standard Mileage Amount Not Billable
			JobTxn Line Vehicle Standard Mileage Amount Billable
TIME TRACKING FACT
			TimeTracking Duration Quantity Not Billable
			TimeTracking Duration Quantity Billable
			TimeTracking Entity Based Amount Not Billable
FIXED ASSETS FACT
			Fixed Asset Count
SITUATIONAL TYPOS
DOCUMENT ATTRIBUTES
	Document Paid/Open Status		Document Current Paid/Open Status
SHIP METHOD
	Shipe method		Ship method