April 29, 2013

Maximo and Excel Integration

This article is outdated! Checkout MxLoader tool.

After having written the two articles on importing and exporting data in Maximo, this morning I woke up with an insane idea.
Is it Excel capable of parsing an XML structures and issue an HTTP calls to Maximo Integration Framework?
Did you already got my idea? I switched on my tablet and did few searches. Yeeesss! I have all the pieces of the puzzle!

The idea is to integrate Maximo and Excel using HTTP calls to a MIF Object Service. This allows to avoid passing through the generation and conversion of CSV files to exchange data between Maximo and Excel.

Let's see how to do it.


Maximo Object Structure

Open Integration > Object Structure application and create a new entry.
  • Object Structure: D-PERSON
  • Description: Demo Person Object Structure
  • Consumed By: INTEGRATION
  • Support Flat Structure: True
  • Source Objects: PERSON


Remove unwanted attributes from the Object Structure. This will simplify the exchanged data and will greatly improve performances of the integration.
Select the Include/Exclude Fields menu and check all the Exclude check-boxes except the following:
  • PERSONID
  • FIRSTNAME
  • LASTNAME
  • DISPLAYNAME

Excel

Download the sample Excel spreadsheet. Open it and enable macros if prompted.
Open the Visual Basic editor. On Excel 2007 and 2010 you may need to enable the "Developer" tab as described here (Excel 2007) or here (Excel 2010).


Open the "Module 1" and modify the following row to match you Maximo environment.

Const URL = "http://MAXIMOHOST/meaweb/os/"

Now go back to the "Query" Excel spreadsheet and click on the "Query" button. If everything goes fine your spreadsheet will be automatically filled with all the people whose name starts with the letter A.


Isn't it really cool?

If you want even more 'cooling' you can take a look at the followup article about synching data.

April 9, 2013

Importing data from a CSV file into Maximo

This entry is part of the Maximo Integration Framework series.

In this tutorial I will show how to configure the Maximo Integration Framework (MIF) in order to import data in Maximo from a Comma Separated Values (CSV) text file.

The best thing is to start with an export. Please follow this article before proceeding.

To be able to export data using the MIF we must configure the following chain:
  1. Object Structure
  2. Enterprise Service
  3. External System


Object Service

If you have followed the steps described in the Flat CSV file export with Maximo Integration Framework article you should already have the D-PERSON Object Structure and a sample CSV file.


Enterprise Service

Go to Integration > Enterprise Services application and create the following entry.
  • Enterprise Service: D-PERSON
  • Description: Demo Enterprise Service for Person data
  • Operation: Sync
  • Object Structure: D-PERSON



External System

Go to Integration > External Systems application and open the D-EXTSYS you have previously created. Click on the Enterprise Services tab and add the D-PERSON Enterprise Service. Enable it and disable the Use Continuous Queue check if you are not 100% sure that your continuous queue is working (go back to MIF first setup article if you have doubts). Here is what you should see.



Updating records

It's now time to try to import some data into Maximo.
Create a copy of the CSV file that you have exported, name it Person.csv and leave just one person record.
Modify an attribute of the person just for a test. Do not modify the PERSONID attribute since it is the unique ID of the record.
Here is how my Person.csv file looks like.

D-EXTSYS,D-PERSON,,EN
DISPLAYNAME,FIRSTNAME,LASTNAME,PERSONID
Keith Mills,Keith,Mills TESTTTTT,MILLS

In the Enterprise Service tab of the D-EXTSYS External System, select the D-PERSON Enterprise Service and click on Data Import button.
Fill the form like this:
  • File Type: Flat file
  • Delimiter (default): ,
  • Text Qualifier (default): "
  • Import Preview: True
  • Import File: select your Person.csv file



Click on the OK button and you should see a message like this: BMXAA5621I - The data import preview mode is successful.
If everything's ok, click again on the Data Import button filling the same form but leave the Import Preview unchecked. Click OK and you are done.
Go to the Administration > Resources > People application, open the record that you have modified and you should see your updates.


Creating new records

As you probably already know each record in Maximo have a unique identifier that may be composed by one or more fields. You may have noticed that those fields cannot be excluded in the Object Structure. In our example the unique ID of the PERSON table is the PERSONID field.
Since we have used the Sync operation in the Enterprise Service, the MIF uses the following simple logic to understand is the imported record should be inserted or if it should update an existing one.
  • PERSONID is empty or doesn't match with any existing record in the PERSON table: Insert a new PERSON record
  • PERSONID matches an existing record in the PERSON table: Update the PERSON record

For example, if we want to create two new people we could use a CSV file like this.

D-EXTSYS,D-PERSON,,EN
PERSONID,FIRSTNAME,LASTNAME
AAAXXX,Aaa,Xxx
BBBYYY,Bbb,Yyy

Please note that I have moved the PERSONID attribute to the first position and removed the DISPLAYNAME attribute since it will be automatically calculated exactly as I would have created the person from the UI.

April 6, 2013

Flat CSV file export with Maximo Integration Framework

This entry is part of the Maximo Integration Framework series.

In this tutorial I will show how to configure the Maximo Integration Framework in order to export data from Maximo into a Comma Separated Values (CSV) text file.

