Generate data mapping

In this workflow, you will perform data mapping and schema extension of the asset package. Performing these steps will allow you to map source-to-target data and create a utility network model that meets your needs.

Data mapping

With the Data Migration tools, you can quickly and efficiently map existing source data to subtypes, asset groups, and asset types within the target utility network asset package. The mapping is done from a Microsoft Excel spreadsheet. The data mapping process uses the Create Simple Data Mapping and Create Migration Workspace tools from the Data Migration toolset within the Utility Data Management Support toolbox.

Create a mapping workbook

In these steps, you will create a source-to-target Excel workbook to simplify the data mapping process. To create this workbook, complete the following steps:

1. On the Analysis tab, in the Geoprocessing group, click Tools.
2. In the Geoprocessing pane, click Toolboxes and expand Project. Expand the Utility Data Management Support toolbox and the Data Migration toolset, and then click the Create Simple Data Mapping tool.
3. In the tool, set the following parameters:
   1. For Source Dataset, browse to the Source_Data folder and choose Water_Naperville_SourceData.gdb.
   2. For Asset Package, browse to Water_Utility_Network_Essentials > Data Models > WaterEssentials_AssetPackage.gdb and click OK.
   3. For Output Location, browse to the Working_Data folder and choose the DataMapping folder.
   4. For Output Folder Name, type Naperville_DataMapping.
      The process generates an Excel spreadsheet called DataMapping.xlsx, which will be stored in this folder.
   5. For Count Features, leave the box checked.

      This parameter returns a count of features in the source data per mapping row in the spreadsheet.

   6. For Source Types, verify that you have a map open in your project, and then click the Create Table button .

      This parameter creates a table in your default database with a predefined schema and adds it to your active map. By default, the table will be named Create_Simple_Data_Mapping_Source_Types.

   7. For Existing Data Mapping, leave the parameter blank.

      This parameter allows you to update your generated data mapping workbook without losing any previously completed work. You will use this parameter in a future workflow.

   8. For Include All Target Classes, leave the box unchecked.

      

4. From the active map, open the Create_Simple_Data_Mapping_Source_Types table that you created while setting the Source Types parameter.
5. Complete the table with the following information:

   Source Type	Match Type	Source Class
   STRUCTTYPE	Include	wNetworkStructure
   FITTINGTYPE	Include	wFitting
   SERVICETYPE	Include	wServiceConnection
   VALVETYPE	Include	wControlValve
   TRANSMISS	Include	wMain
   HYDRFLAG	Include	wSystemValve

   You use this table to specify fields in the source feature classes that you want to use to split the data based on unique values.

   Note:

   If your source data includes subtypes, the Create Simple Mapping tool will automatically divide the data based on the subtype codes. It is important to note that the sample source data that you are using for this tutorial does not contain any subtypes and will only use the fields defined in this table.

   The value of STRUCTTYPE that you entered in the Source Type column corresponds to a field in the wNetworkStructure feature class within the source data. This field contains unique values that you will use for additional data segmentation. Each of the field/feature class combinations you entered will be used for determining unique values.

   Note:

   Additional fields can be incorporated into this table to facilitate further data segmentation.

6. On the Edit tab, in the Manage Edits group, click Save.
7. In the Geoprocessing pane, click Run.

   The tool generates an Excel workbook that allows you to map your source data to subtypes, asset groups, and asset types in your target asset package.

Now that the you have generated the workbook, you will use it to perform data mapping and set other processing parameters.

Perform mapping

Next, you will use the mapping workbook to define how the source data should be migrated into the target dataset. This process will also help you identify any gaps in the target dataset that may require an extension of the schema. To perform the mapping, complete the following steps:

1. In Excel, open the DataMapping.xlsx workbook by clicking Working_Data > DataMigration > Naperville_DataMapping.
2. Click the Data Mappings worksheet.
3. In the Source Class column, locate the wCasing row.
4. Update the specified fields for the wCasing row, using the following table as a guide:

   Target Class	Target Subtype (Asset Group)	Target Asset Type
   StructureLine	Pipe Casing	Casing

   Note:

   Each target cell contains drop-down menus with values extracted from the target asset package.

