The New Zealand Ministry of Health oversees funding for most of the country’s publicly funded health services. The agency wanted to have accurate and timely hospital cost data available to assist district health boards and itself with key activities
such as pricing and service planning. It implemented a new Microsoft solution that provides in-depth analytical capabilities so customers can make smarter decisions. The innovative solution also delivers data faster through easily accessible analytical cubes.
The New Zealand Ministry of Health oversees funding for 20 district health boards, which in turn provide or contract for hospital, primary, community and other health services to New Zealand citizens. The Ministry of Health and district health boards have
established the National Costing Collection and Pricing Programme (NCCPP) to produce an annual national price book for hospital services. The NCCPP is also entrusted with making event-level costing information available for benchmarking, productivity, planning,
and funding purposes.
||With SQL Server 2008, analysts can see up-to-date hospital cost and output data. As a result, they can make better recommendations about price changes.
| Stuart Powell
Manager of Analysis
New Zealand Ministry of Health
Over the past several years, the ministry has been challenged to provide more accurate and accessible data through the NCCPP, so district health board employees could 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 six years, 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 in place at the boards, and an ad hoc data management approach was being used. We weren’t able to provide consistent costing information.”
The district health boards wanted to be able to get 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 boards,” says Powell. “They generally had to wait months for data 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.”
As a result, the NCCPP could not confidently provide hospital cost data in a timely fashion. “Ultimately the success of the NCCPP is driven by meeting its customers’ needs,” says Powell. “We weren’t giving them the tools they needed to improve their cost
analysis.” To meet these needs, the ministry began looking for a new data analysis solution in late 2010.
The New Zealand Ministry of Health decided to work with Wellington-based Dynaface, a technology firm specializing in business intelligence (BI) solutions for healthcare customers. Dynaface developed and implemented a solution that takes advantage of the
data analysis capabilities of Microsoft SQL Server 2008 Enterprise data management software. “We were already using Microsoft technologies every day,” says Powell. “We were pleased with those products, and we were excited about combining them with new Microsoft
The new solution takes detailed costing data from the NCCP and loads it into four online analytical processing (OLAP) cubes in SQL Server 2008 Analysis Services. The cubes are accessible to analysts at each district health board via a secure HTTP connection
using Internet Information Services 7.0. The analysts can access the cubes to view updated healthcare costing information and create their own reports using Microsoft Office Excel 2003 spreadsheet software. The organization plans to deploy Microsoft Office
2010 in the near future.
Analysts can use Excel 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 health boards were already heavy Excel users, so this solution builds on their existing analytical skills,” explains Powell.
Currently, more than 130 district health board 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, and
then use the attached activity, output, or demographic data to determine if the differences are justified.
The SQL Server 2008 solution gives district health boards better analytical capabilities, which ultimately helps them make more informed business decisions. The solution also provides analysts with comparative data faster than before.
Helps Health Boards Make Better Pricing Recommendations
With the new solution, district health board analysts involved with the NCCPP have a deeper understanding of the data they’re viewing and can easily analyze six years of hospital cost data. “With SQL Server 2008, 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 program and its outputs more credibility,” says Powell.
Provides Innovative Analysis Capabilities
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 capitalizes on familiar tools like Microsoft Office Excel,” says
Powell. “When you’re talking about a solution that gives users detailed MDX calculations with a few clicks within Excel, that’s very innovative.”
Speeds Delivery of Critical Data
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 analyzed, the more its quality will be improved,” Powell says.
Going forward, the New Zealand Ministry of Health and Dynaface plan to enhance the solution with new BI features 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 program’s analysis,” says Dan Kilpatrick, Product Architect and Founder, Dynaface. “Also, combining costing data with the spatial data management capabilities in SQL Server 2012 could help us integrate separate
analytical processes and continue improving the usefulness of the data cubes.”
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.