OdataLink - User contributions [en]

Default Page Size

2026-10-22T02:44:02Z

Ash: updated Xero developer page URL

[[File:Default_Page_size_setting.png|300px|thumb|right|Page Size in Model settings]]
A '''Query or call''' sent to [[MYOB]] or [[Xero]] returns a '''Page''' of results ''wherever Pagination is possible''.

The Default Page Size setting controls the number of '''Records''' returned per page.

A Record contains '''all Line Items details''' for that particular endpoint.

Increasing the default page size decreases the calls required to fetch data, which in turn makes refreshes faster.

== How to change the Default Page Size for a model ==

To change the default page size for a [Models|model]] details, follow these steps.

# Click on '''Models''' on the menu in the left-hand side;
# Find the [[Models|model]] you want to work on and click '''View''';
# Select the '''Settings''' tab;
# Click on the '''Edit Model''' button;
# Select the required '''[[Default Page Size]]'''.
#: This controls the number of records that will be returned on a single call for most endpoints. See the recommended page size below for more information.
# Click '''Save'''.

== Recommended Default Page Size ==

The recommended default page size varies based on the type of model or data file you are pulling from.

=== MYOB Models ===

The recommended page size for MYOB Business API is 400 records per page, with a values ranging from 50 to 1000 results per page.

* Smaller data files will perform faster using a larger page size (1000).
* Larger data files will perform more reliably using a smaller number (200).
* MYOB recommends a value of 400 to resolve [[OdataLink Error Code 8002: MYOB timed out while servicing the request|error 8002]] or [[OdataLink Error Code 8003: MYOB encountered a runtime exception while servicing the request|error 8003]].

The page size settings apply to all the endpoints ''except'' the following:

*[[MYOB Endpoint: Company|Company]]
*[[MYOB Endpoint: CompanyPreferences|CompanyPreferences]]
*[[MYOB Endpoint: TaxCodes|TaxCodes]]
*[[MYOB Endpoint: BalanceSheetSummaryReport|BalanceSheetSummaryReport]],
*[[MYOB Endpoint: BalanceSheetSummaryReportTable|BalanceSheetSummaryReportTable]],
*[[MYOB Endpoint: BalanceSheetSummaryReportMultiPeriodTable|BalanceSheetSummaryReportMultiPeriodTable]]
*[[MYOB Endpoint: PayrollTaxes|PayrollTaxes]]
*[[MYOB Endpoint: PayrollTaxTables|PayrollTaxTables]]
*[[MYOB Endpoint: PayrollWages|PayrollWages]]

=== Xero Accounting Models ===

The recommended page size for Xero Accounting models is 1000 with values ranging from 50 to 1000.

The Page size settings apply to the following endpoints:

*[[Xero Accounting Endpoint: BankTransactions|BankTransactions]]
*[[Xero Accounting Endpoint: BankTransactionsExpanded|BankTransactionsExpanded]]
*[[Xero Accounting Endpoint: ContactsExpanded|ContactsExpanded]]
*[[Xero Accounting Endpoint: CreditNotesExpanded|CreditNotesExpanded]]
*[[Xero Accounting Endpoint: InvoicesExpanded|InvoicesExpanded]]
*[[Xero Accounting Endpoint: InvoicesExpandedTable|InvoicesExpandedTable]]
*[[Xero Accounting Endpoint: ManualJournalsExpanded|ManualJournalsExpanded]]
*[[Xero Accounting Endpoint: OverpaymentsExpanded|OverpaymentsExpanded]]
*[[Xero Accounting Endpoint: Payments|Payments]]
*[[Xero Accounting Endpoint: PaymentsTable|PaymentsTable]]
*[[Xero Accounting Endpoint: PrepaymentsExpanded|PrepaymentsExpanded]]
*[[Xero Accounting Endpoint: PurchaseOrders|PurchaseOrders]]

=== Xero Assets Models ===

The recommended page size for Xero Assets API is 200 with possible values ranging from 10 to 200 records per page.

The Page size settings apply to the following endpoints:
*[[Xero Assets Endpoint: Assets|Assets]]

=== Xero Projects Models ===

The recommended page size for Xero Projects API is 500 with values ranging from 50 to 500.

The Page size settings apply to the following endpoints:
*[[Xero Projects Endpoint: Projects|Projects]]
*[[Xero Projects Endpoint: Tasks|Tasks]]
*[[Xero Projects Endpoint: Times|Times]]
*[[Xero Projects Endpoint: GetTasks|GetTasks]]
*[[Xero Projects Endpoint: GetTimes|GetTimes]]

== Related Topics ==

