Outbound integrations from Maximo: Is there a reason why materialized views are not a suitable choice?

Question

I want to set up an outbound integration from Maximo 7.6.1.1 to an external system. Both Maximo and the external system have Oracle 12c databases.

In the external system, I want to select open WOs from the Maximo WORKORDER table for the purpose of near real-time data analysis.

---

The standard integration options seem to be:

1. Flat file
2. XML File
3. Interface Table
4. Web Service

---

I've noticed that materialized views are not usually thought of as a valid integration option.

For example, I could create a materialized view in the external system on the Maximo.WORKORDER table via a dblink. The materialized view could be set up a number of different ways, including refreshing on a schedule, near real-time, etc..

As far as I can tell, a materialized view would be a fairly robust integration technique. However, I've not heard of any of my peers ever using materialized views for Maximo integration purposes.

Is there a technical reason why materialized views are not a suitable choice for Maximo integrations?

Example:

Both databases need to be late-model Oracle dbs in order for materialized views to be possible, which is not always the case.

Answer 1

Oracle's Materialized Views are an excellent tool for the data modeler and database designer. They are, literally, one of the best implementations of MVs in the database market (my main experience is with Oracle, and I have worked with SQL Server and PostgreSQL: I have not worked with DB2 or other databases). As an instantiated view they provide all the power of traditional views, but with additional functionality for constructing higher performing, constrained and indexed "table" objects. I guess one could think of an MV as being a table with metadata describing their construction and refresh.

Why are MVs not more widely used?

Firstly (1), in the case mentioned, access to Maximo, there is always the problem of being able to access the schema of a proprietary product.

Secondly (2), for an MV (or a view) to be successful it has to access the underlying data correctly which requires an comprehensive understanding of the data being accessed. Access to the types of data suggested here can be had using MVs and various techniquest but it may not be the best way (see 3 below).

Thirdly (3), database tables in proprietary systems are part of a dynamic environment of transactional and process oriented data. So, the need to ensure the correctness of an MV in 2 above, applies here as well.

Fourthy (4), very possibly ignorance. How many Oracle professionals have I met who have been on courses to educate them about MVs? Probably very few (I could myself among this group). Why do I think I can write this reply given what I just admitted? I guess it is because I have been around Oracle and other databases for all my professional life. I have had situations where I have had to build transactional->datawarehouse type solutions which required a large and sustained knowledge acquisition for MVs (used extensively for Oracle to Oracle replication).

Fifthly (5) Extending 4 is the observation that people are wedded to the tool they know best. If that is a desktop or web GIS then that is what they know. Access to databases in production environments does tend to be restricted so the sort of "sand pit" for learning is inaccessible unless full stack dev/test/prod databases exist.

2c Simon

Answer 2

Both databases need to be late-model Oracle dbs in order for materialized views to be possible, which is not often the case

With one slight modification, I would guess that this is exactly the reason why integration via materialized views isn't commonly proposed as a "standard" method of integration. The modified reason would be as follows:

Both databases need to be Oracle databases owned by the same company.

Very few companies are going to give trading partners (vendors, customers, whatever) TNS-level access to their production database. So, a materialized-view approach is only viable for intra-company integration.

I guess that's too narrow a use case for the approach to be considered a general-purpose integration technique by people who talk about and write about such things.

That said, if Maximo (I don't know what that is...) exposes its data model in a way that you can safely query it for outbound integration purposes, then I see no technical reason not to take advantage of the fact that both your databases are Oracle and that they're both your databases.

Non-technical reasons may involve standards. For example, several of my clients enforce architecture standards that require all inter-group integration to go through Oracle Service Bus. That is, rather than having a web of systems all talking to each other in different ways, everything just talks to a single thing (OSB) and in the same way.