SQL Server's BI features net Ministry of Health deal

The National Costing Collection and Pricing Programme (NCCPP) was established by the Ministry of Health and the 20 District Health Boards (DHBs) to produce an annual national price book for hospital services. It is also entrusted with making event-level costing information available for benchmarking, productivity, planning and funding purposes.

Over the past several years, the Ministry has been challenged to provide more accurate and accessible data through the NCCPP, so DHB employees can perform more detailed analysis and make better pricing recommendations.

“We had a lot of hospital event-level cost and activity data that we’d collected and distributed over a six year period, but there was no way to make it easily accessible to analysts,” says Stuart Powell, Manager of Analysis, New Zealand Ministry of Health.

“There were different data analysis solutions and data management processes at each DHB, so we weren’t able to provide consistent costing information.”

The DHBs wanted comparative data whenever they needed it so they could see trends in hospital costs over time linked to demographic and hospital output data.

“Typically we would send large files containing this data to the DHBs and they had to wait months for it to become available, with analysts spending a lot of time extracting and formatting data. The process wasn’t fast enough, and it was difficult to maintain one nationally consistent version of the data.”

“Ultimately the success of the NCCPP is driven by meeting its customers’ needs. We weren’t giving them the tools they needed to improve their cost analysis.”

To meet these needs, the Ministry investigated a new data analysis solution.

The solutionThe Ministry worked with Dynaface, an IT firm specialising in business intelligence (BI) solutions for healthcare customers, to develop and implement a solution based on Microsoft SQL Server 2008 Enterprise data management software.

"SQL Server is a modern data platform that helps address the challenges of increasing data volumes by rapidly turning data into actionable business insights, allowing organisations to see how their businesses are operating and quickly build reporting solutions," says Bradley Borrows, Business Group Lead, Server and Tools for Microsoft New Zealand.

The solution takes detailed costing data from the NCCP and loads it into four online analytical processing (OLAP) cubes in SQL Server Analysis Services. The cubes are accessible to analysts at each DHB via a secure internet connection. The analysts can access the cubes to view updated healthcare costing information and create their own reports using Excel, using PivotTables connected to the cubes to manipulate data for reporting. When users build reports, they can incorporate analytical Multidimensional Expressions (MDX) calculations, which provide more detailed cost analysis.

“Most analysts at the DHBs were already heavy Excel users, so this solution builds on their existing analytical skills,” explains Powell.

Currently, more than 130 DHB employees use the solution to compare hospital costs in their region with national and peer group costs. Users can then look at cost differentials and drill down to find the areas where they are different, then use the attached activity, output or demographic data to determine if the differences are justified.

BenefitsDHBs now have access to comparative data and better analytical capabilities, ultimately helping them make more informed business decisions. The analysts have a deeper understanding of the data they’re viewing and can easily years' of hospital cost data.

“With SQL Server analysts can see up-to-date cost data matched to hospital output data. As a result they can make better recommendations about price changes, giving the NCCPP programme and its outputs more credibility,” says Powell.

The NCCPP can now provide an innovative and intuitive BI solution to its customers.

“There are other cube-based solutions in the industry, but this one offers a powerful, rich toolset that capitalises on familiar tools like Excel. A solution that can give users detailed MDX calculations from a few clicks within Excel is very innovative.”

Now analysts can see costing data for the previous year as soon as it’s loaded into the cube.

“Analysts have usable data immediately, and the more it is available, used and analysed, the more its quality will be improved.”

Going forward, the Ministry of Health and Dynaface plan to enhance the solution with new BI features found in SQL Server 2012.

“SQL Server 2012 Master Data Services and Data Quality Services could help provide assurance around the data in the NCCPP, which is a key facet of maintaining trust in the programme’s analysis,” says Powell.

The Ministry of Health has implemented a solution to provide users with in-depth analytical capabilities and faster data delivery through easily accessible analytical cubes, enabling smarter decision-making.

To find out more about SQL Server 2012 click here.