City of Chicago Law Dept. Improves Info Access with Advanced Data Integration and BI

The City of Chicago’s Department of Law is the legal counsel for the City and its departments, agencies and officials. With 15 divisions and 270 attorneys, it is one of the largest legal organizations in Chicago. Size often brings inefficiencies, however, and when the Department of Law wanted to implement a Mitratech TeamConnect matter management solution, it first needed to convert, consolidate and migrate data from 14 disparate systems.

Going beyond adding the capabilities of an advanced matter management system, the Department of Law wanted to improve reduce the maintenance, staffing and training costs associated with so having so many different, unconnected systems.

The Department of Law, working closely with Mitratech, chose Dunn Solutions Group to handle the complex project. And the Dunn Solutions Group team designed a solution that would leverage the powerful data integration and data quality capabilities of today’s business intelligence software tools.

With the toolset and approach determined, there were a number of challenges to overcome:

  • Optimizing the conversion process to work seamlessly with TeamConnect
  • Converting a variety of loose-bound databases (Access, Paradox, Excel spreadsheets and custom applications) into a structured format
  • Coordinating and communicating with more than 60 users/managers of the source systems

The project team worked methodically to resolve the conversion, database, integration and coordination issues, and then began the ETL process. Utilizing an advanced data integration tool, Dunn Solutions Group first mapped and extracted data from the 14 source systems, normalized the data, and loaded it into a staging database. Then, after verifying integrity, the data from the staging database was loaded into TeamConnect using a universal migration script that enforced all business rules and maintained all relationships between objects.

The results: 99.9% successful data conversion and, according to the Department of Law, projected lower overall costs and significantly improved matter management.

With the data integration, consolidation and migration project completed, a second component of the project – the integration of TeamConnect with the City’s Hansen Building Permit System and the City-hosted Address Validation Service – began.

The Hansen system is used by the Department of Buildings to manage the permitting and inspection processes. When an inspection results in a building code violation, a notice is issued, and if the violation is not corrected, the violation becomes a legal matter in the Department of Law.

Using web services inside the City’s SOA infrastructure, Dunn Solutions Group built a bi-directional interface that feeds building code violation cases to the Department of Law, and feeds the results and relevant information back to the Hansen system.

The Address Validation Service is used by a number of City departments to validate and cleanse addresses as they are entered. To integrate this important web service with TeamConnect, Dunn Solutions Group built a custom connector that enables automatic validation and correction, and also fetches geocodes and other geographic attributes related to an address.

The results of these two integration projects: immediate access to violation case information for Hansen users, automatic generation of violation cases in TeamConnect for the Department of Law, and accurate, complete and enhanced address/geographic information inside TeamConnect.