Getting error with accessing the tables using ODATA

Document ID : KB000104953
Last Modified Date : 13/07/2018
Show Technical Document Details
Issue:
The user is getting error with accessing the tables DWH_INV_TEAM , DWH_INV_TASK, DWH_INV_TASK, DWH_INV_ASSIGNMENT, DWH_INV_TASK_HIERARCHY using ODATA connector 

The error is listed below.
Error: 0xC02090F5 at Data Flow Task, DWH_INV_TEAM [1134]: The DWH_INV_TEAM was unable to process the data. The remote server returned an error: (401) Unauthorized.
Error: 0xC02090F5 at Data Flow Task, DWH_INV_TASK [1023]: The DWH_INV_TASK was unable to process the data. The remote server returned an error: (401) Unauthorized.

 
Environment:
CA PPM 15.3 and above ( PPM SAAS Only)
Cause:
The problem comes when you try to fetch more than 10000 records at one request. Analyzing the logs we found 

The schema logs shown this
 
02-Jul-2018 20:07:22.747 CONFIG [http-nio-8080-exec-2] [xxxddwhhdp][oracle][sc51762ddatasource][reporting@xxx.com][Ij3T+xjUbAo+qi6I].[odata_api][getEntities] Error condition
 
02-Jul-2018 20:07:57.656 CONFIG [http-nio-8080-exec-7] [xxxddwhhdp][oracle][sc51762ddatasource][reporting@xxx.com][Ij3T+xjUbAo+qi6I].[odata_api][getEntities] Error condition
 
02-Jul-2018 20:07:22.747 CONFIG [http-nio-8080-exec-2] [xxxddwhhdp][oracle][sc51762ddatasource][reporting@xxx.com][Ij3T+xjUbAo+qi6I].[odata_api][getEntities] Error condition
 org.odata4j.exceptions.ForbiddenException: The top request exceeds the maximum entities per page limit of 10000

We co related with access logs for the same time, the stack in 1st line was success as it pulled 25 records and the stack in 2nd and 3rd line was failure because it pulled 3000 records. 

10.xx.xx.9 - - [02/Jul/2018:20:07:56 +0000] "GET /api/odata/xxxxDataSource/DWH_INV_TEAM?$top=25 HTTP/1.1" 200 3592 8
10.xx.xx.9 - - [02/Jul/2018:20:07:57 +0000] "GET /api/odata/xxxxDataSource/DWH_INV_TEAM?$top=30000 HTTP/1.1" 401 187 0
10.xx.xx.9 - - [02/Jul/2018:20:07:57 +0000] "GET /api/odata/xxxxDataSource/DWH_INV_TEAM?$top=30000 HTTP/1.1" 403 232 3


 
Resolution:
Customers needs to retrieve less than 10000 records per request as 10000 is the limit set and cannot be increased
Additional Information:
Note: If you are using Microsoft SSIS you need to use $top to fetch the records else the default is 25 and too many iteration can invalidate the session token.