[[Editing a model's details]]

== References ==

[https://odatalink.com/2025-03-increase-the-performance-of-your-xero-odata-feed-using-the-page-size-setting/ Increase the performance of your Xero OData Feed using the Page Size setting]

[https://developer.xero.com/documentation/best-practices/api-call-efficiencies/paging/#overview Xero Paging Overview]

[https://developer.myob.com/api/myob-business-api/api-overview/retrieving-data/#PAGING MYOB Paging Overview]

__NOTOC__

Optimizing Your Model Settings for Performance and Security

2026-07-08T10:45:26Z

Ash: correcting wiki links

A [[Models|Model]] is a configuration layer that controls the '''performance, security and access''' to the [[MYOB]] and [[Xero]] data connected to it.

OdataLink recommends the following changes to the configuration of your model in order to get the best performance while maintaining secure access according to the roles and permissions for each user.
 
= Security =
OdataLink includes 2 types of security options that can be configured independent of each other:
 
; Firewall-based security
: Enabling the '''inbuilt Firewall''' restricts '''''unknown IP Addresses''''' which are not authorised to query data.
: The '''System Type''' IP addresses are essential for querying data successfully when the reports are published online.
: The Admin [[Users|User]] of the account can add new [[IP Addresses]] or approve new [[IP Addresses]] for other users.
: Please refer to [[Configuring IP addresses|How to Configure IP addresses in your OdataLink account]] for more information on how to add a specific or a range of [[IP Addresses]].

; Basic Authentication
: Setting a '''[[Model Login Type|Model's Login Type]] to Basic''' enforces users to enter their '''''OdataLink credentials''''' in order to access [[MYOB]] or [[Xero]] data through that model.
: Your [[OData client]] such as Excel, Power BI, or Power Query in Fabric will require the user credentials in order to start fetching the data into the Report or Dashboard.
 
= Performance =
; Hybrid Archive / Cut Off Date
: OdataLink includes a Hybrid Archive feature that you can activate and use to cache historical data in OdataLink.
: This reduces the load on [[MYOB]] and [[Xero]] servers, ensuring upto 10x faster data refreshes.

; Page Size
: The Page Size setting in a model instructs the source server to return the selected number of records per page.
: For [[Xero]], OdataLink recommends setting the highest value available for Page size according to your model type.
: For [[MYOB]], OdataLink recommends setting the Page Size to '''900''' and reduce it until '''400''', and select the most optimal setting according to your dataset.

; Page By
: The Page By setting instructs OdataLink to download data paged one month at a time, one quarter at a time, one calendar year at a time, or default as paged by the source servers
: OdataLink recommends using '''''Page By Month''''' or '''''Page By Quarter''''' in most cases. The '''''Page By Year''''' option should only be used for smaller data files.
: The '''''Default''''' option is there for those that build more complex queries where you do not want OdataLink to page the by a time dimension.

 
= Related Topics =
:[[Archive Types|Model's Archive Type Setting]]
:[[Editing a model's details|Edit Model Settings]]
:[[Model Login Type|Login Type Settings for a Model]]
:[[Configuring IP addresses#Tasks|Firewall Related Configurations]]

How to subscribe to OdataLink with a Complimentary license

2026-07-02T11:37:50Z

Ash:

To continue using OdataLink after your trial, a subscription is required.

A Complimentary license can be provided by the OdataLink team in specific cases.

A Complementary license includes 1 data file license.

Complementary licenses are free of charge and there would be no invoices generated for a complementary license subscription.
[[File:ComplementaryLicense.png|300px|thumb|right|Subscribe to OdataLink with a Complementary License]]
== Steps ==
To subscribe to OdataLink with a Complementary license, follow these steps.
# If you have multiple accounts, activate the one you wish to subscribe to
# Select Account / Subscription item in the menu.
# Click on the Subscribe button.
# Click on Edit Account Details button to update your details.
#: See [[Editing my account's details]] for more information.
# Click Select Billing User button
#: See [[Selecting the billing admin user]] for more information.
# Select Subscription Type as Complementary.
# Tick the checkbox to confirm you agree with the Terms and Conditions.
# Click on the Subscribe button.

== Related Topics ==
[[Managing my account]] 
[[Subscribing to OdataLink]]

== Key Words ==
Complementary License
 Complementary

How to subscribe to OdataLink with a Complimentary license

2026-07-02T11:37:24Z

Ash:

File:ComplementaryLicense.png

2026-07-02T11:35:46Z

Ash:

How to subscribe to OdataLink with a Complimentary license

2026-07-02T11:35:27Z

Ash:

To continue using OdataLink after your trial, a subscription is required.

A Complimentary license can be provided by the OdataLink team in specific cases.

A Complementary license includes 1 data file license.

Complementary licenses are free of charge and there would be no invoices generated for a complementary license subscription.
[[File:ComplementaryLicense.png|300px|thumb|right|Subscribe to OdataLink with a Complementary License]]
== Steps ==
To subscribe to OdataLink with a Complementary license, follow these steps.
# If you have multiple accounts, activate the one you wish to subscribe to
# Select Account / Subscription item in the menu.
# Click on the Subscribe button.
# Click on Edit Account Details button to update your details.
#: See [[Editing my account's details]] for more information.
# Click Select Billing User button
#: See [[Selecting the billing admin user]] for more information.
# Select Subscription Type as Complementary.
# Tick the checkbox to confirm you agree with the Terms and Conditions.
# Click on the Subscribe button.

== Related Topics ==
[[Managing my account]]
[[Subscribing to OdataLink]]

== Key Words ==
Complementary License
Complement

How to subscribe to OdataLink with a Complimentary license

2026-07-02T11:35:13Z

Ash:

To continue using OdataLink after your trial, a subscription is required.

A Complimentary license can be provided by the OdataLink team in specific cases.

A Complementary license includes 1 data file license.

Complementary licenses are free of charge and there would be no invoices generated for a complementary license subscription.
[File:ComplementaryLicense.png|300px|thumb|right|Subscribe to OdataLink with a Complementary License]]
== Steps ==
To subscribe to OdataLink with a Complementary license, follow these steps.
# If you have multiple accounts, activate the one you wish to subscribe to
# Select Account / Subscription item in the menu.
# Click on the Subscribe button.
# Click on Edit Account Details button to update your details.
#: See [[Editing my account's details]] for more information.
# Click Select Billing User button
#: See [[Selecting the billing admin user]] for more information.
# Select Subscription Type as Complementary.
# Tick the checkbox to confirm you agree with the Terms and Conditions.
# Click on the Subscribe button.

== Related Topics ==
[[Managing my account]]
[[Subscribing to OdataLink]]

== Key Words ==
Complementary License
Complement

How to subscribe to OdataLink with a Complimentary license

2026-07-02T11:32:39Z

Ash:

To continue using OdataLink after your trial, a subscription is required.

A Complimentary license can be provided by the OdataLink team in specific cases.

A Complementary license includes 1 data file license.

Complementary licenses are free of charge and there would be no invoices generated for a complementary license subscription.

== Steps ==
To subscribe to OdataLink with a Complementary license, follow these steps.
# If you have multiple accounts, activate the one you wish to subscribe to
# Select Account / Subscription item in the menu.
# Click on the Subscribe button.
# Click on Edit Account Details button to update your details.
#: See [[Editing my account's details]] for more information.
# Click Select Billing User button
#: See [[Selecting the billing admin user]] for more information.
# Select Subscription Type as Complementary.
# Tick the checkbox to confirm you agree with the Terms and Conditions.
# Click on the Subscribe button.

== Related Topics ==
[[Managing my account]]
[[Subscribing to OdataLink]]

== Key Words ==
Complementary License
Complement

How to subscribe to OdataLink with a Complimentary license

2026-07-02T11:31:53Z

Ash:

To continue using OdataLink after your trial, a subscription is required.

A Complimentary license can be provided by the OdataLink team in specific cases.

A Complementary license includes 1 data file license.

Complementary licenses are free of charge and there would be no invoices generated for a complementary license subscription.

== Steps ==
To subscribe to OdataLink with a Complementary license, follow these steps.
# If you have multiple accounts, activate the one you wish to subscribe to
# Select Account / Subscription item in the menu.
# Click on the Subscribe button.
# Click on Edit Account Details button to update your details.
#; See [[Editing my account's details]] for more information.
# Click Select Billing User button
#; See [[Selecting the billing admin user]] for more information.
# Select Subscription Type as Complementary.
# Tick the checkbox to confirm you agree with the Terms and Conditions.
# Click on the Subscribe button.

== Related Topics ==
[[Managing my account]]
[[Subscribing to OdataLink]]

== Key Words ==
Complementary License
Complement

How to subscribe to OdataLink with a Complimentary license

2026-07-02T11:31:21Z

Ash: Created page with "To continue using OdataLink after your trial, a subscription is required. A complimentary license can be provided by the OdataLink team in specific cases. A complementary li..."

To continue using OdataLink after your trial, a subscription is required.

A complimentary license can be provided by the OdataLink team in specific cases.

A complementary license includes 1 data file license.

Complementary licenses are free of charge and there would be no invoices generated for a complementary license subscription.

== Steps ==
To subscribe to OdataLink with a Complementary license, follow these steps.
# If you have multiple accounts, activate the one you wish to subscribe to
# Select Account / Subscription item in the menu.
# Click on the Subscribe button.
# Click on Edit Account Details button to update your details.
#; See [[Editing my account's details]] for more information.
# Click Select Billing User button
#; See [[Selecting the billing admin user]] for more information.
# Select Subscription Type as Complementary.
# Tick the checkbox to confirm you agree with the Terms and Conditions.
# Click on the Subscribe button.

== Related Topics ==
[[Managing my account]]
[[Subscribing to OdataLink]]

=== Key Words ===
Complementary License
Complement

Editing my account's details

2026-07-02T11:25:24Z

Ash:

By editing your [[Account|accout's]] details, you will be able to configure it's '''Name''' and '''Account Code''' as well as '''Address''' fields.

= Steps =

To edit your [[Account|account's]] details, follow these steps.

# Select '''Account / Subscription''' item in the menu.
# Click on the '''Edit Account Details''' button.
# Enter the '''Account Name'''.
#: This is the display name of the [[Account|account]] and is only used within the OdataLink website.
# Enter your '''Company Address'''.
# Select the '''Time Zone''' for the account.
#: Some [[OData Feeds]] will use this information to format dates in a readable format.
# Tick / untick the '''Enabled''' checkbox.
#: Controls whether all [[OData Feed URLs|OData feed urls]] for this [[Account|account]] are usable.
# Click '''Save'''.

= Related Topic =

[[Managing my account]]

Subscribing to OdataLink

2026-07-02T11:25:02Z

Ash: updated the name of the button from "Change address" button to "Edit Account Details" button on subscribe page

To continue using OdataLink after your trial a subscription is required

A subscription can be setup within the OdataLink app via the Accounts tab

The subscription process involves choosing the number of MYOB Companys or Xero Organisations (data files) required in total, confirming the edition that you require and then entering your credit card details

Subscription fees will be process monthly. Your able cancel at any time

When choosing the number of data files. Enter the total number you require. ie if you already have 1 and require 5 then choose 5. A pro rata invoice will be generated for the additional 4 data files

The most cost effective edition will be selected by default based on the number of data files selected

= Steps =

To subscribe to OdataLink, follow these steps.

# If you have multiple accounts, activate the one you wish to subscribe to
# Select '''Account / Subscription''' item in the menu.
# Click on the '''Subscribe''' button.
# Click on '''Edit Account Details''' to update your details.
#: See [[Editing my account's details]] for more information.
# Click '''Select Billing User''' button.
#: See [[Selecting the billing admin user]] for more information.
# Select the '''Subscription Type'''.
# Enter the number of '''Data File Licenses''' required in total.
# Tick to confirm you agree with the '''Terms'''.
# Click on the '''Proceed to Payment''' or '''Subscribe''' button.

If subscribing to paid edition, you will then need to [[Selecting the licensed data files|select the licensed data files]].

= Related Topic =

[[Managing my account]] 

= Search Words =
Subscribe to OdataLink

HelpID:LocalArchive

2026-06-22T05:29:32Z

Ash:

__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 [[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>

<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>

== 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]]
* [[How_to_track_my_OdataLink_Usage#Usage | Usage]]

HelpID:LocalArchive

2026-06-18T07:47:37Z

Ash:

__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>

<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>

== 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]]
* [[How_to_track_my_OdataLink_Usage#Usage | Usage]]

HelpID:LocalArchive

2026-06-18T07:47:00Z

Ash:

__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>

<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>

== 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

2026-06-18T07:46:27Z

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

__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>

<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>

== 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]]

How to Transfer Ownership of an OdataLink account

2026-06-15T06:54:44Z

Ash:

= Overview =
In some cases, an organisation may engage independent consultants or developers to build a specialised custom reporting solution.

When the engagement is completed, or the project is finished, the ownership of the [[Account|OdataLink Account]] can be transferred by following the steps below

__NOTOC__
== Invite an Account Admin and a Billing Admin to your OdataLink account ==
# [[Inviting users to my account|Send an Invite]] to the emails/persons designated to be the Account's [[Users|Admin User]] and [[Users|Billing Admin]] for the account.
# Once the Admin and Billing Admin have registered as Users in OdataLink dashboard, [[Editing my account's details|update your account details]] and save the changes.
# You can add shared mailboxes to [[Selecting_the_user_that_will_receive_invoices|recieve invoices]] by clicking on '''Manage Invoice Recipients''' button in Account/Subscription settings under '''''Invoice Recipients'''''.

== Remove and Revoke the Access of External Users ==
# Once the Account managers are invited to the account, they can [[Revoking users access to my account|Revoke access of external users]] to the account.
# Remove all [[Removing IP addresses|IP Addresses]] belonging to the external users.
 

== Related Topics ==
[[Managing my account]]

[[Managing users]]

[[Configuring IP addresses]]

How to Reduce my OdataLink Usage - For Power Users

2026-06-05T04:30:30Z

Ash: fair use policy published, correcting the location of the policy doc on the website

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your organisation have persists OdataLink data into SQL Server, Azure SQL, Fabric Lakehouse/Warehouse, or any other data platform via SSIS, Azure Data Factory (ADF), using Fabric Data Pipelines or Fabric DataFlows, the following techniques will help reduce egress and pipeline usage costs.

This help page is intended towards Data Engineering and Fabric Developer teams running data pipelines and staging data into persistent storage. For report-level optimisations, see [[How to Reduce my OdataLink Usage - For Advanced Users]].

For OdataLink model-level optimisations, see [[How to Reduce my OdataLink Usage - For Everyday Users]].

You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

= Stage Once, Serve Many =

=== 1. Use a Single Staging Platform or Pipeline ===
The biggest source of high usage is the same OData entity being downloaded by multiple reports, models, and pipelines. Each retrieval contributes to increasing the total egress or data downloaded from connected [[Data Files|Data File(s)]]

# Pull relevant OData endpoints into a '''staging layer''' (SQL Server, Azure SQL, Fabric Lakehouse, Fabric Warehouse, etc.) '''once per refresh cycle'''. Schedule the refresh or trigger it on demand via Power Automate.
# Update all your queries to point to the staging layer - Power BI Dashboards/Reports/semantic models, Excel reports, Fabric notebooks, Power Apps, and disconnected from OdataLink.

This collapses N reports × M queries refreshes into a '''single retrieval per cycle'''.

<blockquote>'''Tip:''' '''Fabric Dataflow Legacy/Gen2''' is a managed staging layer built on the Power Query (M) engine. It is well suited to teams who want centralised OdataLink ingestion without provisioning SQL Server. Multiple Power BI, Excel, Power Apps, and Power Automate consumers can then read from the Dataflow output, querying OdataLink only once per Gen2 refresh.</blockquote>

[https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-incremental-refresh Fabric Dataflow Gen2 incremental refresh]

= Incremental Loading =

=== 2. Replace truncate-and-reload with watermarked incremental loads ===

Truncate-and-reload re-downloads everything on every run. Instead, use a '''high-watermark column'''<ref name="watermark" group="watermark">Cardoso, M. (2023). Robust data ingestion with High-watermarking. ''Microsoft Community Hub''. [https://techcommunity.microsoft.com/blog/fasttrackforazureblog/robust-data-ingestion-with-high-watermarking/3707480 techcommunity.microsoft.com/blog/fasttrackforazureblog/robust-data-ingestion-with-high-watermarking/3707480]</ref> (typically <code>LastModifiedDateTime</code> or <code>UpdatedDateUTC</code>) and pass it into the OData URL via <code>$filter</code> so OdataLink returns only changed rows.

Pattern:
<code>GET .../Invoices?$filter=LastModifiedDateTime gt 2026-05-01T00:00:00Z</code>

# Store the maximum <code>Watermark_Value</code> from the last successful run in a '''control table''' (e.g. <code>dbo.PipelineWatermarks</code>, <code>dbo.Company1Watermarks</code>, <code>dbo.LastUpdatedWatermark</code>).
# Read and inject that value as the lower bound into the OData URL at the start of the next run.
# '''Merge (UPSERT)''' the result into the target table on the natural key (UID). Apply append rules carefully, handling edge cases.
# Update the watermark <ref name="watermark" group="watermark" /> in the control table '''only after''' the merge has completed successfully. A failed run can safely re-pull the same window for the data on retry.

<blockquote>'''Tip:''' This pattern works in '''SSIS''' (OData Source component with parameterised URL via Expression), '''Azure Data Factory / Fabric Data Pipelines''' (Copy Activity with dynamic OData query expression), '''Fabric Dataflow Gen2''' (M parameter), and any custom .NET / Python ETL.</blockquote>

[[OData Feed URLs| OdataLink OData URL structure and query parameters]]

 

=== 3. Project only required columns with <code>$select</code> ===

By default, retrieving an entity returns every column, most of which are rarely useful in a warehouse. Restricting columns at the URL level reduces download size, egress, and staging table footprint.

Example:
<code>GET .../Invoices?$select=InvoiceID,InvoiceNumber,Date,Total,Status,LastModifiedDateTime</code>

Always include your watermark <ref name="watermark" group="watermark" /> column in <code>$select</code>, even if it is not loaded into the final warehouse table. Your query transformations need it for the next refresh.

 

=== 4. Run historical backfills as a one-time non-recurring job ===

The first load of a large entity is the most expensive download. Your Data workflow should handle it as a one-off setup task, and not on a schedule.

# '''Run the backfill once''': using a separate package or pipeline distinct from your incremental job.
# '''Follow <code>@odata.nextLink</code>''': to page through the data in chunks rather than trying to pull everything in a single request.
# '''Break very large entities into date-bounded windows''': (year-by-year or quarter-by-quarter) using <code>$filter=Date ge 'YYYY-01-01' and Date lt 'YYYY+1-01-01'</code>. A failed run only re-runs the failed window.
# '''Record completed windows''': in a control table (e.g. <code>dbo.BackfillProgress</code>). The backfill becomes safe to re-run, already-loaded windows can be skipped.
# '''Switch off the backfill once history is loaded''': Recurring queries or jobs will then fetch new data using incremental loads only (Section 2).

<blockquote>'''Rule of thumb:''' If your scheduled pipeline is re-downloading data older than intended, check your query filters. Historical data should already be in your staging table.</blockquote>

 

= Scheduling =

=== 5. Schedule pipelines deliberately ===

* '''Stagger pipeline schedules''', so that multiple pipelines do not hit the same tenant simultaneously.
* '''Align frequency with the business cycle.''' Daily General Ledger snapshots rarely need sub-hourly retrieval.
* '''Use off-peak windows''': schedule heavy historical refreshes overnight or at weekends.
* '''Use dependency-based triggers''': In Fabric Data Pipelines and Azure Data Factory, trigger downstream pipelines based on the completion event of the upstream load and not at a fixed time. Downstream jobs should run only when the upstream actually produced new rows.

 

=== 6. Refresh reference and transactional data on separate frequencies ===

Reference entities change far less frequently than transactional entities. Split your pipeline into two schedules:

{| class="wikitable"
! Frequency!! Examples
|-
| '''Slow (daily or weekly)''' || Chart of Accounts, Tax Rates, Tracking Categories, Contacts (where stable), Items
|-
| '''Fast (intra-day or hourly)''' || Invoices, Bills, Credit Notes, Bank Transactions, Journals
|}

Refreshing reference tables less often and joining them server-side at the warehouse avoids re-downloading effectively static data multiple times per day.

 

= Monitoring =

=== 7. Monitor pipeline cost end-to-end ===

OdataLink egress is one component of total pipeline cost. Track both OdataLink usage and your Fabric Compute Units (CUs)

* '''OdataLink egress:''' OdataLink Admin Portal → View Account Details → '''Usage Stats'''
* '''Azure/Fabric Compute and storage :''' Azure Cost Management, Fabric Capacity Metrics app

A well-tuned pipeline reduces both egress and compute. If OdataLink usage drops but Fabric capacity unit (CU) consumption rises, review whether the staging layer itself is computing on the same data set multiple times.

 

== Combined egress reduction example ==

Stacked optimisation techniques reduce Egress and Cost significantly:

<code>GET .../Invoices?$filter=LastModifiedDateTime gt '2026-05-01T00:00:00Z'&$select=InvoiceID,InvoiceNumber,Date,Total,Status,LastModifiedDateTime</code>

Combined with stage-once-serve-many architecture, this typically reduces OdataLink egress by '''90%+''' versus N consumers each pulling full unfiltered entities.

 

== Power Users checklist ==

* [ ] 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<ref name="watermark" group="watermark" />
* [ ] Watermark stored in a control table, updated only on successful append/merge
* [ ] <code>$select</code> used on every entity, including the watermark column
* [ ] Full-table backfills are one-time, not recurring
* [ ] Historical backfills broken into resumable date-bounded windows
* [ ] Pipeline schedules staggered and aligned with the business cycle
* [ ] Reference and transactional data refreshed on separate schedules
* [ ] Downstream pipelines triggered by upstream completion, not fixed time
* [ ] Both OdataLink egress and Azure / Fabric compute cost monitored

 

== Need help? ==

If you have followed the steps above and your usage is still high, contact OdataLink support. We can review your data query and transformation steps, refresh patterns, and pipeline design, and recommend further optimisations in order to bring your account usage within limits.
 

= References =
<references group="watermark" />

= Related Topics =

* [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
* [[How to Reduce my OdataLink Usage - For Everyday Users]]
* [[How to Reduce my OdataLink Usage - For Advanced Users]]
* [[OData Feed URLs]]
* [[Models]]
* [[Data Files]]
* [[How to track my OdataLink Usage]]
* [https://odatalink.com/eula/fair-use-policy OdataLink Fair Use Policy]

How to Reduce my OdataLink Usage - For Advanced Users

2026-06-05T04:29:59Z

Ash: fair use policy published, correcting the location of the policy doc on the website

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your reports or dashboards use Power Query transformations to clean and shape the dataset, the following optimisations techniques will reduce egress and improve refresh performance.

This help page is intended towards report developers working with '''M Language/Power Query'''. For developers using Fabric Data Pipelines, Azure Data Factory, SSIS, or persistent staging into Lakehouse/Warehouse/DataLake,
 see [[How to Reduce my OdataLink Usage - For Power Users]]

You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

= Query-Level Filtering =
This sections explains how applying a filter early in the transformation steps helps reduce egress. Query level filtering makes use of OdataLink feature that can pass the filters up directly to the data source. This results in OdataLink only downloading the filtered data out of your [[Data Files|data file(s)]].

=== 1. Push date filters up to the OData feed level (query folding) ===
Filtering data '''after''' it has been downloaded does not reduce egress since the records have already been pulled out from the company file.

Filtering must happen at the '''Navigation step''' in Power Query so the filter is sent to OdataLink as part of the OData query (<code>$filter=...</code>) and only the matching/filtered rows are returned.

# Apply date filters as early as possible, directly on the source table, before any merges, joins, or column expansions.
# Avoid applying <code>Table.SelectRows</code> over a downloaded table when an equivalent filter can be applied at the navigation step.


[[Model Date Filters and Report Date Filters| '''Reference -''' Model Date Filters vs Report Date Filters]]

 

=== 2. Apply supported OData filter operators ===

The following operators fold to the OData feed and execute server-side before any data leaves your data file:

{| class="wikitable"
! Operator !! Operation !! Example
|-
| <code>eq</code> || equal || <code>Status eq 'ACTIVE'</code>
|-
| <code>ne</code> || not equal || <code>Status ne 'ARCHIVED'</code>
|-
| <code>gt</code>, <code>lt</code> || greater / less than || <code>Date gt '2026-01-01'</code>
|-
| <code>ge</code>, <code>le</code> || greater / less than or equal || <code>Amount ge 1000</code>
|-
| <code>and</code> || logical AND || <code>Status eq 'ACTIVE' and Type eq 'BANK'</code>
|-
| <code>or</code> || logical OR || <code>Type eq 'BANK' or Type eq 'CREDIT_CARD'</code>
|-
| <code>startswith()</code> || prefix match || <code>startswith(Empname, 'Ia')</code>
|-
| <code>contains()</code> || substring match || <code>contains(GLCode, '5-01')</code>
|}

<blockquote>'''Note:''' <code>startswith()</code> and <code>contains()</code> work on text/string fields (such as Code, Name, Description) but may fail or produce unexpected results on date, datetime and datetimezone fields. Use comparison operators (<code>gt</code>, <code>lt</code>, <code>ge</code>, <code>le</code>) for those columns instead.</blockquote>

 

=== 3. Filter examples by use case ===

'''Date range''' — highest single-impact reduction:
<code>$filter=Date ge '2026-01-01' and Date le '2026-12-31'</code>

'''Active records only:'''
<code>$filter=Status eq 'ACTIVE'</code>

'''Exclude archived:'''
<code>$filter=Status ne 'ARCHIVED'</code>

 

=== 4. Limit columns retrieved with <code>$select</code> ===

OData supports projection via <code>$select=Col1,Col2,...</code>. By default, every column on the entity is returned, including wide free-text fields that may never appear in your report. Restricting projection reduces row size and network transfer.

In Power Query, expand only the columns you need before any other step, and confirm the resulting native query includes a <code>$select</code> clause.

Example:
<code>$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

 
=== 5. Combining Filters ===

Stacked techniques decrease the Usage rapidly. Example combined filter + projection:

<code>$filter=Date ge '2026-06-01' and Status eq 'ACTIVE'&$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

Typically reduces egress by '''~80%''' vs. an unfiltered, all-columns extraction.

 

= Incremental Refreshing =

=== 6. Use Incremental Refresh for time-series data ===

For datasets keyed on a date, datetime, or datetimezone column, configure '''Incremental Refresh''' so that only new or changed partitions are pulled on each refresh.

* '''Power BI Premium / Fabric:''' native incremental refresh on semantic models.
* '''Power BI Pro:''' supported, but requires care to ensure the date filter folds. Test with '''View Native Query''' before publishing.
* '''Excel:''' use a date-threshold named range and filter the query URL on it.

<blockquote>'''Tip:''' If your organisation uses Microsoft Fabric, ask your data engineering team or IT admin about staging OdataLink feeds into a '''Fabric Dataflow''' (Legacy or Gen2, built on Power Query). Multiple reports, staging databases and data warehouses can then consume the staged data without each one querying OdataLink directly. See [[How to Reduce my OdataLink Usage - For Power Users]].</blockquote>

 
= Monitoring =

Verify your optimisations in your Models Settings '''Manage Models → View → Usage tab''' for monitoring a specific model. Compare:
* Unfiltered request volume (GB) - your baseline before optimising
* Filtered request volume (GB) - after applying optimisation
* '''Reduction %''' = (Unfiltered Volume − Filtered Volume) ÷ Unfiltered Data Volume × 100

'''Target:''' 50–70% combined reduction from filtering + incremental refresh + column projection.

 

== Power Query checklist ==

* [ ] Date filters applied at the OData navigation step
* [ ] Verify Query folding
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets
* [ ] Refresh schedule matches actual report usage
* [ ] Considered Fabric Dataflow Gen2 if multiple reports share the same data

 

== 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 query transformations, and recommend further optimisations in attempts to reduce your account usage.

 

= Related Topics =

* [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
* [[How to Reduce my OdataLink Usage - For Everyday Users]]
* [[How to Reduce my OdataLink Usage - For Power Users]]
* [[OData Feed URLs]]
* [[Models]]
* [[Data Files]]
* [[How to track my OdataLink Usage]]
* [[Model Date Filters and Report Date Filters]]
* [https://odatalink.com/eula/fair-use-policy OdataLink Fair Use Policy]

How to Reduce my OdataLink Usage - For Everyday Users

2026-06-05T04:29:24Z

Ash:

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your account's connected [[Data Files|data file(s)]] are frequently approaching or exceeding monthly limits, the following techniques will help reduce usage with minimal impact to your reporting functionality.

The following optimisations can be applied directly in your '''OdataLink Admin Portal''' or in your reporting software.


You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].


= Refresh Schedule =

=== 1. Reduce Automatic Refresh frequency ===
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.

* Match the refresh frequency to how often you actually look at the report.
* 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.
<blockquote>'''Tip:''' Refreshing a sales dashboard hourly when the team only checks it on Monday mornings can use 24× more data than needed. Drop to daily, or weekly, and you may reduce that report's usage by 95%+.</blockquote>

 

= Model Settings =
The settings below are configured once per [[Models|model]] in your '''OdataLink Portal''' and applies to '''every refresh''' request.

=== 2. Apply a Cut-off Date on your model ===

OdataLink provides the ability to archive historical data to avoid re-downloading of same data on every refresh. The '''Cut-Off Date''' instructs OdataLink to save data up to the cut-off date and serve future refreshes from that archive.

Setting a cut-off date in your model dramatically reduces the volume retrieved on every refresh by avoiding downloading the same historical data multiple times.

To set a cut off date and enable archiving in your model:
# '''Archive Type:''' Set the [[Archive Types|Archive Type]] of your model to '''Live (with Archive)''' to enable archiving.
# '''Archive Cut-Off Date:''' Set either a fixed cut-off date, or a '''rolling window''' that continuously archives data older than X months or X days.


<blockquote>'''Tip:''' OdataLink recommends a rolling window so new data is automatically archived as it ages. You only configure it once.</blockquote>

[https://odatalink.com/2025-03-increase-performance-of-your-odatalink-odata-feeds-using-the-cut-off-date-settings/ How to apply and use the Cut-off Date setting]

=== 3. Set a Default Date Range on your model ===

OdataLink provides the ability to define a default date range for a model. This allows users to control the amount of data that gets downloaded from the connected [[Data Files|data file(s)]].

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.

[[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 been added or changed in your [[Data Files|data file]], rather than re-downloading the whole dataset on every refresh.

Polling is particularly effective for high-volume transactional entities like Invoices, Bills, and Bank Transactions.

[[Configure OdataLink to poll for changes|How to configure polling]]

 

=Monitoring =

Check your Monthly Usage in your OdataLink Portal '''Account/Subscription settings → Usage Stats'''.

'''Healthy pattern:'''
* Consistent month-to-month (no large spikes)
* 20-30% under your monthly allowance
* No overuse warnings

'''Excessive Usage pattern:'''
* Usage increasing or consistently above 100% month-over-month
* Multiple Usage alerts from OdataLink
* Exceeding allowance for more than 1 month

 

== Checklist ==

* [ ] Refresh frequency matches how often the report is actually viewed
* [ ] Weekend and off-hours refreshes disabled if not needed
* [ ] Archive Type set to '''Live (with Archive)''' for the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled
* [ ] Monthly usage reviewed in OdataLink Admin Portal

 

== 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 query transformations, and recommend further optimisations  in order to bring your account usage within limits.

 

= Related Topics =
: [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
: [[How to Reduce my OdataLink Usage - For Advanced Users]]
: [[How to Reduce my OdataLink Usage - For Power Users]]

: [https://odatalink.com/eula/fair-use-policy Fair Use Policy]

Reducing Data Usage and Optimising your OdataLink Feeds

2026-06-05T04:06:57Z

Ash: fair use policy published, correcting the location of the policy doc on the website

= 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)]].

The guidance below applies to all models connecting to Xero, Xero Practice Manager, MYOB and QuickBooks. Following these steps will:

* Reduce your monthly download volume; and
* Improve refresh performance

You can monitor your current usage at any time in your '''OdataLink Admin Portal → Usage Stats'''.

See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

== What is Usage ==
<onlyinclude>
'''Usage''' is defined as the volume of data OdataLink retrieves from your connected [[Data Files|data files]] each month.

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.

{| 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
|}

<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>

 

== 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
* [ ] Weekend / out-of-hours refreshes disabled where not needed
* [ ] Archive Type set to '''Live (with Archive)''' on the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled

'''Advanced Users:'''
* [ ] Date filters applied at the OData navigation step (not post-download)
* [ ] Query folding verified via '''View Native Query'''
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets

'''Azure/Fabric/Data Pipeline developers:'''
* [ ] 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
* [ ] Both OdataLink egress and Azure / Fabric compute cost monitored

 

== 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 to bring your account usage within limits.

 

== Related Topics ==

* [https://odatalink.com/eula/fair-use-policy OdataLink Fair Use Policy]
* [[How to track my OdataLink Usage]]
* [[OData Feed URLs]]

How to track my OdataLink Usage

2026-06-05T04:06:21Z

Ash: fair use policy published, correcting the location of the policy doc on the website

= Overview =
Each OdataLink account is subject to Monthly Data download allowance under OdataLink's [https://odatalink.com/fair-use-policy '''Fair Use Policy (Policy)'''] document.
Usage is shared across entire account and data files, so higher activity on one data file can be offset bu lower activity on other(s).
OdataLink tracks data usage at the Account level, Models level, and per data file level, to help you stay within your Monthly Data Allowance limits.

= Usage =
[[File:OdataLink_Account_Usage.png|400px|thumb|right|Account Usage]]

{{:Reducing Data Usage and Optimising your OdataLink Feeds}}
=== What does not count as Usage ===
* Data retrieved from OdataLink's archive (historical cached data).
* Data retrieved from data sources other than connected Data Files
[[File:OdataLink_Model_Usage.png|400px|thumb|right|Model Usage]]
[[File:OdataLink_Data_File_Usage.png|400px|thumb|right|Data File Usage]]

== Where can I find my Usage ==
Login to your '''OdataLink Admin portal''' and according to each level, navigate to the appropriate '''Usage''' tab as explained below:

=== 1. Account Level Usage ===
# Click on '''Account/Subscription''' Settings on the left side panel.
# '''View Account''' page will be loaded.
# A graph under '''Usage Stats''' will be displayed indicating your '''Account's Total Usage''' for the period as selected in the '''Month''' dropdown field.

=== 2. Model Level Usage ===
# Click on '''Models''' on the left side panel to view all Models.
# Click on the Model Name or the '''View''' button corresponding to the model.
# Click on '''Usage''' tab and a graph will be shown indicating your '''Model's Total Usage''' for the period as selected in the '''Month''' dropdown field.

=== 3. Data File Level Usage ===
# Click on '''Data Files''' on the left side panel to view all Data Files connected to your account.
# Click on the Entity/Company/Data File Name or the '''View''' button corresponding to the Data File.
# Click on '''Usage''' tab and a graph will be shown indicating your '''Data File's Total Usage''' for the period as selected in the '''Month''' dropdown field.

= How to Reduce my Usage =
If your Usage is high or is approaching the Monthly Data Allowance limits, see [[Reducing Data Usage and Optimising your OdataLink Feeds]]

The help page above contains guides for:
* Everyday Users - To optimise at OdataLink level
* Advanced Users - To apply optimisations at report and data query level
* Power Users - To apply optimisations at staging levels and pipeline levels, custom solutions apply

= Related Topics =
: [https://odatalink.com/eula/fair-use-policy Fair Use Policy]
: [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]

HelpID:TrackUsage

2026-06-05T04:05:10Z

Ash: Redirected page to How to track my OdataLink Usage

#REDIRECT[[How to track my OdataLink Usage]]

How to Reduce my OdataLink Usage - For Advanced Users

2026-06-01T05:53:48Z

Ash: corrected framing for fabric

How to Reduce my OdataLink Usage - For Advanced Users

2026-06-01T05:48:55Z

Ash: added other type of columns to use with comparison operators

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your reports or dashboards use Power Query transformations to clean and shape the dataset, the following optimisations techniques will reduce egress and improve refresh performance.

This help page is intended towards report developers working with '''M Language/Power Query'''. For developers using Fabric Data Pipelines, Azure Data Factory, SSIS, or persistent staging into Lakehouse/Warehouse/DataLake,
 see [[How to Reduce my OdataLink Usage - For Power Users]]

You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

= Query-Level Filtering =
This sections explains how applying a filter early in the transformation steps helps reduce egress. Query level filtering makes use of OdataLink feature that can pass the filters up directly to the data source. This results in OdataLink only downloading the filtered data out of your [[Data Files|data file(s)]].

=== 1. Push date filters up to the OData feed level (query folding) ===
Filtering data '''after''' it has been downloaded does not reduce egress since the records have already been pulled out from the company file.

Filtering must happen at the '''Navigation step''' in Power Query so the filter is sent to OdataLink as part of the OData query (<code>$filter=...</code>) and only the matching/filtered rows are returned.

# Apply date filters as early as possible, directly on the source table, before any merges, joins, or column expansions.
# Avoid applying <code>Table.SelectRows</code> over a downloaded table when an equivalent filter can be applied at the navigation step.


[[Model Date Filters and Report Date Filters| '''Reference -''' Model Date Filters vs Report Date Filters]]

 

=== 2. Apply supported OData filter operators ===

The following operators fold to the OData feed and execute server-side before any data leaves your data file:

{| class="wikitable"
! Operator !! Operation !! Example
|-
| <code>eq</code> || equal || <code>Status eq 'ACTIVE'</code>
|-
| <code>ne</code> || not equal || <code>Status ne 'ARCHIVED'</code>
|-
| <code>gt</code>, <code>lt</code> || greater / less than || <code>Date gt '2026-01-01'</code>
|-
| <code>ge</code>, <code>le</code> || greater / less than or equal || <code>Amount ge 1000</code>
|-
| <code>and</code> || logical AND || <code>Status eq 'ACTIVE' and Type eq 'BANK'</code>
|-
| <code>or</code> || logical OR || <code>Type eq 'BANK' or Type eq 'CREDIT_CARD'</code>
|-
| <code>startswith()</code> || prefix match || <code>startswith(Empname, 'Ia')</code>
|-
| <code>contains()</code> || substring match || <code>contains(GLCode, '5-01')</code>
|}

<blockquote>'''Note:''' <code>startswith()</code> and <code>contains()</code> work on text/string fields (such as Code, Name, Description) but may fail or produce unexpected results on date, datetime and datetimezone fields. Use comparison operators (<code>gt</code>, <code>lt</code>, <code>ge</code>, <code>le</code>) for those columns instead.</blockquote>

 

=== 3. Filter examples by use case ===

'''Date range''' — highest single-impact reduction:
<code>$filter=Date ge '2026-01-01' and Date le '2026-12-31'</code>

'''Active records only:'''
<code>$filter=Status eq 'ACTIVE'</code>

'''Exclude archived:'''
<code>$filter=Status ne 'ARCHIVED'</code>

 

=== 4. Limit columns retrieved with <code>$select</code> ===

OData supports projection via <code>$select=Col1,Col2,...</code>. By default, every column on the entity is returned, including wide free-text fields that may never appear in your report. Restricting projection reduces row size and network transfer.

In Power Query, expand only the columns you need before any other step, and confirm the resulting native query includes a <code>$select</code> clause.

Example:
<code>$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

 
=== 5. Combining Filters ===

Stacked techniques decrease the Usage rapidly. Example combined filter + projection:

<code>$filter=Date ge '2026-06-01' and Status eq 'ACTIVE'&$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

Typically reduces egress by '''~80%''' vs. an unfiltered, all-columns extraction.

 

= Incremental Refreshing =

=== 6. Use Incremental Refresh for time-series data ===

For datasets keyed on a date, datetime, or datetimezone column, configure '''Incremental Refresh''' so that only new or changed partitions are pulled on each refresh.

* '''Power BI Premium / Fabric:''' native incremental refresh on semantic models.
* '''Power BI Pro:''' supported, but requires care to ensure the date filter folds. Test with '''View Native Query''' before publishing.
* '''Excel:''' use a date-threshold named range and filter the query URL on it.

<blockquote>'''Tip:''' If your organisation runs Microsoft Fabric, ask your data team about staging OdataLink feeds into a '''Dataflow Gen2''' (built on Power Query). Multiple reports can then consume the staged data without each one querying OdataLink directly. See [[How to Reduce my OdataLink Usage - For Power Users]].</blockquote>

 
= Monitoring =

Verify your optimisations in your Models Settings '''Manage Models → View → Usage tab''' for monitoring a specific model. Compare:
* Unfiltered request volume (GB) - your baseline before optimising
* Filtered request volume (GB) - after applying optimisation
* '''Reduction %''' = (Unfiltered Volume − Filtered Volume) ÷ Unfiltered Data Volume × 100

'''Target:''' 50–70% combined reduction from filtering + incremental refresh + column projection.

 

== Power Query checklist ==

* [ ] Date filters applied at the OData navigation step
* [ ] Verify Query folding
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets
* [ ] Refresh schedule matches actual report usage
* [ ] Considered Fabric Dataflow Gen2 if multiple reports share the same data

 

== 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 query transformations, and recommend further optimisations in attempts to reduce your account usage.

 

= Related Topics =

* [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
* [[How to Reduce my OdataLink Usage - For Everyday Users]]
* [[How to Reduce my OdataLink Usage - For Power Users]]
* [[OData Feed URLs]]
* [[Models]]
* [[Data Files]]
* [[How to track my OdataLink Usage]]
* [[Model Date Filters and Report Date Filters]]
* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]

How to Reduce my OdataLink Usage - For Advanced Users

2026-06-01T05:48:16Z

Ash:

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your reports or dashboards use Power Query transformations to clean and shape the dataset, the following optimisations techniques will reduce egress and improve refresh performance.

This help page is intended towards report developers working with '''M Language/Power Query'''. For developers using Fabric Data Pipelines, Azure Data Factory, SSIS, or persistent staging into Lakehouse/Warehouse/DataLake,
 see [[How to Reduce my OdataLink Usage - For Power Users]]

You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

= Query-Level Filtering =
This sections explains how applying a filter early in the transformation steps helps reduce egress. Query level filtering makes use of OdataLink feature that can pass the filters up directly to the data source. This results in OdataLink only downloading the filtered data out of your [[Data Files|data file(s)]].

=== 1. Push date filters up to the OData feed level (query folding) ===
Filtering data '''after''' it has been downloaded does not reduce egress since the records have already been pulled out from the company file.

Filtering must happen at the '''Navigation step''' in Power Query so the filter is sent to OdataLink as part of the OData query (<code>$filter=...</code>) and only the matching/filtered rows are returned.

# Apply date filters as early as possible, directly on the source table, before any merges, joins, or column expansions.
# Avoid applying <code>Table.SelectRows</code> over a downloaded table when an equivalent filter can be applied at the navigation step.


[[Model Date Filters and Report Date Filters| '''Reference -''' Model Date Filters vs Report Date Filters]]

 

=== 2. Apply supported OData filter operators ===

The following operators fold to the OData feed and execute server-side before any data leaves your data file:

{| class="wikitable"
! Operator !! Operation !! Example
|-
| <code>eq</code> || equal || <code>Status eq 'ACTIVE'</code>
|-
| <code>ne</code> || not equal || <code>Status ne 'ARCHIVED'</code>
|-
| <code>gt</code>, <code>lt</code> || greater / less than || <code>Date gt '2026-01-01'</code>
|-
| <code>ge</code>, <code>le</code> || greater / less than or equal || <code>Amount ge 1000</code>
|-
| <code>and</code> || logical AND || <code>Status eq 'ACTIVE' and Type eq 'BANK'</code>
|-
| <code>or</code> || logical OR || <code>Type eq 'BANK' or Type eq 'CREDIT_CARD'</code>
|-
| <code>startswith()</code> || prefix match || <code>startswith(Empname, 'Ia')</code>
|-
| <code>contains()</code> || substring match || <code>contains(GLCode, '5-01')</code>
|}

<blockquote>'''Note:''' <code>startswith()</code> and <code>contains()</code> work on text/string fields (such as Code, Name, Description) but may fail or produce unexpected results on date fields. Use comparison operators (<code>gt</code>, <code>lt</code>, <code>ge</code>, <code>le</code>) on those columns instead.</blockquote>

 

=== 3. Filter examples by use case ===

'''Date range''' — highest single-impact reduction:
<code>$filter=Date ge '2026-01-01' and Date le '2026-12-31'</code>

'''Active records only:'''
<code>$filter=Status eq 'ACTIVE'</code>

'''Exclude archived:'''
<code>$filter=Status ne 'ARCHIVED'</code>

 

=== 4. Limit columns retrieved with <code>$select</code> ===

OData supports projection via <code>$select=Col1,Col2,...</code>. By default, every column on the entity is returned, including wide free-text fields that may never appear in your report. Restricting projection reduces row size and network transfer.

In Power Query, expand only the columns you need before any other step, and confirm the resulting native query includes a <code>$select</code> clause.

Example:
<code>$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

 
=== 5. Combining Filters ===

Stacked techniques decrease the Usage rapidly. Example combined filter + projection:

<code>$filter=Date ge '2026-06-01' and Status eq 'ACTIVE'&$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

Typically reduces egress by '''~80%''' vs. an unfiltered, all-columns extraction.

 

= Incremental Refreshing =

=== 6. Use Incremental Refresh for time-series data ===

For datasets keyed on a date, datetime, or datetimezone column, configure '''Incremental Refresh''' so that only new or changed partitions are pulled on each refresh.

* '''Power BI Premium / Fabric:''' native incremental refresh on semantic models.
* '''Power BI Pro:''' supported, but requires care to ensure the date filter folds. Test with '''View Native Query''' before publishing.
* '''Excel:''' use a date-threshold named range and filter the query URL on it.

<blockquote>'''Tip:''' If your organisation runs Microsoft Fabric, ask your data team about staging OdataLink feeds into a '''Dataflow Gen2''' (built on Power Query). Multiple reports can then consume the staged data without each one querying OdataLink directly. See [[How to Reduce my OdataLink Usage - For Power Users]].</blockquote>

 
= Monitoring =

Verify your optimisations in your Models Settings '''Manage Models → View → Usage tab''' for monitoring a specific model. Compare:
* Unfiltered request volume (GB) - your baseline before optimising
* Filtered request volume (GB) - after applying optimisation
* '''Reduction %''' = (Unfiltered Volume − Filtered Volume) ÷ Unfiltered Data Volume × 100

'''Target:''' 50–70% combined reduction from filtering + incremental refresh + column projection.

 

== Power Query checklist ==

* [ ] Date filters applied at the OData navigation step
* [ ] Verify Query folding
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets
* [ ] Refresh schedule matches actual report usage
* [ ] Considered Fabric Dataflow Gen2 if multiple reports share the same data

 

== 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 query transformations, and recommend further optimisations in attempts to reduce your account usage.

 

= Related Topics =

* [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
* [[How to Reduce my OdataLink Usage - For Everyday Users]]
* [[How to Reduce my OdataLink Usage - For Power Users]]
* [[OData Feed URLs]]
* [[Models]]
* [[Data Files]]
* [[How to track my OdataLink Usage]]
* [[Model Date Filters and Report Date Filters]]
* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]

How to Reduce my OdataLink Usage - For Advanced Users

2026-06-01T05:47:53Z

Ash: correction: use comparator operators for date and similar fields

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your reports or dashboards use Power Query transformations to clean and shape the dataset, the following optimisations techniques will reduce egress and improve refresh performance.

This help page is intended towards report developers working with '''M Language/Power Query'''. For developers using Fabric Data Pipelines, Azure Data Factory, SSIS, or persistent staging into Lakehouse/Warehouse/DataLake,
 see [[How to Reduce my OdataLink Usage - For Power Users]]

You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

= Query-Level Filtering =
This sections explains how applying a filter early in the transformation steps helps reduce egress. Query level filtering makes use of OdataLink feature that can pass the filters up directly to the data source. This results in OdataLink only downloading the filtered data out of your [[Data Files|data file(s)]].

=== 1. Push date filters up to the OData feed level (query folding) ===
Filtering data '''after''' it has been downloaded does not reduce egress since the records have already been pulled out from the company file.

Filtering must happen at the '''Navigation step''' in Power Query so the filter is sent to OdataLink as part of the OData query (<code>$filter=...</code>) and only the matching/filtered rows are returned.

# Apply date filters as early as possible, directly on the source table, before any merges, joins, or column expansions.
# Avoid applying <code>Table.SelectRows</code> over a downloaded table when an equivalent filter can be applied at the navigation step.


[[Model Date Filters and Report Date Filters| '''Reference -''' Model Date Filters vs Report Date Filters]]

 

=== 2. Apply supported OData filter operators ===

The following operators fold to the OData feed and execute server-side before any data leaves your data file:

{| class="wikitable"
! Operator !! Operation !! Example
|-
| <code>eq</code> || equal || <code>Status eq 'ACTIVE'</code>
|-
| <code>ne</code> || not equal || <code>Status ne 'ARCHIVED'</code>
|-
| <code>gt</code>, <code>lt</code> || greater / less than || <code>Date gt '2026-01-01'</code>
|-
| <code>ge</code>, <code>le</code> || greater / less than or equal || <code>Amount ge 1000</code>
|-
| <code>and</code> || logical AND || <code>Status eq 'ACTIVE' and Type eq 'BANK'</code>
|-
| <code>or</code> || logical OR || <code>Type eq 'BANK' or Type eq 'CREDIT_CARD'</code>
|-
| <code>startswith()</code> || prefix match || <code>startswith(Empname, 'Ia')</code>
|-
| <code>contains()</code> || substring match || <code>contains(GLCode, '5-01')</code>
|}

<blockquote>'''Note:''' <code>startswith()</code> and <code>contains()</code> work on text/string fields (such as Code, Name, Description) but may fail or produce unexpected results on date fields . Use comparison operators (<code>gt</code>, <code>lt</code>, <code>ge</code>, <code>le</code>) on those columns instead.</blockquote>

 

=== 3. Filter examples by use case ===

'''Date range''' — highest single-impact reduction:
<code>$filter=Date ge '2026-01-01' and Date le '2026-12-31'</code>

'''Active records only:'''
<code>$filter=Status eq 'ACTIVE'</code>

'''Exclude archived:'''
<code>$filter=Status ne 'ARCHIVED'</code>

 

=== 4. Limit columns retrieved with <code>$select</code> ===

OData supports projection via <code>$select=Col1,Col2,...</code>. By default, every column on the entity is returned, including wide free-text fields that may never appear in your report. Restricting projection reduces row size and network transfer.

In Power Query, expand only the columns you need before any other step, and confirm the resulting native query includes a <code>$select</code> clause.

Example:
<code>$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

 
=== 5. Combining Filters ===

Stacked techniques decrease the Usage rapidly. Example combined filter + projection:

<code>$filter=Date ge '2026-06-01' and Status eq 'ACTIVE'&$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

Typically reduces egress by '''~80%''' vs. an unfiltered, all-columns extraction.

 

= Incremental Refreshing =

=== 6. Use Incremental Refresh for time-series data ===

For datasets keyed on a date, datetime, or datetimezone column, configure '''Incremental Refresh''' so that only new or changed partitions are pulled on each refresh.

* '''Power BI Premium / Fabric:''' native incremental refresh on semantic models.
* '''Power BI Pro:''' supported, but requires care to ensure the date filter folds. Test with '''View Native Query''' before publishing.
* '''Excel:''' use a date-threshold named range and filter the query URL on it.

<blockquote>'''Tip:''' If your organisation runs Microsoft Fabric, ask your data team about staging OdataLink feeds into a '''Dataflow Gen2''' (built on Power Query). Multiple reports can then consume the staged data without each one querying OdataLink directly. See [[How to Reduce my OdataLink Usage - For Power Users]].</blockquote>

 
= Monitoring =

Verify your optimisations in your Models Settings '''Manage Models → View → Usage tab''' for monitoring a specific model. Compare:
* Unfiltered request volume (GB) - your baseline before optimising
* Filtered request volume (GB) - after applying optimisation
* '''Reduction %''' = (Unfiltered Volume − Filtered Volume) ÷ Unfiltered Data Volume × 100

'''Target:''' 50–70% combined reduction from filtering + incremental refresh + column projection.

 

== Power Query checklist ==

* [ ] Date filters applied at the OData navigation step
* [ ] Verify Query folding
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets
* [ ] Refresh schedule matches actual report usage
* [ ] Considered Fabric Dataflow Gen2 if multiple reports share the same data

 

== 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 query transformations, and recommend further optimisations in attempts to reduce your account usage.

 

= Related Topics =

* [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
* [[How to Reduce my OdataLink Usage - For Everyday Users]]
* [[How to Reduce my OdataLink Usage - For Power Users]]
* [[OData Feed URLs]]
* [[Models]]
* [[Data Files]]
* [[How to track my OdataLink Usage]]
* [[Model Date Filters and Report Date Filters]]
* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]

How to Reduce my OdataLink Usage - For Advanced Users

2026-06-01T05:36:04Z

Ash:

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your reports or dashboards use Power Query transformations to clean and shape the dataset, the following optimisations techniques will reduce egress and improve refresh performance.

This help page is intended towards report developers working with '''M Language/Power Query'''. For developers using Fabric Data Pipelines, Azure Data Factory, SSIS, or persistent staging into Lakehouse/Warehouse/DataLake,
 see [[How to Reduce my OdataLink Usage - For Power Users]]

You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

= Query-Level Filtering =
This sections explains how applying a filter early in the transformation steps helps reduce egress. Query level filtering makes use of OdataLink feature that can pass the filters up directly to the data source. This results in OdataLink only downloading the filtered data out of your [[Data Files|data file(s)]].

=== 1. Push date filters up to the OData feed level (query folding) ===
Filtering data '''after''' it has been downloaded does not reduce egress since the records have already been pulled out from the company file.

Filtering must happen at the '''Navigation step''' in Power Query so the filter is sent to OdataLink as part of the OData query (<code>$filter=...</code>) and only the matching/filtered rows are returned.

# Apply date filters as early as possible, directly on the source table, before any merges, joins, or column expansions.
# Avoid applying <code>Table.SelectRows</code> over a downloaded table when an equivalent filter can be applied at the navigation step.


[[Model Date Filters and Report Date Filters| '''Reference -''' Model Date Filters vs Report Date Filters]]

 

=== 2. Apply supported OData filter operators ===

The following operators fold to the OData feed and execute server-side before any data leaves your data file:

{| class="wikitable"
! Operator !! Operation !! Example
|-
| <code>eq</code> || equal || <code>Status eq 'ACTIVE'</code>
|-
| <code>ne</code> || not equal || <code>Status ne 'ARCHIVED'</code>
|-
| <code>gt</code>, <code>lt</code> || greater / less than || <code>Date gt '2026-01-01'</code>
|-
| <code>ge</code>, <code>le</code> || greater / less than or equal || <code>Amount ge 1000</code>
|-
| <code>and</code> || logical AND || <code>Status eq 'ACTIVE' and Type eq 'BANK'</code>
|-
| <code>or</code> || logical OR || <code>Type eq 'BANK' or Type eq 'CREDIT_CARD'</code>
|-
| <code>startswith()</code> || prefix match || <code>startswith(Empname, 'Ia')</code>
|-
| <code>contains()</code> || substring match || <code>contains(GLCode, '5-01')</code>
|}

<blockquote>'''Note:''' <code>startswith()</code> and <code>contains()</code> are reliable on date string columns but may fail on free-text fields (Code, Name, Description). Use comparison operators on those columns instead.</blockquote>

 

=== 3. Filter examples by use case ===

'''Date range''' — highest single-impact reduction:
<code>$filter=Date ge '2026-01-01' and Date le '2026-12-31'</code>

'''Active records only:'''
<code>$filter=Status eq 'ACTIVE'</code>

'''Exclude archived:'''
<code>$filter=Status ne 'ARCHIVED'</code>

 

=== 4. Limit columns retrieved with <code>$select</code> ===

OData supports projection via <code>$select=Col1,Col2,...</code>. By default, every column on the entity is returned, including wide free-text fields that may never appear in your report. Restricting projection reduces row size and network transfer.

In Power Query, expand only the columns you need before any other step, and confirm the resulting native query includes a <code>$select</code> clause.

Example:
<code>$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

 
=== 5. Combining Filters ===

Stacked techniques decrease the Usage rapidly. Example combined filter + projection:

<code>$filter=Date ge '2026-06-01' and Status eq 'ACTIVE'&$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

Typically reduces egress by '''~80%''' vs. an unfiltered, all-columns extraction.

 

= Incremental Refreshing =

=== 6. Use Incremental Refresh for time-series data ===

For datasets keyed on a date, datetime, or datetimezone column, configure '''Incremental Refresh''' so that only new or changed partitions are pulled on each refresh.

* '''Power BI Premium / Fabric:''' native incremental refresh on semantic models.
* '''Power BI Pro:''' supported, but requires care to ensure the date filter folds. Test with '''View Native Query''' before publishing.
* '''Excel:''' use a date-threshold named range and filter the query URL on it.

<blockquote>'''Tip:''' If your organisation runs Microsoft Fabric, ask your data team about staging OdataLink feeds into a '''Dataflow Gen2''' (built on Power Query). Multiple reports can then consume the staged data without each one querying OdataLink directly. See [[How to Reduce my OdataLink Usage - For Power Users]].</blockquote>

 
= Monitoring =

Verify your optimisations in your Models Settings '''Manage Models → View → Usage tab''' for monitoring a specific model. Compare:
* Unfiltered request volume (GB) - your baseline before optimising
* Filtered request volume (GB) - after applying optimisation
* '''Reduction %''' = (Unfiltered Volume − Filtered Volume) ÷ Unfiltered Data Volume × 100

'''Target:''' 50–70% combined reduction from filtering + incremental refresh + column projection.

 

== Power Query checklist ==

* [ ] Date filters applied at the OData navigation step
* [ ] Verify Query folding
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets
* [ ] Refresh schedule matches actual report usage
* [ ] Considered Fabric Dataflow Gen2 if multiple reports share the same data

 

== 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 query transformations, and recommend further optimisations in attempts to reduce your account usage.

 

= Related Topics =

* [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
* [[How to Reduce my OdataLink Usage - For Everyday Users]]
* [[How to Reduce my OdataLink Usage - For Power Users]]
* [[OData Feed URLs]]
* [[Models]]
* [[Data Files]]
* [[How to track my OdataLink Usage]]
* [[Model Date Filters and Report Date Filters]]
* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]

How to Reduce my OdataLink Usage - For Power Users

2026-06-01T05:34:20Z

Ash:

How to track my OdataLink Usage

2026-06-01T05:24:17Z

Ash:

= Overview =
Each OdataLink account is subject to Monthly Data download allowance under OdataLink's [https://odatalink.com/fair-use-policy '''Fair Use Policy (Policy)'''] document.
Usage is shared across entire account and data files, so higher activity on one data file can be offset bu lower activity on other(s).
OdataLink tracks data usage at the Account level, Models level, and per data file level, to help you stay within your Monthly Data Allowance limits.

= Usage =
[[File:OdataLink_Account_Usage.png|400px|thumb|right|Account Usage]]

{{:Reducing Data Usage and Optimising your OdataLink Feeds}}
=== What does not count as Usage ===
* Data retrieved from OdataLink's archive (historical cached data).
* Data retrieved from data sources other than connected Data Files
[[File:OdataLink_Model_Usage.png|400px|thumb|right|Model Usage]]
[[File:OdataLink_Data_File_Usage.png|400px|thumb|right|Data File Usage]]

== Where can I find my Usage ==
Login to your '''OdataLink Admin portal''' and according to each level, navigate to the appropriate '''Usage''' tab as explained below:

=== 1. Account Level Usage ===
# Click on '''Account/Subscription''' Settings on the left side panel.
# '''View Account''' page will be loaded.
# A graph under '''Usage Stats''' will be displayed indicating your '''Account's Total Usage''' for the period as selected in the '''Month''' dropdown field.

=== 2. Model Level Usage ===
# Click on '''Models''' on the left side panel to view all Models.
# Click on the Model Name or the '''View''' button corresponding to the model.
# Click on '''Usage''' tab and a graph will be shown indicating your '''Model's Total Usage''' for the period as selected in the '''Month''' dropdown field.

=== 3. Data File Level Usage ===
# Click on '''Data Files''' on the left side panel to view all Data Files connected to your account.
# Click on the Entity/Company/Data File Name or the '''View''' button corresponding to the Data File.
# Click on '''Usage''' tab and a graph will be shown indicating your '''Data File's Total Usage''' for the period as selected in the '''Month''' dropdown field.

= How to Reduce my Usage =
If your Usage is high or is approaching the Monthly Data Allowance limits, see [[Reducing Data Usage and Optimising your OdataLink Feeds]]

The help page above contains guides for:
* Everyday Users - To optimise at OdataLink level
* Advanced Users - To apply optimisations at report and data query level
* Power Users - To apply optimisations at staging levels and pipeline levels, custom solutions apply

= Related Topics =
: [https://odatalink.com/fair-use-policy Fair Use Policy]
: [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]

Reducing Data Usage and Optimising your OdataLink Feeds

2026-06-01T05:18:23Z

Ash:

= 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)]].

The guidance below applies to all models connecting to Xero, Xero Practice Manager, MYOB and QuickBooks. Following these steps will:

* Reduce your monthly download volume; and
* Improve refresh performance

You can monitor your current usage at any time in your '''OdataLink Admin Portal → Usage Stats'''.

See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

== What is Usage ==
<onlyinclude>
'''Usage''' is defined as the volume of data OdataLink retrieves from your connected [[Data Files|data files]] each month.

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.

{| 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
|}

<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>

 

== 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
* [ ] Weekend / out-of-hours refreshes disabled where not needed
* [ ] Archive Type set to '''Live (with Archive)''' on the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled

'''Advanced Users:'''
* [ ] Date filters applied at the OData navigation step (not post-download)
* [ ] Query folding verified via '''View Native Query'''
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets

'''Azure/Fabric/Data Pipeline developers:'''
* [ ] 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
* [ ] Both OdataLink egress and Azure / Fabric compute cost monitored

 

== 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 to bring your account usage within limits.

 

== Related Topics ==

* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]
* [[How to track my OdataLink Usage]]
* [[OData Feed URLs]]

Reducing Data Usage and Optimising your OdataLink Feeds

2026-06-01T05:18:06Z

Ash:

= 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)]].

The guidance below applies to all models connecting to Xero, Xero Practice Manager, MYOB and QuickBooks. Following these steps will:

* Reduce your monthly download volume; and
* Improve refresh performance

You can monitor your current usage at any time in your '''OdataLink Admin Portal → Usage Stats'''.

See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

== What is Usage ==
<onlyinclude>
'''Usage''' is defined as the volume of data OdataLink retrieves from your connected [[Data Files|data files]] each month.

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.

{| 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
|}

<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 changes.</blockquote>

 

== 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
* [ ] Weekend / out-of-hours refreshes disabled where not needed
* [ ] Archive Type set to '''Live (with Archive)''' on the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled

'''Advanced Users:'''
* [ ] Date filters applied at the OData navigation step (not post-download)
* [ ] Query folding verified via '''View Native Query'''
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets

'''Azure/Fabric/Data Pipeline developers:'''
* [ ] 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
* [ ] Both OdataLink egress and Azure / Fabric compute cost monitored

 

== 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 to bring your account usage within limits.

 

== Related Topics ==

* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]
* [[How to track my OdataLink Usage]]
* [[OData Feed URLs]]

How to track my OdataLink Usage

2026-05-28T06:30:42Z

Ash:

= Overview =
Each OdataLink account is subject to Monthly Data download allowance under OdataLink's [https://odatalink.com/fair-use-policy '''Fair Use Policy (Policy)'''] document.
Usage is shared across entire account and data files, so higher activity on one data file can be offset bu lower activity on other(s).
OdataLink tracks data usage at the Account level, Models level, and per data file level, to help you stay within your Monthly Data Allowance limits.

= Usage =
[[File:OdataLink_Account_Usage.png|400px|thumb|right|Account Usage]]

{{:Reducing Data Usage and Optimising your OdataLink Feeds}}
=== What does not count as Usage ===
* Data retrieved from OdataLink's archive (historical cached data).
* Data retrieved from data sources other than connected Data Files
[[File:OdataLink_Model_Usage.png|400px|thumb|right|Model Usage]]
[[File:OdataLink_Data_File_Usage.png|400px|thumb|right|Data File Usage]]

= Where can I find my Usage =
Login to your '''OdataLink Admin portal''' and according to each level, navigate to the appropriate '''Usage''' tab as explained below:

== 1. Account Level Usage ==
# Click on '''Account/Subscription''' Settings on the left side panel.
# '''View Account''' page will be loaded.
# A graph under '''Usage Stats''' will be displayed indicating your '''Account's Total Usage''' for the period as selected in the '''Month''' dropdown field.

== 2. Model Level Usage ==
# Click on '''Models''' on the left side panel to view all Models.
# Click on the Model Name or the '''View''' button corresponding to the model.
# Click on '''Usage''' tab and a graph will be shown indicating your '''Model's Total Usage''' for the period as selected in the '''Month''' dropdown field.

== 3. Data File Level Usage ==
# Click on '''Data Files''' on the left side panel to view all Data Files connected to your account.
# Click on the Entity/Company/Data File Name or the '''View''' button corresponding to the Data File.
# Click on '''Usage''' tab and a graph will be shown indicating your '''Data File's Total Usage''' for the period as selected in the '''Month''' dropdown field.

= How to Reduce my Usage =
If your Usage is high or is approaching the Monthly Data Allowance limits, see [[Reducing Data Usage and Optimising your OdataLink Feeds]]

The help page above contains guides for:
* Everyday Users - To optimise at OdataLink level
* Advanced Users - To apply optimisations at report and data query level
* Power Users - To apply optimisations at staging levels and pipeline levels, custom solutions apply

= Related Topics =
: [https://odatalink.com/fair-use-policy Fair Use Policy]
: [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]

How to track my OdataLink Usage

2026-05-28T06:27:10Z

Ash: Created page with "= Overview = Each OdataLink account is subject to Monthly Data download allowance under OdataLink's [https://odatalink.com/fair-use-policy '''Fair Use Policy (Policy)'''] docu..."

= Overview =
Each OdataLink account is subject to Monthly Data download allowance under OdataLink's [https://odatalink.com/fair-use-policy '''Fair Use Policy (Policy)'''] document.
Usage is shared across entire account and data files, so higher activity on one data file can be offset bu lower activity on other(s).
OdataLink tracks data usage at the Account level, Models level, and per data file level, to help you stay within your Monthly Data Allowance limits.

== What counts as Usage ==
{{:Reducing Data Usage and Optimising your OdataLink Feeds}}

=== What does not count as Usage ===
* Data retrieved from OdataLink's archive (historical cached data).
* Data retrieved from data sources other than connected Data Files
[[File:OdataLink_Account_Usage.png|400px|thumb|right|Account Usage]]
== Where can I find my Usage ==
Login to your '''OdataLink Admin portal''' and according to each level, navigate to the appropriate Usage stats tab as explained below:
[[File:OdataLink_Model_Usage.png|400px|thumb|right|Model Usage]]
[[File:OdataLink_Data_File_Usage.png|400px|thumb|right|Data File Usage]]
=== 1. Account Level Usage ===
# Click on '''Account/Subscription''' Settings on the left side panel.
# '''View Account''' page will be loaded.
# A graph under '''Usage Stats''' will be displayed indicating your '''Account's Total Usage''' for the period as selected in the '''Month''' dropdown field.

=== 2. Model Level Usage ===
# Click on '''Models''' on the left side panel to view all Models.
# Click on the Model Name or the '''View''' button corresponding to the model.
# Click on '''Usage''' tab and a graph will be shown indicating your '''Model's Total Usage''' for the period as selected in the '''Month''' dropdown field.

=== 3. Data File Level Usage ===
# Click on '''Data Files''' on the left side panel to view all Data Files connected to your account.
# Click on the Entity/Company/Data File Name or the '''View''' button corresponding to the Data File.
# Click on '''Usage''' tab and a graph will be shown indicating your '''Data File's Total Usage''' for the period as selected in the '''Month''' dropdown field.

== How to Reduce my Usage ==
If your Usage is high or is approaching the Monthly Data Allowance limits, see [[Reducing Data Usage and Optimising your OdataLink Feeds]]

The help page above contains guides for:
* Everyday Users - To optimise at OdataLink level
* Advanced Users - To apply optimisations at report and data query level
* Power Users - To apply optimisations at staging levels and pipeline levels, custom solutions apply

= Related Topics =
: [https://odatalink.com/fair-use-policy Fair Use Policy]
: [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]

How to Reduce my OdataLink Usage - For Everyday Users

2026-05-28T06:25:01Z

Ash:

File:OdataLink Data File Usage.png

2026-05-28T06:15:33Z

Ash:

File:OdataLink Model Usage.png

2026-05-28T06:14:16Z

Ash:

File:OdataLink Account Usage.png

2026-05-28T06:12:31Z

Ash:

Reducing Data Usage and Optimising your OdataLink Feeds

2026-05-28T05:59:09Z

Ash: Added onlyinclude for tranclusion into other wiki pages

= 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)]].

The guidance below applies to all models connecting to Xero, Xero Practice Manager, MYOB and QuickBooks. Following these steps will:

* Reduce your monthly download volume; and
* Improve refresh performance

You can monitor your current usage at any time in your '''OdataLink Admin Portal → Usage Stats'''.

See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

== What is Usage ==
<onlyinclude>
'''Usage''' is defined as the volume of data OdataLink retrieves from your connected [[Data Files|data files]] each month.

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.

{| 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
|}

<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>

 

== 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
* [ ] Weekend / out-of-hours refreshes disabled where not needed
* [ ] Archive Type set to '''Live (with Archive)''' on the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled

'''Advanced Users:'''
* [ ] Date filters applied at the OData navigation step (not post-download)
* [ ] Query folding verified via '''View Native Query'''
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets

'''Azure/Fabric/Data Pipeline developers:'''
* [ ] 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
* [ ] Both OdataLink egress and Azure / Fabric compute cost monitored

 

== 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 to bring your account usage within limits.

 

== Related Topics ==

* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]
* [[How to track my OdataLink Usage]]
* [[OData Feed URLs]]

Reducing Data Usage and Optimising your OdataLink Feeds

2026-05-28T05:58:14Z

Ash:

= 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)]].

The guidance below applies to all models connecting to Xero, Xero Practice Manager, MYOB and QuickBooks. Following these steps will:

* Reduce your monthly download volume; and
* Improve refresh performance

You can monitor your current usage at any time in your '''OdataLink Admin Portal → Usage Stats'''.

See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 
<onlyinclude>
== What is Usage ==

'''Usage''' is defined as the volume of data OdataLink retrieves from your connected [[Data Files|data files]] each month.

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.

{| 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
|}

<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>

 

== 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
* [ ] Weekend / out-of-hours refreshes disabled where not needed
* [ ] Archive Type set to '''Live (with Archive)''' on the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled

'''Advanced Users:'''
* [ ] Date filters applied at the OData navigation step (not post-download)
* [ ] Query folding verified via '''View Native Query'''
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets

'''Azure/Fabric/Data Pipeline developers:'''
* [ ] 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
* [ ] Both OdataLink egress and Azure / Fabric compute cost monitored

 

== 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 to bring your account usage within limits.

 

== Related Topics ==

* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]
* [[How to track my OdataLink Usage]]
* [[OData Feed URLs]]

Reducing Data Usage and Optimising your OdataLink Feeds

2026-05-28T05:53:28Z

Ash:

= 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)]].

The guidance below applies to all models connecting to Xero, Xero Practice Manager, MYOB and QuickBooks. Following these steps will:

* Reduce your monthly download volume; and
* Improve refresh performance

You can monitor your current usage at any time in your '''OdataLink Admin Portal → Usage Stats'''.

See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

== What is Usage ==

'''Usage''' is defined as the volume of data OdataLink retrieves from your connected [[Data Files|data files]] each month.

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 Legacy/Gen2) || Stage-once-serve-many, watermarked incremental loads, historical backfill strategy, scheduling discipline, end-to-end cost monitoring
|}

<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>

 

== 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
* [ ] Weekend / out-of-hours refreshes disabled where not needed
* [ ] Archive Type set to '''Live (with Archive)''' on the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled

'''Advanced Users:'''
* [ ] Date filters applied at the OData navigation step (not post-download)
* [ ] Query folding verified via '''View Native Query'''
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets

'''Azure/Fabric/Data Pipeline developers:'''
* [ ] 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
* [ ] Both OdataLink egress and Azure / Fabric compute cost monitored

 

== 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 to bring your account usage within limits.

 

== Related Topics ==

* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]
* [[How to track my OdataLink Usage]]
* [[OData Feed URLs]]

Reducing Data Usage and Optimising your OdataLink Feeds

2026-05-28T05:49:29Z

Ash:

= 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)]].

