Excel 2007 Pivot Tables Whitepaper

via David Gainer's Excel 2007 blog:

The Excel team have released a new whitepaper which is a useful summary on how and where SSAS cube meta data can be organised in order to be displayed/accessed effectively through Excel 2007 PivotTables. For many SSAS developers there will be nothing new here but it's great to have this information handy in the one document. Anyone writing up SSAS best practices documentation for their organisation will find it particularly useful.

The whitepaper (both in .doc and .docx formats) can be downloaded here.

PPS / BSM - Sparklines in Scorecards

A while back I found out about MicroCharts, an innovative product that enables the creation of efficient, simple data visualisations using fonts. When I first heard about this I wondered if it would be possible to add a new spin on BSM KPIs by incorporating sparklines, a data visualisation concept created by Edward R Tufte, into their definition. I immediately tried to get a sparkline into a BSM scorecard with no luck. Obviously I didn't try hard enough because I tried it again today and, as you can see from the screenshot below, I was successful.














BSM only gives us the ability to stipulate one global font setting for the entire data grid of the scorecard via the cell options pane of a scorecard view. As a result the numbers displayed in the Actual and Budget columns don't look the best as a result of having the Micro Line Charts font applied to them. There's also a little extra space to the right of the sparkline (maybe another tweak or two would get rid of this). Nonetheless, you can see a great little sparkline which gives scorecard users a much better insight into their sales revenue KPI numbers over time, in this case a 24-month rolling window. At a glance you can see that Germany's revenue was flat for quite a time and then jumped considerably, whereas the USA's revenue, while high in comparison, has been quite erratic over time. France, just did business as usual for quite a while, had one monster month and then dropped back to where they were.

UPDATE: the Microcharts guys got in contact to tell me that my original attempt at sparklines alongside numbers in BSM would not have worked no matter how hard I had tried. Apparently the 1.0.1 version of the Microcharts fonts did not handle numbers alongside the graphical elements of the font that make up the charts. The 1.1 (current) version does, hence the success I experienced this time over the last. While the numbers don't look the best at present the MC developers are going to ensure the next version of the fonts will display numbers properly. So in v-next when using the BSM scorecard cell global font setting you will probably see Arial-style numbers (or some other standard font) alongside the sparklines.

I have also been playing with the CTP1 release of PerformancePoint (we also did a bit of research on PPS for the upcoming BSM book in which we have a small chapter). Below is a screenshot of what a similar sparklined scorecard (slightly different numbers) looks like in PPS builder. One of the notable features here is that we can stipulate specific font settings for each column in the scorecard, no need for a global data grid font like BSM. This way I can use the appropriate MicroCharts font only in the sparkline column and a regular font for the others. The extra space that BSM displays is also gone :)

Outside of the sparkline stuff I love the fact that I was able to create and preview the scorecard in PP Builder alone, no deployment needed. Aside from many other things (about which I will blog more in the coming months), the ability to preview whatever you build without having to deploy is a great (and long overdue) feature. The screenshot below shows the PP Builder environment in its entirety with the sparkline scorecard preview.
















If you're interested in more of data visualisation side of things check out Tufte's book Beautiful Evidence (which I hear is good and plan on reading soon) and Stephen Few's excellent Information Dashboard Design. Tufte came up with the sparkline and Few invented the bullet graph as a more effiective means of communicating the status of a KPI. Few also maintains an interesting blog.

As for MicroCharts, I think this product dovetails beautifully with one of Microsoft's key BI strategies of data delivery using Excel while also embracing Few & Tufte's ideas on simple, efficient communication of data without the excess visual "fluff". Because their visualisations are font-based the ability to surface many of them in other places like SSRS and other reporting tools is possible, just like I've done with BSM & PPS. As far as I understand, the MC team are currently working hard on getting their product to integrate with Excel Services. When they succeed, I'll be the first in line.

The old "displayed in thousands" requirement

Call me a hopeless geek but I really get a kick out of learning something that makes my life easier. Sometimes what I learn also annoys me because I realise that I've been doing things the hard(er) way up until that point.

I've been re-reading MDX Solutions 2nd Edition and found something I should have known about long ago. Format string settings in Excel, SSRS, SSAS are pretty much the same across the board. You know, defining #,#.00; (-#,#.00) and all that kind of stuff to make numbers appear the way users want. (There are lots of sites out there with more info on this, the first one I found with a quick Google was http://www.ozgrid.com/Excel/CustomFormats.htm)

A common requirement in financial reporting is to display larger figures in thousands. For example $101,230,142 would be displayed to the user as $101,230. In order to achieve this most of us calculate the actual and then somewhere in the process explicitly divide the result by 1000. Simple. Thanks to Appendix D in MDX Solutions I learned that I can format my "in thousands" figures by adding an appropriate number commas on the end of my format string. Even simpler.

e.g. 123,456,789

Apply a format of #,#, (note the extra comma at the end) the number is displayed as 123,457 (rounded up)
Apply a format of #,#,, (two commas this time) the number is displayed as 123

Each comma on the end of the format string, in essence, divides the number by another thousand. Annoyingly simple.

Excel 12, SSRS - More intuitive dates

