You must have administrator rights to access this feature.
You must have a Cartegraph OMS advanced feature extension to access this feature.
After running Scenario Builder in Cartegraph, often it is helpful to take the resulting work plan and view the recommended activities per asset on a map. Export the work activity list out of Cartegraph, and use ArcGIS to visualize the results. These instructions walk you through the steps to do this; from data export to the eventual visualization of the results on an ArcGIS map.
These are the prerequisites to view scenarios on a map:
- The asset type the scenario is run against must have a corresponding GIS feature layer/class. This is used to join the scenario results.
- The GIS feature layer/class must have a unique ID per asset record that matches the ID of corresponding asset records in Cartegraph.
- Access to ArcGIS Desktop software, specifically ArcMap and ArcCatalog.
- Access to Microsoft Excel, or a comparable spreadsheet software package.
- Basic understanding of ArcGIS Desktop software.
The overall process includes:
- Export Activities with Asset Attributes.
- Organize the Activity List by Plan Years using either Excel and ArcCatalog.
- Join the exported data in ArcMap.
Export Activities with Asset Attributes
- In Cartegraph, click >Scenario Builder.
- From the Scenario list, select the scenario you wish to visualize.
- Click Actions>Export Activities with Asset Attributes.
- A comma delimited file containing the Activity list recommendation for the scenario is exported and saved to your computer.
Organize the Activity List by Plan Years
At this point, you need to break the Activity List into separate Excel workbooks by Plan Year to allow for easier joins in ArcGIS. Use either Microsoft Excel or ArcCatalog/ArcToolbox.
- Open the exported CSV file in Microsoft Excel.
- For all column names, go through and remove any spaces from the column names by either deleting them or replacing them with an underscore _.
- Enable Filtering. In Excel 2010 and later, the Filter button is under the Data ribbon bar.
- With Filtering enabled, drop-down filter options display for each column. On the Plan Year column, uncheck all plan years except Year 1.
- The Activity list is now filtered down to just Plan Year 1. Do a quick visual inspection of the data to ensure this.
- Select all rows and columns of the filtered list, and Copy.
- Click on the new worksheet icon on the worksheet tabs at the bottom of the grid.
- Double-click on the new worksheet tab, and change the name to Plan Year 1.
- Click on the A1 cell of the new worksheet, and Paste the contents of the copy from Step 4
- Highlight the Asset ID column, right-click and select Format Cells, change the cell category to Text. The Asset ID needs to be a Text field for joins in future steps.
- You now have the suggested activities for Plan Year 1.
- Repeat Steps 4-11 for each Plan Year your Activity list contains.
- Once each Plan Year has its own worksheet, cycle though and save off each worksheet as its own Excel Workbook.
- Right-click on each worksheet tab, and select Move or Copy.
- Switch the To book: option to (new book).
- Check Create a copy.
- Do this for each Plan Year worksheet. This creates individual Excel Workbooks for each plan years.
- The workbooks display as separate instances of Excel. Go to each instance of Excel and perform a Save As and name the Excel Workbook with the appropriate Plan Year name.
- Open the exported CSV file in Microsoft Excel.
- In a geodatabase, create a table by right-clicking and select New>Table.
- Name the table after the Plan Year. For example, PlanYear1.
- Use the Import table schema function. Select the CSV file as the source of the table schema. This adds all the field names any types automatically to the new table.
- Make sure to change the field type for Asset ID to Text. The ID field needs to be a text field for the join to occur at the end of this process.
- Click Finish to create the table.
- Repeat Steps 2-4 for each plan year you wish to map.
- Once the Plan Year tables are created, right-click on the PlanYear1 table and select Load>Load Data the Simple Data Loader displays.
- Select the Activity List CSV file as the source data (input data), and click Add it.
- Click Next, twice to skip the subtype data load screen.
- On the field mapping screen, make sure the correct fields are matched idenically to each other. Click Next.
- For the options of what data to load, select the Load only the rows that satisfy a query option and click Query Builder.
- Build a filter statement that reads like "Plan Year" = 1. This filters the records down to just Year 1 records to load into this table. Click Next.
- Review the summary of the wizard steps, and click Finish.
- Repeat Steps 5-11 for each plan year. Make sure in Step 13 to change the filter statement to reflect the correct plan year.
Join Exported Data in ArcMap
- Open the ArcMap application.
- Add the following layers using the Add Data tool: the asset feature class your activity plan is based on and all the plan year tables or Excel files (depending on what path you took above) you created in the previous steps.
- Right-click on the feature layer, select Joins and Relates>Join.
- Select Join Attributes from a Table.
- For Choose the field in this layer that the join will be based on, select your unique asset ID field.
- For Choose the table to join to this layer, or load the table from a disk, select PlanYear1.
- For Choose the field in the table to base the join on, select the unique asset ID (Asset ID).
- Make sure the Keep all records option is selected under Join Options.
- Click Validate Join to make sure everything is working properly.
- Click OK. The Plan Year Activity list is associated against the actual asset feature layer.
- Right-click on the Feature layer you just joined, and select Data>Export Data.
- Save the joined feature layer as a new feature class stored in your geodatabase and name it appropriately. For example, PlanYear1Signs.
- Right-click on the original asset feature layer, select Joins and Relates>Remove Joins>Remove all Joins.
- Repeat Steps 2-12 for each plan year.
You now have created a feature class for each plan year of your scenario that can be added to a map. See Esri help for more information on ArcCatalog and ArcMap.