While working on a Designer™ integration project for MTEMC, we developed a solution to create a GIS relationship between a versioned feature class (a service location) and an unversioned object class (a data table with customer information). This relationship would allow for Customer Information System (CIS) data to be kept up to date with a nightly export of data without having to re-draw any features in the GIS.
Why is it Challenging to Create a Relationship between Versioned and Unversioned Data?
The issue is that relationships between versioned and unversioned data can cause conflicting situations and lead to bad data. Versions allow separate copies of the same data to be edited and then collaboratively managed and pushed to a central version (in this case, SDE.Default). When a relationship is created, the current versioning status is inherited by the destination class from the origin class. In this case, the CIS data table automatically became versioned when creating the relationship from the feature class. To get around that, we simply unversioned the CIS data table after creating the relationship. This does not break the relationship, but it does limit the functionality of that relationship, including disabling most of the out-of-the-box Esri tools.
The reason that this wasn’t going to be an issue for MTEMC is that the CIS data table is never written to, except for during a batch load process. Users can not interact with the data past viewing it in the attribute editor, and it is just simply there for giving additional information to the GIS operator.
The CIS data table is populated nightly from a CIS export file. When relationships are being created, the key used is a constant that is applied to that location. For example, if one location has the Service Location Number of “6000123123” and a new customer moves in to that residence, the Service Location Number will not change in the GIS. It will simply update in the CIS to assign that new customer to that service location.
The Service Location Number acts as the unique identifier for this relationship, meaning that there is only one point on the map for each Service Location Number. Due to the nature of the relationship being between two different versioned statuses, we had to come up with a way to allow multiple users to interact with the data without creating conflicting situations.
Using the Audit Table and SSP Nightly Batch Suite (SSP NBS)
That is where the Audit Table comes in. The Audit Table is a list of currently pending relationships created in Esri ArcMap. Using a custom tool we developed to relate CIS rows to features, an audit row is created for each proposed relationship.
Figure 1. This is the custom tool that MTEMC uses to create relationships in the GIS to their CIS data table. Users may search for specific rows in the CIS table to relate them to the Service Point in the GIS that the tool is opened from.
Figure 2. This is what the tool shows after creating a relationship. Audit information is on the lower section of the form, under the “Pending Relationship” header.
These audit rows essentially lock a CIS row to the version that the audit row is created from, meaning that users in other versions cannot interact with that CIS row until it is either posted or cleared out by SSP Nightly Batch Suite (SSP NBS; learn more about SSP NBS by continuing to read below).
The tool stops users in other versions from creating new relationships between CIS rows and features and shows information about the last proposed relationship. This allows the user to see what version is currently proposing a relationship with that specific CIS row and when it was proposed, which allows for internal discussion about possible conflicts.
Figure 3. Viewing the same data in a different version when there is a row in the Audit table disables the ability to create relationships. The Audit information is shown and various map tools are enabled to allow the user to identify the proposed relationship.
Figure 4. When the Service Point that the Audit row is referring to was created in a different version, map tools are disabled
Invalid or Inactive audits are removed from the table during the SSP NBS process. Any audits that fit the following criteria are removed:
- Created in a version but not saved
- Created in a version, but the version has been deleted
- Posted to default
When these audits are removed, those CIS rows are available for use again. The Audit Table acts as a record of all currently in-process relationship changes for the Service Location Numbers.
The benefit of this solution is that their GIS now always has the latest daily updates from the CIS, without having to reconcile any versions to obtain them. This allows for more accurate customer data in the GIS.
Remember that we only used this type of relationship because the unversioned table was essentially read-only from a user perspective. These types of relationships are generally not encouraged due to the nature of versioning. However, if there is a system in place to keep your data safe this type of a solution can have some very positive outcomes.