Adatis BI Blogs

Dynamic Dimension members on a PerformancePoint KPI

One of our customers had read Nick Barclay's post on dynamic sets in SSAS 2008 and was hoping that this would mean that you could create KPI's with dynamic dimension members.  Well the answer is yes and no.  It's already possible to do this in Monitoring (or should I say PerformancePoint Services) using custom sets in the scorecard designer (more on this below).  However in PPSM these sets are resolved at the point the Scorecard is rendered in the browser. This is fine as long as the members of your sets are not affected by the filters applied to your scorecard (member.children for example) - unfortunately the set does not get re-queried when you change a filter.   For instance if you were to create a set of your top 10 customers and drag that onto the rows of your scorecard, changing a time filter will not cause the KPI dimension members to change even if you've used time.currentmember in your set definition.  So you may end up displaying the Top 10 customers for the current month which may be different to  the Top 10 for the selected time period. Update: Please see Nick Barclay's comment below for a very neat solution to this issue using filter link formulas.  (Wish I'd thought of that!) Custom sets in the scorecard designer aren't the most obvious thing to use nor are they very user-friendly.  Your best bet is to use a tool like SQL Management Studio/Mosha MDX Studio to design a query that you know works then paste out the MDX for the set into the custom set formula editor.  You access this by dragging the Custom item in the Details pane onto the relevant position on your scorecard: Paste your set query in to the pop-up dialog.  For Example: TOPCOUNT( [Product].[Product Model Categories].[Subcategory].members, 10, [Measures].[Internet Sales Amount] ) You can then use the update button on the edit tab of the ribbon to see the results.  Unfortunately there's no way to edit the custom set once you've added it.  You have to delete the dimension members and then add a new custom set.

PerformancePoint Monitoring - Hide empty rows in a grid or chart by design

So as the dust settles and everyone calms down, it's time to move on and get back to what we're good at! Just in case anyone was worried, we'll continue to be at the cutting edge of PerformancePoint Services and the MS BI stack as well as looking at SharePoint in more detail. I thought I'd post this one as it's come up with a couple of our customers recently and isn't entirely clear at first glance.  When you design an Analytic Chart or Grid in Dashboard Designer by default empty rows/columns (or series on a chart) are shown.  If you want them to be hidden when the user first opens the chart/grid then click the browse button at the bottom right of the design pane: The preview window will appear.  Now click either of the two end icons on the toolbar (marked by the red rectangle below) to hide the empty rows/columns (or both) When you click OK the setting will be saved into your design - you can verify this by going into the MDX view of the query and checking that "Non Empty" now appears in your MDX.  You can use the same method to apply sorting by right-clicking on a column in the browse window and selecting the relevant sort function. Of course if you are using an MDX query then use the NonEmpty/Non Empty functions directly. As I've pointed out before, don't forget the built-in Dashboard Designer help which is pretty comprehensive  

PPS Monitoring - Missing Parameters using a Reporting Services report in SharePoint integrated mode

If you're running Monitoring with SP1 applied and working with a Reporting Services report in your dashboard that comes from a report server in SharePoint integrated mode, you may experience an issue where your parameters don't appear in the Report Parameters section. There's a hotfix available for this issue upon request from MS support:

Great Dashboard Design

