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 ElectricUnbalancedDist_Naperville_SourceData.gdb.
   2. For Asset Package, browse to Electric_Unbalanced_Distribution_Utility_Network_Essentials > Data Models > ElectricUnbalancedDistributionEssentials_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, leave the parameter blank.
   7. For Existing Data Mapping, navigate to Source_Data > DataMapping.xlsx

      Note:

      The DataMapping.xlsx workbook includes numerous pre-configured data mapping settings. In the following steps, you will add custom mappings to gain a deeper understanding of how to manually configure these settings.

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

      

4. Click Run.

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:

The DataMapping.xlsx workbook that you referenced, contained many pre-established data mappings. In this section you will add some additional data mappings.

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

   Target Class	Target Subtype (Asset Group)	Target Asset Type
   StructureJunction	Electric Low Voltage Pole	Ornamental LV

   Note:

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

5. Locate SwitchingFacility (Junction Cabinet) and SwitchingFacility (Switchboard) to complete the target cells using the following table as a guide:

   Source Class	Target Class	Target Subtype (Asset Group)	Target Asset Type	New Asset Type
   SwitchingFacility (JunctionCabinet)	ElectricAssembly	Switch Gear	Pad Mounted Switch Gear	Junction Cabinet
   SwitchingFacility (Switchboard)	ElectricAssembly	Switch Gear	Pad Mounted Switch Gear	Switchboard

6. 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 two new asset types directly in the mapping sheet for Junction Cabinet and Switchboard. The only unmapped items that remain are Station and ServiceArea. You will map ServiceArea later, so you will ignore that feature class for now. For Station, you will expand the schema in a subsequent workflow.

Add associations

Often, the items in your source data, upon migration, must be associated to other features in the utility network. For example, in your source data you may have a switch that has a relationship to a structure, either a support structure, a switching facility or underground structure. When you migrate these switches to the utility network, the switches will require a structural attachment association to the structure. 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 Switch (Overhead Disconnect).
4. Scroll to the right to locate the Association Key column, and then type Switch to Structure in the cell for that row.
5. Repeat adding the same association key for Switch (Overhead Load Break), Switch (Underground Disconnect), and Switch (Underground Load Break).
6. Open the Associations worksheet and complete it, using the following table as a guide:

   Association Key	Association Direction	FeatureClass or Relationship	Expression	Cutoff Distance
   Switch to Structure	To From	SwitchingFacility_Switch
   Switch to Structure	To From	SupportStructure_Switch
   Switch to Structure	To From	UGStruct_Switch

The information you provide instructs the process to use relationship classes in the source data to build the needed associations in the Utility Network.

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.

When you created the mapping workbook, you referenced an original workbook that contained many pre-populated values. Some of these values were fields to exclude from copying. You will be appending that list in this workflow.

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
   *	ratedkva
   *	totalkva
   *	totalkvr

   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.

4. Save the Excel workbook.

Expand the model

A new asset group and asset type are needed to map Station. The Electric Station asset group and Distribution Substation asset type can be copied from the Electric Utility Network Expanded data model to extend the Electric Unbalanced Distribution 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.

Electric Unbalanced Distribution Utility Network Essentials—Browse to Electric_Unbalanced_Distribution_Utility_Network_Essentials and open the Data Dictionary folder.

Electric Utility Network Expanded—Browse to Electric_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 Electric_Utility_Network_Expanded > Data Models > ElectricExpanded_AssetPackage.gdb and click OK.
   2. For Target Asset Package, browse to Electric_Unbalanced_Distribution_Utility_Network_Essentials > Data Models > ElectricUnbalancedDistributionEssentials_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 Distribution Substation row that has an AssetGroupName of Electric Station.

   Tip:

   The workbook may contain a large number of rows. The filtering tools in Excel can be especially useful to find the data you are looking for.

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. 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.

6. Save the Excel workbook.

   In this section, you have updated the generated workbook to incorporate the Electric Station asset group and Distribution Substation asset type into the Electric Unbalanced Distribution Utility Network Essentials asset package.

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 Electric_Utility_Network_Expanded > Data Models > ElectricExpanded_AssetPackage.gdb and click OK.
   2. For the Target Asset Package, browse to Electric_Unbalanced_Distribution_Utility_Network_Essentials > Data Models > ElectricUnbalancedDistributionEssentials_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 type called Distribution Substation and a new asset group called Electric Station 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 Distribution Substation 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 ElectricUnbalancedDist_Naperville_SourceData.gdb.
   2. For Asset Package, browse to Electric_Unbalanced_Distribution_Utility_Network_Essentials > Data Models > ElectricUnbalancedDistributionEssentials_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:

      Source types are integrated into the DataMapping.xlsx and do not need to be referenced separately at this point.

   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 Station row, using the following table as a guide:

   Target Class	Target Subtype (Asset Group)	Target Asset Type
   StructureJunction	Electric Station	Distribution Substation

   Note:

   Notice that Distribution Substation asset type for the asset group Electric Station is now available as options in the drop-down menus. These items appear because you updated the workbook to include the new asset group and asset type.

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.