Skip to main content

QuickBooks Online Pack

Ceré Rettig
  • Updated

Pre-built Dashboards

We provided 8 pre-built QuickBooks Online dashboards that can be loaded into your dashboard or operational intelligence instance for you to use with some common QuickBooks Online metrics that will connect to YOUR QuickBooks Online account. When loaded, the dashboards will query QuickBooks Online data in real-time, returning the most up to date metrics. Below are the available dashboards and the metrics found within each dashboard. Must be an Administrator to setup.

Prerequisite: You will need to set up the Quickbooks API connection. You will need MS SQL Server or PostgreSQL repository to utilize these.

Please Note:  Must have minimum privledges of Data Administrator to setup to set this up. 

QBO_pack.png 

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

Quickbooks Online API Installation Instructions

Creating a SQL Database

Setting SQL Permissions

Running the SQL Script

Adding Your Data Source to iDashboards

Setting a Global ID for Your Data Source

Importing Charts and Dashboards

Importing Data Sets and ETL Jobs

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

1.  QuickBooks Scorecard

The Quickbooks Scorecard is a single dashboard that showcases a high-level view of all your financial metrics. Metrics include the total check checking account balances, net income (current fiscal year), total income and balance due by month (drilldown to amount paid and balance due by customer), count of past due invoices and balance due, total accounts receivable (AR), AR aging, revenue by customer (total, paid, balance due, past due), total accounts payable (AP), AP aging, expenses by vendor (total, paid, balance due, past due). 

2.  Income Statement (P&L)

A profit and loss statement (P&L), summarizes the revenues and expenses incurred. These records provide information about a company's ability to generate profit by increasing revenue, reducing costs, or both. Metrics include revenue/expenses/profit by month, income/expenses by account (down to fully qualified name), and income/expenses/profit compared to last year (drilldowns through 3 account levels). User-driven filters for class and a custom date range are also included. 

3.  Accounts Receivable and Customer Income

Metrics include open balance by customer, profitability by customer (fiscal year-to-date), accounts receivable (AR) aging by customer.

4.  Customer Detail

Metrics include total open balance, accounts receivable aging, profitability (fiscal year-to-date), contact information, revenue and balance due by month, revenue and balance due by transaction with drilldown showing line level details including the items sold, classes, quantity, and price. The dashboard also includes user-driven filters with a dropdown to choose a customer name and date pickers for start and end date to build a custom date range. 

5.  Income (Sales)

Metrics include percent open balance for invoices with a balance due, past due balance amount, total income and balance due by month with drilldown to amount paid and balance due by customer for month, amount paid and balance due by class, amount paid and balance due by income account with drilldowns through the various income account levels to see total paid and balance due by item. The dashboard also includes user-driven filters with dropdowns to choose multiple customers by name, classes, and whether invoices are current or past due and date pickers for start and end date to build a custom date range. 

6.  Purchases (Expenses)

Metrics include purchases by class, purchases by month with a drilldown to see purchases by day for month, purchases by expense account with drilldowns through the various expense account levels, purchases by vendor, and upcoming bills due by vendor and due date. The dashboard also includes user-driven filters with dropdowns to choose multiple vendors by name and classes and date pickers for start and end date to build a custom date range.

7.  AR and AP

This dashboard compares your receivables (AR) to your payables (AP) and displays a 12-month cash flow history. Metrics include total, current, and overdue accounts receivable and payable, balance by customer, AR and AP aging with a drilldown to see balances by customer, and cash flow by month. 

8.  Budget vs Actuals

Metrics include the budget vs actuals report by account with details down to the fully qualified name, net income showing revenue, expenses and profit compared to current budget and last year actuals with drilldowns through all income and expense accounts.

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

In SQL Server: Setting SQL Permissions – Running the SQL Script

In iDashboards/TruOI: Adding Your Data Source – Setting a Global ID for Your Data Source

In iDashboards/TruOI:  Importing Charts and Dashboards – Importing Data Sets and ETL Jobs

