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 SanitarySewer_Naperville_SourceData.gdb.
   2. For Asset Package, browse to Sewer Utility Network Essentials > Data Models > SewerEssentials_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	ssNetworkStructure

   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 ssNetworkStructure feature class within the source data. This field contains unique values that you will use for additional data segmentation.

   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 ssCleanout row.
4. Update the specified fields for the ssCleanout row, using the following table as a guide:

   Target Class	Target Subtype (Asset Group)	Target Asset Type
   SewerDevice	Cleanout	Cleanout

   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
   ssControlValve	SewerDevice	Check Valve	Check Valve
   ssGravityMain	SewerLine	Gravity Main	Sanitary Collector
   ssLateralLine	SewerLine	Lateral	Sanitary Lateral
   ssManhole	SewerDevice	Manhole	Manhole
   ssNetworkStructure (Flow Meter)	SewerJunctionObject	Monitoring	Monitoring Device
   ssNetworkStructure (Grease Separator)	SewerDevice	Grease Trap	Grease Trap
   ssNetworkStructure (Lift Station)	SewerDevice	Station	Lift Station
   ssNetworkStructure (Treatment Plant)	SewerDevice	Treatment	Treatment Plant
   ssPresurizedMain	SewerLine	Force Main	Sanitary Force Main
   ssServiceConnection	SewerDevice	Service Connection	Residential Service Connection
   ssTap	SewerJunction	Fitting	Tap

6. Locate ssNetworkStructure (Pump Station) and complete the target cells using the following table as a guide:

   Target Class	Target Subtype (Asset Group)	Target Asset Type	New Asset Type
   SewerDevice	Station	Lift Station	Pump Station

   When you initially completed the table, you skipped over the Pump Station asset type. This is because the target asset package includes lift station data but not pump station data. You will add Pump Station as a new asset type which becomes a clone of Lift Station.

   

   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 Pump Station. The only unmapped items that remain are ssNetworkStructure (Backflow Prevention) and ServiceTerritory. You will map ServiceTerritory later, so you will ignore that feature class for now. For ssNetworkStructure (Backflow Prevention), 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 to other features in the utility network. For example, in your source data you may have a point for a pump stacked on top of a point for a pump station. When you migrate these points to the utility network, the pump will become a nonspatial object that is contained within the pump station, 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 ssNetworkStructure (Flow Meter).
4. Scroll to the right to locate the Association Key column, and then type FlowMeter 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
   FlowMeter	To From	ssManhole		10

The information you provide instructs the process to establish an association between the nearest manhole within a 10-foot radius of a flow meter.

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
   *	INVERTELEV
   *	FACILITYNAME
   *	LASTUPDATE
   *	LASTEDITOR

   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 Backflow Preventer. The Backflow Preventer asset group and asset type can be copied from the Sewer Utility Network Expanded data model to extend the Sewer 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.

Sewer Utility Network Essentials—Browse to Sewer_Utility_Network_Essentials and open the Data Dictionary folder.

Sewer Utility Network Expanded—Browse to Sewer_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 Sewer_Utility_Network_Essentials 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 Sewer Utility Network Expanded > Data Models > SewerExpanded_AssetPackage.gdb and click OK.
   2. For Target Asset Package, browse to Sewer Utility Network Essentials > Data Models > SewerEssentials_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 Backflow Preventer 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 ToAssetGroupName column, rename Backflow Preventer to Backflow Prevention.
6. Leave True for 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 Backflow Preventer asset group and asset type into the Sewer Utility Network Essentials asset package. Additionally, you have renamed the asset group to Backflow Prevention.

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 Sewer Utility Network Expanded > Data Models > SewerExpanded_AssetPackage.gdb and click OK.
   2. For the Target Asset Package, browse to Sewer Utility Network Essentials > Data Models > SewerEssentials_AssetPackage.gdb and click OK.
   3. For Workbook, browse to Working_Data > UtilityNetworkCopy > ExpandedToEssentials_Copy.xlsx and click OK.

      

3. Click Run.
   After running the apply process, you have a new asset group called Backflow Prevention and a new asset type called Backflow Preventer 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 Backflow Preventer 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 SanitarySewer_Naperville_SourceData.gdb.
   2. For Asset Package, browse to Sewer Utility Network Essentials > Data Models > SewerEssentials_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 Backflow Prevention row, using the following table as a guide:

   Target Class	Target Subtype (Asset Group)	Target Asset Type
   SewerDevice	Backflow Prevention	Backflow Preventer

   Note:

   Notice that Backflow Prevention and Backflow Preventer are now available as options in the drop-down menus. 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.