Last Updated on November 19, 2022 by maximosecrets
The Work View is not a database view but an application in Maximo with some surprisingly special capabilities for an application that does not allow inserts, updates or deletes. The Work View was designed to provide a single view of all the tickets and work orders that a user is responsible for or where they are a member of the responsible team. It can be used to significantly improve the performance of Start Centers where there are several million tickets and work orders.
Work View Application
The Work View application will be found in the Administration module. There are no tabs, actions or a toolbar. The application is entirely read-only.
As you can see it shows the set of records from the Ticket Classes (SR, INCIDENT, PROBLEM) and the Work Order Classes (WORKORDER, ACTIVITY, CHANGE, RELEASE). As I am using a system with O&G installed, I just tried creating a new DEFECT and then took ownership of this record and it appeared at the bottom of this list. DEFECT is a class of ticket.
The only thing that you are likely to do in this application is create a public query and save it. This is because the Work View application is used to create Result Set portlets that you add to a Start Center.
The two queries which are provided are as follows:
- OWNERLIST – Owner is login user
where (historyflag =0 and istask =0) and ( owner=:USER or ownergroup in ( select persongroup from persongroupteam where resppartygroup=:USER OR RESPPARTY=:USER))
- WORKVIEW – Work View (Sorted by Priority)
(historyflag=0 and istask=0) and (owner=:USER or ownergroup in ( select persongroup from persongroupteam where resppartygroup=:USER or respparty=:USER)) and status not in (select value from synonymdomain where domainid in( ‘WOSTATUS’,’SRSTATUS’,’INCIDENTSTATUS’,’PROBLEMSTATUS’) and maxvalue in (‘RESOLVED’,’COMP’,’CLOSED’)) order by priority
The “Owner is login user” query shows tickets and work orders where the user is either the owner or a member of the person group that is the owner of the ticket or work order. It does not show closed records or work order tasks. This query supported the original design intent of the application.
The “Work View (Sorted by Priority)” query shows tickets and work orders where the user is either the owner or a member of the person group that is the owner of the ticket or work order and where the status would be considered open, i.e. not a COMP work order or a RESOLVED ticket. It does not show closed records or work order tasks. The result is ordered by priority.
What we can gather from these two queries is:
- The Work View includes closed tickets and work orders
- The Work View includes work order tasks
- There is more than one status synonym domain for tickets, and only one for work orders
The WORKVIEW query with an order by priority will place low priority work orders before high priority work orders. This is because for tickets priority is reversed to work orders 1 (high) – 4 (low), and work orders the priority range is 0-999 (high).
I just created a new work order 1369 – Work View Test, with one task T1271 – Work View Test Task.
When the record is saved in a Ticket or Work Order based object it is copied to the Work View, you can see both the work order and task when you filter in Reported Date for records created today. Tasks have a class of ACTIVITY.
The chevron in the Record column (RECORDKEY) is the Go To menu specific to the Class of record. For WORKORDER it is Work Order Tracking, for ACTIVITY it is Activities and Tasks application, but as I have O&G installed there is also Operator Tasks (Oil).
I navigated to Work Order Tracking and work order 1369, changed status to APPR and then returned and refreshed the query. Both the work order and task have had their status updated on their WORKVIEW records. I have also done a test with creating a Service Request and a Work Order and then deleting them, they were both deleted from the Work View.
There are no table window details to show the other fields held in the Work View object. I have previously added one successfully.
If the primary purpose is to help build and save queries that can be shown in a Start Center Result Set portlet, then you may need to add a few more fields to the Advanced Search to make it easier to build queries without having to always resort to SQL.
The object WORKVIEW is defined as a System level object with just a few fields that are mostly common to tickets and work orders.
- AFFECTEDPERSON, AFFECTEDPERSONNAME, REPORTEDBY, REPORTEDBYNAME
- ASSIGNEDOWNERGROUP, OWNER, OWNERGROUP
- CLASS, RECORDKEY, OWNERID
- ORGID, SITEID
- REPORTDATE, TARGSTARTDATE
- TICKETID, WONUM (non-persistent)
All the fields are persistent except TICKETID and WONUM which are non-persistent. Notice there is no LocationA physical place where assets exist and where work can be performed. More or Asset referenced.
If you do add fields to the WORKVIEW object you will need also to populate its records for the columns added. As inserts, updates and deletes on tickets and work orders are replicated to WORKVIEW then the number of records in WORKVIEW should be the count of the number of records in TICKET and WORKORDER tables. If it isn’t the same and you aim to use the Work View records, then this should be corrected.
Most of the fields have Same As Object/Attribute and so you should be covered if you extend the length of any of these fields.
There are indexes on:
- OWNER, OWNERGROUP
- RECORDKEY, CLASS, SITEID (unique)
- STATUS, CLASS, SITEID, PMGROUP, OWNERID
View Work Details
The only places I know where the WORKVIEW object is used in Maximo applications is the View Work Details action which you see in the detail menu of Assets, Locations and Configuration Items and in their applications.
It is also found in the Select From WO Hierarchies and Relationships button below the Multiple Assets, Locations and CIs table window in Work Order Tracking.
Start Center Result Sets
The Tier 2 Service Desk Console has an example of the Work View being used in a Result Set portlet, it is the colourful one on the right-hand column, and middle portlet.
If you click in the first row for Incident 1264, the Incident application will be launched with Incident 1264 loaded. Similarly for SR 1273 it will load the Service Request application and SR 1273. If you use the last button in the portlet – Open Result Set in the Application it will open the Work View application, and so nothing can be gained from this.
You cannot filter by Class and then open the application for that class, that would be very useful.
If you can configure a result set the Result Set Setup application opens. You can see that this result set is only showing the Open Tickets and Work Orders that the user owns, taken from the query called WORKVIEW, which we saw earlier.
The top table window shows the columns that could be selected, that haven’t already been selected. The bottom table window shows the order of the selected fields to display.
The Color Options tab is where the colour coding by priority has been set up.
Something to be aware of is that the field called APP (Application) contains the name of the application to launch. I reassigned Defect 1285 to AJE – Andrew Jeffery and added the Application column to the Result Set Portlet. You can see that for the Defect the Incident application will be launched. A case has been raised with IBM Support.
Extending Work View
In this section I’ll show you how to add a Location and Asset to the Work View and how it will be populated when a record is updated.
In Database Configuration I have added two new fields:
- ASSETNUM with Same As ASSET, ASSETNUM and defined as UPPER 25
- LOCATION with Same As LOCATIONS, LOCATION and defined as UPPER 12
I have also added crossover domains to two existing non-persistent fields:
- TICKETID has new Crossover Domain called TKT2WV
- WONUM has new Crossover Domain called WO2WV
You’ll need to perform a Structural Change and will need to be in Admin Mode to do so.
In the Domains application I have created a new Crossover Domain TKT2WV – Ticket Crossover to WORKVIEW, it is based on Object TICKET and has a Validation Where Clause of – ticketid=:ticketid
There are two crossovers with Source Field and Destination Field set the same, for ASSETNUM and LOCATION.
In the Domains application I have created a new Crossover Domain WO2WV – Work Order to Work View Crossover, it is based on Object WORKORDER and has a Validation Where Clause of – wonum=:wonum and siteid=:siteid
There are two crossovers with Source Field and Destination Field set the same, for ASSETNUM and LOCATION.
I have returned to the Work Order Tracking application for work order 1369 we created earlier today and entered an asset which populated the location field. In Application Designer I have added two columns in the WORKVIEW application for Location and Asset.
When you load the Work View application and query for work order 1369 the Location and Asset field are now populated with BR450 and 11450 respectively, the same values added to the work order.
Each save of a work order or ticket will cause the corresponding Work View record to be updated and the crossover domain will be triggered and will copy over the additional attributes. This is a special crossover because it fires on update of a non-persistent attribute, crossovers normally only fire on change of value on a persistent attribute.
Don’t forget that you will need to populate the existing records in WORKVIEW table with the attribute values from the corresponding TICKET or WORKORDER records. When I did this on a development environment with 20 million plus records, it was quicker to truncate the WORKVIEW table and repopulate it with an Insert statement.
Work View Performance
Over the years I have spent some time on database performance, not just with Maximo. I have been lucky enough to spend time working on a database in excess of 20 million work orders. There were in excess of 1,000 queries and the majority of these worked against the WORKORDER or TICKET tables. There was 45+ queries that took longer than 1 minute to execute.
Some very slow running Start Centers were reworked against the WORKVIEW and the average performance improved by >400%. Some that took more than 2 minutes to open, were opening in 20 seconds and were providing the same results. Most queries had a set of complex subqueries and the initial query on WORKORDER simply was not selective enough in many cases. Some queries actually ran faster with no index on WORKVIEW i.e. with a Full Table Scan it could still perform significantly faster.
Of the 45 work order indexes, several were greater than 1GB in disk space. As performance issues grew the number of indexes increased. There were more than 10 indexes that included the STATUS column, all of these needed to be updated every time you changed status on a work order, and if there are 100 tasks, that is a lot of reworking of indexes.
The conclusion of the analysis was that as the WORKVIEW table was relatively small, and would contain at most 30 columns, it wouldn’t matter if it did perform a full table scan. It was likely to filter out significantly more records before it had to test against the WORKORDER or TICKET tables as part of a subquery and hence would perform better. But this was not the plan.
Analysis showed that a couple of “super” indexes on WORKVIEW would be needed each with a matching index on WORKORDER or TICKET. The idea behind this super index was to make the initial query highly selective so that the subquery to work order or ticket would mean that fewer records needed to be evaluated.
We found a way of being able to measure the performance of all queries, so that we could replace the Start Centers with WORKVIEW based queries one group at a time, or even one Start Center at a time, if that was wanted. We were convinced that the running of all 1,000+ queries in a dedicated system would drop from over 4.5 hours to less than 1 hour.
The side benefit of the proposal was that we could significantly reduce the number of WORKORDER indexes, especially those involving the STATUS attribute. On the assumption that many of these indexes had been added to support Start Centers, it was probably better to return to the indexes provided in OOTB Maximo and then find the ones that the highly customised system actually needed and then document the reasons why they were needed.
What I did learn after reviewing literally hundreds of Explain Plans over a 2-month period was that most queries on WORKVIEW with a subquery to WORKORDER gave fantastic performance with two matching indexes, one on each table. The WORKVIEW index contained 10 columns, and so in disk space terms was perhaps 30% of the disk space of a full table scan.
It was all a proposal, and I do not believe it has ever been implemented, but I did enough analysis to know that if you are going to grow to a large number of ticket and work order records, then it is probably best off building your Start Centers around the WORKVIEW table.
Leave a Reply