Archive for the ‘Metrics’ category

Charts and More Charts

August 20th, 2009

So what on earth have I being doing for the last 10 days?  Neglecting this blog I know.  But with good reason.  We’re putting the finishing touches to our Unit Monitoring BI outputs which I think are pretty cool.  I hope that as an institution we really begin to benefit from this type of resource and that academics find it informative and useful.

We will shortly be publishing data for just about every unit of study at our institution.  The data is based on 20 measures ranging from Enrolment and Load through to Unit Evaluation.  The following is an example of part of the reporting that will be available to academic staff to better understand what has happened and is happening in their unit.


The above time-series charts supplement a much deeper ‘current period’ perspective which provides traffic light style reporting and tabular presentation of measures.  The double page spread for each unit contains 551 data items.  With 783 units being reported, that amounts to over 431,000 separate pieces of information available twice a year.  That is a lot of information to present on 2 sides of a single sheet of paper but I think we’ve managed to make it consumable.

We shall see what the reaction is, as ever we will be receptive to suggestions for improvement, particularly with respect to the presentation, layout and format of the data.

Metric Distribution Analysis

July 6th, 2009

nysi_coverBeing a bit of a Stephen Few fan, I’ve now absorbed his latest book, Now You See It: Simple Visualisation Techniques for Quantitative Analysis.

Unlike everyone else (yes everyone) who has reviewed it on so far, I don’t actually think it deserves a full 5-Star rating on all fronts (for instance, the reproduction of Tableau charts and some other outputs is low in terms of image quality, which for a visualisation publication surely is a problem).  That aside however, it does have some excellent content and one particular device which I’ve latched onto is contained in the chapter on Distribution Analysis.

Representing a metric and providing information on the value relative to the entire distribution is having your cake and eating it.  Not only do you get the measure, but you see where it sits relative to its peers so I see this as a very complimentary graphic alongside the more traditional metric traffic light and trend presentation.

So what could a metric distribution analysis look like?

Few uses an example where you have the Low, Median and High values of a particular measure displayed on a single axis, he then improves the example by adding a marker for the 25th and 75th percentiles.

This then starts to sound very similar to the tertile distribution (where we chop the data up into thirds) we have been working on for presenting unit and course metrics at UNE.  We have calculated a lower, mid and upper tertile with boundaries at 33.34% and 66.67%.  Each measure for each unit and course is then calculated and given the appropriate traffic light depending on which tertile it falls into.

What we hadn’t thought of was being able to supplement the traffic light, trend and value with a distribution chart.  So here’s how it currently looks in development for a sample unit for our Attrition measure based on Few’s ideas:


  • Attrition is less than 10% for this unit (green star)
  • The lowest attrition of any unit is just under 5% (left red triangle)
  • The 33rd percentile is at around 14% attrition (blue vertical bar)
  • The 66th percentile is at around 22% attrition (light brown vertical bar)
  • The highest attrition of any unit is around 31% (right red triangle)
  • This unit is performing very well and attrition is significantly low

In combination with the traffic light, trend and time-series line chart, there is a huge amount of information being conveyed by a very simple instrument.  Should I add values to this?  Well maybe.  I tried and it got really cluttered and of course the traffic light scorecard itself will have the values so maybe this graphic is fine just as it is when used in conjunction with the other devices.

Metric Epiphany

April 17th, 2009

Thanks for the email feedback and advice about metric star schema designs in response to my last post.  Some very useful suggestions from Griffith got me thinking about storing the actuals as numerator and denominator for percentage measures.

The problem I had was with roll-ups of differing metric ‘currencies’ – percentage v absolute values being handled by the same model.  It is easy to aggregate EFTSL actuals on the fly but percentages won’t roll up unless you have the actuals and can recalculate them at the higher level as suggested by Griffith.  The problem with this is that the model allocates a status to the measure row which just became dynamic and is now only known at reporting time.

lightbulbIt then struck me that there are only two types of behaviour we need to worry about:

  • Percentage
  • Absolute

The absolute values encompass things like enrolments, load, income, headcounts etc, they all roll up from an atomic grain very well and the percentage actual elements all roll up too but their targets need to be averaged.

So we need pairs of measure types in the fact table – a target and actual for absolute values and a target and actual for percentage values.

In the roll-up construction we specific sum(x) for the absolute values and avg(x) for the percentage ones and then in the reporting we determine which measures to display based on the properties of the metric being displayed.

At least that is the theory, lets see what happens…

Metric Facts

April 14th, 2009

Is anyone out there using an aggregated fact table for managing KPI or organisational metric performance?  I’m purely talking about the star schema design here to enable high-level reporting on data that already exists in the warehouse at an atomic level, things like:

  • EFTSL by School or Faculty
  • Student Satisfaction
  • Unit Enrolments

Having tried (and failed) to use Cognos Metric Store for this purpose, I think the most flexible and  best performing results could be obtained from something like the following.  I stress this is not in production, or even built yet, but does it ring any bells with anyone out there?  Please comment if it does.


I won’t get into product specifics here as I am interested in the design rather than the capabilities of specific products although I realise the product does at least influence the design to a certain extent.

There are two potential issues that I recognise with the above at present:

  • The grain of the fact varies according to the metric – some are annual, some semester-based
  • The dimensions are not all relevant for all metrics – we would need some N/A rows in the dim