Clalit Health Services

World’s 2nd Largest HMO moves 2-Terabyte BI Data Warehouse to SQL Server 2008

As the largest health maintenance organization (HMO) in Israel, Clalit Health Services serves more than 3.8 million people, and supports 70 million customer interactions per year. The HMO uses the Microsoft® Application Platform to support some 16 terabytes of data, including a 2.7-terabyte business intelligence (BI) solution that serves as a repository for clinical and operational information. Clalit’s Enterprise BI Data Warehouse has become so important to healthcare professionals and clinic managers that the HMO decided on an early upgrade to Microsoft SQL Server® 2008 Enterprise to take advantage of new features including data compression; enhanced star join functionality to improve query performance; the Resource Governor, to help protect query performance; and support for spatial data types to help Clalit incorporate geographic and related data in planning service delivery.

Situation

Clalit Health Services, the largest health maintenance organization (HMO) in Israel, and believed to be the second largest HMO in the world, has learned the value of pulling business intelligence (BI) from its data stores to help the organization provide better care for its 3.8 million members as it supports more than 70 million customer interactions per year.

The HMO, which was started in 1911 when a group of 150 immigrant workers joined together to form a mutual aid healthcare association, today serves 54 percent of the population of Israel. The organization delivers its services with a staff of 34,000 employees, among them 7,500 physicians, 11,500 nurses, 1,300 pharmacists, 4,400 paramedics and laboratory/imaging technicians, and 9,400 administrative personnel. Healthcare is delivered through a network of facilities that includes 14 hospitals, 1,300 primary and specialized clinics, and a network of pharmacies and dental clinics.

Clalit has been a pioneer in harnessing IT to enhance delivery of healthcare. The Clalit IT infrastructure includes a network of more than 25,000 workstations and 2,200 server computers, running more than 1,000 applications.

While Clalit has a heterogeneous IT infrastructure, in recent years it has moved a significant portion of its operations onto the Microsoft® Application Platform, including Microsoft SQL Server® database software. The HMO currently stores 16 terabytes of information on SQL Server databases, including 2.7 terabytes in a dedicated BI data warehouse. Most of the critical applications are running on the Microsoft Application Platform including patient treatment, laboratories, billing and the SAP system.

We were happy to find that we could take advantage of the new features of SQL Server 2008 without having to upgrade our hardware infrastructure. Mazal Tuchler BI Manager, Clalit Health Services

The HMO’s Enterprise BI Data Warehouse was deployed some years ago using Microsoft SQL Server 2000 Enterprise Edition (64-bit) running on the Windows Server® 2003 Datacenter Edition (64-bit) operating system. The data warehouse serves as a repository for information gathered from Clalit clinical applications, as well as operational data from its SAP system. The Enterprise BI Data Warehouse’s 2.7 terabytes of information grows about 15 percent a year.

Patients benefit because no matter whom they see, or which facility they visit within the Clalit system, their healthcare provider will be able to access their complete electronic medical record, view lab results, and see exactly what medications they are taking. Physicians and other healthcare providers benefit because they have a complete picture of the patient. And clinic managers and the HMO’s analysts benefit because of the wealth of information and insights they can pull from the repository.

The value of the Enterprise BI Data Warehouse is evidenced by the fact that, 5,000 managers from the CEO to the field clinic managers, regularly access the solution’s 100 data cubes for online analytical processing (OLAP). An additional 400 analysts launch complex ad hoc queries against the BI solution.

Solution

As Clalit Health Services upgraded its Enterprise BI Data Warehouse to SQL Server 2008 Enterprise (64-bit), the HMO was particularly interested in four new features of SQL Server 2008:

• Data Compression. The Data Compression feature of SQL Server 2008 enables Clalit to store more data on hard drives, reducing demands on its storage systems. Data Compression also reduces the number of disk reads the databases must perform by incorporating more data into system memory.
• Star Join for Data Warehouse. SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star join query optimizations reduce query response time by recognizing data warehouse join patterns.
• Resource Governor. Clalit plans to use the Resource Governor feature of SQL Server 2008 to define resource limits and priorities for different workloads, and to help ensure resources can’t be unduly impacted by poorly constructed queries or other unusual workloads.
• Support for Spatial data types. SQL Server 2008 support for spatial data enables Clalit to more easily incorporate information on clinic locations, demographics, and other data helpful to analysis and planning.

