Connecting to Xero Accounting

The Sisense Xero connector is a standalone connector that allows you to import data from Xero’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 Xero APIs. To obtain a connection string, you will need to create a Xero app.

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

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

Installing the Xero Connector

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

To install the Xero connector:

  1. Download the Xero installation file.
  2. Open the installation file and click Install.
  3. After the installation process is complete, click Close.
    The Xero connector is displayed in the ElastiCube Manager under Add Data > Web Services.

Connecting to the Xero REST API

To access Xero’s REST API from the ElastiCube Manager, you must provide valid Oauth Xero credentials through a connection string. These credentials are provided by Xero when you register an application.

After you receive your credentials from Xero, you can create the connection string and provide Sisense with it to connect to your data.

Registering an App

Follow the steps below to obtain the OAuth client credentials, the OAuthClientId and OAuthClientSecret:

  1. Log in to the Xero Developer Portal.
  2. Click My Applications > Add Application.
  3. Select the Public option.
  4. Enter a name for your application and the URL of your company. This information is displayed to users when they connect.
  5. If you are making a Desktop application, set the Callback Domain to ‘localhost’.
    If you are making a Web application, set the Callback Domain to the domain name of the URL where the user returns with the token that verifies that they have granted your app access.
    After you click Save, you are shown your OAuth credentials, the consumer key, and consumer secret.

Adding Xero Tables to your ElastiCube

Sisense uses connection strings to connect to Xero and import data into the ElastiCube Manager. Each connection string contains a authentication parameters that the data source uses to verify your identity and what information you can export to Sisense.

Each connection string contains a authentication parameters that the data source uses to verify your identity and what information you can export to Sisense.

The connection string to Xero is in the following format:

jdbc:xero:OAuthclientId=xxxxxxxxxxxxxxxxxxxxxx;

For example:

jdbc:xero:OAuthclientId=xxxxxxxxxxxxx;OAuthClientSecret=xxxxxxxxxxxxx;

InitiateOAuth=GETANDREFRESH;XeroAppAuthentication=PUBLIC;

Your Xero connection string should include the following parameters, separated by a semicolon

To add Xero data:

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

Xero Tables

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

Available Tables

Name Description
Accounts Create, delete, and query accounts for a Xero organisation.
BankTransactions Create, update, delete, and query bank transactions for a Xero organisation.
BankTransfers Usage information for the operation BankTransfers.rsd.
ContactGroups Create, update, and query contact groups for a Xero organisation.
Contacts Create, update, and query contacts for a Xero organisation.
CreditNotes Create, update, delete, and query credit notes for a Xero organisation.
Employees Create, update, and query employees for a Xero organisation.
ExpenseClaims Create, update, and query expense claims for a Xero organisation.
Invoices Create, update, delete, and query invoices for a Xero organisation.
Items Create, update, delete, and query items for a Xero organisation.
LinkedTransactions Create, update, and query LinkedTransactions for a Xero organisation.
ManualJournals Create, update, and query manual journals for a Xero organisation.
Payments Create and query payments for a Xero organisation.
PurchaseOrders Create, update, delete, and query purchase orders for a Xero organisation.
Receipts Create, update, delete, and query receipts for a Xero organisation.
TaxRates Usage information for the operation TaxRates.rsd.
Payroll US Data Model  
Employees Create, update, and query employees for a Xero organisation.
PayItemsBenefits Create, update, and query PayItems for a Xero organisation.
PayItemsDeductions Create, update, and query PayItems for a Xero organisation.
PayItemsEarnings Create, update, and query PayItems for a Xero organisation.
PayItemsReimbursements Create, update, and query PayItems for a Xero organisation.
PayItemsTimeOff Create, update, and query PayItems for a Xero organisation.
PayRuns Create, update, and query payruns for a Xero organisation.
PaySchedules Create, update, and query PaySchedules for a Xero organisation.
Paystubs Create, update, and query pay stubs for a Xero organisation.
WorkLocations Create, update, and query work locations for a Xero organisation.
Payroll AUS Data Model  
Employees Create, update, and query employees for a Xero organisation.
LeaveApplications Create, update, and query Leave Applications for a Xero organisation.
PayItemsDeductions Create, update, and query PayItems for a Xero organisation.
PayItemsEarnings Create, update, and query PayItems for a Xero organisation.
PayItemsLeave Create, update, and query PayItems for a Xero organisation.
PayItemsReimbursements Create, update, and query PayItems for a Xero organisation.
PayrollCalendars Create, update, and query Payroll Calendars for a Xero organisation.
PayRuns Create, update, and query payruns for a Xero organisation.
SuperFunds Retrieve, add and update Payroll Super Funds in a Xero organisation.

Available Views

Name Description
Accounting Data Model  
AgedPayablesByContact Query organisation data for a Xero organisation.
AgedReceivablesByContact Query organisation data for a Xero organisation.
BalanceSheet Query organisation data for a Xero organisation.
BankSummary Query organisation data for a Xero organisation.
BrandingThemes Query branding themes for a Xero organisation.
BudgetSummary Query organisation data for a Xero organisation.
Currencies Query currencies for a Xero organisation.
Journals Query the line items in journals for a Xero organisation.
Organisation Query organisation data for a Xero organisation.
Overpayments Create and query Overpayments for a Xero organisation.
Prepayments Create and query Prepayments for a Xero organisation.
ProfitAndLoss Query organisation data for a Xero organisation.
RepeatingInvoices Usage information for the operation RepeatingInvoices.rsd.
TrackingCategories Query tracking categories for a Xero organisation.
TrialBalance Query organisation data for a Xero organisation.
Users Query users for a Xero organisation.
Payroll US Data Model  
TimeOffBalances Retrieve, add and update an Employee's TimeOff balance in a Xero organisation.
Timesheets Create, update, and query timesheets for a Xero organisation.
Payroll AUS Data Model  
LeaveBalances Retrieve, add and update an Employee's Leave balance in a Xero organisation.

Limitations

To access the AgedPayablesByContact and AgedRecievablesByContact tables, you need to specify a particular ContactID, the unique ID of the Contacts table, in the select query using a WHERE clause. This allows you to retrieve data for a specific contact. These are limitations of the Xero API, as these reports are specific to each contact. Here’s an example:

SELECT due, paid, total FROM AgedPayablesByContact WHERE ContactID='xyz'

SELECT duedate, reference FROM AgedRecievablesByContact WHERE ContactID='1234'

Due this limitation, you cannot build an ElastiCube with these tables without custom SQL.

Daily Limit

There is a daily limit of 1000 API calls against a single Xero organisation in a rolling 24-hour period.

In addition to the daily limit, a single access token can only be used up to 60 times in a rolling 60-second period.

Encountering a Rate Limit

If you encounter a rate limit, the Xero API will return an HTTP 503 (Service Unavailable) error, with the following message: “oauth_problem=rate limit exceeded”.

Note: If you encounter a rate limit, do not continue to make requests, as this may continue to add to your limitation. If necessary, you may need to queue requests.

Token Limitations

Each access token will only last for 30 minutes. If you want longer access to the organization, you need the user to re-authorize your application.