Nick Barclay: BI-Lingual

Excel 2010 gets sparklines, finally

nick.barclay@gmail.com (Nick Barclay) — Wed, 15 Jul 2009 10:37:00 PDT

There are a number of different Office 2010 features and functions we’re getting wind of now that the covers have come off at the WPC in New Orleans.

One feature that is of particular interest is sparklines. I would think that anyone who subscribes to this blog will know what a sparkline is and how useful native Excel support for this visualization will be.

If you’re looking some more visual information about this new feature take a look at the following:

There’s a good video on TechNet. The commentary is in German but the demo is pretty self-explanatory. Make sure you view it in full screen as you’ll get a good close-up view of the various display and formatting options available in the new Sparkline Tools ribbon tab.
Scoble has a post containing six great Office 2010 videos including one that demos the creation of sparklines (it’s the last video out of the six). In that same video there’s a demo of the new pivot table slicer functionality. Many would already be aware of slicer feature via the various Gemini demo videos that have been available for the last few months.

UPDATE: Here is the official Sparkline post from the Excel 2010 team blog

If you build it… just don’t call it PerformancePoint

nick.barclay@gmail.com (Nick Barclay) — Thu, 18 Jun 2009 12:47:00 PDT

Here we go. Now things are going to get interesting. Or are they? MS have just announced that they will make the PPS Planning source code available to customers and partners as the FPA (Financial Planning Accelerator). Is this just a good will gesture to quell the angry mob (even though their torches have probably burned out between January and now).

“Oh well, they discontinued the product, but at least they made the source code available.” I wonder how much use the FPA code will really be. Let’s remember, we’re not talking about a small codebase here. This took a reasonable sized Microsoft team a considerable amount of time to develop. A small consulting firm (the ones hardest hit by the sudden retirement of Planning) is just not going to be able to hand this stuff over to a junior developer and simply say “here, see what you can do with this.”

So, will there be a race among larger, resource-awash partners to see who can further develop and repackage the newly available code first? I’m unsure as to whether the no-cost individual license expressly precludes making an actual saleable product out of the code. Or has everyone already moved on? Some partners are probably rolling their own planning tools, others have sought alliances with alternative vendors, some have probably given up on the idea of a planning, budgeting & forecasting service line altogether.

There were many partners both large and small who invested significant time and money into the future of PPS Planning, and rightfully so – it was strong v1 product and was heading in the right direction. Some of the smaller players were left high and dry by the January news. The announcement also didn’t do much for the customers of varying sizes who were about to start, in the middle of, or just completing a Planning project. I was speaking to a customer the other day who asked me specifically not to mention the name “PerformancePoint” internally for that very reason. They’re looking forward to using the M&A functionality that will be baked into MOSS 2010 but want to be sure that we just don’t use “the PP word”.

I’m sorry to say that I’m seeing more and more customers recoil in fear at the mention of the name PerformancePoint. Microsoft did themselves no favors with the way the PPS roadmap was so suddenly changed earlier this year. As a result PerformancePoint has almost become a dirty word. This is truly unfortunate for the remaining part of the product that will soon become PerformancePoint Services. In the meantime any opportunities for M&A work need extra TLC to convince potential customers that the “other half” of PPS 2007 is still a safe bet for the future.

Whatever new or different products come out of the FPA code I would strongly advise those working on it to ensure they give it a completely different name. I would also hope that MS will recognize the damage they have done to their PerformancePoint brand and come up with a new name for the M&A features coming in MOSS 2010.

Anyone got any suggestions for an alternate name for PerformancePoint Services? In line with all the products it should probably be <InsertNameHere> Services.

Book Review: Now you see it

nick.barclay@gmail.com (Nick Barclay) — Thu, 04 Jun 2009 04:45:00 PDT

Full disclosure: the author provided me with a free, pre-release copy of this book.

Anyone who has worked with me knows that I am big fan of Stephen Few’s work. I consider Information Dashboard Design compulsory reading for anyone who calls themselves a BI professional. In his new book, Now you see it: Simple visualization techniques for quantitative analysis, Stephen casts a wider net addressing anyone whose job it is to analyze data to discover trends, patterns and exceptions hidden within it.

The book centers around developing the reader’s skills particular to the organization, analysis and interpretation of graphical data. Few continues his quest to help readers harness the power of visual perception by presenting data in such a way that information, and the story behind it, becomes clear. Consider once more the book title and cover art: Now you see it.

The author provides a brief history of the discipline of data visualization, its importance and potential. He breaks down the attributes that make a good analytical data set, the traits of a good analyst and functionality a good analytical software package should support. Once readers are familiar with these concepts they learn how the data, analyst and software are best brought together.

Time is spent explaining how data visualization best practices and techniques can be employed in order to extract maximum value out of the massive amounts of data we find ourselves immersed in these days. Logical explanations and relevant examples are provided showing when to use particular visualizations for specific data in order to achieve maximum analytical value. Whole chapters are dedicated to individual analysis types such as time series, distribution, deviation, correlation among others. Each chapter teaches how to effectively present the data graphically as well as how to recognize, interpret and understand patterns specific to a particular analysis type.

I always enjoy Stephen’s books, I am smarter as a result of reading his work and I like his easy-going writing style. In this book he takes on the role of teacher but I never really felt like I was being “taught”. Like his other books Now you see it is full of relevant, colorful figures, illustrations and diagrams that bring the written content to life.

These days more and more people classify themselves as “analysts” - those who work with data in some way, shape or form to help a business achieve its goals. Now you see it teaches analysts, or any BI professional for that matter, how to take their data visualization and interpretation skills to the next level. Yet another highly recommended book to add to the BI library.

Good to be an MVP (again)

