Automating Healthcare
Solving business problems with savvy automation

Building a Data Portal

Business problem
We have a data warehouse stuffed with transaction data from our systems. Now we need to transform the data into actionable information — beginning with reports.

  • What are the right tools for developing reports?
  • What is the best way to deliver reports?
  • How can we ensure that we don't answer the same question more than once?

The right tools
We very quickly graduated from Microsoft Access
, which is designed for working with smaller datasets, to Crystal Reports (now Business Objects). Crystal Developer could create either:

  • local reports which were manually e-mailed or printed or placed in a shared folder; or,
  • report definitions which were then loaded into Crystal Enterprise on a server and scheduled for automatic output into a specific folder on a regular basis.

This worked well for a while, and Crystal Developer is still used as one of the tools for developing reports. However, we soon became frustrated with three significant limitations:

  • When loading a report definition into the Enterprise server for scheduled production, the complete profile has to be recreated whenever the report definition is modified. This is a real nuisance, because reports are often tweaked repeatedly.
  • User-selected parameters are not supported (e.g., there is no way for the user to select from a list of providers or sites and see the relevant report).
  • There are two views in Crystal Developer: design view and report view. Certain types of edits to graphs can only be made in report view, but those edits are lost when the report definition is loaded into the Enterprise server.

In fairness, the current version of Crystal/Business Objects may address these issues. We have not upgraded to the latest version because our last upgrade was so painful that we decided not do another unless absolutely necessary.

Our next tool was SQL Server Reporting Services, which is bundled with Microsoft SQL Server. We needed another developer's tool (Visual Studio), but found that it was worth the expense.

Reporting Services eliminated the first two issues with Crystal. The third issue was partially solved by a charting program from Dundas, which integrates with Reporting Services for more sophisticated graphing. Unfortunately, we have not been able to get the Dundas product to work properly from a client computer, so graphing is still a problem without a full solution. We will be very interested to see the Reporting Services tool in SQL Server 2008, since Microsoft has integrated Dundas into the new version of RS.

Delivering reports
To make it as easy as possible to find reports, we put all reports in one place on the intranet and called it the Data Portal, thinking a cute name would make it easier for people to remember where to look for their reports. As it turned out, the Data Portal name was a mistake (see Redesigning the Intranet).

Out of sight, out of mind — this certainly holds true for reports. We wanted to remind managers and clinicians whenever reports are refreshed. We use e-mail alerts, with a some refinements:

  • To avoid clogging the e-mail server with thousands of attached files every month, we send an alert with only a link to the actual report.
  • We allow users to opt in or out from being alerted about any report:

The Data Portal continues to grow every day — at last count, there were over 57,000 reports. In addition, there are about 40 "parameterized" reports using Reporting Services, where the user may select from a list of criteria and see the resulting report. If these were all produced separately, they would represent several thousand more reports.

All reports are refreshed by automated processes according to predetermined schedules (Automated Reporting).

Answering the question once
In a larger organization, it is not unusual to discover that more than one person is working on the same report request, or that someone else asked the same question a year ago and the report already exists. Part of the challenge is that
various departments may have analysts, and different parts of IT may have analysts focused on different systems with overlapping data.

To help avoid duplication of effort, we took a few steps:

  • Most analysts work in either IT or quality management. Through a special arrangement between the departments, a single manager in IT supervises both groups of analysts. The analysts all sit together, which facilitates collaboration.
  • Report requests go to a special e-mail address, which is monitored by all analysts. If the request duplicates an earlier effort, one of the analysts will probably recognize this and let everyone know. If an analyst has available time, s/he will volunteer to take the request.
  • For those requests that aren't grabbed as they arrive, assignments are made at a weekly triage meeting.
  • All analysts from across the organization are invited to a weekly analyst meeting. Each meeting has an educational focus, often with someone from another department reviewing a business process which may generate data and need reports.

Next steps

  • New tools — We believe that SQL Server Analysis Services (another product bundled with SQL Server) will allow us to design better scorecards and dashboards. We are also exploring an open source Business Intelligence package to see whether it might be worth a try.
  • Better scorecards/dashboards — We need to find a way to build scorecards and dashboards with better functionality. The ones we have been able to build with current tools are not as user-friendly or as functional as we would like.
  • More analysis — We need to do less reporting and more analysis. Resource constraints have limited us to simply fulfilling report requests, but we must go beyond that, to real analysis of data and more creative ways of displaying information.
  • User-designed querying — We could free up some analyst time currently spent producing routine reports if we could offer a web-based querying tool for advanced users. This is the type of tool offered by business intelligence software, but we have not yet found an affordable solution.

Lessons learned

  • A single reporting tool will probably not meet all needs. Even as we add new tools, we probably will continue to use current tools.
  • Centralized coordination of analytic work is a good thing. It helps eliminate duplication of work, and cross-training in tools and data sources helps keep analysts engaged and happy (and makes it less disastrous when someone does leave).
  • The more control offered to users, the better, whether it is opting in/out of alerts or the ability to create their own queries.

Posted 27 March 2008


Custom Applications
ADT Event Alerts
Clinical Operations

Integrated Clerkship

On-call Schedules
People Profiles
Chronic Disease

Security Badge Requests
Charge Capture
Mental Health Treatment
      Plan Tracking

Earned Time Calculator

Supervisory Tree
E-mail Distribution Lists
User Access Requests
HR Requests
Employee Health &

Interpreter Dispatching
Generic Patient Registry
Conference Room

Tuition Reimbursement
Equipment Rental
Code Cart Tracking
Nursing Audits

Show me the data
Growing a Data

Building a Data Portal
Reporting on Full Auto

Intranet Design
Driving With Databases
Speeding with Static

Transparent Security
      and Permissions

Redesigning the

Who works here?
Organizational buckets
System access: Who
      has what?

System access: Use
      it or lose it

Integrating Security

Integrating Provider

Creating A Supervisory

Data Quality Dashboard


RSS Feed