In my last post on public queries, I talked about problems with some of the current setups of Maximo users public query management. As a Maximo system administrator, there are ways to manage this, but user training is often the number one way to get his right. Teach them to not use the Public Query button. Even better, manage the queries they will need through the MaxQuery account and share them with everyone, using parameters and Start Centers!
To begin with, check out IBMs “Improving Start Center Performance in Maximo” site. There is some very useful information here. As an administrator, this should be a great starter set of items you monitor and control in your Maximo system.
On a personal note, I take issue with the statement in the above article that says to limit Start Center result sets to 1-2 per Start Center. That is inadequate in most systems to properly represent the required work that needs to be monitored. My experience is that a properly tuned system can manage 5-7 properly designed result sets for a user base of ~200 people. I have helped many clients build blazing fast systems with no noticeable load time in this range and many times greater. If you need help tuning your servers and/or your result set queries, reach out to us! We would be happy to assist.
Along with making things run smoothly, we should also make sure that queries are not user specific. DO NOT hardcode user IDs into queries if at all possible. When you need to have a group of labor or person records in a result set, put them in a person group. Are you using the result set to represent the logged in user? Utilize the :USER parameter in the = field.
ex: select * from workorder where istask = 0 and siteid = ‘BEDFORD’ and historyflag = 0 and labor = :USER
Hardcoded IDs are a nightmare to manage in the long run. They can be used as a stop-gap until a proper process is put in place, but move to a user or group based parameter as quickly as you can.
A Case Study
Another way to keep your queries fast is to make sure there are no wildcards within queries. A few years ago I was working with a client that had a problem where users were sharing queries created from the Advanced Search popup. It started rather simply with the first user creating a useful query. It ran in about 5 seconds (slow) and returned the information he needed. He then shared this query with a coworker who added fields and changed values in the Advanced Search, and added the query to his start center. Each time they passed the query from coworker to coworker, the amount of wildcards used kept expanding, and the query times got slower and slower. After about 20 users had shared the query, and added them to their start centers, they were having problems logging into Maximo. Their default logout time was 5 minutes, and their Start Centers were not loading within the 5 minute period. While waiting for their Start Centers to load, they were logged out! That is the impact wildcards can have even on a large system.
What happens when you use the Advanced Search in Maximo? It adds a wildcard to both the front AND end of every field you populate. This means no indexes are utilized and EVERY record has to be scanned for each wildcard search. Multiply all of those full table scans across 20+ users and huge enterprise historical databases, and you have yourself a handful!
So, what did we do to help? First off, we had the administrators clear the queries from the users’ profiles and started from scratch. We created a query that met the needs of everyone, with input from team members and team management. The next step was to remove all wildcards from the queries and used parameters to point to their crews, person groups, or userid where applicable. Next, we used the same query for every user. Working with the DB administrators, we helped build indexes to improve the performance of the ‘high impact’ queries in the system.
What was the outcome? Users Start Centers loaded in under 1 second. System administrators were happy as they were no longer receiving calls about login issues. DB Admins were happy because their databases were no longer being overloaded with long running queries. Oh, and the Result set count on the Start Centers? 3 KPIs and 5 Result Sets. Yep, it can be done!
How does this all come back together on the topic of ‘Public Query’? Well, all of the result sets within Maximo that are associated with a user group on a pre-defined template require a public query. Having these managed properly and efficiently makes the Maximo world a happier place for everyone.
Queries are a powerful part of Maximo. When used properly within Start Centers, they can decrease the amount of time it takes to complete and administer work within Maximo, along with helping to expedite Work Order completion. It can also decrease the amount of time needed for users to be in the system, which allows them more time to be there to do what you hired them for, MAINTENANCE!
If you have any questions or want to talk further about this, please reach out to us. This is the kind of work that makes everyone feel better about the investment in Maximo.
Thanks for reading through all of this. If you have any comments, questions, or ideas to add, please comment below! I appreciate any input. Is there a topic you would like to see covered? Leave a comment or send me an email.
For the intro blog post to this topic, link back to Page 1 of this blog post set.
Are you using Maximo Work Centers yet? We can help you get there!