And another PPS Blog

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?

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?

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:

1. enable hierarchical arrangement & nesting of KPIs
2. manage KPI weights
3. normalise KPI scores
4. 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

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

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

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:

1. the incoming filter links configured on the report item embedded in the dashboard
2. 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

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(
            "<b>Param Name: </b>" + Request.Params.AllKeys.GetValue(i) + "<br>" +
            "<b>Param Value: </b>" + Request.Params[i].ToString() + "<br><br>");
    }
%>

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

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

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

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.

• Planning & Architecture Guide
• Deployment Guide
• Server Operations Guide

PerformancePoint MCTS Exam 70-556 Now Available

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 

PPS Data Connection Security with CustomData

There are two well-documented data source security methods available in PerfornancePoint Monitoring & Analyzing: the default Application Security model and Per User Connections. However, as a wise green creature once whispered, "No. There is another..."

More often than not security requirements for a BI solution are based around certain users only being able to access specific members of several important dimensions. CustomData security functionality in PPS serves this purpose very well and provides a solution that sits between the two already well-known data security methods.

Application Security - a single application pool account is used to make all connections to data sources.

• Advantage: because one account makes the data source connections the monitoring server caches results providing greater speed responsiveness to all users.
• Disadvantage: no data security is possible based on who the user is because all users are connecting via one the account; the database does not know who they are.

For more details on the common connection pitfalls of application security please see this post.

Per User Connections - each user's credentials are used to connect to data sources.

• Advantage: highly secure, naturally this security is dependant on there being some sort of existing security framework within the data source itself.
• Disadvantage: little or no caching of dashboard data can occur as each data request is made with the individual user's credentials. Dashboard responsiveness and speed can take a hit.

Security is important to all the clients I work with but I always advocate careful thought and consideration before simply switching on Per User connections on a PPS Monitoring server. There is definite performance hit that comes from the fact that each user gets his or her own data. For more information on the performance difference between the two principal security methods please see the PPS Performance Tuning and Capacity Planning whitepaper.

At the time of this writing, not much official documentation exists on CustomData security, hence the reason for this post. The only reason I knew of its existence originally was this post on the PPS team site some time ago. In that post lip service is paid to the CustomData option but we're really told much more than "it's possible".

What is CustomData Security?

CustomData security is a middle ground between application and per user security. We still use an application pool account to connect to the data source, but we also have access to data about the Domain\Username of each user who is interacting with the deployed dashboard. We use this user-specific data to drive security dynamically using information configured in the relational database the cube sources data from. This alleviates having to configure and maintain many SSAS database roles tied to specific domain accounts or groups. Instead we configure a single, dynamic MDX based role which will be detailed later.

NB: there is no discernable data caching advantage to using CustomData Security. The performance is much the same as when using the PerUserSecurity model. If performance is the most important factor in a PPS deployment then Application Security is certainly the best performing option.

What is Dynamic Dimension Security?

Limiting user access to certain members of a particular dimension attribute hierarchy based on their windows credentials is nothing new. For example, dynamic dimension security may decree that when I look at a dashboard I can only see Australian states in the State/Province dashboard filter.

...while on the other hand Adrian should only see a list of Canadian provinces.

The majority of work to set up CustomData security involves the creation of a dynamic dimension security framework in your multidimensional data source and the relational source that feeds it, not PPS. If you already have security of this kind set up on your UDM then you're already most of the way there. The set up is minimal on the PPS side of things.

How I set up SSAS dynamic dimension security?

Providing any user-driven security access to PPS data cannot function without some form of back-end, SSAS and relational security infrastructure. There's a little bit to do to set this up, so instead of going into a long-winded walk-through I will instead point to two excellent articles/posts on the topic. Both of these documents cover similar ground. Using a dimension containing user details and a factless fact table to relate each user to the specific members of the dimension they are allowed to see e.g. Reseller Stores. Once the relational database infrastructure and data is in place, a SSAS role is set up containing MDX code to return a set of the allowed dimension members for the current user. I highly recommend reading at least one, if not both in order to full understand the remainder of this post.

• Brian C Smith of Hitachi Consulting wrote this blog post that contains sample T-SQL code and steps to set up a simple UDM.
• Teo Lachev wrote this article for SQLMAG in which he covers a number of different techniques including the factless fact approach.

