The Sisense QuickBooks Online connector is a standalone connector that allows you to import data from the QuickBooks Online API into the ElastiCube Manager. After you have downloaded and installed the connector, you can connect to Quickbooks Online API through a connection string you provide Sisense in the ElastiCube Manager. The connection string is used to authenticate users who connect to the QuickBooks Online API. To obtain a connection string, you will need to create a QuickBooks Online app.
Once you have connected to QuickBooks Online, you can import a variety of tables from the QuickBooks Online API.
This page describes how to install the QuickBooks Online connector, how to connect to QuickBooks Online with a connection string, and what tables you can import into the ElastiCube Manager:
- Installing the QuickBooks Online Connector
- Connecting to the QuickBooks Online REST API
- Adding QuickBooks Online Tables to your ElastiCube
- QuickBooks Online Tables
Sisense provides the Quickbooks Online connector as a standalone connector that you can download and add to your list of default Sisense connectors.
To install the QuickBooks Online connector:
- Download the Quickbooks Online installation file.
- Open the installation file and click Install.
- After the installation process is complete, click Close.
The Quickbooks Online connector is displayed in the ElastiCube Manager under Add Data > Web Services.
To access Quickbooks Online’s REST API from Sisense, you must provide valid Oauth Quickbooks Online credentials. These credentials are provided by Quickbooks Online when you create an application.
Creating an App
You can follow the steps below to create an app and obtain the OAuth client credentials, the consumer key and consumer secret. You will need to create an Intuit developer account if you do not already have one.
- Log in to http://developer.intuit.com.
- Click My Apps > Create New App > Select APIs. Select the Accounting API.
- On the Settings tab, define the Launch URL.If you are making a desktop application, set Launch URL to http://localhost/.For a Web application, set Launch URL to a page you would like the user to be returned to after they have granted your application permissions.
Once you have created the app, you will be redirected to a page with information about your app. The Development tab contains your Sandbox credentials and the Production tab contains credentials for use with a production account. The consumer key and consumer secret are displayed on the Keys tab.
Authenticating through Quickbooks Online
After setting the following connection properties, you are ready to connect:
- OAuthClientId: Set this to the consumer key in your app settings.
- OAuthClientSecret: Set this to the consumer secret in your app settings.
- CompanyId: Set this to the ID of the company you want to connect to.
- CallbackURL: Set this to http://localhost.
- InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken and OAuthAccessTokenSecret.
When you connect the driver opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The driver then completes the OAuth process:
- Extracts the access token from the callback URL and authenticates requests.
- Refreshes the access token when it expires.
- Saves OAuth values in OAuthSettingsLocation to be persisted across connections.
Sisense uses connection strings to connect to Quickbooks Online and import data into the ElastiCube Manager.
The connection string to connect to Quickbooks Online has the following structure:
An example of a connection string:
Note: To switch between accounts, you need to delete the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\Quickbooks Online Data Provider.
To add Quickbooks Online data:
- In ElastiCube Manager, click Add Data and then, Quickbooks Online. The Connect to Quickbooks Online window is displayed.
- In Datasource Connection String, enter your connection string.
- Click Connect to Server. Quickbooks Online is displayed in the Select Database list.
- Click OK. Sisense connects to Quickbooks Online and displays a list of tables available for you to import.
- Select the relevant tables and click Add.
The tables are displayed in the ElastiCube Manager.
Switching between Accounts
When you connect to the QuickBooks Online data source, Sisense saves your OAuth values in the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\QuickBooks Online Data Provider on your Sisense server. To connect to the QuickBooks Online data source with another user on the same machine, you must delete the OAuthsettings.txt file. Sisense will then generate a new file for that user.
Another option to support multiple users is to define the location and file name of an OAuthsettings file for each unique user in your connection string through the OAuthSettingsLocation parameter. When each user connects to the data source, Sisense generates the OAuth file with the file name you specify in the location you define. In the examples below, two users are allowed to access the QuickBooks Online data source and for each user, Sisense generates a file that contains that user’s OAuth values in the location defined in the string.
In the example above, to OAuth files are created, one for John and one for Sally in the location C:\QuickBooks Online\auth\.
This is useful if you support many users who each need to access the QuickBooks Online data source.
The Sisense Quickbooks Online connector allows you to import the following tables into the ElastiCube Manager.
|Accounts||Query QuickBooks Accounts.|
|BillLineItems||Query QuickBooks Bill Line Items.|
|BillPaymentLineItems||Query QuickBooks Bill Payment Line Items.|
|BillPayments||Query QuickBooks Bill Payments.|
|Bills||Query QuickBooks Bills.|
|BudgetDetails||Query QuickBooks BudgetDetails.|
|Budgets||Query QuickBooks Budgets.|
|Class||Query QuickBooks Classes.|
|CompanyInfo||Retrieve information about the QuickBooks company.|
|CreditMemoLineItems||Query QuickBooks Credit Memo Line Items.|
|CreditMemos||Query QuickBooks Credit Memos.|
|Customers||Query QuickBooks Customers.|
|Departments||Query QuickBooks Departments.|
|DepositLineItems||Query QuickBooks Deposit Line Items.|
|Deposits||Query QuickBooks Deposits.|
|Employees||Query QuickBooks Employees.|
|Entitlements||Retrieves QuickBooks Entitlements.|
|EstimateLineItems||Query QuickBooks Estimate Line Items.|
|EstimateLinkedTransactions||Query QuickBooks Estimate Line Items.|
|Estimates||Query QuickBooks Estimates.|
|InvoiceLineItems||Query QuickBooks Invoice Line Items.|
|InvoiceLinkedTransactions||Query QuickBooks Invoice Line Items.|
|Invoices||Query QuickBooks Invoices.|
|Items||Query QuickBooks Items.|
|JournalEntries||Query QuickBooks Journal Entries.|
|JournalEntryLineItems||Query QuickBooks Journal Entry Line Items.|
|PaymentLineItems||Query QuickBooks Payment Line Items.|
|PaymentMethods||Query QuickBooks Payment Methods.|
|Payments||Query QuickBooks Payments.|
|Preferences||Query QuickBooks Preferences. The Preferences table contains settings for company-wide preferences, which affect all users.|
|PurchaseLineItems||Query QuickBooks Purchase Line Items.|
|PurchaseOrderLineItems||Query QuickBooks Purchase Order Line Items.|
|PurchaseOrders||Query QuickBooks Purchase Orders.|
|Purchases||Query QuickBooks Purchases.|
|RefundReceiptLineItems||Query QuickBooks RefundReceiptLineItems.|
|RefundReceipts||Query QuickBooks RefundReceipts.|
|SalesReceiptLineItems||Query QuickBooks Sales Receipt Line Items.|
|SalesReceipts||Query QuickBooks Sales Receipts.|
|TaxCodes||Query QuickBooks Sales Tax Codes.|
|TaxRates||Query QuickBooks Tax Rates.|
|Terms||Query QuickBooks Terms.|
|TimeActivities||Query QuickBooks Time Activities.|
|Transfers||Query QuickBooks Transfers|
|VendorCredits||Query QuickBooks Vendor Credits.|
|Vendors||Query QuickBooks Vendors.|
Hey! Was this article helpful?
Questions? Ask the community.