skip to Main Content

Power BI Quickbooks Dashboard

Power BI Desktop View of Our Clients by State (Map) and Breakdown by How We Help Them (TreeMap)
(Yes, this is Quickbooks Data in Power BI, and it was Painless. See Below for Examples Using Power Pivot Too)

Too Legit to Omit (the analytics, that is)

For a couple years now, we’ve been quite a bit bigger than the two-person operation of our early days.  Like I mentioned in a previous post, there are now somewhere between 15 and 20 people who work for us in some capacity ranging from full-time to part-time.  There are a lot of moving pieces these days – pieces that demand our attention, our comprehension.

For example, was our website redesign successful?  And if so, HOW successful?  How much new business did it generate?  How many new clients are we helping relative to the previous “incoming” rate?

But one of those moving pieces is ME.  I traveled more this year than any year previous, and that kept pushing our internal BI onto that proverbial back burner.  (Being so busy is a good problem to have, but still).

As the year winds down, however, I find myself at HQ for a sustained period of time, and that’s afforded me some time to focus on this.  Can you imagine how GOOD that feels?  To finally find time to apply my own craft internally?

It’s thrilling, in all honesty.

Quickbooks does offer reporting, but it’s not that…  Intuit. (get it?)

Quickbooks Built In Reports Leave a Lot to be Desired Compared to Power Pivot and Power BI

Some of the reports built-in to Quickbooks.  Useful for accountants maybe, but not for strategic analysis.

Our financials are the logical first place to start.  And our financials are in the hands of our accounting firm.  Specifically, they are stored in Quickbooks.

This, of course, poses a problem.  Because like ALL accounting and ERP systems, Quickbooks is primarily focused on being a great accounting system.  A system that collects, stores, organizes, and routes data.  Quickbooks is NOT an analytics tool.

And being an analytics (or BI or reporting, whatever you call it) tool is a full-time job.  ANY system whose job it is to collect/organize/route data will NEVER be sufficient for reporting and analysis.  NEVER.  I’m not kidding.  We should never expect different, and that’s not a “knock” on these vendors.  It’s just too many missions for any one company to execute.

Furtheremore, I’ve yet to meet such a system that “wants” to give you its data.  Nope, these systems, Quickbooks included, have complex internal data storage structures, and they’re not exactly keen on offering clean straws by which you can “slurp” the data, nor translators to make said data understandable and useful.

Quickbooks, let my data go... into Power Pivot

What Moses Would Say, Were He Around Today and Running a Mid-Sized Business

QQube:  Translator for Power Pivot / Power BI Folks Like Us

QQube is the signature product from a company named Clearify.  (Goodness gracious, Clearify is a FANTASTIC name for a company in this space.  But QQube…  hey, I get WHY they’d name it that, but I bet they wish they could have a do-over.)

I’ve been talking to the owner/founder of Clearify for several years now because I’ve seen the enormous potential – MILLIONS of companies use QB as their accounting system, and the Power Pivot / Power BI revolution makes industrial-strength tools affordable AND learnable for those companies for the first time ever.  Chocolate, meet peanut butter.  While Microsoft is indeed focused on the world’s largest companies, and for good reason, Small to Midsize Businesses (SMB’s) could be getting TREMENDOUS value out of this stuff…  if they can get to their own data, that is.

Somewhere along the way, we (PowerPivotPro) hired a real accounting firm who migrated us onto Quickbooks – Quickbooks Premier, specifically, running on our accounting firm’s servers.  And like I said, I finally got time recently to try it out with our own QB data.

How it Works

How QQube Works

Quickbooks Data is Locked Away AND Impossible to Understand in its “Raw” Storage.  QQube creates a separate, clean database for us, and we connect Power Pivot / Power BI to THAT.

QQube Config - What Data do you Want to Make Available to Power Pivot and Power BI?

You Use the Config Tool to Tell QQube What You Want to “Replicate” from QB into QQube.
(In this case I’m telling it to grab just the Sales-related data, but you can multi-select LOTS of stuff).

QQube ships MANY templates for Power Pivot and Power BI

Then You Can Start With One of their MANY Pre-built Power Pivot or Power BI Templates
(Just click Open)

QQube ships MANY templates for Power Pivot and Power BI   image

The Templates Contain Sample Quickbooks Data by Default, But Go Into Power Pivot, Click Refresh All, and Bam, It Will Now Contain YOUR Data.  No additional config required.

Active Customers Analysis in Quickbooks, Using Power Pivot

