Skip navigation
All Places > Electric and Gas > Blog > Author: Joseph_Marsh-esristaff

***This workflow works with etlsolutions v0.5.2***

Click Here to learn more about benefits and how to get started with the Data Translation Tools

 

We have had requests for additional documentation and samples at the UC. A zip pro package has been attached to the thread.

 

We want to bring data from one file geodatabase to another file geodatabase. This is also known as Extract, Transfer, and Load (ETL). Let me set the stage.

 

We have a file geodatabase or fgdb for short. Let's call this fgdb, our source.  Inside this geodatabase, we have a feature class named Cars.  We have three subtypes in this feature class: Red, Blue and Green. There are domains assigned to each of these subtypes. I want all my data in this feature class to shuttle over to my target file geodatabase. This target fgdb has a feature class called Trucks with different schema. Schema is a term that includes attributes, data types, domains, and other data management concepts. The Trucks feature class has three subtypes as well, but they are different than those found in Cars. They are One, Two, and Three rather than Red, Blue and Green.

 

There are three ways to translate data with the workbook. The first method does a straight field mapping without any domains. The second method uses a sheet as a lookup table for domains. Finally, the last method uses a hard coded value to ‘burn in’ values. I will cover each method in this blog.

 

 

 

The first tool in our Data Translation toolbox is Create Mapping Workbooks. 

  • Open the GP Tool Create Mapping Workbooks tool within Data Translation toolbox.
  • Point each one of your source feature classes to its corresponding target feature class.
  • Specify the output folder for your mapping workbooks.
  • (Optional) Check the box to Calculate feature count statistics to generate information on what fields have populated data.

 

 

 

Once the tool has run, you will see a Points folder that contains a mapping workbook called Cars and a mapping.xlsx file which will be used later when using the Load Data from Workbook tool. Let's go into how to populate information into these excel workbooks.

 

Method One: Straight Field Mapping

 

Step 1:

Open the Cars workbook and navigate to the Mapping sheet.  We have columns for targetField, sourceField, and fieldType which are all system derived. The columns, expression, sheet, sheetKeys, and sheetValue are used for methods two and three. This default workbook is configured to transfer data to your new file geodatabase without any translations.

 

 

 

What this means is the domains will be mapped using their codes. For example, Red will not be mapped as anything since there isn't a 0 code in the domain "Type" of our target feature layer. Blue will be mapped as One since they share the domain code of 1. Green will be mapped as Two since they share the domain code of 2.

 

Step 2:

Before we use the Load Data from Workbook tool to Extract, Transfer and Load our data, it's time to inspect our mapping.xlsx workbook.

 

Each row in this workbook directs to the tool to: set the source database, set the target database, and set the lookup workbook. This first row was created when we pointed our Cars feature class to our Trucks feature class during the Create Mapping Workbooks section at the beginning of this workflow.

 

 

Step 3:

Now that I have inspected everything, it's time to run Load Data from Workbook tool.

  • Open the Load Data from Workbook tool in Data Translation toolbox.
  • Specify the location of the mapping workbook created from Create Mapping Workbooks tool.
  • (Optional) Truncate the target geodatabase before loading.

 

 

 

When we run this tool, we return values of 0, One, and Two.

 

 

Method Two: Field Mapping with Lookup

 

Step 1:

Open the Cars workbook and navigate to the Mapping sheet. In this sheet, we will define what sheets and columns to use as a lookup table. Since I am not using the sourceField for a straight mapping, I will remove Type from the sourceField column In this sheet, the values I have entered are highlighted. In the sheet column, "Type", highlighted in yellow, is used by the Load Data from Workbook tool to locate the correct excel sheet when mapping to Type.  In the sheetKeys column, "Type", highlighted in yellow, is used by the Load Data from Workbook tool to locate the correct column(s) on the Type tab when mapping to Type. In the sheetValue column, "NewType", highlighted in orange, is used by the Load Data from Workbook tool to locate which column to use as a lookup table on the Type tab.

 

 

Navigating to the Type tab, I have highlighted the Type column in yellow to show the relationship to the user-defined column in keys on the mapping tab previously mentioned.  I have also highlighted the user-defined column and values in orange. I entered the domain values 1, 2, and 3 in column C to map to Red, Blue, and Green, respectively.  I have also included their domain descriptions in column D. What I have accomplished in this workbook is creating a lookup table for old domain values to new domain values. Where Red Car was using a domain value of 0 to describe Red, Truck One uses a domain value of 1 to describe One.

 

 

Repeat steps 2 and 3 above from Method One

 

Here is our final output.

 

 

 

Method Three: Hard Code Burn-in

 

Step 1.

Open the Cars workbook and navigate to the Mapping sheet. In this sheet, remove all the values you entered in Method 2. We will simply enter in a hard-coded value of "3" into the expression column. This will burn-in the value of 3 for Red, Blue, and Green.

 

 

Repeat steps 2 and 3 above from Method One

 

Here is our final output.

 

When the power goes out, it is all hands on deck at a utility company. Everybody from all corners of the organization come together.  They assign damage assessment trucks to finance and transmission trading employees.  Lineman are working around the clock to get critical assets back up. With all this calamity, having real-time information to make decisions is key to success. Communicating information within the utility effectively, utilities can shave minutes to hours off of their recovery time.  Every minute matters when you have many customers without power and some of those customers require electricity to live.

Utility companies face significant challenges when the lights go out.  They need to be able to simultaneously manage their assets, their workers, and their customers. Using ArcGIS to store, analyze, and visualize data, employees from planning to operations to customer care can all work harmoniously from the same sheet of music. The ArcGIS Platform figuratively lights up the utility with GIS, allowing the utility to literally light up the community. 

Here is a new video showing Operations Dashboard for ArcGIS.  This easy to configure dashboard effectively tells the current scenario of where are the utility's damaged assets, where are their workers, and how many customers is the outage affecting.  Sharing information freely across any organization will improve operational efficiency and customer satisfaction.  Outage Management System - Real-Time Monitoring - YouTube 

 In recent years, severe storms have crippled major cities across the United States. These storms, consisting of events such as hurricanes, heavy rain and flooding, winter storms, and tropical storms, have left hundreds of thousands of people without power. Communicating information about power outages to a connected public, the media, and various agencies is a top priority for most utilities. This transparency promotes better communication and collaboration with your constituents.

Utility companies face significant public safety and public relations challenges when a network outage occurs. To meet regulatory requirements and address business needs, they must provide outage information to customers in a timely and accessible manner. When an outage occurs, customers typically want to know if the utility is aware of the outage and when will their power be restored. This information needs to be available even when the power is off.

Traditionally, most utility companies manage their public-facing outage viewers internally because they are a critical part of creating a positive customer experience. Trusting an outside company to manage these types of critical apps is often viewed as impractical and expensive. However, this type of implementation approach requires a significant infrastructure (hardware, software, network bandwidth) investment and related IT support staff during the worst possible storm event. In reality, this infrastructure may only be needed less than 20 percent of the time in any given year. As a result, this dedicated environment often sits idle during clear, blue sky days.

Esri's Outage Viewer offering, combined with our managed cloud services capabilities, makes it possible for utility companies to leverage the cloud. We can configure and host your outage viewer in a secure, scalable, and reliable environment, which will reduce your total cost of ownership (TCO) and enable you to maintain positive customer sentiment during storm events.

Many utilities are engaging their customers with successful implementations of Esri's Outage Viewer.  These online solutions are live 24/7/365 and can be viewed with the following links:

 

Additional information is attached.