|
|
| (14 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| = Overview = | | = Overview = |
| | To keep OdataLink subscriptions affordable and predictable for everyone, we've introduced a [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. This policy sets a monthly download limit for data retrieved from your connected [[Data Files|data file(s)]]. |
|
| |
|
| To keep OdataLink subscriptions affordable and predictable for everyone, OdataLink has introduced a [https://odatalink.com/fair-use-policy '''Fair Use Policy'''] with a '''''Monthly Download Allowance per licensed Data File'''''.
| | The guidance below applies to all models connecting to Xero, Xero Practice Manager, MYOB and QuickBooks. Following these steps will: |
|
| |
|
| The guidance below applies to ''all Connected Accounting Systems'', namely Xero, Xero Practice Manager, MYOB and QuickBooks, but is most relevant to Xero given the new upstream egress charges. Following these steps will:
| | * Reduce your monthly download volume; and |
| | * Improve refresh performance |
|
| |
|
| * Reduce your monthly download volume,
| | You can monitor your current usage at any time in your '''OdataLink Admin Portal → Usage Stats'''. |
| * Improve refresh performance, and
| |
| * Help you stay within your allowance — avoiding optimisation requests or custom pricing.
| |
|
| |
|
| You can monitor your current usage at any time in the [https://app.odatalink.com OdataLink Admin Portal] under the '''Usage''' tab in Model and Data File details.
| | See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]]. |
|
| |
|
| <br /> | | <br /> |
|
| |
|
| == For everyone (accountants, bookkeepers, business users) == | | == What is Usage == |
| | <onlyinclude> |
| | '''Usage''' is defined as the volume of data OdataLink retrieves from your connected [[Data Files|data files]] each month. |
|
| |
|
| The following changes can be applied directly in your OdataLink Admin Portal or in your reporting software:
| | This includes data retrieved by [[Microsoft Power BI|Power BI]], [[Microsoft Excel|Excel]], or [[OData Client Compatibility|other]] data pipeline and reporting tools, either via automatic scheduled refreshes or manual report refresh. |
| | </onlyinclude> |
| | = Pick your Optimisation Layer = |
| | Optimisation techniques are grouped by the tooling you use. Pick the page that matches your role — or read all three if your organisation spans several layers. |
|
| |
|
| === 1. Reduce automatic refresh frequency === | | {| class="wikitable" |
| | ! Page !! Audience !! What it covers |
| | |- |
| | | [[How to Reduce my OdataLink Usage - For Everyday Users]] || Accountants, bookkeepers, business users || Refresh frequency, [[Models|model]] settings (Cut-off Date, Default Date Range, Polling), monitoring |
| | |- |
| | | [[How to Reduce my OdataLink Usage - For Advanced Users]] || Power BI, Fabric, and Excel report developers (Power Query / M) || Query folding, OData <code>$filter</code> operators, <code>$select</code> projection, incremental refresh |
| | |- |
| | | [[How to Reduce my OdataLink Usage - For Power Users]] || Data engineering teams (SSIS, ADF, Fabric Data Pipelines, Fabric Dataflow Legacy/Gen2) || Stage-once-serve-many, watermarked incremental loads, historical backfill strategy, scheduling discipline, end-to-end cost monitoring |
| | |} |
|
| |
|
| Most reports do not need to refresh every hour, every day, or on weekends. Each scheduled refresh re-pulls data and counts toward your allowance.
| | <span style="color:navy"><blockquote>'''Tip:''' Start with the [[How to Reduce my OdataLink Usage - For Everyday Users|Everyday Users]] page regardless of your role. The model-level settings described there apply to '''every''' refresh, automatic or manual, report or pipeline, and deliver the largest reduction with the least amount of changes.</blockquote></span> |
|
| |
|
| * Match the refresh frequency to how often you actually look at the report.
| | <br /> |
| * Disable refreshes outside business hours and on weekends if the report is not viewed then.
| | <!-- |
| * For monthly reports, a daily or weekly refresh is usually sufficient.
| | == For everyone (accountants, bookkeepers, business users) == |
| | |
| === 2. Apply a Cut-off Date (Hybrid Archive) on your model ===
| |
| | |
| The Cut-off Date setting tells OdataLink to treat older data as "archived". Archived data is downloaded once and reused, while only recent data is refreshed on each pull.
| |
| | |
| This method dramatically reduces the volume retrieved on every refresh.
| |
| | |
| [https://odatalink.com/2025-03-increase-performance-of-your-odatalink-odata-feeds-using-the-cut-off-date-settings/ How to use the Cut-off Date setting →]
| |
| | |
| === 3. Set a Default Date Range on your model === | |
| | |
| If you never report on data older than, for example, two financial years, you can configure the model to ignore data outside that window entirely.
| |
| | |
| This applies a date filter at the source and reduces every download.
| |
| | |
| [https://help.odatalink.com/index.php?title=Default_Date_Range How to set a default date range →]
| |
| | |
| === 4. Enable Polling for Changes ===
| |
|
| |
|
| Polling allows OdataLink to detect and pull only the records that have changed in your accounting file, rather than re-downloading the full dataset on every refresh.
| | Please refer to the following support note detailing optimisation techniques that can be applied directly in your OdataLink Admin Portal or in your reporting architecture. |
|
| |
|
| [https://help.odatalink.com/index.php?title=Configure_OdataLink_to_poll_for_changes How to configure polling →] | | [[How to Reduce my OdataLink Usage - For Everyday Users]] |
|
| |
|
| <br /> | | <br /> |
| Line 51: |
Line 46: |
| == For Power BI, Fabric, and Excel developers == | | == For Power BI, Fabric, and Excel developers == |
|
| |
|
| The following optimisation techniques require working inside Power Query or transformation queries in your reporting software.
| | For optimisations applied inside Power Query — query folding, <code>$filter</code>, <code>$select</code>, incremental refresh — see: |
| | |
| === 5. Push date filters down to the OData feed level (query folding) ===
| |
| | |
| Filtering data '''after''' it has been downloaded does not reduce egress. The records have already been pulled from the company file.
| |
| | |
| Filtering must happen at the '''Navigation step''' in Power Query so that the filter is sent to OdataLink as part of the OData query (`$filter=...`) and only the matching rows are returned.
| |
|
| |
|
| # Apply date filters as early as possible in the query — directly on the source table, before any merges, joins, or column expansions.
| | [[How to Reduce my OdataLink Usage - For Advanced Users]] |
| # Avoid applying `Table.SelectRows` over a downloaded table when an equivalent filter can be applied at the navigation step.
| |
| # Verify folding by right-clicking a step in Power Query → **View Native Query**. If the option is greyed out, the filter is no longer folding and is being applied client-side.
| |
| | |
| [https://help.odatalink.com/index.php?title=Model_Date_Filters_and_Report_Date_Filters Model Date Filters vs Report Date Filters →] | |
| | |
| === 6. Use Incremental Refresh for time-series data ===
| |
| | |
| For any dataset keyed or based on a date, datetime, or timestamp column, configure '''Incremental Refresh''' so that only new or changed partitions are pulled on each refresh and not the entire history. | |
| | |
| * '''Power BI Premium / Fabric:''' native incremental refresh on semantic models.
| |
| * '''Dataflow Gen2 (Fabric):''' [https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-incremental-refresh Dataflow Gen2 incremental refresh →]
| |
| * '''Power BI Pro:''' incremental refresh is supported but requires care to ensure the date filter folds back to the OData source. Test with '''View Native Query''' before publishing.
| |
| | |
| <span style="color:navy"><blockquote>'''Tip:''' OData feeds can be staged into a Fabric Dataflow Gen2, then consumed downstream by Power BI, Excel, Power Apps, and Power Automations.
| |
| This means OdataLink (and therefore Xero) is queried '''once per refresh''', not once per consumer.
| |
| This change can significantly cut total egress when multiple reports share the same data.
| |
| </blockquote></span>
| |
| <br /> | | <br /> |
|
| |
|
| == For SQL, SSIS, and Fabric Developers == | | == For SQL, SSIS, and Fabric Pipeline Developers == |
|
| |
|
| For users staging OdataLink data into SQL Server, Azure SQL, Fabric Lakehouse / Warehouse, or other persistent stores via SSIS, Azure Data Factory (ADF), or Fabric Data Pipelines. | | For optimisations applied at the pipeline / data warehouse layer — stage-once-serve-many, watermarked incremental loads, Fabric Dataflow Gen2 staging, scheduling discipline — see: |
|
| |
|
| === 7. Stage once, pull indefinitely ===
| | [[How to Reduce my OdataLink Usage - For Data Engineers]] |
| | |
| The single biggest egress saving comes from removing duplicate downloads. Avoid having multiple reports, models, or tools all pulling the same OData entity directly. Instead:
| |
| | |
| # Pull each OData entity into a staging table (SQL Server, Lakehouse, Warehouse) **once per refresh cycle**.
| |
| # Point all downstream consumers (Power BI semantic models, Excel reports, SSRS, Fabric notebooks) at the staging layer — not at OdataLink.
| |
| | |
| This collapses N reports × M refreshes into a single retrieval per cycle.
| |
| | |
| === 8. Implement true incremental loads with `$filter` ===
| |
| | |
| When loading data into a SQL DB or an Azure Lakehouse, do not truncate-and-reload on every run. Use a high-watermark column (e.g. `LastModifiedDateTime`, `UpdatedDateUTC`) and pass it into the OData URL via the `$filter` parameter so OdataLink only returns changed rows.
| |
| | |
| Example pattern:
| |
| <code>GET .../Invoices?$filter=LastModifiedDateTime gt 2026-05-01T00:00:00Z</code>
| |
| | |
| # Store the maximum `LastModifiedDateTime` from the previous successful load in a control table.
| |
| # Use that value as the lower bound for the next pull.
| |
| # Merge (UPSERT) the result into the target table on the natural key.
| |
| | |
| <span style="color:navy"><blockquote>This pattern works in **SSIS** (OData Source component, parameterised URL), **Azure Data Factory** / **Fabric Data Pipelines** (Copy Activity with dynamic OData query), and any custom .NET / Python loader.</blockquote></span>
| |
| | |
| [https://help.odatalink.com/index.php?title=OData_URLs OdataLink OData URL structure and query parameters →] | |
| | |
| === 9. Select only the columns you need with `$select` ===
| |
| | |
| OData supports projection via `$select=Col1,Col2,...`. By default, retrieving an entity returns every column, many of which are wide free-text fields that you may never report on. Restricting projection at the URL level reduces row size, network transfer, and data file egress.
| |
| | |
| Example:
| |
| <code>GET .../Invoices?$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>
| |
| | |
| === 10. Use `$top` and pagination deliberately ===
| |
| | |
| For initial historical loads, paginate with `$top` and `$skip` (or follow the `@odata.nextLink`) at a controlled rate. Avoid full-table re-pulls in production schedules — full re-loads should be a one-time backfill, not a recurring job. | |
| | |
| === 11. Schedule pipeline runs during off-peak windows and align cycles ===
| |
| | |
| * Stagger pipeline schedules so that a single Xero tenant is not being hammered by overlapping pipelines.
| |
| * Align pipeline frequency with the underlying business cycle. Daily General Ledger snapshots rarely need sub-hourly retrieval.
| |
| * For Fabric Data Pipelines, use **dependency-based triggers** rather than fixed schedules — downstream pipelines run only when the upstream load actually produced new rows.
| |
| | |
| === 12. Cache reference data separately ===
| |
| | |
| Reference entities (Chart of Accounts, Tax Rates, Tracking Categories, Contacts where stable) change far less frequently than transactional entities (Invoices, Bills, Bank Transactions). Refresh reference tables on a slower cadence (daily or weekly) and transactional tables on a faster cadence — instead of refreshing everything together.
| |
|
| |
|
| <br> | | <br> |
| | | --> |
| == Quick checklist == | | == Quick checklist == |
| | A cross-cutting checklist drawn from all three pages. Detailed steps and tool-specific guidance are in the linked pages above. |
|
| |
|
| | '''Everyone:''' |
| * [ ] Refresh schedule matches how often the report is actually used | | * [ ] Refresh schedule matches how often the report is actually used |
| * [ ] Weekend / out-of-hours refreshes disabled where not needed | | * [ ] Weekend / out-of-hours refreshes disabled where not needed |
| * [ ] Cut-off Date configured on the model | | * [ ] Archive Type set to '''Live (with Archive)''' on the [[Models|model]] |
| * [ ] Default Date Range configured on the model | | * [ ] Cut-off Date or rolling window configured |
| | * [ ] Default Date Range configured to match your reporting window |
| * [ ] Polling enabled | | * [ ] Polling enabled |
| | |
| | '''Advanced Users:''' |
| * [ ] Date filters applied at the OData navigation step (not post-download) | | * [ ] Date filters applied at the OData navigation step (not post-download) |
| * [ ] Query folding verified via **View Native Query** | | * [ ] Query folding verified via '''View Native Query''' |
| | * [ ] <code>$select</code> used to limit columns retrieved |
| * [ ] Incremental refresh enabled for time-series datasets | | * [ ] Incremental refresh enabled for time-series datasets |
| * [ ] Pipelines stage data once into SQL / Lakehouse before downstream consumption | | |
| * [ ] `$filter`-based incremental loads with a high-watermark column | | '''Azure/Fabric/Data Pipeline developers:''' |
| * [ ] `$select` used to limit columns retrieved | | * [ ] OdataLink data staged once into SQL / Lakehouse / Warehouse / Dataflow Gen2; downstream consumers read from the staging layer |
| | * [ ] All recurring loads use <code>$filter</code> with a high-watermark column |
| | * [ ] <code>$select</code> used on every entity, including the watermark column |
| | * [ ] Full-table backfills are one-time, not recurring |
| | * [ ] Pipeline schedules staggered and aligned with the business cycle |
| * [ ] Reference and transactional data refreshed on separate cadences | | * [ ] Reference and transactional data refreshed on separate cadences |
| | * [ ] Both OdataLink egress and Azure / Fabric compute cost monitored |
|
| |
|
| <br> | | <br /> |
|
| |
|
| == Need help? == | | == Need help? == |
|
| |
|
| If you have followed the steps above and your usage is still high, contact OdataLink support. We can review your model configuration, refresh patterns, and pipeline design, and recommend further optimisations before any custom pricing applies. | | If you have followed the steps above and your usage is still high, contact OdataLink support. We can review your model configuration, refresh patterns, and pipeline design, and recommend further optimisations to bring your account usage within limits. |
|
| |
|
| <br> | | <br> |
| Line 155: |
Line 95: |
| == Related Topics == | | == Related Topics == |
|
| |
|
| * [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy] | | * [https://odatalink.com/eula/fair-use-policy OdataLink Fair Use Policy] |
| * [https://app.odatalink.com OdataLink Admin Portal — Usage tab] | | * [[How to track my OdataLink Usage]] |
| | * [[OData Feed URLs]] |
| <!-- * [https://developer.xero.com/pricing Xero Developer Pricing] | | <!-- * [https://developer.xero.com/pricing Xero Developer Pricing] |
| * [https://developer.xero.com/faq/pricing-and-policy-updates Xero Pricing and Policy Updates FAQ]--> | | * [https://developer.xero.com/faq/pricing-and-policy-updates Xero Pricing and Policy Updates FAQ]--> |