The tools of the master forecaster
Business intelligence apps can help agency managers make better decisions
Business intelligence software can guide managers through
difficult decisions, report anomalies or issues in an organization,
and help managers check on the condition of their agency. BI
software can examine the present state of affairs and analyze past
performance trends. If used effectively, the right business
intelligence tools can even predict the future.
And these little-understood programs are not just for financial
metrics. If the proper data is collected in a hospital, for
example, these tools can show managers the performance metrics per
department, ward and physician. These metrics can include
turnaround times, lead times for the average patient, and even
diagnose efficiency rates of departments and staff members.
However, understanding and choosing the right combo of business
intelligence tools can be a daunting task. There are dozens of
databases, buzzwords to bypass such as datacubes and dashboards,
and a myriad of reporting, querying and analysis tools that
complicate the search.
Two broad types of programs make up business intelligence
software tools. The first type is the database, or the
software-and-server combo that holds the data. Most often,
organizations use transactional databases like an enterprise
resource planning (ERP) database. Different flavors of ERP software
include products from Oracle Corp. and SAP AG. Relational
databases, like Microsoft SQL Server, are another common form of
database, particularly in the federal sector.
Despite the importance of these databases, the following review
focuses on products that make up the second part of the business
intelligence topology: reporting, querying and analyzing tools that
extract information from the aggregated data sources, like SQL, and
allow the user to find, manipulate and demonstrate the data.
Generally speaking, the database section is straightforward.
Simply pick the type of database your agency needs, which depends
upon the type of data you’ll be collecting, and build the
database using Oracle or Microsoft.
Most managers will tell you that the tough part of business
intelligence often comes when it’s time to do something with
the data. One pitfall is that often the wrong data, or too much
data, is collected. For example, you don’t generally want to
record or track trivial events, such as the department within your
organization that uses the refrigerator most. Although that’s
hyperbole, you’d be surprised how many organizations
I’ve found that have three or even four sets of
identification numbers for a single product, employee or event.
This brings me to my point. The best reporting, querying and
analyzing tools make it simple for managers to find what they are
looking for, and demonstrate organizational anomalies or weakness
at a glance. In order to better express this point, we have
reviewed five of the top business software tools that analyze,
query or report data.
We analyzed the performance, ease of use, functionality and
price attributes of each solution. Additionally, after using each
tool with a database of information to play with, we asked the big
question to determine the final grade: Would my operations greatly
improve if we employed this software tool?
Note to the reader: Business Objects S.A. was unable to make the
deadline for this story but expressed an interest in submitting a
product in the next BI roundup.
Carlos A. Soto, a former associate reviewer for GCN, isGCN Lab Review: Decisioneering Crystal BallPros:
studying for a master’s degree in business finance at Rice
University in Houston, Texas.
Financial and Six Sigma planning and
Requires business finance skillsPerformance:
BEase of use:
Many managers use reporting and analysis tools like
ProfitMetrics and iDashboards to get a rough sense of where their
organization is headed. But by looking into the Crystal Ball
Premium tool, you can get a very accurate look into the future
through the use of probability algorithms as opposed to the more
subjective trend analysis.
Crystal Ball Premium makes use of a mathematical technique
called the Monte Carlo simulation to predict the probability of
possible outcomes of a numeric scenario.
Despite how difficult this sounds, learning to use Crystal Ball
is almost as easy as installing the software. Although you can use
just Microsoft Excel to run a Monte Carlo simulation, it’s
incredibly more difficult to do so. In addition, it takes a long
time and is taxing on the CPU.
After running through a few simple steps in an installation
wizard, the Crystal Ball software loads and attaches to your Excel
toolbar, right below your drop-down windows. In minutes we were up
A very intuitive and simple-to-use introductory wizard launches
every time you initiate the software from the toolbar, and a
tutorial guides you through the steps needed to create a
An example that the software provides for Crystal Ball’s
powers is a simulation of profit forecasting given a simple
spreadsheet that has revenue, costs and total profit.
The Monte Carlo simulation essentially depends on your
understanding of your industry by letting you assume the
probability of your revenues and costs to go up or down
Through this probability assumption, the software then initiates
a simulation of a predetermined set of time forecasting where your
revenue, costs and total profits should be in the future. The
benefit of this simulation is that it then lets you drill into the
data to see the best- and worst-case scenarios as well as the most
Another benefit of the Crystal Ball tool is that it lets you run
statistical operational-efficiency reports that are commonly known
as Six Sigma operations.
Six Sigma calculations let you better optimize a process in your
organization by reducing variability and uncertainty, thereby
saving time and money.
Crystal Ball gives Six Sigma users the ability to calculate the
variance levels of an activity and localize disorders within a
series of events.
Six Sigma operations are predominantly found in plant operations
or industrial situations where a repetitive series of events
produce a product such as in a Humvee. An invaluable tool to the
military, Six Sigma applications can help to minimize cost,
increase operational speed and, more important, eliminate mistakes
By default, Excel can run many Six Sigma applications, but
Crystal Ball’s Six Sigma applications are easier to use and
more graphically intensive. The level of detail is also more
detailed than on a plain Excel document.
The retail list price of the premium-edition software is $3,000;
however with a General Services Administration discount, an agency
can pick up Crystal Ball for about $2,574. Decisioneering Inc.,
Denver, (800) 289-2550, www.crystalball.com
GCN Lab Review: Hyperion System 9 Essbase Analytics with
Pros: Robust analytical features and power graphic engine
Ease of use: A
Overall importance: A
Hyperion Solutions Corp., Santa Clara, Calif.
Hyperion System 9 Essbase Analytics is modeling software that
lets you link all your existing databases to provide a consolidated
view of your agency. With Essbase you can analyze your
agency’s finances, keep track of spending, conduct
merchandise planning, view product profitability, or conduct
capacity planning. In the past I have used Essbase to conduct
customer profitability reports, product profitability studies and
Essbase, or Extended Server Spreadsheet Database, is an
enterprise resource planning tool, which integrates and compiles
all your agency’s data and processes into a centralized
system. The ninth and latest version of this tool gives you the
capacity to unify all the basic functions of your organization in
order to maximize planning.
In techie talk, Essbase is a robust data cube. Data cubes are
computer programs that often interface with databases and let you
churn and manipulate data to create numerical scenarios describing
the condition of a project or agency. An important distinction
between reporting software such as the dashboard tools and data
cubes like Essbase are that data cubes are used to drill through
raw data to create the reports that dashboards typically
Currently, statistical analysis software such as Crystal Ball is
not a feature in Essbase 9, but rather complements its
capabilities. That’s probably one of the reasons Hyperion
recently purchased Decisioneering, the company that makes Crystal
According to Hyperion, Essbase can use more than 250
sophisticated algorithms out of the box, with extensible analytics
and procedural calculations built on centralized logic and business
rules. The software is user-friendly and logically constructed. In
seconds I was able to conduct Net Present Value and Rate of Return
on a mock set of data. Essbase 9 has time series algorithms that
track moving minimum, maximum and average inventory levels. It also
has statistics and data-mining features to help predict metrics
like department purchasing behavior based on a variety of pricing
and promotional strategies.
Essbase’s pricing is complex because it depends upon your
enterprise size and needs. The typical base price is $50,000, but
it can exceed $1 million as well. However, out of all the programs
in this review we found Essbase the most important for your agency.
It performs well, and the newly elevated reporting graphics make it
even more versatile. Most important, it’s easy to use.
GCN Lab Review: Microsoft Excel 2007
Pros: Universal application with improved
graphics and Web support
Cons: Can be time-consuming to create complex
Ease of use: A-
Overall importance: A
Some people might hate to hear it, but perhaps the most pivotal
business application in financial history is Microsoft Excel.
What’s more, Microsoft has made leaps and bounds with the
recent release of Excel 2007, as part of the Office Enterprise
As with all the new Office applications, the most noticeable
difference between the old and new versions of Excel is in the
layout of the tabs, icons and toolbars, as well as the look and
feel of the application.
Although Microsoft claims that the learning curve of the Office
suite could average two to three weeks, I got up to speed in about
two hours. A lot of this is attributed to the restyling and
configuration of Excel ’07 which includes a graphical ribbon
across the top of the window that displays the tools that once lay
hidden in older versions of the application.
As this review emphasizes, Excel is not only the place where
most business intelligence tools and plug-ins operate, but Excel by
nature is also a data cube and great software for quick operational
analysis. One issue that I have with the new version is that
accessing the Analysis Tool Pack is a lot more difficult.
The Data Analysis tool pack lets users perform 19 different
types of analysis calculations on data, including running a summary
statistical analysis on a set of numbers that renders the mean,
median, mode, range and variance of those numbers, which the new
Excel can compute in seconds.
Another part of the tool pack performs regression analysis,
which can be used to find out if a mathematical relationship
between two variables exists. For example, let’s say I have a
hypothesis that the distance that a tank can travel is heavily
correlated to the weather.
I can gather the numerical data in a field study, run a
regression analysis in Excel and see if a relationship exists or
Like the older versions of Excel, the tool pack is not installed
by default. But the ’07 version makes it easier to install
and operate because there are fewer steps in the process to get to
the tool pack.
Despite the fact that Excel ’07 can run the same
statistical Six Sigma Data that Crystal Ball is capable of, we
still found Crystal Ball easier to use and more robust in its Six
Sigma analysis. However, the best scenario is a combination of both
The list price for Excel 2007 is $230, but you can upgrade forGCN Lab Review: ProfitMetrics Dashboard
Works with wide variety of inputsCons:
Difficult to usePerformance:
B-Ease of use:
Many business intelligence software companies use the analogy
that business intelligence tools help diagnose the condition of a
company or agency in a manner similar to how blood tests and X-ray
scans are used to map the condition of a patient. ProfitMetrics
takes that concept one step further by stating that the problem
isn’t in the analysis or numbers, but in the way the analysis
and numbers are displayed.
Based in a small office in Houston, this five-year-old company
has an interesting approach toward business reporting. Mark Palmer,
CEO of ProfitMetrics, says the trick to better understanding your
organization is to see it from a holistic point of view in a
printout that contains as much data on your company as possible.
Sounds simple enough but it’s rarely done, Palmer said.
The average PowerPoint slide has about one or two facts per
page, he said. The front page of a typical newspaper has about four
to 10 facts. A ProfitMetrics report about an agency can average
anywhere from 60 to 100 facts per page in a format that is designed
to diagnose all aspects of how the agency runs. This level of
detail provides what Palmer calls a high-density dashboard view of
your organization that lets you analyze trends and find anomalies
in a single glance.
The software works by pulling your data from any type of
database and placing that data into an input file where the metrics
you chose are applied. The software is tailor-made for each
organization, so these metrics can be anything from budget analysis
to the employee turnover rates of a hospital over time by
ProfitMetrics relies exclusively on Extensible Markup Language
in configuring the charts and converting the numbers into visual
artifacts. So an XML editor configures the way in which the data
will be displayed by creating an XML Dashboard Description file and
merging that file with the input file that contains the metrics or
raw data of your organization. Finally a quick rendering engine
produces a high-density dashboard that can be printed out in PDF
format or viewed dynamically on the Web in a Scalable Vector
Graphics (SVG) format.
Despite the benefit of having your entire agency displayed on a
page, the software has strong drawbacks. The most noticeable
problem is that it’s difficult to use. Explaining how the
software works is hard enough. There are no detail wizards to guide
you through these steps. So unless you want to use the
ProfitMetrics people as live-in consultants every time you want to
change your template to match your agency’s needs,
you’ll need an XML programmer on staff. And it wouldn’t
hurt to train your database administrator to extract the data using
the ProfitMetrics software.
In a live demo, Palmer even had a tough time getting the
software to render images. This issue stems from the immaturity of
the software and the need for a little further development. And
since the software works on predefined templates, you’re
limited if you want to display a metric that is special to your
agency alone. And let’s face the facts: Federal, state and
local agencies have a lot of data they need to render that is very
different from private-sector data, and these differences may not
be found in templates.
We found high pricing an issue throughout the entire review of
business intelligence tools, and this product was no exception.
Palmer said that the pricing and services offered are similar to
what you’d expect in building Web pages.
Depending upon your agency’s needs, the Dashboard
Solutions software can cost anywhere from $10,000 to $40,000. This
may not include a consulting fee you’ll need to pay if you
don’t have XML-savvy staff on hand.
Despite these issues, however, I think that having this softwareGCN Lab Review:
can be a great improvement to your organization if you have the
budget and the time to learn it, and particularly if you and your
staff live by metrics. We look forward to product improvements,
particularly in ease of use, to make this software of even greater
importance to business intelligence reporting.
Real-time, one-page view of your agency, plus drill-down analysis capabilityCons:
Expensive to implementPerformance:
B-Ease of use:
Unlike ProfitMetrics, which can take two to three weeks to install and start generating reports, iDashboards complies with a multitude of Web and database standards such as Java 2 Enterprise Edition, XML, Open Database Connectivity (Oracle) and Microsoft Windows .Net to get your high-density reports up and running within a week, typically two to three days.
iDashboards is similar to the ProfitMetrics model in that the software renders a topographical and holistic view of your agency. However, unlike ProfitMetrics, this view is dynamically exhibited on a Web portal for an up-to-the-minute look at your organization.
This functionality makes it a more important tool if you have a lot of staff dispersed in several locations around the globe, and pushes iDashboards into a category that is more of a decision-making tool as opposed to a reporting tool.
We found ProfitMetrics a little more conducive to letting you conduct trend forecasting, or predicting possible future scenarios by glancing at past information. However, iDashboards is by far the best tool at depicting the current state of your enterprise.
Perhaps the most important aspect of iDashboards is that it is built on layers upon layers of customizable information. So, you can link charts to other charts, dashboards or even URLs.
For example, if I’m looking at a budget chart for December, I can customize the dashboard so that when I click on that chart it links to another chart that details budget information by weeks or days.
Another feature that sets iDashboards apart from its competitors is the pivot feature. A pivot lets you filter the data on the chart without having to create a separate chart. For example, if you have a map of Europe that details the economic data of each country in Europe for 2006, you can select a small map key on the left-hand side of the chart and change the year from 2006 to 2005 or earlier. This lets you quickly see any trends in your analysis without having to create multiple charts.
For all the features that iDashboards offers, we were hoping to see an aggressive price stratum. But iDashboards costs about $70,000 per server installation. iDashboards does offer a 20 percent discount for government employees.