The guidance below applies to all models connecting to Xero, Xero Practice Manager, MYOB and QuickBooks. Following these steps will:

* Reduce your monthly download volume; and
* Improve refresh performance

You can monitor your current usage at any time in your '''OdataLink Admin Portal → Usage Stats'''.

See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

== What is Usage ==
<section begin=usage />
'''Usage''' is defined as the volume of data OdataLink retrieves from your connected [[Data Files|data files]] each month.

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.
<section end=usage />
= 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 Legacy/Gen2) || Stage-once-serve-many, watermarked incremental loads, historical backfill strategy, scheduling discipline, end-to-end cost monitoring
|}

<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>

 

== 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
* [ ] Weekend / out-of-hours refreshes disabled where not needed
* [ ] Archive Type set to '''Live (with Archive)''' on the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled

'''Advanced Users:'''
* [ ] Date filters applied at the OData navigation step (not post-download)
* [ ] Query folding verified via '''View Native Query'''
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets

'''Azure/Fabric/Data Pipeline developers:'''
* [ ] 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
* [ ] Both OdataLink egress and Azure / Fabric compute cost monitored

 

== 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 to bring your account usage within limits.

 

== Related Topics ==

* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]
* [[How to track my OdataLink Usage]]
* [[OData Feed URLs]]

