Importing Demands From A Spreadsheet

  Applies To 
  Product(s): WaterCAD, WaterGEMS
  Version(s): V8 XM, V8i, CONNECT Edition
  Area:  Layout and Data Input
  Original Author: Jesse Dringoli, Bentley Technical Support Group

Overview


This Technote describes the process by which a user can import demand information from a spreadsheet using Modelbuilder, into Junctions or Hydrants. This article assumes Bentley WaterCAD or WaterGEMS V8i (08.11.XX.XX) or CONNECT Edition (10.XX.XX.XX). The process is slightly different in V8 XM (08.09.XX.XX).

NOTE: if you'd like to include a visual representation of the customers associated with each individual demand, use the Customer Meter element instead. See this Webinar  on the workflow of importing with Modelbuilder and using Loadbuilder to allocate to junctions.

IMPORTANT: if you are using the 64-bit version of WaterCAD or WaterGEMS and you encounter the error "Access Database Engine 2010 64-bit is required..." when attempting to follow this process in ModelBuilder, please see this article for a solution.

Background

In WaterCAD and WaterGEMS, demands can be entered as a base flow plus a pattern, or as a unit demand type and count. Also, multiple demands can be entered for each node (junction, hydrant, etc.). An exception would be the Customer Meter element, which is used for a 1:1 representation of all demands.

If your demand data is contained within an Excel Spreadsheet and you have a field that contains labels that match your model's node labels, you can use the Modelbuilder feature to import them. For example:

 

Note: If your demands are in shapefile form and you want to import them into junctions or hydrants, you should use the Loadbuilder tool, which can utilize the spatial information contained in the shapefile to assign demands, using many different methods. 

 

If you attempt to import this information using Modelbuilder, you may end up with the following unexpected results: 

This is due to the user selecting "junction" as the table type, which only provides read-only "demand" attributes to link your data to.  

Preparation

    1. First, you should of course ensure that all your junctions and other elements are present in the model. When we import the demands from the spreadsheet, they will automatically update these existing elements.
    2. Next, if you have not done so already, you'll need to set up any demand patterns that you'll be using. These are typically diurnal curves that adjust the base demand over the course of the EPS simulation. Go to Components > Patterns. Create a new hydraulic pattern and enter the starting multiplier along with the table of time/multiplier: If you have a lot of demand patterns, see this article for how to import those with modelbuilder: Importing Demand Patterns Using Modelbuilder

 

Open your source spreadsheet and ensure that it is formatted properly. Ensure that you have columns for the junction label, base demand and pattern, with a header at the top (see first illustration further above.) Ensure that the labels for your patterns match the ones in the source file that contains the loading data. If you would like to use a fixed pattern, simply enter "Fixed" under the pattern column for those junctions. (or, omit the pattern column altogether, if all junctions will have fixed demands.) 

Properly Importing your Demands in Modelbuilder 

Note that when you import demands using this process, they will override any previous demands assigned to those nodes included in the spreadsheet. So, this process cannot be used to 'update' an individual demand for nodes that have multiple items in the demand collection. All of the demands that should be present for a particular manhole should be included in the spreadsheet when using this process. 

Note: if you have WaterCAD or WaterGEMS V8 XM Edition (08.09.XX.XX) then the following Modelbuilder steps will be slightly different, as that version is older. The basic process is the same though.

 

1) Start a new modelbuilder run by going to Tools > Modelbuilder and clicking the white paper button. Select your data source type and then the file itself. Click the checbox next to the layer/worksheet that contains the data. You can click "show preview" to check the data in the later selected:

 

 

2) Click Next and uncheck the "create nodes if none found at pipe endpoint" check box:

 

 

3) Click Next and uncheck everything except for "update existing objects in destination if present in source". This is because we are updating elements, not creating or deleting them.

 

 

4) Click Next and accept the default "Current Scenario" and "Label". This is because we will be updating our current scenario and using the WaterCAD/GEMS label field.

 

 

5) Click Next to display the field mappings. This is an important step. First, click the layer on the left side (representing the worksheet containing your demands) and select "Junction - Demand collection" as the "Table Type". This is because the data that we are updating is not directly within the junction itself, but within the junctions' demand collection. A collection means that there are multiple items for each individual junction (composite demands are possible.) For example, you may have noticed that there are two individual demands for J-1 in the source spreadsheet shown in the first illustration. This is a composite demand.

