Quickbooks

[Veritas]

I want to develope a dashboard of financial statement data pulled from Quickbooks desktop.  What is the best way to pull in the data?  I would welcome guidance from any other users who have developed dashboards from Quickbooks.

[EugeneLebedev]

Reports section in the native quickbooks connector doesn't allow you to do that. You will get aggregated data for the time period that you select through parameters. 

I have developed my own connector that allows you to do that.  All P&L data is combined in a single table. You can also drill down to transactions that make up value of every account, filter by day and see data on accrual vs cash basis. Same for Balance Sheet, Cash Flow, Account Receivables, Budget, etc.

Here are some dashboards I was able to create with the data from my script
Here is more info about it: https://vidi-corp.com/how-to-connect-power-bi-to-quickbooks-online-reports/

My email is eugene.lebedev@vidi-corp.com

[Anonymous]

Click the "Reports" drop-down menu and select "Company & Financial." Click the report you want to prepare. You'll find more reports listed under "Accountant & Taxes." Customize the report by filling in the "From" and "To" date fields or choosing an option from the "Dates" drop-down menu. These options are also available in QuickBooks enterprise hosting.

[BetterIdeaGroup]

Hello everyone! 

 I know this thread has been around for a while and many of you may have done work arounds but for those that land on this query after 2023...great news! QQUBE version 10 has a HUGE focus on Power BI !!

Today I recorded a video  giving you a peek at one of the MANY templates provided by QQUBE. 

Here is a brief tutorial on how to use QQUBE and Aha! to understand what product segments are profitable

https://app.weet.co/play/7fde128b/using-qqube-to-build-a-business-segment-margin-report

[MarissaGO]

Hi @BetterIdeaGroup,

I am using QQube to pull in the Quickbooks data into Power BI. I sync'd the data without error, but now when I open the Power BI Example "financial_pandl_actual-budget-forecast_(qqube6.x).pbix", I get the following errors. I am pulling data from Quickbooks Desktop Pro 2018. Any help is appreciated!

OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E

Load was cancelled by an error in loading previous table

Thank you!

[BetterIdeaGroup]

