Extracting Specialized Information from Your CMMS/EAM

Data conversion and report capabilities are key areas to study.

As a maintenance or engineering person responsible for implementation of a computerized maintenance management system (CMMS), you and your organization represent the functional side of the implementation. The other personnel that are involved with the CMMS are database administrators and the application programmers.

As functional system administrators, you face many challenges working with a CMMS. Two of them are legacy data conversion/migration and report writing.

Database administrator
Database administration typically is handled by a person or department that maintains the database just as the maintenance department maintains the plant. The administrator (often known as a DBA) continually monitors performance parameters, size, and integrity, and performs certain tasks such as backups. Additionally, he makes modifications to the database to support the needs and requirements of the whole system. Most of this terminology will apply best to a CMMS based on a relational database management system (RDBMS), but it has applicability in all CMMSs.

Application programmer
Application programming is involved in what the programs do, and analysis of problems or requested changes. These problems or changes often result in direct interface with the vendor, program changes made locally or by the vendor, new reports, and new behavior of the application. This is typically where changes to screens and database configuration are made.

Functional system administrator
Functional support is key to merging the concepts of how the business process works (work order life cycle, cycle counting, etc.) with the capabilities of the application to support that process. It is important that the functional personnel understand both the process of the facility, as well as how the application works. There are many aspects of how functional personnel meet the challenges of their job; two of those challenges will be discussed: legacy data conversion/migration and report capability and how to enhance it.

Query/reporting tools
Before going into detail, let's discuss the tools to be used to extract data from your CMMS and why they were chosen. First is Microsoft Access because it is widely available, and, more often than not, installed on your desktop PC. It is an underutilized tool and has a serious role for the functional support organization.

Additionally, people with advanced capabilities in Access are typically available in proximity to your location. Computer-based training, videos, and other training options for Access also are readily available. This greatly improves your chances of getting started quickly and effectively.

The next tool is Crystal Reports or Crystal Enterprise. Crystal Reports is a significant player in the decision support software or report writer market, and is widely used. Crystal Enterprise (previously Crystal/Seagate Info depending on the version) is a newer tool that has numerous functions.

In addition to availability and market share as selection criteria for these tools, cost and support are important. Access has remained competitive throughout its growth in the market, and Crystal Enterprise remains competitive with other decision support software tools. You also can check with the vendor of your choice for free demonstration software which is typically a limited time use, full version of the software in question.

Data conversion
Legacy data conversion/migration is a challenge to functional administrators; you need to know how to do it so you can decide whether or not to try it. If you do try it, do so with great care and attention to detail.

Legacy data conversion often requires loading data through the "back door," or directly to the tables, rather than by keying them in on the screens designed for primary or initial entry. This is becoming more important each year for one simple reason—CMMS owners are now replacing their old systems. The maintenance system probably was in a RDBMS in the first place, and even if not, the company has more data in electronic format, which is perceived to be an easy insert into the new, more flexible/adaptable, and better application.

Although this is mostly true, there are a few things to remember. When populating a (theoretically) bug-free CMMS outside of standard programming delivered with the application, problems can crop up and cause great headaches. Three potential problems that are commonly overlooked are properties (size/format) of the fields, population of all fields, and referential integrity that is maintained by code and not by the RDBMS.

  • Field size. In simple terms, your old data is in a field that is 75 characters wide, and the new corresponding field is 60 characters. When the data is loaded into the 60-character field, truncation will occur and there will be characters that do not make it in. When you are dealing with 100,000+ lines of text, you may miss the 25,000+ lines that received truncation until you start generating PM work orders and find that the text descriptions are missing something. Size is most important.
  • Field format. Generally, whether the field is integer, double/single precision decimal, text, etc., this is often taken into account and is not usually a problem. What falls through the cracks here is case. Upper or mixed case are typical options, and to a database, they are significantly different. Specifically, "a" and "A" have different values, especially when bar coded, sorted, or searched.

Case typically comes into play when the field is uppercase and the data is not. The error shows up when the field is used in a query or report set up for the commercial off-the-shelf version of the application; the vendor designates the field uppercase and the data loaded has mixed or lowercase values.

Challenge: If you can, open your main equipment/machine data table in your Test database. If your data requirement for the equipment number is upper case, change it to mixed or lower case. Confirm your change has been saved and close the table. Open the application and go to the main equipment/machine data screen. Attempt to bring up the equipment record and see what you get.

