Data Management with WorkIT 2

1. Data Management with WorkIT 2 NCC uses a process called WorkIT for managing both GIS and asset management data. WorkIT was initially developed during the mid 1990�s with assistance from e-Spatial. This session will give an overview of WorkIT and what changes we have made over the last few years, and how these changes help us to more efficiently manage our data and future proof ourselves. NCC uses a process called WorkIT for managing both GIS and asset management data. WorkIT was initially developed during the mid 1990�s with assistance from e-Spatial. This session will give an overview of WorkIT and what changes we have made over the last few years, and how these changes help us to more efficiently manage our data and future proof ourselves.

2. Agenda What�s the problem? What is WorkIT? How did it work in practice? What changed in WorkIT 2? How does it work now? The Good and the Bad What Next? Technical Slides (Time Permitting)

3. What�s the problem? Our data comes from multiple sources, goes through a maintenance process, resulting in the GIS and Asset Management Systems being maintained. But because some of the data is needed in both the GIS and asset system, a mechanism needs to be in place to keep common data within the two systems synchronised. So if an edit is made to one system, the same change is reflected in the other system.Our data comes from multiple sources, goes through a maintenance process, resulting in the GIS and Asset Management Systems being maintained. But because some of the data is needed in both the GIS and asset system, a mechanism needs to be in place to keep common data within the two systems synchronised. So if an edit is made to one system, the same change is reflected in the other system.

4. The WorkIT Solution WorkIT was developed to solve a few problems. These included issues around * managing both GIS and Asset Management Data * preventing the GIS and AMS datasets from diverging as data is maintained (when an edit is made to one system but not the other) * maintaining linkages between features in both systems * incorporating data from external sources * maintaining spatial data accuracy and recording it * auditing user changes especially around financial information Using WorkIT, we solved these problems. Using the GIS as the ONLY source of data for the Asset Management System, we removed the issues involved with synchronising the two datasets. But within our model, the GIS contains only linking data, all of the asset data is stored within the Asset Management System. To get a full picture of the asset within the GIS, the asset system and GIS must be joined together. WorkIT was developed to solve a few problems. These included issues around * managing both GIS and Asset Management Data * preventing the GIS and AMS datasets from diverging as data is maintained (when an edit is made to one system but not the other) * maintaining linkages between features in both systems * incorporating data from external sources * maintaining spatial data accuracy and recording it * auditing user changes especially around financial information Using WorkIT, we solved these problems. Using the GIS as the ONLY source of data for the Asset Management System, we removed the issues involved with synchronising the two datasets. But within our model, the GIS contains only linking data, all of the asset data is stored within the Asset Management System. To get a full picture of the asset within the GIS, the asset system and GIS must be joined together.

5. What is WorkIT? WorkIT is a set of policies, procedures, specification and software tools. The policies and procedures give us the business rules on how the data is managed. The specifications give us the rules around how the software tools should behave. The user workflow is then controlled by the Management Interface which ensures the business rules are adhered to, controls the GIS application through customisations, and the data flow through to the AMS. The initial release of WorkIT was built around 1997 � 98 and is still running for Waste Water and Drainage assets until their migration to our new solution is completed. WorkIT is a set of policies, procedures, specification and software tools. The policies and procedures give us the business rules on how the data is managed. The specifications give us the rules around how the software tools should behave. The user workflow is then controlled by the Management Interface which ensures the business rules are adhered to, controls the GIS application through customisations, and the data flow through to the AMS. The initial release of WorkIT was built around 1997 � 98 and is still running for Waste Water and Drainage assets until their migration to our new solution is completed.

6. How did it work in practice? The initial version of WorkIT provided the management interface and a scripted process for migrating data from the GIS to the asset system. But there were a lot of manual processes, mainly due to software limitations, which prevented a streamlined workflow. WorkIT defined the rules and practices around these manual processes. The GIS required customisation to enforce the business rules required by both the Council and the asset system, and to provide CAD like tools within the GIS. Both the GIS and asset data were stored in a MS SQL Server database and a link is maintained between the two datasets so that the non-spatial data was all stored within the asset system and only the link and spatial components were within the GIS tables. This means that to get a complete picture of an asset, you must join the two datasets together. The initial version of WorkIT provided the management interface and a scripted process for migrating data from the GIS to the asset system. But there were a lot of manual processes, mainly due to software limitations, which prevented a streamlined workflow. WorkIT defined the rules and practices around these manual processes. The GIS required customisation to enforce the business rules required by both the Council and the asset system, and to provide CAD like tools within the GIS. Both the GIS and asset data were stored in a MS SQL Server database and a link is maintained between the two datasets so that the non-spatial data was all stored within the asset system and only the link and spatial components were within the GIS tables. This means that to get a complete picture of an asset, you must join the two datasets together.

