We designed Connectorly for Xero and Microsoft Power BI to make it easy to report across the full Xero financial data set. We had the following goals:
An easy to understand Xero data structure that provides full access to your Organisations data...
that works across multiple Xero Organisations so that you can report at the aggregate level...
and even if the underlying Xero Organisations have different operating currencies.
To make it easy to work in different date ranges such as fiscal years, halves, quarters, months and weeks...
that includes support across 4-4-5 week ranges as well as the more usual calendar ranges.
To provide an easy way to group financial information across accounts, contacts and products, and quickly switch between these different views.
To enrich the budgeting and forecasting experience, by providing forecast information based on repeating invoices, and expected payment dates.
To achieve these goals we have both simplified and substantially enriched the raw data that comes from Xero, which are structured into Tables as described below.
Accounts (aka Ledgers) are the records that create the Chart of Accounts, which Xero uses to classify financial transactions.
Provide details of bank transactions for all linked bank accounts.
Provide details of the bank transfers between bank accounts.
Budgets can be used to track your actual and forecast spending.
This is particularly useful when combined with the ISO weekly calendars as well as making it simple to track daily progress, for example when tracking Sales performance.
Contacts are the Organisations and Individuals that are transacted with. They may be Suppliers or Customers (or both).
The Dates table provides an easy way to group and/or label time. Tables within the model are related to the Date field so that they can all be filtered by the same time period.
This table contains both Sales and Bills (aka Purchase) Invoices and also any respective credit notes. Amounts are signed so that they may be summed to create totals.
Invoices hold details of the Customer (or Supplier) that was transacted with. Note that they don't hold all financial information; for example, Salary and Tax financial information will not appear in Invoices.
Xero automatically creates Journal records for any transactions that are approved and are the heart of the accounting system, holding details of all transactions against each account over time.
Unlike Invoices, they don't hold details of who was transacted with. They are complete, so expect to find Salary, Tax and other non-invoiced Financial information here.
Contains details of the Xero organisation(s) available for reporting. These records are typically used to filter data to report on; for example to remove subsidiaries when reporting across a Group company, or to switch between different client Organisations.
The details of payments made against their invoices.
Products (aka Services or Items) can be used to report on Stock levels or Sales performance.
Three different types of views can be created, to group Accounts, Contacts and Products and structure reports with.