The Sisense Quickbooks Desktop connector is a standalone connector that allows you to import data from Quickbooks Desktop’s API into the ElastiCube Manager. After you have downloaded and installed the connector, you can connect through a connection string you provide Sisense in the ElastiCube Manager. The connection string is used to authenticate users who connect to the Quickbooks Desktop APIs. To obtain a connection string, you will need to create a Quickbooks Desktop app.

Once you have connected to Quickbooks Desktop, you can import a variety of tables from the Quickbooks Desktop API.

This page describes how to install the Quickbooks Desktop connector, how to connect to Quickbooks Desktop with a connection string, and what tables you can import into the ElastiCube Manager:

Installing the Quickbooks Desktop Connector

Sisense provides the Quickbooks Desktop connector as a standalone connector that you can download and add to your list of default Sisense connectors.

To install the Quickbooks Desktop connector:

  1. Download the Quickbooks Desktop installation file.
  2. Open the installation file and click Install.
  3. After the installation process is complete, click Close.

The Quickbooks Desktop connector is displayed in the ElastiCube Manager under Add Data > Web Services.

Connecting to the Quickbooks Desktop REST API

The Quickbooks Desktop connector makes requests to QuickBooks through the Remote Connector. The Remote Connector is an easy-to-use tool that enables developers to access QuickBooks data remotely.The Remote Connector runs on the same machine as QuickBooks and accepts connections through a lightweight, embedded Web server.

The server supports SSL/TLS, enabling users to connect securely from remote machines. The first time you connect, you will need to authorize the driver with QuickBooks.

The Remote Connector can be used to read and write to QuickBooks in situations where direct COM access to QuickBooks is not available (e.g., ASP.NET, Java, or QuickBooks on a remote machine).

In the Remote Connector, you can define users and their passwords and then use these credentials in the connection string that you create to connect to your QuickBooks Desktop tables.

Follow the procedure below to connect to QuickBooks for the first time through the Remote Connector:

  1. Download the Remote Connector from remoteconnector.com and install the Remote Connector on the machine where QuickBooks is installed.
  2. Open the company file you want to connect to in QuickBooks using an administrator account in single-user mode.
  3. Open the Remote Connector from the system tray and add a user on the Users tab. Enter a User and Password and select the level of access in the Data Access menu.

    Note: The Remote Connector does not use the User and Password properties to access QuickBooks; the User and Password properties authenticate the user to the Remote Connector. Authentication to QuickBooks is handled based on the Application Name property.
  4. When you first connect, a dialog will appear in QuickBooks prompting you to authorize the application. After authorizing the application, you can then execute commands to QuickBooks. Specify the URL of the Remote Connector and the User and Password. By default, the Remote Connector connects to the currently open company file.
  5. If you want to access QuickBooks when QuickBooks is not running, save the company file information for the user. The Remote Connector will then automatically open QuickBooks in the background with the company file for that user.

Note: If the QuickBooks UI is open, you can only connect to that company file. Additionally, note that the user permissions you run the Remote Connector under must match the user permissions you run QuickBooks under. The Remote Connector installation process installs the Remote Connector as a service under the current user account.

Adding Quickbooks Desktop Tables to your ElastiCube

Sisense uses connection strings to connect to Quickbooks Desktop and import data into the ElastiCube Manager.

The connection string to connect to Quickbooks Desktop has the following structure:

jdbc:Quickbooks Desktop:Property1=Value1;Property2=Value2;

The following is an example of a Quickbooks Desktop connection string:

jdbc:quickbooks:User=xxxxx;Password=xxxxxxxxxx;URL=http://xxxxxxxxxxxx;

  • User: Set this to username defined in Remote Connector.
  • Password:  Set this to password defined in Remote Connector.
  • URL: The IP address of the remote machine where Remote Connector wa installed and the port. The port is defined under Connection Settings in the Advanced tab of the Remote Connector.
  • OAuthSettingsLocation: The location of the settings file where OAuth values are saved for each user when InitiateOAuth is set to GETANDREFRESH. See Switching between Accounts for more information.

Note: To switch between accounts, you need to delete the file OAuthsettings.txt file located.

at …\Users\xxx\AppData\Roaming\CData\QuickbooksDesktop\DataProvider.