When a user wants to specify a date, a date range or even select a period relative to today their options have been a bit limited (from a pure MS perspective). Often is will be up to the report developer to design specific date functionality into what the end user will interact with.

One of the commonly-lamented shortcomings of RS2000 was that there was no date-picker available to wrap a date parameter. Sure, if you built your own custom front-end to RS this could be done but not with the out-of-the-box Report Manager. We all know that the date-picker functionality is now in SSRS, and a very welcome addition it is.

So, what about when our user is creating a report in Report Builder. They come across a date field. When developers build models for Report Builder a field with a datetime data type can be manipulated in number of ways and then grouped in the model. For example, the Order Date field; the user is presented with a range of different meta data options pertaining to that specific date field. When initially creating the model the model designer wizard automatically creates a number of different date derivatives when building the initial model (day, month, quarter, year etc.). However if your users have a specific need for a certain format of date meta data you can easily create these with the formula editor in model designer. The left-hand pane of the below image shows an example of pre-defined date derivative fields.

















There are even more options available if the user wants to add some form of date filtering to their Report Builder report. The above screenshot shows the options a user is presented with when they choose to filter on a date field. Not a bad set of choices, and we don't have to code anything to give them that level of functionality, all we do is provide the date field.

So what about dates in Excel 12? A user's ability to sort and filter data in Excel 12 is going to take a major step up. So, if your user is working with data in an Excel table or PivotTable and would like to do some date-based analysis they will be presented with options like Next Year, Last Week, Yesterday etc (screenshot from Excel 12 Blog) Excel 12 will check the system clock and thereby determine what relative time period actually is.

These types of date manipulation options have certainly been available in various forms in different BI products for some time. This is nothing new or revolutionary from that perspective. However, it shows that MS is really concentrating on giving the BI end user much more power right off the mark without any (or at least a lot less) developer intervention. Great stuff!

Excel 12 - Surfacing & visualising data

The latest news from the Excel 12 team means I can finally talk about one of my favourite features of the new release - data bars. This is such a simple yet powerful visualisation tool, I know I'm going to be using it a lot. The flexibility which will be afforded us to configure data bars and other visualisations according to user needs is fantastic. More and more I see Office 12 helping BI developers spend less time creating great reports. This gives us more time to work on the back-end stuff with SSIS & SSAS, which I know most developers (including me) enjoy much more.

I recently made a poor attempt at humour with reference to 'surfacing' data. All jokes aside, Office 12 and other stuff coming from Redmond in the near future will give us far greater power to surface and visualise data for end-user consumption. Already with SSAS we can now define visual KPIs, in the Business Scorecard Accelerator (which is the precursor for what will soon be Business Scorecard Manager 2005 a.k.a. Maestro) we also have similar visual KPI functionality. There is more to come, much more.

BTW Ian Tien's unofficial Business Scorecard Manager 2005 blog can be found here. Also, if you're interested in more data about the Office 12 UI Jensen Harris' blog is where to go

Excel 12 - JWalk likes it

John "Mr Spreadsheet" Walkenbach has said that he is impressed with what Excel 12 will have to offer us. If you don't know who John is just have a look for the best rated Excel books on Amazon - you'll find him there. Told you Excel 12's going to be great!

I'm just catching up on my news having been away from technology this past weekend. Scoble always provides the most work when it comes to catching up on the blogs I subscribe to. I got the reference to John Walkenbach's post from him. BTW - if you don't already subscribe to Scoble's blog, you should.

Excel 12 - more news

OK, I promise that I'm not going to mirror each new post that appears on the Excel 12 blog. Just have a look at the big picture detail that has just been posted. I can tell you that there is a lot of detail beneath all of the points listed here. It also looks like there's going to be a lot more details released that I'll be allowed to blog about soon.

If you are a BI person in any way please keep a close eye on news related to this new version - it's going to be great.

Excel 12 - Into the enterprise

I had a feeling this post on the Excel 12 blog was coming soon. Just have a look at the numbers listed there. This shows that this next version is truly taking Excel into the enterprise space.

...and these are just the numbers. Wait 'till you see the stuff this extra power is going to let us do. Believe me, for BI people this is not just "another release of Excel".

Excel 12 - Holy Sheet!

As promised, some Office 12 news: The official Excel 12 Blog was created late last week. In his first post GPM David Gainer divulges a quick fact which proves that this next version is going to be big.

Excel 2003 currently gives us 65,536 rows and 256 columns for a total of 16,777,216 cells per sheet. Excel 12 will give us 1,048,576 rows and 16,384 columns per sheet for an enormous total of 17,717,869,184 cells. That's a 1024-fold increase of available cells per sheet - close to 18 BILLION cells!

Sheet shape: when you look at it from a pixel perspective (taking cell height of 17px and width of 64px into account) a 2003 sheet is 68 times as long as it is wide; a ratio of 68:1. The increased width of '12' will reduce this ratio to approx 17:1. So we're going to get a 'fatter' sheet, or at least a bit pudgier than the rake-like 2003 version.

So, you ask, if the average sheet is going to be bigger how is the Excel engine going to support users who fill the whole thing up? I'd love to be able to tell you more but can't. I'm sure David will post more details when the time is right. I'll add my two cents worth thereafter.