5. Complete the remaining target values, using the following table as a guide:

   Note:

   As you complete the table, you will notice that certain rows are skipped. You will revisit these rows in a future workflow.

   Source Class	Target Class	Target Subtype (Asset Group)	Target Asset Type
   wControlValve (Backflow Control)	WaterDevice	Service Valve	Backflow Valve
   wControlValve (Pressure Reducer)	WaterDevice	Pressure Valve	Pressure Reducing
   wControlValve (Simple Check)	WaterDevice	Flow Valve	Check
   wCurbStopValve	WaterDevice	Service Valve	Service Valve
   wFitting (Bend)	WaterJunction	Fitting	Elbow
   wFitting (Cap)	WaterJunction	Fitting	End Cap
   wFitting (Coupling)	WaterJunction	Fitting	Coupling
   wFitting (Cross)	WaterJunction	Fitting	Cross
   wFitting (Reducer)	WaterJunction	Fitting	Reducer
   wFitting (Reducing Cross)	WaterJunction	Fitting	Reducing Cross
   wFitting (Reducing Tee)	WaterJunction	Fitting	Reducing Tee
   wFitting (Tap)	WaterJunction	Fitting	Tap
   wFitting (Tee)	WaterJunction	Fitting	Tee
   wFitting (Transition)	WaterJunction	Fitting	Transition
   wHydrant	WaterDevice	Hydrant	Hydrant
   wLateralLine	WaterLine	Service Line	Service Line
   wMain (No)	WaterLine	Water Main	Distribution Main
   wMain (Yes)	WaterLine	Water Main	Transmission Main
   wNetworkStructure (Enclosed Storage Facility)	WaterDevice	Storage	Tank
   wNetworkStructure (Production Well)	WaterDevice	Well	Well
   wNetworkStructure (Treatment Plant)	WaterDevice	Treatment Plant	Treatment Plant
   wPump	WaterDevice	Station	Pump Station
   wServiceConnection (Commercial)	WaterDevice	Service Connection	Commercial
   wServiceConnection (Domestic)	WaterDevice	Service Connection	Residential
   wSystemValve (False)	WaterDevice	System Valve	System Valve
   wSystemValve (True)	WaterDevice	Service Valve	Service Valve

6. Locate wControlValve (Snubber) and complete the target cells using the following table as a guide:

   Target Class	Target Subtype (Asset Group)	Target Asset Type	New Asset Type
   WaterDevice	Pressure Valve	Pressure Reducing	Snubber

   When you initially completed the table, you skipped over the Snubber asset type. This is because the target asset package includes pressure reducing valves, but not snubber valves. You added Snubber as a new asset type which becomes a clone of Pressure Reducing.

   

   Click to enlarge this example of the completed spreadsheet.

7. Save the Excel workbook.

By completing the mapping spreadsheet, you identified areas in the target database that required the schema to be extended. You successfully added a new asset type directly in the mapping sheet for Snubber. The only unmapped items that remain are wInjectionFacility and ServiceTerritory. You will map ServiceTerritory later, so you will ignore that feature class for now. For wInjectionFacility, you will expand the schema in a subsequent workflow.

Add associations

Often, the items in your source data possess spatial components that, upon migration, must be associated with other features in the utility network. For example, in your source data, you may have a line for a casing stacked on top of a line for a water main. When you migrate these lines to the utility network, the casing will contain the water main, requiring an association. To generate automated associations during the migration process, complete the following steps:

1. In Excel, open the DataMapping.xlsx workbook by clicking Working_Data > DataMigration > Naperville_DataMapping.
2. Open the Data Mappings worksheet.
3. Locate the row for the source feature class wCasing.
4. Scroll to the right to locate the Association Key column, and type Casing in the cell for that row.
5. Open the Associations worksheet and complete it, using the following table as a guide:

   Association Key	Association Direction	FeatureClass or Relationship	Expression	Cutoff Distance
   Casing	From To	wMain		1