nick.barclay@gmail.com (Nick Barclay) — Wed, 01 Apr 2009 16:48:00 PDT

I was really happy to receive the (non-April Fools) email this morning informing me that I’m an MVP for another year. Very flattering :)

Installing and Troubleshooting M&A on Win / SQL 2008

nick.barclay@gmail.com (Nick Barclay) — Tue, 03 Mar 2009 07:53:00 PST

With some upcoming presentations on the horizon it was time to build a new Win 2008 VPC. I found out a few gotchas that are worth noting in order to get your fresh, standalone M&A instance up and running quickly. Please note I haven’t tried all the different install scenario permutations. I found some of the points below helped me to get things working as they should.

Pre-Install

First some pre-install tips. Don’t worry if you’re reading this after you’ve installed, just keep the following in mind for next time.

UPDATE: here is a MS knowledgebase article that detailing steps to perform SQL 2008 / PPS installs

Install SP2 before you configure M&A

I don’t know of a specific reason why you shouldn’t configure the M&A server and then load SP2, but it’s probably a good practice on a fresh install. So install M&A, but don’t run the configuration wizard yet. Next install M&A SP2 (remember, you only need to execute PSCSrv.msp, the others are for Planning). Once you’re done, run the configuration wizard.

Install SQL 2005 CU 9 Updates

You may be given a pre-req warning signal while configuring the M&A server telling you that “SQL ADOMD.NET 9.0 (SP2) is not installed”. Note that this is not a red hard-stop, just a yellow warning; you can actually go ahead without these bits if you wish. I actually ignored it on one install and didn’t find any real difference in my ability to connect to both SQL 2005 and 2008 SSAS cubes, nonetheless it’s nice to have greens all the way down the checklist when you install.

Load the following CU 9 updates for SQL 2005 (no, you don’t need to have SQL 2005 installed). You can download these here.

SQLNCLI
SQLServer2005_ADOMD
SQLServer2005_ASOLEDB9
SQLServer2005_XMO

Post Install

The following assumes you have installed and configured SQL 2008, MOSS/SharePoint and PPS M&A and are having problems getting things going.

.NET Framework 3.5 & AJAX Extensions 1.0

Once you’ve run through the M&A configuration steps and subsequently try to deploy, publish or connect to the server you may come into some trouble in the form of the (very descriptive) “Unable to connect to server” error.

If you try and browse to the M&A web service (http://MyServerName:40000/WebService/PmService.asmx) you might be faced with a stack trace that looks similar to this:

Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Could not load file or assembly 'System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.

Source Error:

Line 23:     <compilation defaultLanguage="c#" debug="false">
Line 24:       <assemblies>
Line 25:         <add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
Line 26:       </assemblies>
Line 27:     </compilation>

Source File: C:\Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\WebService\web.config Line: 25

Assembly Load Trace: The following information can be helpful to determine why the assembly 'System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' could not be loaded.

The .NET Framework 3.5 is a pre-req for SQL 2008. Excellent. AJAX is now a built in component of 3.5, so when we ran the M&A Configuration Manager we no longer get a warning flag asking us to install the AJAX 1.0 extension (when configuring M&A on earlier versions of the framework we were warned that we needed to install the AJAX 1.0 components). However, this appears be a red herring – it seems installing v1.0 of AJAX will actually be more of a help than a hindrance.

From my testing these problems seem to be caused by M&A still looking for version 1.0.61025.0 of AJAX but not finding it. Simply changing the value in the Version attribute in various web.config files as I referenced in this post some time ago seems to help but did not resolve the problem completely for me this time.

Solution: If your M&A server is already installed and configured don’t worry. Download and install the AJAX Extensions 1.0. After an IISRESET you should be good to go.

Ensure the Application Pools are Started

I found that when I did a fresh install on Server 2008 some of the M&A application pools sometimes did not get started automatically. This can result in “503 Service is Unavailable” and / or “Unable to connect to server” messages. Go into the IIS manager and ensure both the Preview Site and Monitoring Service application pools are started.

Speaking at TechEd USA 2009

nick.barclay@gmail.com (Nick Barclay) — Tue, 03 Mar 2009 05:16:00 PST

I always knew living in the USA would give me a much better chance of finally attending the big daddy of TechEds. This year’s event will be taking place in Los Angeles from May 11th - 15th. I got the word recently that one of my session submissions has been accepted.

I’ll be presenting a session on PerformancePoint Monitoring and Analytics - the part of the product that is not going away. The session will cover ways to provide more dashboard interactivity, using web page reports and simplifying access to element metadata.

There are a lot of people I’m looking forward to catching up with there. See you all in LA!

Now that’s a fast browser!

nick.barclay@gmail.com (Nick Barclay) — Wed, 25 Feb 2009 06:47:00 PST

One of the bigger stories on Techmeme right now about the latest beta version of Apple’s Safari browser and how fast it is. But just how fast is it? In CNET News’ article a 3d chart is used to illustrate the point.

(ref: CNET News - Safari 4 benchmarked: 42x faster than IE 7, 3.5x faster than Firefox 3)

There are several things wrong with the graph above from a data visualization perspective but there is one that is glaringly obvious and absolutely relevant to the topic at hand. As a result of the data-obfuscating 3d perspective of the graph it looks like the Safari 4, Minefield and Chrome browsers performed their tests in less than 0 milliseconds! Are we talking pre-cognition here? Maybe these browsers know the web page you want to view before you do; a pretty cool feature.

I think I may have heard somewhere that Microsoft research has been trying to crack the browser pre-cognition market too with a new development platform named ESP.NET :)

Here are two attempts I had using the same data.

…and another in grayscale

R.I.P. PerformancePoint Server