These are just two examples of how to implement dynamic dimension security. I'm sure there are many other creative ways to implement this kind of functionality.

How do I turn on CustomData security?

As mentioned earlier, the PPS configuration is minimal. Bpm.UseASCustomData is a boolean attribute that can be found in the same web.config files as the Bpm.ServerPerUserConnection attribute. Change the BPM.UseASCustomData attribute from False to True in the appropriate web.config files as listed below.

• Preview Site: %\Program Files\Microsoft Office PerformancePointServer\3.0\Monitoring\PPSMonitoring_1\Preview\
• Web Service: %\Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\WebService\
• SharePoint: %\Inetpub\wwwroot\wss\VirtualDirectories\80\

NB It is very important to remember that the Bpm.ServerPerUserConnection be set to False when using CustomData security. We want to connect to the data source using the Application Pool account, not each individual user. Turning on Per User connections completely defeats the purpose of the security method being discussed here.

What does UseASCustomData do?

When Bpm.UseASCustomData is switched to True any PPS data source connections made to Analysis Services 2005 contain an extra attribute on the connection string: <CustomData>. The CustomData connection string attribute was added to SSAS 2005. The Monitoring server will populate this attribute with the Domain\Username of the user making the request. The data source connection itself is still being made as the application pool account.

This can be particularly useful in multi-server environment where Kerberos is not configured and the double-hop issue is encountered. The information in the CustomData field can now be used to identify the user without the need for Kerberos.

How do I confirm that CustomData is switched on?

Once you have made the adjustments to the appropriate web.config files, set up a SQL Profiler trace on the SSAS server where your cube is located. Use Dashboard Designer and run a Test Connection on one of your SSAS data sources.

As you can see from the shot below the connection to the cube is being made by my Application Pool account, NETWORK SERVICE (in a production environment you would want to be using a specific domain account for the app pool identity). More importantly note that there is a <CustomData></CustomData> attribute that has been added to the connection meta data containing my Domain\Username details.

How do I make use of the CustomData attribute?

In order to get at the Domain\Username string that PPS provides within CustomData we use the CUSTOMDATA() MDX function. The CustomData connection string attribute does nothing more than hold a string of characters. The CUSTOMDATA() MDX function simply returns that character string to us, we can do whatever we want with it from there. There isn't a great deal of information out there about this function but Chris Webb posted about it a while back. In that post he pondered what this function could come in handy for. Hey Chris, I think this is a good one.

In MDX you can gain access to a string containing the Domain\Username of the currently connected user by via the USERNAME MDX function. USERNAME is the MDX equivalent of the T-SQL SUSER_NAME() function. Try running this query in SSMS to return your Domain\Username.

WITH MEMBER [Measures].[MyUsername] AS USERNAME
SELECT
[Measures].[MyUsername] ON 0
FROM [Adventure Works]

The USERNAME function is used in the articles listed above to access the Domain\Username string for the connected user to drive the dimensional security. The functionality detailed by Teo and Brian is relevant when a user connects to the cube "as themselves" i.e. they are accessing the cube with their individual Windows credentials via Excel, SSMS, ProClarity or even PPS with Per User security. Because of this SSAS knows who each user is.

With PPS CustomData security our aim is a little different: we want to avoid connecting to the cube with individual user credentials. Under the application security model the connection is always being made by the same application pool account. In this case the USERNAME function is useless to us. It will always return the same value: that of the application pool identity account (in my example trace above it would return NT AUTHORITY\NETWORK SERVICE every time). When UseASCustomData is switched on, the Monitoring Server sneakily adds the Domain\Username string to the CustomData attribute for each individual user who interacts with a dashboard. Now, if we don't do anything else the string in the CustomData attribute will simply sit there benignly minding its own business.

Here's the trick: using the same techniques detailed by Teo and Brian we replace the USERNAME MDX function with CUSTOMDATA(). This way we are able to access the Domain\Username value in the CustomData connection string attribute to implement the same dynamic functionality without connections being made on a per user basis.

Testing CUSTOMDATA() within Dashboard Designer

Here's another way of testing that CustomData security is configured on the Monitoring server. Within Dashboard Designer create an analytic grid report and enter the following MDX in the Query pane.

WITH MEMBER [Measures].[MyUsername] as CUSTOMDATA()
SELECT
{ [Measures].[MyUsername] } ON 0,
NON EMPTY { [Geography].[Geography].[All Geographies] } ON 1 
FROM [Adventure Works]

