SSM Taps Into PDW for Big Performance and Productivity Gains
SSM Health Care is a Catholic, not-for-profit health system that operates in four Mid-western states. Its September 2013 merger with Wisconsin-based Dean Health Systems transformed SSM from a hospital-based system into a large integrated delivery system with networks in each of its regions. In Wisconsin, Illinois, Oklahoma and Missouri, SSM manages more than 150 outpatient sites, a pharmacy benefit company, an insurance plan, 18 hospitals, two nursing homes, home care, hospice, and an accountable care organization. SSM was experiencing poor data warehouse performance that was resulting in important analytical reports taking way too long to complete or not even completing at all. SSM also needed to fix several data consistency issues they were experiencing that were impacting the quality of output. SSM source data was coming from their EPIC EMR/EHR system and their ETL/SSIS process was tied to SQL 2008. SSM reached out to Scalability Experts to design and implement a higher performing and more scalable data warehouse environment.
SSM needed to upgrade to the latest SQL Server 2012 platform and take advantage of the speed and performance of a Parallel Data Warehouse solution. SSM also needed to migrate off the existing Data Warehouse to the new PDW while keeping the existing BI projects on schedule in order to eliminate reworks. Additional enhancements and functionality were included in the implementation work covering restart-ability, package dependency, batch and log processing, report performance increases, duplicate data elimination and redesigning of the data dimension structure to ensure consistent and accurate data attributes.
The solution designed for SSM by Scalability Experts (SE) involved implementing a Microsoft Parallel Data Warehouse (PDW) and migrating multiple Extract, Transform, Load (ETL) data processes to load the new appliance. SE monitored, evaluated and documented the ETL/ELT process to define the best process to use with multiple sources such as an EPIC EMR/EHR system. SE accuracy of testing of the system for speed and performance using SSM’s existing source data was project critical. SE worked closely with the SSM team to create a road map and project plan that comprehended existing migration activities as well as future healthcare BI project needs. SE extracted 81 tables from Clarity, migrated 14 dimensions and created 7 additional dimensions for a total of 21 SQL Server Integration Services (SSIS) packages. Working together, 6 fact table SSIS packages were migrated and 3 more created. SSM required a restart-ability in their ETL packages to recover from an ETL package failure. SE designed this restart-ability into the ETL process through implementing batch process/tracking method. SE implemented a production ready batch/process flow to accommodate Multi-Threaded ETL across multiple Fact Tables. SE analyzed all SSM reporting and ETL processes to optimize the PDW geometry. PDW Database design is very dependent on user queries and how SSM would be reporting on the data. SE analysis was required to identify the correct geometry for the PDW tables and designing of the reporting layer. An incorrect geometry will directly impact the performance of the solution. As SE analyzed SSM's reporting needs, SE had to understand how the data needed to be structured for PDW in order to meet SSM SLA’s for reporting and ETL. SE therefore implemented SSM’s SSIS reporting through SQL Server Reporting Services (SSRS). Finally SE implemented maintenance tasks to update statistics, backups and nightly processing.
After SE completed the implementation and optimization of the PDW, SSM experienced significant performance increases. The ETL process before the PDW took 77 minutes and after the PDW this time was reduced to 16 minutes. Prior to the PDW the Data Mart builds were taking 39 minutes and after the PDW this time was reduced to 9 minutes. After SE redesigned the PDW ETL process the performance improvement was 7 times faster. The overall Data Warehouse build improved from 116 minutes to only 16 minutes. Post PDW implementation, SSM has received very positive feedback and comments from their BI Power Users as well from c-level executives. Results have been so positive in performance, productivity gains and capabilities that SSM has purchased a second PDW. In the future SSM plans to take advantage of Big Data and Predictive Analytics capabilities to drive even greater value for the organization.