PerformancePoint Monitoring has made it really easy to build and publish basic dashboards.  However designing really great dashboards is something that very few people achieve.  Despite my recent post on making your reports pretty, I'm not a fan of bells and whistles - in a pre-sales/sales situation they definitely have their place - but in a production dashboard it's hard to justify. A dashboard needs to deliver the right information in an easy to read and and easy to interpret manner.  Sounds simple right? IMO it's easier to come up with a bad dashboard than a good one but a good dashboard maybe harder to sell than a bad one particularly when the budget holder isn't going to be actually using it. One of those that has mastered dashboard design is Stephen Few and I'm unashamedly going to crib from his excellent book "Information Dashboard Design - The Effective Visual Communication of Data" for this post.  This is one of my favourite IT books and I urge anyone who works with any type of report to get a copy.  It's got loads of examples of dashboards and makes it clear why each is good or bad.  I guarantee you will design your dashboards differently after reading; use pie charts in your reports? You won't after reading this book! Anyway this post isn't supposed to be a free ad for Stephen's book, it's meant to give a few simple tips on making your dashboards a little better so here goes: Don't exceed a single screen - that bit of information that is off the bottom of the screen is less important, right? Wrong! You might even give the wrong message by leaving part of a report off the screen.  Most dashboard tools make it easy to provide navigation to detail so think more about why you need to put the information together.  And consider your layout - the brain naturally gives least attention to the bottom right area. Don't overuse colour or decorate unnecessarily - My post about adding pretty borders to your reports is a great example of unnecessary use of decoration;  What value does that border round the reports really add to the user-experience?  It often distracts from the real information.  Does a traffic-lighted map of the UK really add any value where a bar chart is much more readable? "Dude! - your dashboard looks like a real car dashboard! coooool".  And Sacha will kill me for this but please - NO 3D charts!   Your company's logo here? errr No thanks :) Too much or too little information- By nature a dashboard is a high-level overview - you really don't need sales figures to the penny.  Consider also the context of the data you are presenting:  actual sales for January may not be much use unless you can compare it to budget, forecast or previous information. Use variety only when necessary - dashboard designers often feel compelled to use variety where variety isn't necessary - I'm definitely guilty of this one. It's not a crime to have your dashboard only use barchart and grids.  Don't add a Radar chart just because it will add something different. Highlight Important Data - sounds obvious but not always simple.  You should be drawn straight away to the information that requires your attention the most.  In the key figures area of the dashboard below you can see straight away that Late Arrivals has some issues.  I particularly like the lack of a green icon against the other KPI's. Of course there's so much more to it than these few points and in fact when you consider the complexity of human visual perception perhaps it's a wonder anyone can get it right! So what does a great dashboard look like? Well everyone has their own tastes but compare the dashboard above with the one below and make your own decisions: one is simple, clean, readable and informative! In case you're wondering the lower dashboard was built using XLCubed.  

Advanced PerformancePoint Dashboard Webcast

Alyson Powell Erwin from the PPS Monitoring team did a great webcast yesterday on building advanced dashboards.  If, like me, you were unable to watch it live you can view the recording here.  This was actually the last of a series of PPS webcasts. There's a bit of everything in this one so well worth a watch.  Alyson confirms in the presentation that SP1 is due for release in May and one of the fixes will be passing multi-value parameters to Reporting Services reports - yaayyyy! Unfortunately, from previous correspondence I've had with Alyson, cascading filters will not be making it.  Fingers crossed for V2 as this has been an issue for a few of our clients.

PerformancePoint Monitoring Dashboard Object Security

On the properties tab of every object in PPS Monitoring you will find a permissions section that allows you to assign either reader or editor rights.  These permissions actually relate to two quite different areas: What you will see when you view the deployed dashboard; and What objects you can use and edit to build a dashboard using dashboard designer. For the latter you'll also need be in a suitable dashboard designer security role which I've posted about previously but otherwise the concepts are fairly clear;  Reader will allow you to use the objects in your dashboard and Editor will allow you to edit the objects as well. For the viewing of dashboards things are a little less straight forward: DashboardsTo view a dashboard you'll need to be at least a member of the reader role otherwise you'll get a "dashboard is unavailable" message.  Being in the editor group adds no additional permissions when viewing the dashboard (that i can see) Data sourcesDashboard viewers need to have at least reader permissions on a data source if it used in a kpi or report or you will get an error message ScorecardsYou must be at least a reader on a scorecard to be able to view it in a dasboard otherwise it is just not displayed.  This, as with reports also, can obviously cause an issue with the layout of your dashboard as things will get moved depending on your permissions.To be able to add comments to KPI's you need to be a member of the editor role but only be a reader to view them KPIsIf a KPI is used on a dashboard that you have access to, the kpi will be displayed but you must have at least reader access on that particular KPI to see any values otherwise they will be blank.  Strangely, if you have editor permissions on the scorecard you will be able to add a comment to the KPI whether you have permission to see it or not ReportsTo view a report on a dashboard you need to be at least a reader.  If not the report will not appear at all (no message).  No additional permissions seem to be available in the editor role. IndicatorsFrom a display point of view Indicators inherit permission from the kpi they are displayed in, so there is no need to set any specific user permissions.  This seems to be the only area where any form of permission inheritance is used. Note that all roles can either use Groups or specific users. Note also that you only need to publish dashboards to PPSM server to update security permissions - there is no need to re-deploy to Sharepoint unless you have changed the layout of the dashboard.

