REST/oData "paging" to retrieve more than 20k rows from a Creatio table
Hi. This is a question for anyone there who is knowledgeable in Creatio’s implementation of oData…
We're connecting our BI tool “Qlik” to Creatio via the OData service and in our first test we, of course, have hit the 20,000-record limit per request. To retrieve full datasets (e.g., from the 'Opportunities' table), we need to configure paging in the Qlik connector (using Qlik’s native REST connector).
Like other BI tools, there are several ways to do paging in Qlik -- depending how it is implemented by the oData provider.
(I refer to this document at Qlik: Loading paged data | Qlik Connectors Help )
Can anyone tell me which paging method is used by Creatio's OData feed?
Typical options are:
- Next URL (e.g., @odata.nextLink)
- Offset (e.g., using $skip/$top)
- Next Token
- Next Page (using field values)
- Custom
- If a custom script using parameters like $skip and $top is required, I'd need an example of how to structure the calls to loop through and get all records.
Thanks in advance for any guidance you can provide!
~~~ Rob
The screencap below is the configuration screen for the Qlik REST connector -- It will probably look like other BI vendors connectors. The current connection works, as long as we limit the resultset to $top=20000:
Like
Creatio OData API uses $top and $skip to retrieve the data in pages.
For example:
Page 1 (first 1000)
https://creatiourl/0/odata/contacts?$top=1000&$skip=0
Page 2 (next 1000)
https://creatiourl/0/odata/contacts?$top=1000&$skip=1000
Page 3 (next 1000)
https://creatiourl/0/odata/contacts?$top=1000&$skip=2000
etc (I have no idea how that translates to your Qlik dialog)
Ryan
Hi Ryan --
Yes, that part I got working in Postman. The tricks are:
1) get the BI tool Qlik to connect using their REST connector
2) configuring the connector to automatically do the skips, and load all the data
3) configure the connector to automatically get a fresh security token from Creatio each time the connector is called. (OR, make a token that doesn't expire)
I got #1 and #2 ... working on #3. I had to figure out which "pagination type" would work with Creatio's version of the oData implementation.
Once I got "Offset" right, I was able to use the $skip and $top parameters to make the connector go get 10000 records, and loop until there were no more. (This image is part of the Qlik REST connector settings)
--- regards,
---Rob