To add Quickbooks Desktop data:

  1. In ElastiCube Manager, click Add Data and then, Quickbooks Desktop. The Connect to Quickbooks Desktop window is displayed.
  2. In Datasource Connection String, enter your connection string.
  3. Click Connect to Server. Quickbooks Desktop is displayed in the Select Database list.
  4. Click OK. Sisense connects to Quickbooks Desktop and displays a list of tables available for you to import.
  5. Select the relevant tables and click Add.
  6. The tables are displayed in the ElastiCube Manager.

Switching between Accounts

When you connect to the Quickbooks Desktop data source, Sisense saves your OAuth values in the file OAuthsettings.txt file located at …\Users\xxx\AppData\Roaming\CData\Quickbooks Desktop Data Provider on your Sisense server. To connect to the Quickbooks Desktop 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 Desktop data source and for each user, Sisense generates a file that contains that user’s OAuth values in the location defined in the string.

jdbc:QuickbooksDesktop:OAuthSettingsLocation=C:\QuickbooksDesktop\auth\john.txt;OAuthClientId=11276856774486;

OAuthClientSecret=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

jdbc:QuickbooksDesktop:OAuthSettingsLocation=C:\QuickbooksDesktop\auth\sally.txt;OAuthClientId=11276856774486;

OAuthClientSecret=064c70d78567jm2b7e7e4224fad;InitiateOAuth=GETANDREFRESH;Version=2.8;CallbackURL=http://localhost/;

In the example above, to OAuth files are created, one for John and one for Sally in the location C:\QuickbooksDesktop\auth\.

This is useful if you support many users who each need to access the Quickbooks Desktop data source.

Quickbooks Desktop Tables

Quickbooks Desktop’s RESTful APIs expose the following Quickbooks Desktop tables that you can import into the ElastiCube Manager through the Sisense Quickbooks Desktop connector:

Available Tables