7. What changed in WorkIT 2 Around 2004 - 2005, Council staff became aware that the software components of WorkIT were going to need to be replaced, and that most of the customisations would need re-writing. So we took a few years to gauge what was happening and where companies were taking their products. During this time we also reviewed the concepts, policies and procedures around WorkIT. We discovered that after more than 15 years, the concepts hadn�t significantly changed, but some of our requirements had changed a little. As part of the review we looked at what software we had, what we used (not always the same thing), and what was available. As we reviewed our solution, some of the key the decisions we made before we started were: * that nothing was sacred (any piece of the solution was able to be replaced) * there were no favourites * all the products had to compete on an even par and on their own merits Some key requirements that we wanted to introduce were: * build a single authoritative data repository, so all our data was in one place * each product needed to be able to integrate with other aspects of the solution * we wanted to re-use as much data as possible without any re-work. This included being able to use the survey data to produce as-built drawings, and then re-use the information for asset maintenance. * a flexible data model. We wanted to be able to extend our functionality, but to do this within a single asset repository means that the asset system had to be flexible enough to allow for the fields we needed to add The resulting solution was to create a common CAD/GIS framework by standardising our CAD, GIS, Engineering, and Survey tools on software products provided by Bentley Systems, replacing Microsoft SQL Server with Oracle, and replacing our asset management system with Accela AMS. We also updated the management interface and changed it to a web based solution. Around 2004 - 2005, Council staff became aware that the software components of WorkIT were going to need to be replaced, and that most of the customisations would need re-writing. So we took a few years to gauge what was happening and where companies were taking their products. During this time we also reviewed the concepts, policies and procedures around WorkIT. We discovered that after more than 15 years, the concepts hadn�t significantly changed, but some of our requirements had changed a little. As part of the review we looked at what software we had, what we used (not always the same thing), and what was available. As we reviewed our solution, some of the key the decisions we made before we started were: * that nothing was sacred (any piece of the solution was able to be replaced) * there were no favourites * all the products had to compete on an even par and on their own merits Some key requirements that we wanted to introduce were: * build a single authoritative data repository, so all our data was in one place * each product needed to be able to integrate with other aspects of the solution * we wanted to re-use as much data as possible without any re-work. This included being able to use the survey data to produce as-built drawings, and then re-use the information for asset maintenance. * a flexible data model. We wanted to be able to extend our functionality, but to do this within a single asset repository means that the asset system had to be flexible enough to allow for the fields we needed to add The resulting solution was to create a common CAD/GIS framework by standardising our CAD, GIS, Engineering, and Survey tools on software products provided by Bentley Systems, replacing Microsoft SQL Server with Oracle, and replacing our asset management system with Accela AMS. We also updated the management interface and changed it to a web based solution.

8. Asset Management Systems We wanted to use web based solutions as much as possible, and we wanted solutions that could integrate. We went from a client based Toolbar application and asset system, to web based solutions for both. This achieved several things for us: Active Directory User Authentication, we no longer need to have separate password control or generic logins. The client PC doesn�t need special configuration or software We can integrate with other solutions (e.g. Intranet) Simpler interface for asset managers We wanted to use web based solutions as much as possible, and we wanted solutions that could integrate. We went from a client based Toolbar application and asset system, to web based solutions for both. This achieved several things for us: Active Directory User Authentication, we no longer need to have separate password control or generic logins. The client PC doesn�t need special configuration or software We can integrate with other solutions (e.g. Intranet) Simpler interface for asset managers

9. A Common CAD/GIS Framework Maximum data re-use Less user training Common file format Lower Costs Increased Functionality Increased Productivity We used software from Bentley Systems to create a common CAD/GIS framework. This enabled us to: Maximise the re-use all our existing data and new data without any transformation. Maximise our user training, we no longer need our users to learn multiple software packages as the key tools are the same or similar between products. Have a common file format, each Bentley product may have additional supporting files, but the core data is within the DGN file or Oracle database which is able to be used by the other products. Lower costs, by standardising we have been able to negotiate a 6 year fixed price contract with Bentley Systems at a similar cost to what we were previously spending. This has enabled us to not only increase our license count, but also access a library of over 400 software products that allows us to select the best tool for the job. Increase Functionality Increase Productivity Due to the licensing arrangement, we have been able to access products that were not previously viable due to cost (like the Haestad Methods Suite). We used software from Bentley Systems to create a common CAD/GIS framework. This enabled us to: Maximise the re-use all our existing data and new data without any transformation. Maximise our user training, we no longer need our users to learn multiple software packages as the key tools are the same or similar between products. Have a common file format, each Bentley product may have additional supporting files, but the core data is within the DGN file or Oracle database which is able to be used by the other products. Lower costs, by standardising we have been able to negotiate a 6 year fixed price contract with Bentley Systems at a similar cost to what we were previously spending. This has enabled us to not only increase our license count, but also access a library of over 400 software products that allows us to select the best tool for the job. Increase Functionality Increase Productivity Due to the licensing arrangement, we have been able to access products that were not previously viable due to cost (like the Haestad Methods Suite).