To achieve such goal we must configure the following chain:
  1. Object Structure
  2. Publish Service
  3. External System
For more details about how the MIF works look at the Maximo Integration Framework overview.

If this is the first time you are using the Maximo Integration Framework, you should check that your MIF is properly set up before going ahead.


Object Structure

Open Integration > Object Structure application and create a new entry.
  • Object Structure: D-PERSON
  • Description: Demo Person Object Structure
  • Consumed By: INTEGRATION
  • Support Flat Structure: True
  • Source Objects: PERSON


Remove unwanted attributes from the Object Structure. This will simplify the structure of the output file and will greatly improve performances of the import/export process.
Select the Include/Exclude Fields menu and check all the Exclude check-boxes except the following:
  • PERSONID
  • FIRSTNAME
  • LASTNAME
  • DISPLAYNAME

Publish Channel

Go to Integration > Publish Channels application and create the following entry.

  • Publish Channel: D-PERSON
  • Description:Demo Publish Channel for Person data
  • Operation: Publish
  • Object Structure: D-PERSON



External System

Go to Integration > External Systems application and create the following entry.

  • System: D-EXTSYS
  • Description: Demo External System
  • End Point: MXFLATFILE
  • Enabled: True
  • Outbound Sequential Queue: jms/maximo/int/queues/sqout
  • Inbound Sequential Queue: jms/maximo/int/queues/sqin
  • Inbound Continuous Queue: jms/maximo/int/queues/cqin


Go in the Publish Channel tab and insert the D-PERSON channel created previously.


Don't forget to enable the Publish Channel and to save the External System.


Export data

To export the data stored in the Maximo PERSON table just click on the Data Export button. A dialog box will be displayed allowing you to enter an SQL where clause to filter to exported rows.
For simplicity just put a small number in the Export Count field to limit the number of exported rows.


Click on the OK button and your CSV file will be exported in a directory named flatfiles under the path specified in mxe.int.globaldir system property. In my environment the directory is D:\MIF\flatfiles. Here is how the output file should look like.

D-EXTSYS,D-PERSON,,EN
DISPLAYNAME,FIRSTNAME,LASTNAME,PERSONID
Tom Revis,Tom,Revis,REVIS
Fred Stanley,Frederick,Stanley,STANLEY
Keith Mills,Keith,Mills,MILLS
Mark Jacoby,Mark,Jacoby,JACOBY
Bob Pillance,Bob,Pilla,BPILLA


If the file does not appear in one or two minutes please verify your Maximo Integration Framework configuration.


Final considerations

Now you can include more attributes to your objects using the Include/Exclude Fields action of the Object Structure application or you can create your own object structure.
You may also try to import data in Maximo database or look at other articles about Maximo Integration Framework.

This is just the beginning of your journey in the MIF. Keep in mind that MIF is a powerful tool that allows to do many things.
Just to play a little bit let's do one last trick.
Go to Publish Channels application, open the D-PERSON object and select Enable Event Listener.
Now go in Administration > Resources > People and create a new person record. If you look in your MIF directory on the Maximo server and you should see that a new CSV file has been automatically created with the new person data.


April 3, 2013

List Maximo customizations

In this little post I will share few database queries to list the customizations made to your Maximo environment. It also applies to other TPAE based products.

Security restrictions

SELECT * FROM securityrestrict
ORDER BY objectname, attributename;

Custom objects and attributes

SELECT * FROM maxobject
WHERE userdefined=1
ORDER by objectname;

SELECT * FROM maxattribute
WHERE userdefined=1
ORDER by objectname, attributename;

Custom code
 
For an accurate analysis of customized Java classes you can use the IBM Maximo Customization Detection Tool. When the customization detection tool is executed, it generates a report that will list all of the services, object, and attribute classes that have been extended for Maximo or Maximo industry solutions and add-on products.
You may also try another technique to compare two different Maximo instances as described in this post.

April 2, 2013

Maximo performances and network latency

Maximo and other IBM service management products based on TPAE (like SmartCloud Control Desk) can be seen as sitting at the apex of a pyramid that consists of network, hardware, and software services.

As you can see, network performance are the foundation of the pyramid.
The simplest way of measuring network performances and speed is the ping command. I understand that this is not a professional measuring tool but it is almost always enough accurate for a first troubleshooting.

All you have to to is to issue the following command from some of the computers where users access the Maximo UI.

>ping -n 10 [MAXIMOHOST]

A typical output of this command looks like this.

>ping -n 10 xxx.zzz.com

Pinging xxx.zzz.com [1.2.3.4] with 32 bytes of data:
Reply from 1.2.3.4: bytes=32 time=132ms TTL=56
Reply from 1.2.3.4: bytes=32 time=148ms TTL=56
...

Ping statistics for 1.2.3.4:
    Packets: Sent = 10, Received = 10, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 130ms, Maximum = 148ms, Average = 133ms


Maximo is very sensitive to network latency. Ping times higher than 50 ms result in decreased performance in Maximo. The higher the latency the more Maximo is affected.

I have developed a small script that can be used to launch the above command and other useful information in a text file.

To mitigate the effect of network latency on Maximo performance, hardware compression and network caching can be either at the hardware level using compression devices such as Riverbed or at the software level using gzip. Refer to Best Practices for System Performance whitepaper for more details.