Skip to main content

Business Intelligence for Small and Middle Business with QuickBooks

Author(s): 
Bralgin, Igor

 

Foreword

Thinking and speaking about Business Intelligence, we usually imagine a huge and expensive software and hardware solution that is mainly used in a big organization. Although just big companies implement Business Intelligence solutions mainly, small and home business can need to analyze data too. Remembering that the main goal of Business Intelligence is a comprehensive analysis of detailed business data, we have every reason to say a Business Intelligence solution is not only possible but also necessary for a small company.

In this page, I want to describe a few ways to develop simple but useful analytical solutions for small and middle businesses that use QuickBooks software for accounting and financial management.

What Business Intelligence Solution Is

Before we will begin, let us brush up on very briefly what analytical or Business Intelligence solution usually is. This is system that periodically extracts needed information from the business transaction systems, loads information into additional data store, which is called data warehouse, and provides an intuitive capability of reporting and analyzing. In a very simple reporting solution, when the information about history of changing data and consolidation of data from different sources are not necessary, a data warehouse can be omitted and reports can get information directly from the source system data base.

In this point, you can stop me and argue that QuickBooks has a variety of very useful embedded reports. Yes, of course! It does, but when a business grows at breakneck pace or in periods of recession in trade, company's management wants to have additional more flexible reports. Also, it is a possible company has a few QuickBooks instances for different lines of business, and it can be very important to have reports which consolidate information about all business directions.

How to access the QuickBooks data?

Regarding our short explanation of solution, the first step needed for achievement our goals is to get access to QuickBooks data. QuickBooks application has its own file format for store of accounting data, but, fortunately, there is the ODBC driver for reading and writing QuickBooks data files. This driver is called QODBC (qodbc.com) and provides access to the data in QuickBooks 2002-2011 files and opportunity to use SQL queries. The driver does not cost much money and it is very simple in the installation and setup. After installation QODBC, we can go ahead with our developing.

We can use any ODBC compatible software for read from (and write to) QuickBooks data base. For example, depending on our requirements and goals we can use Microsoft Office (Access, Word, Excel) or Open Office; almost any developer tools such as Visual Studio, PowerBuilder, Delphi; reporting tools like Crystal Report; and tons of others. Also, QuickBooks data can be exported to MS SQL Server, Oracle, and DB2 databases or linked to these data bases servers. As we can see, there are many potential ways to build as a simple reporting system and a full functional Business Intelligence solution just because of an installation of QODBC.

Implementation

The next paragraphs say a few words about how we can practically apply QODBC using MS Office or OpenOffice. Both of these software tools provide almost the same functionalities, but essential differences are prices – OpenOffice is free, MS Office costs some money.

QuickBooks – QODBC – MS Excel

If we use MS Excel, access to QuickBooks data can be set by using the Data Base Query wizard (just be sure that the Microsoft Query Add-on is installed). Select the Data menu, then select the Import External Data menu item, and after then select the New Database Query menu item. Now the Choose Data Source dialog window is available (Figure 01). Select the Data Source name that is associated with currently opened QuickBooks and click OK. Now Query Wizard dialog window allows us to choose the QuickBooks table and columns which are necessary for report (Figure 02).

 Using Excel's Data menu for getting data from QuickBooks

Figure 01. Using Excel's Data menu for getting data from QuickBooks.

 Choice of QuickBooks tables and columns for report

Figure 02. Choice of QuickBooks tables and columns for report.

The two next optional steps of Query Wizard allow us to set the filter and sort criteria. Click Finish button. Voilà! We have the Excel spreadsheet with the necessary data from QuickBooks (Figure 03).

 Excel report with QuickBooks information

Figure 03. Excel report with QuickBooks information.

We can do all usual Excel operations: filter, sort, group and hide data; build diagrams and charts; etc. Moreover, using Refresh Data function (Data menu → Refresh Data item) we are always able to have the last updated data from QuickBooks. The most astounding fact is that users do not have to know SQL language to access QuickBooks and form report.

OpenOffice Calc

Unfortunately, OpenOffice Calc does not support external data export directly. If we want see QuickBooks data in a OpenOffice Calc worksheet, creating new data base in OpenOffice Base we should connect it to our QuickBooks data and after that export data from the OpenOffice Base database to an OpenOffice Calc worksheet.

QuickBooks – QODBC – MS Access or OpenOffice Base

MS Access or OpenOffice Base applications give us more flexibility in accessing and analyzing data than a spreadsheet application does.

Both of these software tools can be connected using QODBC to QuickBooks, and they can be linked with all necessary QuickBooks tables at once (Figures 04 and 05). Moreover, we can use quite powerful additional functionalities to create tables and queries (Figure 06), to design forms and reports, and to program macros. Thus these applications provide an opportunity to build a serious analytical solution, because we can consolidate data from different sources (from two QuickBooks files, for example), store history of data changes and "upload" newest data.

 Get QuickBooks data to Access

Figure 04. Linking QuickBooks and MS Access.

 Get QuickBooks data to OpenOffice Base

Figure 05. Linking QuickBooks and OpenOffice Base.

 OpenOffice Base report with QuickBooks information

Figure 06. OpenOffice Base report with QuickBooks information.

QuickBooks – RDBMS

The most powerful and real Business Intelligence solution we can build using one of industrial data base management system such as MS SQL Server, Oracle or DB2, but this subject goes beyond the scope of this subject of simple and not expensive approaches.

QuickBooks – QReportBuilder

Last, but not least, in this discussion of simple analytical solutions is another one software application that can be very useful for building the QuickBooks custom reports. This is a commercial product called QReportBuilder. The application is the powerful reporting tool that allows us to built reports of every sort and kind in the visual designer (Figure 07) and run the reports easily. QuickBooks allows us to save reports in different formats such as pdf-format, dynamic HTML, rich text, plain text, MS Excel (Figure 08) and send reports by email and fax. Also, it is possible to combine QuickBooks reports with external sources such as Excel or MS Access. Moreover, installing this application we have obtained QODBC driver described in the previous part of the article, so we can expand functionality of our reporting everywhere.

Building report in QReportBuilder 

Figure 07. Building report in QReportBuilder.

 Save reports in different formats

Figure 08. Save reports in different formats.

Go ahead

As we have just learned, there are a few tools and ways to implement simple analytical solution and begin to take advantages of Business Intelligence opportunity. Understanding of real business situation is the key to success and competitive advantages, and this understanding is better with having analytical tools. On account of article format, we have just touched the surface of the wide area of Business Intelligence and analysis. There are many snags, nuances, and challenges in this field, but the general idea, I hope, is clear.