10. Technology Changes WorkIT 1 Delphi MapInfo MapBasic MS SQL Server (with MapInfo SpatialWare) WorkIT 2 Java VBA Oracle .Net APIs (CAD / GIS) Web Services (AMS) XML From a software perspective we were starting again, this enabled us to implement old ideas with new technologies and new methods. We have used Java to build the web based Tool Bar application, VBA for the minor tweaks to the GIS environment, and Oracle as the backend database. We now have .Net APIs available for our CAD / GIS environment and Web Services for managing data in our asset management system. We no longer need to hardcode business logic, topology rules, or forms within our GIS environment, this is now all XML based. From a software perspective we were starting again, this enabled us to implement old ideas with new technologies and new methods. We have used Java to build the web based Tool Bar application, VBA for the minor tweaks to the GIS environment, and Oracle as the backend database. We now have .Net APIs available for our CAD / GIS environment and Web Services for managing data in our asset management system. We no longer need to hardcode business logic, topology rules, or forms within our GIS environment, this is now all XML based.

11. How does it work now? Manual re-entry is no longer required Scripted processes replaced by web services Simplified work flow With this upgrade, we have removed the manual processes from our workflow, users simply attach the file you want to use and copy the data into the GIS. The data can be from Survey tools, CAD, other GIS formats. We have been able to remove the issues around symbology and level names used in CAD files from external suppliers. We replaced the scripted process with web services that users can run from the management interface. Our business logic has been moved to the database and we still have a full audit of all changes that are made. With this upgrade, we have removed the manual processes from our workflow, users simply attach the file you want to use and copy the data into the GIS. The data can be from Survey tools, CAD, other GIS formats. We have been able to remove the issues around symbology and level names used in CAD files from external suppliers. We replaced the scripted process with web services that users can run from the management interface. Our business logic has been moved to the database and we still have a full audit of all changes that are made.

12. The Good and the Bad Flexibility Reduced Customisation Reduced Complexity Single Source of truth Good Specification Fixed Price Agreements Improved Accuracy Reusable Data Flexibility Developers Documentation Resources Existing Drawing Quality The Good * Flexibility, we have a solution that allows us to add features or attributes when required, doesn�t force a particular data-model or structure, and allows us to perform the advanced asset management functions that we required (like work orders, inspections, etc). * Reduced customisation, we have reduced the amount of custom code in the GIS from over 300,000 lines to a few hundred. The functionality we required existed within the products we selected. * Reduced Complexity, our users no longer have to be concerned about the manual components of the process. * We are able to build a single source of truth on Oracle for all our GIS and asset data (including datasets that are not part of the asset management system). * We developed a good specification for how we wanted things to work, this then enables us to easily communicate our requirements to developers and consultants. We knew exactly what we needed, we also had an understanding of the technologies that we wanted to use. This knowledge also helped during discussions with the developers. * because we had good specifications, developers were able to give us fixed price quotes for the work. This has allowed us to stay within our budget. * we have been able improve the accuracy of not only the data that is stored in the GIS, but also the quality of other parts of the process. * and we are able to re-use more data with less effort. The Bad * Flexibility, having too much flexibility can be bad because you can do almost anything. And we did struggle with this a little when defining the data model for the features, and attributes. * Documentation from developers, developers don�t like to write too much documentation. * Resources, this project is behind schedule, but still within budget. This was largely due to me not allowing enough time for some aspects of the project. * Because we have been able to re-use the actual as-built drawing (rather than just the co-ordinates of features), we have noticed some issues around the quality and accuracy of some of these drawings. The Good * Flexibility, we have a solution that allows us to add features or attributes when required, doesn�t force a particular data-model or structure, and allows us to perform the advanced asset management functions that we required (like work orders, inspections, etc). * Reduced customisation, we have reduced the amount of custom code in the GIS from over 300,000 lines to a few hundred. The functionality we required existed within the products we selected. * Reduced Complexity, our users no longer have to be concerned about the manual components of the process. * We are able to build a single source of truth on Oracle for all our GIS and asset data (including datasets that are not part of the asset management system). * We developed a good specification for how we wanted things to work, this then enables us to easily communicate our requirements to developers and consultants. We knew exactly what we needed, we also had an understanding of the technologies that we wanted to use. This knowledge also helped during discussions with the developers. * because we had good specifications, developers were able to give us fixed price quotes for the work. This has allowed us to stay within our budget. * we have been able improve the accuracy of not only the data that is stored in the GIS, but also the quality of other parts of the process. * and we are able to re-use more data with less effort. The Bad * Flexibility, having too much flexibility can be bad because you can do almost anything. And we did struggle with this a little when defining the data model for the features, and attributes. * Documentation from developers, developers don�t like to write too much documentation. * Resources, this project is behind schedule, but still within budget. This was largely due to me not allowing enough time for some aspects of the project. * Because we have been able to re-use the actual as-built drawing (rather than just the co-ordinates of features), we have noticed some issues around the quality and accuracy of some of these drawings.

