Analytics Results Limit

[Bharadwaj319]

We are trying to fetch data on Analytics using the below simple query.

But it fails to save nearly 1.5 Lakh records saying the below error.

select systemusername,username,firstname,lastname,email,customproperty53,statuskey from users where statuskey=1

Is this anywhere in the configurations we can explicitly increase this?

In the below document we see this.

But our data is nowhere close to this count.

https://docs.saviyntcloud.com/bundle/SSM-Admin-v55x/page/Content/Chapter16-SSM-Analytics/Analytics-G...

[RakeshMG]

What is the exact version your env is in ?

[Bharadwaj319]

Current version is v5.5 SP3.13

[RakeshMG]

Please share the output of below queries : 

select name, configdata from configuration where name like '%Guardrails%';

select name, configdata from configuration where name like '%analyticsGRConfigJson%';

[Bharadwaj319]

select name, configdata from configuration where name like '%Guardrails%';

NAME	CONFIGDATA
MASTER_SWITCH_FOR_ANALYTICS_PERF_GUARDRAILS	1

select name, configdata from configuration where name like '%analyticsGRConfigJson%';

NAME	CONFIGDATA
analyticsGRConfigJson	{     "maxQueryExecutionTime": "300",     "maxMemoryThreshold": "90",     "retryInterval": "30",     "maxRetryCount": "5",     "batchSize": "5000",     "maxRowThreshold": "100000",     "enableMaxRowThreshold": "1" }
defaultAnalyticsGRConfigJson	{   "8": {     "maxQueryExecutionTime": 1200,     "maxMemoryThreshold": 85,     "retryInterval": 30,     "maxRetryCount": 10,     "batchSize": 3000,     "maxRowThreshold": 4000000,     "enableMaxRowThreshold": "1"   },   "16": {     "maxQueryExecutionTime": 1200,     "maxMemoryThreshold": 85,     "retryInterval": 30,     "maxRetryCount": 10,     "batchSize": 3000,     "maxRowThreshold": 4000000,     "enableMaxRowThreshold": "1"   },   "32": {     "maxQueryExecutionTime": 1200,     "maxMemoryThreshold": 85,     "retryInterval": 30,     "maxRetryCount": 10,     "batchSize": 7500,     "maxRowThreshold": 4000000,     "enableMaxRowThreshold": "1"   },   "64": {     "maxQueryExecutionTime": 1200,     "maxMemoryThreshold": 85,     "retryInterval": 30,     "maxRetryCount": 10,     "batchSize": 10000,     "maxRowThreshold": 4000000,     "enableMaxRowThreshold": "1"   },   "reWrite": true }

[RakeshMG]

The values looks correct, could you also please check in logs if same values are printed.

[Bharadwaj319]

Right I see this in the logs. Is it this maxRowThreshold value you are referring from the logs?

If that is so, I see it is set to 100000 as per the analyticsGRConfigJson.

2023-06-02 13:04:32,503 [https-jsse-nio-443-exec-22] DEBUG analytics.AnalyticsController - params<><>[update:1, id:3196, runtime:false, actionJson:{"
sendEmail":"false","saveToDBElastic":{"version":"v2","checked":"true"},"saveToFile":"false"}, version:[v1, v2], fromUpdateDynamicButton:false, attrCou
nt:0, analyticsName:SupportTesting2, displayName:SupportTesting2, analyticsQry:select username,systemusername from users, description:SupportTesting2
Created by Saviynt Support for Testing Purposes only, userid:, status:1, category:Information Refresh, subcategory:, risk:Low, recommendations:, baseC
ount:, noOfHistoryToKeep:20, ENABLEARCHIVAL:true, enableArchival:true, externalConnection:, emailTemplate:, saveToDBElastic:true, securityContext:0, s
aveToFileLoc:/datadrive/sharedappdrive/saviynt/reports/Archive/Analytics/, controller:analytics, action:columntypeV2]
2023-06-02 13:04:32,580 [https-jsse-nio-443-exec-22] DEBUG analytics.AnalyticsController - select username,systemusername from users
2023-06-02 13:04:32,582 [https-jsse-nio-443-exec-22] DEBUG analytics.AnalyticsESService - Entered checkMaxAnalyticsRowsPerQuery
2023-06-02 13:04:32,584 [https-jsse-nio-443-exec-22] DEBUG analytics.AnalyticsESService - metadataMap :: [totalData:1125, lastHistorySize:1125, colum
ns:[SYSTEMUSERNAME, USERNAME, FIRSTNAME, LASTNAME, DISPLAYNAME, EMAIL, STARTDATE, Corporate AD creation on, USER STATUS], newControl:true, lastExecuti
onTime:3.825 Seconds, lastQueryExecutionTime:0.006 Seconds]
2023-06-02 13:04:32,586 [https-jsse-nio-443-exec-22] DEBUG analytics.AnalyticsESService - guardRailConfigMap : [useNewGuardRail:true, maxQueryExecuti
onTime:300, maxMemoryThreshold:90, retryInterval:30, maxRetryCount:5, batchSize:5000, maxRowThreshold:100000, enableMaxRowThreshold:true]
2023-06-02 13:04:32,587 [https-jsse-nio-443-exec-22] DEBUG services.SaviyntGuardRailService - Final query for processing select count(*) as count fro
m (select username,systemusername from users) as result
2023-06-02 13:04:32,614 [https-jsse-nio-443-exec-22] DEBUG services.SaviyntGuardRailService - Total time taken to execute Query 0.027
2023-06-02 13:04:32,614 [https-jsse-nio-443-exec-22] DEBUG services.SaviyntGuardRailService - executeQuery is done
2023-06-02 13:04:32,614 [https-jsse-nio-443-exec-22] DEBUG services.SaviyntGuardRailService - Got totalData from resultSet :: 118696
2023-06-02 13:04:32,614 [https-jsse-nio-443-exec-22] DEBUG services.SaviyntGuardRailService - Inside closeRSC rscMap - [:]
2023-06-02 13:04:32,616 [https-jsse-nio-443-exec-22] DEBUG analytics.AnalyticsESService - totalData : 118696
2023-06-02 13:04:32,621 [https-jsse-nio-443-exec-22] DEBUG analytics.AnalyticsESService - Exit from checkMaxAnalyticsRowsPerQuery

[RakeshMG]

Please share output for:

select analyticsname, metadata from analyticsconfiges where analyticsname='<name-of-control>';

[Bharadwaj319]

ANALYTICSNAME

METADATA

SupportTesting2

{"totalData":1125,"lastHistorySize":1125,"columns":["SYSTEMUSERNAME","USERNAME","FIRSTNAME","LASTNAME","DISPLAYNAME","EMAIL","STARTDATE","Corporate AD creation on","USER STATUS"],"newControl":true,"lastExecutionTime":"3.825 Seconds","lastQueryExecutionTime":"0.006 Seconds"}

[rushikeshvartak]

select name, configdata from configuration where name like '%analyticsGRConfigJson%';

NAME	CONFIGDATA
analyticsGRConfigJson	{     "maxQueryExecutionTime": "300",     "maxMemoryThreshold": "90",     "retryInterval": "30",     "maxRetryCount": "5",     "batchSize": "5000",     "maxRowThreshold": "100000",     "enableMaxRowThreshold": "1"

You need to change above JSON value

[RakeshMG · Accepted solution]

Please raise FD ticket to get the value updated for analyticsGRConfigJson from 100000 to 400000.