The Microsoft Application Platform provides ease of integration, including incorporation of 3rd-party applications, which are found in the heterogeneous architecture

We learned from our use of the SQL Server 2008 Data Compression feature on our data warehouse that we could save a lot of space. Doron Ytshaki Chief Technology Officer, Clalit Health Services

Clalit used in creating its Enterprise BI Data Warehouse. The multi-tier architecture includes:

• ETL Tier. Information from clinical, lab testing, pharmaceutical, SAP, and other applications is prepared for importing into the Enterprise BI Data Warehouse using both IBM WebSphere DataStage for performing extract, transform, and load (ETL) processes and Microsoft SQL Server 2005 Integration Services 
• Database Tier. The 2.7 terabytes of the Enterprise BI Data Warehouse is hosted on a single instance of SQL Server 2008. The relational database is hosted on an 8-processor Unisys ES7000 server computer with an EMC Symmetrix storage area network.
• Analytics Tier. Clalit uses SQL Server Analysis Services to import relational data to create and manage 100 multidimensional data cubes to support faster query processing. Analysis Services is a middle-tier server for OLAP and data mining. Clalit is considering upgrading to SQL Server 2008 Analysis Services during 2009.
• Reporting Tier. Clalit managers and healthcare professionals use Microsoft ProClarity® business analysis software to generate reports against the data cubes. SQL Server 2008 Reporting Services and Microsoft Office PerformancePoint™ Server 2007 business intelligence software are used to create dashboard displays and key performance indicators (KPIs) to help decision makers stay current on areas of interest. Reporting Services is a comprehensive, server-based solution for creating, managing, and delivering managerial pre-planned reports to support daily operations and decisions. Analysts launch ad hoc queries using SAP Business Objects software.
• Presentation Tier. Clalit users access the Enterprise BI Data Warehouse using a browser, avoiding the need for downloading client-side applications. Microsoft Active Directory® directory service is used to provide the security of role-based access to data.

“We are always seeking new ways to provide more insight from organizational data using our BI system,” says Mazal Tuchler, BI Manager at Clalit Health Services. “Our integrated Microsoft oriented BI solution, enables us to develop new solutions fast and manage them easily, bringing more value to the business.”

The design and implementation of the upgrade was done by Clalit's team with the help of Assaf Fraenkel, SQL Server Architect from Microsoft Consulting Services and Stuart Ozer from the SQL Server Customer Advisory Team.

Benefits

The Microsoft Application Platform gives Clalit the decision support it values as it continually looks for ways to enhance healthcare delivery and operational efficiency. The HMO found that its early upgrade to SQL Server 2008 helped it to enhance query performance. This upgrade also enabled Clalit to take advantage of spatial data types, and protect query performance using Resource Governor.

“We learned from our use of the SQL Server 2008 Data Compression feature on our data warehouse that we could save a lot of space,” says Doron Ytshaki, Chief Technology Officer at Clalit “This fact is very important for us and will be key factor for upgrading our other databases as well.”

Our integrated Microsoft oriented BI solution, enables us to develop new solutions fast and manage them easily, bringing more value to the business. Mazal Tuchler BI Manager, Clalit Health Services

Valued Decision Support
The healthcare providers and clinical managers of Clalit Health Services have learned to value the decision support they gain from their Enterprise BI Data Warehouse. Healthcare professionals benefit from the wealth of patient data at their fingertips, and benefit from their ability to identify best practices for specific chronic conditions, such as diabetes or congestive heart failure, by analyzing treatment plans across a large patient population. On the operational side, clinic managers can use the BI data to allocate budgeting and manage resources.

