Proposed Solution

My proposal is to create a data warehouse facility in OpenQuote. This would be an incredibly simple implementation of a Data Warehouse and would not be intended to replace fully functional data warehouses. The purpose would simply be to provide an easy way to present OpenQuote data (which by default is held as XML in blob data fields) for either reporting purposes, or for extraction to other systems (including dedicated data warehouses).

The OpenQuote Data Warehouse would be populated either by a scheduled job, or at run-time for each individual transaction that occurs in OpenQuote (a transaction being a policy status / data change - quote, write, MTA, renewal etc.). Basically a snap shot of the OpenQuote policies is taken and saved in the data warehouse, these snap shots will have an expiry date associated with them, all but the most recent policy versions having the expiry date set.

If OpenQuote is configured to update the data warehouse at run-time, each time a transaction occurs on a policy, a copy of that policy will be written to the data warehouse.

If OpenQuote is configured to run the scheduled job for updating the data warehouse, the job will copy all quotes/policies in OpenQuote across to the data warehouse in one go.  All previous non-expired policies recorded in the data warehouse will have their expiry dates set at this point.

When a policy is copied across to the data warehouse data elements will be held in the data warehouse as individual fields in a table (not as XML).  Three types of data element exist:
Generic - data that is generic across all products (i.e. premium, broker etc)
Product Specific - data that is specific to an individual product (i.e. house construction)
Not Required -  data that is not required for the data warehouse.

Data that is specified as generic, will be put in a data warehouse table that has policy data from all products in it. Data that is specified as being Product Specific will be placed in a data warehouse table designed specifically for that product. Data that is not required will simply not be exported to the data warehouse.  By default data should be consider as Product Specific.

When the export process takes place, if a transaction occurs for a product that has not previously had data transferred to the data warehouse, a product specific table will be automatically created to hold any Product Specific data (minimal fields initially - transaction reference, expiry date etc.).

If the export process comes across any data fields that require transferring to either the Generic or the Product Specific tables, but have never previously been transferred, then a new field is created to hold that data element.

Clearly policies are not a flat structure, so nested elements will require their own data warehouse tables to be created as required.

I need to flesh these ideas out a bit, and some areas need further clarification and detail, however hopefully this makes a good starting point for discussion.

Matt

There are two types of companies that I can think of who may use OQ:

1.  Small, who just want default reports from a system.
2.  Larger, who want to access the raw data so they can build data cubes and pivot tables, they then can distribute these to the business users.

I guess my proposal for a 'Data Warehouse Lite' is really most suited to the second type of company, where you want to provide specialist data analyst and warehouse type people easy access to the raw data (preferable in a traditional relational DB structure). You want them to access the data without them impacting (either performance or integrity) the live OpenQuote system.  These companies will probably also want to import the data into thier own warehouses where it can be merged with other systems data for company wide reporting.

For the Smaller users, purhaps we could look at creating pivot tables for them directly form OQ, allowing them to create thier own custom reports in Excel - rather than trying to second guess everyones needs (and product design).

Matt

I like the sound of this. It certainly simplifies things considerably, and frees us from having to perform a xml->relational mapping which is very likely to be a painful exercise for a totally generic solution.

It would be reasonably trivial for OQ to place messages into a JMS point-to-point queue from which plugins can pick them up and do whatever is necessary in order to integrate with external data warehouse solutions. We could provide a sample plugin which simply retains the quote XML pretty much as is, and use something like Birt (http://www.eclipse.org/birt/phoenix/) to generate some sample reports.

Birt might also be a good approach to canned reports like broker bordereau - especially if the report templates sit in Alfresco thus making them available through OQ's normal eclipse integration.

There might be an argument for supporting both point-to-point and pub-sub, but for different reasons.

For communication to a data warehouse or back end, point-to-point is really the only option because it guarantees that messages will be delivered even if the recipient isn't listening when the message is queued. Whereas pub-sub guarantees to deliver messages only to those who are listening when the message appears in the queue. Plainly for data warehouse and backend services guaranteed delivery is essential.

JMS is a standard which defines how Java interacts with message queue implementations - much like JDBC defines how Java interacts with databases. Most J2EE servers bundle a queue implementation, JBoss included. But in a large organisation an OQ installation might well be configured to use some other commercial queue implementation, e.g. IBM's Websphere MQ, or Oracle's AQ.

Dick.

I think we might even make it a bit simpler than that. We simply guarantee that all quotes will be placed into a single point-to-point queue. If an organisation wants their deployment of OpenQuote to feed more than one back office system, they can implement something which splits our one feed into many.

I think this makes sense because any organisation big enough to want to do this will also, probably, have their own mechanism - which may not involve queues at all in the JMS sense. So a single feed gives them the minimum that they need and also keeps things nice and simple.

Dick.

Another thought.

As the solution we offer covers more and more of the insurance world the data we need to capture and persist will extend.

The various artefacts that we need to produce to support that will need to be addressed: screens, alerts, sub-processes etc. Should this not be the point at which we design a base template for a document?

Data mining is a future concern but are we sure we want to try and find a solution to something we haven't finalised yet?
If not shouldn't we be considering all the other items we haven't yet done?

Also, Data mining tools are not a documenting/reporting tool. This is a mistake many companies have made. Data mining universities are in fact used by reporting tools.

Mark