Getting error with accessing the tables using ODATA

book

Article ID: 104953

calendar_today

Updated On:

Products

CLARITY PPM FOR ITG CLARITY PPM FEDERAL Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

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.

 

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][[email protected]][Ij3T+xjUbAo+qi6I].[odata_api][getEntities] Error condition
 
02-Jul-2018 20:07:57.656 CONFIG [http-nio-8080-exec-7] [xxxddwhhdp][oracle][sc51762ddatasource][[email protected]][Ij3T+xjUbAo+qi6I].[odata_api][getEntities] Error condition
 
02-Jul-2018 20:07:22.747 CONFIG [http-nio-8080-exec-2] [xxxddwhhdp][oracle][sc51762ddatasource][[email protected]][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


 

Environment

CA PPM 15.3 and above ( PPM SAAS Only)

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.