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.
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
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.
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.
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.
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.
SELECTThe 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.
All UDFs and supporting objects are created within the PPSMonitoring database in their own schema, cunningly named "MAUDF".
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
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.
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.
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.
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!
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.
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.
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:
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.
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.
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".
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.
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.
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.
