Welcome to Dashboard Spy
SAS BI Dashboard for Google Analytics Data

Rising star business intelligence blogger, Tricia Aanderud, is on fire with her SAS BI blog posts over at http://www.bi-notes.com/author/tricia.

Her post today is quite excellent and worth reading through in detail. It’s available here at http://www.bi-notes.com/2012/02/dashboard-sas-bi-google-analytics-data. I’m cross-posted it here:

Tricia Aanderud is SAS BI evangelist, enthusiastic innovator & hired gun, pursuing design tips, programming tricks & other mishmashes to share. Tricia is the author of the upcoming book Building Business Intelligence Using SAS: Content Development Examples which helps new and experienced users of SAS BI to get up to speed quickly. She invites you to connect to her on Linked In or follow her on Twitter using one of the icons below:

Web Sitehttp://www.bi-notes.com


SAS BI Dashboard Rocks My Google Analytics Data Analysis

February 1, 2012
sas bi dashboard spark table indicator new columns and rangesI have to say I’m in love with the new SAS BI Dashboard and it’s many features. It really brings the Google Analytics to life and helps me understand how the Website traffic changed over time along with the key influences. The following figure gives you an idea of how you can integrate dashboardindicators for an interactive user experience. The best part is how easy it is.  Once I understood the how the feature worked, this indicators below took less than an hour to setup and most of that was deciding what data I wanted to see.   [More articles about Google Analytics here.]

Quick Dashboard Tour

This dashboard has two indicators, a line graph that shows the traffic over time and a spark table that contains specific data about site visitors. As the user drags the slider and clicks, the data in spark table updates.  I left the Week variable in the Spark Chart to help you understand what was happening. Also in the Last Wk Delta,  New Visitors (%) and New Visitor Delta columns, you can see the various gauges available.

sas bi dashboard making indicators interactive



Creating an Interactive Dashboard

For several weeks I’ve been discussing using Google Analytics with the SAS Business Intelligence tool set to get more value out of the data. When you can understand how the trend has changed over time, it helps you determine your next steps.  In a previous post I showed you the basics for creating a SAS BI Dashboard and in this post I’m just going to show you the highlights for how I created the above dashboard.

Preparing the Dashboard Data

For the SAS BI Dashboard, you can use several data sources, such as a SAS dataset or SAS information map. [Read more: Here’s how I extracted the Google Analytics data.]  The dashboard uses two indicators: Chart with slider prompt and a Spark Table.  With some planning, I was able to use the same data for both indicators.

It was important that I had the week over week differences to determine the growth rate.  If you notice, the traffic is divided into Segments based on the source field from Google.  You may have read Dominick Frasso’s article last week  about the importance of using advanced segments based on the ideas of Avinash Kaushik, author of Web Analytics 2.0.  Instead of using the Segments from Google Analytics, I wrote some code to parse the source variable and create the segment variable.  Here’s a snippet to give you an idea what was done and the inset shows the results.

create data in sas bi enterprise guide


The week over week difference was important, so this code determines the growth rate by Segment for weekly and new visitor traffic. So after creating the data (1)  I used the SQL query (2)  to create the data for the Spark Table.  Notice that all of this data is summarized, which is a requirement for using the SAS BI Dashboard.


sas bi dashboard data source in sas enterprise guideData created in SAS Enterprise Guide


Adding a One-Size-Fits-All Range

If you notice, the three new columns are all based on percentage growth. Basically meaning, growth over N percentage is good and under N is bad.  This means I can create one range that I am able to use multiple times instead of trying to create 3 separate ranges. You can see how the range was setup in the following figure.  All values from 0% to 100% are covered by this range.

In the figure inset, you can see the Range in use.  For this range, anything over 60% is green or exceptional is how I like to think about it.  Truthfully, probably any increase over 20% is exceptional for the small amount of traffic on my blog.

Tip! It might be worth it to create several ranges like this one that can be reused in multiple dashboards, you would just need to copy the range to the new folder. [Angela discussed dashboard naming conventions here.]

sas bi dashboard range one size fits all

Creating the Dashboard Indicators

After you have your data and range available, create two indicators for your dashboard.

Creating the Traffic by Weekly Visits Indicator

The first indicator created was the Chart with slide prompt indicator. In this figure you can see the end result along with what settings I used to create it.  Using the  data table above, I created a SQL query to sum the visits variable by week.  This way the line graph shows the total traffic for each week along the X-axis. Again – reusing to keep things lean.