nick.barclay@gmail.com (Nick Barclay) — Fri, 23 Jan 2009 12:45:00 PST

I just love a good premature leak, don’t you? Let’s spell it together… N-D-A.

Anyhoo the announcement has now been made officially on The BI Blog along with an explanatory video from Guy Wiesmantel. PerformancePoint Server, as a standalone product, is being retired. There are two main takeaways from this announcement:

1. Monitoring & Analytics will live on and is to be incorporated into future versions of MOSS Enterprise. So M&A is staying but will be considered a part of MOSS, to be known as “PerformancePoint Services”. I would assume this embedded version of M&A will completely replace the current (and very ordinary) BI/dashboarding capabilities that are bundled with MOSS Enterprise.

2. The Planning part of the PPS product is being retired. Naturally, it will still be supported up until its end-of-life but that will be it. Aside from a to-be-released SP3 no further PPS Planning development will happen. Budgeting & forecasting functionality as is currently provided by Planning will no longer be part of the MS BI stack.

Interesting times…

SSAS 2008 Dynamic Sets in Analytic Reports

nick.barclay@gmail.com (Nick Barclay) — Fri, 12 Dec 2008 07:33:00 PST

The primary aim of PPS SP2 is really platform support, particularly the addition of SQL 2008 as a data source. One of the subtle new features of Analysis Services 2008 is the support for dynamic named sets. Now that we can connect to 2008 cubes we can take advantage of them.

In 2005 we could create named sets but the members they contained were static did not change once the cube had been processed. This is probably why the 2005 AdventureWorks cube doesn't contain any TOP / BOTTOMCOUNT named sets - they were static and not of much use. In 2005 if we want a dynamic TOP n result set we need to write an MDX statement containing a session-scoped WITH SET statement.

WITH
SET [Top 10 Selling Products] AS
    TopCount
    (
      [Product].[Product].[Product].MEMBERS
     ,10
     ,[Measures].[Sales Amount]
    )
SELECT
[Measures].[Sales Amount] ON 0
,[Top 10 Selling Products] ON 1
FROM [Adventure Works]
WHERE
[Date].[Calendar].[Calendar Year].&[2002];

If we wanted a PPS analytic report displaying TOP / BOTTOM n data from an SSAS 2005 data source we had to plug an MDX statement into the analytic report definition like the one above. Because we had to write manual MDX in order get the desired result the user lost their right-click interactivity in the deployed dashboard.

Enter SSAS 2008 dynamic named sets. By setting a named set's Type property to Dynamic we now have a set that will recalculate its members based on the context of the query it is referenced in.

CREATE DYNAMIC SET CurrentCube.[Top 10 Selling Products] AS
    TopCount
    (
      [Product].[Product].[Product].MEMBERS
     ,10
     ,[Measures].[Sales Amount]
    ) ;

Because the named set is an object within the cube we can now build out an analytic report using drag & drop and don't have to touch the MDX to get what we need. As a result users have full access to the right-click menu.

Demo

I set up 3 analytic grid reports using a named set called Top 10 Selling Products and embedded them in a dashboard.

References a Top 10 Selling Products dynamic named set I defined in the AdventureWorksDW2008 cube
Manual MDX implementation of Top 10 Selling Products completely defined in the query tab of the Analytic View Designer (using the MDX code above)
References a Top 10 Selling Products named set that I defined in AdventureWorks for SSAS 2005 (a static named set)

I created a single date filter and hooked each of the grids up to it. As you can see from the shot below. At the All Periods level all three grids show the same 10 products and the same Sales Amount totals. All is well.

When we filter on a year, say CY2002, we see several things.

The SSAS 2008 grid is noticeably faster and completes rendering first with accurate results
The manual MDX implementation results match those returned by the SSAS 2008 version, as it should, but it was slower.
The members that make up the SSAS 2005 named set do not change. We get gaps for the products that we didn't sell in 2002 - like the Mountain-200... bikes. We must have sold plenty of them in later years as the static set put them at the top of the list for all time sales.

Now, let's try interacting with our two accurate grids. As expected the Manual MDX version has no drilldown capabilities. Remember that this limited drill functionality is by design - if you change even one character of the MDX on an Analytical report user interactivity features are disabled.

Now here's the good bit. Because the SSAS 2008 grid was built using drag & drop with no hand-altered MDX we have full right-click access to the data behind it.

So, if you're using (or about to start using) SSAS 2008 get stuck into Dynamic Named Sets and put them to use in your cubes.

The main advantages:

Drag & drop analytic report design = faster, simpler development
Enhanced user experience in the dashboard
More centralized logic in the cube, less code in the element definitions
Faster performance

PerformancePoint SP2 is Here

nick.barclay@gmail.com (Nick Barclay) — Tue, 09 Dec 2008 06:47:00 PST

The main takeaways from this release: full support for Windows Server 2008, SQL Server 2008, .NET 3.5 and virtualization with Hyper-V. Great news! There are some bug and performance fixes but not really any new 'features' per se.

The SP can be downloaded from the following locations:

For a more detailed list of what's inside SP2 have a look at these ReadMe files:

M&A
Planning

How to Win Favor with Australians in the USA

nick.barclay@gmail.com (Nick Barclay) — Fri, 05 Dec 2008 05:57:00 PST

...OK a bit off topic.

Do you work with, or know an Australian who lives in the USA? Don't know what to get them this holiday season? Here's a simple suggestion.

I have just found out that every Australian's favorite cookie, the Tim Tam, is now available in the USA until March next year! You can find them Pepperidge Farm and Target stores. If you've never tried them, you should. Trust me. If you want to make an ex-pat Australian weep with joy or even propose marriage then this is something you can surprise them with.