How to Reduce my OdataLink Usage - For Advanced Users

2026-05-28T05:29:14Z

Ash:

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your reports or dashboards use Power Query transformations to clean and shape the dataset, the following optimisations techniques will reduce egress and improve refresh performance.

This help page is intended towards report developers working with '''M Language/Power Query'''. For developers using Fabric Data Pipelines, Azure Data Factory, SSIS, or persistent staging into Lakehouse/Warehouse/DataLake,
 see [[How to Reduce my OdataLink Usage - For Power Users]]

You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

= Query-Level Filtering =
This sections explains how applying a filter early in the transformation steps helps reduce egress. Query level filtering makes use of OdataLink feature that can pass the filters up directly to the data source. This results in OdataLink only downloading the filtered data out of your [[Data Files|data file(s)]].

== 1. Push date filters up to the OData feed level (query folding) ==
Filtering data '''after''' it has been downloaded does not reduce egress since the records have already been pulled out from the company file.

Filtering must happen at the '''Navigation step''' in Power Query so the filter is sent to OdataLink as part of the OData query (<code>$filter=...</code>) and only the matching/filtered rows are returned.

# Apply date filters as early as possible, directly on the source table, before any merges, joins, or column expansions.
# Avoid applying <code>Table.SelectRows</code> over a downloaded table when an equivalent filter can be applied at the navigation step.