When I added the indicator to the Dashboard, I was able to change it’s displayed name to Traffic by Weekly Visits.  [See Angela Hall’s blog post Removing the Object Name from the BI Dashboard  for more details about indicator names.]

sas bi dashboard slider indicator

Creating Segments Over Time Indicator

What I like about Spark Tables is that I can add as many columns as I please.  One thing to note about this Spark Table, I did not group anything.  All of the variables were grouped by segment and week during the data creation step.

  1. For this dashboard indicator, a new column for each variable (NewPCT, PCT_DIFF, PCT_NEW) was added.
  2. After adding the columns, select your data source variable. (Notice it uses the label not the variable name – so New Visitor Delta is PCT_NEW.)
  3. Add the range you created above.  You can also have a separate range for each column is you prefer.
  4. Select the desired gauge.  In the drop-down you can see a partial listing.
    I showed several different ones in this example so you could get some ideas.  For a real dashboard, I would not be so glamorous or you might say busy. This example is just for your edification.

sas bi dashboard spark table indicator new columns and ranges

Creating the Dashboard

Once you have created the two indicators, add them to a new or existing dashboard.

Don’t be alarmed: Note that when the Spark Table is added to the dashboard, it shows the data for all weeks and the line chart does not have the slider.This is normal – nothing is wrong.  When you view the dashboard, the indicators will come to life.

Adding Indicator Interactions – the Fun!

To add the interaction, do this:

  1. Position the two indicators as desired on your dashboard.
  2. Select the Interaction icon from the tool bar.
  3. In the Set Up Indicator Interactions window, select the indicator and columns to influence. In this case, the Week variable is what you want to use as a filter.
    Refer to the SAS Support site for complete documentation on creating a dashboard.

sas bi dashboard indicator interactions

Creating SAS BI Dashboard Indicator Interactions


The Ever Changing Dashboard

Dashboard Spy Readers – you are in for a very special treat. Today’s guest blogger is Dr. Harold Kerner, Senior Executive Director at the International Institute for Learning, Inc. and author of Project Management Metrics, KPIs, and Dashboards: A Guide to Measuring and Monitoring Project Performance

The Ever-Changing Dashboard

Harold Kerzner
Sr. Executive Director for Project Management
International Institute for Learning (www.iil.com)

If you are reading this short article, then you are on the Dashboard Spy website. I have been a fan of Dashboard Spy for some time, have read every article, but am often disappointed because only a small percentage of the articles are directly related to my profession, project management. I assume I have now gotten your attention! If you want to know more about why I am sometimes disappointed, read on.

I have been a project manager for more than four decades. In all of my years of experience, my greatest curse has been the cost of paperwork on projects. Every piece of paper handed to a client can cost between $1200 to $2000 per page. To prepare a report, the steps we follow are: organizing the report, typing, proofing, editing, retyping, graphic design, approval, reproduction, distribution, storage, security classification if needed, and ultimately, disposal. As a rough guess, we estimate approximately 8-10 fully burdened hours per page, which includes everyone involved in the previous steps. For companies with high overhead rates, reports can consume a large portion of the project’s budget.

Not all reports can be eliminated, but we can somewhat reduce the impact of this curse by striving for paperless project management, where appropriate. In the past, reports were needed to assist clients and stakeholders in making “informed” rather than seat-of-the-pants decisions on projects. Projects thrive on decisions, and decisions require meaningful and timely data. The bad news was that decision makers often had to read through volumes of irrelevant information to find the few pieces of critical data that they required. Fortunately, dashboards have come to our rescue in replacing reports.

The need for dashboards in project management is now quite evident. That’s why I became fascinated with Dashboard Spy. Unfortunately, the majority of the articles that I have read have a common thread: they focus on business intelligence data and information related to the long-term, ongoing business base of the company. The metrics in these dashboards may not change for decades, especially the financial metrics. These dashboards are more strategic than operational.

