Power BI integration with Quickbooks

18May 2020 by Dev Per No Comments

Power BI report for QuickBooks

QuickBook is a leading software application for accounting and bookkeeping. One of our clients wanted to create Power BI reports for data analysis using data from Quick books application. In this case study we discuss:

How we gathered client requirements and understood end-to-end flow of the entire process
Set up the report in QuickBooks desktop version
Integration between Power BI and QuickBooks
Finally identifying the data that needs to be connected and syncing with Power BI to project reports

Solution

We used the below solution components to integrate QuickBooks to Power BI for data analysis, report creation and publishing these reports so that people across the organization can access the reports remotely .

Using QQube as the Middleware to provide data to PowerBI
Schedule Data sync between QuickBooks and QQube
PowerBI Gateway to refresh the data in report from On-Prem QQube
Date/Time based filtering of the data
Tree hierarchy based filters

Challenges

We encountered some issues as below:

QODBC connection using the QuickBooks company’s file had certain limitations for instance if QuickBooks is closed, then Power BI connection didn’t work and throwing error if there’s any popup window is open in QuickBooks
Finding the corresponding tables in QQube in accordance to QuickBooks was a time consuming task
Ensuring this end-to-end integration did not hamper the data quality

Please fill in the details to download the case study.

Results

Accomplishments

Successfully integrated QuickBooks(an accounting software) with Power BI for a seamless
user experience for our client generating custom reports that can be used organization wide
We helped our customer to publish reports in Power BI, this also enabled them to visualize updated
data real time. Users can also filter data and set start and end date to see data for a particular duration as per business needs
Reports can now be shared with all users in the organization, this will not only save time but
also increase the efficiency of the process