[[Model Date Filters and Report Date Filters| '''Reference -''' Model Date Filters vs Report Date Filters]]

 

== 2. Apply supported OData filter operators ==

The following operators fold to the OData feed and execute server-side before any data leaves your data file:

{| class="wikitable"
! Operator !! Operation !! Example
|-
| <code>eq</code> || equal || <code>Status eq 'ACTIVE'</code>
|-
| <code>ne</code> || not equal || <code>Status ne 'ARCHIVED'</code>
|-
| <code>gt</code>, <code>lt</code> || greater / less than || <code>Date gt '2026-01-01'</code>
|-
| <code>ge</code>, <code>le</code> || greater / less than or equal || <code>Amount ge 1000</code>
|-
| <code>and</code> || logical AND || <code>Status eq 'ACTIVE' and Type eq 'BANK'</code>
|-
| <code>or</code> || logical OR || <code>Type eq 'BANK' or Type eq 'CREDIT_CARD'</code>
|-
| <code>startswith()</code> || prefix match || <code>startswith(Empname, 'Ia')</code>
|-
| <code>contains()</code> || substring match || <code>contains(GLCode, '5-01')</code>
|}

<blockquote>'''Note:''' <code>startswith()</code> and <code>contains()</code> are reliable on date string columns but may fail on free-text fields (Code, Name, Description). Use comparison operators on those columns instead.</blockquote>

 