Switch to the Design pane to check that your own Domain\Username is returned.

Typically you will not use CUSTOMDATA() in an MDX statement within Dashboard Designer. It is best to keep the MDX code that drives the security within the roles managed by SSAS as described in the referenced articles.

How do I set up dynamic dashboard filters?

Once the Bpm.UseASCustomData attribute is set to True and your back-end SSAS security framework is set up the creation of dynamic filters could not be easier. The heavy lifting is being done by the SSAS roles. The easiest filter to create is an MDX Query filter type. All that is needed to populate the example State/Province filter that limits Adrian and my access to the states/provinces of our respective countries of birth is the following, very simple, MDX.

Note that I do not need to reference the CUSTOMDATA() MDX function here. All that logic should be contained in the SSAS role that defines the security behaviour.

A word on roles

If you have gone to the trouble of setting up a user-driven security infrastructure in your SSAS cube the last thing you want to do is limit the security access to just PerformancePoint monitoring. All other cube browsing applications should be able to take advantage of what has been created. The simple way is to create at least two roles: one (or more) for all users to connect with user/role based Windows security for Excel, ProClarity, SSMS etc.etc. and a second role for PPS data source connections.

The Windows based role/s grants access to whichever domain groups and users require it. This is the general windows security access role for non-PPS connections. Users are recognised by their windows credentials and the MDX USERNAME function is referenced to apply security in the dimension security MDX statements.

The PPS role need only provide access to the domain account used by the PPS application pool identity. This role uses the CUSTOMDATA() function in the dimension security MDX statements instead of USERNAME. The role name is then referenced in the PPS data source definition as seen below so that we can be sure the connection to SSAS is made using the permissions defined therein.

 

A role security MDX tip

I have learned from reading Mosha's blog that you should try to avoid using STRTOMEMBER or STRTOSET functions if possible for performance reasons. The sample code in the dimension security articles use these functions in the dimension security MDX. I have provided some sample MDX code that alleviates the need for the STRTO... functions. Note that the sample code contains references to both CUSTOMDATA and USERNAME functions, you can only use one at a time.

For example, instead of

EXISTS(
[Reseller].[Reseller Name].MEMBERS,
STRTOMEMBER("[Employee].[Login ID].&[" + CUSTOMDATA() | USERNAME + "]")
,"Security Filter"
)

Try using this

EXISTS(
[Reseller].[Reseller Name].MEMBERS,
FILTER(
  [Employee].[Employee].[Employee].MEMBERS,
  [Employee].[Employee].CURRENTMEMBER.PROPERTIES("Login ID") = CUSTOMDATA() | USERNAME
 )
 ,"Security Filter"
)

UPDATE: 29th Feb 2008. David Gustavson, author of the Performance Tuning and Capacity Planning for PerformancePoint Monitoring Server whitepaper has informed me that there is virtually no caching benefit when using the CustomData data connection method described here. The original version of this post indicated that CustomData provides similar caching performance to Application Security - this is incorrect. In fact the cache performance is not much better than using Per User Connections. I have altered the post to reflect this.

Rational Guide to PerformancePoint Planning Now Available

The Rational Press just have let us know that The Rational Guide to Planning with Microsoft Office PerformancePoint Server 2007 is now available. Adrian has a full chapter breakdown and more here. If you've got to get your hands on a copy of the book immediately you should order it directly from Rational Press. You will find that Amazon and other booksellers will not list the book as available for a few days yet.

It's been around 18 months since Adrian rang me and suggested that we write a book on BSM together. Here we are with our third Rational Guide completed, who-da thunk it. Thanks are due to all who helped us achieve this significant milestone. Tony Mann, Jeff Edman and the team at Rational Press; our tech editors Ian Tien, Corey Hulen and Peter Bull. Thanks also to Bruno Aziza and Bill Baker for their support and work behind the scenes.

A very special thank you goes to our respective wives and daughters, Mary, Christine, Angela and Olivia for putting up with those late night, early morning and weekend absences; we couldn't have done it without them.

...and of course, thanks to co-author, colleague and friend Adrian Downes, to whom I remember saying "you want a book on BSM together, are your crazy?!"

Time Intelligence Filter Display Caption Tip

