Lodi is home to just over 60,000 people and despite its small size, the city has a very profitable local wine industry, producing more than $350 million worth of wine grapes annually.
The city has invested heavily in its technology infrastructure and its commitment to e-services to better serve the community. The city’s information systems (IS) department is responsible for maintaining this and the city’s financial and planning data and sharing this information both internally and externally, with customers, businesses and taxpayers.
But financial and planning systems data was kept in many disparate databases, making sharing it very difficult. IS had set up a Microsoft Access database and used hand scripting and coding in order to uniformly import data from all of the different constituencies. But the process eventually became cumbersome as the city’s systems multiplied over the years.
That’s when the IS team decided to investigate data integration tools as a means to best access data to support the city's growing number of projects and relieve some of the data maintenance burden. The requirements were fairly straightforward: they needed to transfer data from one database to another securely, efficiently and quickly.
The city was using a variety of database servers, including Oracle, MySQL, DB2, and SQL Server, while the core financial and billing systems were housed in IBM DB2, so the solution had to be versatile. Manual execution was becoming a burden, so the new solution also had to be fully automated. Additionally, it needed to be centralized, as multiple team members would be working on projects at different times.
After evaluating a number of options, the team settled on extract, transform, and load (ETL) based data integrator software that also features a built-in scheduler to automate transfer jobs, eliminating the need for the old manual process involving Access. The data integrator gives the team the ability to write a transfer, save it, and schedule it to launch at a later time.
The city is now able to share information and connect with its customers more efficiently thanks to its ability to rapidly and securely share information. For example, the data integrator is used to create files from the databases that become source files for web pages, for both internal and external customers. This means that the public can look up business license information, as well as the city’s public utility accounts, and have their results delivered online.
Because the data on the database is housed internally, for obvious security reasons, sharing this data electronically previously would have been too risky as well as burdensome. With the new solution, the data is easily exported automatically into another database platform from the core systems, and the data can then become source files, giving the public the information they want and need without exposing core systems to security threats.
Transfers that would have previously taken days can now be done in a matter of minutes, allowing the IS department to focus its resources elsewhere.
Picasa
Jay Mishra is the Vice President of Development at Astera Software.