Some American Tim Tam perspectives:

http://nycfoodguy.com/2008/11/17/nyc-cookies-tim-tams-are-finally-in-the-us-we-want-them-to-stay/

http://nymag.com/daily/food/2008/11/tim_tam_chocolate_cookies_land.html

http://www.cnbc.com/id/27663260/site/14081545

Two more BI blogs to subscribe to

nick.barclay@gmail.com (Nick Barclay) — Fri, 05 Dec 2008 05:37:00 PST

More and more people are starting up BI blogs these days.

Richard Lees - finally Richard is blogging, sharing his very considerable experience with the world. Coupled with www.richardlees.com which houses, live PPS dashboards, SSRS reports, Excel Services pivot tables, data mining examples and more for all to see and interact with.

Boyan Penev - I really enjoyed I working with Boyan for some months on a customer engagement and am very glad he has started blogging too. I can see that he has published an article on SQL Server Central recently as well.

Book Review: Applied Microsoft SQL Server 2008 Reporting Services

nick.barclay@gmail.com (Nick Barclay) — Wed, 03 Dec 2008 05:44:00 PST

Full disclosure: the author supplied me with a free copy of this book.

Teo Lachev's been hard at it again, toiling to get yet another full-featured technical book out hot on the heels of the actual product RTM. When I read & reviewed his SSAS 2005 book a couple of years ago I remember being impressed not only at the amount of content in the book but also the depth of content so close to RTM. Those who are familiar with Teo's previous books will not be disappointed with his latest work - Applied Microsoft SQL Server 2008 Reporting Services.

By now I think it's fair to say that most people out there have at least some experience using Reporting Services since the initial release some years ago. If you've had experience with SSRS this book is an excellent reference on the product's new features, a reminder on the older (and updated) ones, and how to go about customizing and extending in a number of different ways. For the complete newbies this book is also a great place to begin and then take some advanced steps soon thereafter.

On average the split between the "this is how you use the product" and the advanced content in technical books seems to be about 80 : 20, sometimes more. Teo's books consistently tip that scale in favor of the advanced stuff, coming out to about 60 : 40, maybe more. There's still plenty of starting-from-scratch foundational content but there's also plenty advanced stuff to satisfy those hungry for a bit more. This is where those already experienced in SSRS will see a lot of value.

Like other Prologika books there are plenty of documented real-world stories & lessons learned to save you finding out the hard way. Each chapter contains plenty of references to pertinent white papers, blog posts and useful applications to assist in administration or development of solutions. Naturally there is a ton of downloadable sample source code too. There's even sample code for creating a Silverlight reporting UI.

Like the late Ken Henderson did in his brilliant Guru's Guide books Teo makes a noticeable effort not to simply repeat information that can be found in BOL. Isn't that what we all want from a technical book?

Worthy of a place on the MS BI geek bookshelf.

Microcharts Visualizations Supported in SSRS 2005

nick.barclay@gmail.com (Nick Barclay) — Mon, 01 Dec 2008 09:26:00 PST

Kudos to the Microcharts team who have just released a product fully supporting sparklines, bullet graphs and more within SSRS 2005. If you're a fan of Stephen Few's work and are longing for more efficient and effective data visualization options in SSRS (yes, including Dundas) then you might want to have a serious look at this technology. Naturally I'm wondering when they're going to support SSRS 2008. I'm sure they're working on it.

Early last year I managed to get some sparklines into a BSM and PPS CTP2 scorecard using one of the earlier versions of Microcharts. Since that time BonaVista Systems (the makers of Microcharts) was acquired by XLCubed. The original Microcharts used custom fonts to render its objects and hence I was able embed the resultant "text" in a scorecard to display the sparklines. As a result of some architectural changes Microcharts now uses images to produce their visualizations hence our ability to use them in a scorecard is not possible. Nonetheless, this image-based architecture now enables quite a few other potential opportunities that I hope to explore (and blog about) in the near future.

As a side note, the MC lead developer / architect, Andreas Lipphardt, maintains a data visualization blog that is well worth a subscription.

UPDATE (2 Dec 2008): Andreas has confirmed that they are working on a SSRS 2008 version (see comments). Apparently there is quite of lot of rework that needs to be done because of changes made to custom report items in the new release.

If I can make it there... I'll make it anywhere

nick.barclay@gmail.com (Nick Barclay) — Fri, 28 Nov 2008 12:17:00 PST

Back on the air! It's been a long time between posts. Where have I been? A better question would be where am I now?

Well, I'm not in Australia, I'm in New York! Yup, permanently. Or at least for a good many years, anyway. Why? No not for work. My wife Angela is American and has loved living in Australia since moving there in 2001, but she really missed her family over here. So earlier this year we decided to make the move, lock-stock-and-barrel, to the other side of the planet (the colder side). We're going to live on Long Island, not far from Manhattan.

Where will I be working? After many early morning phone hookups between Australia and the USA I signed up with the Avanade team in New York city as a Senior Solutions Architect. I started a couple of weeks ago and am looking forward to what's to come.

Being based in the USA should afford me the opportunity to actually attend some conferences (PASS, TechEd etc.) and meet, in person, many of the people I've got to know since starting this blog. Looking forward to that too.

Aside from starting work there's still plenty for us to do in terms of sorting out home, car and general settling in. Thankfully all of that is progressing well so I'm hoping to find some time to get back into blogging as well as contributing to the Microsoft community over here. So if you're based in New York, or anywhere in the vicinity (particularly Long Island) please drop me a line at MyFirstName-dot-MyLastName-at-gmail-dot-com.

Jason Morales' Microsoft BI Update Blog

