How to install and configure the Xero Connector.

Xero Connector data flow

Download the Xero Connector

The xero connector objects can be downloaded at: Xero Connector - TM1 Processes

SQLite Database

Create a new SQLite Database for Xero Connector

Relative to the Apliqo FPM database create the folder ../[FPM]/ods/xero. The xero folder is where the SQLite database and the xero connector software is located.

Using a program like DB Browser, create a new SQLite database called ods_xero.sqlite. In the SQlite database create following tables and views using the follow script. Hint, copy all the sql statements to the

Create ODBC connection to SQLite database

To connect the ods_xero database to Apliqo FPM, create an ODBC connection.

Data Source Name: xero_connector.

Database Name: The full file name of the ods_xero.sqlite database.

TM1 Objects.

Copy the TM1 objects that are in the TM1 Objects folder to the Data folder of Apliqo FPM database. Restart the Apliqo FPM service to read in the new TM1 objects. When the server restarts the }APQ C3 CNT Xero cube should appear.

Xero Connector

Update the tm1-config.ini

Copy the tm1-config.ini to the ../[FPM]/ods/xero folder create in step 1. This must be the same folder as the xeroconnector.exe located. In the tm1-config.ini update:

address - The machine name on the network that hosts the TM1 database. If the xeroconnector.exe and the TM1 database are on the same machine, localhost can be used.
port - The HTTPPortNumber parameter in the tm1s.cfg file for Apliqo FPM.
user - The user the Xero Connector is to use when connecting to the Apliqo FPM TM1 database. This user must have Admin privileges in Apliqo FPM.
password - The TM1 password for the user.
ssl - True/False. The value of the UseSSL parameter in the tm1s.cfg file for Apliqo FPM.
decode_b64 - True/False. To encrypt the user password in the file.

Add the TM1 Connection

In a command prompt navigate to the folder where the xeroconnector.exe is located. Next type the following command providing the --client_id,--client_secret and --description.

xeroconnector.exe --action="tm1_connection_add" --client_id="....1D8" --client_secret="....Mzd" --description="Xeno Group"

A new component will be added to the Connections consolidation in the }APQ C3 CNT Xero Tenant dimension. The principal name of the new element is the Connection ID used in subsequent commands.

Add the location to the SQLite database

In the }APQ C3 CNT Xero cube enter for the new connection:

redirect URL - This is always https://xero.com/.
sqlite_location - This is the full file name for the ods_xero.sqlite file.

Connect to Xero

In a command prompt navigate to the folder where the xeroconnector.exe is located. Next type the following command providing the --connection which is the Connection ID established above.

xeroconnector.exe --action="xero_connect" --connection="....E1D8"

This will open the Xero log in page. Provide your credencials and log in.

After logging into Xero, copy the concentid from the URL back in the command prompt window.

Set up the ODS

In a command prompt navigate to the folder where the xeroconnector.exe is located. Next type the following command providing the --connection which is the Connection ID established above.

xeroconnector.exe --action="ods_setup" --connection="....E1D8"

Import the organisations

In a command prompt navigate to the folder where the xeroconnector.exe is located. Next type the following command providing the --connection which is the Connection ID established above.

xeroconnector.exe --action="xero_import_organisations" --connection="....E1D8"

This will add the organisations element to the connection in the }APQ C3 CNT Xero Tenant dimension. Set the attribute “Active” to 1 for the organisations to be downloaded

Import the Tracking Categories

In a command prompt navigate to the folder where the xeroconnector.exe is located. Next type the following command providing the --connection which is the Connection ID established above.

xeroconnector.exe --action="ods_import_trackingcategories" --connection="....E1D8"

Import the Accounts

In a command prompt navigate to the folder where the xeroconnector.exe is located. Next type the following command providing the --connection which is the Connection ID established above.

xeroconnector.exe --action="ods_import_accounts" --connection="....E1D8"

Import the Journals

In a command prompt navigate to the folder where the xeroconnector.exe is located. Next type the following command providing the:

--connection which is the Connection ID established above.

--modified_after which the eariliest journal loaded into the operational data store. Entered in YYYY-MM-DD format.

--offset is the number of records skipped before being evaluated by the connector. The Xero limits the number of records downloaded via the api. The journals are incremental added to the ODS. Setting an offset skips ahead to load the latest records.

xeroconnector.exe --action="ods_import_journals" --connection="....E1D8" --modified_after="2023-01-01" --offset="100000"

Import the Trial Balance

In a command prompt navigate to the folder where the xeroconnector.exe is located. Next type the following command providing the:

--connection which is the Connection ID established above.

--date is the date the Trial Balance is taken.

xeroconnector.exe --action="ods_import_report_tb" --connection="....E1D8" --date="2023-01-01"

One Import to rule them all

In a command prompt navigate to the folder where the xeroconnector.exe is located. Next type the following command providing the:

--connection which is the Connection ID established above.

--modified_after which the eariliest journal loaded into the operational data store. Entered in YYYY-MM-DD format.

--date is the date the Trial Balance is taken.

xeroconnector.exe --action="ods_import_all" --connection="....E1D8" --modified_after="2021-03-21" --date="2021-02-28"