Hi,

We are working on an ETL solution where data is to be pulled everyday from Maximo using JSON API. We are wondering of a situation -

Day1 - We have received lets say 1000 records from Maximo to a SQL DB
Day2 - Maximo Deleted some 100 records which are already sent on Day 1 to SQL DB.

So how we can trace back the deleted 100 records on Day2 to delete them from SQL DB as well to keep MAXIMO data and SQL DB data in sync.

Any suggestions?(Remember we are fetching data from MAXIMO using JSON API)

Thanks,

------------------------------
Jatin Aggarwal
------------------------------

#Maximo
#AssetandFacilitiesManagement

Hi Jatin,
I am not sure there is a simple way to handle this unless you are doing a complete replace action each time so completely refreshing your dataset in the SQL database with the current records from Maximo which obviously could have a performance cost.
Is this an ongoing action that you will be running as normal business operations or is this a short term task?

The option that is normally used in integration from Maximo in this case would be a push from Maximo when a record is deleted as opposed to pulling data using the API.
The only other method i can think of would be to write the recordkey to a custom object in Maximo when a record is deleted, then use the APIs to check this new object and use it to delete the row from your SQL database and also then use an API to clear the new object in Maximo once they have been processed.

Hopefully this will give you something to look into and maybe someone else in the group has encountered this before and may have other solutions.

Good luck

Steve

------------------------------
Steve Lee
Maximo Technical Sales Specialist
IBM UK
Leeds
------------------------------

Agreed with Steve. Setting up publish channels to send when a delete occurs would be the best way and make it near real time. Because the data doesn't exist, there is no API that helps you identify it after it has been deleted. There is a way to perform delta syncs with the API (so only retrieve records that have changed since you last pulled), but it doesn't help with deletes. 

If the table is small enough, you can configure an audit table on the record and it will track deletes. But audit tables on WO can be painful as there is no index by default and it often gets quite large, especially if attributes are being audited.

------------------------------
Steven Shull
Director of Development
Projetech Inc
Cincinnati OH
------------------------------

Hi Jatin

Best way would be an event listener inside Maximo that informs the ETL (and ultimately the system on the other side) that the records have been deleted and to do the same.

 

The only real alternative is to check whether the records still exist in Maximo from the other side, but that could get very costly.. For example, day 1 to 500 1000 records a day are loaded. Day 2 you need to check for 500, day 2 1000, day 3 1500 etc etc... Unless you were to implement a cut off time.

 

The polling method could potentially be made more intelligent by either not actually deleting (setting a status for example) and looking for that, or possibly populating a third table with the ID of records that have been deleted and just checking that and deleting from the source system based on those values.

 

In any scenario there needs to be 2 way communication between Maximo and the source system of some sort, the question is who initiates the conversation.

Hope that helps.
(Response via Steven Lilley)

------------------------------
Helen Fisher
Marketing Manager & Business Development, BPD Zenith
Happy to be contacted at helen.fisher@bpdzenith.com
------------------------------