nick.barclay@gmail.com (Nick Barclay) — Wed, 11 Jun 2008 16:00:00 PDT

I think many readers of this blog are already on the distribution list for the email-based BI updates that Microsoft's Jason Morales has been sending out regularly since last year. Jason now has a blog on which he is now posting his BI updates.

I believe the email version will be around for a while but will be phased out in favour of the blog version. Well worth subscribing in your favourite feed reader.

PPS M&A SP1 DataSource Cache Bug

nick.barclay@gmail.com (Nick Barclay) — Mon, 09 Jun 2008 16:37:00 PDT

After installing SP1 I had a demo dashboard that all of a sudden performed far slower than it did on the original RTM build. The analytic charts pointing to my OLAP cube were unbelievably sluggish; like 30 sec+ to return result sets on queries that, when executed in SSMS, ran in milliseconds. After some testing I came up with the answer. It was the Cache Setting on my data source. At some point I had set the cache interval on the data source to 0 and not changed it back to a more reasonable number.

In the RTM build of M&A a cache interval of 0 on a multidimensional data source didn't have a noticeable performance effect, you simply lost any caching benefits as the cube was always re-queried. A bug in SP1 makes queries to MD data sources with a 0 cache setting take far longer. When I contacted them the PPS team confirmed that this is indeed a bug and they're already working on a fix.

The workaround: If you have already applied SP1 find any published multidimensional data sources with a cache setting of 0 and set it to something higher - anything above zero seems to work. Unless you are hitting an absolute zero-latency cube there aren't many good reasons for not taking advantage of some caching. I often find that the 0 creeps in there when testing and gets forgotten.

As far as I can ascertain tabular data sources do not seem to be affected.

An easy way to find your published data sources with a 0 cache setting? Easy! Use the MAUDFs. Once MAUDF is set up finding the offending data sources is as simple as running this query:

SELECT *
FROM MAUDF.DataSourceMetadata()
WHERE DataSourceType = 'Multidimensional'
AND MinutesToCache = 0

PPS Blog en Francais und Buch auf Deutsch

nick.barclay@gmail.com (Nick Barclay) — Sun, 08 Jun 2008 20:57:00 PDT

There seems to be PPS blogs popping up all over the place. One that caught my eye recently was that of Aurelien Koppel, a Paris-based BI consultant. Aurelian blogs in French, something I know will bring a tear to the eye of both Bruno Aziza, a Frenchman, and Adrian Downes, a French-Canadian. For those who studied or are familiar with the language this will present a way to stay up to date on the latest in the PPS world and dust off your long-lost French skills. If, however, you define 'language' skills as fluency in C#, C++ and VB check out the Windows Live translator tool which does a pretty good job at converting the entire blog in one hit and displaying the side-by-side results.

Did you study German instead of French at school? Well you're in luck. MS Press released a book on PPS Planning written in German: Unternehmensplanung mit Microsoft Office PerformancePoint Server 2007.

The PPS Monitor & Analyze UDF Project (MAUDF)

nick.barclay@gmail.com (Nick Barclay) — Mon, 02 Jun 2008 16:02:00 PDT

I've often wanted to be able access metadata within a monitoring server with greater ease. Querying the PPSMonitoring database is difficult because the more interesting pieces of information pertaining to a particular element are locked away within the SerializedXML column of the FCObjects table. Sure we can access the data in the SerializedXML column with XPath / XQuery but I really wanted to get stuck into some CLR stuff and figured UDFs would provide the greatest reuse, flexibility and query-ability.

I've created a series of CLR table-valued UDFs and created a home for it on Codeplex, a project that I have named MAUDF (taking a bit of inspiration from the ASSP project). Using the CLR the MAUDFs connect to the PPS Monitoring web service and extract pertinent metadata and return the results in a tabular format.

Why UDFs?

The use of table-valued UDFs enables element metadata to be queried, JOINED and (CROSS | OUTER) APPLY-ed in T-SQL in as if they were tables, opening up the world of M&A metadata to a much wider audience.

Using the UDFs

Some of the UDFs do not require a parameter value, simply executing these functions returns a tabular result set that can be used as if it were a table like the example below that calls MAUDF.ElementMetadata().

-- Get metadata for all elements with a display folder of "PPS Demo"
SELECT *
FROM MAUDF.ElementMetadata()
WHERE DisplayFolder = 'PPS Demo'
ORDER BY ElementType

The remaining UDFs take a single parameter: the GUID that identifies the element in question. We can supply that value by using APPLY, all we need is an anchor table from which to reference the element IDs. In the example below we use the MAUDF.ElementMetadata() function to provide the anchor record set.

-- Get the custom property details for KPIs that have custom properties defined
SELECT
ElementName AS ElementName
,PropertyType
,PropertyName
,PropertyDescription
,PropertyValue
FROM MAUDF.ElementMetadata() e
CROSS APPLY MAUDF.ElementCustomProperties(ElementID) cp
WHERE e.ElementType = 'KPI'

I have also created a view named MAUDF.vFCObjects which is based on the data in the FCObjects table. The view definition simply adds a textual ElementType column. The same query shown above be duplicated using the MAUDF.vFCObjects view as an anchor for the CROSS APPLY operation.

SELECT
ElementName AS ElementName
,PropertyType
,PropertyName
,PropertyDescription
,PropertyValue
FROM MAUDF.vFCObjects f --using vFCObjects as an anchor for CROSS APPLY
CROSS APPLY MAUDF.ElementCustomProperties(ElementID) cp
WHERE f.ElementType = 'KPI'

The main difference between the using an MAUDF or vFCObjets view is query performance. Because vFCObjects is based on a physical table with indexes you can immediately limit the number of rows that are made available to the CROSS APPLY thereby increasing performance. The MAUDF.ElementMetadata() function must first generate an entire result set for all published elements and then this is reduced based on the contents of the WHERE clause, which can take a bit longer.

