Coming on Strong: BI software meets the public sector

Another technology shift is taking hold that is already changing the way organizations — including governments — do business. New software designed with analytical capabilities — known as business intelligence, or BI is gaining influence worldwide.

The U.S. Department of Agriculture, to cite one telling example, employs nearly 8,000 inspection staff who monitor about 6,500 meat, poultry and egg processing plants across the United States. Together they file millions of inspection records annually. ProClarity Corp., an Idaho-based business intelligence solutions provider, installed a BI system for the USDA two years ago. It generates about 30 reports against 30 million inspection observations in just 25 seconds. Inspection findings and other data now are reported in a matter of seconds rather than days or weeks. Improved access to data means greater efficiency, better planning and the implementation of enterprise-wide best practices. Trends, potential problems and correlations are spotted quickly, enabling the agency to make more efficient use of time and resources.

BI is probably the most competitive and fastest growing segment of the software industry. High tech research specialist IDC pegs it as a $7-billion market, with sales expected to double by 2006.

“I think a lot of people perceive business intelligence initiatives about accountability vis-a-vis Enron scandals and to make sure we are tracking things,” says Bob Lokken, CEO and President of ProClarity. “That’s typically not what BI is for. It’s what BI can be used for.

“Overwhelmingly, most organizations use BI for productivity and execution. Most organizations have transaction processes that collect mountains of data, and what people find is the data their systems are collecting have a tremendous amount of insight about how to do things better and more effectively to add more value to the customers. It is of no value if you can’t get it out.” BI is generally considered the next logical step beyond Enterprise Resource Planning (ERP), which captures data transactions. BI organizes the data and puts it to work with a range of tools specific to individual organizations. BI is also increasingly going on-line with tools to mine data for insights that will improve customer relations, productivity and services.

Terence Atkinson, who heads public sector relations for Cognos, Canada’s leading provider of BI solutions, cites one public sector client that monitors disease control statistics. Influenza figures are tracked and stored in a database so trends can be analyzed to help figure out how many flu shots can be ordered for next year.

In the federal government, the Public Service Commission uses a BI software application provided by SAS, regarded as the largest privately held software company. The PSC analyzes the data and produces applicant reports and personnel data involving 170,000 positions over 27 divisions across the country, with information on appointments, employee population and separation.

“What they use SAS for, in addition to putting the data together,” says Gary Love, public sector program manager for SAS in Canada, “is putting out annual snapshots, standardized data, trend analysis presented in news and reports to show how the divisions are being managed.” Public sector needs are very specific, Love adds. The corporate world needs only to produce an annual report or quarterly reports to show that earnings are on target. Government departments in Canada, the U.S. and the U.K. are mandated to manage the metrics of their businesses; “What’s useful for government (is) if one of those metrics shows it is not moving in the right direction, then very early on with good BI, you can identify precisely where the problem is and quickly rectify it.” Is BI worth all the excitement? Scott Murray, CTO for the Canadian Institute of Health Information, says that, as a not-for-profit agency, the CIHI can’t use the bottom line to justify the expenditure on BI.

“When we were created, all of our products were paper-based,” Murray says. “Over the past several years, we’ve been moving towards e-based offerings for the collection and dissemination of information. Generating this BI environment has been the next step, and it’s a big step in terms of the value we can offer our external stakeholders.

“So instead of static information, we give them an environment where they can ask their own questions, generate their own results in a secure way. It’s hugely powerful and there is a lot of excitement in the community about this. So are we delivering value? Definitely.” BI is not only for internal use. “BI is also what gets pushed out onto the Web,” says Atkinson of Cognos. “When you talk about e-government, government is not only providing services. E-government can also be government-to-government by sharing information with other levels and other governments.”

Steve O’Keefe at IBM Canada provides a primer on the three key elements in a complete business intelligence application:

– The core is the data warehouse, where data is stored in an environment where it can be used. Scalability, or the ability of a system to grow, is critical to accommodate increasing loads of data.

– ETL tools provide the means of getting at the database. ETL are the extract, transfer and load tools, which take information from disparate files, extracting, transferring and cleansing them.

– Front-end tools allow users to access the data, then manage it using analytics and activity-based management.

Cost varies according to the size of an organization and the number of users. From complex customized applications to off-the-shelf products, the market provides virtually everything. In late June, for example, Microsoft announced a series of add-ons to its Excel Office group called the Microsoft Office Business Score Card Excelerator, with software components that allow for free-form financial analysis and reporting with pivot tables.

Microsoft is knee deep in R&D related to BI, which is a key component of the SQL Server platform. “It’s becoming very core about all the key things that we’re doing with our products,” says Darren Massell at Microsoft Canada. When it comes to cost and return on investment, Murray at CIHI has a warning. CIHI knew before 2000, when it was moving to a BI solution, how urgently health care would grow as a leading issue. But now, working with more than two terabytes of health care data — which is expected to more than double before the end of the year — and a new external facing system for generating queries, CIHI could never have guessed how dramatically its needs would have changed.

“What you need to do is make sure those underlying investments are the right ones. Invest in a tool that is in it for the long haul. Initial decisions were made before I got here, but they chose something that was bullet-proof…In this case, it certainly has stood us in good stead.”