13. What Next? More Assets More Functionality More Integration Currently we have migrated our piped water infrastructure into this new system, this will be followed by our drainage and waste water assets. After these we will then look at the other infrastructural assets, before moving on to sports grounds and Council owned Building and Facilities and then possibly Cemeteries. We are now able to offer our asset managers software and data for performance modelling and monitoring, our designers have access to better design tools and more consistent data, our accountants and auditors have a transparent and auditable financial process, and our end users have a single authoritative data source and a simplified workflow. We are planning further integration with our existing service request, financial systems, and other corporate systems used by Council. Currently we have migrated our piped water infrastructure into this new system, this will be followed by our drainage and waste water assets. After these we will then look at the other infrastructural assets, before moving on to sports grounds and Council owned Building and Facilities and then possibly Cemeteries. We are now able to offer our asset managers software and data for performance modelling and monitoring, our designers have access to better design tools and more consistent data, our accountants and auditors have a transparent and auditable financial process, and our end users have a single authoritative data source and a simplified workflow. We are planning further integration with our existing service request, financial systems, and other corporate systems used by Council.

14. Questions? Technical Slides if time permits Technical Slides if time permits

15. WorkIT Data Flow Spatial and non-Spatial changes are supplied to our users via as-built drawings, and data maintenance requests. The data is maintained within the Bentley Map product with the data being stored within an Oracle database server, and then pushed through to the Accela AMS. Read-only access is also provided for reporting and other analysis. Spatial and non-Spatial changes are supplied to our users via as-built drawings, and data maintenance requests. The data is maintained within the Bentley Map product with the data being stored within an Oracle database server, and then pushed through to the Accela AMS. Read-only access is also provided for reporting and other analysis.

16. WorkIT Application The �WorkIT Application� is made up of the WorkIT Toolbar Application (management interface), Bentley Map, and Accela AMS, with a common Oracle Data Store The �WorkIT Application� is made up of the WorkIT Toolbar Application (management interface), Bentley Map, and Accela AMS, with a common Oracle Data Store

17. Data Management The asset data is only able to be edited via the Bentley Map interface, all other applications have read-only access to this data. The users are never working on the live data, all new and modified features are stored in a series of �Capture� tables, this allows us to perform a series of checks with both business and spatial rules. Each management job is a discrete area, and all the features affected are processed as single task. When the user finishes their updates, the management interface is used to perform the checks, create an audit of the changes, post the data to the authoritative GIS and asset tables, and clear the features for that job from the �Capture� tables. The asset data is only able to be edited via the Bentley Map interface, all other applications have read-only access to this data. The users are never working on the live data, all new and modified features are stored in a series of �Capture� tables, this allows us to perform a series of checks with both business and spatial rules. Each management job is a discrete area, and all the features affected are processed as single task. When the user finishes their updates, the management interface is used to perform the checks, create an audit of the changes, post the data to the authoritative GIS and asset tables, and clear the features for that job from the �Capture� tables.

18. Data Migration Migrating the existing asset data uses the same process as any other data maintenance task. The only difference is that it is the entire dataset and the task takes a bit longer. Oracle SQL scripts are written to perform any manipulation required to make the data for the new data schema and to build any necessary feature linkages. Migrating the existing asset data uses the same process as any other data maintenance task. The only difference is that it is the entire dataset and the task takes a bit longer. Oracle SQL scripts are written to perform any manipulation required to make the data for the new data schema and to build any necessary feature linkages.

19. Any More Questions?