Understanding API Pagination and OData
What is API Pagination?
API pagination is a crucial technique for managing large datasets in web services. When an API returns a vast amount of data, it’s often broken down into smaller, more manageable chunks called “pages.” This process helps optimise performance, improve load times, and reduce server load. By implementing pagination, developers can retrieve data incrementally, making the data consumption process more efficient and user-friendly.
What is OData?
OData, short for Open Data Protocol, is a standardised protocol for querying and updating data. Developed by Microsoft, OData provides a uniform way to interact with data sources using RESTful APIs. It supports a wide range of operations, including filtering, sorting, and pagination, which are essential for managing large datasets. OData’s uniform approach simplifies data integration and manipulation across different platforms and applications.
API Pagination in Hubway Connect
Hubway Connect is an OutSystems Enterprise API Gateway that leverages OData for efficient data handling. Here’s how you can implement API pagination in Hubway Connect and other tools like Power BI and Excel.
Client-Driven Paging in Hubway Connect
Client-driven paging in Hubway Connect allows you to control data retrieval efficiently. The key parameters used are:
- $top: Specifies the maximum number of items to retrieve per page.
- $skip: Skips a specified number of items to fetch subsequent pages.
By adjusting these parameters, you can request specific pages of data, ensuring efficient data retrieval without overloading the system.
Enhancing API Performance with Hubway Connect
Hubway Connect offers several benefits beyond pagination:
- Efficient Data Retrieval: Optimises data retrieval to ensure you get the right information without overwhelming your system.
- Seamless Integration: Connects OutSystems applications with external services seamlessly.
- Customisable Limits: From R2.3 onwards, you can customise pagination limits at the project level to suit your specific needs.
Ready to explore Hubway Connect? Book a meeting or sign up for a free 15-day trial to see how it can transform your API management!
Achieving Pagination in Microsoft Power BI
Pagination in Power BI is essential for handling large datasets efficiently. Here’s how to implement pagination in Power BI using Hubway Connect:
- Open Power BI: Go to Transform Data → New Data Source → Blank Query.
- Use Advanced Editor: Click Advanced Editor and paste the following code. Replace <Service link>/<Entity Name> with your service link and entity name. Also, replace columnA, columnB, etc., with the attributes in your entity.
- let BaseUrl = “<Service link>/<Entity Name>”, RecordsPerPage = 1000,
GetJson = (Url) =>
let Options = [
Headers = [#”accept” = “application/json”]
],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetTextData = (Url) =>
let Options = [
Headers = [#”accept” = “text/plain”]
],
RawData = Web.Contents(Url, Options),
TextData = Text.FromBinary(RawData)
in TextData,
GetRecordCount = () =>
let Url = BaseUrl & “/$count”,
TextData = GetTextData(Url),
Count = Number.FromText(TextData)
in Count,
GetPage = (Index) =>
let Skip = “?$skip=” & Text.From(Index * RecordsPerPage),
Top = “$top=” & Text.From(RecordsPerPage),
Url = BaseUrl & Skip,
Json = GetJson(Url),
Value = Json[#”value”]
in Value,
RecordCount = List.Max({ RecordsPerPage, GetRecordCount() }),
PageCount = Number.RoundUp(RecordCount / RecordsPerPage),
PageIndices = { 0 .. PageCount – 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Records = List.Union(Pages),
Table = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(Table, “Column1”, {“columnA”, “columnB”, “columnC”})
in
#”Expanded Column1″
- Authenticate: When prompted for credentials, select Basic Authentication and enter your OutSystems username and password for the service link.
This setup will paginate in blocks of 1000, retrieve all records in the entity, and add them to your Power BI model.
Incremental Load Approach
For handling large tables, consider implementing an incremental load approach:
- Delta Sync: Filter by date range to sync only records created or updated after the last sync. This approach reduces the amount of data processed during each load.
- Pagination: If the result set is still large after applying the delta sync, split it into smaller chunks using pagination.
Implementing Pagination in Microsoft Excel
Pagination in Excel is similar to Power BI. Here’s how to set it up:
- Open Excel: Go to Data → New Query → From Other Sources → Blank Query.
- Use Power Query Editor: Click Advanced Editor and paste the following code. Replace <Service link>/<Entity Name> with your service link and entity name. Replace columnA, columnB, etc., with the column names in your entity.
- let BaseUrl = “<Service link>/<Entity Name>”, RecordsPerPage = 1000,
GetJson = (Url) =>
let Options = [
Headers = [ #”accept” = “application/json”]
],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetTextData = (Url) =>
let Options = [
Headers = [ #”accept” = “application/json”]
],
RawData = Web.Contents(Url, Options),
TextData = Text.FromBinary(RawData)
in TextData,
GetRecordCount = () =>
let Url = BaseUrl & “/$count”,
TextData = GetTextData(Url),
Count = Number.FromText(TextData)
in Count,
GetPage = (Index) =>
let Skip = “?$skip=” & Text.From(Index * RecordsPerPage),
Top = “$top=” & Text.From(RecordsPerPage),
Url = BaseUrl & Skip & Top,
Json = GetJson(Url),
Value = Json[#”value”]
in Value,
RecordCount = List.Max({ RecordsPerPage, GetRecordCount() }),
PageCount = Number.RoundUp(RecordCount / RecordsPerPage),
PageIndices = { 0 .. PageCount – 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Records = List.Union(Pages),
Table = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(Table, “Column1”, {“columnA”, “columnB”, “columnC”})
in
#”Expanded Column1″
- Authenticate: When prompted for credentials, select Basic Authentication and enter your OutSystems username and password for the service link.
This setup will paginate in blocks of 1000, retrieve all records in the entity, and add them to your Excel model. Click Close and Load to load the query into the Excel worksheet.
Incremental Load Approach
As with Power BI, implementing an incremental load approach can optimise data handling:
- Delta Sync: Filter by date range to sync only records created or updated after the last sync.
- Pagination: If the result set remains large, split it into smaller chunks using pagination.
Efficiently managing large datasets through API pagination is crucial for performance and usability. Hubway Connect simplifies this process, providing a powerful, no-code solution for creating and managing APIs. By leveraging Hubway Connect’s capabilities, you can streamline your data retrieval processes, integrate seamlessly with external services, and customise pagination limits to meet your needs.
Ready to experience the benefits of Hubway Connect? Book a meeting with our experts or sign up for a free 15-day trial today. Transform your API management and optimise your data handling with Hubway Connect.