At last, upper management has approved your long-standing proposal to integrate GIS with an Enterprise Work Management System (WMS). Congratulations! You celebrate with your team, and then go back to your office, close the door, sit down at your desk and wonder… are we ready?

You feel pretty confident. After all, the GIS group has been using ArcFM™ Designer since its 9.x release. You are now at 10.2.1.d (right?), and throughout the years the designers have been editing the proposed work and updating the as-built details in GIS using a collection of Compatible Units (CUs) that has been carefully maintained all along. Yes, CU administrators added new CUs to the collection and created Template and Macro Favorites to facilitate the designer’s job and ensure data integrity. Workflows were set in place so that CUs could also be updated and retired. Early on, the administrators used the Compatible Units tab of the ArcFM™ System Favorites Manager Tool (❤️) from within

ArcCatalog, and after upgrading to 10.0.3 (or later) via the CU Administration Tool (). These meticulous CU administrators even ensured that the PROCESS CU Library was kept in sync with the SDE CUs.

Yet, you seem worried. Your mind takes you back to that release: 10.0.3. It was Spring of 2012, and at the TUG Conference in Fort Collins, CO, Telvent’s Product Team unveiled the new SDE CU Library implementation. It was awesome! The CUs were now supported by a Flat Table structure (SDE.MM_CU_LIBRARY), one CU per record. Goodbye BLOBs! Finally, one could look at CU records and “see” CUs. Furthermore, CU Template and Macro Favorites were now referencing the CUs directly in the Library: changes in the Library records immediately “propagated” to those favorites. And to top it off, there it was: the CU Administration Tool. With its easy to use capabilities – Update Schema automatically created the SDE.MM_CU_* tables and relationships, while the Migrate Data… and I quote [1]

Utilities went back home, upgraded ArcFM™/Designer, followed the instructions to upgrade the legacy CU collection to the new CU Library. Right? Then, why are you worried? Are you hiding any skeletons in your closet?

Oh! I see… after running the Migrate Data tool a bunch of duplicated CUs were reported. So? That should have helped in cleaning up a little bit of the clutter. True, but in your case, some of the “duplicates” were perfectly justified and supported by the legacy CU infrastructure with no problems. Perhaps your utility may have purposely created those “duplicates” to differentiate the use of the same asset but in different scenarios? For instance, the same 40 Foot Class V Wood Pole may be used in Distribution designs as well as in Service jobs. Nevertheless, the way your utility handles CU unitization is different in those distinct scenarios. As a solution the CU administrator created two CU records for the same pole:

Table 1 – Example of purposely “duplicated” CUs

Yes, I know… You now regret the team having approved that decision. But, wait a minute… the decision seems perfectly sound. The “duplicates” did not cause any problems prior to the attempted upgrade. You could create it both “flavors” of the same CU with no complaints from the ArcFM™ Systems Favorites Manager. It is not really a duplicate since you can easily differentiate them when they appear in the Compatible Units tree. And, most importantly, it satisfied your business requirements: knowing that a PW405 pole was being hauled out of the warehouse, whether to support overhead primary lines in a Distribution design, or secondary lines in a Service job. So,

1. When did the “duplicates” become a problem?
2. How have you lived with this problem all along?
3. And why does this issue re-surface as a problem again?

1. CU Uniqueness in 10.0.3

As indicated above, one of the crucial capabilities of the 10.0.3 CU Library is CU referencing, and I quote[2]

Uniqueness the TIP says? Well… suddenly your team had a problem: the two distinct CUs were not unique anymore! By sharing the same WMS Code, Table Name and Subtype when executing the Migrate Data tool they were identified as duplicates. Quoting again from Footnote 1.

Then, what happened to the two pole CUs? The best answer is to quote again from Footnote 1

Confronted with this situation, the CU administrator running the Migrate Data for that first time must have made a note of all the duplicates and X-ed out of the tool. Immediately a popup appeared on the screen asking whether to Synchronize CUs, and most likely the answer clicked was ‘No’. (I hope so…)

If this story rings a bell, then how have you lived with this problem all along?

2. Living with Duplicate CUs

After running the Migrate Data tool, and with a bit of apprehension, the CU administrator examined the contents of the SDE.MM_CU_LIBRARY. To his or her satisfaction the legacy CUs (including one for each duplicate) were found stored in the new Flat Table CU Library. Yet, because the Synchronize request was (cautiously) denied, the CUs were never referenced. In other words, the CUs ended up truly duplicated: all of them still within the legacy BLOBs, and most of them also in the CU Library. And because there was no referencing you had to live with this split CU collection in which legacy CUs had to continue being managed via the ArcFM™ System Favorites Manager Tool, and new ones managed with the CU Administration Tool.

Furthermore, making any changes to the CU Library with CU Administration, and on closing the tool, you have always been prompted whether to Synchronize… and (hopefully) you always answered ‘No’. (There is no OOTB mechanism to partially synchronize the CU Library; it is an all or nothing event.)

3. The Problem with Duplicates – Again

The GIS team has lived with this “duplicates issue” for a long time, without any ill-effect in the maintenance of your GIS or the usability of Designer. In fact, some folks (you know who you are…) are not even aware that this is an issue. Your secret is well guarded… but not for much longer.

One of the conditions imposed by the impending integration is that WMS becomes the system of record for CUs. WMS’s are equipped with sophisticated CU Libraries that seamlessly supports applications such as Cost Estimation, Work Approval, Warehouse Reporting, Billing, Forecasting… The CU Library itself is managed internally from WMS: adding new CUs, updating existing ones, and retiring the old. Furthermore, each WMS may implement CUs, their hierarchical organization and its CU Library in a manner that is meaningful within the context of that WMS and its functionality. These constructs are usually proprietary, and far from the how Designer arranges and uses its CU collection.

Ok… this is nothing new. So, why are my duplicates a problem again?

Because they make the integration very challenging. You have copies of the same CU in three places:  in the CU Favorites tree and in your current designs both as BLOBs and in the Designer CU Library as records in a flat table. (Four if you count that the PROCESS version of the CU Library must also be maintained in sync.) And also because the problem never went away, and you still, to this day, have to maintain the same CU collection in two places not being able to take advantage of the referencing capabilities of the CU Library.

It is time to resolve this issue, but how?

To be Continued…

In this article I have explained the process that might have introduced CU duplicity in your Designer CU collection. I also commented about how you may have been able to “ignore” this issue by having some carefully crafted workflows that managed all instances of the duplicates. But then, I brought to your attention how an incoming WMS integration project will make your team confront this problem again, and force you to understand it, and to look for a solution.

In a second part of this blog I will dive into the technical details that you may need to implement in order to eliminate CU duplicity, make your CU Library referential, and be able to support a simpler de-coupled integration with WMS.

Acknowledgement

My thanks go to Darin Warke who prompted my research on this issue, and helped me clarify the details.

[1] https://www.productinfo.schneider-electric.com/portals/ui/arcfmsolution1021d/viewer/598c704a46e0fb0001428ee2/5ce81ba35a01e4000114eb86/r/MigrateData_4#MigrateData_42

[2] https://www.productinfo.schneider-electric.com/portals/ui/arcfmsolution1021d/viewer/598c704a46e0fb0001428ee2/5ce81ba35a01e4000114eb86/r/CUReferencingCPT_0000132262