To understand why these dashboards may not be directly applicable to project management, we need to look at how project management will work in the future. Consider the following steps, that are listed in the order in which I believe they will take place:

  • The project manager and the client/stakeholders will meet and come up with an agreed-upon definition of success for that project. (Each project can have different success criteria.)
  • The project manager and the client/stakeholders will identify the competing constraints that will be tracked in order to monitor that the success criteria are being met. The constraints can be time, cost, quality, risk mitigation, safety, aesthetic value, etc. Each stakeholder may identify a different set of constraints, and metrics and Key Performance Indicators (KPIs) will be established to track each of the constraints.
  • The project manager will show the client and the stakeholders the core metrics that to be used on the project, namely cost, schedule, resource utilization, quality, scope and action items.
  • The project manager will show the client and the stakeholders their metrics/KPI library and ask the client and the stakeholders to select what other metrics and KPIs they wish to have displayed in the dashboards. The client and stakeholders may identify metrics that do not appear in the original library.
  • The project manager will try to get all of the stakeholders and the client to come to an agreement on what will be displayed on the dashboards. It is highly unlikely that an agreement will be reached. The project manager must then plan on the possibility of having different dashboards for each of the stakeholders and the client.
  • Because of the necessity for different dashboards, every project team may be assigned a dashboard designer knowledgeable in infographics.
  • As the project progresses, the client and the stakeholders may identify the need for changes to the metrics and KPIs. This may require additional effort from the dashboard designer.
  • Some of the metrics and KPIs may be applicable to only one of the life cycle phases of a project. An example would be the amount of money or percentage of direct labor dollars used for planning the project. As metrics and KPIs change, so might the construction and design of the dashboards.
  • When the project comes to an end, the project team is debriefed by the person(s) responsible for maintaining the metrics/KPI library. The metrics/KPI library is then updated.

By now, you should recognize the differences in the dashboards needed for project managers. They include:

A mixture of metrics and KPIs rather than merely KPIs

  • Throughout the life of the project, metrics may become KPIs and vice versa
  • The dashboards are ever-changing
  • There may be a wide variety of different dashboards on the same project
  • There must be flexibility built into the dashboard design so that changes can be made quickly
  • Every project team may require support from a dashboard designer

Hopefully, you are beginning to see the complexity of project management dashboards. Unfortunately, for some companies the situation becomes more challenging. For most BI and strategic dashboards, we know who the viewers will be. There may be only a dozen or so executives at the top floor of the building reviewing the dashboard information. But for companies that are managing project for external clients, the audience of client and stakeholder viewers can be several orders of magnitude greater than internal viewers. And each of these viewers may have different dashboard needs. It is impossible to develop standard dashboards that will satisfy everyone all of the time.

When you manage projects for external clients, you always strive for repeat business. Follow-on work is based upon customer satisfaction and loyalty. Customer satisfaction can be directly dependent on the timely flow of meaningful information. At the end of each project, we generally debrief the client and stakeholders and ask them what we can do differently on the next project we manage for them. Most customers respond that they want the information displayed in the dashboard to be more closely aligned with their business model rather than your company’s business model. This may require new metrics. The price you must pay for this is the requirement for an ever-changing dashboard.
Project managers may have three information systems on their project:

  • An information system for the project manager
  • An information system for the project manager’s parent company
  • An information system for the clients and stakeholders

The first two bullets may be satisfied by standard dashboards that do not change often. The third bullet will most assuredly require ever-changing dashboards.
The last topic to be discussed is metric/KPI measurement. With strategic and BI dashboards, the majority of the data needed to support the information in the dashboards comes from the company’s information systems. Information on topics such as market share, new customers and profitability is relatively easy to measure and report.

But what happens when clients and stakeholders want metrics that require complex measurement techniques? Before agreeing to provide a metric or KPI in a dashboard you must be sure that you understand how to perform the measurement and that you have people on the project team trained in metric measurement. As an example, clients today are asking for a metric that reports the ongoing value of a project as the work progresses. Without a good grasp on metric measurement, you cannot promise that this metric will be included in the dashboard.

As can be seen, the dashboard environment in project management has some different requirements than the traditional BI dashboards and strategic dashboards. I enjoy reading the material in Dashboard Spy and encourage all of you who have ideas for the best way to design project management dashboards and ever-changing dashboards requiring flexibility in design to submit articles to Dashboard Spy. I look forward to reading them.
HAROLD KERZNER, (M.S., Ph.D., Engineering and M.B.A) is Senior Executive Director for Project Management for International Institute for Learning and Professor Emeritus of Systems Management at Baldwin-Wallace College. He has published more than 50 books (including later editions) on project management. His latest book is Project Management Metrics, KPIs and Dashboards, released in August 2011. Contact Dr. Kerzner at harold.kerzner@iil.com

Connecticut Economic Dashboard

The idea of benchmarking the performance of a municipality or governed area such as a state is quite interesting. The Connecticut Economic Resource Center or CERC has been using business intelligence dashboards to provide perspectives on Connecticut’s competitive performance.

CT economic dashboard

Visit the dashboard here: CERC Dashboard

Take a look at the CERC dashboard and you’ll note that it’s basically an Economic Dashboard for the state of Connecticut. The dashboard is from idashboards.com and is interactive in nature. The graphics used in the dashboard represent both well-established metrics and newly developed measures to benchmark the state’s current conditions and potential for growth in areas such as:




