Xero.com data consolidation and reporting solution selection

Once upon a time, I was using Xero to record and manage a company’s finances and wanted to use Zebra Bi for Power BI to create interactive income statement actual vs plan vs budget reporting.

Xero Features

Zebra BI offers very compelling visualization tools to create financial reporting in Power BI and Excel specifically for comparing actuals vs plan and budget. Zebra BI’s product is a Power BI and Excel plugin. Using it in Power BI is as simple as choosing Zebra BI as a visualization in same way as any other Power BI chart object.

Zebra BI Example Visualizations

Xero API

The primary challenge to using Power BI with Xero is deciding the best way to get data out of Xero so that it can be used by Power BI.

Xero has an API that provides access to almost all of the Xero data. The API can be used directly or indirectly via many third parties. However, the API features and limitations require careful  consideration to determine if they meet your use case.

The Xero API has some good documentation as well as an interactive API explorer that can be used in a no-code way to learn about the API endpoint features and data returned.

Two methods of using the Xero API:

• • Directly from Xero – The API can be used directly by registering for API access and developing your own code and process.
  • Indirectly via third parties – There are also a variety of third parties include a range of SaaS platforms and tools that connect to the API and retrieve Xero data on your behalf. These provide a wide range of methods and Xero data “modelling” and transformation to enable it to be readily used for financial reporting.

I ended up conducting a selection process to identify the best solution to get Xero data which considered a variety of Xero API and third party limitations.

Requirements Summary

• • Multi-year Xero data retrieval and analysis eg 2019, 2020, 2021.
  • Use Power BI to enable Zebra BI reporting.
  • Data must include actuals, budget and multiple forecasts, and ability to create previous year metrics.
  • Method to allow ad-hoc “account mapping” eg ability to categorize accounts other than Xero tracking categories eg group all travel as “Travel”.
  • Entirely automated to minimize manual effort required. This includes retrieval and transformation of Xero data and Power BI report refresh.

Xero API Limitations

• • API 11 month per call limit – need workarounds to get more.
  • Scheduling – need method to schedule data refresh.
  • Update existing data – either delete all or update existing data.
  • Data modelling – Xero Report API’s provide Xero modelling however additional modelling required to get desired datasets.
  • Budget data – need a method to incorporate Xero budget data. Xero is only recently providing budget data via API.
  • Forecast data – need a method to incorporate forecast data. Xero does not have forecast data functionality. However, a workaround is to create a budget that has forecast data.
  • Account grouping – how to categorize accounts other than Xero tracking categories eg group all travel as “Travel”.
  • Implementation & support complexity – code, technical mix, processes, components, environments.
  • All methods require extraction of Xero data into some intermediary storage location eg a datawarehouse or datamart.

Summary

The graphic below summarizes the selection investigation results and comparision and highlights my top two choices that best satisfied my requirements.

Note this comparison doesn’t include costs. However, generally speaking, these solutions have more or less similar costs.

My requirements were best met using Acterys which provides Xero API data ETL,  an advanced datawarehouse, and scheduling and user administration capabilities.  Of the remaining solutions FreshBI  was ranked second.

Note that there are a lot of other potential solutions including the many Xero application partners  or the many cloud data integration tools but these were the ones that I found to be most useful.

Solution Details

FreshBI Power BI Custom Connector for Xero Accounting was initially selected primarily because it was quick and easy way to get Xero API data.

• • The FreshBI team has developed their own custom Power BI Xero connector that they sell for a fixed price. Once you have the connector you are free to modify it as required which is what I did.
  • The FreshBI connector connects to API and returns data to Power BI as Power BI queries. The connector can be accessed and the query code modified as desired to get data into desired shape or to modify it to retrieve multiple years of data.
  • However, code required non-trivial modification to get desired data. Original connector retrieves only 1 year of pivoted format data. I modified the query code to unpivot the data months from columns to rows. Stopped before making further modifications to retrieve > 1 year data. (Interesting note: based on my discussions with FreshBI they said they may modify connector to provide this capability which they will make available as update to purchased connector).
  • Connector cannot refresh from Xero from PBI Online. Only Microsoft ‘certified’ connectors have this capability and FreshBI said Microsoft will certify their connector only if it has many more users. Hence, a Power BI pbix file published to PBI Online can only refresh data by using the PBI Gateway which is free app that runs connector on laptop or on server and syncs Xero data and PBI Online dataset.
  • Support complexity deemed high due to modifications, multiple components and environments.

Acterys ultimately selected because it provides best supported, most robust Xero data solution.

• • Acterys has developed a sophisticated modern BI data modelling and process which meets all data requirements eg actuals, budget, forecast, relational star schema, process to import budget and forecast, modify existing datasets.
  • Includes Microsoft Azure SQL Server database for Xero data storage as reporting data source.
  • Xero data refresh process includes scheduling, status email notifications, and previously retrieved update process. Changes made to data within past 7 days updated in Acterys database.
  • Support complexity deemed medium, though Acterys solution is quite complex, it built and supported by Acterys, for all of their customers, which reduces end user support complexity.

ODataLink and CData were not selected as they are essentially alternate methods of FreshBI connector API data acquisition, and do not offer additional data modelling other than Xero Report API. Interesting note: As a result of my discussions with them, OdataLink added new functionality to retrieve > 1 year data.

Synchub not selected because while it provides excellent data syncing capabilities it does not have any data modelling simply providing raw Xero database table data.

Xero PBI App is a Power BI application that is available in the Microsoft Power BI app store. It is considered only as a Xero “showcase”. While the reports can be modified, or new reports created using the dataset, the data has only rolling 1 year data, and it is not possible to have access to the report pbix file to inspect or modify the dataset, hence it is limited to the purpose of the “showcase” reporting.

Hopefully this write-up was helpful when deciding how to get data out of Xero for analytical and reporting purposes. It seemed to me while researching this topic that there is still a lot of opportunity to provide Xero or other ERP / financial application data integration and reporting capability.