There are different situations where one query structure will out-perform the other but not by too much. The aim of providing both is flexibility and choice.

MAUDF Schema

All UDFs and supporting objects are created within the PPSMonitoring database in their own schema, cunningly named "MAUDF".

Sample Queries

Below are some more sample queries. All these and more are contained in the Codeplex project. Some of the samples use the no-parameter-UDFs as an anchor to CROSS APPLY to, others use the vFCObjects view. The choice is yours as to which suits your requirements.

-- Which elements has "NT AUTHORITY\Authenticated Users" been granted access to?
SELECT
f.ElementTypeName
,f.ElementName
,m.MembershipRole
FROM MAUDF.vFCObjects f
CROSS APPLY MAUDF.ElementMemberships(ElementID) m
WHERE MembershipLogin = 'NT AUTHORITY\Authenticated Users'
ORDER BY f.ElementTypeName

-- Get the count of each report type published to the server
SELECT
ReportType
,COUNT(*) ReportCount
FROM MAUDF.ReportMetadata()
GROUP BY ReportType
ORDER BY COUNT(*) DESC

-- Get all Scorecard annotations
SELECT
f.ElementName
,CommentTitle
,CommentMessage
,AnnotationKpiMetricName
,AnnotationTuples
,FilterSlice
,AnnotationKpiID
FROM MAUDF.ScorecardMetadata() s
CROSS APPLY MAUDF.ScorecardAnnotations(s.ScorecardID) sa
INNER JOIN MAUDF.vFCObjects f
ON s.ScorecardID = f.ElementID
ORDER BY s.ScorecardID, AnnotationID, CommentCreatedDate

As a Report Source

Once you can easily access element metadata in a tabular format the creation of SSRS reports becomes far easier.

For example, a common request from many customers relates to the display of threshold banding numbers set for a particular KPI target. The SSRS report depicted below displays the threshold band numbers and the indicator icons associated with them each target in a KPI. The report takes a KpiID as a parameter. The report can then easily be incorporated into a dashboard by creating a filter link from a scorecard to the report passing the KpiID.

The SQL code to return the result set for this report is also contained in the samples.

Where do I get it?

The MAUDF setup instructions, source code, release binaries, samples, documentation and more can be accessed from the MAUDF Codeplex project.

I'm looking forward to hearing feedback and any new ideas anyone may have. Please note that this is the first time I have written anything seriously in C# (or any other .NET language for that matter).

Thanks very much to Darren Gosbell for his early alpha testing and feedback.

Book Review: Drive Business Performance

nick.barclay@gmail.com (Nick Barclay) — Mon, 19 May 2008 15:49:00 PDT

Full disclosure: The authors provided me with a free copy of this book.

With a foreword by none other than Drs Kaplan and Norton you can be sure that software is not central to this book's message. Drive Business Performance is part of Wiley's Microsoft Executive Leadership Series, however the only time "Microsoft" is mentioned is when referencing quotes from Steve Ballmer or Chris Liddell. The intended audience is a business one not technical, although technical people would do well to get their hands on a copy nonetheless.

Fitts and Aziza provide a comprehensive look at what Performance Management (PM) is and just how successful organisations can be when they succeed in creating a "Culture of Intelligent Execution". Central to the book's philosophy are what the authors have dubbed the "Six Stages of Performance Management Value". Targeted lists of questions at the end of each section enable readers to assess their organisation's current PM capabilities.

For mine the book's greatest strength are the anecdotes and quotes peppered throughout to underscore each topic. It is obvious the authors went to a great deal of effort to interview high-ranking members of companies who spearheaded some very successful PM implementations. The information gathered in these interviews coupled with well-chosen quotes from the likes of Winston Churchill, Henry Ford and many others serve as evidence to support Fitts and Aziza's Six Stages and the culture of performance management in general.

PM is not just something that can be thrown together in a few weeks by a couple of interns, it is an organisation-wide paradigm shift that takes time, effort and dedication to bear fruit. As recounted many times within the book, the rewards to organisations who implement an effective PM initiative can be very substantial.

This is a great book to hand to anyone who is considering implementing any form of PM. It details plenty of good "See! They did it, why can't we?" case studies as well as providing practical guidance describing how to do it within your own organisation. Highly recommended reading.

And another PPS Blog

nick.barclay@gmail.com (Nick Barclay) — Thu, 08 May 2008 14:00:00 PDT

If you're subscribed to Adrian or Tim and have already read that Paul Steynberg is now blogging, please accept my apologies for restating this fact. As PPS adoption begins to grow I just want to do my part to ensure new blogs are brought to the attention of as many people as possible. From experience I can say that the more subscribers / traffic a blogger has, the more they want to blog, and that's good for all of us.

Paul brings technical experience and financial knowledge to the table, which is an excellent combination of skills for a blog on financial BI. I don't think I've seen many posts on PPS Management Reporter yet, but he has already written one. More please, Paul!

...but can I print it?

nick.barclay@gmail.com (Nick Barclay) — Fri, 02 May 2008 16:45:00 PDT

Many businesses these days are making a concerted effort to access as much BI data as possible through the browser. Funny thing is that once this wonderfully dynamic, interactive data is available in a thin-client environment all some users (still) want to do is print everything out (not just the summary page, everything) and then circle the important bits with a red pen.

The printability of an interactive dashboard or report and all the data that lies beneath still seems to find itself located high on the list of deliverables for many projects. Making a web-based BI solution printable can add a great deal of unnecessary complexity to something that should be kept as simple as possible.