Marlene Orton ( is an ottawa based freelance journalist.

SIDEBAR – CIHI: BI in action

Business Intelligence provides a raison d’etre for the massive stores of data collected by agencies and departments. The Canadian Institute for Health Information, whose very existence relies on the collection and collating of information, illustrates how BI works.

The 10-year-old public not-for-profit organization is funded by government at the federal and provincial level. Other stakeholders include segments of the medical community and health care institutions. CIHI, which is growing at roughly 20 per cent annually, has a staff of about 100 IT personnel within the payroll of about 400 people. Their job is to gather data related to health care and give it some sense before disseminating it to the outside community.

CIHI’s information is drawn on by everyone from pediatricians to politicians. Since its inception, CIHI has acquired two large legacy organizations: the Health and Medical Records Institute (HMRI) and the Canadian MIS Database, which kept financial data for care giving institutions such as hospitals. Other databases have migrated from Statistics Canada, along with staff.

“If data mining or business intelligence has relevance anywhere, you would expect it to have relevance for an organization like ourselves,” says Scott Murray, CIHI’s CTO. “We’re formed from some legacy organizations that were disparate and had their own data holdings. By definition we inherited those data holdings, which evolved and had lives of their own over a number of years.”

CIHI operates with a 10-member BI team led by a director. All that data was pasted together creating a series of disparate databases. To derive value from all the information, CIHI chose an Oracle solution to help with several major issues. First, the data was “cleansed” of duplication and transformed from assorted legacy systems into a single cohesive system. New ‘”dimensions” were created to allow for the real work of Business Intelligence – the slicing and dicing, analysing and setting up of what-if scenarios.

“For us, it’s been a huge leap forward in terms of being able to make good use of the information in a cohesive and consistent environment,” says Murray. “Everyone is living with legacy. The nirvana of the totally, wholly integrated and consistent database doesn’t exist. You have to bring it together somewhere to make intelligent use of this data. What you are doing is creating a new environment, specifically oriented and tuned for use and analysis. You structure the data differently.”

Murray’s team describes commonly used elements as “dimensions,” which represent factors common to trends or figures. This might include, for example, the number of therapeutic abortions on a geographic dimension or population dimension, perhaps based on age. The data, which is pasted together, is structured by tools that are meant to give the data some value.

One example under CIHI’s wing is the Canadian Joint Replacement Registry, which collects the specifics on hip and knee joints. This specialized functional data service goes to work every time an orthopedic surgeon performs an operation, says Murray. The “make and model” of the joint is noted and tracked. “This allows us to measure the quality of the devices we’re putting into people. If there is a problem, we know what make and model was installed. You can start to track if certain devices aren’t up to snuff. The registry is only a few years old. We don’t have the longevity of information yet but that’s where we’re headed.”

Beyond CIHI, another example of BI at work is Alberta’s Wait List Registry (, which uses a Cognos solution.Choose a category from a list that includes, say, eye surgery and MRI scans, fill in the field form for the name of the hospital and up pops the number of patients on the waiting list and how many weeks most patients have waited to receive treatment.

At CIHI, however, the BI team includes a manager and a hybrid team of technical and business experts. “You have to have sound understanding of the business aspect in an analytical context,” says Murray. “These are (people) who can say: ‘You know, I really think people would like to look at the data this way or that way,’ and will help the (data) offerings and provide the standard interfaces.”

CIHI’s data marts amounted to a total of three terabytes at mid-July 2004, expected to grow to five terabytes by the end of the year. The databases, collected for use by the medical, government, political and other communities, include among many others:

• Adverse Events in Canadian Hospitals

• Canadian MIS Database

• Canadian Organ Replacement Register

• Drug Utilization Project

• Hospital Mental Health Database

• Homecare Indicators Project

• International Classification of Functioning, Disability and Health (ICF)

• Licensed Practical Nurses Database

• Mental Health Indicators Project

• Hospital Morbidity Database

• National Health Expenditures Database

• National Physician Database

• National Trauma Registry

• Ontario Trauma Registry

• Registered Nurses Database

• Therapeutic Abortions Database

The BI solution was built atop technology decisions made before Murray become CTO about four years ago. Since CIHI had invested in Oracle with an IBM platform, CIHI continued the relationship. Cost to implement the BI side has been $2 million over a four-year period to the beginning of the current fiscal year. With the new portal getting under way, the figure is expected to hit about $4 million by the end of the fiscal year.

“And that is personnel costs only,” says Murray. “That excludes the technology solutions we have brought into this equation. You have to make the right initial investment so you are not sizing a tool or a solution based on today’s reality if you know you are going to grow. We knew we were going to start with a couple of data marts and expand. We knew that from the get-go.”

For the moment, however, CIHI is pondering its options before deciding where to go next. Some proposals include adding GIS components to the queries, which would allow users to zoom in on specific geographic areas.

“So we’re assessing whether the tools we have this far are the ones to take us through the long haul,” Murray says. “I don’t have an answer yet. Certainly the database is fine. It’s robust. All those structures are sound. It’s more that layer on top of it that we could provide to the end users that we’re taking a hard look at.”

— Orton

Related Download
3 reasons why Hyperconverged is the cost-efficient, simplified infrastructure for the modern data center Sponsor: Lenovo
3 reasons why Hyperconverged is the cost-efficient, simplified infrastructure for the modern data center
Find out how Hyperconverged systems can help you meet the challenges of the modern IT department. Click here to find out more.
Register Now