Microsoft Business Intelligence roundup

There have been a few key announcements in the Microsoft BI world recently, we’ve gathered them up and summarised below in case our readership have missed any of the key announcements.

SQL Server 2008 R2 – (CTP Summer 09)

“SQL Server 2008 R2 expands on the value delivered in SQL Server 2008 by providing a wealth of new features and capabilities that can benefit your entire organization. This release will further improve IT Efficiency with new and enhanced management capabilities and empower business users to access, integrate, analyze and share information using business intelligence tools they already know.” Read more here.

So what does this mean for you? In R2 there will be a number of new features from Gemini to Master Data Services, support for more than 64 processors to extended functionality in the Management Studio. We’re all looking forward to Gemini and the potential that has to offer – rest assured the XLCubed development team are working closely to ensure that the product is compatible straight out of the box. If you have any questions contact

Service Pack 1 for SQL Server 2008 Available  (April 09)

Microsoft announced the release of SP1 for SQL Server 2008 earlier this month, for many this marks the psychological point at which they’ll take interest in and investigate the product in depth. With a large uptake of the product already in the market place and the fastest OLAP engine we’ve seen from Microsoft, there is now no excuse not to evaluate upgrading or migrating to SQL Server.

Contact our services team for more information or how we can help you with SQL Server 2008.

SQL Server 2008 SP1

Service Pack 1 for SQL Server 2008 is now available for customers. The Service pack is available via download here and is primarily a roll-up of cumulative updates 1 to 3, quick fix engineering updates and minor fixes made in response to requests reported through the SQL Server community. While there are no new features in this service pack, customers running SQL Server 2008 should download and install SP1 to take advantage of the fixes which increase supportability and stability of SQL Server 2008.

Customers have no reason to wait to upgrade to SQL Server 2008 and many are already taking advantage of SQL Server 2008 as a smart IT investment. In fact, there have been over 3 million downloads of SQL Server 2008 since the RTM in August. With this Service pack, Microsoft is introducing 80% fewer changes to customer configurations compared to previous SQL Server Service Pack releases. This remarkable decrease is a testament to a revised product development process and updated servicing strategy that is focused on ease of deployment while keeping customer environments stable.

Microsoft BI Conference moves bi-annual

The MS BI conference last held in October 2008 in Seattle, WA has now been changed to an bi-annual event, citing  global economic constraints to travel budgets worldwide, Microsoft are moving the BI conference to a bi-annual event, with the next conference scheduled in Seattle on October, 2010. The next BI Conference scheduled for October 2009 will be moved to October 2010 in Seattle, WA, and all further BI Conferences will be held every second year on an ongoing basis. Content till then will be covered at the SQL Pass Summit, TDWI and SharePoint conferences.

If you were looking forward to seeing the XLCubed product team at the BI Conference this year, don’t worry you can still contact them at

SQL Server Fast Track Data Warehouse (Feb 09)

Microsoft announced SQL Server® Fast Track Data Warehouse, a new set of Reference Architectures for SQL Server 2008 that enables customers to accelerate their Data Warehouse deployments and reduce cost.  In addition, customers can further jump start their Data Warehouse design with new industry solution templates provided by System Integrators – Avanade, Hitachi Consulting, Cognizant and HP.

Seven new Reference Architectures with storage capacities from 4 to 32 TB were unveiled in partnership with HP, Dell and Bull.  Developed and tested by Microsoft, these architectures use balanced hardware optimized for Data Warehousing.  As a result customers will get

  • Better price performance than competitive solutions.  Fast Track Data Warehouse offers similar performance to the competition at 1/5th the price
  • Faster time to value and lower cost to setup and configure
  • Better performance out of box through pre-tested hardware. 


Customers can also choose the right Fast Track Data Warehouse with the right performance, storage capacity and pricing to suit their business needs.   Unlike Appliance Vendors with proprietary solutions, the new reference configurations use industry standard hardware from Dell, HP and Bull giving flexibility and cost savings to customers.

Fast Track Data Warehouse is available from today: customers will buy their SQL Server 2008 licenses through their preferred Microsoft Partner and the hardware from Dell, HP or Bull. If you’re looking to implement a data warehouse, contact the services team to see we can help.

Demise of Performance Point Planning (Jan 09)

It’s been a few months now since the announcement by Microsoft of the demise of Performance Point Planning, and the rebranding of the Monitoring and Analytics elements as PerformancePoint Services. This was an announcement back in January (09) that caught many by surprise, however for us its provided a useful segue into the new XLCubed Planning application. Many customers were waiting to see what was coming next, when PerformancePoint would be ready to compete with the likes of existing players with proven planning technology (i.e. in memory OLAP) and the  tempting announcements around Gemini certainly added confusion. Now looking back at the conversations we had in Seattle and Microsoft presentations perhaps the announcement isn’t as big a surprise as it felt at the time.