For the "Key fields", select the column header that you used for the labels (most likely "Label".) This is used to link the demand entries with the junctions in your model.

In the bottom right corner, you must map fields in your spreadsheet to fields in WaterCAD/GEMS. This is because WaterCAD/GEMS cannot interpret your labels. For example, if you had a column called "Base_flow", there is no way for it to know that this means the Base Demand.

'Demand (Base)' - this should be mapped to your base demand column. Ensure that the correct units are selected.

'Pattern (Demand) (Label)' - this should be mapped to your pattern column 

 

6) Click "next" and choose "yes" when prompted to build the model. You can uncheck the options concerning selection set creation.

In the Modelbuilder Summary, you should receive a message stating that a certain number of your junctions were updated.

7) Close Modelbuilder and examine your demands. You can use the Demand Control Center (under the Tools menu) to easily view all demands:

 

What if I have unit demands?

If you have unit demands (unit demand label + count), you'll need to do a separate Modelbuilder run using a slightly different process. The unit demand information will need to be in a different worksheet in your spreadsheet (or a different spreadsheet altogether) with columns for the junction label, Unit demand, unit demand count and Pattern:

     Instead of defining patterns in your model, you'll need to first import the Unit Demand types, under Components > Unit Demands. Either click the "new" button and define them, or import some commonly used ones from the engineering libraries (purple book icon)

     
    In the last Modelbuilder step, you would select "Junction - unit demand collection" as the table type, instead of "Junction - demand collection". You would map the following fields


    'Unit Demand (Label)' - this should be mapped to the column that represents the type of unit demand.

    'Number of Unit Demands' - this should be mapped to the column that represents the 'count' of units. For example, if the unit demand is "Residential" with it's demand representing 1 house, then a 'count' of '10' would represent 10 houses. The program will multiply the unit demand by the count to acheive the total unit load.

    'Pattern (Demand) (Label)' - this should be mapped to the column that represents the pattern associated with each unit demand.

     


    After Modelbuilder imports the data, you can view your unit loads in the Unit Demand Control Center, under the Tools menu. This tool will also show you the computed demand based on the unit demand and the "number of unit demands" (the count.)

    How can I place demands on hydrants?

    To import demands into hydrants instead of junctions, you would select "Hydrant - Demand collection" as the table type, instead of "Junction - Demand collection". Or, "Hydrant - Unit demand collection" if using unit loads. However, hydrants usually represent demands that would only occur during a fire (using the automated fireflow routine or emitter coefficient). So, demands are typically not assigned directly to them.

    How can I import demand patterns, too?

    Automated importing of demand patterns is only recommended if you have a large number of them that would otherwise take too long to enter (even with copy/pasting the columns of data). For steps to use Modelbuilder to import demand patterns, see this article:

    Importing Demand Patterns Using Modelbuilder

    How can I see every customer meter in the model?

    If you want to see every customer meter, instead of lumping multiple demands into a single node, use the Customer Meter element. See this Webinar  on the workflow of importing with Modelbuilder and using Loadbuilder to allocate to junctions.

    How can I update demands?

    If demand information changes in your source file and you need to update the model, one approach is to create a new child demand alternative, assign it to a scenario, remove all demands (open the Demand Control Center and select/delete all rows), then import the new demands using the process outlined further above.

    If you need to update only the pattern assignments but you only have information on the new pattern required for each node, consider copy/pasting in the Demand Control Center. If there is only one individual demand entry per junction, sort the source data and the Demand Control Center by label (so they are both sorted the same way), confirm that the number of rows matches, select all the pattern cells in your source spreadsheet and press CTRL+C to copy, then right click on the pattern column header in the Demand Control Center, choose "select column" and press CTRL+V to paste. Note that the pattern labels will need to match and the patterns already need to be available in the pattern manager. 

    See Also

    Can demand data be exported to an Excel file for use in another model?

    Importing Demand Patterns Using Modelbuilder

    Automatically import hourly AMI data to Customer Meters and Patterns

    Article In Other Languages

    Recommended
    Related