Maximo Consultant's Blog

Page 2 of 18

The performance effect of the Download button in Maximo

12 July 2024 / / 0 Comments

Introduction

If you have been working with Maximo for a while, you already know about the Download button on the top left of every table in Maximo. With one click, it will export everything we see displayed in the table into an Excel file. This is great for doing further data analysis and reporting from that data. It is so simple and convenient, right? Not quite.

Performance Degradation

The danger with the Download button is that, since it is too convenient, everyone is using it for every data requirement. One frequent problem is the user keep asking us to add more columns to the List tab of the key applications like Work Order Tracking. Most of the time, the Maximo Administrator will comply to such requests in a blink (another problem of Maximo being too easy to customize). Often, many of those columns are retrieved via relationships. One additional column usually does not really make much difference. But if there is a high number of records, and as the amount of user activities increases, it will create a snowball effect in degrading the overall system performance and people will start complaining about Maximo being slow. 

However, the real problem with the Download button is that, by default, there is no limitation set for it. Usually after Maximo was first implemented, it worked great. After several years, the amount of data grew, and people start using this method to download data for various reporting requirements. The Download button can significantly affect system performance.

Measure the impact

For a client that I recently worked with, many users frequently use the Download button to extract a large amount of data (e.g. all work orders in one year) to create their own reports in Excel. This led to a tremendous amount of stress on the servers.

First, we must realize that the output of this “Download” function is an XML file (although the extension is XLS), Maximo consumes a lot of processing power and memory to generate the XML file. To fully understand how it affects the server, I did a small test by setup a copy of the client’s database a local VM. I opened the Work Order Tracking app and try to download all active work orders (15k records). That took around 15 minutes to generate and download the file. Then I tried to download all work orders reported in the last year (this includes both closed and active work orders). It took 50 minutes. And during this whole time, the VM’s CPU and memory utilization was saturated the whole time.

CPU and Memory usage while Maximo is processing the download request
CPU and Memory usage after the processing completed

Crashing the server

To test the worst-case scenario, I opened the SR application, and click on “All Records”, then clicked on the Download button to download all 600 thoudsand records. Users can easily make this “mistake”, and once they clicked on the Download button there is no option for them to cancel the process.

At first, the process saturated CPU and memory utilization for more than an hour, after that, the session expired. However, in the background, SQL Server process continued running. It was consuming 30-40% CPU utilization for the entire day. I left it to run for about 6-7 hours until I got fed up and had to restart SQL Server to kill that process. Theoretically, since I was the only user in the system, when the process was running and it reached the maximum JVM heap size, Garbage Collector would try to clean the other previous MBOs which it has used, and able to free up some memory.

However, in the production environment, as it happened to our client, if the server load is high, sometimes, the Garbage Collector couldn’t free up memory quick enough, it resulted in the OutOfMemory error and crashed the server.

What can we do about this problem?

To reduce the impact of the Download function, we should set a limit on the number of records the user can download by setting a value to the webclient.maxdownloadrows property. There are already some tech notes by IBM that talked about it.

However, the next question is, once we have set a limit, what is the alternative method for the user to download the data they need? I can think of a few methods like building a simple BIRT report. It allows the users to choose XLS as the output format. We can also setup Application Export function with a flatfile output format. But my favourite option would be using the “Create Report” function. By default, when we create and run a report in the “Preview” mode, it exports the exact same number of columns on the List tab of an application, then we can “Export Data” from that report. The process takes a few clicks, but processing time is usually less than one minute compared to 10 or 20 minutes. That’s a quick win. Also, once the user got used to it, they can extract any data that they like. That means less work for the Maximo Administrator.

How to generate INSERT script for Maximo data with SQL Server?

10 July 2024 / / 1 Comment

When dealing with a large amount of data or migrating workflow configuration, we might need to generate and use INSERT script. This article describes how to do it with SQL Server database.

Why do we need to generate INSERT script?

When deploying changes, the preferred method is DBC script. There are different tools to generate DBC scripts for many types of configurations. However, in certain cases, such as when migrating workflow configuration, we still need to use SQL script.

For deploying a data package, it is a good practice to script and deploy the change as part of the DBC deployment process. It is easier to track the changes with version control system such as GIT or SVN).

As an example, in a recent project, the users used Maximo in DEV to enter and validate the data. After they finished, I extracted the data using the method described in this post and loaded it to Production as part of the DBC deployment process. 

Do you know the UpdateDB or RunScriptFile tool can deploy SQL files directly?  It is not required to put SQL statements into a DBC file with the FREEFORM tag.

