OdataLink

Reducing Data Usage and Optimising your OdataLink Feeds: Difference between revisions

← Older editNewer edit →
Ash (talk | contribs)
Write
604 edits
mNo edit summary
Ash (talk | contribs)
Write
604 edits
mNo edit summary
Line 7: Line 7:
* Improve refresh performance
* Improve refresh performance


You can monitor your current usage at any time in your [https://app.odatalink.com OdataLink Admin Portal].
You can monitor your current usage at any time in your [https://app.odatalink.com/Accounts/View OdataLink Admin Portal → '''Usage Stats'''].


Steps to monitor usage by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]] can be found in the [[How to track my OdataLink Usage]] support page.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].


<br />
<br />
Line 18: Line 18:
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.
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.


= 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.
{| 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 Gen2) || Stage-once-serve-many, watermarked incremental loads, historical backfill strategy, scheduling discipline, end-to-end cost monitoring
|}
<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 effort.</blockquote></span>
<br />


== For everyone (accountants, bookkeepers, business users) ==
== For everyone (accountants, bookkeepers, business users) ==
Line 29: Line 45:
== 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) ===
[[How to Reduce my OdataLink Usage - For Advanced Users]]
 
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.
# 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.
 
[[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 Pipeline 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 ===
 
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:
[[How to Reduce my OdataLink Usage - For Data Engineers]]
<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 <b>SSIS</b> (OData Source component, parameterised URL), <b>Azure Data Factory / Fabric Data Pipelines</b> (Copy Activity with dynamic OData query), and any custom .NET / Python query.</blockquote></span>
 
[[OData Feed 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
'''Power BI, Fabric, and Excel developers:'''
* [ ] 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
'''Data engineering teams:'''
* [ ] `$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 134: Line 95:


* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]
* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]
* [https://app.odatalink.com OdataLink Admin Portal — Usage tab]
* [https://app.odatalink.com/Accounts/View OdataLink Admin Portal → '''Usage Stats''']
* [[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]-->
Retrieved from "https://help.odatalink.com/index.php?title=Reducing_Data_Usage_and_Optimising_your_OdataLink_Feeds"