Urban vitality



Business vitality

The designers wanted to give different perspectives on the economic metrics and thus incorporated the following techniques:

Often, a single webpage shows different data items that highlight interactions among the variables.

Graphics were chosen that quickly showed general trends as well as focus and highlight recent critical changes.

To further understanding of the issues, different presentations of the same data are often used.

Here is the link to the dashboard. Please check it out. Also, take a look at this page and note how promotion of the dashboard is important as well. Note at the bottom of the page the news releases about the dashboard called “Dash Flash”.

About the CERC Dashboard

Home Energy Dashboard

Dashboards in the home can lower energy consumption. The growing availability of “energy dashboards” for home owners to use for monitoring their energy consumption is helping to reduce electrical loads.

Here’s a look at one such unit:

Home Energy Dashboard

It’s from this post:

Musings of an Energy Nerd

Here’s an excerpt:

In recent years, the technology of our cars has advanced at a more rapid rate than the technology of our homes. A new car’s dashboard has gauges that display all kinds of information, including the amount of fuel in the car’s tank, the oil pressure, the electrical system voltage, and sometimes the tire pressure. Many new cars even have a real-time fuel-efficiency gauge that displays miles per gallon.

If you’re interested in comparable information about your home, you’ll probably have to go down to the basement and look at the float gauge on the top of your fuel oil tank. Then you can go outside and read the gauge at the top of your propane tank. Next, stick the prongs of a multimeter into an outlet to verify your electrical system voltage. And if you want electrical use data, you can wait until you get your utility bill at the end of the month.

It will probably be many decades before most homes have such car-like features as electrically operated windows or dashboards that indicate whether the doors are latched. But technology-savvy homeowners can already install a real-time whole-house electricity meter with a display for the kitchen or living room wall. Often referred to as “energy dashboards,” such monitors are available for less than $200.

You can even buy a more sophisticated dashboard that displays electricity, natural gas, and water use — although the cost of the required monitoring equipment rises steeply with these added features, into the thousands of dollars.

Documented energy savings

Studies have repeatedly shown that homeowners do a better job of conserving energy if they get real-time energy-use feedback. If you can see how many kilowatts your house is consuming, you’re more likely to check whether you accidently left the basement lights on.

In a March 2006 paper, “The Impact of Real-Time Feedback on Residential Electricity Consumption,” researcher Dean Mountain, a professor of economics at the McMaster Institute for Energy Studies in Hamilton, Ontario, reported data from an energy-dashboard study conducted by a Canadian utility, Hydro One. On average, the 400 Ontario households that received a PowerCost whole-house electricity monitor reduced their electricity usage by 6.5%. Mountain noted, “An important observation from the study is that the behavioral response remained persistent and did not decrease over time during the study period.”

In an April 2006 paper, “The Effectiveness of Feedback on Energy Consumption,” researcher Sarah Darby reviewed published studies of savings attributable to electricity and natural-gas dashboards. “The literature reviewed here mostly consists of primary sources, with a few review papers. The focus is on feedback on gas and electricity consumption,” she wrote. “The norm is for savings from direct feedback (immediate, from the meter or an associated display monitor) to range from 5%-15%.”

An article titled “Evaluating Energy Use Feedback Devices,” published in the July/August 2008 issue of Home Energy magazine, reports the results of a Florida study of electricity-use monitors. Three researchers from the Florida Solar Energy Center —Danny Parker, David Hoak, and Jamie Cummings — measured electricity savings in houses equipped with an electricity monitor called The Energy Detective.

After correcting the data for reductions in energy use that were weather-related, the researchers concluded that the homes with energy monitors had average electricity savings of 7.4%.

What Drives Your Business Dashboard Initiatives?

When it comes down to the very essence of the “Why Dashboards?” question, the answer really comes down to speed. There is a measurable need for faster and more digestible, more accessible information. I believe that this “need for speed” in understanding business metrics and trends is what drives the adoption of business dashboards.

Take a look at this chart from the Aberdeen Group on the Top Pressures Driving Dashboard Initiatives:

dashboard initiative

Want to see the full report? Go here (this link is a direct link to a pdf):

Aberdeen’s Executive Dashboards: The Key to Unlocking Double Digit Profit Growth.

From the paper:

