Customer Solution Case Study
/ / Public Health Management To Be Improved by Health Data Warehouse
Overview
Country or Region: Puerto Rico
Industry: Healthcare
Customer Profile
With 5,000 employees and an annual budget of more than U.S.$500 million, the Puerto Rico Department of Health (DoH) operates healthcare facilities and administers public health policy.
Business Situation
The DoH lacked a centralized way to maintain and access healthcare statistics. This impeded effective public health management and made it difficult to conform to the Healthy People 2010 initiative.
Solution
Using Microsoft® SQL Server™ 2005, the DoH and partner Nagnoi developed a Data Warehouse for the island’s healthcare information.
Benefits
n Reduces costs
n Removes duplicates
n Imports heterogeneous data
n Centralizes security
n Provides dynamic analysis / “The health of our people is a serious business and we have to have the right tools and information to do our job the best way possible. The Data Warehouse will help us achieve that.”
Olga C. Valentin, Director of Technology and Information Systems, Puerto Rico Department of Health
The Puerto Rico Department of Health (DoH) needed a centralized way to track public health data, an improved process for retrieving healthcare statistics and reports, and to provide users with dynamic multidimensional analyses capabilities. With partner Nagnoi, Inc., the DoH planned a Data Warehouse for all of the island’s healthcare information, starting with data feeding from 11 of its most critical internal systems, including Vital Statistics, the patients’ Electronic Medical Record (EMR) system, and the Medical Assistance Program system. After considering several database options for the Data Warehouse store and a third-party tool for extraction, transformation and loading, the DoH selected Microsoft® SQL Server™ 2005 Enterprise Edition as its project solution.
Situation
“The fuzzy lookup and fuzzy grouping capability of SQL Server 2005 helps us to remove duplicates and to merge the information from diverse systems and sources.”Néstor V. Figueroa, Partner, Nagnoi, Inc.
Many residents of the continental United States think of Puerto Rico in terms of travel: its spectacular beaches; its rich culture and history, with its Indian, African and Spanish influences; the diverse environments of this tropical island; and Puerto Rico’s abundant nightlife, entertainment, and gaming.
Beyond these perceptions, the Commonwealth of Puerto Rico has its own unique population, social institutions, strengths, and problems. One social problem is that a large segment of the population cannot afford healthcare or health insurance; a compensating strength is a Commonwealth-funded health service through the island’s largest healthcare providers.
With 5,000 employees and an annual budget of more than U.S.$500 million, Puerto Rico’s Department of Health (DoH) operates healthcare facilities and administers public health policy throughout the island. The DoH, like the health departments of all the other United States states and territories, has joined the Healthy People 2010 initiative. Healthy People 2010 is a “set of health objectives for the Nation to achieve over the first decade of the new century.” In order to meet these objectives, every state and territory has to be able to measure the health of its populations statistically, and demonstrate improved health in its population over time. The DoH collects public health data through surveys, campaigns, interviews, surveillance systems, and the census.
As of 2004, however, the DoH “had no systematic and integrated way to track, monitor, and measure the progress of Healthy People 2010,” according to Néstor V. Figueroa, Partner at Nagnoi, Inc., which is a partner to the DoH. “The healthcare data we had was scattered across many islands of information, on many different platforms, and in many different formats. Access to the information was bureaucratic: If you needed a report, you submitted a request, and waited days or even weeks for it to be run on a mainframe. If you request a change to the report, you’d have to go through the process again. We needed to enable a more democratic and instantaneous access to health statistics for the DoH, epidemiologists, and the other programs and organizations within the DoH.”
In addition, Puerto Rico has been going through dramatic healthcare reforms. One of the key initiatives has been the implementation of the Health Information Integrated System (HIIS), for which the Data Warehouse plays a key role in the integration of the data. An important concept being implemented as part of the HIIS is the Master Patient Index (MPI) or a unique identifier for each patient. The objective is to propagate the MPI throughout all systems at the DoH, including the Data Warehouse, which serves as the integrator that puts all the pieces of the puzzle together. This improves healthcare, since it automatically ties the healthcare information to the correct person.
“Our objective is to be able to have the information readily available when we need it and in the right format,” said Olga C. Valentin, Director of Technology and Information Systems, Puerto Rico Department of Health. “The health of our people is a serious business and we have to have the right tools and information to do our job the best way possible. The Data Warehouse will help us achieve that, in addition to providing our users with the capability to do different types of data analysis, which is something available now to just a few people in the organization.
“An important goal of the Data Warehouse is to be able to manage public health in a more proactive way by integrating alerts in the system, detect patterns early, and collaborate as a result of the analysis. In addition, the Data Warehouse will provide us with the technological tools to monitor compliance with federal requirements such as Healthy People 2010.”
Solution
The DoH wanted to improve the process of retrieving healthcare statistics, enable compliance with Healthy People 2010, and support effective decision making in healthcare management. In short, it wanted to improve public health management. One of the steps it took to accomplish that was to start constructing a centralized Data Warehouse for all of the Commonwealth’s healthcare information, with the help of partner Nagnoi.
The DoH considered several database management systems as the foundation for its Data Warehouse, as well as several (extract, transform and load (ETL) tools. The DoH was initially skeptical about using Microsoft® SQL Server™, based on the size that the Data Warehouse will end up growing up to in a few years, and leaned toward using Oracle or Sybase IQ. However, after testing SQL Server 2005, it felt confidant that SQL Server 2005, which is part of Microsoft Windows Server System™ integrated server software, could do the job. “SQL Server 2005 fit the requirements, and was more economical than the alternatives,” said Figueroa.
The DoH originally issued a Request for Proposal (RFP) for an ETL tool to help build the database warehouse, expecting to buy a third-party special-purpose ETL tool like Informatica or Ascential. “We discovered that SQL Server 2005 Integration Services is a real, big-time, enterprise ETL tool,” said Figueroa. “After testing it, we recommended that the DoH choose this option.” This represented a savings of roughly $250,000.
As a development and test environment, the DoH has deployed the SQL Server 2005 Enterprise Edition database on the Microsoft Windows Server™ 2003 Enterprise Edition operating system, in an active-active cluster of two computers and a SAN RAID array. Each computer in the cluster has dual Xeon processors and 4 GB of RAM. In addition, the test environment has one Web server, one ETL server, and one Business Intelligence/Data Warehouse (BI/DW) server; each of these servers has dual Xeon processors and 2 GB of RAM with Windows Server 2003, and the ETL and BI servers have SQL Server 2005 Enterprise Edition.
The production environment has essentially the same configuration, only scaled up and out. The main database cluster has 4-processor computers with 6 GB of RAM; there are two Web servers, each with 4 GB of RAM; and there are two BI servers and one ETL server, each with 4 processors and 4 GB of RAM. The DoH and Nagnoi are currently adding 11 major source systems to the Data Warehouse. Later, there will be a potential to add many more source systems from inside and outside the DoH.
For Phase I of the Data Warehouse project, the users of the system will be the DoH’s epidemiologists; the individual program offices responsible for tracking heart disease, cancer, and other major diseases; the immunization program office; and the Department of Vital Statistics. In Phase II, the Data Warehouse will be expanded for users outside of the Department of Health. The DoH plans to make its statistical information available to the U.S. Centers for Disease Control (CDC) in Atlanta, Georgia; to pharmaceutical companies for clinical trials to promote research and development on the island; and to the island’s medical and the general health community, to promote better public health management.
The DoH is working with sample data now in the test Data Warehouse. When the full system goes online, and expands its source systems to include the island’s hospitals’ data, Figueroa expects the production Data Warehouse to grow to terabytes of information.
Nagnoi and the DoH are currently experimenting with front end reporting systems for the Data Warehouse. They are using SQL Server 2005 Reporting Services, which is a comprehensive solution for creating, managing, and delivering traditional paper-oriented reports and interactive Web-based reports. In addition, they are using SQL Server 2005 Analysis Services cubes for multidimensional analysis, as well as SQL Server 2005 Report Builder, a new and easy-to-use report creation tool. They are also experimenting with Microsoft Office SharePoint® Portal Server 2003 as the user interface to Report Builder and Reporting Services. To track key health indicators, the DoH will be implementing Microsoft Office Business Scorecard Manager 2005.
Benefits
By using SQL Server 2005, the DoH lowered the cost of its Data Warehouse considerably. It had lower licensing costs, and lower hardware costs for Intel Xeon-based servers than if it had used proprietary UNIX servers. In addition, the DoH did not require a separate ETL tool. Dropping the separate ETL tool alone saved roughly $250,000. As the SQL Server 2005 licenses were covered by an existing Enterprise Agreement, there were considerably more savings compared to buying licenses for another database.
The DoH benefited from the increased functionality in SQL Server 2005, such as the SQL Server Integration Services already mentioned and the improvements made to Analysis Services, including seven new algorithms for data mining. It also benefited from increased productivity using the new design environment. Overall, it got more power for less money.
The Data Warehouse facilitates access to multiple, heterogeneous data sources from one single web-based information portal. It also matches the information from multiple data sources—no simple task when there is not a single unique identifier for each citizen. “The fuzzy lookup and fuzzy grouping capability of SQL Server 2005 helps us to remove duplicates and to merge the information from diverse systems and sources,” says Figueroa.
“To gain credibility of the integrity of the Data Warehouse, our goal was to demonstrate that the quality of the data in the Data Warehouse is better than the source,” says Alan Koo of Nagnoi, the project’s Technical Architect. “To meet that goal, the Nagnoi team used powerful [Integration Services] components such as Slowly Changing Dimensions, Fuzzy Lookup, Fuzzy Grouping, Script Component, among others, to ease the implementation of the very complex logic that traditionally had to be done with hundreds of custom coding lines. This complexity has now been reduced mainly to configuration clicks of the components, which has made the development tasks a lot simpler, faster, easier to maintain, and, as a consequence, less costly.”
In order to facilitate health data analysis, the DoH Data Warehouse might have pre-built aggregate data that, for example, categorizes deaths from heart diseases by time, gender, education level, disability status, and family income. It might also analyze the top 10 medicines prescribed by age group, gender, and time; or report on most common diagnostics and procedures. The Data Warehouse centralizes the security of the data to one system, rather than exposing all the source data systems to queries.
According to Figueroa, the Data Warehouse fills the critical need to track public health data in a systematic way. “It will help the Commonwealth to comply with a series of federal requirements for reports and data,” says Figueroa, “and provide a single source for the information needed to design effective public health campaigns, or even to make easily accessible the information to complete federal discretionary grants.” The Data Warehouse facilitates the analysis of critical data for epidemiology, and it could become an important source of statistics for the new Office of Statistics of the Commonwealth of Puerto Rico. In addition to providing analytical capabilities to the registries, such as the Cancer Registry, that will be using the data, the process will also provide feedback to the sources that feed the Data Warehouse in order to improve the quality of the data from their respective databases.
“From the IT standpoint,” said Valentin, “this transforms what is today a very time-consuming process to provide reports and statistics to the many different users throughout the DoH. With the proper permissions and the security established, users will simply log-in into the Data Warehouse and obtain what they need, when they need it, in the format they need it. Our staff will start executing their job in a much more efficient way. Their time will be used to analyze the information rather than spending time acquiring and formatting the data. This is very powerful.
“We will go from a data push culture to become an on-the-fly information consuming organization. This will save a lot of time to our IT staff. Instead of building and programming reports constantly, I can use my staff for more value-added activities,” says Valentin.
“This effort is one of our most important strategic projects, and will have a direct impact on the quality of life of every Puerto Rican,” says Valentin. “We really look forward to start reaping the benefits of managing the health of our population with integrated information available just a few clicks away.”