What is the limitation with SQL Server Management Studio?

With Oracle database, generating INSERT statements is quite simple with SQL Developer. All we need to do is right clicking on a table, then choose Export Data > Insert. However, with SQL Server, a similar function is buried deep in the drop-down menu and is hard to find. There are at least two other features with similar purpose in the same menu which causes confusion and makes people thought this is not possible with SQL Server. These functions don’t work for this requirement. They are:

  • Right-click on the table, then choose Script Table As > INSERT to
  • Right-click on the database, then choose Tasks > Export Data

For this requirement, we need to right-click on the database (not table), then choose Tasks > Generate Scripts…

However, this approach has some limitations:

  • It does not let you define a WHERE clause. You’ll need to generate script for the whole table.
  • With Maximo, all tables have the ROWSTAMP field which you need to exclude. Otherwise, the INSERT script does not work.

How to generate INSERT Script?

To address the limitations described above, we can create a VIEW or a staging TABLE from the source table. This allows us to set a WHERE clause to filter the data we want to extract and exclude the ROWSTAMP column. A VIEW is more convenient in a development project because we can define any rule to manipulate the data such as changing the ID field to a new range to avoid conflict. However, note that there is a bug with older versions of SQL Servers that it cannot generate script correctly for the Data Only mode. In such case, we can use a staging table instead.

To provide an example, below are the detailed steps to generate INSERT script for all “Fire Extinguisher” records in the ASSET table:

Step 1: Create a temporary table:

  • Use one of the two SQL statements below to get a comma-separated list of column names from the table. The first query uses the MAXATTRIBUTE table, and it only works for Maximo database. The second query works for any generic SQL database, but you will need the SELECT permission to the Information_Schema
SELECT string_agg(column_name, ', ') 
WITHIN GROUP (ORDER BY column_name) 
FROM information_schema.columns 
WHERE table_name = 'asset' AND column_name != 'rowstamp'
SELECT string_agg(attributename, ', ') 
WITHIN GROUP (ORDER BY attributeno) 
FROM maxattribute WHERE objectname = 'asset' 
AND persistent = 1
  • Copy/Paste the list of columns to write a CREATE TABLE statement below, then execute it to create the table. In this step, we can also transform or map any ID fields (such as ASSETID and ASSETUID in this example) to avoid conflict. 
DROP TABLE IF EXISTS tmp_asset;

SELECT [Copy/Paste the column names here]
INTO tmp_asset
FROM asset
WHERE description = 'Fire Extinguisher';

Step 2: Generate INSERT Script from the temp table:

  • Right-click on the database, choose Tasks > Generate Scripts…
  • In the Generate Scripts wizard, choose “Select specific database objects”, then select the tmp_asset table we created in the step above.
  • In the next page, “Set Scripting Options”, click on the “Advanced” button, under the General group, change the “Types of data to script” option from Schema Only to Data Only
  • Back to the Set Scripting Options, either choose to save the output as a SQL file or open it in a new query window.

What are the alternative methods?

If the method described in this post does not work for you, two alternative methods you can try are

Some problems with authentication in MAS and how to work around

16 June 2024 / / 0 Comments

The new Maximo Application Suite is still new to me. It took me a bit of time to get over some of the basics. This post contains a few quick notes, hopefully, it can save you a bit of time when facing the same problem. This is pure personal observation. The solution is based on trial and error. I haven’t dug through the official documentation to have a good understanding of how it works. Please don’t quote me if the information provided here is incomplete or if it doesn’t work for you.

Slow Login Process

In Maximo 7, opening the Maximo URL and getting past the login screen (with a saved password) usually takes 2-3 seconds. However, with MAS, the login screen is much slower to load. It is likely due to the SAML integration which we cannot do much about. After logging in, the Suite Navigator screen also takes a long time to load. The whole process takes about 15-20 seconds.

Once the screen is loaded, I reckon, 99% of the time, people will go to the Manage application (which is the old Maximo app).

To access Maximo faster, after logging in, I bookmarked the manage screen. The next time I can access Manage using the bookmark. Although I still have to log in, it will skip the Suite Navigator screen and get me straight to the Manage application. This saves me about 10 seconds.

REST API URL:

In Maximo 7.6.x, to retrieve a list of assets, I can send a request below:

https://[MAXIMO_ROOT]/maximo/oslc/os/mxasset?oslc.select=assetnum,location,description,status&oslc.pageSize=10&lean=1

It will result in something like this:

However, in MAS (Maximo 8.x), when I sent a similar request using POSTMAN with the same URL format, I got an HTML response with the error message: “You need to enable JavaScript to run this app.“

