Seine-Saint-Denis District Council

The Water and Sanitation Department of Seine-Saint-Denis optimizes its IT system

The Water and Sanitation Department (DEA) of the district council of the 93rd 'Département' of France has restructured its IT system by business field starting with its databases. Its objective? To have a single base that brings together all information specific to one particular field rather than as many bases as there are applications. This restructuring would not have been possible without implementing synchronization programs among the various databases; a task successfully accomplished with the help of DataStudio ETL.

In the very midst of restructuring their IT system in 2001, the Water and Sanitation Department of the Seine-Saint-Denis district council created an IT System Branch, which in 2004 started reporting to the district's IT Systems Department; it then began brainstorming on how to handle its data. In 2006, the DEA launched a wide-ranging restructuring plan for its IT system: with over 70 business applications, each with their own database, the DEA managed a multitude of redundant data. Present in several different applications, the data was also stored according to models specific to each application. Finding data when required was often not simple.

As a result, the information, located in several places, was not always coherent or systematically updated, to name just a few problems. To put an end to this plethora of disparate data, the DEA pragmatically decided to totally overhaul the system first, relying on DataStudio to institute a data flow exchange that would eliminate redundant data. Then, starting with this new, healthier base, it launched a wide-ranging project of revamping of its information system.

Setting up the ?triangle of data?

First of all, the DEA strove to identify and classify the data in its IT system according to three fundamental criteria, thereby defining their ?triangle?: reference data (present or used in several applications), temporary data and, finally, thematic data (specific to an application). Restructuring the information system called for a first stage entitled ?Evasion?, during which the DEA started revamping and maintenance on 17 of the 70 existing business applications. For this, the company selected two database standards for all applications: one from a market publisher (Oracle) for large volumetrics, and PostgreSQL (open source) for lesser needs. Equipped with its ?triangle of data? and its database standards, which would help eliminate the technical problem of heterogeneous sources, the DEA set up three bases for the 17 applications, one for each business activity: urban planning, water quality and metrology. The DEA thus avoided redundancy issues. Each base hosts unique data and the applications access the information indifferently according to the nature of the query. The DEA thus rationalised its organisation significantly by reducing its number of databases from seventeen to three.

In each of these three bases, data is classified according to the triangle: temporary data, thematic data specific to an application and reference data used by at least two applications. At the level above ?EVASION? is the Geographic Sanitation Information System (SIGA) and the ?CERISE? (Control of industrial effluents and of wastewater treatment plants) database, which manages the information and inspections conducted at the industrial sites in the Seine-Saint-Denis district. These two contain the reference data for "EVASION: reference data extracted from the SIGA and CERISE is necessary for synchronising with the reference data in the ?Evasion? stage.

Cette organisation est la genèse du besoin d'un outil ETL concernant un premier axe, l'informatique décisionnelle avec le chargement régulier de données agrégées dans les entrepôts de données, et un second : l'aspect synchronisation de bases transactionnelles.

This restructuration is what instigated a need for an ETL tool for one area: business intelligence software with regular loading of aggregated data into data warehouses, and for another: synchronising transactional bases.

In other words, the DEA is gradually setting up databases, which bring together operational information by field, and synchronising them with the referent systems.

Redundant in fact, because present in several bases, the data has nonetheless gained in reliability and coherence since it is now only entered in just one place and immediately updated in the other bases.

An organisation based on DataStudio programs

To set up this organisation, seemingly complex and yet still much simpler in terms of data management and coherence, the DEA relied on DataStudio. ?Only a few days were needed to conduct the first synchronisation between the Cerise base and our reference base,? explains Chimère Ndoye, project manager at the Rosny-sous-Bois IT Systems Branch, who worked on the ?Evasion? program as well as on the deployment of the ETL/DataStudio tool. ?It was our first time working with DataStudio and just the fact that it is based on SQL language made it easy to pick up. We have a good command of SQL; it was therefore very easy for us, after just a few days of training on the DataStudio philosophy, to carry out our own synchronisations by ourselves.?

At the same time, by using the Data ETL, the DEA avoided having to technically connect the bases. This redundancy by synchronisation was conducted for security reasons, thus preventing a too powerful adherence between the databases. Totally stand-alone and independent, they communicate using a common language: a topology that encourages scalability. On the users' side, the gains are tremendous, according to Chimère Ndoye. Where before they had to wait for the technical teams to be available, today users are completely autonomous. The synchronisations between bases are automatic and, if necessary, a user can even launch one from their work station, by simply clicking on a button and triggering the remote call via the DataStudio SOAP module.

The DEA of the Seine-Saint-Denis district council

The Water and Sanitation Department (DEA), one of the Seine-Saint-Denis district council's technical departments, is in charge of fighting floods and pollution and maintaining local heritage, while also ensuring the safety of the people, both residents and staff working on the network. Over 40 allied crafts work together within this department, primarily financed by a sanitation tax and with a staff of some 300 agents.

www.seine-saint-denis.fr