Tim Kent's Blog

Triggering Reporting Services Subscriptions

Just a quick one tonight whilst I'm sat in Mcdonalds waiting for Breakdown Recovery :-(  Obviously should have spent 50k on a successful man's car instead of my window cleaner's VW!

I recently had a requirement from a client who had a number of data-driven RS subscriptions; simple enough. However they wanted to only run the subscriptions on completion of their cube processing (which was controlled by an SSIS job).  My solution (maybe not the most elegant but it worked for me) was to use the sp_Start_Job system stored procedure (MSDB). 

As you may know whenever you set up a subscription in RS it creates a SQL Agent job to trigger the subscription.  These jobs are given GUIDs as names which is less than helpful but you can work out which is your job by looking at the create date and the schedule you created for the subscription.  Once you've found it you can re-name it with a more meaningful name and also delete the timed schedule as this is not required.

Next create a simple control table to hold the job names that you wish to trigger. Then create a simple SSIS Package that loops on the control table and use an Execute SQL task to run the sp_Start_Job stored procedure passing the job name as parameter


Finally add this package to your main batch processing package.

When I have a moment I'll upload the package.  Be interested to hear from people who have done it differently...

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.


On Being Far Too Modest....

I suspect most of you who read our blogs also subscribe to Adrian Downes' blog and will have already read that B-IQ Principal Consultant Nick Barclay has been awarded MVP SQL Server.  What Adrian forgets to mention is that he has also been awarded the MVP!!

Both these guys have been at the forefront of pushing MS BI and in particular PerformancePoint to new levels - they are MVP's in the truest sense if the word.

Huge Congratulations from all of us Adatis!

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 Cumulative Update

A cumulative update for PerformancePoint was quietly released a few weeks ago without any fanfare. It's basically just all the (5) hotfixes to date packaged up.  There's no link that lists all the fixes together so I've been through all the kb articles and compiled one (below). 

No doubt Microsoft will roll all of these fixes into Service Pack 1 which is due for release April/May according to the rumours.

This version is 3.0.3711.0. (i've updated the version table accordingly)

Please note this update is available upon request only

Planning Fixes
  • Some writable regions of a form are unavailable when you create the form by using the PerformancePoint 2007 Add-in for Excel Report Wizard.
  • When you use Microsoft Office Excel 2007 together with a protected worksheet, the data entry cell style is not applied to a writable area of the worksheet.
  • When you refresh a worksheet, a workbook, or a matrix in Excel 2007, cells that were unlocked earlier may again become locked.
  • The property value for a filter does not display the filter selection in a form. Instead, it displays the member label.
  • You cannot select the members of a Member View that is used in a Microsoft Excel filter.
  • The page filter tree is always fully expanded when it is opened. This causes performance issues when large dimensions are used.
  • Consider the following scenario:
    • You create a new dimension and add it to a model by using the PerformancePoint add-in for Microsoft Excel.
    • In Excel, you create an assignment to the model.
    • You save and close the workbook.
    • You the open the workbook and try to add an annotation to the assignment.
In this scenario, you receive an unhandled exception error.
  • When you use a SQL rule as part of a calculation, the results that are returned are incorrect.

    Note If you use a Multidimensional Expressions (MDX) query or a native SQL query as part of the same calculation, the results that are returned are correct.
  • When you try to select members from a member view in PerformancePoint Add-in for Excel, you receive the following error message:
Hierarchy contains a problem. Unable to launch member selector.
  • When you try to retrieve assignments, you experience performance issues in PerformancePoint Add-in for Excel. This problem occurs when many users have assignments.
Monitoring Fixes
  • The scoring engine returns a score of 0 if the following conditions are true:
  • You use a stated score.
  • The Target value is NULL.
  • The Excel Services Report adds too much white space to the Web report. The report does not fully use the size that is provided in the PerformancePoint Server Web part.
  • The "Export to Excel" feature and the "Export to PowerPoint" feature do not correctly handle NULL values for images.
  • When you use the Microsoft Excel Export: % formatted data operation, the results appear incorrectly in Excel.
  • When you use the "Export to Excel" feature to export a text target value, the exported text may be formatted incorrectly.
  • If the user has not previewed the Scorecard, the conditional visibility does not work for all languages.
  • When you select the "multi-select tree" display method, the Scorecard is displayed incorrectly for a filter. The Scorecard is typically displayed on an incorrect column.
  • The Show Details option displays incorrect data when an Analytic View Designer is in a multidimensional expression (MDX) mode.
  • When you try to view the PerformancePoint Server 2007 dashboard that is inside an inline frame (IFRAME) tag on a Web page, you receive an "Access denied" error message.
  • When you use the PerformancePoint Dashboard Designer, the stated score generates incorrect results when the target value is text.
  • When you export to Excel in the PerformancePoint Dashboard Designer, you see an incorrect score.

New Adatis Team Member

I was dying to use "New Kid on the blog" for the title of this post but a quick Google found that Andrew Fryer (as well as thousands of others) had beaten me to it!

Anyway my excuse for the awful pun is that we have a new member of the Adatis Community; Jeremy Kashel joins as a Principal Consultant with a huge wealth of BI experience.  He'll be a regular blogger on all things MS BI and PPS so for those of you who don't subscribe to our aggregated feed make sure you add Jeremy's to your favourite reader.  We're absolutely stoked to have him at Adatis.

Just in case you're wondering, Jeremy is second from the right ;) And Sacha actually still has a jacket like the red one!

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:

To 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 sources
Dashboard 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

You 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

If 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

To 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.

From 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.

Oooh - See the pretty report ;)

Users love pretty things! Especially upper management who have minions to actually make sense of the numbers behind the reports for them ;)  Out of the box Reporting Services reports look anything but pretty! Here's few tips to make your bosses happy :)

  • Use a background image as a border for charts. The image behind the chart below is a simple png file of only 8kb in size.  To make this work you need to use a rectangle object with the background image set to your image and the chart dropped in the rectangle

  • Use custom palettes for your chart series.  To do this set up a new table with two columns, one that relates to the key's in your data source and one with the hex codes of the colours you want to use.  Then in your recordset join the id to your data.  Once you are returning the hex value for each row to your RS dataset you can use this to define the series colour:

1) Right click on chart -> Properties

2) Select the data tab then edit the value

3) Select the appearance tab then the series style button

4) Select the fill tab then use an expression to

5) Set the color property to your dataset field

6) Click OK, OK, OK, OK (phew!)

I've uploaded a sample project to show how this is done. 

  • Use black borders and lines sparingly (silver is always a good one).
  • Put RS in Sharepoint Integration mode - the toolbars and filters are much nicer and auto-hide (sort of!).  Make sure you consider the limitations of this mode first though.
  • Avoid using 3d perspectives in your charts - this is the easiest way to make your charts fugly (IMO)!

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:

Proclarity Slicer

  • 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...

PerformancePoint Planning Assignments Web Part Update

Thanks to all those who have requested a copy of our Planning web part and extra thanks to those who've provided feedback.   Hopefully we'll get a chance to implement some of your great suggestions in the next release.  We've released a V1.1 with a couple of minor fixes - in particular the ability to handle applications with spaces in the name.

You can download the file here:

Planning Assignments Web Part V1.1.zip (1.17 mb)

Keep that feedback coming :)