Populating all fields in a table is recommended, if not required, when loading data. Fields that are required by the database typically will generate an error when a record is loaded without an appropriate value (even when that value is zero). The problem usually occurs when there are fields that are not required by the database configuration, but are required for the practical use of the application. There is also a tendency to concentrate only on fields that will show up on the screen. If the main screen that displays machine data shows only 35 fields, but the main table has 85 fields, you must plan to populate 85 fields, even if you only write "nulls" into them; they must be reviewed for requirements that are practical as well as necessary.

  • Referential integrity. Referential integrity is typically covered by the RDBMS in question. Given the machine data table with the machine number as its primary key (a number that is unique in that table), there may be other tables that relate to it by using this primary key. For example, work order data in a one-to-many relationshipone machine may have zero, one, or many work orders.
    • Designate delivery format (html, Word, Excel, rtf, pdf, etc.)
    • Designate destination of delivery (e-mail user, disk file on network, printer, etc.)
    • Designate when to run report including time of day and frequency (every day/week, every X hours/months, first, last, or Nth day of the month, etc.)
    • Send notification of success or failure via e-mail
    • Send text with report if e-mailed

CMMS vendors are not limited to using links to relate the data. They can use programming to ensure the integrity of the data so you do not, for example, create a work order for a machine number that does not exist. When loading legacy data, one must be sure to keep this in mind, and provide additional data to support the functional relationship even if it is not an expressly required field.

The good news and the bad news are now the same. Access can load data into most relational databases through open database connectivity (ODBC). As long as the user has the appropriate security access, queries typically can be created to add, update, or delete rows in the tables. But this same capability can allow the wrong people the same access. Even the right people with access can cause problems if they forget what they can do, and accidentally modify data when they did not mean to.

A last warning: in some systems, especially in legacy mainframe applications, you get the option to not save your work. Or perhaps you hit escape and undo the last record(s) altered; generally, this option is not present, or may be present only for a record, or perhaps even just a field. Be careful, and test, test, test!

Report writing
You will recognize this challenge when you hear such questions as "Can I get a report that shows me?" or "Can I get the reports in Excel, and have them generate off shift and appear on the network?"
Both Access and Crystal can do wondrous things in reporting through ODBC or native drivers. I will attempt to separate them in terms of their functionality and when to use what. For comparison, MS Access 97 and Crystal Reports 8 were considered. Microsoft and Seagate continue to enhance both products with each successive version, further blurring the lines between them when it comes to reporting.

Access provides the capability to store data in one database, and query/ reporting and automation in the same database, or in a completely different database. Further, through macros, you can automate certain functions that can run a query and transfer a small subset of data and calculations onto a network drive in a spreadsheet, which another department may want to access.

Access is also extremely useful in tracking down problems because queries are simple to generate. Conditional formatting is also available to highlight exceptions within a report. Wizards can accelerate the process and assist the user with the creation of more complex queries, reports, forms, and tables. This is especially important as some of these elements have complex programming behind them that can be difficult for both the novice and more experienced users to grasp.

Access also has the capability to create an entire application front end to handle selection of your reports through menus with help screens, etc., because it allows the creation of forms and macros/modules. The forms represent screens and the macros/modules represent automation that exists behind button clicks.

Although Crystal Reports does not have the capability to create a front end application, it does have more powerful report generation capabilities. Both Access and Crystal Reports allow multiple grouping levels in reports, with subtotals. Crystal Reports goes a step further in making that grouping dynamic. That is, while viewing the report, it can be changed from a very detailed report to a summary with two mouse clicks. Further, a drill down tree lets users go through a lengthy report to a specific area and view its summary or detail. If your CMMS comes with a Crystal runtime, you will be able to launch your reports from your CMMS, passing parameters to the report to limit its scope.

Current versions of both applications allow you to save data and deliver an executable to your recipient. This will allow someone to view the report without having either application on the desktop. Although it can be a timesaver, it often can be a disk space or network problem depending on the infrastructure and the size and frequency of the data transfer. To assist in this area, you can use either application to park current data on your network for retrieval on demand.

Training for the tools
Although both Crystal Reports and Access come with wizards to assist in report creation, Crystal is often perceived as more difficult. Two levels of training for both products can best address this perception: technical training on the tool and functional training on the CMMS database structure.

Technical training for the application will explain how to use the tools to generate the reports. This will allow a user to make it presentable; allow for filtering with static (hard coded) or dynamic (entered at runtime) parameters; provide an understanding on delivery formats and what is required to allow someone else to review the report online, etc.

Functional training will provide the understanding of how the fields on the screen relate to the tables and columns and how the tables relate to one another in the database.

One of the greatest features of Access and Crystal Reports is that they both are widely available in the United States and Canada, as well as in other parts of the world. This allows for relatively local technical training to be found quite easily. There are also user groups for both products in many large cities.

