Israel Police needed to unite data from more than 20 intelligence applications to give its officers a faster and more efficient tool for solving crime. They needed an enterprise grade extract, transform, and load (ETL) tool for performing complex transformations on feeds before adding information to its new online analytical processing (OLAP) intelligence system. After testing Informatica PowerCenter against Microsoft® SQL Server™ 2005 Integration Services, Israeli Police decided to use Integration Services for the ETL component of its OLAP solution. The 2-terabyte intelligence system, running on a single instance of SQL Server, helps Israeli Police intelligence officers complete data searches in seconds that once took days to assemble from disparate systems. Integration Services has also proven easy to incorporate into custom applications created using Microsoft Visual Studio® .NET.
Israel Police is a national organization that traces its roots back to months before the inception of the State of Israel on May 14, 1948. The country’s early leaders knew that providing a safe environment for its citizens would require a highly professional and dedicated police force in addition to its armed services.
||Many of our data feeds require complex transformations, so we need an enterprise-grade ETL tool. SQL Server 2005 Integration Services provides the robust ETL we need, and it is easy to use.
Manager, IT Intelligence Unit
Israel Police Technology Division
Today Israel Police is regarded as one of the most progressive, best-trained, and most dedicated forces in the world. It is responsible for everything from enforcing traffic laws to handling matters of state security and public safety, from preventing white-collar crime to controlling riots and uprisings, to acting as ambassadors of goodwill in the community.
Israel Police helps protect a population of more than 6.7 million with its force of 27,000 officers, spread over six districts, 17 sub-districts, and some 70 police stations. As with police forces throughout the world, Israel Police has had to continually grow professionally and technologically to keep pace with the ever changing world of crime.
In recent years, Israel Police deployed several intelligence applications to help investigate and prevent organized crime, but the collection of solutions running on mainframe and other platforms, was difficult for intelligence officers to use. The fact that the intelligence applications were spread across a number of disparate systems made it impractical to search for patterns and other insights across multiple sources.
Israel Police needed to find an enterprise grade extract, transform, and load (ETL) and OLAP solution that would enable it to pool data from more than 20 separate information systems into a centralized database that would help its officers analyze the intelligence to solve old crimes and prevent new ones.
Israel Police, working with Microsoft® Gold Certified Partner Ness Technologies, created the Intelligence Knowledge System, based on a relational database OLAP solution for intelligence analytics. The multi-tier solution uses Microsoft SQL Server™ 2005 Integration Services for ETL, and SQL Server running on the Windows Server™ 2003 Enterprise Edition operating system to host the 2-terabyte database and a 1 terabyte XRS index to facilitate text retrieval and directory search. Both Windows Server 2003 and SQL Server 2005 are part of the Microsoft Windows Server System™ integrated server software.
The decision to use SQL Server 2005 Integration Services was significant to the project because performing ETL to unite the disparate data sources was critical to the project’s success. The Israel Police project team initially planned to use Informatica PowerCenter for ETL. Microsoft invited Israel Police to join a technology adoption program, and after a Microsoft Consulting Services proof of concept demonstration using SQL Server 2005, Israel Police chose to use SQL Server 2005 Integration Services for ETL.
Application development was performed using Microsoft Visual Studio® .NET development system and the Microsoft .NET Framework. The .NET Framework is an integral component of the Microsoft Windows Server operating system that provides a programming model and runtime for Web services, Web applications, and smart client applications. The deployment uses Microsoft Office System and Microsoft BizTalk® Server 2004, which connects people and systems together through manageable business processes that help customers to orchestrate interactions in a highly flexible, automated manner.
||We don’t plan on discarding any data because in police work you never know what information might later prove useful. … SQL Server scales well so it will support our need to continually add more information to the database.
Manager, IT Intelligence Unit
Israel Police Technology Division
The Intelligence Knowledge System was created using an n-tier architecture, including:Presentation Tier.
Information is accessed through a secure intranet connection. The presentation application was created using the WinForms feature of the .NET Framework. The presentation tier is hosted on load-balanced Web servers running Microsoft Internet Information Services (IIS) version 6.0. Web services are used to access the application tier to create queries to run against the OLAP intelligence system. The presentation tier supports extensive mapping for geographically linked displays.Application Tier.
Created with Visual Studio .NET and the .NET Framework, the application tier uses COM+ components to manage queries to the database. Visual Studio was used to incorporate XRS functionality into the intelligence application. The intelligence application works with the database to pull together information from a range of sources to help police identify patterns or relationships that might help solve or prevent crimes. The application tier supports complex link analysis that searches for significant patterns and relationships (for example, whom known criminals associate with and transact business with). The application tier also includes a geographic information system (GIS) for mapping data to locations.ETL Tier.
SQL Server 2005 Integration Services provides the enterprise-grade ETL environment required for integrating daily feeds of data from more than 20 sources for use in the OLAP-based Intelligence Knowledge System.Database Tier.
The data tier includes two databases: A 2-terabyte relational database running on a single instance of SQL Server; and a 1-terabyte XRS index running on a separate instance of SQL Server. Each database runs on its own two-node active/active cluster. Both 2-node clusters are hosted on HP ProLiant DL585 4-way computers with 8 GB of RAM. Storage is on a fibre channel HP EVA 5000 storage area network with 76 disks. The solution supports some 1,500 concurrent users, accessing some 30 million intelligence objects. (Intelligence objects can include people, organizations, cars, bank accounts, and a range of other objects of potential interest.) SQL Server also stores documents and multimedia objects including short video and audio files stored as binary large objects (BLOBS). Reporting Tier.
Reports are generated using SQL Server Reporting Services. Data analysis and ad hoc reports are created using Transact SQL (T SQL) to explore some 200 million links between intelligence objects.
The Intelligence Knowledge System has some 1,700 users spread across about 100 police stations in Israel.
Israel Police has enjoyed a number of benefits since deploying its Intelligence Knowledge System, including: enterprise-grade ETL with SQL Server 2005 Integration Services, better intelligence with its solution supported by SQL Server, faster reporting, and enhanced integration.
SQL Server 2005 Integration Services is an essential part of the solution. “We are importing data from mainframe applications and other sources, more than 20 different feeds altogether,” says Jacqueline Levy, Manager of the IT Intelligence Unit, Israel Police Technology Division. “Many of our data feeds require complex transformations, so we need an enterprise-grade ETL tool. SQL Server 2005 Integration Services provides the robust ETL functionality we need, and it is easy to use.”
Better Intelligence with a Unified Database
Israel Police intelligence officers have a much more powerful working environment now that they can conduct searches across a single data store instead of having to deal with more than 20 separate intelligence applications and data sources.
“Each time an intelligence officer wanted to know something about a person, he or she had to go to perhaps 10 different systems and gather information on what the criminal had done in the past, what kind of car they drove, what type of weapons were used, whom they associated with, and so on,” says Tzahi Matar, Development Manager at Ness Technologies. “They used to have to work with a programmer who would use a report generator to search each system. “With the old system, a single search across several systems could take days to complete. Today, from a single screen, intelligence officers can work on their own, exploring the full range of data and get virtually instant results from whatever queries they want to create.”
The solution also provides innovative ways of displaying data. “An intelligence worker can, for example, project a mapped display showing the criminal records of every person living within 200 meters of any given intersection of Jerusalem or any other city,” says Matar. “This provides an entirely new way of exploring data and searching for significant relationships between data points.”
The database supporting the solution is expected to increase in size continually, because all information is potentially useful. Israel Police plans to upgrade the database from SQL Server 2000 to SQL Server 2005. “Our Intelligence Knowledge System should grow even more valuable the larger it gets,” says Levy. “We don’t plan on discarding any data because in police work you never know what information might later prove useful. There are thousands of possibilities in police work. SQL Server scales well so it will support our need to continually add more information to the database.”
SQL Server Reporting Services has simplified the process of generating reports from the intelligence data. “Reports that used to take half an hour and more to create are now completed in less than a minute,” says Levy.
Israel Police is always looking for new ways to pull value from its data, so it is important to have an efficient development environment that easily integrates with the database. “We saw immediate benefits because we were developing our intelligence solution using Visual Studio, and using SQL Server 2005 Integration Services to transform data,” says Matar.
Bringing so many data sources together requires powerful ETL tools. “The system merges all information available to the police into one big database and the intelligence user can issue queries that take the whole aspects of a person into account,” Matar says. “Bringing data from 20 different systems, including from mainframes, requires a lot of transformation before it can be pumped into database that forms the heart of the system. The earlier SQL Server ETL tools were good, but not powerful enough for the transformations we were doing. We needed SQL Server 2005 Integration Services. Integration Services gives us enterprise-grade ETL and seamlessly integrates with Visual Studio, so it is much easier to create solutions than using third-party ETL tools.”
Microsoft Server Product Portfolio
For more information about the Microsoft server product portfolio, go to:
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is comprehensive, integrated data management and analysis software that enables organizations to reliably manage mission-critical information and confidently run today’s increasingly complex business applications. By providing high availability, security enhancements, and embedded reporting and data analysis tools, SQL Server 2005 helps companies gain greater insight from their business information and achieve faster results for a competitive advantage.
And, because it’s part of Windows Server System, SQL Server 2005 is designed to integrate seamlessly with your other server infrastructure investments.
For more information about SQL Server 2005, go to:
© 2006 Microsoft Corporation. All rights reserved. This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Microsoft, BizTalk, Visual Studio, Windows Server, and Windows Server System are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are property of their respective owners. Document published December 2006