Analysis Services KPIs and Reporting Services 2008 R2

Jeremy Kashel — Tue, 25 May 2010 19:40:53 GMT

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.

Tracking PerformancePoint Planning Submissions Using Reporting Services

Jeremy Kashel — Fri, 26 Sep 2008 16:29:29 GMT

The standard operational reports that come with PerformancePoint will allow you to report on a variety of PerformancePoint admin related activities, such as cycles, assignments, forms, jobs and associations.

I find that the assignments report is particularly useful - after all, finding out who has/hasn't submitted is an important part of any data-gathering exercise. Whilst it is useful, I do find that the assignments report is the one that admin users want changed, especially when a model site exists containing many cycles and assignments.

Extra Functionality

With a large PPS Planning implementation you can easily end up with many assignment instances, cycles and users. I've been involved in such an implementation recently, and, due to the large number of assignments, the admin user requested a bit more filtering capability than the out of the box assignments report provides.

Also, the existing assignments report tells will tell you that user A has submitted their assignment, but it won't go into any detail about what the submission actually contained. E.g. did the user submit all their entities? For some users it is quite key to know what other users have been submitting - for one thing it makes navigation easier if as an approver you know exactly which department/entity to pick in the assignment filters.

Examples

By knowing which tables to use, you can write an SSRS report that provides the additional functionality mentioned above.

The starting point is to get the base report query right. In my case, as I'm in a separate auditing database, the query goes inside a custom stored procedure, and is as follows:

SELECT              A.AssignmentId,    C.CycleInstanceName, AD.AssignmentDefName, A.AssignmentName,
                    U.UserId, U.UserName, ENT.Name AS EntityName, CUST.Name As CustomerName,
                    CASE
                        WHEN A.Status = 'partial' OR A.Status = 'Approved' OR A.Status = 'Submitted' THEN 1
                        ELSE 0
                    END AS Draft_Submitted,
                    CASE 
                        WHEN A.Status = 'Approved' OR A.Status = 'Submitted' THEN 1
                        ELSE 0
                    END As Final_Submitted,
                    CASE
                        WHEN A.Status =  'Approved' THEN 1
                        ELSE 0
                    END AS Approved, Approve.UserName As Approver
FROM                dbo.Assignments A 
LEFT OUTER JOIN     dbo.[MG_Planning_MeasureGroup_default_partition] Fact
                    ON A.AssignmentID = Fact.AssignmentID
LEFT OUTER JOIN     dbo.AssignmentDefinitions AD ON AD.AssignmentDefID = A.AssignmentDefID
LEFT OUTER JOIN     dbo.CycleInstances C ON C.CycleInstanceID = A.CycleInstanceID
LEFT OUTER JOIN     dbo.D_Entity ENT ON ENT.MemberId = Fact.Entity_MemberId
LEFT OUTER JOIN     dbo.D_Customer CUST ON CUST.MemberId = Fact.[Customer_MemberId]
LEFT OUTER JOIN     dbo.BizUsers U ON U.UserID = A.ContributorUserId
LEFT OUTER JOIN     dbo.ApproverList AL ON AL.AssignmentID = A.AssignmentId
LEFT OUTER JOIN     dbo.BizUsers Approve ON Approve.UserID = AL.ApproverUserID

You can figure out most of the tables to use by looking at a view called AssignmentsView within the application database.

One thing that I have taken into account is assignment definitions. If you have large number of users completing an assignment, then the chances are that you will have set up an assignment definition that points at a business role or a submission hierarchy. You ideally want to be able to filter on the assignment definition to return all assignment instances that belong to that assignment definition. Therefore, in my case I have three filters for the report, but you could easily add more:

The final view is a report that shows the status of the assignments returned by the filter, but also, when expanded, shows the the entities and customers that the contributor has submitted:

The above is just a taster of what can be achieved. A couple of ways that it can be extended include:

Integrating with Sacha's data auditing idea to provide detailed history on what values the contributor has changed;
Including comments, annotations and deadlines.

Technorati Tags: PerformancePoint,Planning,PPS,SSRS,Reporting Services,SQL Server 2005

Jeremy Kashel's Blog : Reporting Services

Analysis Services KPIs and Reporting Services 2008 R2

Tracking PerformancePoint Planning Submissions Using Reporting Services