Xero Accounts Reporting Database - Overview
Purpose
The Xero Accounts Reporting Database provides a read-optimised, analytics-friendly representation of Xero accounting data.
It is designed for:
- Power BI
- Looker Studio
- Tableau
- Excel and SQL analytics
- Finance and reconciliation reporting
This database is not an operational copy of Xero.
It is a reporting layer designed to simplify joins, preserve financial history, and expose consistent reporting metrics.
What is Included
The database contains:
-
Core Xero accounting entities:
- Contacts
- Invoices
- Credit Notes
- Payments
- Bank Transactions
- Accounts
- Journals
-
Normalised base tables aligned to the Xero API
-
Derived reporting views for financial analysis
-
Stable UUIDs suitable for long-term BI models
Each customer receives:
- A dedicated database
- Automated synchronisation
- Backfilled historical data (subject to Xero API limits)
Design Principles
1. Accounting-first modelling
Tables follow standard accounting concepts such as:
- Debits and credits
- Document states
- Posting and effective dates
This ensures consistency with financial reporting expectations.
2. Base tables and reporting views
- Base tables closely mirror Xero objects
- Reporting views provide:
- Ledger-style outputs
- Reconciliation-friendly datasets
- BI-ready aggregations
Most reporting should use views.
3. Append-safe and audit-friendly
- Records are never hard-deleted
- Corrections and reversals are preserved
- Full audit history remains queryable
Key Reporting Views
| View Name | Purpose |
|---|---|
| InvoiceSummary | One row per invoice with totals, tax, and status |
| PaymentAllocations | Payments applied to invoices |
| AccountBalances | Account-level balances over time |
| BankReconciliation | Bank transaction matching and reconciliation |
Typical BI Use Cases
- Accounts receivable ageing
- Cash flow analysis
- Revenue by customer or account
- Tax and GST reporting
- Invoice and payment reconciliation
- Month-end and period-close reporting
Recommended Next Steps
- Start with the reporting views
- Use base tables only when raw detail is required
- Build BI models around UUIDs
- Treat this database as the source of truth for Xero reporting