NameDescription
AccountsCreate, update, delete, and query QuickBooks Accounts. To update Accounts, set the QBXMLVersion to 6.0 or higher.
BillExpenseItemsCreate, update, delete, and query QuickBooks Bill Expense Line Items.
BillLineItemsCreate, update, delete, and query QuickBooks Bill Line Items.
BillPaymentChecksCreate, update, delete, and query QuickBooks Bill Payment Checks. QBXMLVersion must be set to 6.0 or higher to update a BillPaymentCheck.
BillPaymentChecksAppliedToCreate, update, delete, and query QuickBooks Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.
BillPaymentCreditCardsCreate, update, delete, and query QuickBooks Bill Payments.
BillPaymentCreditCardsAppliedToCreate, update, delete, and query QuickBooks Bill Payment AppliedTo aggregates. In a Bill Payment, each AppliedTo aggregate represents the Bill transaction to which this part of the payment is being applied.
BillsCreate, update, delete, and query QuickBooks Bills.
BuildAssembliesInsert, Update, delete, and query QuickBooks Build Assembly transactions.
BuildAssemblyLineItemsCreate and query QuickBooks Build Assembly transactions.
CheckExpenseItemsCreate, update, delete, and query QuickBooks Check Expense Line Items.
ChecksCreate, update, delete, and query QuickBooks Check Line Items.
ClassCreate, update, delete, and query QuickBooks Classes. QuickBooks requires QBXML version 8.0 or higher for updates to a Class.
CreditCardChargeExpenseItemsCreate, update, delete, and query QuickBooks Credit Card Charge Expense Line Items.
CreditCardChargeLineItemsCreate, update, delete, and query QuickBooks Credit Card Charge Line Items.
CreditCardChargesCreate, update, delete, and query QuickBooks Credit Card Charges.
CreditCardCreditExpenseItemsCreate, update, delete, and query QuickBooks Credit Card Credit Expense Line Items.
CreditCardCreditLineItemsCreate, update, delete, and query QuickBooks Credit Card Credit Line Items.
CreditCardCreditsCreate, update, delete, and query QuickBooks Credit Card Credits.
CreditMemoLineItemsCreate, update, delete, and query QuickBooks Credit Memo Line Items.
CreditMemosCreate, update, delete, and query QuickBooks Credit Memos.
CurrencyCreate, update, delete, and query QuickBooks Currencies. This table requires QBXML version 8.0 or higher, and you will need to enable multiple Currencies in your QuickBooks company file to use it.
CustomerContactsCreate, update, delete, and query QuickBooks Customer Contacts. This table requires QBXML version 12.0 or higher, and is only available in QuickBooks editions 2016 and above.
CustomerMessagesCreate, delete, and query Customer Messages.
CustomerNotesCreate, update, and query QuickBooks Customer Notes. This table requires QBXML version 12.0 or higher.
CustomersCreate, update, delete, and query QuickBooks Customers.
CustomerShippingAddressesCreate, update, delete, and query QuickBooks Customer Shipping Addresses. Multiple Customer Shipping Addresses are supported in only QuickBooks 2013 and higher. Additionally, this table requires QBXML version 12.0 or higher.
CustomerTypesCreate, update, delete, and query QuickBooks Customer Types.
DateDrivenTermsCreate, delete, and query QuickBooks Date Driven Terms.
DepositLineItemsCreate, update, delete, and query QuickBooks Deposit Line Items. QBXMLVersion must be set to 7.0 or higher to update a deposit.
DepositsCreate, update, delete, and query QuickBooks Deposits. QBXMLVersion must be set to 7.0 or higher to update a deposit.
EmployeeEarningsCreate, update, delete, and query QuickBooks Employee Earnings.
EmployeesCreate, update, delete, and query QuickBooks Employees.
EstimateLineItemsCreate, update, delete, and query QuickBooks Estimate Line Items.
EstimatesCreate, update, delete, and query QuickBooks Estimates.
InventoryAdjustmentLineItemsCreate and query QuickBooks Inventory Adjustment Line Items.
InventoryAdjustmentsCreate, query, and delete QuickBooks Inventory Adjustments.
InventorySitesCreate, update, delete, and query QuickBooks Inventory Sites. Inventory Sites are only available in QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on.
InvoiceLineItemsCreate, update, delete, and query QuickBooks Invoice Line Items.
InvoicesCreate, update, delete, and query QuickBooks Invoices.
ItemLineItemsCreate, update, delete, and query QuickBooks Item Line Items.
ItemReceiptExpenseItemsCreate, update, delete, and query QuickBooks Item Receipt Expense Line Items.
ItemReceiptLineItemsCreate, update, delete, and query QuickBooks Item Receipt Line Items.
ItemReceiptsCreate, update, delete, and query QuickBooks Item Receipts.
ItemsCreate, update, delete, and query QuickBooks Items.
JobTypesCreate and query QuickBooks JobTypes.
JournalEntriesCreate, update, delete, and query QuickBooks Journal Entries. Note that while Journal Entry Lines can be created with a new Journal Entry, they cannot be added or removed from an existing Journal Entry.
JournalEntryLinesCreate, update, delete, and query QuickBooks Journal Entries. Note that while Journal Entry Lines can be created with a new Journal Entry, they cannot be added or removed from an existing Journal Entry.
OtherNamesCreate, update, delete, and query QuickBooks Other Name entities.
PaymentMethodsCreate, update, delete, and query QuickBooks Payment Methods.
PayrollNonWageItemsQuery QuickBooks Non-Wage Payroll Items.
PayrollWageItemsCreate and query QuickBooks Wage Payroll Items.
PriceLevelPerItemCreate and query QuickBooks Price Levels Per Item. Only QuickBooks Premier and Enterprise support Per-Item Price Levels. Note that while Price Levels can be added from this table, you may only add Per-Item Price Levels from this table. Price Levels may be deleted from the PriceLevels table. This table requires QBXML version 4.0 or later.
PriceLevelsCreate, delete, and query QuickBooks Price Levels. Note that while Price Levels can be added and deleted from this table, you may add only fixed-percentage Price Levels from this table. Per-Item Price Levels may be added via the PriceLevelPerItem table. This table requires QBXML version 4.0 or higher.
PurchaseOrderLineItemsCreate, update, delete, and query QuickBooks Purchase Order Line Items.
PurchaseOrdersCreate, update, delete, and query QuickBooks Purchase Orders.
ReceivePaymentsCreate, update, delete, and query QuickBooks Receive Payment transactions. QBXMLVersion must be set to 6.0 or higher to update a ReceivePayment.
ReceivePaymentsAppliedToCreate, update, and query QuickBooks Receive Payment AppliedTo aggregates. In a Receive Payment, each AppliedTo aggregate represents the transaction to which this part of the payment is being applied. QBXMLVersion must be set to 6.0 or higher to update a ReceivePayment.
SalesOrderLineItemsCreate, update, delete, and query QuickBooks Sales Order Line Items.
SalesOrdersCreate, update, delete, and query QuickBooks Sales Orders.
SalesReceiptLineItemsCreate, update, delete, and query QuickBooks Sales Receipt Line Items.
SalesReceiptsCreate, update, delete, and query QuickBooks Sales Receipts.
SalesRepsCreate, update, delete, and query QuickBooks Sales Rep entities.
SalesTaxCodesCreate, update, delete, and query QuickBooks Sales Tax Codes.
SalesTaxItemsCreate, update, delete, and query QuickBooks Sales Tax Items.
ShippingMethodsCreate, update, delete, and query QuickBooks Shipping Methods.
StandardTermsCreate, update, delete, and query QuickBooks Standard Terms.
StatementChargesCreate, update, delete, and query QuickBooks Statement Charges.
TimeTrackingCreate, update, delete, and query QuickBooks Time Tracking events.
ToDoCreate, update, delete, and query QuickBooks To Do entries.
TransferInventoryQuery and delete QuickBooks Transfer Inventory transactions. Transfer Inventory is available in only QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on.
TransferInventoryLineItemsCreate and query QuickBooks Transfer Inventory Line Items. Transfer Inventory is available in only QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on.
TransfersCreate, update, and query QuickBooks transfers. Requires QBXML version 12.0 or higher.
UnitOfMeasureCreate, update, delete, and query QuickBooks units of measure. QuickBooks requires QBXML version 7.0 or higher to use this table.
UnitOfMeasureDefaultUnitsCreate and query QuickBooks unit-of-measure default units. QuickBooks requires QBXML version 7.0 or higher to use this table.
UnitOfMeasureRelatedUnitsCreate and query QuickBooks unit-of-measure related units. QuickBooks requires QBXML version 7.0 or higher to use this table.
VehicleMileageCreate, update, delete, and query QuickBooks Vehicle Mileage entities. QuickBooks requires QBXML version 6.0 or higher to use this table.
VendorCreditExpenseItemsCreate, update, delete, and query QuickBooks Vendor Credit Expense Line Items.
VendorCreditLineItemsCreate, update, delete, and query QuickBooks Vendor Credit Line Items.
VendorCreditsCreate, update, delete, and query QuickBooks Vendor Credits.
VendorsCreate, update, delete, and query QuickBooks Vendors.
VendorTypesCreate, update, delete, and query QuickBooks Vendor Types.
WorkersCompCodeLinesQuery QuickBooks Workers Comp Code entries by line. Requires QBXML Version 7.0 or higher.
WorkersCompCodesQuery QuickBooks Workers Comp Code entries. Requires QBXML Version 7.0 or higher.

