Building a Data Portal
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.
are the right tools for developing reports?
is the best way to deliver reports?
can we ensure that we don't answer the same question more than
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,
which were then loaded into Crystal Enterprise on a server and
scheduled for automatic output into a specific folder on a regular
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:
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.
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).
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.
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
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.
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.
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).
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:
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.
allow users to opt in or out from
being alerted about any report:
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 are refreshed by automated processes according to predetermined
schedules (Automated Reporting).
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
help avoid duplication of effort, we took a few steps:
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.
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.
those requests that aren't grabbed as they arrive, assignments
are made at a weekly triage meeting.
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.
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
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.
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.
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
single reporting tool will probably not meet all needs. Even as
we add new tools, we probably will continue to use current tools.
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).
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