Examining the issues facing the broking community, Ian Bowen focuses on how efficient information systems can act as a catalyst in transforming business

One of the perennial problems for brokers is how to achieve quick and easy access to comprehensive management information and business intelligence. Merger or acquisition exacerbates the problem when different systems are inherited and consolidation across the business becomes a major headache.

Oliver Laughton-Scott, of M&A specialists IMAS, makes the following observations:

"If management information systems can't tell you which clients are making money, they aren't working. Brokers need to understand which customers they should retain.

"If brokers are embarking on acquisitions, they want to be able to analyse any hard core of unprofitable business to be able to dispose of it post-acquisition."

Whether or not there has been any recent or historical M&A activity, it is not unusual to find several disparate broking systems within brokers offices, home grown or package solutions. These tend to operate in isolation from each other and any attempt to consolidate information across them tends to be time consuming and ultimately unsatisfactory.

The Holy Grail is for brokers to be able to implement a solution quickly that can provide comprehensive management information and business intelligence. Given that this is a perennial problem, what is getting in the way of a solution?

There three main barriers are: access to data; having a data repository that facilitates reporting; and having a reporting tool that not only provides that required information, but can also distribute it around the organisation.

Why is access to information so limited? The answer to this lies in the way in which the host systems store their data. Brokers with one or more of the major quotation and policy management engines, such as those supplied by Sirius, Misys, Cheshire Data, Sectornet, SSP etc will be familiar with the design that steps the user through the process of ascertaining who the customer is, which product they are interested in, capturing the key underwriting criteria and then providing and retrieving the best price-based on the criteria.

This is also true of the policy management systems used by insurers. It is not surprising that these systems are designed to process such transactions efficiently, but this has a drawback when it comes to providing meaningful information based on the data that the transactions have stored.

The structure of the data stored is not conducive to providing you with meaningful management information.

Query tool
Typically the legacy systems providers will supply a query tool, which will allow simple inquiries, but these have their limitations in terms of which data they can access and the time it takes to run them, particularly if the production system is active at the same time.

It is often also true that the query tool will only work on one legacy system platform, making consolidation across multiple systems virtually impossible.

By way of example, the following information is often difficult, if not impossible to provide from a single legacy system, let alone multiple systems:

• New policy report

• Cancelled policy report

• Premium reserves report

• Debit overdue report

• Exceptional new claims report

• Detail product reports (for example, motor, household, travel, payment protection)

This is often exacerbated for the brokers by their relative lack of knowledge of the structure of the host systems databases and the consequential difficulty the users can have in accessing their data using standard query tools.

What can be done to make it easier to provide businesses with the information they need?

The answer lies in moving the data out of the host systems into a database that provides much easier access for the business users. There are tools available that are configured to extract data from the above-mentioned commonly used host systems and reformat it for ease of reporting.

If data is extracted from these host systems and placed elsewhere in an information-friendly repository, all the above pitfalls, in particular having access to limited data and performance issues, can be overcome.

Having pre-configured tools like these under the control of the user community is essential for effective business management. For example, if a chain of brokers is looking to establish how much business it has placed with Norwich Union (NU), it could find that some of its business is still recorded with Commercial Union (CU) or Guardian Royal Exchange (GRE).

The transformation part of the process will automatically translate CU and GRE into NU for reporting purposes. This translation should be made available in user-definable tables to reflect the ever-changing nature of the insurance markets.

This simplistic example illustrates that cleansing and changing the data held in the host systems need not be an exercise that is slowed down through the involvement of IT departments.

The modern way of holding data is in dimensional data marts (or databases). Dimensional data marts are relational databases that are optimised for reporting purposes.

Unlike operational, transaction-based, databases, dimensional data marts do not use the principle of normalisation to ensure that data is held only once.

In a dimensional database it is acceptable to hold the same data more than once so it makes the reports and queries easier. Normalised databases are good for transaction-based update systems, but dimensional databases are ideal for reporting.

Dimensional data marts hold their data in two types of tables, known as fact tables and dimension tables. In the insurance model, facts are based on policy, claim and accounting transactions, with 'snapshot' accumulations to summary level and the dimensions that define the facts include data such as agent, product and insured object.

Significant benefits
Having these structures and the ability to pull data in from a number of sources makes it easier for enterprises to keep all their information in one place and provides easy access for staff and external bodies. This has significant benefits, such as automatically providing data to the FSA taxonomies to satisfy the regulatory reporting requirements.

The storage of data in the fact and dimension data marts outlined above provides a significantly improved access to information using the many reporting tools, such as Crystal Reports, ORA and Oracle Reports available in the market.

The structure of the data marts makes reporting and querying a simple task. All reports are essentially based on queries that join together a fact table and one or more dimension tables.

The dimensions provide the detail and group levels of the report; the facts provide the totals and counts that need to be reported on.

It is the task of the reporting tool to provide the summary amounts at change of product and agent, and the count of policies in each group.

This is functionality that is common to any reporting tool.

With this level of sophistication, the less likely it will be that the use of reporting tools will require IT skills or assistance.

The interesting point to note is that these solutions exist today and are pre-configured for the broking community, so that access to all the data is readily available and therefore information and business improvement:

Having a strategy for accessing and distributing information is vital.

In many respects, given the ease of access to data provided by the fact and dimension model, the key requirement for enterprise reporting is to provide reports for many users who have different roles and responsibilities.

For example, head office managers will require access to all the consolidated information contained within the data marts.

There may be a head office with company-wide reporting facilities, with regional managers who may well work in different offices and who need access to their local information only.

The key feature of reporting tools under this solution is their ability to reflect the organisation structure of the business and to be able to distribute information around the organisation securely.

Not only will brokers be able to deal with the issues highlighted by Laughton-Scott, they will also be able to track them on a regular basis and distribute the issue to the person within the enterprise best placed to deal with them. IT

' Ian Bowen is sales director of IT-Freedom