The information you provide instructs the process to establish an association between casings within a 1-foot radius of a water main.

Note:

For more information on associations, see the Info worksheet in the generated DataMapping.xlsx workbook.

Exclude fields

As you perform the mapping, consider how the fields will be migrated. In a subsequent step, you will automate the process of copying all fields from the source data to the target dataset. During this process, you must decide which fields, if any, you want to exclude.

The source data contains a field called FACILITYID, which serves as the unique identifier for each asset. In the target asset package, there is a corresponding field named assetid. Therefore, the FACILITYID field will be mapped to the existing assetid field, eliminating the need to copy the schema of this field from the source. There are other fields in the source data that you will exclude from copying as well.

To exclude fields, complete the following steps:

1. In Excel, open the DataMapping.xlsx workbook by clicking Working_Data > DataMigration > Naperville_DataMapping.
2. Open the Exclude Copy Fields worksheet.
3. Complete the worksheet, using the following table as a guide:

   Target Table	Field Name
   *	FACILITYID
   *	ACTIVEFLAG
   *	ENABLED
   *	ROTATION
   *	FACILITYNAME
   *	CREATOR
   *	CREATIONDATE
   *	LASTUPDATE
   *	LASTEDITOR
   *	FITTINGTYPE
   *	STRUCTTYPE
   *	SERVICETYPE
   *	VALVETYPE
   *	TRANSMISS

   Note:

   Typing an asterisk in the Target Table column directs the tool to block copying of rows with the specified field name to all object classes in the target database. If you want to exclude field copying only from certain target object classes, type the object class name in the target table column.

   Note:

   In this tutorial, editor tracking fields are excluded from being copied. If you want to migrate editor tracking information from your source data, disable editor tracking on the target feature classes in the asset package, migrate your data, and then re-enable editor tracking.

4. Save the Excel workbook.

Expand the model

A new asset group and asset type are needed to map Injection Facility. The Injection Facility asset group and asset type can be copied from the Water Utility Network Expanded data model to extend the Water Utility Network Essentials data model. To extend the model, complete the following steps:

Note:

To understand the asset package schema, explore the following data dictionaries, which are included with the solution.

Water Utility Network Essentials—Browse to Water_Utility_Network_Essentials and open the Data Dictionary folder.

Water Utility Network Expanded—Browse to Water_Utility_Network_Expanded and open the Data Dictionary folder.

Create a utility network copy workbook

You will create an Excel workbook, which you will use to extend the schema of the data model. To create the workbook, complete the following steps:

1. In the Utility Data Management Support toolbox, expand the Asset Package toolset and click the Create Utility Network Copy Workbook tool.
2. In the tool, set the following parameters:
   1. For Source Asset Package, browse to Water_Utility_Network_Expanded > Data Models > WaterExpanded_AssetPackage.gdb and click OK.
   2. For Target Asset Package, browse to Water_Utility_Network_Essentials > Data Models > WaterEssentials_AssetPackage.gdb and click OK.
   3. For Workbook, browse to Working_Data > UtilityNetworkCopy and name the workbook ExpandedToEssentials_Copy.xlsx.

      

3. Click Run.

   The tool generates an Excel workbook. You will use this workbook in the following workflow.

Modify the utility network copy workbook

You will use the Excel workbook to define which asset groups and asset types from the Expanded model should be copied to the Essentials model. To define which properties to copy, complete the following steps:

1. In Excel, open the ExpandedToEssentials_Copy.xlsx workbook located in Working_Data > UtilityNetworkCopy.
2. In the AssetTypeName column, locate the Chlorination row.
3. For the row, copy the values in the Source, AssetGroupName, AssetTypeName, AssetGroupCode, and AssetTypeCode columns.
4. In the same row, paste the copied values into the ToSource, ToAssetGroupName, ToAssetTypeName, ToAssetGroupCode, and ToAssetTypeCode columns.
5. In the ToAssetTypeName column, rename Chlorination to Injection Facility.
6. Leave True for the Categories, AssociationRoles, Rules, Terminals, TierParticipation, and EdgeConnectivity columns.

   Note:

   These columns are used to define which utility network properties you want to bring over when you copy them.

   

   Click to enlarge this example of the completed spreadsheet.