== 3. Filter examples by use case ==

'''Date range''' — highest single-impact reduction:
<code>$filter=Date ge '2026-01-01' and Date le '2026-12-31'</code>

'''Active records only:'''
<code>$filter=Status eq 'ACTIVE'</code>

'''Exclude archived:'''
<code>$filter=Status ne 'ARCHIVED'</code>

 

== 4. Limit columns retrieved with <code>$select</code> ==

OData supports projection via <code>$select=Col1,Col2,...</code>. By default, every column on the entity is returned, including wide free-text fields that may never appear in your report. Restricting projection reduces row size and network transfer.

In Power Query, expand only the columns you need before any other step, and confirm the resulting native query includes a <code>$select</code> clause.

Example:
<code>$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

 
== 5. Combining Filters ==

Stacked techniques decrease the Usage rapidly. Example combined filter + projection:

<code>$filter=Date ge '2026-06-01' and Status eq 'ACTIVE'&$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

Typically reduces egress by '''~80%''' vs. an unfiltered, all-columns extraction.

 

= Incremental Refreshing =

== 6. Use Incremental Refresh for time-series data ==

For datasets keyed on a date, datetime, or datetimezone column, configure '''Incremental Refresh''' so that only new or changed partitions are pulled on each refresh.

* '''Power BI Premium / Fabric:''' native incremental refresh on semantic models.
* '''Power BI Pro:''' supported, but requires care to ensure the date filter folds. Test with '''View Native Query''' before publishing.
* '''Excel:''' use a date-threshold named range and filter the query URL on it.

<blockquote>'''Tip:''' If your organisation runs Microsoft Fabric, ask your data team about staging OdataLink feeds into a '''Dataflow Gen2''' (built on Power Query). Multiple reports can then consume the staged data without each one querying OdataLink directly. See [[How to Reduce my OdataLink Usage - For Power Users]].</blockquote>

 
= Monitoring =

