How can I check visual limit settings for each visual in my system?

Use-case: Occasionally users may set higher than normal query limits for their visuals (i.e. 100,000 records), which could cause the browser to crash or create a bad user experience by taking minutes for a visual to load on the page.

Solution:
If you have access to the Arcadia metadata database, you can run the following query to get a result table that will show you which visuals have the highest limit settings.

For each of the visuals you’re interested, use the id of the Visual to access and Edit the limit (if needed):


"limit_type" categories:

using visual builder limit = using the explicit limit set at the bottom of the Visual builder shelves.

41%20AM

using default limit = using the default limit imposed by Arcadia based on the visual type or set at the “Data” section of the visual settings.

07%20AM

using order limit = limit is set by ordering a field by the Top “K” or Bottom “K” values

23%20AM


Query (Tested in MySQL 5.5):

SELECT
id,
report_type,
CAST(if(replace(replace(replace(replace(if(LOCATE(’“order_limit”:’, report_data)=0,"",SUBSTR(SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14),1,if(LOCATE(",",SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14))=0,LOCATE("}",SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14)),LOCATE(",",SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14)))))," “,”"),",",""),’"’,""),"}","")="",if(replace(replace(replace(replace(if(LOCATE(’“limit”:’, report_data)=0,"",SUBSTR(SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8),1,if(LOCATE(",",SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8))=0,LOCATE("}",SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8)),LOCATE(",",SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8)))))," “,”"),",",""),’"’,""),"}","")="",replace(replace(replace(replace(replace(replace(if(LOCATE(‘LIMIT’, report_data)=0,5000,SUBSTR(SUBSTR(report_data, LOCATE(‘LIMIT’, report_data) + 6),1,LOCATE(",",SUBSTR(report_data, LOCATE(‘LIMIT’, report_data) + 6))))," “,”"),’"’,""),":",""),",",""),"}",""),"]",""),replace(replace(replace(replace(if(LOCATE(’“limit”:’, report_data)=0,"",SUBSTR(SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8),1,if(LOCATE(",",SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8))=0,LOCATE("}",SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8)),LOCATE(",",SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8)))))," “,”"),",",""),’"’,""),"}","")),replace(replace(replace(replace(if(LOCATE(’“order_limit”:’, report_data)=0,"",SUBSTR(SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14),1,if(LOCATE(",",SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14))=0,LOCATE("}",SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14)),LOCATE(",",SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14)))))," “,”"),",",""),’"’,""),"}","")) as UNSIGNED INTEGER) as limit_size,
if(replace(replace(replace(replace(if(LOCATE(’“order_limit”:’, report_data)=0,"",SUBSTR(SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14),1,if(LOCATE(",",SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14))=0,LOCATE("}",SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14)),LOCATE(",",SUBSTR(report_data, LOCATE(’“order_limit”:’, report_data) + 14)))))," “,”"),",",""),’"’,""),"}","")="",if(replace(replace(replace(replace(if(LOCATE(’“limit”:’, report_data)=0,"",SUBSTR(SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8),1,if(LOCATE(",",SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8))=0,LOCATE("}",SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8)),LOCATE(",",SUBSTR(report_data, LOCATE(’“limit”:’, report_data) + 8)))))," “,”"),",",""),’"’,""),"}","")="",“using default limit”,“using visual builder limit”),“using order limit”) as limit_type,
FROM arcadia.reports_report
WHERE report_type NOT IN (“dashboard”, “picklist”, “daterange”)
ORDER BY limit_size DESC