To accelerate the development of its SQL query processes and make its users more autonomous in the utilisation of business datamarts, the MFA has chosen, rather than a classic ETL utility, the data morphing tool Amadea d'ISoft. A reallly recreational utility!
THE ENTREPRISE. Created in 1930, the Mutuelle Fraternelle d'Assurance (MFA) was originally the health insurance company for Parisian coachmen, chauffeurs and taxis. In 80 years its territory has expanded considerably, since the "Fraternelle" offers all kinds of contracts for property insurance (IARD), not only to transportation specialists but also to the general public (vehicles, homes, vacation, health, risk). With 86 000 insured customers and managing nearly 150 000 contrats, this insurance company has a network of 20 agencies and offices in France.
THE PROBLEM. Faced with competition and the legal limits of the market, insurance companies must be very dynamic. They need pertinent, easily accessed and timely indicators.
When Nicole Salmeron, director of the MFA domain, integrated the company in 2003 the decisional system in place was not well adapted nor was the organisation using it. There were no defined indicators, business information was spread around in an unstructured manner (Excel tables and e-mail). The relation infocenter which had been in place a decade (specifically conceived on PowerBuilder with an Oracle database) had been finished by restitution utilities Business Objects and SAS, but they were complex and not often used. Its weekly input was especially long, done with PL SQL procedures which took a long time to develop, demanded a great deal of time to load and were complex to maintain and advance. In addition the data production structure of the company was complex: "Our two largest databases, Damages and Production, did not describe the same things in the same way," explain, Nicole Salmeron.
This is where the need to review the decisional system came in. "We had to be more dynamic to respond to our users' needs, in particular the marketing services and sales forces." It started with finding a solution to facilitate the loading and manipulation of data, accelerate query procedure development, constitute business datamarts and driver indicators, and give the users more autonomy.
THE CHOICE. The MFA had confided its systems audit to SSII Bull to find solutions. First track studied: to turn towards an ETL utility (MFA evaluated that of BO) which automatically generates SQL code. But most ETL utilities cost too much for the Mutual's budget. Bull then presented the MFA utility Amadea. It is not exactly an ETL (even if it does extract and load data) but a "data morphing" utility: it transforms very large volumes of raw and heterogeneous data into useable information for refined analysis (the MFA tables contain sometimes up to 5 million records). This transformation process passes by a succession of elementary operations and gives immediate access to all of the intermediate results. Permanent access to data, interactivity of the system that requires no programming, its treatment in real time, the acceptance of very large volumes of data, integrated reporting : these are some of the characteristics of Amadea that seduced the MFA. A demonstration realised to obtain a cross table from a base of one million records is success in itself. Nicole Salmeron was conquered right away : "It's visual, graphic, logical, immediate !" Apart from the clarity of the graphical representation, what seduced her in the utility was its iterative processes : "One conceives, one tests, one constructs, one manipulates the data thinking all the while. If one makes a mistake, one returns and starts over : an error doesn't cost anything! "
UNFOLDING OF THE PROJECT.. The MFA thus opted for an ISoft utility the beginning of 2005, with the acquisition of a Studio/Server license (the users have access to the utility in web mode). A pilot project realised with the Bull team started up with the commercial department to constitute a dynamic configurable datamart (with a data model in star mode, adapted to the problematic of interrogations by utilities such as BO) and operational indicators on the commercial activity of the agencies. Putting this project into place took six months, for the insurer hoped at the same time to transpose the old PL SQL programs to enter "clean" data into the Amadea referential (correcting code errors). Once the commercial datamart constituted, three other business datamarts were put into place: Damages Control, Risk Control and Marketing. And nothing was fixed: the evolution is an essential, integrated part of this iterative system ("our decisional system is constantly under construction"). The Amadea method is operational in the whole company today, used by the general direction as well as by the agencies.
THE RESULTS.Saving of time and flexibility of processing : this are the great additions the utility has made. The alimentation of a process database (the heaviest operation) only takes three to four days while before, with PL SQL, it took between fifteen days and three weeks. Inventaries that once took a day or two of elaboration are now finished in a few minutes. There can be daily alimentation of the infocentre. "It save enormous amounts of time!" beams Nicole Salmeron. The iterative process of the utility allows refinement of the reflection on the data for analysis, to review the basic calculation of other parameters, to review data keeping other variable elements in account (evolution of points of compensation, for example). The utility facilitates the work of comprehension (especially work on the notion of the multidimension is easier) as well as traceability in the construction of data and the chaining of selection sequences.
Cherry on the cake, the utility is really fun : "I enjoy myself enormously with Amadea!" exclaims Nicole Salmeron who also appreciates the "very structured and orderly side" of the results furnished: "The results can be shown to someone outside of computer sciences, and he understand exactly what they are about." The effects of processing are also a strong point of the utility, thanks to its data compacting algorithms there is no need for more powerful machines. "Amadea is much more than an ETL!" concludes Nicole Salmeron.