Verify your optimisations in your Models Settings '''Manage Models → View → Usage tab''' for monitoring a specific model. Compare:
* Unfiltered request volume (GB) - your baseline before optimising
* Filtered request volume (GB) - after applying optimisation
* '''Reduction %''' = (Unfiltered Volume − Filtered Volume) ÷ Unfiltered Data Volume × 100

'''Target:''' 50–70% combined reduction from filtering + incremental refresh + column projection.

 

== Power Query checklist ==

* [ ] Date filters applied at the OData navigation step
* [ ] Verify Query folding
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets
* [ ] Refresh schedule matches actual report usage
* [ ] Considered Fabric Dataflow Gen2 if multiple reports share the same data

 

== 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 query transformations, and recommend further optimisations in attempts to reduce your account usage.

 

= Related Topics =

* [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
* [[How to Reduce my OdataLink Usage - For Everyday Users]]
* [[How to Reduce my OdataLink Usage - For Power Users]]
* [[OData Feed URLs]]
* [[Models]]
* [[Data Files]]
* [[How to track my OdataLink Usage]]
* [[Model Date Filters and Report Date Filters]]
* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]

How to Reduce my OdataLink Usage - For Everyday Users

2026-05-28T05:26:55Z

Ash:

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your account's connected [[Data Files|data file(s)]] are frequently approaching or exceeding monthly limits, the following techniques will help reduce usage with minimal impact to your reporting functionality.

The following optimisations can be applied directly in your '''OdataLink Admin Portal''' or in your reporting software.


You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].


= Refresh Schedule =

=== 1. Reduce Automatic Refresh frequency ===
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.

* Match the refresh frequency to how often you actually look at the report.
* 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.
<blockquote>'''Tip:''' Refreshing a sales dashboard hourly when the team only checks it on Monday mornings can use 24× more data than needed. Drop to daily, or weekly, and you may reduce that report's usage by 95%+.</blockquote>

 

= Model Settings =
The settings below are configured once per [[Models|model]] in your '''OdataLink Portal''' and applies to '''every refresh''' request.

=== 2. Apply a Cut-off Date on your model ===

OdataLink provides the ability to archive historical data to avoid re-downloading of same data on every refresh. The '''Cut-Off Date''' instructs OdataLink to save data up to the cut-off date and serve future refreshes from that archive.

Setting a cut-off date in your model dramatically reduces the volume retrieved on every refresh by avoiding downloading the same historical data multiple times.

To set a cut off date and enable archiving in your model:
# '''Archive Type:''' Set the [[Archive Types|Archive Type]] of your model to '''Live (with Archive)''' to enable archiving.
# '''Archive Cut-Off Date:''' Set either a fixed cut-off date, or a '''rolling window''' that continuously archives data older than X months or X days.


<blockquote>'''Tip:''' OdataLink recommends a rolling window so new data is automatically archived as it ages. You only configure it once.</blockquote>