If you don't provide a caption for an item when creating your Time Intelligence filter the TI engine will simply populate the name of the member that the STPS formula relates to.

For example creating a TI filter as seen below with no caption values...

... will result in a filter populated like this

 

The only caveat to the "no caption" approach is if you've stipulated a STPS formula that returns a range of members e.g. Month-7:Month-1. In these cases you should provide a caption otherwise the formula itself will appear in the filter. As you can see in the shot above, the items that get a non-geek-readable name are the range formulas that return more than one member.

On the other hand, if you provide captions...

The captions will be displayed accordingly...

The use of STPS formula captions is not an all or nothing deal. Mix and match with or without captions as required.

For more Time Intelligence / STPS reading have a look a David Francis' excellent post on the topic.

Dashboard Designer Room to Work

With the various panes on the left and right of the Dashboard Designer interface you can quickly run out of room to work, particularly when designing a dashboard as seen in the screenshot below.

Naturally we can minimise the appropriate parts of the designer interface by clicking the double chevron button at the top right of each pane. It is worth noting that Dashboard Designer's ribbon can also be minimised by double-clicking on any of the three ribbon tabs (Home, Edit or Create). To unhide simply double-click on any of the tabs again. This is the same functionality that is native to the ribbon implementations in Word, Excel and PowerPoint 2007.

As you can see from the next screenshot we now have much more screen real estate to get the job done.

It is also worth noting that, once hidden, the ribbon can be made visible by clicking any of the tabs once. This will make it visible for one click of any ribbon button, thereafter the ribbon will automatically hide itself again. Double-clicking a tab locks it back into position permanently.

Scorecard Layout Undocumented Feature

This is an undocumented Dashboard Designer feature that the M&A book's tech editor, Corey Hulen, shared with me. Holding down the SHIFT key when dragging dimensions / sets onto scorecard column or row axes allows you to nest the members under a single scorecard item.

For example, my scorecard has two KPIs, Reseller Order Quantity and Reseller Sales Amount. I want to break down only the Reseller Order Quantity KPI by product category. As shown below dragging the product category to the right of the KPIs displays drop guides against both of them.

Once the dimension members have been selected the corresponding product categories will be nested under both KPIs. Which is not what I want.

Performing the same operation while holding down the SHIFT key allows me to nest the desired dimension members under a single item (as indicated by the single drop guide) on the row axis as seen in the screenshot below.

Now the members I want they are nested only under the relevant Reseller Order Quantity KPI. this leaves the way open to nest a different set of members under the Reseller Sales Amount KPI using the same technique.

This is certainly not ground-breaking functionality, just more of a time-saver than anything else. Sure you can nest the appropriate dimension members without using SHIFT and then manually delete the members that are not required. This just makes life a bit easier.

Rational Guide to Monitoring and Analyzing now available

The Rational Press team have just informed us that The Rational Guide to Monitoring and Analyzing with Microsoft Office PerformancePoint Server 2007 is now available.

Multiple Filter Links on Strategy Map Reports

If you've been wondering why you have been unable to pass more than one dashboard filter link to a Strategy Map report wonder no longer. This is a bug that will hopefully be fixed soon; MS are aware of it. I discovered it while writing the Dashboard chapter of the book.

Scenario

You have a dashboard consisting of a scorecard and a strategy map report. The  strategy map is linked to the scorecard KPI items. You wish to filter both the scorecard and strategy map on your dashboard using two global filters: Fiscal Year and Country. Creating links for both filters on the Page endpoint on the scorecard is no problem. Doing the same on the strategy map is not.

In the RTM build a strategy map dashboard item exposes two endpoints: Filters and Page. Strategy map filter links should all be applied to the Page endpoint. The dashboard UI will allow you to connect filter links to the Filters endpoint but the resultant dashboard will simply not apply the filter - this endpoint is a no-op. The Filters endpoint was deprecated but somehow made it into the RTM build.

The Filters endpoint no-op is a red herring that distracts you from the real problem: after you have connected one filter link to the Page endpoint it is no longer available to be used by a second filter link.

 

We should be able to hook up multiple filter links to the Page endpoint. At this point you can hook up either the Fiscal Year or the Country filter link, but not both. This is not acceptable.

Workaround