As it turned out, with MAS now, to access this same API endpoint, we will need to change the /oslc/ part to /api/ as follows:

https://[MAS_MANAGE_ROOT]/maximo/api/os/mxasset?oslc.select=assetnum,location,description,status&oslc.pageSize=10&lean=1

API Authentication

It appears to me that with POSTMAN both the Native authentication and the LDAP authentication method don’t work with MAS. The only method that seems to work is using API Key by sending in the apikey header as follows

On a side note, I noticed that, with the new API Key application, anyone who has access to it can see and use the API Key of any other users, including the maxadmin account. This seems like a security concern to me. Once have access to the keys, you can use integration tools like MXLoader to manipulate data while pretending to be someone else. Anyway,

How to suppress attribute logic with the NOSETVALUE flag?

1 June 2024 / / 0 Comments

Introduction to the NOSETVALUE flag

Over the last 5 or 6 years, I haven’t had to write any new custom Java classes. Most requirements today can be done without Java coding. Despite that, some logic is hard to achieve using automation script. In this post, I’ll talk about using the NOSETVALUE access modifier to deal with one of such scenarios.

According to the MboConstants API document, the NOSETVALUE is “Similar to the READONLY, except that no exception is thrown and the setValue() calls are suppressed”. This means when a field has the NOSETVALUE flag, it is not editable, but it also doesn’t throw an error if other logic tries to update it.

Requirement

In many places, when a field is updated, the Maximo will update some other fields. We want to retain the standard logic except for a certain field we don’t want it to overwrite. Below are two examples I have come across:

  • With the HSE add-on, when a user updates the priority of a work order, the target dates are updated according to the prioritization matrix. The client does not want the target dates to be overwritten.
  • When assigning a workgroup to a work order, Maximo automatically populates the Lead field. However, the client wants this mandatory field to be left blank to force the user to specify the correct person.

Solution

For the requirement above, in Java, we can store the original value of the affected field in a variable, call the super.action() function to run the out-of-the-box Maximo logic, then set the field with the original value again.

However, with automation script, we do not have such control. To prevent Maximo from overwriting a field, we can switch on the NOSETVALUE flag on the affected field before the execution of the action method of the trigger field. After that, we need to switch it off to make the field editable again.

The execution order of attribute events is as follows:

  1. Java – validate
  2. Autoscript – validate
  3. Java – action
  4. Autoscript – action

The out-of-the-box logic to update any fields is executed in step #3. Thus, with automation script, we can switch on the NOSETVALUE  flag at step #2, and then release it at step #4.

Implementation

To give an example, if we want to prevent Maximo from overwriting the Lead field when specifying the WORK GROUP (persongroup) field, we can create an automation script below:

  • Script Name: WO_PERSONGROUP
  • Launch Point 1:
    • Launch Point: WORKGROUP_VALIDATE
    • Object: WORKORDER
    • Attribute: PERSONGROUP
    • Events: Validate
  • Launch Point 2:
    • Launch Point: WORKGROUP_ACTION
    • Object: WORKORDER
    • Attribute: PERSONGROUP
    • Events: Action
  • Code:

Since the script is very simple, I combined two launch points into one script. This means, when the PERSONGROUP field is updated, it will be called twice. The first execution triggered by the Validate event will lock the LEAD field. After the out-of-the-box Java logic is executed, the second execution triggered by the Action event will release the lock to make the field editable again.

Happy coding.

Set XSLT file in a Maximo Publish Channel

How to transform XML messages in Maximo using XSLT?

23 May 2024 / / 1 Comment

Automation Script is powerful for implementing direct integration between Maximo and an external application. One main challenge of direct integration is transforming the XML message produced by Maximo into the format expected by the external system. For a simple interface, we can write scripts to construct a JSON or an XML document from scratch. However, with a complex interface where the message contains a few dozen fields, it is not ideal to handle all of the data transformation logic within the script. In such cases, we can use the standard XSLT transformation capability in Maximo instead.

The advantage of this approach is two-fold:

  • It separates the data transformation logic from the integration flow logic.
  • By using the standard integration framework, it is fully compatible with other functionalities such as the integration queues, message tracking, and message reprocessing capabilities.

The capability to use XSLT has always been a standard feature in Maximo for a long time. However, it is not widely used. It is because XSLT by itself is a language, and it takes some time to learn. To address this gap, I will provide an example below, in this post. It serves as a starting point. Once you have it running, modifying the XSLT file to map other objects is straightforward.

