Connecting to Xero Accounting
The SisenseXero connector is a certified connector that allows you to import data from the Xero API into Sisense via the Sisense generic JDBC connector. The Xero connector offers the most natural way to connect to Xero data to search (Customers, Transactions, Invoices, Sales Receipts, etc.), update items, edit customers, and more, and provides additional powerful features.
The Sisense Xero is a certified connector. The support for the connector is provided by Sisense and will be assisted by the certification partner's support, if needed. For any support issues or additional functionality requests, please contact your Sisense representative or open a request through our Help Center. For advanced inquiries specific to driver functionality, you can also contact our certification partner’s support directly via [email protected].
After you have downloaded and installed the connector, you can connect through a connection string you provide Sisense in Sisense. 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 download the Xero driver and deploy it, how to connect to Xero with a connection string, provides information about the Xero data model, and more.
- Downloading the Xero JDBC Driver
- Deploying the Xero JDBC Driver
- Connecting to Xero
- Adding Xero Tables to your ElastiCube
- Xero Connector: Additional Resources
You can download the Xero JDBC driver here.
- The driver is certified for Sisense v7.2 and above.
- Sisense v7.4 and above: Click the above link to download a ready-to-use driver.
- Sisense prior to v7.4: Click the above link to download a 30-days free-trail of the driver. Contact Sisense for the full license version.
To run the setup, execute the following command: java -jar setup.jar (OR, if your system is set up to run Java applications, double-click on setup.jar).
During the installation, pay attention to the path of the installation (you will need it later on, to direct Sisense to the Jar file. The default path is C:\Program Files\CData\CData JDBC Driver for Xero 2019\lib).
Note: The install file (setup.jar) is a Java application that requires Java 6 (J2SE) or above to run. If you do not have Java 6 installed, you may download it from here.
To access Xero’s REST API from Sisense, 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:
- Log in to the Xero Developer Portal.
- Click My Applications > Add Application.
- Select the Public option.
- Enter a name for your application and the URL of your company. This information is displayed to users when they connect.
- 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.
Accessing Xero Tables via Connection String
Sisense uses connection strings to connect to Xero and import data into 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.
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 following is an example of a Xero connection string:
Your Xero connection string should include the following parameters, separated by a semicolon
- OAuthClientId: Set this to the consumer key in your app settings.
- OAuthClientSecret: Set this to the consumer secret in your app settings.
- XeroAppAuthentication: Set this to the type of your application. Allowed values: PUBLIC or PARTNER. Default: PUBLIC.
- InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
To help you create a connection string and test the connection, see Connection String Builder for Certified Connectors.
If you have any issues connecting to your data source, see Troubleshooting JDBC Data Connectors.
- In the Data page, open an ElastiCubeor create a new ElastiCube.
- In the Model Editor, click . The Add Data dialog box is displayed.
- Click Generic JDBC to open the JDBC settings.
- In Connection String, enter the Xero URL. See Creating a Connection String for more information.
- In JDBC JARs Folder, enter the name of the directory where the Xero JAR file is located (see Deploying the Xero JDBC Driver).
- In Driver's Class Name, enter the following class name: https://www.cdata.com/drivers/xero/jdbc/.
- In User Name and Password, enter your Xero credentials. These fields are not required if the user name and password were provided in the connection string.
- Click Next. A list of tables in the database are displayed. All tables and views associated with the database will appear in a new window.
- From the Tables list, select the relevant table or view you want to work with. You can click next to the relevant table or click Preview to see a preview of the data inside it.
- (Optional) Click + to customize the data you want to import with SQL. See Importing Data with Custom Queries for more information.
- After you have selected all the relevant tables, click Done. The tables are added to your data model.
For the full documentation set for the Xero connector, click here.
For connection string options, click here.
For information on the Xero data model, click here.
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.
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.
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.