What you will need:

  • QuickBooks Online Login
  • QuickBooks Online account
  • QuickBooks Online developers account (it's free)
  • iDashboards Data Admin Login
  • iDashboards http://<server:port>idashboards/
    • For your idb_archive
  • DataHub http://<server:port>/idbdata/
    • For your etl_archive
  • SQL Server Login (the one used for your iDashboards setup)
  • SQL Sever (MS SQL, MySQL, Oracle, PostgreSQL)
  • An SSL Certificate as Quickbooks requires you to have a secure connection
  • SSL implemented
  • A fully qualified domain name (domainname.com) localhost and IP addresses will not work
  • The QuickBooksOnline.zip downloaded onto your computer and unzipped.

What is in the QuickBooks Online.zip?

There are: two zip files, the etlarchive which has pre-made ETL Jobs and Charts, the idbarchive which has pre-made Dashboards, Charts and Picklists, and a QuickBooks Online MS SQL script.

Quickbooks Online API Installation Instructions

Follow the QuickBooks Online API instructions for the section: Connecting to QuickBooks Online This is the very first section of the article. For this pack you will not need to create data sets or ETL jobs. They come pre-made. We will be importing them once the setup is complete.

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

In SQL Server: Setting SQL Permissions – Running the SQL Script

 

In iDashboards/TruOI: Adding Your Data Source – Setting a Global ID for Your Data Source

In iDashboards/TruOI:  Importing Charts and Dashboards – Importing Data Sets and ETL Jobs

In SQL Server

Creating a SQL Database

We need to create your database in MS SQL (or MySQL, Oracle, PostgreSQL).  Your Data needs a place to live within the iDashboards structure.

  • Open MS SQL Server Management Studio (SSMS)
  • Connect to your server. ConnectToServerJPG.JPG
  • Right click on Databases
  • Select New Database
  • In the Database name box, type in idb_datastore
    • If idb_datastore already exists please move on to Setting SQL Permissions.
  • Click OK

You've now created the database where your QuickBooks Online information will live.QuickBooksOnlineDatabase.gif

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

In SQL Server: Running the SQL Script

In iDashboards/TruOI: Adding Your Data Source – Setting a Global ID for Your Data Source

In iDashboards/TruOI:  Importing Charts and Dashboards – Importing Data Sets and ETL Jobs

Setting SQL Permissions

  • Now we need to make sure your login used to connect SQL and your dashboards has the right permissions.
  • Open the Security folder
  • Open the Logins folder
  • Select your iDashboards SQL user login
  • Right Click on the user and select Properties
  • In the top left Select User Mapping
  • Click on idb_datastore and make sure it is mapped to db _owner by clicking the box next to db_owner in the list below.
  • You've now set your SQL Permissions for the QuickBooks Online database.SQLPermissions_2020-04-23.gif

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

In SQL Server: Setting SQL Permissions

In iDashboards/TruOI: Adding Your Data Source – Setting a Global ID for Your Data Source

In iDashboards/TruOI:  Importing Charts and Dashboards – Importing Data Sets and ETL Jobs

Running the SQL Script

  • Go to File
  • Click on Open
  • Select File again
  • Select the QuickBooks Online Pack folder
  • Select the QuickBooks.sql file
  • Click the Execute button
  • The data, and ETL jobs, will now connect with your charts, picklists, and dashboards.GoogleAnalyticsSQL.gif

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

In SQL Server: Setting SQL Permissions – Running the SQL Script

In iDashboards/TruOI: Setting a Global ID for Your Data Source

In iDashboards/TruOI:  Importing Charts and Dashboards – Importing Data Sets and ETL Jobs

In iDashboards/TruOI

Adding Your Data Source

We need to add your data source. 

  • Log into your iDashboards: http://<server:port>/idashboards link as Admin. 
  • Click the Admin Icon
  • Go to Data Sources
  • Click New you will add it as a MS SQL Server (Microsoft Driver).  If you are using a different SQL database then please choose the appropriate database for YOUR SQL source. 
  • The picture below is how it will look:mceclip2.png

With your information input here, it will look similar to this picture below. 

mceclip0.png

Click Save and your Data Source will be added. 

It should look like the outlined data source in red in the picture below. 

The asterisk (*) shows that it is connected, at this time there will not be any charts or data sets linked to it.  Once we are done setting up the Quickbooks Online pack you will be able to come here and see the linked Charts, Picklists, and Data Sets. 

mceclip1.png

 

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

In SQL Server: Setting SQL Permissions – Running the SQL Script

In iDashboards/TruOI: Adding Your Data Source

In iDashboards/TruOI:  Importing Charts and Dashboards – Importing Data Sets and ETL Jobs

 

Setting a Global ID for Your Data Source

We will need to set a Global ID for your Data Source

Importing Charts and Dashboards (idb_archive)

We can Import your Charts and Dashboards

Importing Data Sets and ETL Jobs (etl_archive)

  • Log into your Data Hub as Data Admin (Not iDashboards) at your link: http://<server:port>/idbdata
  • Click on the System drop down menu
  • Select Import/Export
  • From the Operation: drop down menu select Manage Global IDs, you will see the Google Analytics Data Source you created.
  • Click on API Accounts
  • Type in QUICKBOOKS next to your QuickBooksOnline API you created in a previous step.
  • Use the drop down menu that says Manage Global IDs to select Import Charts and Dashboards
  • Click the Choose File button
  • Go to where the QuickBooks Online Pack is located on your computer and Select the etl_archive zip file.
  • Click Open
  • Click Submit
  • Check to make sure all items show a Green Check Mark GreenCheck.JPG next to them
  • Click Import 
  • You will then see a message appear that your import was successful!
  • Congratulations!  You've successfully Installed the QuickBooks Online Pack!

Please Note: If your dashboards and charts do NOT show data, you may need to run your ETL jobs. 

Pre-built ETL Jobs in Data Hub

In addition to the 8 pre-built dashboards we also provide 4 pre-built ETL jobs that can be loaded into your dashboard or operational intelligence instance. Below is a list of available ETL Jobs and the data found within each.

QBO_ETL.png

1.  QBO_Expenses

This ETL job extracts and merges various expense objects from the QuickBooks Online API. Entities include Bill, Purchases, and Vendor Credit, all with Account-Based Expense Line details.

Data is loaded into the following 7 tables: QBO_Bill, QBO_Bill_Lines, QBO_Purchase, QBO_Purchase_Lines, QBO_VendorCredit, QBO_VendorCredit_Lines, and QBO_AllExpenses. The ETL job is scheduled to run daily at 1:04 am and all loads will Delete and Replace Existing Rows.

2.  QBO_Income

This ETL job extracts and merges various income objects from the QuickBooks Online API. Entities include  Sales Receipts, Invoice, Account, and Items, with Sales Receipts and Invoice including Sales Item Line details.

Data is loaded into the following 7 tables: QBO_SalesReceipt, QBO_SalesReceipt_Lines, QBO_Invoice, QBO_Invoice_Lines, QBO_Account, QBO_Items, and QBO_AllSales. The ETL job is scheduled to run daily at 1:05 am and all loads will Delete and Replace Existing Rows. 

3.  QBO_Reports

This ETL job extracts and merges various reports from the QuickBooks Online API. Reports include AP Aging Summary (today), AR Aging Summary (today), and Customer Income (this fiscal year to date). Additionally, the Balance Sheet and Profit and Loss Detail reports are pulled and merged for the fiscal year and fiscal year to date to allow for flexibility in reporting. All reports are loaded into the following 5 tables: QBO_AP_Aging, QBO_AR_Aging, QBO_CustomerIncome_Report, QBO_BalanceSheet_Report, and QBO_ProfitLoss_Report. The ETL job is scheduled to run daily at 1:06 am and all loads will Delete and Replace Existing Rows. 

4.  QBO_Misc

This ETL job extracts and merges various objects from the QuickBooks Online API. Entities include CompanyName, Budget, Budget > Budget_Detail, Account, and Customers. Data is loaded into the following 3 tables: QBO_Company_Name, QBO_Budget_Detail, and QBO_Customers. The ETL job is scheduled to run daily at 1:07 am and all loads will Delete and Replace Existing Rows.

The QuickBooks Online Pack is delivered as-is. There will be no modification of the dashboards by the Support Services Team. TruOI Support Services has put a lot of effort into making sure that the dashboards contain as much information as can be provided. By downloading and installing the QuickBooks Online Pack the dashboard Administrator assumes responsibility for its usage and for understanding the data presented.

NO FURTHER DEVELOPMENT of the QuickBooks Online Pack WILL BE MADE by TruOI Support Services.

What Dashboards and Key Performance Indicators (KPIs) are Used in this Pack?

In SQL Server: Setting SQL Permissions – Running the SQL Script

In iDashboards/TruOI: Adding Your Data Source – Setting a Global ID for Your Data Source

In iDashboards/TruOI:  Importing Charts and Dashboards – Importing Data Sets and ETL Jobs

Comments

0 comments

Please sign in to leave a comment.