Analyze data volume of API calls with Invantive SQL

Question

The SQL engine hides away all nifty details on what API calls are being done. However, some cloud solutions have pricing per API call.

For instance:

select *
from   transactionlines

retrieves all Exact Online transaction lines of the current company, but:

select *
from   transactionlines
where  financialyear = 2016

filters it effectively on REST API of Exact Online to just that year, reducing data volume. And:

select *
from   gltransactionlines
where  year_attr = 2016

retrieves all data since the where-clause is not forwarded to this XML API of Exact.

Of course I can attach fiddler or wireshark and try to analyze the data volume, but is there an easier way to analyze the data volume of API calls with Invantive SQL?

score 0 · Accepted Answer · edited Mar 13 '17 at 09:01

First of all, all calls handled by Invantive SQL are logged in the Invantive Cloud together with:

the time
data volume in both directions
duration

to enable consistent API use monitoring across all supported cloud platforms. The actual data is not logged and travels directly.

You can query the same numbers from within your session, for instance:

select * from exactonlinerest..projects where code like 'A%'

retrieves all projects with a code starting with 'A'. And then:

select * from sessionios@datadictionary

shows you the API calls made:

You can also put a query like to following at the end of your session before logging off:

select main_url
,      sum(bytes_received) bytes_received
,      sum(duration_ms) duration_ms
from   ( select regexp_replace(url, '\?.*', '') main_url
         ,      bytes_received
         ,      duration_ms
         from   sessionios@datadictionary
       )
group 
by     main_url

with a result such as:

Analyze data volume of API calls with Invantive SQL

1 Answers1