by Clifford Alper, Kevin Brown and Gerald R. Wagner
The design, development and availability of visualization software have changed dramatically in about the past 24 months. The changes have resulted in software that is more interactive, more intuitive, easier to use, faster to implement, and far less expensive. This white paper is an introduction to three non competitive software products that are leaders in this revolution. There is one each for visualizing the categories we’ve named the past, the present, and the future, i.e., Category One, Category Two and Category Three.. The reader should understand that the products described below are not purely in one category. However, each is in the category of their primary strength. These products tell the story of the current state of the art. Visualization technology is rapidly adding more fuel to the gigantic Business Intelligence fire. As noted author Stephen Few states “Data visualization offers the greatest area of potential benefit to business intelligence today”.
Visualization technology and applications can be put into three categories, namely:
Category One: Visual discovery of insights hidden in historical data bases.
Category Two: Visual presentation of the current state of the organization.
Category Three: Visual simulation of alternative futures.
These are not unique categories. Category One has some similarity to Category Two. Category Two has some similarity to Category Three. However, Category One has little or no similarity to Category Three. Philosophies, theories, and concepts expressed in each section do apply to other sections.
Tableau’s strength is for visual discovery of insights hidden in historical data. DM Review Magazine evaluated over 60 solutions and named Tableau as “best overall” for Data Visualization.
PC Magazine recently named Tableau ‘Best of 2005’ in the Data Analysis category. Tableau uses visualization for what it calls exploratory data analysis. Exploratory data analysis is an underappreciated aspect of data analysis. Traditionally, data analysis is considered part of statistics. Statistics is appropriate when testing hypotheses when they are in doubt. But often the data speaks for itself. Do we really need to perform a statistical test to confirm what seems obvious, that say, the orange circles in the upper left are star performers and the orange squares in the lower right are troublesome? And even when a hypothesis is in doubt and further statistical analysis is required, exploratory data analysis makes it easy to isolate the important factors. For example, before fitting a linear regression to some data, we would surely want to isolate each of the subgroups sharing a common linear trend.
Visualization and exploratory data analysis are synonymous. By far the most important aspect of exploratory analysis is that we force ourselves to look at the information in new ways. As a result, we often see things we never expected to see. Images stimulate us to think about our business in different ways, leading to creative insight and better decisions.
Tableau builds tools designed for visual analysis in support of business intelligence. It is a powerful system for asking and answering analytical questions involving data stored in databases. Tableau enables anyone in the organization to better analyze business data using their natural ability to think visually.
One powerful way to evaluate any analysis tool is to test its effectiveness in answering specific questions. At the most fundamental level, does the tool have the analytical power needed to answer the question? At another level, how long does it take to answer the question? As anyone under time pressure knows, a question that is hard to ask using the tools at hand is often not asked at all.
Let’s consider some examples of how visual analysis is used to answer specific business questions.
What is the best selling product in a region? Most business intelligence tools allow people to interact with data using tables of numbers. Figure 1 is familiar and typical. The data is presented using the industry standard crosstab, or Pivot Table. This table shows the breakdown of Sales by Product Type, Product and Region. The underlying relational database consists of all sales transactions in the organization. The crosstab summarizes the data by computing the total sales by accumulating sales of individual stores, products and time periods.
Which product-region pair is the best seller? That question can be answered with the table, but it takes time. In fact, you must sequentially scan all the numbers in the table looking for the maximum. And that scan is error prone because you must remember the best selling product as you scan the table. If you lose track of the current maximum value, you need to restart the scan. Now look at the bar graph in Figure 2. The best selling product immediately pops out: Columbian Coffee in the East. The analysis in Figure 2 makes it easier and quicker to answer the question because it presents the information in a better way.
Why is the visualization in Figure 2 better? Because it uses the power of our perceptual system. Psychology experiments have shown that people can immediately find the tallest bar amongst a set of bars of varying height. The length of a bar is a preattentive visual cue. There is no equivalent visual cue in the table. Our visual system performs rapid search by processing the image in parallel. The parallelism means that the time it takes to find the largest bar is independent of the number of bars in the set . In computer science terms, the search takes constant time. In contrast, the time needed to search the table depends on the number of cells. It takes linear time. As the number of items increases, the efficiency of the bar chart vs. the table increases arbitrarily. This simple example demonstrates the power of visualization to save time and reduce errors in elementary analytical reasoning.
For the question we posed (“What is the best selling product in a region?”), the bar chart is better than the table. However, there are analytical tasks where tables are better than charts. For example, tables provide a powerful indexing mechanism that can be used to speed up search. Suppose we change the question to “What is the Sales for Columbian Coffee in the East? This question is hard to answer precisely with a bar chart, but it is easy with a table. Why is that? First, the row and column headers in a table efficiently organize the information so that it is easy to look up values. If the rows are sorted alphabetically, we can quickly find the row corresponding to a name. To answer the question, we quickly find the row containing Columbian Coffee and the column containing the Eastern region, where we find the desired Sales figure. Second, it is hard to estimate the length of a bar accurately. Our visual acuity is limited and visual estimates of length are only accurate to a couple of decimal places. In contrast, the number in the table is shown exactly to full precision.
This example is meant to show the power of visual thinking, and its most important principle: For a given question or analytical task, some visual representations work better than others. Visual thinking involves choosing the right visual representation for your task. Tableau’s technology was developed to make it easy for analytical thinkers to create the right visual representation, the representation that makes the problem they are working on easy to solve.
Have sales increased this year compared to last year? Here is another example involving two different versions of a bar chart. Each row corresponds to a different Product Type. Each bar represents a different Region and Year, and each Region is colored differently so it is easy to distinguish one region from another. The visualization in Figure 3 has a cluster of four bars for each of two years; the visualization in Figure 4 flips the order to a cluster of two bars for each of four regions.
Suppose we want to compare sales of products in the two years. Which visualization is better at helping us answer that question? Look at the chart in Figure 3. It is difficult to decide whether sales are increasing or decreasing from 1998 to 1999. Now look at the chart in Figure 4. It is very clear that sales increased for all products in all regions from one year to the next.
Why does the right image facilitate the comparison? Comparisons are easier when the items to be compared are adjacent. It is more difficult to compare items at a distance. Our visual system is foveated, which means that our eye’s processing power is concentrated at the center of the visual field where the resolution is highest. Even though we monitor our entire field of view, we focus our attention in the direction we are looking. For this reason, nearby items are seen all at once. In order to compare items at a distance, we need to move our eyes back and forth between them. Thus, when answering a question involving comparison, it is important to order the values along the axis to facilitate the comparison. Changing the visualization to show the data in the right order makes the answer to the question obvious. A different question might require a different order.
Tableau supports the cycle of analysis. In practice, an analysis session consists of a sequence of questions. Individual questions are posed, and their answers found. During the session, each answer leads to new questions. This cycle of hypothesis, experiment and discovery is typical of analytical problem solving. We call this the cycle of analysis. It is essentially a “visual Q&A session.”
Tableau is designed to support the cycle of analysis. First, posing a question and getting the answer involves only a few actions. Visual thinkers quickly learn to compose pictures that answer their specific questions. Cumbersome interfaces derail your train of thought. The naturalness of the Tableau interface keeps you focused on the problem at hand. Second, visualization often shows the unexpected. An unexpected finding stimulates the thought process, encouraging deeper analysis or a different path of exploration. Third, Tableau records the analysis process as it proceeds. It is always possible to undo, redo or return to a previous state. It is also possible to create bookmarks signifying important findings or branch points in the analysis. The key point is that exploration is easy, and hence encouraged.
The strength of Crystal Xcelsius is for visualizing current operating data with highly sophisticated and attractive charts. The product was founded by Infommersion, Inc. in San Diego, California and was acquired by Business Objects in 2005.
Crystal Xcelsius is an easy-to-use desktop application that creates highly interactive, data-rich dashboards linked to Excel data or corporate databases. The point-and-click interface is simple to learn and should be intuitive for users familiar with building charts within Excel -- there are no programming or technical skills required. Dashboards can be created quickly, and exported to PowerPoint, Acrobat (PDF), Email, or HTML. The Xcelsius dashboards can be viewed locally in any of these formats, or can be copied to an intranet/internet where they can be accessed by anyone with web browser with the Macromedia Flash plug-in (free). Hosting the dashboards requires no server or Xcelsius software. All of this functionality is available with the Standard or Professional versions of Xcelsius. For dashboards with more industrial strength connections to corporate databases, the Crystal Xcelsius Workgroup is required, along with the programming capabilities to link up the dashboards to SQL databases, OLAP databases, or other data sources.
The Sales Map dashboard below provides an illustration of the interactive features of many Xcelsius elements. Interactive design elements include:
Alerts: Many of the Crystal Xcelsius design elements can display alert colors based on linked data. The most common use is for red/yellow/green alerts in gauges or icons, but alerts are also available in tickers, charts, and maps. The sales map in Figure 5 is an example of geographically-based alerts which can be programmed based on sales volume or other state-based metrics.
The Retail Outlet Summary in Figure 6 is a sample dashboard that illustrates a number of the drill-down and query features that can be constructed within Crystal Xcelsius. At the top is a ticker displaying metric information and performance results for the retail stores.
Below the ticker is a green table with summary information from the selected retail stores. The entire table was imported directly from Excel. This Excel import capability provides powerful drill-down and data display functionality. The red and green alerts are individual Xcelsius components that sit on top of the table, and are linked to the table values.
The controls on the right side of the dashboard illustrate user-friendly querying capability. In this example, the user can select combinations from the list of 100 stores, as well as three different analysis periods (10-60-90 days) and two metrics (Sales Lift and Gross Profit Lift.) In this way, the user can drill into a large number of data combinations through a simple, highly intuitive interface. The query capability can be built into a model with Excel-based data, or can link to industrial-strength SQL or OLAP databases.
Most business reporting is backwards looking in nature, analyzing last month, YTD, last year. Yet, running a business is similar in ways to driving a car; nobody drives with eyes glued to the rear view mirror! A driver needs to see what’s coming, not what’s passed, and thus requires a clear view of the road ahead. Business analysis tools need to look ahead as well, and Xcelsius provides several components which are valuable for constructing forward-looking dashboards. The dashboard in Figure 7 contains a simple example of a slider component, used to provide “what-if” capability for projecting future growth in the number of customers. The table includes the actual number of customers during the current year (2005) as well as a projection for next year (2006.) The column chart reflects the values in the table, and the sliders allow the user to vary the predictions. The table values and the chart both change interactively as the user moves the slider, providing an intuitive visual tool for developing projections. There are several other components such as dials and spinners which can similarly be utilized to build powerful “what-if” calculators.
While the purpose of this paper is not to provide a “how-to” guide for Xcelsius, a quick overview of the process would be useful. Xcelsius dashboards are created in the desktop environment and require nothing more than Excel and the Macromedia Flash player (a free browser plug-in) along with Xcelsius itself. The user first creates a new dashboard within Xcelsius and links it to the specific Excel file containing the dashboard data. The dashboard elements are dragged and dropped onto the dashboard canvas and linked to the Excel data in the same way that an Excel chart is linked to the underlying data. Users who are familiar with chart creation in Excel will find Xcelsius to be fairly easy and intuitive. Once the dashboard elements are designed and linked to the data, the export function generates the dashboard itself, which is a Macromedia Flash file.
There are a variety of dashboard export options:
The simplicity and portability of the Xcelsius dashboards are among its greatest advantages. The Flash format can be run by any Flash-enabled browser and does not require Xcelsius or any other proprietary software. There is no server required for viewing the dashboards, nor is a connection to the internet required.
This makes hosting a dashboard extremely easy. Since a dashboard consists of nothing more than Flash files, HTML, and possibly graphics, all that is required is copying these files to an intranet or web location and then providing a browser link; no server software or Xcelsius software is needed for this hosting.
A variety of options are available for supplying data to the dashboards. Excel is the simplest of these, and is always utilized during the design process. Refreshing dashboard data in the spreadsheet model requires changing the Excel data, importing the Excel source into Crystal Xcelsius, and then exporting a new dashboard. This simple spreadsheet model is ideal for small scale dashboards with relative static data sources. For more “industrial strength” enterprise, there are several options for connecting real-time to external data sources. Note that connections to data sources other than Excel require the Workgroup edition of Crystal Xcelsius, whereas the Standard and Professional versions can be used with Excel.
There are two situations that would call for connecting the dashboard to external data sources:
External data sources include:
Links to a SQL database can be created for an Xcelsius dashboard utilizing xml and asp scripting on the server. Both the xml and scripting involved are relatively simple.
The SOAP Web Services connector is a surprisingly easy-to-use component that allows the dashboard designer to map web services data directly into the dashboard with almost no specialized technical knowledge of web services.
The ability to connect the Xcelsius dashboard to non-proprietary data sources – SQL, XML, Web Services – means that information from a wide variety of corporate databases and other sources such as Excel can be collected and then integrated into a database or data mart to supply the dashboards.
Once the connection is established from these data sources to the dashboards, simple settings specify the frequency of update. Dashboards can be configured to refresh data as often as every few seconds.
Forecasts and predictions that are developed within Planners Lab can be exported to Xcelsius dashboards and integrated with other enterprise data. A good example would be revenue projections that include a combination of actual and forecast revenues. Revenue forecasts would be developed and updated monthly using the simulation and visualization features of Planners Lab while YTD actual revenue would be pulled from the accounting system. A year-end revenue projection – that is, YTD Actual Revenue + Forecast Revenue – could be presented within an interactive Xcelsius dashboard. The Planners Lab includes an export utility that is configured to structure exported xml files for linking directly into Crystal Xcelsius. By utilizing local xml files for data interchange, it is possible to make forecast changes in Planners Lab and then export these changes to update the Xcelsius dashboard in seconds. This connection requires the Crystal Xcelsius Workgroup license.
Crystal Xcelsius provides an easy-to-use development platform for power presentations as well as highly attractive and interactive dashboards and portals. The Standard and Professional editions are lightweight, low priced desktop applications that provide considerable design power to Excel users with little or no programming or technical background. The Workgroup edition provides links to a variety of enterprise data sources and the ability to develop powerful dashboards with real-time data links and extensive data drill-down. Dashboards created from all versions can be deployed quickly and easily and can be presented in a variety of standard formats including PowerPoint, PDF, and HTML.
The Planners Lab strength is for simulating and visualizing the future. Simply put, it is for “on the fly” visual Q&A about the future. The product in practice is also used for reporting historical data, comparing budget vs. forecast, and other more operational uses but that is not its primary purpose.
The software is a strategy simulator with two primary components, namely, (1) an easy to learn and use assumption description language (also called model-building or planning language) and (2) intuitive and easy to use drag and drop visualization. Decision makers can easily and on the fly “play” with assumptions and immediately experience the consequences of changes. Assumptions are in plain English (or another native language) and readable by anyone.
The product’s raison d’etre is that it facilitates conversation like interactions with decision makers while they think about and describe their business assumptions. These assumptions, invisible in most decision-making processes, are often more important than the numbers themselves. No other product supports such understandable participation by decision makers in creating the assumptions that generate the numbers. The sources of assumptions can be from databases, market research, surveys, and decision maker minds, to name a few. Often times, assumptions about the future come from accumulated experiences in decision maker minds in the form of opinions.
The example shown here is a project proposal for a consulting company. The example is for estimating the profit on a consulting project with 3 levels of consultants, namely, Senior Consultants (Professional), Junior Consultants (Assistants), and Administrative Assistants (Administrative). Variables include days billed, billing rates, salary rates, overhead rates, travel and entertainment, equipment, and materials.
This example model and a trial version of the software are available by contacting Jerry Wagner at firstname.lastname@example.org.
The model building process starts with creating a hierarchical tree with nodes. These nodes are a picture of the overall model design or architecture. It is like building a family tree. The tree could be anything such as an organizational chart, a Balanced Scorecard process, R&D investment options or whatever fits the user’s situation. The nodes for this example are shown in the left hand side of Figure 9. The user designs (lays out) the tree however they wish. This is not a “canned” process according to what someone else thought was wanted or needed.
Also note “Quarter 1 – Quarter 8” in the Column Statement window in Figure 9. This indicates this model is for an 8 quarter planning horizon. This could have been months, quarters, decades or whatever. The general rule of thumb is enter it as you think makes sense and it is probably OK.
Each node contains its own plain English equations (or whatever is ones native language). These are the assumptions for a node. The assumptions for “Billings” are also shown in Figure 1. Time period data in an assumptions (equation) are separated by commas if data changes from period to period. Whatever is the last thing that appears in an equation is used for all remaining time periods. Therefore, Professional Billing Rate is 1400 for the first four quarters and then 1500 for the remaining quarters. The user with almost no training and a little practice can read a Planners Lab model like a story. It is readable by anyone familiar with the business. Therefore, maintenance by anyone becomes straight forward vs. another tool such as Excel which is nearly impossible for someone other than the original author to maintain.
The Planners Lab has several reserved or key words such as TRIRAND and FOR which have specific meanings. These reserved words provide shortcuts for building models. The reserved word TRIRAND indicates a triangular distribution. For our example, it is uncertain how many days of professional time will be billed. The values that define a triangular distribution are a lowest value, a most likely value, and a highest value. For this example the lowest Professional Days Billed in quarter one will be 225, most likely it will be 250, and it could go as high as 300. The distribution then changes for the second time period.
The model shown here could have been built in any number of ways – different tree structure, different variable names, different equations, etc. There might be hundreds of assumptions within a node. Enterprise planning models could have thousands of assumptions across all nodes. The end user decides what fits their situation, their needs, their vocabulary, etc.
After creating a model the user continues the learning and discovery process by visualizing results and asking what if and goal seek questions. At the bottom of the screen is a button called “Playground”. Clicking that button displays the icons for chart styles at the top of Figure 10.
The user simply drags any chart styles onto the stage (the main window); clicks on nodes that contain variables they wish to display; and drags variables from those nodes onto a chart template. To remove a chart, just drag it off the stage. To remove a variable from a chart simply drag it off the stage. To move a variable from one chart to another, simply drag from one to the other. Any number of chart styles an be on the stage at any time. This is not possible to communicate in this written document but it is very intuitive and simple to use. Anyone can do this.
Figure 10 is the result of dragging the “table” chart onto the stage and then dragging the variables shown onto the template. As variables are dropped onto the table template the the data automatically populates the table.
For large complex models it can become difficult to know how and what variables are related to each other. A helpful tool is “Anatomy”. Drag this chart type onto the stage and then drag a “goal variable” onto the chart. A goal variable is defined as a dependent variable such as revenue which depends upon independent variables such as salaries. For example, salaries would be considered a “what if” variable in the Planners Lab vocabulary. The software automatically lets the user know which variables are goal variables and which are what if variables. The Anatomy feature is very useful for understanding and communicating the structure and variables for a model.
Figure 11 shows the relationships between variables that affect Overall Revenue.
The What If feature is for making temporary changes in the model logic and seeing the affect of those changes. It is for “playing” with different assumptions and visualizing the impact of such changes. In Figure 12 we are wondering how to keep losses at no more than $50,000 in quarter one and then break even starting in quarter two. The What If line charts (dashed lines around the chart border) indicates that data in these charts are “draggable”. Data points or entire lines can be dragged and dropped and the impact seen in other variables. In this case changing overhead to 1.08 in quarter one and to 1.19 in quarter 2 and changing Professional Days Billed from 250 in quarter one and to 295 in quarter two is one combination of changes to achieve the goals.
Goal seek asks the question, “what does the value of one variable need to be “moved to” in order to achieve a desired goal value in another variable?”. In Figure 13 the goal variable is Overall Margin and the “moveable” variable is Professional Days Billed. The question becomes what number of days of professional billings are needed to achieve a loss of $50,000 in quarter one and break even in quarter two. The number of days billed in quarter two needs to be 352 up from 275 and 392 for quarter one up from 250.
The Impact feature asks the question “what impact does x% change in what if variables have upon a goal variable. In Figure 14 the goal variable is Overall Margin. The what if variables are Professional Billing Rate, Assistant Billing Rate, Administrative Billing Rate, Professional Days Billed, Assistant Days Billed and , Administrative Days Billed. The vertical slider on the right side of the chart is to move percent changes upon or down from zero. In this case a 10% increase in Professional Billing Rate resulted in a 22.9 % increase in Overall Margin. Reducing the Professional Billing Rate by 15% caused a 34.3 % drop in Overall Margin. It is very simple to get these kind of real time on the fly answers with the Planners Lab.
Risk analysis is for quantifying uncertainty. In Figure 15 are shown three risk analysis charts. On the right is Overall Margin, the center chart is for Overall Expenses and the left side chart is for Overall Revenues. The user slides the quarter slider to choose which quarter to view. The user also drags and drops the pointer on the horizontal axis to ask questions like what are the chances of exceeding a value or what are the chances of being less than a value. In this case Overall Margin has a 25.6% chance of exceeding $134,690.12.
More about the Planners Lab: This is only a brief view of the basics of the Planners Lab. Far more capability exists. However, it is not possible to fully understand the dynamic simplicity of the Planners Lab visualization by these written words. You need to experience it to. It takes about one hour of training to get it.
The software is a client server network enabled product. Therefore it supports both occasionally connected and group collaboration via a network including the Internet. The software can interact with data bases to extract historical data from packages such as Excel and SQL. It can also provide data for Xcelsius charts as described in the Xcelsius section. A use could be for an analyst to use the Planners Lab to on the fly visualize/evaluate several what if scenarios prior to an important executive meeting. The analyst then decides which scenarios to present during the meeting in the form of Xcelsius charts.
Planners Lab model builders can publish a model to a web server so that invited viewers can have complete use of all visualization functionality. However these viewers can not change the logic in the model, i.e. the nodes and their equations.
The Planners Lab is one of the first major business applications to use Macromedia FlashMX™ as its interface development platform to support animated interaction. The Planners Lab software is a starting point to achieve the vision that business software should provide decision makers with engaging experiences, such as are currently available in computer gaming. Just as gamers receive immediate visual response to their actions, decision makers should have comparable feedback experiences with software-based strategy simulators.
For most organizations it is not a matter of which of these three products to acquire and deploy. They each have different purposes. All three are affordable and important in most organizations – for visualizing the past, present, and future.
Clifford Alper is the author of the Xcelsius section. He is Director of Product Development, Analysis Factory, Inc. Analysis Factory "helps its clients become more profitable, resourceful and efficient by turning the data they've got into the answers they need." Check analysisfactory.com. Kevin Brown is the author of the Tableau section. He is Vice President of Tableau Software, Inc. Tableau Software began as an extensive R&D project inside Stanford University’s prestigious Department of Computer Science. Check tableausoftware.com. Dr. Gerald Wagner is the author of the Planners Lab section. He is a Distinguished Research Fellow, Peter Kiewit Institute, University of Nebraska, Omaha.
Alper, C., K. Brown and G. R. Wagner, "New Software for Visualizing the Past, Present and Future", DSSResources.COM, 09/23/2006.
Gerald R. Wagner provided permission to publish and archive this article at DSSResources.COM in July 2006 and by email on September 16, 2006. This article was posted at DSSResources.COM on September 24, 2006.