The only thing that is stopping you from applying more than one filter link to the Page endpoint is the value of the AcceptsMultipleFilterLinks attribute on the Page EndPoint element within the workspace. It should be True but the designer UI sets it to False. We need to get into the XML of the workspace and change it manually.

1. Save & close your workspace (and take a backup, just in case)
2. "Open with" the workspace .bswx file in Notepad or any other text / XML editor
3. Search for "StrategyMap" in order to find the correct report view node
   
4. Scroll through the <CustomData> element numbers and letters that represent the strategy map object itself until you get to the <EndPoints> node underneath
5. Change the value of the AcceptsMultipleFilterLinks for the Page endpoint from false to true
    
6. Save & close the file and reopen in Dashboard Designer. You can now apply as many filter links to the strategy map Page endpoint as you like

PerformancePoint Aust / NZ Launch Dates

PerformancePoint PM Bruno Aziza just posted the dates and registration links to the Australian & NZ PPS launch events which I have copied and pasted here for convenience.

Melbourne - November 28 - Register here, Agenda here

Sydney - November 29 - Register here, Agenda here

Auckland - December 4 - Register here, Agenda here

 

I'll be presenting the M&A session at both the Melbourne and Sydney events. If you're going to be at either event please come up and say hi.

PerformancePoint Monitoring Data Source Connection Problems

Now that the PPS user groups are live on TechNet I have already seen several posts about data source connection issues. This was similar to posts in the BSM newsgroup on the same topic. I have been meaning to post about this for a while. Now is as good a time as any.

Data Source Connection Failed!

The inability to connect to a data source is all about permissions. Well, duh! Many people using PPS M&A (and BSM) for the first time pull their hair out trying to determine why data source connections keep failing. Most of the time they are looking at their own permission levels as the source of the problem "...but I'm an administrator on all these boxes!!"

A PPS Monitoring server's default, out-of-the-box, behaviour is to connect to all data sources using a PPS application pool identity account.

That last sentence is important so it's worth repeating...

A PPS Monitoring server's default, out-of-the-box, behaviour is to connect to all data sources using a PPS application pool identity account.

It does not matter if your personal administrative account "rules them all" within your IT infrastructure; if the identity account configured in the appropriate PPS application pool does not have permission to connect to and read data from any data source (OLAP, relational DB, an Excel file, a SharePoint list... anything) your connection will fail. Period.

By default PPS is not trying to make a connection as you, it makes the connection as the identity account configured within one of the Monitoring app pools.