It's ironic in these days of "going green" that organisations who pride themselves on their environmental friendliness and sustainable business practices are the ones who want to print out reams of report data. Now, please don't mistake me for an environmental zealot desperately trying to save trees; printing a page or two of report data is fine and can be very useful. But printing an entire "pack" that literally flattens out all that interactive, drillable goodness that that we work so hard to build really seems to be a waste, not so much of trees but of technology.

In this day and age where wireless hotspots and 3G cards abound, VPNs are commonplace and most employees work off a laptop why is there still a need to print large chunks of dynamic, highly-accessible, web-based data? Often the need to print everything is accompanied by requests for the data be available in the BI environment as close to real time as possible. This is a very reasonable request in many cases. Naturally the contents of the printed report is out of date very quickly as a result. Hmmm, maybe that's the reason Google haven't produced a book of their work yet. Pity. If they did then I'd to be able to bring my printed copy of the internet with me wherever I (and my staff of Google-book-carriers) go! I could even fold over the corner of the pages that I like and use my red pen to mark the interesting bits.

The short shelf life of printed report data reminded me of a classic sketch from an old radio program that I am certain some readers of this blog may have heard of - The Goon Show. Others may have no clue what I am talking about. Either way I am sure you'll get a kick out out of it *.

Bluebottle: What time is it Eccles?

Eccles: Err, just a minute. I've got it written down here on a piece of paper. A nice man wrote the time down for me this morning.

Bluebottle: Then why do you carry it around with you, Eccles?

Eccles: Well, if anybody asks me the time, I can show it to them.

Bluebottle: Wait a minute Eccles, my good man...

Eccles: What is it fellow?

Bluebottle: It's writted on this bit of paper, what is eight o'clock, is writted.

Eccles: I know that my good fellow. That's right. When I asked the fella to write it down, it was eight o'clock.

Bluebottle: Well then, supposing when somebody asks you the time, it isn't eight o'clock?

Eccles: Then I don't show it to them.

Bluebottle: Well how do you know when it's eight o'clock?

Eccles: I've got it written down on a piece of paper!

* copied from http://en.wikipedia.org/wiki/The_Goon_Show#Surreality

If you want to listen to the audio of this sketch it can be found on the same Wikipedia page as the text above.

KPI, Scorecard, Dashboard - what's in a name?

nick.barclay@gmail.com (Nick Barclay) — Fri, 02 May 2008 16:30:00 PDT

Is it just my imagination or are the words "KPI", "Scorecard" and "Dashboard" appearing in spec documents with increasing frequency and (sometimes) decreasing thought? Just because a requirements document states deliverables of KPIs, scorecards and dashboards does not necessarily mean that using PPS elements that happen to share the same name represent the most appropriate solution.

This is not a post about what I think KPIs, scorecards & dashboard are or aren't, neither is it a post about any shortcomings in PPS functionality. What I'm focusing on is the sometimes casual use of the words "KPI" "scorecard" and "dashboard" in requirements documents. When it comes to a (potential) PPS deployment this has can skew the solution that is eventually delivered and potentially limit it's future capabilities and flexibility as an organisation's BI maturity grows.

Scorecards

In the heady days of BSM I blogged that people often expected a scorecard to do the job of a report and were disappointed when the product did not deliver on their expectations. "I guess it's just not fully featured yet..." they'd say. I still maintain that this disappointment was misplaced as expectations were centred around their idea of what a scorecard is for when it came to BSM and, more recently, PPS.

The PPS team has done a great job in improving the flexibility and functionality available to us when it comes to building out scorecard elements. In many ways I consider this to be a double-edged sword. So much new layout, aggregation and formatting power has now been given to the scorecard element that many new users of the technology are once more looking to the scorecard to deliver functionality that it is not best suited for.

There are many things that PPS scorecards do well but these are the ones that I think are most relevant:

enable hierarchical arrangement & nesting of KPIs
manage KPI weights
normalise KPI scores
roll up disparate KPIs

To me these are the things that make a scorecard what it is. A scorecard is there to provide an overall insight to an organisation / business unit / business area. Either we're heading in the right direction, in line with the strategic objectives of business, or we're not. If not, why not? That overall insight is materialised by the scorecard using the features above.

Scorecards facilitate comparison and roll up of figures that are normally incomparable with a simple reporting tool. Try rolling up your gross profit, cost of sale % and the number of customer complaints last month. Adding $1,436,814, 7.54% and 36 is not going to give you a very useful figure. Nonetheless these are useful KPIs that can assist in measuring the overall health of a business. Creating a KPI for each and arranging them in a scorecard could be the genesis of the very powerful business tool. Why? Because the inner workings of the scorecard element do all the heavy lifting to normalise and roll these numbers up - they can now be compared in the context of the scorecard. It's the normalisation of KPI values and subsequent weighed roll up that is the killer feature of scorecards.

Often the output users want when they say "scorecard" is the next level down from a scorecard like the one depicted above; the more granular detail of what is causing our customer complaints to increase, what department are they increasing for? The high-level scorecard simply indicates that the level of customer complaints has breached it's defined threshold.

A scorecard is not a report. More often than not people use the "scorecard" to describe [what I would consider to be] a report that uses some form of visual indicator like a smiley face or traffic light to denote good or bad performance. Now this is not to say that they are wrong in their use of this term, the definition of a scorecard is not set in stone. Just be wary not to let someone's use of this word steer you inexorably towards the creation of a PPS scorecard element without considering the best way to deliver what's required. If a breakdown of customer complaints by department with subtotals is required, how about an SSRS report? Just because PPS scorecards do aggregations and support the arrangement of dimension members on row and column axes does not necessarily make them a replacement for Excel pivot tables or the data regions supported by SSRS. Don't try and push the data into a scorecard just because requirements ask for a series of green ticks or a red crosses and uses the word "scorecard" to describe what's needed.