Functional training on what goes on behind the scenes in the CMMS, with regard to the tables and fields and their relationships, is much less common.

Some CMMS vendors now are offering training on Crystal Reports and other report writers. Before deciding, make sure the training is specific to writing reports for the application and version you are interested in, as opposed to training you could obtain from the report vendor or training partner.

Further, if you have in-house or contract expertise in the back end of the application, and you are going to bring training in rather than send users out to training, make sure to coordinate with the training vendor. Most do not know your CMMS, but they can learn the basics about its structure. This will allow the documentation they produce, as well as examples, to be specific to your CMMS, and provide more useful training.

Delivering, saving reports
Both applications also allow delivery of reports in numerous formats, including Excel, text (with various delimiters), rich text format (easily read by most current Windows-based word processors), and html which requires only a browser (Internet Explorer, Netscape Navigator, Opera, etc.) to view.
Crystal Reports also goes further in allowing you to save your report in data interchange format (DIF) or directly into a Word file, and allows you to deliver it via Lotus Notes, Exchange, or MAPI if installed.
Crystal Enterprise also makes the following scenario possible:

The product comes out of the box with a complex array of possibilities to allow you to control scheduling from only a few desktops and have the processing take place on different servers depending on the workload and network configuration. Needless to say, it can be rather cumbersome and scary to consider the administration of it all.

To simplify the startup, buy a powerful desktop machine and call it your Crystal Enterprise Server1 (for example). Assign it a network login and a login to your CMMS database, and you are ready. Install Crystal Enterprise on that machine, and make sure it is secured and backed up regularly; tie it into your uninterruptible power supply for your network (expand it at additional cost as needed), and you are ready to go.

Additionally, Crystal Enterprise will save previous runs of a report. If you obtain a request for a report generated by Crystal Enterprise five months ago, it will still be accessible from within Crystal Enterprise. You can retrieve it, and send or print it again as needed, thus providing some functionality of a report repository application.
A word about saving reports in other formats, especially Excel. Reports generally contain headers, labels, multiple levels of grouping, font manipulation, and other text-type data. This can make the spreadsheet format rather difficult to use.

In Access, you can get around this by building your reports on queries that handle virtually all your calculations first, and the report makes it look nice and adds headers and grouping.
In Crystal, although you can create queries and base your reports on them, a couple of other options may work better. Reports can be designed with minimal headers and either no groupings, or view the report first, hide the drill downs, and then save the report—it will look and work much better as a spreadsheet.
Crystal (aka Seagate) Analysis is an excellent way to create spreadsheet views of your data that work properly as spreadsheets. It also has on-line analytical processing (OLAP) capabilities.

Which tool do you use?
In terms of which tool to use, both are recommended. Access is an excellent tool for quickly creating queries for diagnostic use, or for answering questions now. It also is a great tool to use for creating a standalone database for system administration use which contains queries and reports for that purpose.
Wading through the data model or entity diagram to find the correct tables every time can be cumbersome. Access also can be used for setting up one or more databases that contain common tables by module or function that you need to use more frequently. So if your CMMS has 150 basic tables, but your work order-related queries only hit 20 of them, a database can be created that has all the appropriate links.
Crystal Reports will typically design more production-type reports that you will want to use and distribute. Crystal Enterprise will provide you with the scheduling tool, as well as a report repository containing both a history of reports run on schedule as well as reports to run.

Final word
CMMS systems come with a set of standard reports. Generally, the number is increased with each major release. Still, they never seem to be enough. Understanding the tools at your disposal, and the relationships within the database, will bring you a long way toward quickly addressing query and report requests.
Access is most likely your best choice for a desktop database solution. It is probably already available to you and support is available within your organization. Most likely, you will not need to purchase another database solution. If you have one already (such as Lotus Approach), it will have similar capabilities. If you understand them, then you need only apply the query/report capability to your CMMS database.

Crystal Reports is a little more difficult to decide on. There are numerous report writers and decision support software available, from both the manufacturers of databases and business intelligence tools. For example, Oracle, Sybase, IBM-DB2, and Informix all provide report writing solutions as standalone or integrated applications. In addition to Seagate (Crystal products), you will find products from Cognos (PowerPlay/Impromptu), the SAS Institute, Corvu, Information Builders, Computer Associates, Micro Strategy, Business Objects, and other providers. Before you purchase one of these tools, check in-house to find out if it is already available. MT

Christopher N. Winston is an independent professional in the Detroit, MI, area contracted to HSB Reliability Technologies, 1701 N. Beauregard St., Ste. 400, Alexandria, VA 22311; Internet. He has more than 18 years CMMS implementation and business system analysis experience and has a bachelor of science degree in mechanical engineering.