Maximo Data Scrubber

AutoSoft Systems

2 Round Hill Court

East Greenwich, RI 02818

401.885.3631

401.884.5653 Fax

401.996.3631 Cell

AMDG

Decision Ready Information!

autosoft@aol.com

Commercial & custom multi-user computer software for a variety of applications including performance metrics, statistical analysis, data extraction and merger from multiple large databases, computer simulation and management information systems. Founded in 1982.

We know Excel! Whenever possible, Excel is the user interface, VBA is the programming language, ODBC connects to the database of your choice.

Pharmaceutical & Biotech

Construction Management

AMDG

Maximo® is a trademark of IBM

AutoSoft is not affiliated with IBM.

This program combs through the Maximo database and isolates 5 defined exceptions and 2 specific database changes. It automatically sends emails to the system owners, notifying them of the exceptions and "encouraging" them to make corrections. The number of exceptions is trended with time so that progress can be tracked.

It is important to note that this entire program was written by Dr. Serdakowski in less than 80 hours. In this case, the Corporate Facilities and Engineering Manager had a clear vision for what was needed and communicated his vision to Dr. Serdakowski. This type of detailed analysis is virtually impossible with conventional Maximo reporting tools such as Brio, which is a very effective tool for "one-up" queries but cannot be automated.

Maximo data is constantly changing, with new errors being introduced. Significant errors lead to operational difficulties, increased costs and possible compliance issues. The Data Scrubber will catch and correct problems as they are generated. This process is considered a business procedure, not an SOP procedure which would require a higher level of management.

The 5 defined exceptions are:

●

Classification Adherence - The primary location for the asset is level 1. A list of equipment for each system has been compiled. For example, a chilled water system would have in its list items like: Air Separator, Blackflow Preventer, Chiller, Compressor, Control Panel …. None of these items should be classified as "Level 1". If one is, then an exception is noted.

●

The asset is in an incorrect system - Sometimes a piece of equipment will be included in a system incorrectly. For example, a Transformer should not be included in a Chilled Water system.

●

Hierarchy Adherence - The asset's first 10 digits of its primary location are not same as the subsystem - this is to assure the parent-child relationship is well defined in the Maximo database. Exceptions to this rule are identified.

●

Spares - The asset exists in the system. These specified items should not exist in the system, but should be considered spare parts. Again, for a Chilled Water system, items like Motor, Starter, Strainer… would be classified as such. If a listed item appears in a system, an exception is noted.

●

Nomenclature - The equipment description does not equal anything on the list. This identifies equipment added to a system that does not belong there.

The 2 specific database changes are:

●

Classification Level 1 to Classification Level 2 in the last 30 days

●

Classification Level 2 to Classification Level 1 in the last 30 days

The main screen looks like:

Help is available at the touch of a button:

Significant pre-work was performed by the engineer in charge of this scrubbing to identify the corporate systems and what pieces of equipment need to be included and excluded in each system. All of this information was compiled into an Excel workbook with a worksheet for each system. The light blue button on the main screen allows the user to select the document and begin the process. An import log (cell B4 above) records the name of the imported file and the date of the import.

For example, Row 22 above, lists in cells C22:I22 the number of entries for that system in each of the 7 categories searched. Cell L22 tells us that there are 81 systems of this type corporate-wide with 729 associated pieces of equipment. There are 393 total exceptions (U23) 100 of which are spare parts that are incorrectly placed in the system (Q23). Double clicking on cell A22 opens a stand-alone Excel workbook with details of the exceptions. The main screen summarizes the 81 systems and the count of each problem for each system:

There are 7 worksheets which give specifics on each exception:

The System worksheet lists all systems and the names and emails of each system owner:

The Equipment worksheet lists all of the equipment in all systems of this type:

The email utility is quite powerful. It will automatically send out emails to all system owners (or a specified subset) that will alert them to exceptions in their systems as well as offer suggestions as to how to correct the exceptions.

Again, Help is just a click away:

Performance metrics are generated in the form of a trend chart. One can simply track the number of exceptions for a selected system or the database as a whole using the Archive worksheet. Trend Charts are generated with a simple mouse click:

AutoSoft Systems | 2 Round Hill Court, East Greenwich, Rhode Island, USA 02818 | 401.885.3631 | Fax: 401.884.5653 | Mobile: 401.996.3631

This web page was last updated at 02/21/2014 07:52 AM and is written in EXCEL!

AXIOM is a Trademark of Consona - USYS is a Trademark of Zumbach - AutoSoft Systems is not affiliated with Consona nor Zumbach