Explore the data loading workbooks
To get started, you will download an ArcGIS Pro project package that contains everything that you need for the tutorial. Once you download the project, you will explore some of the data that you will use.
Download the data
In this section you will download and review the content for this tutorial.
- Download the tutorial data.
The project package automatically downloads to the Downloads folder.
- In File Explorer, browse to the Downloads folder and double-click the Water Data Loading Lesson file.
The project unzips to the Documents folder and opens in ArcGIS Pro. You can now review the contents of the project to see all the maps and databases associated with the tutorial.
- In the Catalog pane, click the Project tab and expand the Maps folder to see all the maps in the project.
The project contains two maps:
- Water Asset Package—This map points to the target converted data for the tutorial. The database comes populated with sample data, but this data will be overwritten once you complete the tutorial.
- Water Data Management—This map points to the source data for the tutorial. You can use this map to familiarize yourself with the source data.
- In the Catalog pane, expand the Databases folder to see the databases for the project.
The project has a default database, named the same as the project, and two others:
- Water_assetpackage.gdb—This database is the target database referenced in this tutorial.
- Water_source.gdb—This database is the source database referenced in this tutorial.
You will save the locations of these databases to a text file so that you can easily refer to them later in the tutorial.
- In the Catalog pane, click the water_source geodatabase to select it.
- On the ribbon, on the Catalog tab, in the Clipboard group, click Copy Path.
- Open a text editor, such as Notepad.
- In the text editor, paste the copied path.
- In the Catalog pane, click the water_assetpackage geodatabase to select it.
- On the Catalog tab, click Copy Path and paste the path into the same text editor.
- Save the text file.
Next, you will explore the workbooks that you will use to map fields and create lookups between your data and the data model.
- In the Catalog pane click the Project tab and open the Folders\Water Data Loading Lesson\p20 folder.
The following are the two main folders that you will use:
- Blank Mappings—This folder contains a blank data mapping workspace that you will be populating as part of this tutorial.
- Complete Mappings—This folder contains the data mapping workspace for a complete set of mappings for the source and target workspace. You can refer to this workspace if you get stuck during the tutorial.
The workspaces provided as part of this tutorial were created using the data loading tools. To learn more about how to create data loading workspaces, refer to the next steps at the end of this tutorial for links to helpful articles, tutorials, and blogs.
- Right-click the Blank Mappings folder and choose Copy Path to copy the location of the folder to the clipboard.
- Paste the path into the text editor.
Now that you are familiar with the project and have documented some of the data paths for later use, you will review the data loading workspace that you will use.
Explore data loading workspaces
Data loading workspaces are a series of spreadsheets created by the data loading tools that allow you to map data from a source database to a utility network data model. For the purposes of this tutorial, the data loading workspace is provided for you. Next, you will open and get familiar with the data loading workspace that you will use. You will also update the workspace to use the location of the course data we downloaded.
- Open File Explorer and browse to the \Water Data Loading Lesson\Blank Mappings folder and expand it.
The Blank Mappings folder represents the data loading workspace and contains the definitions of how you will migrate data from the source database to the target database.
Note:
Learn more about data loading workspaces.
- Open the DataReference workbook.
You will review the contents and purpose of the DataPath and SourceTargetMapping sheets of this workbook.
- Click the DataPath sheet to view it.
- In the second row, in the Workspaces column, paste the path of the source database that you recorded earlier.
- Populate the third row in this table with the path of the target database that you recorded earlier.
Note:
Because these folder paths are user and project specific, your paths may be different than the ones shown.
- Click the SourceTargetMapping sheet.
The SourceTargetMapping sheet shows all the layers that the data loading workspace is configured to translate.
- Update the SourceDefinitionQuery and TargetDeleteQuery columns for all the rows to have the expression 1=1.
Updating the rows to the 1=1 expression ensures that every row in the source database is processed, and that the target table is cleared every time the conversion is run. This is important to prevent duplicating data if you run the data load multiple times. As you develop the mappings, you will make these queries more selective, especially in situations where multiple sources are being consolidated into a single target layer. You can see an example of what the final filters look like by referring to the CompleteDataMappings folder.
Because you will only be loading the system valve and water main layers, you will ensure that the data loading tool is only loading these layers. You can control the layers that are loaded by setting their Enabled values to True.
- Find the water main and system valve rows, row 8 and 15, respectively and ensure their Enabled column value is set to True.
- Verify that the Enabled column value for all other rows is set to False.
If you forget to disable the other layers, the tool may fail when processing those layers and potentially create duplicate data.
- Save the workbook.
From this workbook, you can access the other data loading workbooks that you will use.
- Click cell E8 to open the workbook for water mains.
The data mapping workbook for water mains appears.
This is the workbook you will populate for the water mains.
- In the DataReference workbook, find the row for system valves (row 15) and click it to open the data mapping workbook for system valves.
This is the workbook you will populate for the system valves.
- Save and close the DataReference workbook.
Note:
When you close any of these workbooks, save your changes to ensure you do not lose any progress. Also save your changes periodically throughout these tutorials.
You have familiarized yourself with the data and mapping workbooks for water mains and system valves. Next, you will begin mapping values between the data and the data model.
Prepare the system valve workbook
In this module, you'll modify the data loading workspace to convert the system valves from the source data into the corresponding utility network features.
Open the data dictionary
Before you begin mapping data, you will explore the data model using the online data dictionary. Since the information is spread across multiple workbooks in the data loading workspace, it is often easier to use the online data dictionary to find this information.
If you have made any modifications to the schema, they will not be reflected in the online help’s data dictionary. In this case, you will need to refer to the schema spreadsheets created in the data loading workspace.
- Go to the Use Water Distribution Utility Network Foundation page for the solution.
You will be reviewing the enhanced data dictionary as part of this tutorial because it allows you to pin the different cards that describe the data model, which saves a lot of scrolling.
- On the solutions page, click the data dictionary link, or click the link provided here.
The data dictionary appears.
Because you will be looking at system valves in this section, you’ll open the card for it in the data dictionary.
- In the table of contents, expand Layers and click Water Device.
The Water Device card appears in the display. Make note of the Subtype Field, ASSETGROUP.
- Expand Subtypes.
After reviewing the list, you can see there is a System Valve option. The subtype field for this class is called ASSETGROUP, so when you populate the field mappings for this layer, you will use the value that corresponds to that subtype.
- Click System Valve to open its card.
The System Valve card displays information that you need to understand how to map fields. Next, you will map the fields between the source data and the data model.
Map fields
You will begin by populating the field mappings on the first sheet in the system valve workbook. The goal is to populate as many fields as possible in the target data model using the data that is available. Not all the fields on the feature are needed, so if there is not a value available for a field or you are not sure how to populate it, you can skip the field and come back to it later.
- In File Explorer, browse to the tutorial folder. In the Blank Mappings folder, expand the DataMapping and Points folders. Open the wSystemValve-WaterDevice_System Valve workbook.
Note:
The wSystemValve-WaterDevice_System Valve workbook will be referred to as the system valve workbook going forward.
- Click the SourceSchema sheet to review the fields in the source data.
The SourceSchema sheet appears with a list of fields and values.
If your company keeps an internal data dictionary or schema reference, you can refer to that information as well.
- Compare the list of fields on the SourceSchema sheet with the list of target fields on the Mapping sheet and find the common fields between the two layers.
Note:
Opening a second window of Microsoft Excel can allow you to view both lists of fields simultaneously.
After reviewing both lists of fields, these are the fields that you can map between the two datasets.
Target field Source field globalid
GlobalID
diameter
Diameter
normalstatus
NormallyOpen
presentstatus
CurrOpen
operable
Operable
installdate
installdate
assetid
FacilityID
ownedby
OwnedBy
maintby
MaintainedBy
additionaldevice
BypassValve
notes
LocDesc
symbolrotation
Rotation
Because each subtype has a unique alias for each field, you will also compare the source fields with the data dictionary.
- Place the data dictionary side by side with the source fields workbook.
- In the data dictionary, on the System Valve card, expand Fields.
- Look for field aliases for the asset group that match the fields in the source data model.
The utility network has a field called additionaldevice that is used to indicate whether a feature has a bypass valve. This matches with the BypassValve field in the source data model.
Once you’ve reviewed all the fields in this asset group, you will have found other fields you can map.
Target field Asset group field Source field designtype
Valve Type
ValveType
additionaldevice
Has Bypass
BypassValve
designinfo
Turns To Close
TurnsToClose
lifecyclestatus
Lifecycle Status
ActiveFlag
You now have a list of 15 fields that you have matched between the two systems.
For each of these fields, assume that you can map them directly from the source feature to the target feature.
- Return to the system valve workbook.
- On the Mapping sheet, find the globalid field row. In the Expression column for the globalid field row, click the cell, click the drop-down arrow, and choose !GlobalID!.
Next, you will map the remaining fields in the same manner.
- Use the following table as a guide to find each of the fields, and in the Expression column for that field, pick the matching source field.
Target field Source field diameter
Diameter
designtype
ValveType
normalstatus
NormallyOpen
presentstatus
CurrOpen
additionaldetails
ClockToClose
designinfo
TurnsToClose
operable
Operable
installdate
installdate
assetid
FacilityID
ownedby
OwnedBy
maintby
MaintBy
additionaldevice
BypassValve
notes
LocDesc
symbolrotation
Rotation
lifecyclestatus
ActiveFlag
All the fields are now mapped for the system valves.
Now that you’ve found the common fields between the two models, you have finished the first step of mapping features in the layer. Next, you will create a lookup that will allow you to change the values in fields to ensure things match between the data models.
Create lookups for system values
You created mappings for each of the fields and assumed that each field would map directly between the two layers. In this section, you will revisit that assumption and identify situations where you will create lookups to translate between the two models. The data loading tool allows you to create and reference lookup sheets when the values representing the data are different between the source and target fields. This may be as simple as translating a value of Yes to a Y or turning a list of manufacturer names from the source data into a set of codes in the target data.
The data loading workspace automatically creates associated sheets in the workbook if any of the source fields have domains assigned to them. To identify where to create lookups, see if any of the fields you have created mappings for have tabs in the spreadsheet.
- View the system valve workbook and, if necessary, click the Mapping sheet. Explore the fields and see if there are matching tabs.
The following fields have tabs that you will review and create lookups for:
- ValveType
- Diameter
- OwnedBy
- Maintainedby
- BypassValve
- ActiveFlag
- Open the browser tab that has the data dictionary.
- Under System Valve, expand Fields.
- Scroll down the list of fields and find the designtype field.
- In the row for the designtype field, find Water_Valve_Type and click the button next to it to open the card for valve types.
A list of available controller valve types for a utility network appears.
You will use the types listed in the data dictionary to fill out field in the system valve workbook.
- In the system valve workbook, open the VALVETYPE sheet and compare its values with the list from the online dictionary.
Reviewing these lists, you can see that each of the values in the workbook have a corresponding code in the data model.
To create a lookup, you will add two columns to the VALVETYPE sheet: a column for the type and a column for the description.
- In the VALVETYPE sheet, next to the psSystemValveType (Description) field, in column C, type New Valve Type. In column D, type Description.
Note:
You can click the column name piSystemValveType (Description) to jump to the source worksheet.
While you can name these columns anything you want, they will be referenced later in the tutorial in other tabs of the workbook. For the tutorial, you will use the names indicated. For your own migrations, you can use your own naming standard.
- Using the information in the table, populate the two columns you added with the type and description from the target domain.
New Valve Type Description Ball
20
Butterfly
21
Cone
22
Gate
23
Plug
24
Unknown
0
Notice that there are no entries for Roundway or Other in the data dictionary. It is common for there to be codes in the source domain that do not have a corresponding value in the target domain. If you can confirm you have data that uses these codes, add new codes and descriptions to the target model and to this spreadsheet.
Note:
The data loading workspace is a copy of the geodatabase schema when the workspace was created. If you make any changes to the source or target schema after creating the workspace, you must either manually update the data loading workspace with the changes or create a new workspace and migrate all the mappings to the new workspace.
Now that you’ve finished creating the lookup, you will update the Mapping sheet to reference these columns.
- Click the Mapping sheet, find the designtype row and do the following:
- In cell D20, type VALVETYPE.
- In cell E20, type VALVETYPE.
- In cell F20, type New Valve Type.
The values that you type are highlighted in red at first because you are not allowed to have an expression and a lookup defined on the same field. To solve this issue, you will remove the expression.
- For the designtype row, in the Expression column (cell C20), select !VALVETYPE! and delete it.
The red highlighting on the row goes away.
You have updated the designtype field with the sheet name (VALVETYPE), original field name (VALVETYPE), and new column name (New Valve Type), from the lookup that you created. You have also cleared the expression so that the lookup is used during the loading process.
Next, you will create a lookup for several other fields.
Create lookups for several fields
There are five more fields that you must create lookups for. You will follow the same process of creating the fields and populating the values, but to simplify things, you do not have to keep going to the data dictionary. The correct values will be provided for you. If you want to use the workflow that you have used to find the values in the data dictionary, you can do so, but the instructions will not include that.
- Create a lookup for the BYPASSVALVE sheet by performing the following steps:
- In the BYPASSVALVE sheet, add two column headers, one called New Additional Device and one called Description.
- In the New Additional Device column, in cell C2, type 2 and type 1 into cell C3.
- In the Description column, in cell D2, type No and in cell D3, type Yes.
- Continue creating the lookup in the Mapping sheet by performing the following steps:
- In the Mapping sheet, go to row 32 (additionaldevice) and clear the expression.
- For the LookupSheet and LookupKeys columns, type BYPASSVALVE.
- In the LookupValue column, type New Additional Device.
You have created the lookup for the additionaldevice field. Next, you will create the lookup for the ownedby field.
- Create a lookup for the ownedby field by performing the following steps:
- In the OWNEDBY sheet, add two column headers, one called New Ownedby and one called Description.
- In the New Ownedby column, type 1 into the first blank cell (C2), type 2 into cell C3, and 100 into cell C4.
- In the Description column, in cell D2, type Our Agency, in cell D3, type Private, and in cell D4, type Other.
- Continue creating the lookup in the Mapping sheet by performing the following steps:
- In the Mapping sheet, go to row 30 (ownedby) and clear the expression.
- For the LookupSheet and LookupKeys columns, type OWNEDBY.
- For LookupValue, type New Ownedby.
You have created the lookup for the ownedby field. Next, you will create the lookup for the maintby field.
- Create a lookup for the maintby field by performing the following steps:
- In the MAINTBY sheet, add two column headers, one called New Maintby and one called Description.
- In the New Maintby column, in the first blank cell (C2), type 1 and in cell C4, type 100.
- In the Description column, in cell D2, type Our Agency, in cell D3, type Private, and in cell D4, type Other.
- Continue creating the lookup in the Mapping sheet by performing the following steps:
- In the Mapping sheet, go to row 31 (maintby) and clear the expression.
- For the LookupSheet and LookupKeys columns, type MAINTBY.
- For LookupValue, type New Maintby.
Next, you will create the final lookup for the system valves on the lifecyclestatus field.
- Create a lookup for the lifecyclestatus field by performing the following steps:
- In the ACTIVEFLAG sheet, add two column headers, one called New Lifecycle Status and one called Description.
- In the New Lifecycle Status column, in the first blank cell (C2), type 256 and in cell C3, type 8.
- In the Description column, in cell D2, type Out of Service and in cell D3, type In Service.
- Continue creating the lookup in the Mapping sheet by performing the following steps:
- In the Mapping sheet, go to row 36 (lifecyclestatus) and clear the expression.
- For the LookupSheet and LookupKeys columns, type ACTIVEFLAG.
- For LookupValue, type New Lifecycle Status.
The lookups for the system valves are now complete.
Create lookup for the diameter field
Next, you will create a lookup for the Diameter field. There are many values for this field, so you will copy and paste the values from the completed mappings workbook.
- In the system value workbook, click the DIAMETER sheet.
The DIAMETER sheet appears.
The values for piPipeDiameter (Description) are what is in the source data. Next, you will look at the values in the data dictionary to see what matches.
- Open the browser tab that has the data dictionary.
- On the System Valve card, in the Fields section, find the diameter field.
- Click the button next to 9_Water_Diameter to view its values.
The Water_Diameter coded domain values appear.
- Compare the values in the source sheet to the values in the data dictionary.
The source data does not have an entry for ½ inch, so you will not include this in the lookup.
- Return to the system valve workbook and the DIAMETER sheet.
- Add two column headers, one named New Diameter and one named Description.
Next, you will copy the values from the source data and paste them into the lookup fields since a majority of the values are the same.
- Select cells A2 to B31. Right-click the selected cells and choose Copy.
- Click cell C2, right-click and choose Paste.
- In cell C31, change –1 to 0 and in cell D31, change Other to Unknown.
Next, you will adjust the Mapping sheet so the diameter field uses the lookup you created and not the expression that is currently assigned.
- Click the Mapping sheet to open it.
- In column A, find the row that contains the diameter field (A17).
- Clear the expression in cell C17 and type the following values:
- In cell D17, for LookupSheet, type DIAMETER.
- In cell E17, for LookupKeys, type DIAMETER.
- In cell F17, for LookupValue, type New Diameter.
You have created a lookup for the diameter field and are finished creating lookups for the system valves.
Note:
To see exactly what each lookup should look like, refer to the wSystemValve-WaterDevice_System Valve workbook that is in the Completed Mappings folder for the project.
Populate the asset group and asset type
Two of the most important values on a feature are its asset group and asset type. These fields control how the feature behaves in the utility network. This includes which layer the feature appears in, along with its symbology, rules, and many other behaviors. This section will show you how to use the technique you learned in the previous sections to populate the asset group and asset type for each of the features.
You previously determined that you are using the system valve asset group and the subtype code for this asset group is 2.
- In the system valve workbook, in the Mapping sheet, for assetgroup, set the Expression value to 2.
By setting the expression to a number, you are saying that every feature in the system valve layer in the source data will be converted into a system valve in the target model. Next, you will determine the asset type or types for this layer.
- View the browser tab with the data dictionary and expand the Water Device card. Expand Subtypes and click System Valve.
The System Valve card appears.
The System Valve card explains the purpose of a system valve in the model and can also be used to review any of the schema or rules assigned to this asset group.
- On the System Valve card, expand Asset Types, find the System asset type, and view its code.
You can read the descriptions of all the asset types and select the one that seems most appropriate. In this case, the System asset type for the System Valve asset group appears the most appropriate choice for features in this layer.
- In the Mapping sheet, for assettype, set the Expression to 170.
When the conversion runs, this ensures that every system valve in the source database appears in the system valve layer with an asset type of system. If you determine that the source system valve layer contains other types of valves—such as isolation zone valves, pressure zone valves, or fire hydrant valves—you could repeat the process that you used for creating lookups to create a lookup for the asset type field.
- Save the system valve workbook.
You have successfully mapped fields and created lookups for the system valves. Next, you will do the same for water lines.
Prepare the water line workbook
In this module, you will modify the data loading workspace to convert the system valves from the source data into the corresponding utility network features.
Open the data dictionary
As you did in the previous module with system valves, before mapping the data, you will explore the data model for the target layer, using the data dictionary in the online help to find this information.
If you have made any modifications to the schema, they will not be reflected in the online help’s data dictionary. In this case, you will need to refer to the schema spreadsheets created in the data loading workspace.
- Open the browser tab that has the data dictionary in it.
You are ready to explore and map the water mains, so you will open the card for Water Line. The data dictionary may still be open to the Water Device category, so you will collapse that first.
- In the data dictionary, click Water Device to collapse it.
Once you collapse Water Device, you can see Water Line. This is the section that you want to explore because water mains are a type of water line.
- Under Layers, click Water Line to view its card.
The Water Line card is added to the display.
Make note of the Subtype Field, ASSETGROUP.
- Expand Subtypes and find the subtype that corresponds to mains.
After reviewing the list, notice that there is a value for Water Main. The subtype field for this class is called ASSETGROUP so when you populate the field mappings for this layer, you will use the value that corresponds to this subtype.
- On the card, expand Water Main to view its information.
You can use the information in the Water Main card to understand how to map the fields.
Map fields
You will begin by populating the field mappings on the first sheet in the water main workbook. The goal is to populate as many fields as possible in the target data model using the data that is available in the current data model. Not all the fields on the feature are needed, so if some fields don’t have a value, or you aren’t sure how to populate it, you can skip the field and come back to it later.
- In File Explorer, go to where you downloaded the data, browse to Water Data Loading Lesson\Blank Mappings\DataMapping\Polylines, and open wMain-WaterLine_Water Main.
Note:
You can open Water Data Loading Lesson\Complete Mappings\DataMapping\Polylines\wMain-WaterLine_Water Main as a reference while you map the fields. You must modify its name first so you can open two of the same named workbooks.
- Click the SourceSchema sheet to review all the fields in source data.
Note:
If your company keeps an internal data dictionary or schema reference, you can refer to that information while you map the fields.
- Compare the list of fields on the SourceSchema sheet with the list of fields on the TargetSchema sheet and find the common fields between the two layers.
After reviewing both lists of fields, these are the fields that you can map between the two datasets.
Target field Source field globalid
GlobalID
installdate
InstallDate
assetid
FacilityID
ownedby
OwnedBy
maintby
MaintBy
diameter
Diameter
material
Material
Because each subtype has a unique alias for each field, you will also compare the source fields with the data dictionary.
- Place the online data dictionary side by side with the workbook showing the SourceSchema sheet.
- In the data dictionary, and in the Water Main card, expand Fields.
- Look for the field aliases for the asset group that match the fields in the source data model.
The utility network has a field called designtype that is used to indicate the type of water that flows through this main. This matches with the WaterType field in the source data model.
Once you’ve reviewed all the fields in this asset group, you will have found other fields that you can map.
Target field Source field lifecyclestatus
ActiveFlag
designtype
WaterType
You now have a list of nine fields that you have matched between the two systems
Target field Source field globalid
GlobalID
installdate
InstallDate
assetid
FacilityID
ownedby
OwnedBy
maintby
MaintBy
diameter
Diameter
lifecyclestatus
ActiveFlag
material
Material
designtype
WaterType
For each of these fields, assume that you can map them directly from the source feature to the target feature. You will revisit and improve on this assumption in a later section.
- In the water main workbook, click the Mapping sheet.
- In the TargetField column, find the globalid field (cell A8). In the Expression column for the globalid field, click the empty cell, click the drop-down menu, and choose !GlobalID!.
- For each of the eight remaining target fields, in the Expression column, pick its corresponding source field. Use the following information as a guide:
Target field Source field installdate
InstallDate
assetid
FacilityID
ownedby
OwnedBy
maintby
MaintBy
diameter
Diameter
lifecyclestatus
ActiveFlag
material
Material
designtype
WaterType
You mapped all the necessary fields between target and source.
Next, you will examine the field mappings and create lookups.
Create lookups
The data loading tool creates lookup sheets when the source data has domains. You then reference the lookup sheets and fields when the values for the data are different between the source and target fields. This may be as simple as translating a value of Yes to a Y or turning a list of manufacturer names from the source data into a set of codes in the target data.
You created mappings for each of the fields and assumed that each field would map directly between the two layers. Next, you will revisit that assumption and find situations where you must create lookups to translate between the two models.
To identify where a lookup is necessary, see if any of the fields you have created mappings for have tabs in the spreadsheet.
- In the water line workbook, view the Mapping sheet and scroll the list of worksheets to view their names and see if any match the fields.
The data loading workspace automatically creates sheets in the workbook if any of the source fields have domains assigned to them. These are used as a quick and straightforward way to create lookups.
The following fields that have corresponding tabs and must be reviewed:
- OwnedBy
- Maintainedby
- Diameter
- ActiveFlag
- Material
- WaterType
You will review the schemas for the source and target schemas for each of these mappings to figure out what the mappings are. You can use the target schema tab or the online data dictionary to see which of these fields have domains assigned. In this example, you are using the online dictionary to avoid having to navigate between multiple spreadsheets.
- View the data dictionary. In the Fields list, find Material and expand the domain Water_Main_Material.
The domain values appear on the card below.
- In the water main workbook, click the Material sheet and compare its values with the domain coded value list from the online dictionary.
Next, you will create a lookup by adding columns to the MATERIAL sheet.
- In the MATERIAL sheet, type two additional column names, New Material and Description.
Note:
While you can name these columns anything you want, they will be referenced later in the tutorial in other tabs of the workbook. For the purposes of this tutorial, you will use the names indicated. For your own migrations, you can use your own naming standard.
Referring to the source and target domain lists, you will identify the new material code for each material value in the source data model. If you find a value in the source model that doesn’t have a corresponding target value, set the code to 0 and the description to unknown. If you have populated data in the model with these codes, you must add new material codes to the target model and reference these codes in this lookup.
To save time, rather than adding all the codes and descriptions manually, you will copy them from the completed water main sheet.
- In File Explorer, browse to the tutorial data folder and expand \Completed Mappings\DataMapping\Polylines. Double-click wMain-WaterLine_Water Main to open the completed water main workbook.
- In the completed mains workbook, click the MATERIAL tab.
This sheet contains all the information that you need to create the lookup. Note that there are some types that do not exist in the data. For these, a value of 0 and a description of Unknown is assigned.
- Select cells C2 to D37 and press Ctrl+C to copy the values.
- Return to the water main workbook's MATERIAL sheet. Click cell C2 and press Ctrl+V to paste the values.
It is common for there to be codes in the source domain that do not have a corresponding value in the target domain. If you can confirm you have data that uses these codes, add new codes and descriptions to the target model and to this spreadsheet.
Note:
The data loading workspace is a copy of the geodatabase schema when the workspace was created. If you make any changes to the source or target schema after creating the workspace, you must either manually update the data loading workspace with the changes or create a new workspace and migrate all the mappings to the new workspace.
Now that you’ve finished creating the lookup, you will update the Mapping tab to reference these new columns.
- Populate the New Material and Description columns with the code and description from the target domain.
- Click the Mapping sheet and find the material row. For the LookupSheet and LookupKeys columns, type MATERIAL. For the LookupValue column, type New Material.
The values that you type are highlighted in red because you cannot have an expression and a lookup defined on the same field.
- In the material row, remove the expression so the conversion will use the lookup to populate this field.
Removing the expression clears the red highlighting on the row. Next, you will build the remaining lookups. There are five more to build. Rather than consulting the data dictionary for every field, you will use the values provided. However, you can consult the data dictionary to verify the work.
- Create a lookup for the ownedby field by performing the following steps:
- Click the OWNEDBY sheet.
- Add two column headers, New Ownedby and Description.
- In cell C2, type 1, and in cell D2, type Our Agency.
- In cell C3, type 2, and in cell D3, type Private.
- In cell C4, type 100 and in cell D4, type Other.
- In the Mapping sheet, find the ownedby row and do the following:
- Remove the value in the Expression field.
- For LookupSheet and LookupKeys, type OWNEDBY.
- For LookupValue, type New Ownedby.
- Create a lookup for the maintby field by performing the following steps:
- Click the MAINTBY sheet.
- Add two column headers, New Maintby and Description.
- In cell C2, type 1, and in cell D2, type Our Agency.
- In cell C3, type 2, and in cell D3, type Private.
- In cell C4, type 100 and in cell D4, type Other.
- In the Mapping sheet, find the maintby field and do the following:
- Remove the value in the Expression field.
- For LookupSheet and LookupKeys, type MAINTBY.
- For LookupValue, type New Maintby.
- Create a lookup for the diameter field by performing the following steps:
- Click the DIAMETER sheet.
- Add two column headers, New Diameter and Description.
- In the completed workbook for water mains, click the DIAMETER tab. Copy cells C2 to D31.
- In the water main workbook, in the DIAMETER sheet, click cell C2 and copy the values.
- In the Mapping sheet, find the diameter field and do the following:
- Remove the value in the Expression field
- For LookupSheet and LookupKeys, type DIAMETER.
- For LookupValue, type New Diameter.
- Create a lookup for the activeflag field by performing the following steps:
- Click the ACTIVEFLAG sheet.
- Add two column headers, New Lifecycle Status and Description.
- In cell C2, type 256, and in cell D2, type Out of Service.
- In cell C3, type 8, and in cell D3, type In Service.
- In the Mapping sheet, find the lifecyclestatus field and do the following:
- Remove the value in the Expression field
- For LookupSheet and LookupKeys, type ACTIVEFLAG.
- For LookupValue, type New Lifecycle Status.
- Create a lookup for the designtype field by performing the following steps:
- Click the WATERTYPE sheet.
- Add two column headers, New Design Type and Description.
- In the New Design Type column, starting at cell C2 and going down a cell after each entry, type 1, 4, 2, 0, 0, and 0.
- In the Description column, starting at cell D2 and going down a cell after each entry, type Potable, Raw, Irrigation, Unknown, Unknown, Unknown.
- In the Mapping sheet, find the designtype field and do the following:
- Remove the value in the Expression field.
- For LookupSheet and LookupKeys, type WATERTYPE.
- For LookupValue, type New Design Type.
- Save the workbook.
Next, you will look at the asset type and asset group values.
Populate the asset group and asset type
Two of the most important values on a feature are its asset group and asset type. These fields control how the feature behaves in the utility network. This includes which layer the feature appears in, along with its symbology, rules, and many other behaviors. This section will show you how to use the technique you learned in the previous sections to populate the asset group and asset type for each of the features.
- In the data dictionary, view the Water Line card.
Note:
Scroll up as it is already open near the top of the screen.
You previously decided that you are using the Water Main asset group, and the subtype code for this asset group is 1.
- In the water line workbook, in the Mapping sheet, for the assetgroup field, set the Expression value to 1.
By setting the expression to a number, you are saying that every feature in the main layer in the source data will be converted into a water main in the target model. Next, you will determine the asset type or types for this layer.
- Go back to the data dictionary and the Water Line card. Click Water Main to open its card.
This card explains the purpose of a water main in the model and can also be used to review any of the schema or rules assigned to this asset group.
- Expand Asset Types to see the types of water mains available in the data model.
You can read the descriptions of all the asset types and select the one that seems most appropriate. In this case, you know that the mains in the source layer are either Transmission Main or Distribution Main in the data model. You can differentiate them by using the TRANSMISS flag.
Next, you will create a lookup to ensure that transmission mains get an asset type of 1 and distribution mains get an asset type of 2.
- In the water line workbook, click the TRANSMISS sheet. Add two columns, New Asset Type and Description.
- In the New Asset Type column, in cell C2, type 1, and in cell C3, type 2.
- In the Description column, in cell D2, type Transmission Main, and in cell D3, type Distribution Main.
- Click the Mapping sheet and find the assettype field. For the LookupSheet and LookupKeys columns, type TRANSMISS. For LookupValue, type New Asset Type.
- Save the workbook.
With the mappings you created, every feature in the main layer in the source database appears in the water main subtype of the water line layer in the target database. If the feature has a TRANSMISS value of Yes, its Asset Type value is Transmission Main(1), if it has a TRANSMISS value of No, its Asset Type value is Distribution Main(2).
Because the asset type is a required field that plays a key role in the utility network, you must make sure that each feature has the TRANSMISS field populated and that the values in it are correct. Missing or incorrect values will result in errors later when you build the network for the first time.
Now you have a fully populated data mapping workbook for water mains. The next step is to load the data into the utility network data model using the mappings and lookups that you have created.
Load data into the utility network
Now you will use the Esri Data Loading tools to run the data mapping workspace that you populated. This module is optional because it requires changing the default Python environment that is installed with ArcGIS Pro, which some users may not feel comfortable doing or may not have permission to perform.
However, if you can complete this module, you will not only be able to run the data mapping workspace you created, but you will also be able to create data loading workspaces for your own databases.
Check for data loading tools
To complete this section, you may need an administrator to help you install more software on your machine. Installing this solution requires adding a new Conda package to the Python environment for ArcGIS Pro. Because you cannot change the default environment for ArcGIS Pro, you will create a clone of the default environment. If you are not comfortable performing these steps on your own, you can have an administrator perform these steps for you.
- Open the Water Data Loading project in ArcGIS Pro, if necessary.
- On the ribbon, click the Analysis tab and in the Geoprocessing group, click Tools.
- In the Geoprocessing pane, click Toolboxes.
- Look through the list of toolboxes and find the ones that start with D.
If you see the Data Loading Tools toolbox in the list of toolboxes, the tools are already installed on your machine, and you can move on to the next section.
If you don’t see the Data Loading Tools toolbox, you must install them if you want to perform the loading operation.
Download the Esri Data Loading Tool solution
Next, you will install the Data Loading Tools solution. The first step in this process is to clone the default Python environment of ArcGIS Pro. All the steps are on the solutions page, but you can also access the steps directly in the tutorial.
- In ArcGIS Pro, on the ribbon, click the Project tab.
- On the New project pane, click Package Manager.
The Package Manager appears.
The Package Manager allows you to manage environments and packages. In this case, the Active Environment is the default arcgispro-py3 environment. This environment cannot be modified because ArcGIS Pro uses many of its components. You will clone the environment to create a copy that you can modify.
- Beside the Active Environment drop-down list, click settings.
- Click Clone Environment to create a copy of the default Python environment.
- Accept the default name and location for the cloned environment, and click OK.
Note:
You may need an administrator to help you perform these steps if you don’t have permission to install software on your machine.
Cloning the environment involves copying numerous Python files into a new folder, so it will take several minutes.
- Once the cloning is complete, click the cloned environment to select it.
- Click the More button for the new environment, and click Activate.
The activation process will take a minute or so. When it is finished, the cloned environment will have a green bar beside it, indicating that it is active.
- Click the Refresh package metadata button.
Refreshing the package metadata retrieves the latest set of available packages.
- Click OK
- Shut down ArcGIS Pro.
- Restart ArcGIS Pro and open the Water Data Loading Lesson project.
Now that you have cloned the default Python environment, you will install the data loading tools solution.
- In ArcGIS Pro, click the Project tab and click Package Manager.
The cloned environment is listed in the Active Environment box.
- In the Package Manager, click Add Packages to see the packages available for installation.
- In the search box, type dlt to find the dltsolutions package.
- Click Install.
- On the Install Package window, click I agree to the terms and conditions, and click Install.
- After the installation is complete, exit ArcGIS Pro.
- Start ArcGIS Pro and open the Water Data Loading Lesson project.
- Click the Project tab, click the Package Manager tab. In the Installed Packages list, scroll down and find the dltsolutions package.
- Click the back arrow to return to the project.
- On the ribbon, on the Analysis tab, click Tools. In the Geoprocessing pane, click Toolboxes and find the Data Loading Tools toolbox.
You have installed the Data Loading Tools toolbox and are now ready to migrate your data into the utility network.
Run the data migration
Next, you will run a data loading geoprocessing tool to convert the data from your water database into the utility network. First, you will verify the data paths.
- In File Explorer, browse to the \Water Data Loading Lesson\Blank Mappings folder and double-click the DataReference workbook to open it.
Note:
You populated these values earlier in the tutorial, but if you missed that step or are using a different database, you can correct the paths now.
The second row points to the source database, and the third row points to the target database.
- Return to ArcGIS Pro.
- In the Geoprocessing pane, expand Data Loading Tools and click Execute Data Load.
The Execute Data Load tool appears.
- For Data Reference Workbook, click the browse button. Browse to the tutorial data folder and expand the \Water Data Loading Lesson\Blank Mappings folders and double-click the DataReference workbook.
Note:
Your specific path may vary slightly since each package is uniquely named and placed in your Documents\ArcGIS\Projects folder when it's opened for the first time.
The Load Data To parameter is automatically set to Target, so you can leave that as is.
- Click Run to run the data load using the mappings you provided.
The tool completes in several minutes using the sample dataset. You can also run the tool using the complete data mappings to compare the output of your mappings against the output of the complete mappings.
Note:
If you received an error or warning, you can click the View Details button to see the messages. You can also use this to review the messages from the tool to get a better sense of how the conversion process works.
Review the results
In this section, you will review the results of the conversion using ArcGIS Pro to figure out whether you have mapped data correctly. Because you configured your data loading workspace to convert to an asset package database, you can use the Water Asset Package map included in the project to review the data.
- In ArcGIS Pro, in the Catalog pane, click the Project tab, if necessary.
- Under Maps, right-click Water Asset Package and click Open.
The map appears and the water assets that you loaded appear on the map.
- In the Contents pane, expand the Water Device layer.
- Right-click System Valve and choose Attribute Table.
- Review each of the rows and columns and review the migrated data.
Note:
For larger datasets, you can use ArcGIS Data Reviewer or the data engineering tools to confirm the completeness and correctness of your conversion.
- In the table, right-click the first valve and choose Zoom To.
The corresponding valve feature on the map is selected and zoomed to.
From here, you can zoom in and out on the map or review the attributes of nearby features to ensure the feature has been migrated correctly.
You have successfully migrated data from your source database into the target data model by mapping fields and creating lookups to ensure everything matched up between the two databases. If you find that some data is missing after the conversion or wasn’t converted correctly, adjust the data mapping workspace and rerun the conversion. If you see duplicate data after running the migration, revisit the first section of this module in which you set up target delete queries and enabled and disabled certain layers in the data reference workbook.