Xero - beautiful accounting software

Xero Developer Help Center

Xero Developer Community

Community > Getting Started >

Data Schema of PowerBI Xero App or Xero Website Dashboards

Started by Themis A -   in Getting Started

Hello,

We have been using the dashboards from Xero App on PowerBI and we would like to recreate them on PowerBI desktop.

We have created a connection to Xero database and we now have access to all Xero tables but we want to see what is the data schema (e.g. how the tables are linked) of Xero App tables so as to recreate something similar on PowerBI desktop .

We also want the same for the Dashboards which are available on Xero Website. What is the data schema behind of how the tables are linked so as to produce the online reports (e.g. the P&L or Trail Balance report). Doing reverse engineering can be a bit difficult.

Any help will be greatly appreciated.

Thanks
T
> What is the data schema behind of how the tables are linked so as to produce the online reports.

XERO is exposing API's rather than raw tables. To generate those reports if you are not using the report builders themselves you need to go vanilla and link them up. P&L reports from my understanding can be done through the Invoices API as both bills and invoices are exposed within that API; information on whether they've been paid is also available.
 

Jonathan Mifsud  

How did you connect to the Xero Tables in Power BI? Did you use the CNet ODBC connector? Just curious if there is another way?
 

Adriaan Jacobs  

>How did you connect to the Xero Tables in Power BI? Did you use the CNet ODBC connector? Just curious if there is another way? <

I have the same question - we need to merge data from other data sources with xero data in our PowerBI Dashboards, reports and models. I can't find a way to connect to Xero using PowerBI desktop - only through the app and limited edit report capabilities in the browser.

Thanks
Ed
 

Ed Richard  

> How did you connect to the Xero Tables in Power BI? Did you use the CNet ODBC connector? Just curious if there is another way? <

Unfortunately most of the available methods are methods where you need to purchase a software.
Method 1 (Free): Use Python programming code to connect to API and get the data. The tables themselves dont have a good field structure though
Method 2: Use an ODBC connector like the one from CData company
Method 3: Use SSIS Integration toolkit like the one from Kingswaysoft company
Method 4: Use a software that brings all the info to a specified database (check Blendo)
Method 5: You can try MS Azure which has one year free subscription and see its capabilities
 

Themis A  

Thanks - using our own code running Azure - what is the current recommended best practice for Authentication assuming this will be an un-attended process running once a week?
 

Ed Richard  

This has been asked many times over the years in this forum and I've never seen Xero post it. Many ISVs seem to be jealous of their database schemas for some reason, which doesn't quite make sense insofar as they expose said database through an API. The next best thing is probably this:
https://github.com/XeroAPI/Xero-OpenAPI

The Xero content pack for Power BI is wholly inadequate. You can use it to create a Live Connection from Power BI Desktop, which is better and exposes the tables accessible through the API, but Live Connection limits you to one source and prevents you from doing any modeling, so that's still far from ideal.

There's also no connector in Power BI's dataflows, which is where it would be best to do this. However, there is a preview connector in Azure Data Factory, which has its foibles (make sure to read the doc and generate a 512-bit key, not 1024) and in my experience times out on the "Complete" versions of the tables but does work with the "Minimal" ones.
 

Olivier Travers