Requirement: transform a standard Maximo’s PO XML message to another XML message but in the format expected by an external system.

Implement: to make it easier for you to test, I used a standard Maximo demo instance.

  • Step 1: Create a po_v1.xsl file with the XSLT code below. Put it in a local folder on the Maximo server, for example, C:\Temp\po_v1.xsl
  • Step 2: Duplicate the MXPOInterface publish channel to create a new one named ZZPO. In the XSL Map field, point to the path of the XSLT file created in the previous step. Enable Event Listener on the publish channel
  • Step 3: Add the ZZPO publish channel to the external system EXTSYS1. Tick on the Enable? check box of this record.
  • Step 4: For the MXXMLFILE End Point which is used by the EXTSYS1 external system, set the FILEDIR field to point to a local folder, e.g. C:\TEMP

Now, whenever a PO is updated, instead of producing a standard XML message, it produces a transformed XML message as shown below:

Standard Maximo PO Message:

Transformed output XML message:

Note: It appears to me the XSL code is cached in Maximo memory. To avoid having to restart Maximo whenever I made some changes to the XSLT file, I renamed the file to a new version. It forces Maximo to refresh the XSL code when the publish channel is invoked.

Highlight records with color in start center

How to highlight Start Center Result Set with color

14 May 2024 / / 0 Comments

Highlighting work orders or service requests with color based on their priority and due date is a common requirement. While it is easy to implement this in the applications, it is harder to achieve with Start Center result sets as the functionality is quite limited.

I have seen this question pop up a few times in various forums but there hasn’t been a detailed instruction posted on the Internet. Hopefully, this post can provide enough details for the non-technical consultants to implement this for their clients.

I will demonstrate the steps to implement this with a simple requirement. A customer wants to display a list of Service Requests and highlight them in color as follows:

  • Priority 1 – Urgent: if the age of the SR is less than 30 minutes, display it as Green. If the age is less than 2 hours, display it as Orange. And if the age is higher than 2 hours, display it as Red.
  • Priority 2 – High: Green for less than 2 hours, Orange for less than 8 hours, and Red for more than 8 hours
  • Medium/Low: Green for less than 1 day, Orange for less than 3 days, and Red for more than 3 days

The functionality around color coding for Start Center Result Set is quite limited:

  • The expression is limited to a few simple operators such as Less Than, Equal, and Greater Than. And there is no option to use Conditional Expression.
  • The condition must be based on a field displayed on the result set

To workaround this limitation, we can use a non-persistent field, for which, we can use Formula or write an automation script to initialize a value based on a complex logic. The steps are as follows:

Step 1: Use the Database Configuration app, add a new field to the SR object, then Run Apply Configuration Changes

  • Attribute Name: COLORCODE
  • Data Type: Integer
  • Persistent?: Unchecked

Step 2: Use the Object Structures app, create a new object structure:

  • Object Structure: RPSR
  • Consumed By: REPORTING
  • Source Objects: add the SR
  • Use Exclude/Include Fields action: include the COLORCODE non-persistent field.

Step 3: Use the Automation Scripts app, create a new Script with Attribute Launch Point:

  • Launch Point: COLORCODE
  • Object: SR
  • Attribute: COLORCODE
  • Events: Initialize Value
  • Script Name: SR_COLORCODE_INIT
  • Language: Python
  • Source:

Step 4: Edit the Start Center template, add a Result Set to the Start Center, then edit it:

  • Application: SR
  • Query: All Service Requests (or select any Saved Query you want to use)
  • In the Object List, ensure to select Object Structure created in the previous step
  • Add the fields you want to display. In this case, I added Service Request, Summary, Reported By, Reported Date, Reported Priority, Color Code. Note that the new non-persistent field Color Code must be added
  • In the Color Options tab, set up the 3 color options as depicted in the image below.

If everything is set up correctly, the Start Center Result Set should display values in the Color Code column, and the records should be highlighted in the right colors

Troubleshooting:

  • If the Color Code field does not display a value, need to check the automation script which is responsible for initializing the value for the field
  • When adding fields to the Result Set, if the Color Code field is not there, it is because the custom object structure is not selected. By default, the Result Set will always have a “Service Requests” item in the object list, even if there is no Object Structure of type REPORTING created. If you have multiple object structures for the SR object, you can give the object structure a specific description to easily identify it.
  • After deploying the Start Center to production, there could be various issues that prevent it from displaying properly, please refer to my previous post for more details on how to troubleshoot it.
« Older posts Newer posts »

© 2024 Maximo Consultant's Blog

Theme by Anders Noren — Up ↑