Of Course, I Immediately Started Adding My Own Measures, Pivots, and Charts to the Template
(This is REAL DATA from our biz, not a sample)

Hey, the website DID seem to work –  note the jump in October after the site went live on Oct 1, but what the HECK was going on in April/May?  We don’t have sufficient history in QB yet to see whether this is a seasonal trend, but next year, we will now have the ability to perform Year Over Year comparisons…  hello DATEADD function!

QQubes's List of Tables/Views in Power Pivot

If You Want to Start 100% From Scratch, Open up Existing Connections & Pick from the Tables/Views Available
(Here I’m Grabbing the Item Table, which I used in the TreeMap view at the very top of this post)

My Observations So Far

1) This is a very, VERY serious and comprehensive product. I know that MS has a content pack for Power BI revolving around Quickbooks, but in my experience, this sort of thing (data adapters for accounting and ERP systems) is not something you can do super well unless you devote an entire company to it.  And staff that company with longtime experts on the accounting or ERP system in question.  (Our good friends at Simplement, for instance, fit this mold PRECISELY, but for the SAP space rather than Quickbooks.  Yes, QB and SAP are products for very different “scales” of business, but the similarities in terms of the “translator” biz are striking.  The folks at QQube have similarly long histories with QB).

2) The schemas and templates could be a lot better-optimized for us.  Being a company that is full-time focused on building a robust translation layer is necessary to do a good job, but it’s all-consuming work.  We should not expect them to ALSO be world-class Power Pivot and Power BI experts.  So there’s a lot of room for them to optimize here.  EX: I don’t particularly like the way they handle their date/calendar tables yet for instance, they’re not yet leveraging measures very much in their templates, and their table names are all overly-long which makes for awkward formulas.

3) That said, I AM absolutely getting tremendous value out of this already. I’ve gone from “hey I can’t get the data at all, it’s not even worth TRYING,” to quickly feeling spoiled and saying “hey, that data you gave me?  I want it improved.”  And they ARE listening to me, so I think we’ll also be seeing rapid improvement on this front.

4) Note that QQube is NOT new.  It’s been around for years already, and lots of customers are using it.  It’s just that the Power Pivot / Power BI angle is relatively new for QQube.

More Info

Anyway, I’m going to keep at it, and will update you on my progress over time.  I’m stoked to finally be doing this.

In the meantime, you can check out their offerings on their website:

QQube Product Offerings

It’s NOT expensive, starting at $425, which is kinda crazy.  I quite seriously expected it to be a couple grand, but I won’t complain.

Questions

Do you use Quickbooks?  If so, are you relying on their built-in reports, or somehow building your own?  If the latter, how?

Drop me a comment and let me know, I’m curious for multiple reasons, including “hey I own a business that uses Quickbooks and would love to compare notes.”

