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 businesses can need to analyze data too. Remembering that the primary 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.
On 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 begin, let us brush up on briefly what analytical or Business Intelligence solution usually is. This is a system that periodically extracts needed information from the business transaction systems, loads the information into an additional data store, which is called a data warehouse, and provides an intuitive capability of reporting and analyzing. In a very simple reporting solution, when the information about the 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 database.
At this point, you can stop me and argue that QuickBooks has a variety of useful embedded reports. Yes, of course! It does, but when a business grows at a breakneck pace or in periods of recession in trade, the company's management wants to have more additional flexible reports. Also, it is a possibility the company has a few QuickBooks instances for different lines of business, and it can be very important to have reports consolidating the information about all business directions.
How to access the QuickBooks data?
According to our short explanation of the solution, the first step needed for achieving our goals is to get access to the QuickBooks data. A QuickBooks application has its own file format for storing 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 the 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 development.
We can use any ODBC compatible software for reading from (and writing to) the QuickBooks database. 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 Reports; and tons of others. Also, QuickBooks data can be exported to MS SQL Server, Oracle, and DB2 databases or linked to these database servers. As we can see, there are many potential ways to build as a simple reporting system and a fully functional Business Intelligence solution just because of the 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 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 necessary for reports (Figure 02).
Figure 01. Using Excel's Data menu for getting data from QuickBooks.
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 the Finish button. Voilà! We have the Excel spreadsheet with the necessary data from QuickBooks (Figure 03).
Figure 03. Excel report with QuickBooks information.
We can do all general Excel operations: filter, sort, group and hide data; build diagrams and charts; etc. Moreover, using the 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 the SQL language to access QuickBooks and form report.
OpenOffice Calc
Unfortunately, OpenOffice Calc does not support external data export directly. If we want to see QuickBooks data in an OpenOffice Calc worksheet, creating new database 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), design forms and reports, and 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.
Figure 04. Linking QuickBooks and MS Access.
Figure 05. Linking QuickBooks and OpenOffice Base.
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 database management systems 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 software application that can be very useful for building the QuickBooks custom reports. This is a commercial product called QReportBuilder. The application is a powerful reporting tool that allows us to build reports of every sort and kind in the visual designer (Figure 07) and easily run the reports. 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 the QODBC driver described in the previous part of the article, so we can expand the functionality of our reporting everywhere.
Figure 07. Building report in QReportBuilder.
Figure 08. Save reports in different formats.
Go ahead
As we have just learned, there are a few tools and ways to implement a simple analytical solution and begin to take advantage of Business Intelligence opportunities. 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 the 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.