As above our long term commitment to an Excel front ended planning application continues, the demise of PerformancePoint Planning has simply increased the market for us and in many ways freed clients from the constraints of using purely Microsoft technology. Augmenting the Microsoft toolkit and providing our clients with the functionality they need to build effective planning, budgeting and forecasting applications remains at the forefront of our product set and services.

If you want to know more about our products and services (consulting team) just send an email to and someone in your region and market sector will get back to you straight away.

Augmenting the MS BI Stack

Here at XLCubed we’re often asked how the product sits in relation to the Microsoft Business Intelligence tools.
The answer is that we add to and augment the features and functionality that Microsoft has to offer. Excel is a fantastically powerful and flexible spreadsheet engine and this is exactly what it should be used for. However all too often, Excel is used as a database. With linked spreadsheets, and huge data extracts.

XLCubed have a number of products designed to take advantage of the functionality available with Microsoft Business Intelligence tools, these include XLCubed Excel edition, MicroCharts, and XLCubed

2009 Excel Dashboard Competition

We are pleased to announce the 2009 Excel Dashboard Competition:

The Competition

Like last year, the competition is for real world solutions, we are not providing a sample data set, and we’re looking forward to seeing some great examples of reports, charts and dashboards.

The dashboards are judged on the clarity and effectiveness of their design, particularly

  • Clean and clear organization
  • Effective table and chart design
  • A single-screen display, properly designed for the web, screen or print outs

We’ll also consider technical aspects of the dashboard, did it use effective  techniques for

  • The Dashboard layout
  • Data management, data logic and calculation : YTD figures, variances, etc….
  • Dashboard delivery: Sharing the dashboard via PDF, the web or as an Excel Workbook

There will be prizes for the top 3 entries, with the winner having first choice of prize from:

The Rules
We’ve kept the rules simple:

  • The solution must be in Excel 2000 or more recent, and not require additional software other than Excel and Chart Tamer , MicroCharts and XLCubed.
  • Entries can use any combination of tables, Excel charts,  bullet graphs and MicroCharts (sparklines). Each have their strengths and role to play in an effective dashboard
  • We will publish the top 3 dashboards on our website, so please ensure this is not problematic for any of your submissions.
  • Please change names and data as appropriate in the dashboards to protect the innocent.
  • Final Entries by 19 July 2009, Judges decision final!



XLCubed Video Library

We have begun to compile a series of videos to help all users of our products. We will keep adding instructional videos on a regular basis, though if you are a current customer and there is a particular element you would like to see let us know at These videos provide a highlighted quick introduction to the possibilities and functionality, to fully explore and understand the capabilities of the product set contact the team to arrange an evaluation or comprehensive training course.

XLCubed Videos:

Video 1 – Getting started with XLCubed In this video we show how to get started with XLCubed, creating a connection to you analysis cube and building a sample report.

Video 2 – XLCubed Grid Navigation in this video we show how to navigate around an XLCubed grid in Excel and utilise some more of the functions.

Video 3 – Introduction to formula reporting with XLCubed Excel edition in this video we show how to use the formula mode in XLCubed to query an analysis services cube.

Video 4 – Publishing a dashboard to the web using XLCubed in this video we show you how you can publish your XLCubed report/dashboard to the web using XLCubed web edition.

Video 5 – Ad-hoc reporting using XLCubed Web using the XLCubed web edition we demonstrate ad-hoc reporting against an analysis services cube in a thin client web browser.

Video 6 – XLCubed user defined calculations This report shows how you can use XLCubed Excel edition to create user defined calculations.

Video 7 – Extending an XLCubed report with Excel functionality The first brief introduction to extending an XLCubed report using Excel functionality. In this video we show how you can drive a report from an Excel range.

Video 8 – XLCubed Relational database support The first video using XLCubed to connect to a relational database, querying the dataset using SQL.

Video 9 – XLCubed Report Templates How to use XLCubed templates to provide a starting point for end users.

Video 10 – coming soon, using parameters in a URL to drive an XLCubed report

Video 11 – coming soon, visual grids – XLCubed grids with integrated MicroCharts


MicroCharts Videos:

Video MC1 – Introduction to MicroCharts – Sparklines An introduction to creating Sparklines with MicroCharts for Excel, in cell charts for your Excel spreadsheet or dashboard.

