The tools of the master forecaster

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, is
studying for a master’s degree in business finance at Rice
University in Houston, Texas.

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
and running.

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
simulation.

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
interdependently.

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
probable outcome.

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
and repetition.

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
Visual Explorer

Pros: Robust analytical features and power graphic engine

Cons: Expensive

Performance: A

Functionality: A

Price: B

Ease of use: A

Overall importance: A

Contact:

Hyperion Solutions Corp., Santa Clara, Calif.

(800) 286-8000

http://www.hyperion.com

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
workforce analysis.

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
display.

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
Ball.

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
business models

Performance: A

Functionality: A

Price: A-

Ease of use: A-

Overall importance: A

Contact:

Microsoft Corp.

(800) 765-7768

http://www.microsoft.com

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
2007.

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
not.

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
programs.

The list price for Excel 2007 is $230, but you can upgrade for
about $100.

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
department.

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 software
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.