In its efforts to constantly enhance patient care, using the Microsoft BI solution, Clalit applies BI in analyzing clinical histories across its millions of patients, and creates KPIs for deploying best practices in patient care in order to ensure better health service quality.

Clalit has developed KPIs for more than 60 medical conditions. “Using our Enterprise BI Data Warehouse, we codify best practices and measure every clinic and all medical staff according to their performance for each KPI,” Tuchler says. “For example our KPIs for diabetes call for yearly eye examinations, thus enabling staff to identify problems early, and enabling physicians to work proactively to protect the patient’s vision.”

Enhanced Query Performance
Clalit anticipates enhanced query performance from the star join query optimizations of SQL Server 2008.

“Our initial testing shows we’ll see 50 percent to 60 percent data compression using SQL Server 2008,” says Tuchler. “We hope that while compressing data reduces our hardware demands we will also benefit from faster query performance.”

The star-join query optimization feature of SQL Server 2008 enables faster query responses by supporting finer grained partitions when joining data sets, which can substantially reduce query size. Tuchler notes: “Our tests indicate we see a 20 percent benefit in query processing time using the star-join optimization feature in SQL Server 2008.” I

n addition, a new feature of minimal logging for bulk operations will shorten some ETL processes by 50 percent. This feature will help Clalit to refresh the data earlier to the customers.

Support for Spatial Data
With more than 1,000 clinics, Clalit is interested to use spatial capabilities available in SQL Server 2008, to help it incorporate geographic information into its Enterprise BI Data Warehouse.

The Resource Governor feature of SQL Server 2008 will be an excellent safeguard to protect query performance across the system. Mazal Tuchler BI Manager, Clalit Health Services

“We have already launched a pilot project using Business Objects and MAPINFO which ebables users to present the result of an ad-hoc query on a map,” says Tuchler. “We would like to embed spatial abilities available on SQL Server 2008 in order to expand this solution to the query itself. Clalit can highly benefit from this new option in decisions with geographical contexts, such as determining optimum placement of new facilities, and related considerations.”

Performance Protection with Resource Governor
Even with more than 5,000 users, Clalit has remained impressed with the query response performance of its Enterprise BI Data Warehouse. But looking ahead to future growth and increasing demands, Clalit plans to take advantage of the Resource Governor feature of SQL Server 2008 to help ensure user satisfaction.

“As our Enterprise BI Data Warehouse has become ever more critical to daily operations, we want to ensure that performance can’t be degraded by a poorly executed or run-away query,” says Tuchler. “The Resource Governor feature of SQL Server 2008 will be an excellent safeguard to protect query performance across the system.”

Improved Performance without New Hardware
Testing by Clalit IT demonstrated that it could upgrade to SQL Server 2008 without replacing its existing hardware. “We were happy to find that we could take advantage of the new features of SQL Server 2008 without having to upgrade our hardware infrastructure,” says Tuchler. “We’ll get even better performance from our existing hardware.”

Summary
Clalit upgraded its 2.7-terabyte Enterprise BI Data Warehouse to SQL Server 2008 to take advantage of new features including Data Compression, Star Join for Data Warehouse, Resource Governor, and support for spatial data types.

 

Windows Server 2008, SQL Server 2008, and Visual Studio 2008
Windows Server 2008, SQL Server 2008, and Visual Studio 2008 provide a secure and trusted foundation for creating and running your most demanding applications. Combined, the products offer advanced security technology, developer support for the latest platforms, improved management and Web tools, flexible virtualization technology to optimize your infrastructure, and access to relevant information throughout your organization.

For more information about Windows Server 2008, go to: www.microsoft.com/windowsserver2008

For more information about SQL Server 2008, go to: www.microsoft.com/sql/2008/default.mspx

For more information about Visual Studio 2008, go to: www.microsoft.com/vstudio

For More Information
For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234 in the United States or (905) 568-9641 in Canada. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to: www.microsoft.com

For more information about Clalit Health Services products and services, visit the Web site at: www.clalit.co.il/HE-IL/english

 

This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

Document published July 2008