Is Excel 2013 Power View hurting SSAS?

Since the release of Office 2013 Preview, there has been an awful lot of commentary both in the BI blogosphere and in all media mostly praising the new look and features Microsoft are giving us. I agree that the new suite is getting a much needed face-lift (after all 2007 -> 2010 did not deliver much in this regard), and the mandatory for post-2010 releases “touch-enabled” interface. As a BI guy, the most interesting thing for me in the new Office is Excel, and within Excel, Power View (not completely ignoring the new Pivot Table options like adding new calcs natively, without using the OLAP PivotTable Extensions plug-in).

There are deficiencies in Power View as it stands now (maybe that is why it is an add-in, not enabled by default) but it is a nice data exploration tool which even Stephen Few told me was going in the right direction. It still doesn’t allow us to do things like Top 10, % of Total, etc. which I love in Excel Pivot Tables, but I am hoping that we’ll get all these and more at some point. Together with maps, small multiples and highlighting, Power View does seem like a potential killer application, making other tools like Tableau seem within reach.

However (here we go), in my opinion it has one drawback which really spoils the good things: it doesn’t work on SSAS Multidimensional cubes. I am sure that if someone from the Microsoft SSAS or SSRS teams reads this they wouldn’t be surprised. After all they have heard this numerous times since the release of Power View (even when it was Crescent). Chris Webb wrote about it and caused some stir, it was discussed and the issue was parked as “will be fixed at some point”. I don’t want to resurrect it in its previous format since we now know that a fix is coming (as publicly stated by Microsoft at PASS and other forums), but I would like to point out that last week after demoing Excel 2013 in front of some decision-makers in the organisation I currently work at, I had to answer questions like “So, we can’t use Excel with SSAS at all?” and “Do we have to upgrade all our cubes to in-memory models now?”. This made me think: we can’t cover the whole feature set of SSAS MD in SSAS Tabular, yet we can’t use the new end-user tools on SSAS MD as well. Basically, I am left with one option only – to recommend SSAS MD and Excel Pivot Tables as the only strategy for corporate BI competitive with other vendors like IBM and SAP. Furthermore, since we can’t use DirectQuery on Teradata, I can’t also say that SSAS Tabular is better than SSAS MD as an analytical platform on top of Teradata. Yes, one day when we get it working it will trump all other options, and the Teradata guys I work with are genuinely excited about the future, but it is unfortunately the future, while other options are there now. And the future in IT always looks bright, while the present is a different story altogether.

So, is Excel 2013 Power View hurting SSAS MD? Maybe not directly, but the more we promote Power View (hint: as it’s getting deployed on every workstation we hardly need to promote it), the more we also highlight the deficiencies in SSAS Tabular and make SSAS MD look like the neglected sibling. As for my demonstrations of Microsoft BI, I would probably treat the two components of SSAS – MD and Tabular as two separate stacks. MD – the best out there for corporate BI, and Tabular – the best for team/personal BI, not to be mixed together. To those who can’t wait to get their hands on a powerful, modern analytical tool working on the iPad and use it on a powerful, robust, widely used analytical engine which works on less than terabytes of memory (let me spell it out: Power View +mobile on SSAS MD), we have to tell: “at some point in the future, hopefully”.

PS: Yes, it does sound like yet another whiny post, but we have been waiting for a very long time (in IT-terms) since the inception of Crescent to get this bloody issue fixed and it is hard to imagine mighty Microsoft struggling to catch up behind Cognos (yes, they have mobile – terrible implementation, but works), Tableau (last time I checked they were a tiny company with overpriced products) and QlikView (ugly and slow from what I’ve seen). It’s not like Microsoft weren’t aware of this, and it’s not like it’s happening for the first time – remember how well PerformancePoint did being unable to connect to anything but SSAS MD and having a lacking feature set, plus SSRS to SSAS integration has always been a pain point. The new, quicker, release cycle at Microsoft seems to be delivering the same amount of features in less-complete releases, which doesn’t seem to help with inspiring confidence in its vision and ability to execute. However great these new ingredients are, the dish doesn’t taste that well when some are lacking…tends to get cold quickly too.

Stacked Charts: A Little Swiss Army Knife of SSRS

I am a fan of Jon Peltier for his work around Excel visualisation. It is incredible how many ways we can tweak Excel charts to get great results – out of the box, with a little bit of development effort. In terms of flexibility and power, SSRS is very similar to Excel and two of its charting components – the Stacked Column chart and the Stacked Bar chart allow us to create some interesting non-default charts.

Let’s see an example of a typical stacked graph based on a simple T-SQL dataset:

SELECT ‘A’ AS description, 0 AS fPoint, 100 AS sPoint
UNION
SELECT ‘B’ AS description, 50 AS fPoint, 200 AS sPoint

In the designer:

And when rendered:

This is a typical bar chart and there is nothing new here. The interesting part starts when we change the colour of the fPoint to No Color. The same graph, rendered starts looking like something else:

