Design Goals
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.
Tables
Accounts
Accounts (aka Ledgers) are the records that create the Chart of Accounts, which Xero uses to classify financial transactions.
When multiple Xero Organisations are grouped, a deduplicated set of Account Codes are returned which, when combined with a standardised Chart of Accounts, make it easy to report on financial data across Organisations.
Bank Transactions
Provide details of bank transactions for all linked bank accounts.
Bank Transfers
Provide details of the bank transfers between bank accounts.
Budgets
Budgets can be used to track your actual and forecast spending.
Connectorly processes budgeted amounts to calculate a value for each day, based on the number of days in the month. This makes it easy to report with budgets against timeframes other than months.
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
Contacts are the Organisations and Individuals that are transacted with. They may be Suppliers or Customers (or both).
Contacts are processed so that key metrics, such as Lifetime Sales value are available.
Dates
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.
Four different calendars are supported:
The 'normal' Gregorian Calendar; years start on January 1st and finish on December 31st.
The Fiscal calendar, which uses the fiscal year from your Xero organisation. If you have multiple Xero organisations grouped, then it uses the primary Organisation's fiscal year. This is the most commonly used calendar used for financial reporting.
The ISO Year calendar. This splits months into full weeks, following a 4-4-5 week pattern, so 'January' has four weeks (28 days), 'February' has four weeks and 'March' has five (35 days). Weeks (and hence Months and Years) always start on a Monday. Years usually have 364 days (52 weeks). In order to catch up with reality(!) every now and then an extra week is added, in which case January has 5 weeks. This calendar is useful if you want to closely track weekly figures and is often used in retail. It's disadvantages are that you can't compare months, as they are not the same period. Mind you, that's also true (and less pronounced so overlooked) for Fiscal and Gregorian calendar comparisons.
The ISO Fiscal Year calendar. This uses the ISO Year calendar, except that the year will start on the closest Monday to the beginning of the fiscal year as set in Xero.
Invoices
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.
Journals
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.
To simplify forecasting and budgeting, Connectorly automatically creates additional Journal entries (which can be filtered by Journal Type).
Alongside actuals, draft invoices, forecast invoice FX Exposure and forecast budget values are created.
Organisations
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.
Overpayments
Details of over-payments made by Contacts.
Payments
The details of payments made Contacts against their invoices.
Prepayments
Details of pre-payments made by Contacts.
Products
Products (aka Services or Items) can be used to report on Stock levels or Sales performance.
Purchase Orders
Details the Purchase Orders that have been created in Xero.
Quotes
Details of any quotes that have been created in Xero.
Views
Three different types of views can be created, to group Accounts, Contacts and Products and structure reports with.