Nick Barclay: BI-Lingual

Jason Morales' Microsoft BI Update Blog

noreply@blogger.com (Nick Barclay) — Wed, 11 Jun 2008 18:00:00 -0500

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

noreply@blogger.com (Nick Barclay) — Mon, 09 Jun 2008 18:37:00 -0500

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

noreply@blogger.com (Nick Barclay) — Sun, 08 Jun 2008 22:57:00 -0500

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)

noreply@blogger.com (Nick Barclay) — Mon, 02 Jun 2008 18:02:00 -0500

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

noreply@blogger.com (Nick Barclay) — Mon, 19 May 2008 17:49:00 -0500

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

noreply@blogger.com (Nick Barclay) — Thu, 08 May 2008 16:00:00 -0500

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?

noreply@blogger.com (Nick Barclay) — Fri, 02 May 2008 18:45:00 -0500

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?

noreply@blogger.com (Nick Barclay) — Fri, 02 May 2008 18:30:00 -0500

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

noreply@blogger.com (Nick Barclay) — Thu, 17 Apr 2008 16:49:00 -0500

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

noreply@blogger.com (Nick Barclay) — Wed, 02 Apr 2008 03:36:00 -0500

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.

Creating Dynamic Web Page Reports

noreply@blogger.com (Nick Barclay) — Thu, 28 Feb 2008 15:51:00 -0600

As a follow up to my post on debugging filter links with web page reports I thought it would be worthwhile to put together a quick example to illustrate just what an unsung hero the web page report type is. Just think of the web page report as the catch-all report type. Basically, if you can't do it with one of the many pre-baked M&A report types then the web page report is what you want.

In the debugging filter links post we saw how dashboard items communicate with each other by embedding data in the Request.Params collection. In that example we simply displayed the contents of this collection. The next logical step is to actually do something with those contextual values we are passing to the report item at runtime. Remember that all the web page report does is send the user to a static URL, the dynamic part comes from two places:

the incoming filter links configured on the report item embedded in the dashboard
what is done with this filter link data by the code contained in the page we send the user to

Web page reports pretty much opens up the entire .NET framework to us, the possibilities are virtually limitless.

For example, let's imagine (in a somewhat bizarre set of circumstances) that our users want a dashboard page that will allow them to select a which search engine they wish to use. They wish to select their search engine of choice using a dropdown. the home page of the search engine name they select will appear in the dashboard and they can then search to their hearts content.

1. Create a tabular data source for the search engine filter

This will be done using a simple Import from Excel 2007 Workbook tabular data source and manually entering the names of three lesser-known search engines. Please note that they are arranged in alphabetical order, nothing more :)

Note the Column Name property in the shot above is set to SiteName. This value could be anything but be aware that when creating filter links from a tabular data source filter it is this column name value that will be used as the name of the item that is added to Request.Params.

2. Create the redirect ASPX Page

Next is to write the code for our redirect page. This will be the page that we point the web page report towards and contains the logic that makes our report dynamic.

In this example I have called it SearchEndineRedirect.aspx. Once completed drop the file (or a copy of it) into the %Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\Root\ directory.

Here is the code for that page.

<%@ Page Language="C#"%>

<%
 if(!string.IsNullOrEmpty(Request.Params["SiteName"]))
 {
 string SearchEngine = Request.Params["SiteName"];
 string WebSite = "http://www." + SearchEngine + ".com";
 Response.Redirect(WebSite);
 }
 {
 Response.Write("No filter values available yet - create a dashboard and configure a SiteName filter link on this report");
 }
%>

All this code is does is look for the item named SiteName in the Request.Params collection and then concatenates the value contained therein with a "http://www." in front and a ".com" at the end. We then redirect the user to this newly-generated URL.

3. Create a web page report

