The PerformancePoint downloads page has been recently updated to include a framework on how BI can provided to the IT Department.
Normally the IT Department would be helping out with supporting BI solutions, not actually being end users themselves - but it makes sense when you consider the kind of information that Operations Manager captures.
As this video explains, the end goal is to create solutions that will allow effective monitoring the IT infrastructure. An example of the kind of the kind of dashboards that can be produced is shown below:
There is white paper and also a sample solution available for download to learn more.
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.
One of the great features of PEL is that you can choose either a SQL or MDX implementation for your rules, depending on what you want to achieve. Whilst the MDX rules are much less restrictive than the SQL rules, they can sometimes run slower, depending of course on how your environment is set up.
When the MDX rules do take a long time to run, it's possible that you might see this message:
What has essentially happened within Analysis Services is that an object (e.g. a cube or dimension) has been processed and is waiting to commit. Unfortunately this is not possible whilst an existing query is running, so therefore AS waits for the query to complete. It will, however, only wait so long, which is defined by one of the Analysis Services Properties called ForceCommitTimeout. Once this threshold has been reached, then the offending query is canceled, resulting in the error message above.
Finding the right balance for the Analysis Services ForceCommitTimeout and the PerformancePoint PAC 'OLAP Cube Refresh Interval' setting is key. If you have set PPS to re-process its cubes too often then you may well see the above message. On the other hand, if you set the ForceCommitTimeout too high, then queries executed whilst the cube is waiting to commit will be made to wait, meaning your big query will get though ok, but other users may see bad performance.
Darren Gosbell has written an excellent post here that provides a thorough explanation of ForceCommitTimeout and other related properties.