Adatis BI Blogs

Analysis Services KPIs and Reporting Services 2008 R2

There was a comment on my last blog post by Peter Eb asking about whether Reporting Services 2008 R2 knows how to render SSAS KPIs automatically. The short answer is that it doesn't unfortunately, but there are only a few steps needed to get it working. For this blog post I've built a simple report that uses the Adventure Works DW 2008 R2 Analysis Services database as a data source. The first step is to build a data set that contains a query that references the Month attribute and the Channel Revenue KPI: I'm interested in reporting on the status and trend part of the KPI, so I've added the month and value parts of the KPI, plus blank columns for the status and the trend: For the status and the trend columns, the next step is then to drag on (or right click and insert) an indicator in each of the cells. This is when the manual steps come in: Right click on the indicator to open the properties window; Set the Value of the indicator to be the Status part of the Analysis Services KPI; Change the States Measurement Unit to Numeric; Configure the indicator Start and End properties to be -1, 0 and 1. SSAS KPIs can have an unlimited number of status values, so it may be necessary to add more states here if SSAS returns more than 3 values for the Status. This changes that are needed to the indicator properties window is shown below: Applying the same method to the Trend part of the KPI produces the following simple report: This post wasn't really supposed to compare SSRS Vs PerformancePoint Services, but I've found out that PerformancePoint Services does pick up the SSAS KPI meta data (slight mismatch on the colours only), saving you having to set up the indicator yourself: It's a shame that the KPIs aren't picked up automatically by SSRS, but then again the above process isn't too difficult to master.

PerformancePoint Services 2010 – First impressions

We’ve been lucky enough (or we rather hassled enough people in MSFT for long enough!) to have been participating in the Office 2010 technical preview for the last couple of months but as it’s all been under NDA haven’t been able to blog about it.  This also means that we’ve had a chance to look round SharePoint 2010 and, in particular for me, PerformancePoint Services.  Nick Barclay has just done a series of posts about what’s new/improved/different in the new version so go there for the full list.  Here’s a quick round up of our first impressions: What looks good so far: SharePoint integration – Whilst Dashboard designer is still pretty much the same product for doing your, errr, dashboard design it’s no longer the admin and security tool as well.  This is all carried in SharePoint and in fact you have to set up a specific PPS site to do this.  A great deal of effort has obviously gone into this and which looks to have paid off. Security is all through SharePoint – no need to set up permissions twice! AS Conditional formatting now works. Decomp tree is back!! Measures can now be formatted independently. Workspace browser is now much more intelligently organised. Filter by value – you can now restrict rows\columns by value Dynamic dimension measures on scorecards- this was a bit of a workaround in 2007 as I’ve posted about previously. This now works properly Re-usable filters – Filters can now be shared and re-used across dashboards Disappointments: Lack of improvements for data visualisation – Very disappointing - other than the decomp tree, the visualisation side of PPS has changed little.  Still no real control over how your graphs look. The only other new item to be introduced is the Pie Chart!!!! oh dear.  Still no bar charts (and I mean Bars not Columns), no chart formatting options or (controllable) second y axis options that I can see :( Decomp tree is not a chart type but a right click option from a deployed report.  i Like the option to do this from any point in a report but would be nice to have both options. It’s still called PerformancePoint! – I have to admit I when I read another blog of Nick’s following the demise of Planning I didn’t entirely agree with him that it should be renamed.  Having spent the last ten months trying to explain to various IT departments that PerformancePoint is not the devil and that the Monitoring side has not been affected (usually to no avail) has changed my opinion completely.  As per Chris’s blog – ProClarity just seems to have disappeared – I know that was never what Monitoring was supposed to be but the lack of an ad-hoc cube browser is a huge oversight. Did I mention the lack of data visualisation improvements???? There’s lots more to discuss and there will be more to come over the next few weeks time allowing.  SharePoint 2010 looks pretty impressive…