The relationship between Asset Management Systems and GIS is growing stronger and more important, but often GIS professionals don’t have experience working with Asset Management Systems, and that can cause issues. To help, we are offering this short series of articles intended as a guide for GIS professionals.
In our first article, we covered:
- Two Aspects of Decision-Making: Requirements vs. Capabilities
Today, we will cover:
- Capabilities: What are the capabilities of the asset management system we are considering? Should we use asset management software or a custom database solution? How do we decide what to do?
- Getting started
If you’ve been following along with our recommendations, you’ve already figured out why you want to employ asset tracking, what assets are important, and what you need to know about those assets. Now, it is time to find a solution for these needs. There are really two basic options: commercial asset management software, often called computerized maintenance management software, or a database-level system custom to your organization. The choice depends on the complexity of what you need to track and the amount of money you have to spend. Let’s look at each.
Commercial Asset Management Software
This is a software package that is installed at the server level, generally built on top of a relational database. It contains functionality to track a multitude of assets and asset classes, depending on your needs. Usually these are combined into modules, which may include: sewer/storm water, streets, parks, facilities, and other utilities. The modules then contain basic attributes about each asset, including physical descriptions, length, diameter, material, etc.; and also information about condition, like date installed, current condition, last maintained, etc.
Many of these systems incorporate, or are designed to integrate, with a work order system. Since the systems are tracking condition, they are often pre-loaded with regular maintenance schedules for a variety of assets and asset types. These can then be used to generate work orders at regular intervals for maintenance. As well, if an emergency occurs, then a work order is generated for that, and the appropriate assets are included along with the action performed. Once this is synchronized into the CMMS, you begin to generate a historic record of condition and action for each asset.
As with any software product, the initial look at one of these systems is going to seem like they can do everything, and include everything, possibly up to the kitchen sink. You have to make sure you take a deeper look at each system, the cost, both implementation and long-term, and the options for customization.
Here are a few things to keep in mind when looking at these systems:
- Ability to integrate with your GIS software (ideally without messy file transfers and ETL). This may seem obvious as GIS is somewhat our default, but it isn’t always. There are a lot of systems out there, and they have a range of integration with GIS, from basic, to systems that sit on top of your GIS database.
- Ability to hold data in a way that is compatible with how GIS manages that data. One complex situation is if you currently manage linear assets using dynamic referencing. An example would be if you have sewer mains, and have sewer laterals locations calculated based on their distance along each main. Often asset management systems aren’t set up to deal with things like that. They need a permanent layer for each asset, not a dynamic layer.
- Ability to decompose certain assets into smaller sub-assets, including knowing how many ‘levels’ of decomposition you can reach. (Some older systems might only let you have four levels of sub-assets).
- Ability to aggregate certain classes of assets, sometimes in multiple way. This could include combining condition estimates for different layers when doing capital improvement planning, to see where multiple assets in a particular location all need work in a similar timeframe.
The other major factor to consider is cost. It is important enough to be discussed separately from the list above. If you implement a CMMS, you will have to consider a few types of costs:
- Implementation cost: Moving your asset data from your current system, whatever that may be, and loading the new system.
- Initial Software cost: Varies widely per system. Sometimes, it is based on the number and types of assets you have to manage, and sometimes it is based on the size of agency or organization you are.
- Maintenance costs: Ongoing software support. Does this include software upgrades, some level of customization support each year, etc.?
- Hardware costs. There may be requirements for a dedicated server, and the associated software needs to get that running.
- Other software costs. Do you need/want to implement a work order system, or a more enterprise-level GIS to integrate with this system? You may need database software.
- Training/Implementation. The importance of this cannot be overstated. If you implement a software package like this, the time spent preparing your data, customizing the software to the needs of your organization, and then training staff thoroughly to use it, is critical to the program’s success and future use.
Another key consideration is the types of changes that may affect your asset records and how this will be reflected in your asset management system and GIS. Often in addition to the normal ‘create, read, update, delete’ operations, there can often be ‘merge, combine, split, or decompose’ actions that can be difficult to deal with. For example, if some of the assets that you are dealing with are something like a road centerline, and you are recording things like maintenance expenditure against road centerline records (where one line = one asset record) then what happens if it becomes necessary to split that line (because of a new intersecting road being built for example?) If you can’t tell WHERE along the original line you invested the maintenance expenditure, then you can’t precisely tell how to apportion the historic expenditure against the two (or more) new lines.
Another issue can arise if you have used unique identifiers of a parent asset as part of the unique identifier of any sub-assets. For example, if there is a pump house building with a unique ID “1234” and the pumps within that pump house use the encompassing building ID as part of their IDs (e.g. “1234-P208”) then there can be a lot of problems if for some reason it is decided to move a pump to building “5678.” Do you retain the old unique ID because of the historic data related to it?
In addition to the functional requirements of your asset management system, also consider things like the ease of integration with other systems (including your GIS), and non-functional requirements such as performance.
Custom Database System
The alternative to purchasing a software package is to develop an asset tracking solution inside your database. This option requires that you have your data stored in a Relational Database Management System, with spatial functionality, like Oracle Spatial, Microsoft SQL Server with SQL Server Spatial, or PostreSQL with PostGIS. This level of database is necessary because they have support for SQL views, triggers, and stored functions. The combination of these three items will allow you to perform more complex tracking of your assets.
The initial process is the same as with commercial software. You will build your GIS and structure the tables containing both spatial and attribute data as necessary to best represent the assets in the city. Once that step is complete, the next phase is to look at the types of asset tracking you want to do as described previously, from maintenance management, to condition tracking, to CIP planning. You will need to figure out what special tables you would require to accomplish these.
While in some ways, this will replicate some functionality of a commercial product, the difference is that you will be able to choose exactly what is important for your organization. Here are a few things to consider:
- Do you, or one of your staff, have the experience necessary to set up the tables, advanced queries, and stored functions to create a tracking system? Setting something like this up is going to take a firm grasp of database fundamentals, as well as more advanced skills. If you don’t know it, can you learn to do it?
- Does your GIS department have the time to spend on this sort of implementation? The system can get to be quite complex depending on your needs. Being able to think through the design of the database and then implementing that will require some dedicated time.
- If you have the skills and the time to put into this, is it the best use of your staff time? If the needs are complex, sure, you may be able to implement them, but at what expenditure of time, and thus delay of other projects?
As with the commercial software, cost is also a factor, but it is the cost of staff time, not the cost outlay for software. Here are the cost details to consider:
- A custom solution implemented by internal staff moves the cost from spending to purchase software, to spending staff time dedicated to this project. How much staff time are you willing/able to dedicate to this type of development project?
- What priorities will have to be delayed in order to implement this project? If asset management is a high enough priority, and the cost/benefit to building a system pencils out, then move forward. If it doesn’t, or there are simply too many other urgent tasks that need to be completed, you will need to consider those.
- Generally, the GIS department is funded centrally, with contributions by all the other departments, as it is meant to serve the entire organization. Given this, you need to look for buy in from the other departments to see if this is even a priority on which they are willing to have you spend time.
How to decide?
Your decision should come down to the needs of your organization, then money and time. If you have a lot of assets to track, and are looking for maintenance and condition tracking with a work order system, then a commercial system is going to be right. If you have just a few asset classes, and want to be able to model complex interactions between them for CIP planning, you may not be able to find that in a commercial system.
The benefit of a commercial system is that it contains a lot of asset types, has the ability to integrate with other software packages, and can maintain a lot of data about the assets in question.
The benefit of a custom solution is that it may be exactly as simple or complex as your organization needs. You won’t be paying for the overhead of modules that you never use, and can quickly add items that would not be included out of the box in a commercial package.
The last two factors, money and time, balance each other. If you have money but no time, a commercial software package is likely the right path to take. If you have time and ability, but less available funding, then a custom solution may be appropriate.
Okay, you’ve looked at your organization, evaluated your needs, and figured out whether to buy or build a solution. Now to get started.
The first thing to remember is to keep it simple. Don’t track more than you need to. Just because you have a lot of different data types doesn’t mean you need to track all of them, especially at the beginning. Whether you are purchasing software or building a solution, come up with a priority list of the assets that are most critical. Put tracking in place for these first. For a city or agency, this will likely be your major utilities and/or streets. Sewer is often a good place to start because it is a simple system, with the potential for tracking a lot of items. For businesses, it might be your fleet. Add each vehicle, and start entering regular maintenance.
Use as much existing data as possible. If you are putting in new software, you may need to transfer data. Verify the data for quality first before you move it over. This will save error correction time in the future. For a custom solution, you will be working with your existing data, so choose the highest priority asset and build out your maintenance and condition tables, and tracking queries.
Start with the most recent data that you have. With a vehicle fleet, load in the most recent regular maintenance that has been performed. This will give a baseline against which people can start working. As time goes on, you can backfill historical data to be able to start seeing trends with maintenance and condition issues.
The last piece of advice is not to try to put everything in place at once. This applies most especially with commercial software. The company is likely to want to get their implementation contract completed as soon as possible. Have a plan in place with a timeline that breaks the asset groups into individual sections of time, as opposed to taking all the data and setting it up en masse. Doing this will enable you to go live with part of it, and test to ensure you have the attributes correct, and all the subordinate tables seeded with good data.
One thing that is really to be avoided with the above is when professionals just import into GIS and/or asset management systems some poorly thought-out Excel spreadsheet that some business user has used for decades without putting it through a database normalization process. A similar issue is often found if someone uses a shapefile or other flat file or de-normalized XML file and just imports that data into a GIS or asset management system without considering normalization or optimizing the structure of the data. This might make it an easy process with which to get started, but it is often just storing up trouble for later.
There you have it — asset management in a nutshell. In this case, a very lengthy nutshell, but there are a lot of moving parts, and a lot to think about. Let’s run through the steps again:
- Determine the requirements of your system, starting with the purpose for tracking assets. Are you interested in maintenance, condition, capital improvement planning, budgeting, and/or regulatory compliance?
- Next is to figure out what assets need to be tracked. This could be properties or vehicles if you are a business, or utilities, fleet, parks, and facilities for a public agency. Start to think about how the assets and the tracking purposes intersect. That leads to the third step:
- The details of each asset, and what data is important. This will be led by the subject matter experts not only for each asset, but also for the tracking purposes.
- Now that you know your needs, find a solution to meet them. Look at commercial software or a custom database solution.
- Commercial asset management software is a complete package with modules to handle many types of assets. They focus more on maintenance and value tracking for O&M and valuations. Ensure that the modules match the needs of your organization. Be aware of the costs for software, hardware, implementation, and training.
- A custom database solution is just that: custom tables, queries, and functions stored in your database to handle the same functionality as the commercial software. This solution depends on your time and experience to implement, but given that, can be more flexible than commercial software. Since you determine the structure, you have the opportunity to create more tracking between disparate assets to answer specific planning questions. Time is the factor here. Do you and your department have the time to implement this solution? Are the departments you support willing to divert your time from their projects, to build this system?
- Making the choice between systems is a matter of the needs of your organization. The complexity of your assets, the purpose for tracking them, the time with which you have to work with, and the amount of funding all come into play. Don’t make this choice in a vacuum. You absolutely need buy in from all departments involved.
- Getting started is a matter of planning. Prioritize what assets are most critical. Start at the top and implement one group at a time, testing as you go along. Bring in your most recent datasets, and maintenance/condition data to seed your tracking tables. Make a plan to backfill with older records as the historical data will help you start to see trends. Don’t try to do everything at once.
As was mentioned above, taking more time at the time of implementation is going to save you frustration and tech support hassles down the road. The best advice we can give you, for all facets of an asset management implementation, is to come up with a plan and work through it. You could even use this document as a template, expanded with detail specific to your organization. This will benefit you threefold:
- First, you will have something to refer back to, to keep you on track. It will ensure you don’t miss a step, or forget something critical.
- Second, it will help you be accountable to your supervisors. You can keep them apprised of where you are in the process, any roadblocks you are running against, and items where you may need their assistance.
- Third, you can use this to keep your software contractor in line if you are going that route. Don’t let them jump ahead until you’ve covered everything in your process.
Right now, you are probably saying, “The best laid plans of mice and men ….”, and it is true. You will need to be flexible, because things will come up as you go along, but know that you can do it!