Available Views

NameDescription
BillLinkedTransactionsQuery QuickBooks Bill Linked Transactions.
CompanyInfoQuery the company information from QuickBooks.
CreditMemoLinkedTransactionsQuery QuickBooks Credit Memo Linked Transactions.
DeletedEntitiesQuery deleted Entities.
DeletedTransactionsQuery deleted Transactions.
EstimateLinkedTransactionsQuery QuickBooks Estimate Linked transactions.
HostQuery the QuickBooks host process. The Host represents information about the QuickBooks process currently being executed.
InvoiceLinkedTransactionsQuery QuickBooks Invoice Linked Transactions.
ItemReceiptLinkedTransactionsQuery QuickBooks Item Receipt Linked Transactions.
ItemSitesCreate, update, delete, and query QuickBooks Item Sites. Item sites are only available in QuickBooks Enterprise 2010 and above, and only with the Advanced Inventory add-on. This table requires a minimum of QBXML version 10.0.
PreferencesQuery information about many of the preferences the QuickBooks user has set in the company file.
PurchaseOrderLinkedTransactionsQuery QuickBooks Purchase Order Linked Transactions.
SalesOrderLinkedTransactionsQuery QuickBooks Sales Order Linked Transactions.
StatementChargeLinkedTransactionsQuery QuickBooks Statement Charge Linked Transactions.
TemplatesQuery QuickBooks templates.
TransactionsQuery QuickBooks transactions. You may search the Transactions using a number of values including Type, Entity, Account, ReferenceNumber, Item, Class, Date, and TimeModified.
VendorCreditLinkedTransactionsQuery QuickBooks Vendor Credit Linked Transactions.