Hmm, it looks like…a Gantt Chart! If we were to replace A and B with task names and the X axis with date/time values we have the opportunity to create a chart somewhat resembling a real Gantt chart. In the data set we must observe the rule that fPoint shows time when the task has begun and sPoint – its duration. For a detailed walk-through you can check out this article on SQL Server Central.

But the post does not finish here. With the same technique (different data set) we can create the following, as well:

A pyramid chart! The data set has its own set of rules, which are different to the Gantt chart. The data set used for this example was:

SELECT ‘A’ AS description, 5 AS fPoint, 1 AS sPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 5 AS sPoint
UNION
SELECT ‘C’ AS description, 2 AS fPoint, 7 AS sPoint
UNION
SELECT ‘D’ AS description, 0 AS fPoint, 11 AS sPoint

The easiest way to show how to build the data set is to imagine we have a third series – tPoint:

SELECT ‘A’ AS description, 5 AS fPoint, 1 AS sPoint, 5 AS tPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 5 AS sPoint, 3 AS tPoint
UNION
SELECT ‘C’ AS description, 2 AS fPoint, 7 AS sPoint, 2 AS tPoint
UNION
SELECT ‘D’ AS description, 0 AS fPoint, 11 AS sPoint, 0 AS tPoint

The sum of fPoint, sPoint and tPoint should always be the same and equal to the base of the pyramid and to maintain the symmetry, we want fPoint and tPoint to be the same. In general, the formula for this is:

fPoint = (Max(sPoint) – sPoint) / 2

We have to guard against 0/2 as we will always have one case (for the Max(sPoint)) in the data set where this equals 0.

And one last example of the power of the stacked charts in SSRS:

Here the Stacked Bar is changed to Stacked Column chart type in SSRS and the data set used is:

SELECT ‘A’ AS description, 5 AS fPoint, 3 AS sPoint, 0.1 AS tPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 8 AS sPoint, 0.1 AS tPoint
UNION
SELECT ‘C’ AS description, 2 AS fPoint, 1 AS sPoint, 0.1 AS tPoint
UNION
SELECT ‘D’ AS description, 1 AS fPoint, 6 AS sPoint, 0.1 AS tPoint

The tPoint is the marker for our targets and we can adjust the thickness by altering our data set. One limitation is that we cannot have targets lower than the top of the actual (i.e. the tPoint will always appear above, and not in the middle of, the fPoint bar), but this could be useful if we know that tPoint must appear above or just on top of fPoint (e.g. sPoint = 0 will give us a marker capping the bar). We can change this by adding one more series – topPoint, which will be 0 when we are below, or on target and will show the amount above target when we have some:

SELECT ‘A’ AS description, 5 AS fPoint, 3 AS sPoint, 0.1 AS tPoint, 0 AS topPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 8 AS sPoint, 0.1 AS tPoint, 0 AS topPoint
UNION
SELECT ‘C’ AS description, 1 AS fPoint, 0 AS sPoint, 0.1 AS tPoint, 1 AS topPoint
UNION
SELECT ‘D’ AS description, 1 AS fPoint, 6 AS sPoint, 0.1 AS tPoint, 0 AS topPoint

Then we can add it to the chart and colour the series in the same colour as the fPoint series:

When we render this we get:

There we go, the limitation has been lifted.

Note that for each different variation of the charts we are using customised data sets. The data transformations needed may not be trivial in some cases, but for most purposes they will be very simple and easy to work with in T-SQL; and because we keep this logic in T-SQL and just feed it ready for plotting to SSRS these charts render fast – a good practice worth re-iterating here.

PivotViewer meets World Cup 2010

Just got this link in my inbox and thought the BI community may be interested (especially if you live in Germany, Spain, Netherlands or Uruguay – the semi-finalists of this year’s Fifa World Cup) in this demo of thenew PivotViewer SilverLight control:

http://pivot.metia.com/worldcup/

Even if you are not a soccer fan, you will surely still find it very interesting.

The official site where you can download the control is here: http://www.silverlight.net/learn/pivotviewer/

Data Mystification Techniques

I am a fan of studies in Data Visualisation. It is a creative and dynamic field with a lot of room for experiment. I am considering report and dashboard design, and within this frame Data Visualisation, as a form of practical art. Well designed and built reports are critical for solution adoption and usability. However, in this post I will concentrate on exactly the opposite topic – intentionally mystifying reports, obscuring the data and making it hard, for the report consumers to reach informed conclusions. I will also show how we can make the data visualisations as misleading as possible. This post is not as abstract as one may think, as it draws its examples from a very real project, for which I had to build a report under heavy pressure.

Initially, I was asked to build a report based on a small data set (~450 rows) stored in an Excel workbook. The data was perfectly suitable for building a Pivot Table on top of it, so I did so and then I decided to use the pivot table as a source for my report. The users have one measure – Spending Amount and a few dimensions – Category and Focus for that spending. The request came in the form: “We want to see the Amount by Category and Focus in both graphical and numeric form“. So, I sat down and produced this prototype (with their colour theme):

