about us partnering us white papers contact us healthcare discussion & support forums home
reprice Medical Claim Audits Model Managed Care Contracts Medicare Payment Solutions Visium solutions products

The case for adoption of SQL Server 2008 in the Healthcare Industry


PART I


  • Introduction
    		• Healthcare companies today, across a wide range of disciplines, struggle with challenges associated with the ever increasing demands for robust data processing in the face of a constantly changing market and the downward pressure on IT departments to do more with less. This has become more evident as the economy began to contract. IT capital expenditures have been significantly reduced and in some case put on hold. Because of that fact, having the right tools at your disposal is more critical then ever.
  • Process Summary
    		• While this whitepaper focuses on a sub-set of complex business & IT challenges associated with various payment integrity processes in a healthcare organization, solutions discussed within this document can be applied to various industries and disciplines.
    Outside of the requirement for competent and knowledgeable analysts, the entire data related aspect of the audit process can be broken down into five major components:
  • Integration/Implementation
  • Data Load
  • Distribution
  • Processing
  • Reporting & Analysis
  • Orchestration
    This case-study will focus on the operational challenges associated with Integration/Implementation and Data Load processes. It is based on a set of real challenges we faced and the solutions we have adopted to tackle those challenges.
    Process Overview
    		•
  • Legacy Process
    		• In most cases the process of data implementation consisted of these key steps:
  • Data Definition
  • Data Interpretation
  • Data Mapping
  • Data Loading
  • Data Validation

    Process Overview
    		•
  • The problem

    Hardware
  • DELL Poweredge 2600
  • RAID-5
    Software
  • Windows 2003 Server
  • SQL Server 2000
  • Proprietary data-mapping software
    Warehouse Size
  • 1 Terabyte (initial size)
    		• One of the biggest challenges we have faced early on is: how do we map many different file formats and load massive amounts of data quickly and reliably. We have originally adopted processes and software such as Custom Visual Basic Code, ADO and DTS in order to solve above challenges. When the size of the warehouse increased by 600% percent from one terabyte to six terabytes, it became apparent that our existing solution, while sufficient for smaller to medium size data-sets, was not going to be able to meet the rigorous demands placed on them by the reality of everyday business challenges in a large organization. Another challenge we faced early on had to do with the fact that massive data requirements were putting significant strain on our ability to load and process data quickly. With the addition of 140GB’s worth of data every month throwing hardware at the problem did alleviate certain bottlenecks, but the overall problem persisted: the volume of data was too great, and the budget for hardware was limited. We therefore were faced with a problem: how do we deal with the almost exponential rate of growth while keeping hardware expenditures under control.

    When we signed one of the largest health-care organizations in the country as a customer; that is precisely the challenge we were facing. Our IT team was presented with a challenge: we are to operate in an environment where we have to identify claims while competing with other identification vendors. This “First In First Out” approach to claims audits presented a very unique set of challenges for our organization: how do you scale your process to a large national payer in order to stay competitive with other vendors? By trying to scale our warehouse and our processes from one terabyte to six terabytes we quickly realized that key components of our process would have to be changed if we wanted to meet operational and performance targets our customers expected. The volume of data created another problem for our technical staff. Even after upgrading our hardware to a state of the art Fiber SAN system, the 140 GB’s of fresh data every month was creating operational bottlenecks associated with data loading and processing. This forced us to create complex horizontal and vertical data partitioning schemes, such as partitioned views, which were time consuming to maintain and had a negative impact on the overall performance of the warehouse. Due to the fact that we were operating in a competitive environment, these operational challenges had direct impact on the overall production downstream and could be boiled down to the following key areas:
  • Acquisitive nature of our customer presented us with a growing list of inherited claims systems with very different sets processes and data formats.
  • A typical implementation for new datasets would take 90 to 180 days due to complexity of development and testing of custom mapping algorithms.
  • Changes to existing dataset could take up-to several weeks to develop and test due to the complexity of the mapping algorithms as well as complexity of warehouse partitioning schemes.
  • It could take us up-to four days to load new monthly files due to the sheer size of the data.
  • The Solution

    Hardware
  • 2 DELL Poweredge 2800
  • Hardware Load Balanced Failover Cluster
  • LSI Fiber RAID System
    Software
  • Windows 2003 Server
  • SQL Server 2008
  • SSIS
    Warehouse Size
  • 6 Terabytes
    		• By implementing Microsoft SQL Server 2008 and SQL Server Integration Services (SSIS) we were able to significantly cut down the time it took us to implement and test data mapping, validation and import.
  • The graphical nature and robust data mapping features of SSIS enabled our team to quickly define source, destination and transformational logic using a click of a mouse.
  • SSIS enabled our team to streamline data mapping and validation of external datasets.
  • Because SSIS designer integrates with Visual Studio, we had the ability to seamlessly integrate data mapping packages into our overall solution, while maintaining full version control of all changes.

    Upgrading our warehouse to Microsoft SQL Server 2008 also allowed us to take advantage of several enterprise features that combined with the newly added capabilities of SSIS cut down our overall data load times by 800%.
  • Newly added CLR features of Sql Database engine and SSIS transformation engine reduced the overall transformation duration by 350% while significantly reducing the CPU utilization
  • Native 64bit support allowed us to easily add more RAM to our servers and greatly improve the overall performance of the system.
  • New Data partitioning functionality enabled us to consolidate all of our data into large partitioned tables and thus eliminating the complex logical partitioning schemes. By doing so, we significantly improved our data-mining capabilities but allowing us to store all related data in a single physical location.
  • Physical partitioning of data and indexes enabled us to cut data loading by 600%.
  • Conclusion
    		• By implementing SQL Server 2008 we were able to make significant productivity enhancements in two key operational areas: data implementation and data loading and maintenance. Scalability and reliability of SQL Server enabled us to meet data management challenges of any organization. All these benefits resulted in a significant reduction in the time it took our information technology team to deliver data to our analysts resulting in a nearly 30% boost to our overall production.
  • About The Author
    		• Since 1981 Chart-Tech has provided cost containment and revenue enhancement solutions to healthcare. Hundreds of clients across the U.S. have benefited from the expertise and technology the company provides.
    www.chart-tech.com
    847-864-1432
       
    hipaa statement hipaa statement privacy policy legal notice