[https://odatalink.com/2025-03-increase-performance-of-your-odatalink-odata-feeds-using-the-cut-off-date-settings/ How to apply and use the Cut-off Date setting]

=== 3. Set a Default Date Range on your model ===

OdataLink provides the ability to define a default date range for a model. This allows users to control the amount of data that gets downloaded from the connected [[Data Files|data file(s)]].

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.

[[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 been added or changed in your [[Data Files|data file]], rather than re-downloading the whole dataset on every refresh.

Polling is particularly effective for high-volume transactional entities like Invoices, Bills, and Bank Transactions.

[[Configure OdataLink to poll for changes|How to configure polling]]

 

=Monitoring =

Check your Monthly Usage in your OdataLink Portal '''Account/Subscription settings → Usage Stats'''.

'''Healthy pattern:'''
* Consistent month-to-month (no large spikes)
* 20-30% under your monthly allowance
* No overuse warnings

'''Excessive Usage pattern:'''
* Usage increasing or consistently above 100% month-over-month
* Multiple Usage alerts from OdataLink
* Exceeding allowance for more than 1 month

 

== Checklist ==

* [ ] Refresh frequency matches how often the report is actually viewed
* [ ] Weekend and off-hours refreshes disabled if not needed
* [ ] Archive Type set to '''Live (with Archive)''' for the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled
* [ ] Monthly usage reviewed in OdataLink Admin Portal

 

== 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 query transformations, and recommend further optimisations  in order to bring your account usage within limits.

 

= Related Topics =
: [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
: [[How to Reduce my OdataLink Usage - For Advanced Users]]
: [[How to Reduce my OdataLink Usage - For Power Users]]
: [[Reducing Data Usage and Optimising your OdataLink Feeds]]
: [https://odatalink.com/fair-use-policy Fair Use Policy]

How to Reduce my OdataLink Usage - For Everyday Users

2026-05-28T05:24:23Z

Ash:

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your account's connected [[Data Files|data file(s)]] are frequently approaching or exceeding monthly limits, the following techniques will help reduce usage with minimal impact to your reporting functionality.

The following optimisations can be applied directly in your '''OdataLink Admin Portal''' or in your reporting software.


You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].


= Refresh Schedule =

=== 1. Reduce Automatic Refresh frequency ===
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.

* Match the refresh frequency to how often you actually look at the report.
* 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.
<blockquote>'''Tip:''' Refreshing a sales dashboard hourly when the team only checks it on Monday mornings can use 24× more data than needed. Drop to daily, or weekly, and you may reduce that report's usage by 95%+.</blockquote>

 

= Model Settings =
The settings below are configured once per [[Models|model]] in your '''OdataLink Portal''' and applies to '''every refresh''' request.

=== 2. Apply a Cut-off Date on your model ===

OdataLink provides the ability to archive historical data to avoid re-downloading of same data on every refresh. The '''Cut-Off Date''' instructs OdataLink to save data up to the cut-off date and serve future refreshes from that archive.

Setting a cut-off date in your model dramatically reduces the volume retrieved on every refresh by avoiding downloading the same historical data multiple times.

To set a cut off date and enable archiving in your model:
# '''Archive Type:''' Set the [[Archive Types|Archive Type]] of your model to '''Live (with Archive)''' to enable archiving.
# '''Archive Cut-Off Date:''' Set either a fixed cut-off date, or a '''rolling window''' that continuously archives data older than X months or X days.


<blockquote>'''Tip:''' OdataLink recommends a rolling window so new data is automatically archived as it ages. You only configure it once.</blockquote>

[https://odatalink.com/2025-03-increase-performance-of-your-odatalink-odata-feeds-using-the-cut-off-date-settings/ How to apply and use the Cut-off Date setting]

=== 3. Set a Default Date Range on your model ===

OdataLink provides the ability to define a default date range for a model. This allows users to control the amount of data that gets downloaded from the connected [[Data Files|data file(s)]].

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.

[[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 been added or changed in your [[Data Files|data file]], rather than re-downloading the whole dataset on every refresh.

Polling is particularly effective for high-volume transactional entities like Invoices, Bills, and Bank Transactions.

[[Configure OdataLink to poll for changes|How to configure polling]]

 

=Monitoring =

Check your Monthly Usage in your OdataLink Portal''' Manage Models → View → Usage tab'''.

'''Healthy pattern:'''
* Consistent month-to-month (no large spikes)
* 20-30% under your monthly allowance
* No overuse warnings

'''Excessive Usage pattern:'''
* Usage increasing or consistently above 100% month-over-month
* Multiple Usage alerts from OdataLink
* Exceeding allowance for more than 1 month

 

== Checklist ==

* [ ] Refresh frequency matches how often the report is actually viewed
* [ ] Weekend and off-hours refreshes disabled if not needed
* [ ] Archive Type set to '''Live (with Archive)''' for the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled
* [ ] Monthly usage reviewed in OdataLink Admin Portal

 

== 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 query transformations, and recommend further optimisations  in order to bring your account usage within limits.

 

= Related Topics =
: [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
: [[How to Reduce my OdataLink Usage - For Advanced Users]]
: [[How to Reduce my OdataLink Usage - For Power Users]]
: [[Reducing Data Usage and Optimising your OdataLink Feeds]]
: [https://odatalink.com/fair-use-policy Fair Use Policy]

How to Reduce my OdataLink Usage - For Power Users

2026-05-28T05:20:54Z

Ash:

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your organisation have persists OdataLink data into SQL Server, Azure SQL, Fabric Lakehouse/Warehouse, or any other data platform via SSIS, Azure Data Factory (ADF), using Fabric Data Pipelines or Fabric DataFlows, the following techniques will help reduce egress and pipeline usage costs.

This help page is intended towards Data Engineering and Fabric Developer teams running data pipelines and staging data into persistent storage. For report-level optimisations, see [[How to Reduce my OdataLink Usage - For Advanced Users]].

For OdataLink model-level optimisations, see [[How to Reduce my OdataLink Usage - For Everyday Users]].

You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

= Stage Once, Serve Many =

== 1. Use a Single Staging Platform or Pipeline ==
The biggest source of high usage is the same OData entity being downloaded by multiple reports, models, and pipelines. Each retrieval contributes to increasing the total egress or data downloaded from connected [[Data Files|Data File(s)]]

# Pull relevant OData endpoints into a '''staging layer''' (SQL Server, Azure SQL, Fabric Lakehouse, Fabric Warehouse, etc.) '''once per refresh cycle'''. Schedule the refresh or trigger it on demand via Power Automate.
# Update all your queries to point to the staging layer - Power BI Dashboards/Reports/semantic models, Excel reports, Fabric notebooks, Power Apps, and disconnected from OdataLink.

This collapses N reports × M queries refreshes into a '''single retrieval per cycle'''.

<blockquote>'''Tip:''' '''Fabric Dataflow Legacy/Gen2''' is a managed staging layer built on the Power Query (M) engine. It is well suited to teams who want centralised OdataLink ingestion without provisioning SQL Server. Multiple Power BI, Excel, Power Apps, and Power Automate consumers can then read from the Dataflow output, querying OdataLink only once per Gen2 refresh.</blockquote>

[https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-incremental-refresh Fabric Dataflow Gen2 incremental refresh]

= Incremental Loading =

== 2. Replace truncate-and-reload with watermarked incremental loads ==

Truncate-and-reload re-downloads everything on every run. Instead, use a '''high-watermark column'''<ref name="watermark" group="watermark">Cardoso, M. (2023). Robust data ingestion with High-watermarking. ''Microsoft Community Hub''. [https://techcommunity.microsoft.com/blog/fasttrackforazureblog/robust-data-ingestion-with-high-watermarking/3707480 techcommunity.microsoft.com/blog/fasttrackforazureblog/robust-data-ingestion-with-high-watermarking/3707480]</ref> (typically <code>LastModifiedDateTime</code> or <code>UpdatedDateUTC</code>) and pass it into the OData URL via <code>$filter</code> so OdataLink returns only changed rows.

Pattern:
<code>GET .../Invoices?$filter=LastModifiedDateTime gt 2026-05-01T00:00:00Z</code>

# Store the maximum <code>Watermark_Value</code> from the last successful run in a '''control table''' (e.g. <code>dbo.PipelineWatermarks</code>, <code>dbo.Company1Watermarks</code>, <code>dbo.LastUpdatedWatermark</code>).
# Read and inject that value as the lower bound into the OData URL at the start of the next run.
# '''Merge (UPSERT)''' the result into the target table on the natural key (UID). Apply append rules carefully, handling edge cases.
# Update the watermark <ref name="watermark" group="watermark" /> in the control table '''only after''' the merge has completed successfully. A failed run can safely re-pull the same window for the data on retry.

<blockquote>'''Tip:''' This pattern works in '''SSIS''' (OData Source component with parameterised URL via Expression), '''Azure Data Factory / Fabric Data Pipelines''' (Copy Activity with dynamic OData query expression), '''Fabric Dataflow Gen2''' (M parameter), and any custom .NET / Python ETL.</blockquote>

[[OData Feed URLs| OdataLink OData URL structure and query parameters]]

 

== 3. Project only required columns with <code>$select</code> ==

By default, retrieving an entity returns every column, most of which are rarely useful in a warehouse. Restricting columns at the URL level reduces download size, egress, and staging table footprint.

Example:
<code>GET .../Invoices?$select=InvoiceID,InvoiceNumber,Date,Total,Status,LastModifiedDateTime</code>

Always include your watermark <ref name="watermark" group="watermark" /> column in <code>$select</code>, even if it is not loaded into the final warehouse table. Your query transformations need it for the next refresh.

 

== 4. Run historical backfills as a one-time non-recurring job ==

The first load of a large entity is the most expensive download. Your Data workflow should handle it as a one-off setup task, and not on a schedule.

# '''Run the backfill once''': using a separate package or pipeline distinct from your incremental job.
# '''Follow <code>@odata.nextLink</code>''': to page through the data in chunks rather than trying to pull everything in a single request.
# '''Break very large entities into date-bounded windows''': (year-by-year or quarter-by-quarter) using <code>$filter=Date ge 'YYYY-01-01' and Date lt 'YYYY+1-01-01'</code>. A failed run only re-runs the failed window.
# '''Record completed windows''': in a control table (e.g. <code>dbo.BackfillProgress</code>). The backfill becomes safe to re-run, already-loaded windows can be skipped.
# '''Switch off the backfill once history is loaded''': Recurring queries or jobs will then fetch new data using incremental loads only (Section 2).

<blockquote>'''Rule of thumb:''' If your scheduled pipeline is re-downloading data older than intended, check your query filters. Historical data should already be in your staging table.</blockquote>

 

= Scheduling =

== 5. Schedule pipelines deliberately ==

* '''Stagger pipeline schedules''', so that multiple pipelines do not hit the same tenant simultaneously.
* '''Align frequency with the business cycle.''' Daily General Ledger snapshots rarely need sub-hourly retrieval.
* '''Use off-peak windows''': schedule heavy historical refreshes overnight or at weekends.
* '''Use dependency-based triggers''': In Fabric Data Pipelines and Azure Data Factory, trigger downstream pipelines based on the completion event of the upstream load and not at a fixed time. Downstream jobs should run only when the upstream actually produced new rows.

 

== 6. Refresh reference and transactional data on separate frequencies ==

Reference entities change far less frequently than transactional entities. Split your pipeline into two schedules:

{| class="wikitable"
! Frequency!! Examples
|-
| '''Slow (daily or weekly)''' || Chart of Accounts, Tax Rates, Tracking Categories, Contacts (where stable), Items
|-
| '''Fast (intra-day or hourly)''' || Invoices, Bills, Credit Notes, Bank Transactions, Journals
|}

Refreshing reference tables less often and joining them server-side at the warehouse avoids re-downloading effectively static data multiple times per day.

 

= Monitoring =

== 7. Monitor pipeline cost end-to-end ==

OdataLink egress is one component of total pipeline cost. Track both OdataLink usage and your Fabric Compute Units (CUs)

* '''OdataLink egress:''' OdataLink Admin Portal → View Account Details → '''Usage Stats'''
* '''Azure/Fabric Compute and storage :''' Azure Cost Management, Fabric Capacity Metrics app

A well-tuned pipeline reduces both egress and compute. If OdataLink usage drops but Fabric capacity unit (CU) consumption rises, review whether the staging layer itself is computing on the same data set multiple times.

 

== Combined egress reduction example ==

Stacked optimisation techniques reduce Egress and Cost significantly:

<code>GET .../Invoices?$filter=LastModifiedDateTime gt '2026-05-01T00:00:00Z'&$select=InvoiceID,InvoiceNumber,Date,Total,Status,LastModifiedDateTime</code>

Combined with stage-once-serve-many architecture, this typically reduces OdataLink egress by '''90%+''' versus N consumers each pulling full unfiltered entities.

 

== Power Users checklist ==

* [ ] 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<ref name="watermark" group="watermark" />
* [ ] Watermark stored in a control table, updated only on successful append/merge
* [ ] <code>$select</code> used on every entity, including the watermark column
* [ ] Full-table backfills are one-time, not recurring
* [ ] Historical backfills broken into resumable date-bounded windows
* [ ] Pipeline schedules staggered and aligned with the business cycle
* [ ] Reference and transactional data refreshed on separate schedules
* [ ] Downstream pipelines triggered by upstream completion, not fixed time
* [ ] Both OdataLink egress and Azure / Fabric compute cost monitored

 

== Need help? ==

If you have followed the steps above and your usage is still high, contact OdataLink support. We can review your data query and transformation steps, refresh patterns, and pipeline design, and recommend further optimisations in order to bring your account usage within limits.
 

= References =
<references group="watermark" />

= Related Topics =

* [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
* [[How to Reduce my OdataLink Usage - For Everyday Users]]
* [[How to Reduce my OdataLink Usage - For Advanced Users]]
* [[OData Feed URLs]]
* [[Models]]
* [[Data Files]]
* [[How to track my OdataLink Usage]]
* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]

How to Reduce my OdataLink Usage - For Advanced Users

2026-05-28T05:19:36Z

Ash:

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your reports or dashboards use Power Query transformations to clean and shape the dataset, the following optimisations techniques will reduce egress and improve refresh performance.

This help page is intended towards report developers working with '''M Language/Power Query'''. For developers using Fabric Data Pipelines, Azure Data Factory, SSIS, or persistent staging into Lakehouse/Warehouse/DataLake,
 see [[How to Reduce my OdataLink Usage - For Power Users]]

You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

= Query-Level Filtering =
This sections explains how applying a filter early in the transformation steps helps reduce egress. Query level filtering makes use of OdataLink feature that can pass the filters up directly to the data source. This results in OdataLink only downloading the filtered data out of your [[Data Files|data file(s)]].

== 1. Push date filters up to the OData feed level (query folding) ==
Filtering data '''after''' it has been downloaded does not reduce egress since the records have already been pulled out from the company file.

Filtering must happen at the '''Navigation step''' in Power Query so the filter is sent to OdataLink as part of the OData query (<code>$filter=...</code>) and only the matching/filtered rows are returned.

# Apply date filters as early as possible, directly on the source table, before any merges, joins, or column expansions.
# Avoid applying <code>Table.SelectRows</code> over a downloaded table when an equivalent filter can be applied at the navigation step.


[[Model Date Filters and Report Date Filters| '''Reference -''' Model Date Filters vs Report Date Filters]]

 

== 2. Apply supported OData filter operators ==

The following operators fold to the OData feed and execute server-side before any data leaves your data file:

{| class="wikitable"
! Operator !! Operation !! Example
|-
| <code>eq</code> || equal || <code>Status eq 'ACTIVE'</code>
|-
| <code>ne</code> || not equal || <code>Status ne 'ARCHIVED'</code>
|-
| <code>gt</code>, <code>lt</code> || greater / less than || <code>Date gt '2026-01-01'</code>
|-
| <code>ge</code>, <code>le</code> || greater / less than or equal || <code>Amount ge 1000</code>
|-
| <code>and</code> || logical AND || <code>Status eq 'ACTIVE' and Type eq 'BANK'</code>
|-
| <code>or</code> || logical OR || <code>Type eq 'BANK' or Type eq 'CREDIT_CARD'</code>
|-
| <code>startswith()</code> || prefix match || <code>startswith(Empname, 'Ia')</code>
|-
| <code>contains()</code> || substring match || <code>contains(GLCode, '5-01')</code>
|}

<blockquote>'''Note:''' <code>startswith()</code> and <code>contains()</code> are reliable on date string columns but may fail on free-text fields (Code, Name, Description). Use comparison operators on those columns instead.</blockquote>

 

== 3. Filter examples by use case ==

'''Date range''' — highest single-impact reduction:
<code>$filter=Date ge '2026-01-01' and Date le '2026-12-31'</code>

'''Active records only:'''
<code>$filter=Status eq 'ACTIVE'</code>

'''Exclude archived:'''
<code>$filter=Status ne 'ARCHIVED'</code>

 

== 4. Limit columns retrieved with <code>$select</code> ==

OData supports projection via <code>$select=Col1,Col2,...</code>. By default, every column on the entity is returned, including wide free-text fields that may never appear in your report. Restricting projection reduces row size and network transfer.

In Power Query, expand only the columns you need before any other step, and confirm the resulting native query includes a <code>$select</code> clause.

Example:
<code>$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

 
== 5. Combining Filters ==

Stacked techniques decrease the Usage rapidly. Example combined filter + projection:

<code>$filter=Date ge '2026-06-01' and Status eq 'ACTIVE'&$select=InvoiceID,InvoiceNumber,Date,Total,Status</code>

Typically reduces egress by '''~80%''' vs. an unfiltered, all-columns extraction.

 

= Incremental Refreshing =

== 6. Use Incremental Refresh for time-series data ==

For datasets keyed on a date, datetime, or datetimezone column, configure '''Incremental Refresh''' so that only new or changed partitions are pulled on each refresh.

* '''Power BI Premium / Fabric:''' native incremental refresh on semantic models.
* '''Power BI Pro:''' supported, but requires care to ensure the date filter folds. Test with '''View Native Query''' before publishing.
* '''Excel:''' use a date-threshold named range and filter the query URL on it.

<blockquote>'''Tip:''' If your organisation runs Microsoft Fabric, ask your data team about staging OdataLink feeds into a '''Dataflow Gen2''' (built on Power Query). Multiple reports can then consume the staged data without each one querying OdataLink directly. See [[How to Reduce my OdataLink Usage - For Power Users]].</blockquote>

 
= Monitoring =

Verify your optimisations in your OdataLink Admin Portal '''Manage Models''' → View → '''Usage tab'''. Compare:
* Unfiltered request volume (GB) - your baseline before optimising
* Filtered request volume (GB) - after applying optimisation
* '''Reduction %''' = (Unfiltered Volume − Filtered Volume) ÷ Unfiltered Data Volume × 100

'''Target:''' 50–70% combined reduction from filtering + incremental refresh + column projection.

 

== Power Query checklist ==

* [ ] Date filters applied at the OData navigation step
* [ ] Verify Query folding
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets
* [ ] Refresh schedule matches actual report usage
* [ ] Considered Fabric Dataflow Gen2 if multiple reports share the same data

 

== 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 query transformations, and recommend further optimisations in attempts to reduce your account usage.

 

= Related Topics =

* [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
* [[How to Reduce my OdataLink Usage - For Everyday Users]]
* [[How to Reduce my OdataLink Usage - For Power Users]]
* [[OData Feed URLs]]
* [[Models]]
* [[Data Files]]
* [[How to track my OdataLink Usage]]
* [[Model Date Filters and Report Date Filters]]
* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]

How to Reduce my OdataLink Usage - For Everyday Users

2026-05-28T05:18:20Z

Ash:

= Overview =
Your OdataLink subscription includes a monthly data download allowance under our [https://odatalink.com/fair-use-policy '''Fair Use Policy''']. If your account's connected [[Data Files|data file(s)]] are frequently approaching or exceeding monthly limits, the following techniques will help reduce usage with minimal impact to your reporting functionality.

The following optimisations can be applied directly in your '''OdataLink Admin Portal''' or in your reporting software.


You can monitor your current usage at any time in your '''OdataLink Admin Portal'''.
See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].


= Refresh Schedule =

=== 1. Reduce Automatic Refresh frequency ===
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.

* Match the refresh frequency to how often you actually look at the report.
* 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.
<blockquote>'''Tip:''' Refreshing a sales dashboard hourly when the team only checks it on Monday mornings can use 24× more data than needed. Drop to daily, or weekly, and you may reduce that report's usage by 95%+.</blockquote>

 

= Model Settings =
The settings below are configured once per [[Models|model]] in your '''OdataLink Portal''' and applies to '''every refresh''' request.

=== 2. Apply a Cut-off Date on your model ===

OdataLink provides the ability to archive historical data to avoid re-downloading of same data on every refresh. The '''Cut-Off Date''' instructs OdataLink to save data up to the cut-off date and serve future refreshes from that archive.

Setting a cut-off date in your model dramatically reduces the volume retrieved on every refresh by avoiding downloading the same historical data multiple times.

To set a cut off date and enable archiving in your model:
# '''Archive Type:''' Set the [[Archive Types|Archive Type]] of your model to '''Live (with Archive)''' to enable archiving.
# '''Archive Cut-Off Date:''' Set either a fixed cut-off date, or a '''rolling window''' that continuously archives data older than X months or X days.


<blockquote>'''Tip:''' OdataLink recommends a rolling window so new data is automatically archived as it ages. You only configure it once.</blockquote>

[https://odatalink.com/2025-03-increase-performance-of-your-odatalink-odata-feeds-using-the-cut-off-date-settings/ How to apply and use the Cut-off Date setting]

=== 3. Set a Default Date Range on your model ===

OdataLink provides the ability to define a default date range for a model. This allows users to control the amount of data that gets downloaded from the connected [[Data Files|data file(s)]].

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.

[[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 been added or changed in your [[Data Files|data file]], rather than re-downloading the whole dataset on every refresh.

Polling is particularly effective for high-volume transactional entities like Invoices, Bills, and Bank Transactions.

[[Configure OdataLink to poll for changes|How to configure polling]]

 

=Monitoring =

Check your Monthly Usage in your '''OdataLink Portal''' Manage Models → View → '''Usage tab'''.

'''Healthy pattern:'''
* Consistent month-to-month (no large spikes)
* 20-30% under your monthly allowance
* No overuse warnings

'''Excessive Usage pattern:'''
* Usage increasing or consistently above 100% month-over-month
* Multiple Usage alerts from OdataLink
* Exceeding allowance for more than 1 month

 

== Checklist ==

* [ ] Refresh frequency matches how often the report is actually viewed
* [ ] Weekend and off-hours refreshes disabled if not needed
* [ ] Archive Type set to '''Live (with Archive)''' for the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled
* [ ] Monthly usage reviewed in OdataLink Admin Portal

 

== 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 query transformations, and recommend further optimisations  in order to bring your account usage within limits.

 

= Related Topics =
: [[Reducing Data Usage and Optimising your OdataLink Feeds|How to Reduce Usage - Main Page]]
: [[How to Reduce my OdataLink Usage - For Advanced Users]]
: [[How to Reduce my OdataLink Usage - For Power Users]]
: [[Reducing Data Usage and Optimising your OdataLink Feeds]]
: [https://odatalink.com/fair-use-policy Fair Use Policy]

Reducing Data Usage and Optimising your OdataLink Feeds

2026-05-28T05:16:16Z

Ash:

= 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)]].

The guidance below applies to all models connecting to Xero, Xero Practice Manager, MYOB and QuickBooks. Following these steps will:

* Reduce your monthly download volume; and
* Improve refresh performance

You can monitor your current usage at any time in your '''OdataLink Admin Portal → Usage Stats'''.

See [[How to track my OdataLink Usage]] for usage broken down by [[Data Files|Data file]], [[Models|Model]], and [[Users|User]].

 

== What is Usage ==
'''Usage''' is defined as the volume of data OdataLink retrieves from your connected [[Data Files|data files]] each month.

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 Legacy/Gen2) || Stage-once-serve-many, watermarked incremental loads, historical backfill strategy, scheduling discipline, end-to-end cost monitoring
|}

<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>

 

== 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
* [ ] Weekend / out-of-hours refreshes disabled where not needed
* [ ] Archive Type set to '''Live (with Archive)''' on the [[Models|model]]
* [ ] Cut-off Date or rolling window configured
* [ ] Default Date Range configured to match your reporting window
* [ ] Polling enabled

'''Advanced Users:'''
* [ ] Date filters applied at the OData navigation step (not post-download)
* [ ] Query folding verified via '''View Native Query'''
* [ ] <code>$select</code> used to limit columns retrieved
* [ ] Incremental refresh enabled for time-series datasets

'''Azure/Fabric/Data Pipeline developers:'''
* [ ] 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
* [ ] Both OdataLink egress and Azure / Fabric compute cost monitored

 

== 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 to bring your account usage within limits.

 

== Related Topics ==

* [https://odatalink.com/fair-use-policy OdataLink Fair Use Policy]
* [[How to track my OdataLink Usage]]
* [[OData Feed URLs]]

HelpID:ReduceUsage

2026-05-28T05:12:56Z

Ash: Redirected page to Reducing Data Usage and Optimising your OdataLink Feeds

#REDIRECT [[Reducing Data Usage and Optimising your OdataLink Feeds]]