Ash at 05:29, 22 June 2026

2026-06-22T05:29:32Z

← Older revision		Revision as of 00:29, 22 June 2026
Line 14:		Line 14:
	By default, every refresh re-downloads your full history. Older transactions rarely change, so re-fetching them each time spends your monthly usage allowance for no benefit.		By default, every refresh re-downloads your full history. Older transactions rarely change, so re-fetching them each time spends your monthly usage allowance for no benefit.

	A local archive fetches only the recent window live and keeps the rest locally. See [[~~https://help.odatalink.com/index.php?title=How_to_track_my_OdataLink_Usage~~#Usage\|What is Usage]].		A local archive fetches only the recent window live and keeps the rest locally. See [[How to track my OdataLink Usage#Usage\|What is Usage]].

	===2. Stay within Xero's API limits===		===2. Stay within Xero's API limits===

Ash at 07:47, 18 June 2026

2026-06-18T07:47:37Z

← Older revision		Revision as of 02:47, 18 June 2026
Line 80:		Line 80:
	== Related topics ==		== Related topics ==
	* [[Model Date Filters and Report Date Filters]]		* [[Model Date Filters and Report Date Filters]]
	* [~~https://help.odatalink.com/index.php?title=~~How_to_track_my_OdataLink_Usage#Usage Usage]		* [[How_to_track_my_OdataLink_Usage#Usage \| Usage]]

Ash at 07:47, 18 June 2026

2026-06-18T07:47:00Z

← Older revision		Revision as of 02:47, 18 June 2026
Line 80:		Line 80:
	== Related topics ==		== Related topics ==
	* [[Model Date Filters and Report Date Filters]]		* [[Model Date Filters and Report Date Filters]]
	* [[https://help.odatalink.com/index.php?title=How_to_track_my_OdataLink_Usage#Usage\|Usage]]		* [https://help.odatalink.com/index.php?title=How_to_track_my_OdataLink_Usage#Usage Usage]

Ash: new page to help users set up their own local archive

2026-06-18T07:46:27Z

new page to help users set up their own local archive

New page

__NOTOC__
== Overview ==
In some use cases, it is not possible to use OdataLink's inbuilt archive to improve performance and reduce refresh time.

This may be due to organisational policies, subject to approval, or operational decisions.

A '''Local Archive''' keeps previously downloaded data in your report (Power BI or Excel) and fetches only the most recent period live from your [[Data Filed | Data Files]] on each refresh.

Older records are read from the archive instead of being downloaded again.

==Why create a Local Archive==
=== 1. Lower Data Usage and Egress ===
By default, every refresh re-downloads your full history. Older transactions rarely change, so re-fetching them each time spends your monthly usage allowance for no benefit.

A local archive fetches only the recent window live and keeps the rest locally. See [[https://help.odatalink.com/index.php?title=How_to_track_my_OdataLink_Usage#Usage|What is Usage]].

===2. Stay within Xero's API limits===
Xero throttles data file refreshes (for example 5,000 calls per day and 60 per minute), and some endpoints are paged (Journals return 100 records per page regardless of your model's page size).

Pulling only recent data on each refresh keeps you comfortably under these limits and avoids rate-limit errors (9004 to 9008).

===3. Faster, more reliable refreshes===
Less data per refresh means quicker refreshes and fewer timeouts, especially for larger datasets.

===4. Preserve history===
Your data stays in the [[Models|model]] even if it ages beyond your OdataLink model's [[Default Date Range|Default Start Date]], exceeds a report limit, or the live connection is temporarily unavailable.

===5. If historical data does not change, do not re-download it===
Posted historical transactions are stable, and Xero journals are immutable (a correction is posted as a new journal, never an edit).

Downloading the same historical rows on every refresh is redundant.

== How to build a Local Archive ==
# Create a parameter in your Power Query, '''MonthsToRefresh''' which indicates how many recent months' data is fetched live.
# A cut-off date '''ArchiveFrom''' can be calculated by reducing the MonthsToRefresh from today's date.
# Rows recorded on or after '''ArchiveFrom''' will be considered '''Live''' (re-fetched on each refresh).
# Rows before '''ArchiveFrom''' date are '''history''' (retained from the archive).
# The two datasets are then combined into the final table.

== Power Query ==
====STEP 1: Create ArchiveFrom parameter ====
<syntaxhighlight lang="powerquery">MonthsToRefresh = 3</syntaxhighlight>

====STEP 2: Calculate Cut-off date====
<syntaxhighlight lang="powerquery">
let
Today = Date.From(DateTimeZone.FixedLocalNow()),
ArchiveFrom = Date.AddMonths(Today, -MonthsToRefresh)
in
ArchiveFrom
</syntaxhighlight>

====STEP3: Split the dataset and combine ====
<syntaxhighlight lang="powerquery">
let
// Live data: the most recent MonthsToRefresh months
KeepLive = Table.SelectRows(Source, each Date.From([Date]) >= ArchiveFrom),

// History: older than the cut-off
KeepHistory = Table.SelectRows(Source, each Date.From([Date]) < ArchiveFrom),

// Combine history + live
Combined = Table.Combine({KeepHistory, KeepLive})
in
Combined
</syntaxhighlight>

<span style="color:navy"><blockquote>'''Note:''' Apply this filter right '''after Navigation''' step so that the filter flows though OdataLink and filters data at the source.
See '''[[Model Date Filters and Report Date Filters]]'''
</blockquote></span>

== Make it a True Archive ==
In a single file, <code>KeepHistory</code> and <code>KeepLive</code> read from the same <code>Source</code>, so they recombine the same rows. To actually persist history and fetch only recent data live, point each branch at a different source:
* '''KeepHistory''': a stored copy that is not refreshed (a Power BI dataflow, a saved CSV/Parquet, or a separate archive query).
* '''KeepLive''': the live OdataLink feed, filtered to <code>>= ArchiveFrom</code>.

In Power BI, the productionised equivalent is '''Incremental Refresh''', which uses RangeStart/RangeEnd parameters to retain old partitions and refresh only recent ones.

== Related topics ==
* [[Model Date Filters and Report Date Filters]]
* [[https://help.odatalink.com/index.php?title=How_to_track_my_OdataLink_Usage#Usage|Usage]]

HelpID:LocalArchive - Revision history

Ash at 05:29, 22 June 2026

Ash at 07:47, 18 June 2026

Ash at 07:47, 18 June 2026

Ash: new page to help users set up their own local archive