Video MC2 – Bullet Graphs in Information Dashboards  An overview of Bullet Graphs, and how to build them using MicroCharts in Excel

XLCubed V5

Yesterday we released version 5 of XLCubed. V5 offers continued enhancements across the product set including a completely new web interface, embedded pdf printing, and further extension of our interactive ‘Visual Grids’ to include all MicroChart chart types.













Version 5 is joined by a new Website which now includes a series of ‘how do I’ youtube videos, available on the individual product pages or at the XLCubed youtube channel. The content will be expanded across additional areas in the coming months, and we hope will become a key resource for customers and evaluators alike.

The basic strucure of a cube

This week we’ll take a look at the basic structure of a cube from an end user perspective, as opposed to the architectural underpinnings. This is intended as a high level overview, and for brevity contains some generalizations, and focuses on Microsoft Analysis Services cubes.

An OLAP cube consists of several key elements, the most fundamental of which are dimensions and measures

1) Dimensions.

Dimensions are the business elements by which the data can be queried. They can be thought of as the ‘by’ part of reporting. For example “I want to see sales by region, by product by time”. In this case region, product and time would be three dimensions within the cube, and sales would be a measure, below. A cube based environment allows the user to easily navigate and choose elements or combinations of elements within the dimensional structure.

2) Measures

Measures are the units of numerical interest, the values being reported on. Typical examples would be unit sales, sales value and cost.

Note that there are modelling techniques which develop cubes with only one pseudo measure, typically called ‘value’ or similar, and implement what the user would think of as the measures through a dimension. There are performance and navigational reasons which can make this a good approach, but not one we’ll cover here in our introduction.

The diagram below shows a very simple cube which we’ll use for discussion.











This particular cube is for an exports business, and consists of 3 dimensions, Source, Route, and Time. The two measures are Packages, being the number of packages shipped, and Last, being the last shipped date.

Very few real world cubes will have just three dimensions, but I’ve yet to learn how to draw a 12 dimensional cube! The diagram above is enough to illustrate the fundamental principle, that at every intersection of the different dimensions, are stored the value for each of the measures. In larger real world cubes the principle is the same, just the numbers of intersections is larger.

The diagram highlights a few additional features of dimensions which need to be understood.

  • Hierarchies

A dimension can contain one or more hierarchies. Hierarchies are really navigation or drill paths through the dimension. They are structured like a family tree, and use some of the same naming conventions (children / parent / descendant). Hierarchies are what brings much of the power to OLAP reporting, because they allow the user to easily select data at different granularity (day / month / year), and to drill down through data to additional levels of detail.

    • Hierarchies consist of different levels. For example a time dimension would typically have a year, a month and a day level. A customer hierarchy may consist of Country, State, City, and Name levels.
    • The levels are either implied in the case of dates, or exist as ‘attributes’ in the source data. So for example customer number 12324, John Brown, would have additional information recorded such as his address, broken into house number & street, city, state, country. Each of these is an attribute.
    • Hierarchies are really ordered navigation paths through the attributes
    • In Analysis Services 2005, the user’s view of a dimension will typically consist of both the defined Hierarchies, and also the Attributes. Attributes are ‘flat’, i.e. contain no ordered drill path.


  • Members

A member is any single element within a hierarchy. For example in a standard Time hierarchy, 1st January 2008 would be a member, as would 20th February 2008. However January 2008, or 2008 itself could also be members. The latter two would be aggregations of the days which belong to them. Members can be physical or calculated. Calculated members mean that common business calculations and metrics can be encapsulated into the cube, and are available for easy selection by the user, for example in the simplest case Profit = Sales – Cost

  • Aggregation

Aggregation is a key part of the speed of cube based reporting. The reason why a cube can be very fast when for example selecting data for an entire year, is because it has already calculated the answer. Whereas a typical relational database would potentially sum millions of day level records on the fly to get an annual total, Analysis Services cubes calculate these aggregations during the cube build and hence a well designed cube can return the answer quickly.

Sum is the most common aggregation method, but it’s also possible to use average, max etc. For example, if storing dates as measures it makes no sense to sum them.

The cube introduces a number of dimensions, hierarchies and measures, modeling the business of interest, and all of which are available to the end user to quickly and easily select, drill, and slice and dice. With a well designed cube the user benefits from a reporting environment which is highly flexible, contains the pre-calculated business metrics they regularly use, and is fast in terms of data retrieval.

So, what’s an OLAP Cube, anyway?

As "An Excel User in a Cubed Kingdom" I’m starting my exploration of this new found land with a simple question: what is an OLAP cube? In plain English, please…