Hi MarissaGO,
First let me clarify the architecture. QQUBE extracts QuickBooks desktop information and stores it in a SQL Anywhere database. That database is what is used by PowerBI (and other ODBC compliant tools. With the information available it's hard to tell which of the 3 areas are problematic:


1. Environmental - i.e. You exact configuration of hardware / software is giving you trouble. For example the memory or CPU size of your computer could be "giving up". Can you access the same data on another computer without issue?

2. Data related - In some situations data can cause issues. - redoing the query with a sample data file usually roots out this issue

3. A software logic issue. Because of the various pieces of software involved in this solution, once you have ruled out the other 2 area's, the goal would be to sort out which product is having an issue; So to test for a Powerbi issue you would want to know if some other ODBC based data source of comparable size can be extracted on your version. Similarly, for a QQUBE issue, isolate just the QQUBE database by verifying that you can access the same data from the QQUBE Excel templates?

I apologize if all this seems complicated but since these topics live on the forum for awhile I wanted to give others tools for figuring out what might be preventing them from getting the information they need to run their business, If you would like to get some direct help can you book an appointment on:
https://calendly.com/ernestcook

If you suspect an issue with QQUBE, you can open a new case at: https://clearify.com/cases/add-edit-case/0
if you suspect an issue with PowerBI that support request can be started at: https://powerbi.microsoft.com/en-us/support/pro/


Ernest

[MarissaGO]

Hi @BetterIdeaGroup,

I am using QQube to pull in the Quickbooks data into Power BI. I sync'd the data without error, but now when I open the Power BI Example "financial_pandl_actual-budget-forecast_(qqube6.x).pbix", I get the following errors. I am pulling data from Quickbooks Desktop Pro 2018. Any help is appreciated!

OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E

Load was cancelled by an error in loading previous table

Thank you!

[BetterIdeaGroup]

Hi @MarissaGO,
First let me clarify the architecture. QQUBE extracts QuickBooks desktop information and stores it in a SQL Anywhere database. That database is what is used by PowerBI (and other ODBC compliant tools. With the information available it's hard to tell which of the 3 areas are problematic:


1. Environmental - i.e. You exact configuration of hardware / software is giving you trouble. For example the memory or CPU size of your computer could be "giving up". Can you access the same data on another computer without issue?

2. Data related - In some situations data can cause issues. - redoing the query with a sample data file usually roots out this issue

3. A software logic issue. Because of the various pieces of software involved in this solution, once you have ruled out the other 2 area's, the goal would be to sort out which product is having an issue; So to test for a Powerbi issue you would want to know if some other ODBC based data source of comparable size can be extracted on your version. Similarly, for a QQUBE issue, isolate just the QQUBE database by verifying that you can access the same data from the QQUBE Excel templates?

I apologize if all this seems complicated but since these topics live on the forum for awhile I wanted to give others tools for figuring out what might be preventing them from getting the information they need to run their business, If you would like to get some direct help can you book an appointment on:
https://calendly.com/ernestcook

Ernest

[BetterIdeaGroup]

Also:
If you suspect an issue with QQUBE, you can open a new case at: https://clearify.com/cases/add-edit-case/0
if you suspect an issue with PowerBI that support request can be started at: https://powerbi.microsoft.com/en-us/support/pro/

[keithwilhoit]

QuickBooks Desktop to Power BI Dashboards by Data AnalytiX

Using the QODBC middleware provided in QuickBooks Enterprise, data is retrieved from QuickBooks and transferred to the database reporting program Alpha Anywhere/Alpha 5.  Once transferred, the powerful functionality of Alpha 5 is used to transform the data into tabular custom reports that cannot be developed using QuickBooks standard memorized report customization.  The transformed data is exported to CSV data files that are loaded into Power BI files to generate Power BI dashboards.

For more information—

Visit Data AnalytiX website, www.dataanalytix.net

Email, keithwilhoit@dataanalytix.net

Call Keith Wilhoit 630-926-6868

[BetterIdeaGroup]

You can use the ODBC features in Enterprise. Not perfect by a long shot but it is a connection to give you some raw data if you want to fight thru getting the views connected (not obvious how to do it but possible). I have found the QODBC connector fails so if you want to get into reporting between PowerBi and QuickBook enterprise your best option is a 3rd party product called QQube  which extracts the QB data into a local sybase Database. The database model is optomized for reporting so really is PERFECT for using with PowerBi.  It  comes with PowerBi Templates (Desktop files) that cover nearly every area you would want to report on.

I am certified on the product so do let me know if you need help getting QQube or getting it working.

Ernest

[santosh11]

Hi Ernest,

I am santosh Menon from Invent UAE. We are using enterprise version of quickbook and we have plan to integrate QB to this BI tool.

I downloaded trial version of QQube and try to hook up data from Qbooks to QQube to my trial version of Power BI.

But its not working, while it shows sync is sucess and complete, data from QQube to BI is unsuccessful. Please advise.

Should i migrate to QB online?

or Please suggest solutions for this.

I can be reached on 00971 56 7518398 or santosh@invent-uae.com

Cheers

Santosh Menon

[BetterIdeaGroup]

Hi Santosh,

  Congrats on your progress so far !  Based on your description I am left wondering if you have Power BI Desktop installed and have opened any of the Power BI Example pbix files that are provided as a part of QQUBE ? If you open any of those are you seeing your data?

In the video I talk about publishing from Power BI desktop to the Powerbi site. That is a critical step to using the gateway since the gateway has to know what data to sync.   Can you confirm you have done that?  If you would like help getting these products to work I have a link that will let you Book a meeting .

re: switching to QuickBooks online (QBO)  - If you are using the Enterprise version of QuickBooks it's you would not be happy with QBO as it has only a subset of the functionality of QuickBooks Enterprise and is a VERY different interface. 

[jessewysong]

Is there a Power BI specific tutorial anywhere for this? I did all the thing son the QB end, and I can access QB data via Excel, but attempting to get at the data in POwer BI always leads to an error even though I can see all the tables. Very painful to be so close and yet so far. 

I am using Quickbooks Enterprise 2017.

best,

Jesse

[BetterIdeaGroup]

Hey Jesse, It looks like you are using the QB Enterprise ODBC driver from QODBC. Does that sound right?
http://qodbc.com/support.htm
I would first confirm that you don;t have a mismatch between 32 bit and 64 bit. In the example I see you are using a 64 bit driver. Are you running a 64 bit powerbi?

[jessewysong]

Thank you for the reply. I filled out a support request with QODBC, the compnay that makes the driver for QB. It looks like its NOT able to work with Power BI, and perhaps the company hardcodes in any compatiable third party apps. I am including their reply below in case other folks were as frustrated as I.

Hi Jesse,

I would like to inform you that QODBC is not supported to the Power BI desktop. Normally we suggest using MS Access as middleware in QODBC unsupporting application. But in this case, middleware also not working because Power BI is not able to fetch MS Access linked table. 

Please refer:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2584/45/how-to-use-ms...

You can dump QuickBooks data in MS Access.

Import the source data into a new table in the current database, this option will directly extract tables into Access Database, you can view and edit these tables but all modification will not be uploaded unless you build another query to do so.

Please refer:
http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2771

[BetterIdeaGroup]

I just created a new video that show's how you can use the gateway to connect QuickBooks data stored in the QQUBE database. Find the video at:  https://youtu.be/X5w0itYzYtI

Ernest

www.BetterIdeaGroup.com

[Happyman]

Hi Ernest, do you have an email to write you about help on connect QQube to BI? or do you have a website where I can use the professional services for that service? 

Thanks

[BetterIdeaGroup]

Thanks for reaching out. my web site is:

http://www.betterideagroup.com/qb-datawarehouse.html

A new version was just released over the Thanksgiving holiday that has LOTS of powerbi examples, I'd love to show them to you.

my office # is 916-774-2600

[Anonymous]

@BetterIdeaGroup question regarding QQube.  Can I augment the data model of a template PowerPivot 2013 file from QQube with some of my own data / other queries?  Or am I confined to the data model as is?

[BetterIdeaGroup]

Absolutely ! A huge value of the QQUBE product is getting the information OUT of QuickBooks and into a structure for just that kind of work to be able to be done. The engine is SQL Anywhere and readonly userid/passwords get you to the tables/views. The Templates are a great start for basic users but as a developer myself I usually start with just pulling in the tables I need that make sense and then augment with alternate sources as needed in the use case.