Errors when trying to work with 705,000 records

1508
5
04-25-2018 01:15 PM
Edward_RodrigoSanchez
New Contributor II

I’m working in Insights 2.2.1 for ArcGIS Enterprise 10.6, I connect to database table in SQL Server 2016, this table has 705.000 records. When I try to explore data in a map card (enable location), the system shows me a message “The dataset contains too many records: Try reducing the size of your dataset by filtering it or adding less than 100.000 entities”. I try to adding the same data as Excel or CSV, the system return message “Error loading the file. A maximum file size of 100 MB is allowed”. As last choice I publish the table as service of points (feature layer hosted), add in a map card and the system try to work but show a message “An error has occurred”.

The question:

How can I take advantage of Insights for ArcGIS if I can’t work with 705.000 records?  

What do you advise me?

 

Thanks,

 

Edward S.

0 Kudos
5 Replies
Edward_RodrigoSanchez
New Contributor II

Thanks Will, I modified the properties and found the following:

1. Continues to display the error message when the SQL Server table is used. "The dataset contains too many records: Try reducing the size of your dataset by filtering it or adding less than 100,000 entities"

2. Now, when using the hosted entity layer service, the message changes to a warning "Can not show all entities, click to get more information", which allows working with Insights cards and interacting with the Map.

In other words, to be able to work directly with more than 100,000 records of an alphanumeric SQL server table, you would need to previously spatialize the data (provide a point service) and modify the properties in the ArcGIS server.

Thanks for the help.

0 Kudos
AkshayHarshe
Esri Contributor

Hi Edward,

Are you still experiencing this issue?

I am guessing your were enabling location by addresses.. Have you tried a newer version of insights (2.3)? Also in 3.0 it should work. Note that this will consume credits for geocoding.

Thanks,
Akshay Harshe
Edward_RodrigoSanchez
New Contributor II

Hi Akshay,

I don’t continue reviewing the topic, I worked with the points service and modified the properties in ArcGIS Server. At that time, it worked to resolve the client's request.

Thanks for your attention.

SteveLewis
Occasional Contributor

I would connect to a View in SQL Server and that has a query that pre-aggregates your data.  You can also get it to join your data back to a point or polygon after the query is complete.  Let the SQL Server optimizer do the work.  

SELECT SHAPE, REP_AREA as ReportingArea, Neighborhood, c.* FROM hex_10Meter AS A INNER JOIN (SELECT Hex_ID, Sum(Total_Tons) AS Tons,  FROM Maps_CPN_Hex20 GROUP BY Hex_ID) AS C ON A.Hex_ID = c.Hex_ID