KPIs

PPS KPIs are very flexible elements. Their real strength lies in the ability encapsulate logic centred around a particular metric and compare actual with desired performance. These KPI elements can be hierarchically arranged, weighted and rolled up in a scorecard. KPIs do their best work and provide the greatest reuse when the the majority of logic and data required for their definition is stored and managed in the back end. i.e. the definition built in Dashboard Designer is kept simple. Another feature of well-designed KPIs is a common structure and naming standard for Actual and Target metrics.

Overly complex and over-engineered KPIs (many targets, differing metric names, lots of MDX) can certainly lead to a solution but just how reusable are these elements? Can they be reused in other scorecards, or were they purpose built to "make scorecard X look like the picture in the spec"?

Just because someone uses the term "KPI" does not always mean the creation of a PPS KPI element, an SSAS KPI or MOSS KPI is necessarily the answer. Many immediately equate the use of a smiley face, traffic light or background colour formatting with a KPI and subsequently a scorecard. Often the requirement is as simple as emphasising a positive or negative delta to a budget or forecast. Does this kind of comparison need to be made into a physical KPI? Possibly, but not definitely. Consider the alternatives. Often this kind of requirement can be fulfilled simply and elegantly with an SSRS report. Because you paid $$ for an application doesn't mean you have to use it more than the tools that came bundled with the SQL licence. Don't fall into the "when the only tool you have is a hammer, everything looks like a nail" trap. Use the right tools for the right job.

Square peg? Round hole? No problem.

Bart, get me my sledgehammer!

Just because KPI, scorecard and dashboard are used when describing the solution does not mean you must use these PPS elements to expose the data. Doing so can lead to a top-heavy, logic-ridden BI presentation layer as a result of the ol' "make it look just like this spreadsheet, please". Please try and avoid this situation at all costs. The square peg, round hole approach will work if you bash at it hard enough, but just how reusable will your elements be? How sensitive to change will your solution be when users start to wake up to the potential of BI and come up with all sorts of new ideas? I like Bill Inmon's quote of BI end users: "Give me what I ask for, then I'll tell you what I really want".

Dashboards

Whether you agree with someone's use of this word is immaterial. Try to figure out what they really want when they say "dashboard" before diving into solution mode. Could it be that they're simply referring to a report with a few different data regions and some dynamic navigation interactivity? Maybe. Maybe not... but it's still worth thinking about for a bit.

The dashboard PPS element is a fantastic addition to the product and provides enormous potential to build great dynamic, interactive content that is accessible through WSS. Nonetheless don't let the use of "d" word can muddy the waters as to the best way to deliver the solution.

Keep it Simple

Simple, reusable, modular elements are the hallmark of a good M&A implementation. One of the design goals for the PPS team was to make the creation of elements simple and easy, another was to facilitate reusability of elements. Sure there are a ton of areas where you can add a little bit of extra code (particularly MDX) to create some really cool dynamic behaviour in KPIs, scorecards and dashboards. I'm a big fan of this functionality, so long as it's not overused. The code required should itself be as simple as possible. If you're beginning to see nested IIF statements housed anywhere within your BSWX file, stop - it's too complex. Let your ETL and multidimensional data source do as much of the work for you as possible. Don't tie up all your logic in the front end.

Consider this situation: you have filled your PPS elements to the brim with custom code to get a scorecard/dashboard/KPI looking "just the way it does in the picture" and then the user says "that's great, can you please reproduce that same logic in a separate report?" Where does that leave you? D'oh!

Creating a good dashboard (with any BI application) should be simple and quick. This simplicity and speed of creation is borne of a solid back end foundation, the use of the right tools for the right job and keeping it simple. If you are skirting the fringes of the application and using hard-coded hacks to make things look "the way the user wants them to", take a step back and reconsider your approach.

In Summary

Just because someone utters the words KPI, scorecard or dashboard doesn't necessarily mean that the end solution should be delivered using those precise elements. Again, please note that I am not stating that people are right or wrong in their use of these terms. My point is to be wary that just because someone uses these words to describe what they want doesn't mean you necessarily need to create PPS elements that share the same name.

More often than not users refer to a report with a couple of traffic lights on as a scorecard. Don't assume that everyone is thinking Kaplan-Norton, normalized score calculations, cascading KPIs and weighted rollups when they say "KPI" and "scorecard". They may not thinking along the visual and analytical lines of Stephen Few or Wayne Eckerson when they say "dashboard" either.

Analyse and understand what users want and deliver it with the most appropriate tools. Do not bash the virtual square peg into the round hole with a PerformancePoint golden hammer.

PPS Planning Excel Add-In Blog

nick.barclay@gmail.com (Nick Barclay) — Thu, 17 Apr 2008 14:49:00 PDT

Peter Eb., one of the developers of the PerformancePoint Planning Excel Add-In and very active contributor/moderator of the Planning TechNet forum, has just started blogging. He's already posted some interesting tips on the add-in's functionality and features. Well worth subscribing.

http://blogs.msdn.com/petereb/default.aspx

Two MVP's... who'da thunk it

nick.barclay@gmail.com (Nick Barclay) — Wed, 02 Apr 2008 01:36:00 PDT

Wow. I was really thrilled and honoured at the news I got this afternoon - I have been awarded MVP SQL Server.

Now, you wouldn't know it by his post, but the ever-modest Adrian Downes has also been awarded the title of SQL Server MVP for his efforts in recent times. Instead of announcing his well-deserved award he congratulated me on mine instead. Typical :)

Well done, mate.