As you can see from the screenshot, the report is fairly simple – the bar graphs on the top clearly show how the Amount is distributed per Category and Focus. Also, because of an explicit request, I build the bar graph on the second row to show category and focus amounts combined, and in order to clarify the whole picture, I added the table at the bottom of the report. The report works for colour-blind people too, as the details for the Focus per Category Expenditure bar graph are shown directly below in the data table.

I sent the report prototype for review, and the response was:

1. Remove the table.
2. Change the bar graphs to make them more “flat”. Meaning: there is too much difference between the bars.

The reasoning – it is “too obvious” that the data is unevenly distributed. As the report is supposed to be presented to higher level management, discrepancies in the amount allocation would “look bad” on the people who requested me to create the report at the first place.

Adding more fake data got rejected, so I was advised to prepare a new prototype report with the new requirements. It follows:

Now, Stephen Few would spew if presented with such a report. I did everything I could to obscure the report – added 3D effects, presented the amount in 100% stacked graphs when absolutely not necessary and, of course, I had to add a Pie Chart. A 3D Pie Chart. The whole report is totally useless. It is impossible to deduct the actual numbers of the various category/focus amounts. Furthermore, the Pie Chart combines the focus and category and uses virtually indistinguishable colours for the different slices. The 3D effect distorts the proportions of these slices and if printed in Black and White, or if viewed by a colour-blind person, the report looks like this:

Since my goal of total mystification of the report was achieved, I sent the second prototype back.
The response was: “It looks much better, and we really like the top right bar graph and the Pie Chart. Would it be possible to leave just those two and change the Pie Chart to show data broken down just by Category?”

So, the users did not like my combined series. A point for them. Then I decided to remove the 3D cone graph, to remove all 3D effects, to make it more readable and to create the following 3rd prototype:

Here, we can see that the pie has the actual percentages displayed, and each category amount can be calculated from there. The stacked bar graph is again almost useless.

The users liked it, but still thought that it was too revealing, and were particularly concerned with the fact that there are a couple of “invisible” categories (the ones with small percentages) on the Pie Chart. They had a new suggestion – change the pie chart back to a bar graph and play with the format, so that even the smallest amount is visible. I offered an option, made another prototype and it finally got approved. The exact words were: “The graphs are exactly what we want“. There it is:

The Y Axis in the first graph is interesting. Not only that there is a “scale break”, but in fact the scale is totally useless, as it is manually adjusted, because of the big gaps between the amounts. I needed two scale breaks, which for a series with 6 values is a bit too much. You can compare the normal linear scale of the first Prototype I created and this one. However, it hit the goal – my users were satisfied.

I consider this effort to be contrary to be an exercise in “Data Mystification”. It is very easy to draw the wrong conclusions from the report, and it achieves the opposite to the goals of Business Intelligence, as instead of empowering users, it could actually confuse them and lead them to making wrong decisions.

Developing reports for colour-blind people

According to Wikipedia 8% of all males and 0.4% of all females in Australia are colour-blind to some degree. The percentages are slightly different in the USA – 7% for males and 0.4% for females. It is estimated that these would be similar to other countries in the world, which means that a very high percentage of people may have difficulties distinguishing colours. Therefore, a significantly large part of our potential report users may not be able to interpret some of our graphs and charts correctly (There would be around 400 colour-blind males and 20 colour-blind females in an organisation which employs 10000 people).

In example, the following chart is quite nice and simple but can be useless to colour-blind people:

versus:

Similarly, this table is somewhat confusing if we remove the colours:

versus:

We have to be very careful with design of KPIs and dashboards in general:

versus:

As we can clearly see from the above examples, not being able to clearly distinguish colours makes our poorly designed charts and tables confusing.

We should always keep in mind the following considerations when designing and implementing our most-common report items to ensure that they can be used by everyone in our client organisation:

• KPI indicators must be different in shape rather than just colour
• Line charts should utilise markers with different shapes
• Bar graphs should include a marker on the top of each bar
• Avoid colour-coded pie-charts – they can be extremely difficult to read for a person with even the slightest colour-blindness condition
• Avoid colour-coding tables – either backgrounds or text colours are usually unacceptable

Other more general suggestions:

• Shapes are much more important than colours
• Greyscale and shades of the same colour are acceptable, as a colour-blind person can distinguish between those

Of course, even after all our efforts to create reports readable by everyone, we may miss some detail. The best way to ensure that we have done a good job is to test. There are two good and easy ways to do that:

1. Printing – print the report in black and white and see if all information is well presented
2. Changing display to greyscale – Windows lets us choose how many colours we want to display on our screen. Choosing greyscale and then playing with our report is possibly the best way to ensure that colour-blind people can use our reports.

It is fairly simple and easy to always apply these design principles when creating reports. I have found that most organisations are quite happy to include minor tweaks to their dashboards and reports when they understand how important they could be for some of their employees. Furthermore, it helps to promote accessibility to technology regardless of minor disabilities and gender.