Modifications in Version 10

Overview

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:

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)