I’m retrieving several tables for a client from Exact Online, but I’m running into the configured limits, specifically the daily limit of 50 GB of data traffic. I’d like to find out which tables or columns are generating so much data that I’m hitting this limit.
An example: the table ExactOnlineREST.CRM.Accounts contains a column called Logo, presumably an encoded image. After excluding this column, I was able to resume my data processing. In the meantime, however, I’m hitting the same limit again, presumably due to other heavy tables or columns.
How can I monitor or analyse which tables/columns are responsible for the high data usage? Are there any tools, logging options or best practices to help gain insight into this?
50 GB of network traffic usage is extremely high, especially if it does not concern hundreds of environments. Some recommendations are:
Analysis
It is not known which monitoring tools Azure Data Factory itself offers for network traffic analysis. Therefore, this is described here solely from the perspective of Invantive Cloud. The most commonly used options are:
Monitoring
Many details are available from, for example, the Cloud page “Timeline”. See also:
Session I/Os
The Session I/Os screen also shows the network traffic from Invantive Cloud to Azure Data Factory, alongside the individual API calls.
Note that only the I/Os since 0:00 UTC on the current day are available.
SQL Queries
The processing of a query can be viewed in detail via queries on the data dictionary. See:
Common Optimisations
Compression
When using OData, it is advisable to exchange data in compressed form. Indicate via the standard HTTP header that the data may be sent compressed. See:
This normally saves roughly a factor of 10.
Horizontal Filtering
In general, the advice is to omit fields with large content, such as for relations and articles:
ExactOnlineREST.CRM.Accounts@eol
ExactOnlineREST.Logistics.Items@eol
Specifically for Twinfield, it is also recommended to skip null values, see also:
Vertical Filtering
In general, it is advisable to apply filters if you believe that Items should be used instead of ItemsIncremental or ItemsBulk.
Note that UniversalSQL supports “server-side filtering”. See, for example:
Filtering can also be applied to the *Bulk tables, although somewhat less effectively than on the non-bulk ones. On the *Incremental tables, a filter will only be applied within UniversalSQL. The engine processes all rows in the table. See also:
For the following tables, you may wish to consider the choice between variants and appropriate filters:
This question was automatically closed after 1 week of inactivity. The last answer given has been marked as the solution.
If the problem occurs again, please ask a new question via a separate topic. Please include a link to this topic in the new question by pasting its URL into the text.