Who wants to stare at endless rows of numbers on an Excel sheet if you can visualize the information with a click of the mouse and gain real-time insights into your business’s finances?
That’s why connecting Power BI to QuickBooks saves you a lot of headaches (and eye strain). But the process isn’t exactly plug-and-play.
QuickBooks Desktop doesn’t have a Power BI connector as QuickBooks Online does. To extract data from QuickBooks Desktop, you need an ODBC driver. Of course, there’s always the option of building out your own API queries, but we find that the time investment of building your own will generally be far more expensive than purchasing a solution.
Overall, there’s a lot of conflicting information about which software to use and how to configure it to meet your needs. Let’s get to the bottom of this.
But Wait…What’s an ODBC Driver?
ODBC stands for Open Database Connectivity. An ODBC driver allows applications to access data using SQL in data management systems (DBMSs.) ODBC supports maximum interoperability, enabling a single application to connect with different DBMSs.
An ODBC driver allows Power BI Desktop to connect to a QuickBooks file. It publishes reports to the Power BI service using a remote connector. You can configure a Power BI gateway to connect the Power BI cloud service with a local data source name (DSN) or create a similar gateway with QQube to periodically refresh the data within the Power BI service.
The Insider Scoop: Top ODBC Connectors To Use
Setting up an ODBC connector is more complex than connecting directly to a pre-built connector since you need to configure the machine, admin profiles, and QuickBooks. Publishing data to the server with remote gateways is also a complex task.
There are many ODBC drivers available. Based on our experience, here are the top contenders:
FLEXquarters QODBC is recommended by Intuit, the maker of QuickBooks. It’s the cheapest option available but the most complex to set up. You must configure the software, drivers, and machine at multiple user levels (i.e. admin, single-user) for it to work “as advertised.”
You can’t directly extract budget or forecast data. You can only do so through a financial summary report. Additionally, we find the connection finicky at times, although it’s ultimately workable.
QODBC Driver for QuickBooks Desktop costs $199 for a single user and $499 for a single server. Meanwhile, QODBC Driver for QuickBooks Online costs $14.95 per user per month.
This custom CData QuickBooks Connector is much easier to set up than QODBC. By leveraging Power BI’s DirectQuery functionality, users will always be connected directly to the data source and able to view the most recent data.
It also simplifies the connection of the ODBC driver as you only need to configure the ODBC DSN through QuickBooks and the connector through CData’s software. Moreover, CData is a Microsoft partner and offers good customer support.
The CData connector requires special configurations for VMs, but this extra step shouldn’t be a show stopper. CData’s Power BI Connector costs $1,999 per year.
QQube comes with a pre-built Power BI data model and measures. The free PBIX file saves you all the work needed to get the raw tables, clean them, and convert them. According to their CEO, Chunk Vigeant, QQube weaves multiple types of software development kit (SDK) queries to produce the final output (which seems like magic!).
The software has received good reviews from users, and the customer support is also strong and responsive. Just keep in mind that you can only retrieve budget data from summary reports.
The server edition costs $1,595 for 10 users, which seems to be a good deal compared with CData’s annual fee. The software also requires much less work to set up than QODBC, saving you time and money on that front.
From the Trenches: Our Tips on Connecting Power BI with QuickBooks
Here’s what we have learned over the years to save you from hours of head-scratching and frustration:
- Follow the setup guides! Don’t get ahead of yourself.
- Take notes on the configurations you have tried. There’s no reason to make a mistake twice.
- Reach out to support if you encounter problems. The Power BI forum is also a great resource to get answers.
- A lot of content online is outdated. Look at the publish dates or reach out to support for the latest information.
- If you’re setting up someone else’s machine, get admin privileges to both QuickBooks and the computer so you can work on any potential configurations.
- If you’re working for a client, set realistic expectations. The initial configuration, as well as subsequent maintenance and optimization, will be time-consuming.
If you’re not sure which connector to go with, sign up for free trials. They should give you enough time to see if you may run into potential problems with a specific driver.
Lastly, don’t give up! Connecting Power BI with QuickBooks using ODBC drivers is a complex process, but it’s very doable. Plus, the ability to visualize financial data to facilitate decisions on business-critical decisions is priceless.
If your head is still spinning, the SkyPoint team is always here to offer stress-free guidance. Reach out!