Where are the QQube Table and Field Details? | CLEARIFY - Page 1

Mapping Documents and Schema Details

You are an advanced user/developer/expert - or somebody not knowledgeable about QuickBooks details - who is familiar with databases and table joins and is comfortable with using MSQuery or PowerQuery, Access, Crystal Reports, or even Power BI in the traditional manner - choosing specific tables and creating your own joins -  and would like advanced documentation for this process.

You Should Rarely Need Them - Unless...

The whole purpose of the QQube Technology is to avoid having to deal with the traditional manner in which we have been taught to interact with data for the last 4 decades.  e.g. here is a mapping document, and you are off on your own.

QQube uses traditional data warehousing techniques, where the is one FACT table containing measures, and inner joined tables called DIMENSIONS

So unless you are trying to do something that QuickBooks doesn't do underneath the hood, the need to write manual SQL Queries, and join tables from scratch, is generally not part of the average end-user experience.

The end user, just opens up one of the examples, and just starts to drag and drop their fields; further if they use the Excel Add-In, they can use the bubble text information (shown below) to see what each field does, or contains.

• If you are an end-user this saves you thousands of dollars of developer time.
• If you are a developer, it saves you weeks of time, and saves your customer money in the form of development time:  you don't have to learn the SDK, or reverse engineer some raw data connector which doesn't take into effect the hundreds of QuickBooks/SDK quirks, like QQube has.

BUT you may certainly have a legitimate need for writing SQL Queries for specific granular purposes -  OR you just are more comfortable doing it the old fashioned way - no harm in that.

Where to Start

• First, study what is / what is not available in QuickBooks Desktop. This will save you much consternation down the road:
• Second, look through the schemas for each analytic
  • See how the data is organized
  • Review the various dimensions
  • Peruse special topics such as customer vs jobs, reference number vs transaction number, etc.
• Third, open up a Microsoft Access Example from the QQube Configuration Tool to see how the FACT table and DIMENSIONS are tied together.  Example: the field "LinkToCustomerID" in the FACT table links to the field "LinkForCustomerID" in the Customer DIMENSION table.

You will find that QQube provides out of the box examples - for each analytic available in QQube - for Access, Crystal Reports, Power Pivot, Tableau, and PowerBI.  Which means that you never start from scratch.  Just open up an example, and drag and drop.

Use the Excel Add-In

Although Excel may not be the application in which you will end up writing your reports or analytics, you should use it for three purposes:

• Easily familiarize yourself with the data organization, using drag and drop features.
• Use the Bubble Text to learn more about possible field values, and designed intentions.
• Create Manual SQL Queries - if needed - without having to start from scratch.

Add-In and Data Organization

Bubble Text for Deeper Field Information

FOLDER BUBBLE TEXT:

FIELD BUBBLE TEXT

Creating Manual SQL Queries

We provide an easy way to create manual SQL queries, as the Excel Add-In is actually a front-end to MSQuery.

Here is what you do:

1. Load the analytic you want in the QQube Configuration Tool
2. Ensure that there are no synch errors.
3. Open up one of the examples in the QQube Configuration Tool
4. Invoke the Excel Add-In
5. Drag and drop the fields you need
6. Retrieve the SQL Statement from the Connection Properties from the Excel Data Tab as shown below:

If your needs are for advanced SQL elements and functions, you can use this page on Version 12.0.1 for SQLAnywhere for reference: 

NOTE QQube versions 6.x and later are using SQLAnyhwere version 17, but the commands and functions have not materially changed from version 12.x

Updated 4/11//2020