Configure the URL property to point towards the newly-created SearchEngineRedirect.aspx page (http://localhost:40000/SearchEngineRedirect.aspx) as seen below.

Remember, all we're doing is sending the user to a URL, it could be any URL. Preferably, though, we want it to be a page that has been created with the intention of doing something useful with the filter link values that are going to be passed to it. Remember, we can also pass static parameter values to our report via the web page report URL. This technique is detailed in the debugging filter links post

Note that our C# code from step 2 checks whether the SiteName item of Request.Params collection IsNullOrEmpty. In essence we're checking whether this item exists in the collection and handle things from there. By doing this we avoid a stack trace error being displayed in the DD interface when configuring the report because, at this point, the SiteName item can't exist; we need to create the filter link in the dashboard first. Handling this unavoidable situation elegantly helps in confirming that our ASPX page is ready for use.

4. Create and publish the dashboard

Create a simple 1 page dashboard and create a Tabular Values filter referencing the Search Engines data source and using the SiteName field configured in step 1.

5. Deploy the dashboard to the preview site and test

Now all that is left is to admire our handiwork. As you can see from the shots below the user's interaction with the dropdown passes the selected value to our SearchEngineRedirect.aspx which then generates the URL and redirects them to the appropriate page via the web page report.

The sample workspace and .aspx file used in this post can be downloaded here.

Debugging Filter Links with Web Page Reports

noreply@blogger.com (Nick Barclay) — Sun, 24 Feb 2008 04:30:00 -0600

Often when hooking up and testing dashboard filter links you may find the need to see precisely what value is being passed by that filter link. This becomes particularly urgent if you just can't figure out why things in your dashboard are not functioning the way you expect them to.

A few months ago the PPS team put out this post about using web page reports to mimic the absent OverridingURL functionality that used to exist in BSM. It is this technique that I have adapted to create a simple debug page to display the values a filter link passes across to other dashboard items.

Dashboard Filter Links and Request.Params

For those who may not have done much web development (this includes me) the Request.Params collection typically contains things like browser metadata, cookies, server variables and query string values among others. It is a common means of providing page-to-page communication within web applications.

Filters and scorecards pass outgoing filter link data to other dashboard items by appending the data to the Request.Params collection. The receiving dashboard item finds the appropriate Request.Params item it has been configured to look for in the collection and obtains the data needed to drive its behaviour.

What we want to do is simply expose the items in the Request.Params being sent out by either a dashboard filter or scorecard item so that we can see what they're passing across.

1. Create & Configure ASPX page

Create an .ASPX file named RequestParamItems.aspx using the following code. (thanks to Hassan Syed for his help filling the gaps in my C# skills)

<%@ Page Language="C#"%>

<%
 for (int i = 0; i < Request.Params.Count; i++)
 {
 Response.Write(
 "Param Name: " + Request.Params.AllKeys.GetValue(i) + " " +
 "Param Value: " + Request.Params[i].ToString() + " ");
 }
%>

The code contained in the file is simple and can certainly be expanded to do much more. All this version does is iterate through the page's Request.Params collection and write the values out. Once complete copy the file into the %Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\Root\ directory.

2. Create Web Page Report

Using Dashboard Designer create a web page report that references this file that has been dropped in the Root directory. Note the URL property in the shot below points towards http://localhost:40000/RequestParamItems.aspx.

Once set up, the report immediately retrieves the static members of the Request.Params collection. This provides a good way to confirm that your report is configured correctly and is ready to do its job once contained in a dashboard.

There's some interesting data that is brought back at this initial stage but where are the custom filter link values? They don't exist yet, we have to create them in a dashboard first. The web page report itself when viewed in DD will never contain anything more than the default members of the Request.Params collection.

3. Use the web page report to a dashboard

Add the web page report to a dashboard and create incoming filter links on it. A new item is added to the top of the Request.Params collection for each unique filter link source value name (e.g. Display Value, Member UniqueName etc.) that is added to the report. The filter link values associated with each source value name will be contained within the same Request.Params item delimited with semi-colons.

In the shot below I have created 6 incoming filter links on my FilterLink Values Debug report. Unfortunately from a UI perspective the links look like they are duplicated, they are not; the names of the item the filter link originates from is displayed which can be a little confusing. In fact I have added 3 Display Value links and 3 Member UniqueName links one from each of the three filters located above the report.

4. Publish the dashboard and deploy it to the Preview site

Once deployed we can interact with our filters and actually see what those values are via the report. In the screenshot below you can see where the values for the filter selections for Product Color, Geography and Calendar appearing at the top of the report grouped with their source value name.

This debugging functionality should only be used when deploying to the Preview site. It goes without saying (but I'll say it anyway) that the detailed contents of Request.Params will only be confusing to end users.

The real value of having a debug web page report like this published to the Monitoring server is reusability. Whenever you need to troubleshoot filter link values you simply add it to a dashboard and hook up the troublesome filter links.

Debug Filter Link Formulas

Note that our debug web page report can also be useful for displaying the values returned by filter link formulas. The following shot shows the resultant set that is added to the Request.Params collection after the filter link formula has been applied to the Calendar filter link. In this case it is the .CHILDREN of the currently selected filter value - the two semesters of calendar year 2004.

Add hard-coded parameters

It should also be noted that we can manually add values to the Request.Params collection via the URL configured in the report definition. Simply add a "?" followed by the name you want to give parameter and the value to be assigned to it separated by "=".

While the addition of a hard-coded parameter is definitely not a dynamic solution at all it can still be very useful in certain situations. For example, the project I am currently working on requires users be able to write and save textual commentary at the dashboard page level. The .NET team has developed a great comment collector control which they embedded in an ASPX page. We call this page using a web page report and hook up the appropriate filter links in the dashboard. However, each instance of the control needs to "know" what dashboard page it is located on so that the comments entered can be properly associated to that particular page. To solve this we've created an instance of the comment collector web page report for each dashboard page, we simply append an extra parameter to each report URL which passes the name of the page that the comment collector will reside on.

Remember that the URL in a web page report is static, we cannot alter it at runtime. All we can do is tell the web page report where it needs to send the user. What happens after that is up to the code within the page in concert with the contextual data provided by filter links and/or URL-based static parameters.

The sample workspace file and .aspx page used in the shots above can be downloaded here.

PPS Web Service Connection Issues & .NET 3.5

noreply@blogger.com (Nick Barclay) — Fri, 22 Feb 2008 03:45:00 -0600

Recently I came across another connection problem that was not related to data sources or application pool account privileges. We were unable to connect to the web service in Dashboard Designer but couldn't figure out why not. We checked everything. Eventually we tried to simply browse to the web service location in IE. On trying to browse the .asmx we were faced with a descriptive stack trace that gave us more more of an idea of where to look to solve the problem. BTW, it is worth noting that we were working on a standalone PC being using for development, not a server.

The solution came courtesy of Hassan Syed, an excellent .NET consultant I have been working with recently who maintains a blog named "From fragile to agile" (love the name). Without stealing too much of his thunder I will say that the problem was probably related to installing .NET 3.5 long after PPS M&A had been configured and had been working without issue. Hassan has documented what was done to bring the web service back on line here.

It should come as no surprise that a newer version of the framework could possibly cause problems with software that was written on an earlier version. Nonetheless, if you do want to upgrade to 3.5 Hassan's post is worth noting.

Show Details functionality on Scorecards

noreply@blogger.com (Nick Barclay) — Wed, 06 Feb 2008 03:28:00 -0600

A somewhat unknown feature of scorecards is the ability to right-click a cell and drill to the details behind it by selecting Show Details. By default, however, the show details right-click option always seems to be grayed out. Why?

A common assumption is that this is a scorecard scoped setting like the ability to add scorecard comments. In fact, the answer to enabling this functionality lies in the calculation settings of the individual metrics of each KPI contained in the scorecard. In order to enable show details functionality the metric calculation must be set to Source data. The PPS team posted about this a while back. They also wrote a more detailed post a short time ago too that also covers drillthrough actions on analytic charts and grids, definitely worth a read.

Once Source data has been selected the Show Details option will be enabled in any scorecard that contains the metric's parent KPI. Such a fine level of control can be handy for only allowing drillthrough on specific KPI metrics only.

So, the trick to getting the Show Details option ungrayed for a specific scorecard cell is to ensure the KPI metric calculation property is set to source data. However, although the item is now un-grayed... will it work straight away? Depending on the structure of your scorecard you may find that you get the following error:

Cannot show details for this cell because both the row and column must have a selection.

This basically means that the scorecard wants at least one dimension member on its row or column axis so that it has some form of reference from which to perform the drillthrough. Sometimes, though, you may not want your scorecard to have dimension members on present columns or rows; you just want straight KPIs and metrics: a "traditional" scorecard.

Here's the trick to making this work while keeping your scorecard structure the way you want it:

I found that I only needed to have one dimension reference on one axis to get things to work. All you need to do is add a benign dimension member (one that is not vital for the operation of the scorecard) on the top level of the row axis. Once added right-click the item and select Hide. In the screenshot below I added the All member of the Customer attribute hierarchy.

Once the dimension is hidden the dummy All Customers member that we have hidden will display in red. The scorecard has its dimension reference and end users will be none the wiser.

Now that this is done the Show Details gets its dimension reference on one axis (which seems to be enough in this case) and the drillthrough function will return a nice data set to you from a simple right-click and selection of Show details in the scorecard.

Other tips

Don't forget to ensure that the Application Pool account (or the users themselves if you've implemented PerUser security) has read access to the relational database itself and that drillthrough access has been granted on the source cube.

Also remember that you won't have much luck performing a drillthrough on a calculated member or a metric that uses a custom MDX formula.

Downloadable PPS TechNet Guides

noreply@blogger.com (Nick Barclay) — Tue, 29 Jan 2008 19:04:00 -0600

Via Aseem Nayar's posts in the PPS M&A and Planning TechNet Forums:

You can now download the following PerformancePoint Server 2007 guides as Word documents from TechNet.

PerformancePoint MCTS Exam 70-556 Now Available

noreply@blogger.com (Nick Barclay) — Sun, 20 Jan 2008 14:43:00 -0600

Via Adrian Downes:

Exam number 70-556 for PerformancePoint Server 2007 is now available for those interested in adding another MCTS arrow to their certification quiver.

Prometric already lists the exam on its site. Unfortunately, at the time of this writing, Microsoft Learning does not yet have the exam listed. Nonetheless, we have access to Trika Harms zum Spreckel's preview of the requirements from when the exam was in its beta phase here.

UPDATE (29 Jan 2008): the official preparation guide for the exam can now be found at http://www.microsoft.com/learning/exams/70-556.mspx