ProClarity and Reporting Services reports in a PPS Monitoring Dashboard

So I was going to do a post on displaying a reporting services report in a PPSM Dashboard but then realised then that the PPS team had already done that: And then I was halfway through a post on how a Proclarity view works in PPSM when the PPS Team posted one on just that! Hey guys - leave some for us!! ;) Just so you haven't completely wasted your time reading this, here's a couple of additional points: Multi-select filters don't work with multi-value parameters in RS. It only passes the first item selected through to RS. There is no concept of cascading filters in PPSM v1 - The dev team know this is much requested feature. So far we've got around this by using a multi-select tree filter and the promise of a fix in the next version!! Your don't need to define anything particularly in your Proclarity to be able to connect a PPSM filter.  Just connect a filter that contains valid members from any dimension in your cube.  I know Alyson has mentioned this in her post but quite a neat feature I reckon If you have a slicer on the background - this will still display within your PAS view as shown below: Remember to consider RS/Proclarity security and kerberos related implications depending on your PPSM security config and environment (Another post coming here i think!) Using these two report types lets you get around a few of the "still to come" features of the built-in analytic chart and grids such as: Customise chart colors - (PC/RS) Use a secondary Y axis on a chart (PC) Use SQL stored procedures as a datasource (RS) Lastly Nick B has just done a great post on how parameters work with a web page report. This opens up pretty much endless possibilities...

Book: Rational Guide to Monitoring and Analyzing with PerformancePoint Server 2007

Christmas came early last week when a copy of the First PPS book to be published dropped through our letter box - Thanks Nick and Adrian :)   For those of you aren't familiar with Rational Guides the format is designed to get you up and running with a product rather than be an in-depth technical reference and this is exactly what the authors achieve with this book.  I suspect that Nick wrote the majority of this book whilst Adrian will have more focus on the upcoming companion book Following the introduction the book is split into: Installation and Configuration; The elements (a chapter on each of the core dashboard elements); and Implementation and Management (Deploying and securing PPSM) and flows nicely from one chapter to the next with writing at a suitable level - not ridiculously technical and not condescending.  The KPI chapter I found particularly useful as this is one of the deeper areas of PPSM. Whilst not wanting to sound like a free advert, this is an excellent book for getting started and reaching a good level with PPS M&A.  If you are looking for a hardcore technical guide to PerformancePoint then: a) other than what's on technet, it doesn't exist yet!; and b) this isn't it. Having said that, the authors point out a number of quirks, bugs and pitfalls of PPS M&A that you might never find out otherwise.  Definitely a recommended read for anyone who wants to get up and running with PPSM One thing to note is that although ProClarity is marketed as the "Analyze" component of PerformancePoint it is not covered in this book - that's easily a book in itself!    

Nick Barclay: PerformancePoint Monitoring Data Source Connection Problems

Whilst I try to avoid just blogging about other people's blogs, this one is too important to miss and the more people who can find it the better.  Nick Barclay (who just in case you don't know is the author the upcoming Rational Guide To Monitoring and Analyzing with Microsoft Office PerformancePoint Server 2007) has done a definitive post on Data source connection problems in PPS monitoring. I reckon it's a pretty safe bet that "Why can't I connect to a data source?" will become the most asked question around PPS Monitoring.  Nick's post has the answers!