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 Transmission_Naperville_SourceData.gdb.
   2. For Asset Package, browse to Electric_Transmission_Utility_Network_Essentials > Data Models > ElectricTransmissionEssentials_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, 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. 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:

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

   Target Class	Target Subtype (Asset Group)	Target Asset Type
   ElectricTransmissionDevice	High Voltage Switch	AC Circuit Breaker HV

   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
   Busbar (High Voltage)	ElectricTransmissionLine	High Voltage Busbar	AC Station Three Phase HV
   Connector	ElectricTransmissionLine	High Voltage Connector	AC Connector HV
   Disconnect	ElectricTransmissionDevice	High Voltage Switch	AC Disconnect HV
   HVOverheadConductor	ElectricTransmissionLine	High Voltage Overhead Conductor	AC Overhead HV
   PowerTransformer (Station Power)	ElectricTransmissionDevice	Power Transformer	Station - HV->MV
   PowerTransformer (Station Step)	ElectricTransmissionDevice	Power Transformer	Station Step - HV->HV
   PowerTransformer (Station Step Up - MV->HV)	ElectricTransmissionDevice	Power Transformer	Station Step Up - MV->HV
   Substation	StructureJunction	Electric Station	Transmission Substation
   Tap (Overhead)	ElectricTransmissionJunction	High Voltage Connection Point	AC Tap HV
   Tower	StructureJunction	Electric High Voltage Pole	Pole HV

6. Locate LightedMarker and complete the target cells using the following table as a guide:

   Target Class	Target Subtype (Asset Group)	Target Asset Type	New Asset Type
   StructureJunction	Marker	Aerial Marker	Lighted Marker

   When you initially completed the table, you skipped over the LightedMarker asset type. This is because the target asset package includes other types of markers, but not a lighted marker. You added Lighted Marker as a new asset type which becomes a clone of Aerial Marker.

   

   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 Lighted Marker. The only unmapped items that remain are Busbar (Medium Voltage), DistributionBreaker, Tap (MV Bus), and ServiceArea. You will map ServiceArea later, so you will ignore that feature class for now. For the others, you will expand the schema in a subsequent workflow.

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 PrimaryVoltage. In the target asset package, there is a corresponding field named nominalvoltagelinetoline. Therefore, the PrimaryVoltage field will be mapped to the existing nominalvoltagelinetoline 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
   *	Enabled
   *	Rating
   *	PrimaryVoltage
   *	SecondaryVoltage
   *	Voltage

   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

New asset groups and asset types are needed to map the missing data. The needed asset groups and asset types can be copied from the Electric Utility Network Expanded data model to extend the Electric Transmission 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.

• Transmission Essentials
• Expanded

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_Transmission_Utility_Network_Essentials > Data Models > ElectricTransmissionEssentials_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 Three Phase Circuit Breaker MV - Controller row with a source of ElectricDevice.

   Tip:

   Using an Excel filter can help you find rows quickly.

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. Change the ToSource column; change the value to ElectricTransmissionDevice.

   Note:

   Because the names of the source feature class and the target feature class are different, you have to modify this value.

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.

7. Repeat these steps for Busbar Tap MV, changing the ToSource column to ElectricTransmissionJunction, and Station Three Phase MV, changing the ToSource column to ElectricTransmissionLine.

   

   Click to enlarge this example of the completed spreadsheet.

8. Save the Excel workbook.

   In this section, you have updated the generated workbook to incorporate the missing asset groups and asset types into the Electric Transmission 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 Target Asset Package, browse to Electric_Transmission_Utility_Network_Essentials > Data Models > ElectricTransmissionEssentials_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 will have added the missing asset groups and asset types 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 the missing asset groups and asset types have 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 Transmission_Naperville_SourceData.gdb.
   2. For Asset Package, browse to Electric_Transmission_Utility_Network_Essentials > Data Models > ElectricTransmissionEssentials_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.
   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 missing mappings, using the following table as a guide:

   Source Class	Target Class	Target Subtype (Asset Group)	Target Asset Type
   Busbar (Medium Voltage)	ElectricTransmissionLine	Medium Voltage Busbar	Station Three Phase MV
   DistributionBreaker	ElectricTransmissionDevice	Medium Voltage Circuit Breaker	Three Phase Circuit Breaker MV - Controller
   Tap (MV Bus)	ElectricTransmissionJunction	Medium Voltage Connection Point	Busbar Tap MV

   Note:

   Notice that the missing asset groups and asset types are now available as an option in the drop-down menu. These items now appear because you updated the workbook with the information from the expanded asset package.

   

   Click to enlarge this example of the completed spreadsheet.

4. Save the Excel workbook.

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 substation that needs to be associated with a transformer. 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 Source Class rows and update the Association Key column with the value in the table below.

   Source Class	Association Key
   Busbar (High Voltage)	Bus
   Busbar (Medium Voltage)	Bus
   CircuitBreaker	CB
   Disconnect	Disconnect
   DistributionBreaker	DistBreaker
   PowerTransformer (Station Power)	XFR
   PowerTransformer (Station Step)	XFR
   PowerTransformer (Station Step Up - MV->HV)	XFR
   Tap (Overhead)	TapPole

4. Open the Associations worksheet and complete it, using the following table as a guide:

   Association Key	Association Direction	Feature Class or Relationship
   CB	To From	Station_CircuitBreaker
   Disconnect	To From	Station_Disconnect
   XFR	To From	Station_Transformer
   Bus	To From	Substation_Busbar
   DistBreaker	To From	Substation_DistributionBreaker
   TapPole	To From	Tower_Tap
   TapStation	To From	Substation_Tap

The information you provide instructs the process to apply existing relationship classes in the source to the target database.

Note:

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

You have completed the initial data mapping from the source data to the target asset package. You can now proceed to the Migrate data workflow.