I like this simple, non-technical definition: a cube is a set of predefined answers. It’s up to you to select the right questions.

OK, let’s detail this.

Imagine that you have a very large database with the usual business data: orders, customers, sales representatives… Now you want to know how much a customer category ordered over the last year. You query the database and you get the answer. Then you want monthly sales. Query it again. Dig a little deeper to see what products that category ordered. Query it once more.

What is happening behind the curtain? Each time you enter a new query the system looks at each transaction (or a subset) and performs the necessary calculations to answer your query. You’ll get your answers, but it will be painfully slow: depending on your query and the database size, it may take hours. It is not an option.

But you don’t really need to see each individual order, do you? If you only need to know monthly sales, why should your system go through each transaction? If you pre-aggregate that data, you’ll get your answers much, much, faster, because there aren’t five million records, just 100.000. You’ll be able to actually work, instead of staring at your monitor, waiting for an answer.

This is what a cube does. It provides faster answers by eliminating the unnecessary detail for the task at hand. You shouldn’t look at a cube as an unique, condensed version of the database. While you have a virtually infinite number of questions that the database can answer, a cube focus on providing answers to a small set of questions. That’s why you can have different cubes (marketing, fin, sales), all of them getting data from the same source. They all answer different sets of questions.

When designing a cube (it’s your job, not IT’s), resist the temptation of a one-size-fits-all cube. Clearly define a coherent set of questions for your fundamental business needs and make sure they are answered once the cube becomes available.

If you are exploring your data you will not want to wait one hour each time you make a change. On the other end, a fast cube with no data to explore is useless. There is  a fine balance between maximum flexibility and maximum performance.

An OLAP cube not only ensures that you retrieve the right data from the database but also allows you to explore it efficiently. Two good reasons to add OLAP cubes to your toolbox.

Next time we’ll see how plain English can describe the structure of a basic OLAP cube.

An Excel User in a Cubed Kingdom

I’ve been using Excel for my entire professional career, most of the time in large corporations where adding a piece of software to the standard IT structure would be some kind of heresy. When I have a business need that can be solve by an out-of-the-box Excel installation that’s the path I follow (I’m also a power user of the company’s formal BI tool, so I know where to draw the line).

Over time, I’ve developed a framework that helps me to solve problems from a very specific point of view: how to minimize file size, how to minimize calculation time, how to deploy, how to update, and so on. This is the logic that you often must follow, and you tend to believe it is the best one. At that point you must start a conversation (a very fashionable word nowadays..) with someone that doesn’t share that logic.

Take OLAP cubes, for example. I never use them. I use the corporate BI tool or I create a 100% pure Excel application. But then Andreas told me about XLCubed and how you could deploy online your always-updated-file. That was a turning point because for me those are two killer features that I’ve been yearning for a long time.

I started to play with the tool, but still using the same logic. And was plain wrong. Sure I could use all my Excel background, but I needed to adjust it to a different logic. Using an OLAP cube you get a new set of functions that simplifies much of your work and you need to reevaluate some Excel functions because some of them will perform better under this new environment. You’ll leverage your Excel background to create a a new logic at a higher level.

I’m an experienced Excel user, probably like you, and this is just Excel on steroids. I’m leaving my comfort zone, one foot at a time, and I’ll document and share with you my learning curve. So come with me to the Cubed Kingdom and we’ll walk through this together.

First stop, next post: what is a cube, anyway?

Gemini – Smarter Excel Dashboards with End-User BI

I attended beginning this week the Microsoft BI Conference in Seattle where Microsoft presented an interesting new product that might change the way you build your Excel dashboards in the future: “Gemini”, an End-User Business Intelligence plug in for Excel.

End-User Business Intelligence has been around for quite a while with some of the most prominent Applix TM/1 , PALO, PowerOLAP and MIS Alea. These tools where tailored for non-IT proficient Excel users to allow them to build real Business Intelligence solutions with Excel, without leaving Excel or having to learn complicated BI or Data Warehousing techniques. All you have to learn is that a cube is like a very powerful multidimensional spreadsheet that can easily aggregate and hold large amounts of data. To pull out data from the cube you use a formula like this…

=LookupCube(“Sales”, “Units”, “Hats”, “Store 19”, “Oct-2007”)

…returns unit sales information consolidated by product line and region for the month shown. And this formula…

= LookupCube(“Sales”, “Sales USD”, “Hats”, “Store 19”, “Oct-2007 YTD”)

…returns year-to-date sales in US dollars for the same product line and region

Continue reading “Gemini – Smarter Excel Dashboards with End-User BI”

(de)Faults in Excel Charting