NB: this post is all about the default data source connection behaviour of PPS M&A. Per User connections (where you are making the connection as yourself) can be configured, but that is another kettle of fish altogether. Per User connections take a bit of manual web.config work to configure, so you will certainly know if this has been set up (it's not just a simple checkbox that can be inadvertently ticked).

Application Pools

The Monitoring server configuration process creates and uses several Application Pools. For the purposes of this post we are only looking at two: PPSMonitoringWebService, PPSMonitoringPreview. Nonetheless we must also be wary of identity used by the SharePoint - 80 app pool too.When you initially configure your monitoring server you are given the chance to stipulate which account will be used as the identity in all app pools. So whatever you choose will be configured for all. The identity can be changed for each individual app pool as needed. Best practice here is to always use a specifically-created domain account.

PPSMonitoringWebService app pool identity account is used to connect to the Monitoring web service when you are working in Dashboard Designer (i.e. creating data sources, scorecards, KPIs etc.).

PPSMonitoringPreview app pool identity is used by the preview web site. This site allows designers to deploy and preview dashboards instead of having to deploy to SharePoint every time. If you deploy your dashboard to a preview site, any data source connections are made using the identity configured within this app pool.

SharePoint - 80 application pool identity is used to make connections once a dashboard has been deployed to SharePoint. Ensuring this app pool identity has appropriate access to the PPSMonitoring DB and data sources is essential. It is recommended to make the identity account used by this app pool to be the same as the PPS-created app pools. This just saves the hassles of aligning the permission set of two accounts rather than just one.

TIP: When trying to troubleshooting a connection/permission problem of any sort run a Profiler trace to see precisely who is making the connection attempt (and failing in the process). If you have different identities in the various app pools this will at least point you in the right direction as to where the permissions problem may be originating from.

UPDATE (04 April 2008): Thanks to Phil Austin who wrote this post on using IIS 5.1 in Win XP. I did not cover this situation adequately in the original version of this post. Because there are no application pools in this version of IIS you cannot determine which account is being used as the identity via the IIS manager. However a quick profiler trace as recommended above will expose the ASPNET as the account that is trying to connect. Ensure it has appropriate access to the data source and you're right to go.

Identity Permissions

The screenshot below is the most common error seen by many people using Dashboard Designer to create a data source.

[I'm simply repeating the text from the screenshot so that search engines will pick it up and make this post more find-able]

"The PerformancePoint Server could not connect to the specified data source. Verify that either the current user or application pool user has Read permissions to the data source, depending on your security configuration. Also verify that all required connection information is provided and correct"

This problem is easy to troubleshoot if you know where to look. First place not to look is the mirror; the problem here is not your own personal permission set.

Here are two simple troubleshooting steps:

1. Determine what account is configured for the app pool identity. If you have performed a "Next > Next > Next" install this will probably be Network Service. This account is fine for testing when all the bits you need are on the one machine (ServerA). If, however, your SSAS server, where your cubes are housed exists on a separate machine (ServerB) the Network Service account from ServerA will not be able to make a connection to it. As mentioned above, best practice here is to use a purpose-built domain account. Get into IIS and look at the Identity tab on the app pool properties to confirm precisely which account is being used.

2. Impersonate the identity account and try to connect to the data source in question. Leave Dashboard Designer alone for a while. Forget about it. Either log in as, or Run As... using the app pool identity account and try to access the data you're looking for using another application. For example, if you're having problems with a cube try creating a PivotTable in Excel or browse the cube in SSMS. The most important thing here is do not test the connection as you, do it as the identity account - this is what PPS is trying to do. If you can't connect to the data source when impersonating the identity account then PPS doesn't have much of a chance either.

Web Service Connection

Data source connections are made using app pool identities, we know that now. However, in order to determine the app pool identity you first need to connect to the Monitoring web service. If you are seeing the message below then your first problem is web service related, it's not data source (yet).

[Once more repeating the text for search engines]

"Unable to connect to the specified server. Make sure the address is correct"

This is an error that you may see in Dashboard Designer. It means you cannot connect to the Monitoring web service configured in the Server tab of Dashboard Designer options, specifically the Server URL property.

This error is far less common than that of data sources but is still worth mentioning. Often, this is an app pool identity permissions problem, but sometimes it's a result of the web service URL settings within a specific workspace.

App Pool Identity Permissions (again)

If you originally had Network Service (or something else) set up as an app pool identity account and subsequently changed it to a domain account you may have problems connecting to the Monitoring web service. If you change the app pool account to something else manually be sure to grant the new account membership to the BPMDeveloper role in the PPSMonitoring system database.

Workspace URL Setting

Workspace files contain the URL of the Monitoring server they were built against / published to. for example if you have just opened up one of the sample workspaces that ship with PPS M&A you may find that you cannot connect to your perfectly-configured Monitoring web service any more. The sample MS workspaces were all built and tested using a monitoring server called ab004-23. The web service address within these workspaces is set to http://ab004-23:40000/WebService/PmService.asmx. Unless you have a server with the same name in your domain (with a PPS Monitoring Server on it) you will not be able to connect. All that you need to do here is change the server name to that of your monitoring server:  http://SERVERNAME:40000/WebService/PmService.asmx. This is all documented in the ReadMe that ships with the samples but it's worth mentioning here again.

It is also worth noting that if you open an MS sample workspace and then close the workspace, (keeping Dashboard Designer open) the Server URL and Server name properties will continue to point to the monitoring server on ab004-23 (or the most recent workspace file). This is not a problem and is common (by design behaviour) to all workspaces, not just the MS samples.

You have three simple choices:

1. manually change the URLs in the Options > Server tab
2. Open up a workspace you have already worked on that is configured to point towards your monitoring server. The URLs will be automatically populated
3. Close down the Dashboard Designer application and fire it up again. Dashboard Designer is a ClickOnce application. Once initially installed, each time it is subsequently launched it will repopulate the Server URL and Server name text boxes with the PPS Monitoring web service URL of the Monitoring Central site it was last launched from.

In Conclusion

So, the next time you are having PPS data source connection problems, don't immediately think the problem lies within your own personal permission set. Instead think of your PPS application pool identity account saying, as Seinfeld's George Costanza would, "It's not you, it's me."