The New ODBC Access in Enterprise Solutions 2011

Well, it is that time of year for the annual QuickBooks release, and the topics for newsletters, blogs, webinars turns to new features, how they work, and what they mean to your customers. This is one of those years where Intuit created several long time requested features, which should make some users happy.

Note: This article was originally published in September 2010.

On the accountant side, I am fond of the QuickBooks File Manager feature; Intuit has certainly made it easier to organize your clients’ files. Also introduced was a long time requested Balance Sheet by Class, which is a welcome addition, but still requires adjustments to make it work to your benefit. (Certain transactions do not have a class entry available).

Other improvements include an optional history pane that is available when you open up a transaction screen, and populate a name. It shows pertinent information like current balance, most recent transactions, contact info, etc..

Improvements in inventory adjustments and adding a Collections Center for Accounts Receivable are solid features. Multi-Warehouse in QuickBooks Enterprise will also be a big hit in the entry level mid-market space.

But I digress. I will let my colleagues pontificate in detail about the slew of new features in this year’s QB releases, so that I can focus on one specific improvement – the new ODBC access for Enterprise Solutions.

Do we really have direct access to the QuickBooks Database?

Yes – this is the real deal. Use an ODBC connection and get your QuickBooks Data.

For years many of us have been aggressively requesting that Intuit give us direct access to the Sybase engine – at least for reporting purposes. The SDK has been very successful at creating a safe, secure mechanism for getting data into QuickBooks – but it has been a constant struggle for those of us who provide custom – and product – reporting solutions to thousands of QuickBooks customers who need something that QuickBooks does not provide out of the box.

Intuit made its first attempt at making data available for the Mid Market when they released the web base Business Analysis tool for Enterprise Solutions last summer. However they pulled the product soon after release. For me it was a personal disappointment – not only because I had worked for 2 ½ years as a data base analyst, and schema architect for that project, but because many of us wanted to provide solutions without the pain.

So last fall, the new Intuit team reviewed their direction, and provided a new method: connecting directly to the QuickBooks Database via an ODBC connection. Although I am decidedly still disappointed that the data is not organized so that people do not have to figure out the logic – such as it was in Business Analysis, I have always been in favor of exposing the data to those of us with the propensity to work with database technology. For this I give Intuit a positive response for this first release – it is way, way, way overdue.

So what is the connection to the database?

What my dear engineering friends at Intuit did was to create a series of data ‘Views’ which give us a window to the raw QuickBooks data, and thereby allowing us to use Excel, Crystal, or any ODBC compliant application to extract it.

In technical terms, a view is a dynamic stored query of the physical tables. It looks like a table, but in fact only fills up with data when you invoke its use. A database engineer does not control the physical details of a view like a table (you can with materialized views, but that is beyond the scope of this article); but simply writes a SQL statement that pulls all or a subset of the data from a table – or multiple tables – into the view.

A fictitious oversimplification of what the Intuit Engineers might have done, would be: CREATE VIEW “QBData” AS Select CustomerID, Amount from InvoiceTable. You would then open up Excel, invoke Microsoft Query, and see the View named “QBData” as if it were a table, showing CustomerID, and Amount as fields.

You may ask, why not just query the database directly? In most cases the view only contains a subset of the actual table data, or is organized in such a way, that it will make it easier for you to get what you want. This is exactly what the Intuit engineers did: they created a carefully thought out subset of the pertinent QuickBooks data that will allow experts to create reporting and data analysis solutions without having to know the definition of every single field in the database – including those fields which would never be used for reporting purposes.

Here is a glimpse of what you would see if you opened up Excel and invoked the new ODBC driver:

Figure 1

Figure 2

Good News, Bad News

Let’s look at the positive side of this ground breaking feature.

