For many years I have used X/Y Event Layers in ArcGIS to take data from a text file or a database, and then render it in a map. I’ve then used the Data Export tools to place a copy of this snapshot into a Geodatabase for further analysis. This has always worked well for me.
However, I’ve recently been working on an integration project with Microsoft Dynamics. Within the SQL Server database I have a number of tables describing customers and the locations of incidents that I want to be able to visualise and analyse in a Silverlight Web Application. My initial reaction was to use X/Y Event layers, but I found a number of issues with doing this. This blog post discusses these issues with reference to integration with MS Dynamics. It should be born in mind that the comments are relatively generic and can be used in any situation where you need to read data from a live non-spatial database.
The first issue I faced with using X/Y Event layers is that ArcGIS will always look for an ObjectID field which is not available as part of the standard Dynamics database schema. You can choose to ignore this, but this removes much of the functionality. For example, ArcGIS will render the points on your map, you can set symbology based upon values in a field, but you can’t select features or undertake geoprocessing with them. I had a look at the documentation around X/Y Event layers and found a tool in the Data Management Toolbox called “Make Query Table” that allows you to append an ObjectID on the fly, as long as you have another numerical/unique field in the table (see screenshot below). Unfortunately the Dynamics unique identifier is a GUID rather than a number , and whilst I could have added one, I did not want to play with the Dynamics schema too much.
Another option that has became available with ArcGIS 10 is the use of the “Query layer” data-type, which allows an ArcGIS User to connect straight to a database table with a geometry spatial column, without using ArcSDE. The main issue here was that I need to be able to edit the location and the ArcGIS 10 “Query Layer” is currently Read-Only.
With two options discounted I had to look a little further. The solution I chose to implement is sweet. Essentially, I used the existing X/Y’s within Microsoft Dynamics, and the GUID, to create a feature class which only held the point geometry and the GUID. This then allows you to create a relationship from the feature to the attribute information held in Dynamics. This means that any changes in Dynamics are carried through to the next load or refresh of the data in ArcGIS. Changes and additions were maintained using the Rest API Feature Service. Some bespoke code was required for the business logic to make this work but it was a relatively minor development task. So, in this situation, ArcGIS is maintaining the parts of the data that it is best able to manage, and Dynamics looking after the same. There is no duplication of data, and it all works harmoniously.
There’s a lot of information on creating ArcSDE views, such as this article here:
http://help.arcgis.com/en/arcgisserver/10.0/help/arcgis_server_dotnet_help/index.html#//0029000000s8000000.htm
One of the other advantages about this approach is that you can index the database and push all of the processing back to the DBMS. The beauty of which is that the right tool, in my opinion, is then handling all of the data, and making it available to ArcGIS for the presentation/analysis work. You are therefore consuming the best bits from each of the tiers in this architectural model.
There is, however, a limitation to this model. It works well as long as you do not want to edit any attribution within ArcGIS. But what if you wanted to take incident data out and edit in ArcGIS Mobile, for example? In this model you would still be able to view the attribute data but not edit it. The issue is that to edit using the native Esri components all data must be in the Geodatabase. So, the model here would be to hold just as much data as is required in the Geodatabase and maintain duplicate data side by side. This is obviously going to require a development activity, but could be managed through code or an Enterprise Service Bus. Triggers would have to be used to Extract-Transform-Load the data in each direction but this would give a true enterprise level solution.
Unfortunately time and requirements did not allow me to explore this final option further; the ArcSDE view implementation worked well for my needs.