Aberdeen’s latest research on dashboard usage validates the business adage that “you can’t manage what you don’t measure.” From the executive management team down to the line-level business managers and “front-line” employees, the research shows that employees of all levels and functions are deriving value from the business visibility that dashboard tools provide. By leveraging a strong set of process, knowledge, organizational, and performance measurement capabilities, Best-in-Class companies are employing both strategic and tactical dashboard solutions in order to drive double digit improvements in profitability and have achieved substantial increases in customer service and sales performance. This benchmark report is based on feedback from 285 organizations globally.

Using Executive Dashboards for Hospital Management

Those of us who have done dashboard projects in multiple industries know that the first step is to understand that vertical’s particular workflows, business rules and measures of performance. Finding out what KPIs to display on the enterprise dashboard for a particular type of business is no easy task unless you have first hand knowledge of that industry. Most consultants called in to implement a business dashboard don’t really know the industry first hand and must rely on the client’s subject matter experts. That is fine, but the dashboard vendor or consultant must do as much preparation in studying the client’s business as much as possible in advance. Yes, your particular expertise may be in dashboarding as a horizontal service, but as nice as your dashboard looks and works, it will not succeed if it doesn’t measure the right things.

For anyone involved in healthcare-related performance dashboards, here is a great primer on the workflows for a hospital. This is from a vendor named statcom and, while it is a sales and marketing tool, it does offer plenty of value-add and education for us dashboarders. It is offered as an interactive flash hospital workflow and patient flow simulator.  I’ve grabbed a couple of screenshots of the demo as well as the different executive dashboards involved:

Interactive hospital workflow demo 


Screenshots of a Brand Management Enterprise Dashboard

Executive Dashboard Topic: Measuring Brand Strength and Marketing Metrics with Dashboards

An executive dashboard can be a powerful force multiplier, especially when it comes to areas that, while traditionally reliant on trend analysis and data crunching, have not jumped on the idea of enterprise dashboards as quickly as other parts of the organization. Such is the case in the area of marketing, particularly brand management. (more…)

How Much Does a Business Dashboard Cost?

Dashboard Spy Topic: Costing Dashboards. There are many factors when it comes to determining the cost of a business dashboard project. Of course, one of the big determining factors is whether you build the dashboard yourself (a custom dashboard development project), integrate an off the shelf dashboard product from a dashboard software vendor, or some combination of the two (customizing an engine for example).

Take a look at the article titled “Cost of Dashboard Deployments“.

Here’s an excerpt:

Selecting a dashboard solution for your organization can be stressful or overwhelming. Many considerations have to be taken into account such as the purpose of the dashboard, how widely it will be deployed, what integration criteria is required, and important features and functionality. In addition to all of this, organizations compare these criteria based on pricing structures. Whether this means the initial cost of deployment, the implementation times associated with the solution, or what the solution will cost over time, identifying cost and how it affects the overall evaluation process cannot be overlooked.

This article looks at what cost considerations exist when looking at dashboard solutions and what realistic expectations are regarding dashboard solution costs. In addition, some dashboard vendors have provided averages of the cost of deployment for their products based on the number of users or various licensing fees/structures. These numbers (excluding names) will provide additional insight into what organizations should expect when implementing dashboard solutions.

tag: cost of dashboard deployment

Using Vision to Think

I had the opportunity to browse through the book Readings in Information Visualization: Using Vision to Think. I could not put it down. It’s a big book, hard to find and impossible to put down once you start looking at it. Use the link above to find a used copy. Take a look at this online version to see the kind of examples that enthralled me:

Online version (Google Books) of “Information Visualization: Using Vision to Think

information visualization using vision to think


Dashboard Recap – Recent Screenshots Sept 13 2007

It came to my attention that some Dashboard Spy readers forget that the dashboard screenshots appear under a different url: www.enterprise-dashboard.com, where I maintain Dashboard Examples Volume 1.  I apologize to the faithful reader who recently emailed me saying that while he enjoys the commentary and related materials that appear on this main site (dashboardspy.com), he wants to see more dashboard screens.

Well, in the spirit of helpful navigation, here is a recap of the recent dashboards that have been posted to Dashboard Examples Volume 1.

Hospital Bed Management Dashboard System with Excel Dashboard Download:

Excel Dashboard for Hospitals

In-flight Project Status Dashboard done in Xcelsius:

Xcelsius Project Dashboard

Security Operations Center Dashboard (a look at an upcoming product):

Security Dashboard

Marketing Campaign Dashboard in SharePoint

Sharepoint Marketing Dashboard

Business Objects Crystal Xcelsius Dashboard:

Xcelsius Dashboard

So go on over, bookmark the site, and you won’t miss anymore dashboards.