And if you have any questions for me about my experience with QQube so far, drop a comment on that, too.

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 18 Comments
  1. Very interesting, I had noticed that PBD offered to open data from QB, we are presently reviewing our CAO and moving toward QB, will definitely have a go and hopefully a load of questions by January 2016.
    Cheers!

    1. Connectors are to raw data – and the PowerBI only connects to the QuickBooks Online version. QQube works with all desktop editions, including multi-currency, CA, UK, NZ.

      QQube transforms all of the raw spaghetti in the existing database into usable pieces. So if you were connecting to raw data you would have to reverse engineer, deal with tables and relationships – something that could take weeks, or even months to figure out. QQube has much logic, and management reporting that QB doesn’t have, and probably won’t.

      QQube already has the shells for many applications, including PowerPivot of course, PowerBI, Crystal Reports, Tableau, etc. etc.

      Shells for each subject in QQube are already complete – it is never necessary to tie one subject to another, unless you are trying to do something that QuickBooks doesn’t do underneath the hood.

      There are two pages for the technically inclined from the CLEARIFY® site that can explain a little more. One is how to use QQube with various applications: http://www.clearify.com/wiki/view/359/using-qqube

      Second one is how the QQube database works: http://www.clearify.com/wiki/view/89/about-qqube

      Great write-up from the best PowerPivot mind in the business, Rob Collie

    2. Do you have QuickBooks Desktop Or Online? PowerBI works with QB online, QQUBE works with QB Desktop. Also, QQUBE first and foremost is a data warehouse – so everything is in it. Want a P & L ? It’s there. Want General Ledger Detail? It’s there. It’s close to impossible to get GL detail from QuickBooks without using the QB API.

  2. Rob, Great post. My consulting focus is reporting, BI and integrations. I use QQube and Excel quite extensively with my clients. The data extracted is as complete as the SDK will allow. It saves me from having to do table joins and vlookups and to easily assist clients with more intricate job cost and inventory reports. You still need to understand QB and how txns are handled. Its easy to manipulate to get the data you need for your report. I have just recently used with PP and loved it as it greatly reduced the size of the resulting Excel file and it was the only way to present the report in the manner the client wanted. QQube connects great to Tableau whereas the QB ODBC driver does do well.

  3. I’m really pleased you posted on this. I’ve been following your blog for the last year as I’ve discovered the offering of the Power Suite of products. In the last two weeks I’ve realised that there is a massive opportunity for helping accounting firms/ financial departments with doing exactly what you’ve done. I’ve been hacking around trying to extract the data from the database without a “connector” and after reading your post now realise that it is not a worthwhile use of my time. I appreciate the post.

    1. Bradford,
      QQube is the only way to go if extracting data out of QuickBooks. Clients are crying for help with reports written the way they want and having templates to work from. Feel free to contact me offline if you want to learn more about QQube.
      BTW, in my post.. I meant to say that QQbue works great with Tableau, whereas the QODBC does not.

  4. Hi Rob,

    I follow your post for all updates you make. Also this is my first point for any queries related to power pivot.

    I have a suggestion for you. Normally if I am looking for anything I will put that in search box and look for relevant articles for it. I was wondering can you just post a excel file with following details for all your post

    Blog Title
    Date of posting
    Link to article.

    I understand you can easily extract this from backend and for us as readers it will be really helpful to have a consolidated list and then we can just search within it and click on the relevant link.

    What say !

  5. “Somehow building our own” is my answer. To get useful reporting that I *attempt* to do on a monthly basis (for a small business), I have memorized reports that I export, then copy/paste into existing spreadsheets and refresh my pivot tables. I use a few VLOOKUPs. A few reports require some cleanup and tweaking. It’s doable and usually accurate. It is WAY more flexible than anything QB provides. However, I estimate I spend about two days per month on reporting. We might be a small biz, but I have high standards and that is just TOO. MUCH. TIME. And it’s only updated once a month, which is so five years ago.

    Imagine – a dashboard driven by data from multiple sources that is accessible from anywhere and close to real time! Reporting without hours of error-prone copy and paste! So I’m pretty excited about stepping into Power BI. Toying with two options: Quickbooks Pro + QQube or Quickbooks Online with the Power BI connector. Really not sure – I see pros and cons to both. Any wisdom is surely appreciated.

    1. Katherine,
      QQube provides the QB data for you to report on. its not a report writer. I use QQube quite extensively with Excel and Power Pivot. its awesome!. Reports are instantly available to client for reporting. Dashboarding and BI is my passion but lots of the QB community just aren’t there yet.With Power BI they have access to data and visualizations that to this date have only been available to larger entities. I think PowerBI is a huge gamechanger. It Gives SMB’s a huge leg up at minimal investment. Feel free to reach out to me if you would like help with QQube or QB reports.

      1. Fran,
        Thanks for replying! I see my comment should have read: Quickbooks Pro + QQube + Power BI (which was my intent). The idea of transitioning to QB online, which we have considered for other reasons, is appealing, but I am nervous about loss of functionality when I am so accustomed to Pro. Once connected to Power BI, is the data coming from QB Online comparable to QQube/QBPro?

        1. Katharine,
          We are getting a bit off PowerPivot topic but…
          QBO has a different table structure than desktop. Not nearly as robust. Moving to QBO depends on what you are doing with QB. If you are heavy in Job costings, inventory or even distribution – stick with desktop.

          1. Regarding QB Online, correct me if I’m wrong, but I don’t think you can control what you connect to from Power BI. I know there is a Content Pack which provides a Power BI Model with some limited information, but I don’t think you can connect to QB Online from Power BI Desktop which you would need if you wanted to interact with the Power BI Model.

          2. Tim, I agree with you. I believe Flexquarters has built a QODBC driver for QBO and with it you could probably connect. But that starts getting above my pay grade pretty quickly.

          3. I just noticed there is a connector to QBO from Power BI.and it does look like its pretty limited.

    1. Mike, Your comment reminds me that QB online has a Power BI Connector. At first I shied away from it considering that QBO customers are usually their data is not robust enough to provide meaningful Dashboards. But I was proven wrong.
      I have my own company on QBO and found that the Power BI “out of the box” dashboards developed by Intuit are very well done and useful. My work centers around supporting the QB community with reports and dashboards and this is one area I haven’t dived into yet.

Leave a Comment or Question