What are the best practices for mapping in Mapping Workbooks?

[VHolubec]

Hello,

Especially for smaller/small utilities – like city water companies, there is a phenomenon present, that the input feature layer contains different types of data - from structural and logical network or cross several asset groups - at once. While it could work for plain data model with just feature layers (point and polylines), this is not enough for Utility Network data model (in my case for Water Data Management UN Solution).

This approach leads to two situations:
1) The layer contains both – for example points of System Valves (Water Device -> System Valve -> System) and points of Valve Box (Structure Junctions -> Pipe Box -> Valve Box).
For this situation I have two solutions:

a) Separate features to standalone layers for Structure Junctions and Water devices. And then load it in standard way
b) Make the separation via query in DataReference excel file
With that in mind – are there any samples documentation for DataReference queries? Some tip and tricks? Or would you recommend some third way?

2) The second use case is more tricky – it coming from situation when the one point layer mapped to – for example Water device layer – contains assets cross several Asset groups – for example System Valves (Water Device -> System Valve -> System) and Fire Hydrant (Water Device -> Fire Hydrant -> Fire Hydrant) or Pressure Reducing Valve (Water Device -> pressure Valve -> Pressure Reducing).

How to solve this situations – I think separation on the layer level is possible, but I assume that this could be done in the Mapping Workbooks as well – but can´t find any sample/documentation how – with some Lookup, or Query? I also heard that expression input could consist more values, but how it works? What´s the best practices how to solve this situation in the Mapping Workbook xlsx?

For the beginners there is outstanding learn.arcgis.com lesson from @RobertKrisher, but these situations are not covered here.

I will be happy for any tips!

Thank you.

[TedHoward2 · Accepted solution]

Data Loading toolset is designed to prevent having to break out source data into chunks/multiple layers. There is some new documentation as of 3.3 that I would invite you to review: Data loading workspace concepts. But based on your questions there are two suggestions I can give:

1. To select a subset of a source table/layer, use SourceDefinitionQuery in the Data Reference workbook. This is expecting a SQL query, much like the "where clause" used widely in the geoprocessing tools. You can find a lot of SQL query examples here.

2. To target a specific asset group/asset type in which to load data, use the Expression column in a Mapping worksheet. In other words, directly enter a value into the Expression column that specifies the asset group you are loading data to.

As far a tips & tricks - there are some really great videos that cover advanced capabilities of the Data Loading toolset. These are a couple years old now but the workflows shown are still valid.

[VHolubec]

Hello @TedHoward2,

thank you for those resources! 

Just one additional question, just to be sure I understand it correctly - one Mapping Worksheet, can´t point to more AssetGroups - am I right? So if the source layer point to more asset group, the best way is to use query definition in Data Reference - as mentioned in the video here:

Duplicate the first initial record in the Excel for each asset type + duplicate the excels each with different name (so it´s not necessary to run the Create Data Loading Workspace once again)

Am I right? 

Thank you.

[TedHoward2]

one Mapping Worksheet, can´t point to more AssetGroups

 - You could use a lookup in a Mapping Workbook for loading data to multiple AssetGroups. Whether you should do that depends on if you need different field mapping per AssetGroup etc.

Duplicate the first initial record in the Excel for each asset type + duplicate the excels each with different name

- Yes you could do that. I would suggest consolidating where possible though. There's nothing wrong with using the following sql:  

assetgroup in (1,2,3)

[VHolubec]

Hello @TedHoward2,

thank you for answers. Instead of making more excels and use the query you mentioned (assetgroup in (1,2,3)) in DataReference excel, I´ve gone the way of consolidating you mentioned - and so the way of "double lookup", where in the list of values sheet I set the new fields for Asset Group alongside the Asset Type:

And then set the lookup for the Asset Type and Asset group.

I hope this workflow is correct and the most possible consolidated, right? I didn´t know that I can make "advanced lookup" and use both Asset Group and Asset Type in the value list above - if you @TedHoward2 or @RobertKrisher  would think about the next blog post - please consider to describe those tips and trick for data loading with more inspiring examples.

I have a feeling that users don´t know all the handy ways they could use there!

Thank you very much for help!