BI makes the case

Analysis software helps South Carolina program advocates prove their points<@VM>Sidebar | Cubes render data in many dimensions

Two years ago, when South Carolina Lt. Gov. Andre Bauer sought state funds to supplement federal dollars for the South Carolina Office of Aging's meal program, he knew too many of the state's senior citizens were hungry and unhealthy.

However, to persuade legislators to vote for those funds, he needed proof ' or at least persuasive correlations ' between meals and improved health for seniors.

Bauer, who heads the Office of Aging, turned to the South Carolina Office of Research and Statistics (ORS), part of the state's Budget and Control Board.

He wanted the office to show that meals for seniors, paid for with federal funds issued to the state through the Older Americans Act, correlated with better health.

Using NovaView business intelligence (BI) software from Panorama as the front end to a Microsoft SQL Server database, ORS was able to establish that correlation. In June 2006, South Carolina's state legislature appropriated $2.9 million in state funds for the meal program for the calendar year 2007.

That's one example of relationships ORS is now finding through its BI tools, incorporating identity-protected data from about 20 state agencies, Medicare, Medicaid, hospitals and other organizations.

'We can tie funding to programs, agencies, the private sector and nonprofits,' said Pete Bailey, chief of the 45-person health and demographics group at ORS. 'We can monitor [funding], evaluate the outcomes and tie them back to continued funding. If a program isn't successful, the legislature can see that, see where the problem is and fix it.'

ORS' goal is to use its BI tools to help state agencies and organizations fulfill their missions.

Although it took several years of negotiating with the entities that own the data to get it and then more time to build the databases, ORS is starting to show what it can do with the decision-support tools.

Ultimately, the BI capabilities could add accountability to the political process by measuring the results of policy decisions.

'Business intelligence has been widely used in private-sector industries for years,' said Nigel Pendse, an independent BI analyst, author of the annual Business Intelligence Survey and lead analyst for the Web-only OLAP Report on online analytical processing at www.olapreport.com.

'It's for fast-moving industries to track products and competition, profit and customer profitability,' he said. 'Governments are less likely to use these types of products.'

From the ground up

BI software includes the underlying database software or server and the front end, which 'queries the [database] and represents the results of the queries in different formats,' Pendse said. Microsoft, SAP, IBM and Oracle are the leaders in the BI field, but 'the big [companies] do a bad job,' he said. 'Most of the surviving small companies do a good job.'

Large companies' BI products are combinations of different products, Pendse said.

'Panorama is better than the others because it has built-from-the-ground-up technology,' he said. 'It doesn't have the integration challenge.

It hasn't been stitched together from different sources.'

NovaView, with its dashboard, reporting and analytical capabilities, was selected by ORS because, at the time, it was the only BI tool that could exploit the full power of the online analytical processing and data-mining capabilities of SQL Server. Panorama developed the SQL Server software and sold it to Microsoft in 1996.

'After selling the old technology to Microsoft, Panorama built the front end,' Pendse said. 'All was happy for 10 years.'

When ORS chose SQL Server, NovaView was the de facto front-end BI module to extract information from the database.

In the past two years, Microsoft acquired ProClarity, another BI front end, which it is incorporating into SQL Server. 'Now Microsoft is less likely to feed business to Panorama,' Pendse said. Consequently, the company is broadening its base of business.

Some experts might not necessarily need a tool like NovaView to query a database and understand the results. But for employees at ORS, the tool makes accessing and using databases much easier.

Five or six years ago, before the office started using BI tools, 'statisticians had to manually aggregate all the data,' Bailey said. 'Now you just press a button. You can do an analysis in a day, whereas before it would have taken months.'

Building cubes

Since acquiring its BI tools, ORS has built multiple databases, also known as data cubes. Each cube represents data drawn from local, state and federal sources.

The so-called seniors' cube was the source of the correlation between the seniors' meals program and their improved health. The metric for improved health was a reduction in the number of emergency room visits and hospitalizations of seniors who received meals. These correlations, plus ORS' ability to measure state spending outcomes, were instrumental in obtaining state funds for the meals program.

