Download - Performing Data Migration
US Army Corps of EngineersBUILDING STRONG®
Performing Data Migration
USACE SDSFIE TrainingPrerequisites: Implementing a Local Adaptation
BUILDING STRONG®
Video sequence
2 of 63
BUILDING STRONG®
Objectives Understanding the data migration process Understanding how to prepare for data migration Understanding the ESRI-based options for the data
migration process Understanding how to use the Simple Data Loader for
semi-automated data migration Understanding how to use the PLTS Data Loader option
to perform automated data migration Understanding problems with data loading process, and
how to address them
3 of 63
BUILDING STRONG®
AN OVERVIEW OF THE DATA MIGRATION PROCESS
4 of 63
BUILDING STRONG®
The SDSFIEData Migration Process
5 of 63
Current DistrictSource Data Set
Future DistrictTarget Data Set
Source Data Dictionary
TargetData Dictionary
CrosswalkTables
Data Migraton
BUILDING STRONG®
The SDSFIEData Migration Process
6 of 63
Current DistrictSource Data Set
Future DistrictTarget Data Set
CrosswalkTables
Data Migraton
The Crosswalk plays a key role in data migration by guiding the data loading. There are typically up to 3 levels of Crosswalk tables:
1.Feature Class
2.Attribute
3.Enumerant
Our demonstrations use 2 levels for simplicity
BUILDING STRONG®
Automated Data Loading
7 of 63
Source Data Set
Target Data Set
Process moves through the highest level table, feature class by feature class
Each feature class mapping pair is referenced, one-by-one
BUILDING STRONG®
Automated Data Loading
8 of 63
Source Data Set
Target Data Set
When migrating a specific feature class mapping pair (ID=2), the software accesses the set of attribute mapping pairs (IDs = 9 – 16) unique to that feature class mapping pair
BUILDING STRONG®
Automated Data Loading
9 of 63
Source Data Set
Target Data Set
For attribute mapping pairs with a source and target enumeration, the software accesses the set of enumerant mapping pairs (IDs = 1 – 3) unique to that attribute pair (ID = 11)
BUILDING STRONG®
PREPARING FOR DATA MIGRATION
10 of 63
BUILDING STRONG®
Migration Preparation Checklist Source geodatabase (populated), consistent with the
Source Data Dictionary used in the Crosswalk creation process
Target geodatabase (empty), compliant to your adaptation schema, based on the Target side of your Crosswalk
Crosswalk, with element-level mappings between your Source and your Target schemas
Software to support the data loading process Knowledge base and/or support for any issues you may
encounter
11 of 63
BUILDING STRONG®
ESRI BASED OPTIONS FOR DATA MIGRATION
12 of 63
BUILDING STRONG®
ESRI Based Data Loading Production Line Tool Set (PLTS)
► Fully automated data loading, run from ArcCatalog► Requires simple reformatting of your Crosswalk tables► Best for data migrations involving many feature classes► Handles enumerant mappings, for attributes with enumerations► Requires additional license for extension
Simple Data Loader► Semi-Automated data loading, run from ArcCatalog► Crosswalk tables used as reference for attribute mappings► Best for data migrations involving relatively few feature classes► Does not require additional license, but does not handle domain mappings
Others► ArcMap Object Loader, run from in edit session► Safe Software FME (Feature Manipulation Engine)
13 of 63
BUILDING STRONG®
USING THE SIMPLE DATA LOADER
14 of 63
BUILDING STRONG®
Using the Simple Data Loader
15 of 63
Right click target feature class Select Load > Load Data Press the Next button Click folder icon:
► Navigate to and select source feature class, then press Open
Press the Add button Check for file accuracy Press the Next button In attribute mapping window:
► Check each Source field, and if incorrectly matched or unmatched:• Click in that Source field• Select correct match from list
► Press the Next button, when ready► Press the Next button► Read Summary, and press Finish
No data present in Preview of target feature class!
BUILDING STRONG®
Using the Simple Data Loader Select Load > Load Data
16 of 63
BUILDING STRONG®
Using the Simple Data Loader
Press the Next button
17 of 63
BUILDING STRONG®
Using the Simple Data Loader
Click folder icon:
18 of 63
BUILDING STRONG®
Using the Simple Data Loader
► Navigate to and select source feature class, then press Open
19 of 63
BUILDING STRONG®
Using the Simple Data Loader
Press the Add button
20 of 63
BUILDING STRONG®
Using the Simple Data Loader
Check for file accuracy, and then press the Next button
21 of 63
BUILDING STRONG®
Using the Simple Data Loader
Press the Next button
22 of 63
BUILDING STRONG®
Using the Simple Data Loader
In attribute mapping window:► Check each Source field, and if
incorrectly matched or unmatched:• Click in that Source field
23 of 63
BUILDING STRONG®
Using the Simple Data Loader
► Check each Source field, and if incorrectly matched or unmatched:
• Select correct match from list
Use your attribute Crosswalk Tables to make
the correct selection!
24 of 63
BUILDING STRONG®
Using the Simple Data Loader
► Press the Next button, when ready
25 of 63
BUILDING STRONG®
Using the Simple Data Loader
► Press the Next button
26 of 63
BUILDING STRONG®
Using the Simple Data Loader
► Read Summary, and press Finish
27 of 63
BUILDING STRONG®
Using the Simple Data Loader Repeat steps, starting with next
target feature class
Data now present in Preview of target feature class!
28 of 63
BUILDING STRONG®
USING THE PLTS DATA LOADER TOOL
29 of 63
BUILDING STRONG®
The PLTS Crosswalk Tables
PLTS requires a special format for Crosswalk tables Excel workbook format, “XREF.xlsx” Each XREF workbook contains 3 spreadsheets:
► Dataset Mapping – source to target feature classes► Field Mapping – source to target attributes► Update Values – source to target enumerants (domain values)
Reformatting is very straightforward
30 of 63
BUILDING STRONG®
PLTS Preparation: Reformatting Crosswalk Tables Download PLTS_XREF_Template.xlsx accessed through
USACE component page of sdsfieonline.org Download PLTS_XREF_Example.xlsx (same location) Open _Example workbook, and familiarize yourself with
structure Open _Template workbook Open your Crosswalk file Before starting, note that template headings (Row 1) in
each of the 3 spreadsheets cannot be altered!
31 of 63
BUILDING STRONG®
Reformatting Crosswalk Tables: Copying and Pasting
Start with Feature Type mappings in your Crosswalk file►Note, again, that template headings (Row 1) cannot be altered!►Select and Copy block of feature types from Source Name column►Click in Cell B2 in XREF Dataset Mappings sheet, and Paste►Select and Copy block of feature types from Target Name column►Click in Cell C2 in XREF Dataset Mappings sheet, and Paste
Repeat process for Attribute mappings spreadsheet, pasting into Field Mappings spreadsheet (into Cells C2 and D2, for source and target attributes, respectively)
Repeat process for enumerants, pasting into Update Values spreadsheet (into Cells C2 and D2, for source and target enumerants, respectively)
32 of 63
BUILDING STRONG®
Reformatting Crosswalk Tables: Copying and Pasting
33 of 63
District Crosswalk workbook
PLTS XREF workbook
Do not copy headers!
BUILDING STRONG®
Reformatting Crosswalk Tables: Understanding Table Hierarchy Look at _Example workbook
► Pick a feature class mapping, noting the ID number► Find the set of SourceField attributes in the Field Mapping sheet
that have the same ID number (in the DatasetMappingId field) as the feature class you picked; note this relationship
► Pick an attribute mapping that involves a enumeration (domain), and note the ID number
► Find the set of FromValue records in the Update Values sheet that have the same ID number (in the FieldMappingId field) as the attribute you picked; note this relationship
34 of 63
BUILDING STRONG®
Reformatting Crosswalk Tables: Updating the IDs
Dataset Mapping spreadsheet► Make sure every feature class mapping row has an ID, and that
these numbers start in Cell A2 with “1” and continue sequentially downward
Field Mapping spreadsheet► Make sure every attribute mapping row has an ID, and that
these numbers start in Cell A2 with “1” and continue sequentially downward
► Fill in DatasetMappingId values for correct feature type (hint: paste in the feature type names from your Crosswalk, and then convert to the correct numbers)
35 of 63
BUILDING STRONG®
Reformatting Crosswalk Tables: Updating the IDs
Update Values spreadsheet► Make sure every eunumerant mapping row has an ID, and that
these numbers start in Cell A2 with “1” and continue sequentially downward
► Fill in FieldMappingId values for correct attribute (hint: paste in the attribute names from your Crosswalk, and then convert to the correct numbers)
► Again, examine structure in the _Example file, and verify that you have done this correctly
► Also, revisit slides 7 – 9 in this Video!
36 of 63
BUILDING STRONG®
The Three PLTS Tools
37 of 63
PLTS Create Cross-reference► Creates cross-reference in a personal geodatabase (GDB)► Utilizes the reformatted Crosswalk, now XREF.xlsx► Needs to know names and locations of source and target GDB
PLTS Preload Validate► Checks validity of all inputs to the data loading process► Needs to know names and locations of source and target GDB► Needs to know name and location of cross-reference PGDB► Outputs text file with summary information
PLTS Data Loader► Loads the data from source GDB to target GDB► Needs to know names and locations all 3 inputs► Outputs text file with loading success record counts (of total records)
for each feature class
BUILDING STRONG®
Using the PLTS Data LoaderConditions for Starting PLTS sequencePopulated Source data presentEmpty Target data presentXREF.xlsx present
38 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Create Cross-reference Tool Press PLTS Create Cross-
reference tool button
39 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Create Cross-reference Tool
Press “… “ button to navigate to and select source GDB
40 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Create Cross-reference Tool
► Select source GDB, & press Select
41 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Create Cross-reference Tool
Press “… “ button to navigate to folder in which Cross-reference PGDB will be created
42 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Create Cross-reference Tool
► Name PGDB, and press Save
43 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Create Cross-reference Tool
Specify Target and Mapping File► Repeat specification for Target GDB► Check Import mapping from file box► Repeat specification for XREF.xlsx
Check all four file specifications, and then press OK
44 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Create Cross-reference Tool
Press OK
45 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Create Cross-reference Tool
Cross-reference PGDB has been created
46 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Preload Validate Tool Press PLTS Preload Validate tool button
47 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Preload Validate Tool
Press “… “ button to navigate to folder in which Log File will be created
48 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Preload Validate Tool
► Specify name for file, & press Save
49 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Preload Validate Tool
Specify other file inputs► Repeat specification for Target GDB► Repeat specification for Source GDB► Repeat specification for Cross-
reference PGDB Check all four file specifications,
and then press OK
50 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Preload Validate Tool
Press OK
51 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Preload Validate Tool
Check Log File to ensure that all references and mappings validated, then close Log File
52 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Data Loader Tool Press PLTS Data Loader button
53 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Data Loader Tool
Specify All Inputs► Specify Cross-reference PGDB► Specify Source GDB► Specify Target GDB
Specify location and name for log file output
54 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Data Loader Tool
Check all four file specifications, and then press Next
55 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Data Loader Tool
Press Next
56 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Data Loader Tool
Press Finish
57 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Data Loader Tool
Watch loading process status, and press Yes when complete
58 of 63
BUILDING STRONG®
Using the PLTS Data LoaderPLTS Data Loader Tool
Check Log File to ensure that all feature classes loaded correctly, then close Log File
59 of 63
BUILDING STRONG®
TROUBLE-SHOOTING DATA LOADING ISSUES
60 of 63
BUILDING STRONG®
Trouble-shooting Incomplete Data Loading
Review of Slide 13, Video 6: Data Type Mismatches Attributes with Text (String) data type
► Look for field length differences for attrbutes with domains; some were shortened from 3.0 to 3.1 Gold
► When going from a larger to smaller text field, you cannot migrate records, even if the source field is unpopulated or has a value of “0”
► You can migrate records for a particular field, when going from a larger to smaller text field, if the source is <Null>.
Unless source field is <Null>, you cannot map to a GUID (e.g., sdsID), nor to a Blob data type
61 of 63
BUILDING STRONG®
Review
62 of 63
Understanding the data migration process Understanding how to prepare for data migration Understanding the ESRI-based options for the data
migration process Understanding how to use the Simple Data Loader for
semi-automated data migration Understanding how to use the PLTS Data Loader option
to perform automated data migration Understanding problems with data loading process, and
how to address them
BUILDING STRONG®
Questions?
This is the final video in the training series
Contact [email protected] with comments or for additional information
63 of 63