I recently spoke at SQLBitsIII, and an aspect which went down well was a simple overview on how to make the most important aspect of a graph, namely the underlying data, the prime focus and clear and easy to read. I also had the opportunity to attend Stephen Few’s Information Visualisation Workshops in London, which I’d thoroughly recommend. Stephen also spent some time, as part of a much more detailed overall agenda, on how a typical default chart can be morphed into an effective display.

So it’s back to basics this week, and how to improve the standard, out of the box Excel chart. Unfortunately, despite it’s pervasiveness, the default chart settings, which many users will never stray from, are in the case of Office 2007 not ideal, and in earlier versions, pretty awful. In this piece I’ll outline a few simple steps which can turn the default visual delights of the Excel graph into something you need not be embarrassed to put on the projector.

I’m using ‘classic’ Excel as my start point, because it’s still the incumbent in most organisations (and also because it’s worse). The example is for column charts, but the majority of the tips are valid for any chart type. As our start point we have the unit sales data for 3 products across 6 countries, as a default Excel Column chart, below.







Nice. It’s wrong in a lot of ways, but how many hundred times have you seen this or a version of this? It’s well trodden ground if you have read Tufte, Few at al, but the key recommendations to improve things are surprisingly simple, and quick to implement.

1) Remove the Clutter and noise

The purpose of the chart is to display the data of interest clearly and concisely. It’s not to distract the user with pretty shading or 3D effects etc. Although the default chart is no-frills, there are a number of items which are adding nothing, or have undue prominence, and in doing so detract from the overall goal.

  • The Plot Area
    • The grey background to the plot area adds nothing, so we remove it
    • The border on the plot area – remove it also (numerous studies have shown we only need two axes to effectively group and visualise data)
  • Gridlines
    • The default gridlines are black, too visually intense. They are there for reference when required, not the prime focus, so are best muted – set them to a light grey.

2) Axes and Legend

The axes frame the chart, and are a key point of reference; however they should not draw the focus from the chart itself. As with the gridlines, they should be toned down.

  • Change the default black font colour to charcoal / dark grey
  • Change the default axis colour from black to charcoal / dark grey
  • Typically reduce the font size to 8

Rules for the legend are similar to those for the axis

  • Change font from black to grey
  • Remove border or change it’s colour to very light grey
  • Typically reduce font size to 8
  • For a clustered column, my preference is for the Legend positioned at the bottom, and reading across in the display order of the columns.

3) Columns and colour

The black column borders add nothing, and as such should be removed, they are another form of Tufte’s ‘non-data ink’.

On to colour, and unfortunately Excel’s default chart fills are heavily saturated plum and wine with a light cream..  So I’d strongly suggest changing the chart colour palette. For column charts, there is typically a reasonable block of colour for each series, so the colour scheme shouldn’t be too bold, or it becomes an eyesore. You should aim for mid-intensity colours of similar saturation (unless one is intended to stand out), pastels tend to work well.


All the steps above are simple and fairly fast to action, with one exception, the colour scheme. Unless you already have pre-prepared palettes it’s possible to spend an age trying to get the ideal combination – remember the 80/20 rule!








In my example above, which hopefully you’ll agree is an improvement, I’ve used the colour palette from our upcoming ‘Chart Tamer’ product. Chart Tamer is a lot more than just a colour palette, but that aspect has benefited from minds with much more expertise in colour than mine, and I’ll go with their choice over mine every time!

Household Income Distribution 1967 – 2005 As Small Multiples Chart

In my last post I tied to fix an overloaded line chart Jorge presented in a recent post about loss aversion:

Jorge asked "does it make any sense to add those nine series to a single chart?
My attempt to fix the chart by using some color coding, has its shortcomings that caused quite some discussion.


So again, how can you give the users all the data they expect while keeping the chart clean and readable?

D Kelly O’Day pointed out "More data or better colors won’t help a poor chart type selection" and presented a dot plot


Lets try to select the right chart type. In Chart Rules, As Simple as Possible, But Not Any Simpler! I presented an easy to learn set of rules to determine the best chart type .

1. Determine the relationship you want to display

In our case a we have a Distribution Relationship, we want to show the Distribution of the Income Levels

2. Determine if you want to emphasize individual values or the overall pattern and

emphasize individual values or the overall pattern  and Determine the chart type

As we want to emphasize individual values a column chart works best.


This chart already gives us a good feel for the income distribution in 1967- Looks like a almost perfect bell distribution with a belly for the mid income levels. But how did things change from 1967 to 2005? Lets create a set of small multiples to show the situation in 1967, 2005 and the increase from 1967 to 2005.