In 2007, those funds paid for 5,476 meals and other home- and community-based services for seniors. The resulting data is just now starting to trickle in to ORS for analysis.

'Working with the seniors' cube, we want to show [that] an investment of less than $1,000 a year in home- and community-based services helps an individual maintain independence and dignity in his or her home ' preventing or delaying the expense of a $40,000 a year nursing home bed or a $24,000 average Medicare inpatient hospital discharge,' Bauer said in a statement.

CommuniCare, a nonprofit based in Columbia, S.C., also has benefited from ORS' BI capabilities. CommuniCare's mission is to provide prescription medications for the state's uninsured workers. It operates solely from donated funds.

Last year, the nonprofit spent $34 million to distribute 142,000 prescriptions to more than 14,000 clients. Chief Executive Officer Ken Trogdon said he assumed the organization was helping its clients, but he turned to ORS for some hard numbers.

CommuniCare provided ORS with identity-protected data covering the time from a client's enrollment with the organization until 18 months afterward. ORS combined this with hospitalization and emergency room utilization metrics extending from a year before an individual signed up for the program until 18 months afterward.

ORS' analysis showed that diabetics who received medications from CommuniCare reduced their visits to emergency rooms by 27 percent and in-patient hospitalizations by 15 percent during the 30-month period.

Clients with hypertension who received the necessary medications reduced emergency room use by 33 percent and inpatient hospitalizations by 20 percent.

Individuals who received prescription psychotropic drugs from the nonprofit reduced emergency room visits by 24 percent and hospitalizations by 31 percent.

'Before, we knew we were doing something good, but it was just warm and fuzzy,' Trogdon said. 'When you can quantify the numbers, it justifies our existence and shows the impact we're having. If we can show what we're doing in a state like ours that suffers greatly from poverty, then there is a good chance the program can grow nationwide.'

The latest addition to ORS' analysis capabilities is a geographic information system that allows results to be mapped according to county, ZIP code and congressional district. 'With the mapping system, we can show these issues to every House member, senator and congressman and the people they represent,' Bailey said.

'When a politician wants to be re-elected, he can show how things have changed while [he was] in office,' he said. 'If there haven't been sufficient improvements, he and agencies can work together. Politicians haven't previously had specifics of these problems for the people they represent.'

Bailey didn't say it, but the ability to measure outcomes also means that politicians can be held accountable for their actions, and political programs and initiatives can be objectively evaluated.
THE CONCEPT OF a cube of data is central to business intelligence software, but these cubes aren't the common 3-D blocks that might come to mind. Instead, a BI cube can have multiple dimensions in which each is an element of a data category.

Data elements might be durations in minutes, hours or months. Or they might be the number of visits a person makes to a hospital.

'Cubes typically have between five and 10 dimensions,' said Nigel Pendse, an independent BI analyst, author of the annual Business Intelligence Survey and lead analyst for the Web-only OLAP Report.

'The software can do more, but people have trouble understanding it,' he said.

Data dimensions can be linked or cross-tabbed to find correlations or relationships, just as a Microsoft Excel user finds a cell that links one unique row and one unique column. In fact, Excel is the most commonly used BI software tool.

Because of the sheer quantity of data it manages from local, state and federal sources, the South Carolina Office of Research and Statistics, part of the state's Budget and Control Board, uses Microsoft SQL Server to create its 20 data cubes, and more are being developed.

The office uses Panorama's NovaView to query the data cubes for information that will help client agencies do their jobs better.

'The beauty of the software is that you can define the relational database and how the data are related, define the data, and the computer does all the work for you,' said Pete Bailey, chief of health and demographics at ORS.

Reader Comments

Please post your comments here. Comments are moderated, so they may not appear immediately after submitting. We will not post comments that we consider abusive or off-topic.

Please type the letters/numbers you see above