Unlike the SDK which needs to go through several layers to retrieve QuickBooks Data, this is as fast as you will ever get. Think of it as running a QuickBooks report – you are using a parallel mechanism.
After years of requests, we finally get access to budget data. In the SDK, it required that you set your system date to the end of the year; additionally you could not filter budget data by class. I know there will be people dancing in the aisles for this one.
Do you know those shipping addresses that are locked up in the customer files? You get the keys to that.
Correct multi-currency implementation. IMHO the SDK folks completely missed the mark on their interpretation of how this was to be used; in fact we were unable to provide any kind of custom reporting service or solution for multi-currency requests. This implementation, however, follows expected accounting logic. It is a big time welcome.
Costing information. At the time of the alpha thru early beta testing releases, this was not available, but I am so happy that my ‘begging’ did not go unheeded. Sales subjects will now have a full feature set.
Custom field names. When QuickBooks added additional custom fields to Enterprise Solutions last year, an SDK was not released, and we were left with nameless custom fields.
The ability to link transactions together with minimal program effort. For those of us with database experience, joining several similar tables to make one big table (UNION statement) can be difficult. The engineers wisely made the transaction tables with the same number and order of fields; this was a brilliant move.
While consulting with my Intuit engineering friends on the technical details for this project, ‘Uncle Chuck’ was granted two special requests: The ability to ascertain who entered and changed transactions (for my CPA colleagues); and providing the transaction number to make it easier to match back to QuickBooks when creating reports (people can often forget to put in a reference number on vendor transactions – or in the case of a deposit transaction, it doesn’t exist).
Account order and Customer order. Nice to know that we can pull out our account/customer lists in the order in which we placed it in QuickBooks.

So what is missing?

Payroll information. I do not purport to know all of the legal ramifications here, so I am willing to give Intuit the benefit of the doubt, until they work this out. Remember that we could get payroll information – in a roundabout way – using the SDK, but the permission mechanism was a little different.
Employee information. We get a few fields, but far less than what the SDK gave us – which in itself was already dearth.
Worker’s comp code information.
Payroll items. Both wage and non-wage items are MIA and would still require the SDK.
Billing Rates. Doing WIP will require going to the SDK to get that information.
Company information. Whereas the SDK provided that information, it is not available through these data views.
Company preferences. Helpful when determining whether a file uses a feature – or uses it in a certain way.
Vehicle and Vehicle Mileage. This is a lesser used feature, but we do see clients who use it for time and billing.
Bank Transfers.
Custom form field names are MIA. The SDK was never able to give us this information, but it would be nice to have the custom field names that we use in all our forms.

So what is the verdict?

The ability to get ‘direct’ access to the QuickBooks database is certainly a step in the right direction and way overdue. The fact that we have full access to data in the sales subject arena at least gives us a good piece of meat to chew on. The manner of implementation for the database purist is exquisite – thanks to the Intuit engineers.

But three subject areas – Job Costing, WIP, and Financial – lack the pieces needed to construct complete reporting and analysis solutions. It is ironic that we finally have access to the budget information, but cannot create a financial compendium, because we do not have the payroll information (unless of course, you do not use QB for payroll).

The SDK had the ability to programmatically pull dozens of QB reports, so that you would not have to reverse engineer the logic that QuickBooks uses for cash basis reporting, job costing idiosyncrasies, or how to correctly piece together A/R or A/P. Here you have no such crutch.

Many developers will be burning the midnight oil trying to figure out the logic of the linked transactions – or fields that contain numbers, where you would expect an item name, or payment method name, etc. (I am so glad that I already lived through this part).

This is an implementation for experienced database experts – and people comfortable with SQL statements; it will make many of them smile from ear to ear – late night forays or not. For those of you without SQL skills, and who simply want to go into Excel and get some QB data, I strongly assert that you will need assistance to do so. Either that, or look at Expanded Reports, KPI, QVinci, or QQube. Remember too, that QODBC is still convenient for those who have invested a lot of time in learning its mechanism, and where speed is not an issue.

Where do we go from here?

Our new company CLEARIFY is establishing a community to provide more detailed documentation, and to provide some pre-built SQL queries that you can plug in to Excel to get sales information. We will unveil this at the upcoming Sleeter conference during my presentation on ODBC access. In the meantime, we will provide some teasers for you in next month’s article.

If you want to get started, from within QuickBooks go to File>Custom Reporting and a screen will give you instructions for setting this up. Again, we will tackle these details in the next article, and at the Sleeter Conference.

Chuck Vigeant is the Managing Partner of CLEARIFY, LP and is known as the “grandfather” of Crystal Reports and Business Intelligence for QuickBooks. He is considered the foremost expert on QuickBooks ODBC connectivity and both the Intuit SDK and IPP offerings for data analysis and integration, and his company is the world’s largest provider of Business Analysis, Business Intelligence, and custom management reporting systems for a variety of Intuit products. Chuck has recently expanded his efforts into creating specialized small business data warehousing applications, which have fundamentally changed the way people extract and use their data. His QQube application now makes it possible to do the most complex QuickBooks data analysis in Excel or SAP Crystal Solutions applications without an engineering or programming degree – or even knowledge of tables or relationships.Chuck achieved several technical certifications – most notably MCSE and CCNA; and holds a Bachelor’s degree in Business and Public Administration from The University of Hartford, and a Masters of Education degree from The University of North Texas.