7. Save the Excel workbook.

   In this section, you have updated the generated workbook to incorporate the Injection Facility asset group and asset type into the Water Utility Network Essentials asset package. Additionally, you have renamed the asset type to Injection Facility.

Apply workbook changes to the asset package

Now that you have generated and modified the workbook, you must apply those changes to the working asset package. To apply the changes, complete the following steps:

Note:

When applying the workbook changes, you will be directly modifying the target asset package. It is recommended that you create a compressed file of the asset package, both before and after the apply process. Maintaining a backup of the geodatabase allows you to revert back to a previous version.

1. In the Utility Data Management Support toolbox, expand the Asset Package toolset and click the Apply Utility Network Copy Workbook tool.
2. In the tool, set the following parameters:
   1. For Source Asset Package, browse to Water_Utility_Network_Expanded > Data Models > WaterExpanded_AssetPackage.gdb and click OK.
   2. For the Target Asset Package, browse to Water_Utility_Network_Essentials > Data Models > WaterEssentials_AssetPackage.gdb and click OK.
   3. For Workbook, browse to Working_Data > UtilityNetworkCopy > ExpandedToEssentials_Copy.xlsx and click OK.

      

3. Click Run.
   You now have a new asset group called Injection Facility and a new asset type called Injection Facility in the target asset package.

Update the mapping workbook

Next, you will update the mapping workbook. After expanding the asset package to include a new asset group and asset type, you will update the mapping workbook to include these new additions. This update allows the new asset group and asset type to be selected from the mapping drop-down menus.

Load changes to new mapping workbook

Now that Injection Facility has been added to the asset package, it must be reanalyzed to add the new values to the mapping workbook. To reanalyze the values, complete the following steps:

1. In the Utility Data Management Support toolbox, expand the Data Migration toolset and click the Create Simple Data Mapping tool.
2. In the tool, set the following parameters:
   1. For Source Dataset, browse to the Source_Data folder, and choose Water_Naperville_SourceData.gdb.
   2. For Asset Package, browse to Water_Utility_Network_Essentials > Data Models > WaterEssentials_AssetPackage.gdb and click OK.
   3. For Output Location, browse to the Working_Data folder and choose the DataMapping folder.
   4. For Output Folder Name, type Naperville_DataMapping_Extended.
   5. For Count Features, leave the box checked.
   6. For Source Types, leave the parameter blank.

      Note:

      Upon the initial data mapping workbook generation, the source types were integrated into the Source Types worksheet. As they are now incorporated, there is no requirement to reference them again.

   7. For Existing Data Mapping, browse to WorkingData > DataMapping > Naperville_DataMapping > DataMapping.xlsx and click OK.
   8. For Include All Target Classes, leave the box unchecked.

      

3. Click Run.

The result of this process is a new DataMapping.xlsx workbook with updated target drop-down values and all previous mappings retained.

Add missing mapping

Finally, you will use the updated mapping workbook to revise the data mappings. To add the new values, complete the following steps:

1. In Excel, open the DataMapping.xlsx workbook located in Working_Data > DataMigration > Naperville_DataMapping_Extended.
2. Browse to the Data Mappings worksheet.
3. Update the specified fields for the wInjectionFacility row, using the following table as a guide:

   Target Class	Target Subtype (Asset Group)	Target Asset Type
   WaterDevice	Injection Facility	Injection Facility

   Note:

   Notice that Injection Facility is now available as an option in the drop-down menu. These items now appear because you updated the workbook to include the new asset group and asset type.

   

   Click to enlarge this example of the completed spreadsheet.

4. Save the Excel workbook.

You have completed the initial data mapping from the source data to the target asset package. Additionally, you have expanded the model to account for any missing asset groups and asset types. You can now proceed to the Migrate data workflow.