Converting to QQube 7.x Reports and Analytics | CLEARIFY

Converting to 7.x Reports and Analytics

If you are upgrading to QQube version 7.x  from 6.x, then read no further as there are no structural changes between those two versions.

If you are upgrading to QQube version 7.x from 4.x/5.x then this is your guide.

Conversion Overview

QQube versions 2.x thru 5.x had little or no changes to existing fields or structure.

But based upon customer requests, current customer usage, and market research into technology trends and the tools available for the consumption of analytics - we made decisions, beginning with QQube 6.x to ensure that the future our company remains viable, while keeping the upgrade path as least painful as possible.

This meant that certain structural and field name changes were implemented the changes in this guide, for version 6.x (and kept the same in version 7.x).  We expect there will be no more changes in the future.

If you need advanced assistance, we have a distinguished team of Certified QQube Solution Providers to assist with the conversion process, and use of the new capabilities of QQube. 

What Changed

Changes fall into FOUR categories:

1. Field Name changes 
2. Field Removals
3. Complete rename of analytic 
4. Field Name is the same, but is now in a different table

Why did we make these changes?

• Tools like Microsoft PowerBI and Tableau interact with data warehouses like QQube in different ways than Excel, and on-demand visualization is the trend of the future.
• We want to minimize the pain points of using QQube the first time
• Our first versions of QQube, although successful, were inefficient in data storage, and slow on general refresh times.

If you used the Excel Add-In to create reports in your previous versions of QQube, then you should have no conversion concerns or issues

Change Details

FIELD NAME CHANGES

1. Period 01 Actuals YTD is now Period 01 YTD Actuals
2. Period 01 Budget YTD is now Period 01 YTD Budget
3. Period 01 % Sales is now Period 01 Actuals % Sales
4. Period 01 % Budget YTD is now Period 01 Budget % Sales

We did this to create consistency for naming as we added a few hundred columns to Financial Summaries in , and will add more in the future.  Now the naming structure will be:

Period Name > Period Length (YTD or RT) > Actuals/Budget > "blank space" or % of sales

FIELD REMOVALS

1. 1. Income Columns in Profit and Loss Financial Summary FACT tables.   The additional fields we added for Current Month, etc. have ballooned the number of columns in some of these analytics to 300 or more columns.  Since we already have % of Sales, we made a decision to pull these out and have 144 less columns in the mix.
      1. Period xx Income
      2. Period xx Income YTD
      3. Period xx Budget Income
      4. Period xx Budget Income YTD
   2. Two Unnecessary Fields in the Date DIMENSIONS. 
      
      1. Calendar Date Type (which always said "Default"
      2. Day Number (Same as Day Number in Year - it was redundant)

COMPLETE RENAMING OF AN ANALYTIC

1. Cash Requirements is now called Cash Position We changed the name of the FACT table from vf_CASHREQUIREMENTS TO vf_CASHPOSITION.

FIELD NAME IS THE SAME BUT IN A DIFFERENT FOLDER/TABLE

This is the bulk of change in QQube Versions after Ver 5.x and affects detail subjects like Sales, Job Costing, Inventory, Purchasing, General Ledger Detail, Time Tracking, and Payroll.

The main folder (FACT table) for each subject used to contain all transactional information, whether it was something you measured (e.g. amount or qty), or something you measured BY e.g. Document Ship Method, or Line Billable Status.

Current versions of QQube correct this, by separating measures, from document attributes, line attributes, and geography.

How do I Convert 4.x and 5.x to 7.x? Simpler Than You Think

Excel Add-In

• If you used the Excel Add-In to create your reports in earlier versions of QQube, then all you need to do is open it and let QQube refresh the data. That's it. 

Crystal Reports - Detail Analytics

• Go to the Database>Database Expert, and add the new folders containing the geography, document attributes, line attributes, date attributes, and line data attributes.
• Run the Verify Database (Database>Verify Database) will automatically prompt you with a notice that a field is 'missing' e.g. not in the place it expects, and then ask you to replace it.

Crystal Reports - Financial Summary Analytics

• You won't need to add anything via the Database Expert, just run the Database>Verify Database to add all of the additional fields we have created for these analytics.

Microsoft Access - Detail Analytics

• Essentially you will add your new linked tables, and replace your fields.  If you want to see how the new tables are laid out, just open up the new Access shells in the QQube Configuration Tool.

Microsoft Access - Financial Summary Analytics

• Microsoft Access has a 256 column limitation, so we have created several options for Access developers.  Detailed information on this is found here: 

Tableau, PowerPivot, Power BI - Detail Analytics

• You can always recreate your report from scratch using the new shells in the QQube Configuration Tool, OR open up your existing report, bring in the new folders and substitute the appropriate fields. 

Tableau, PowerPivot, Power BI - Financial Summary Analytics

• Unless you are affected by the four types of renamed fields, or removed fields as noted above, you should be able to just open you report, and refresh..

For Developers and Custom Report Writers

1. Splitting of the FACT table. We moved geography, document attributes and line attributes from the FACT table to several dimensions.
   1. Document Attributes are now stored in a table called vdf_SALES_DOC_ATTRIB  (GL for General Ledger Detail, SALES for Sales, etc.)
   2. Line Attributes are now stored in a table called vdf_SALES_LINE_ATTRIB
   3. Geography is now stored in a table called vdf_SALES_GEOGRAPHY
2. Single Date Options. We created Single Date Options in one folder, because tools like Excel,andPowerBI take a date, and creates year, month, week columns from it.  This may eliminate the potential need for a full blown date dimension -althoughtheyarestillincludedinQQube.
   1. Single Dates are stored in table called vdf_SALES_DOC_DATES
   2. Line Date (Service Date for Sales Docs, and Purchase Order) is stored in a table called vdf_SALES_LINE_DATES

EXAMPLE using the Document Reference Number in the Sales Analytic

• Previous notation: QQubeFinancials.vf_SALESDETAILS.SalesTxnDocumentReferenceNumber.
• Current notation: QQubeFinancials.vdf_SALES_DOC_ATTRIB.SalesTxnDocumentReferenceNumber