Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

Choosing the Right Microsoft Reporting Technology Part 4: PerformancePoint

If you’ve followed this blog series from the beginning then you may have started thinking about which tools would be best for your environment.  If so that’s great and I’m glad I got you thinking, but I encourage you to keep an open mind as we go through the last couple tools because both PerformancePoint and Power View provide some of the most impressive visualizations that the Microsoft BI reporting tools have to offer.

If you’re new to reading this blog series I encourage you to start from the beginning even if you think you have a strong understanding of the tools detailed because you may reconsidered using tools that previous you dismissed as an option.  The tools discussed so far have been:

We have two more tools to go (listed below) and then a final wrap up post where I’ll show you how to use a decision matrix to make quick decisions based on your reporting needs.  The final two presentation layer tools are:

  • PerformancePoint
  • Power View

This week I’m excited to talk about one of my favorite Business Intelligence reporting tools called PerformancePoint.

PerformancePoint

What it is

PerformancePoint is one of those tools that when used right can create some of the most impressive and interactive reports within the Microsoft reporting tools.  While the tool can create reports, scorecards, filters, KPIs and indicators the main goal with PerformancePoint is to create dashboards.  Each of the items mentioned previously are component within a completed dashboard design.  For example, the screenshot below is a dashboard using NFL data which has brought together a filter, KPI, scorecard and two charts.  Each of the sub components of a dashboard are developed first and then brought together the make a completed dashboard.  The tool used for building theses dashboards is called Dashboard Designer and is primarily used by Developers and not end users.

image

I mentioned that the dashboards created by PerformancePoint are highly interactive, which is often dependent on the data source type used.  For example, if an Analysis Services cube is used any dimension hierarchies that are part of that design make it easy for users to drill up and down through that hierarchies.  A user could even completely change the attributes that are being displayed on a report as shown below.  While many data source can be used in PerformancePoint Analysis Services is preferred because it gives you the most bang for your buck when it comes to the interactive features.

image

If a user browsing the dashboard wants to change the report type within two clicks it can be changed to reflect their preference.  So if User A prefers bar charts and User B prefers grids that flexibility is built into the tool.

image

Another neat feature of PerformancePoint that users love is the Decomposition Tree.  Simply by right clicking on a value in a report you can launch a Decomposition Tree.  This part of the tool allows users to dig in deeper into the data so they understand how they arrived at the number the report displays.

In the Decomposition Tree shown below I started with all plays in the 4th Quarter(still looking at football data) and then I decided to look at those play by Down.  When I saw first down had the most plays (as you might expect) I decided to navigate through my team hierarchy that was in my cube, which stored the Conference, Division and Team.  Now at that level I wanted to see how many plays in this tree were runs vs. passes.  You see very quickly I was able to break down this information in an impressive visualization that helps me understand my data better (if you look closely it also explains why I’m a disappointed Jaguars fan).

image

Now that you’ve seen the end result of PerformancePoint development let’s talk about the tool at a higher level.  PerformancePoint is a service within SharePoint 2010.  So yes that means you must have SharePoint to use it.  The tool did exist in 2007 but was not as nicely integrated into SharePoint as it is now.  PerformancePoint originated as part of Proclarity but as you can see now fits nicely into Microsoft Business Intelligence.  Mark Stacey gave a brief history of PerformancePoint in this post.

The quickest way to get started with the tool is to use the SharePoint 2010 template site called Business Intelligence Center which has all the components needed to begin development.  Any objects created in PerformancePoint are saved into a SharePoint library called PerformancePoint Content List.  That means by default nothing you developed is saved locally although you can optionally save a workspace file on your machine with the content.

What it isn’t

While the final result of PerformancePoint is highly interactive for end users this is not a tool that the end users will actually using to develop their own reports.  So unlike Excel if a user wants a new dashboard created they will likely have to involve IT.  A power user could potentially take part in development, but I describe in the limitations section further down why I don’t recommend that.

I would not consider PerformancePoint an ad-hoc reporting tool although it does have some features described earlier that allow users to change a reports type and the content it displays.  The reason I don’t consider it ad-hoc is because the user is still limited to what the developer placed on the dashboard as far as filters, KPIs, scorecards, etc…

Also, the dashboards you develop in PerformancePoint are meant for high level reporting so it’s not a great idea to place detail level reports on a dashboard.  You may see performance issues if you do and because it is a detail report it will likely take up a lot of restate on the dashboard.  So an example of this would be you may use a scorecard that analyzes orders by year and product category, but you wouldn’t put line item detail information about an order on a dashboard.  Something like that may be better suited for Reporting Services.

Last, if you reports require a lot of customizations like column name changes or special colors on chart then you’re out of luck.  Basically whatever metadata comes from the source is what you get in the report.  So be sure to name measure and dimension attributes in your cube properly before bringing them into PerformancePoint.  Same for chart colors, there is no way to change the default colors that are provided to you in a bar chart for example.  If you need that kind of flexibility then again Reporting Services or Excel may be the tool you’re looking for.

Who Uses it

From the perspective of the report consumer the user can vary drastically.  Often PerformancePoint is thought of as an executive level reporting tool but I’ve seen users range from executive team members, department heads and even lower level managers.  In fact, I have done work for a major retailer that exposed PerformancePoint dashboards to the highest level of management and to individual store managers so they could make decisions that would impact the sales floor immediately.

From the perspective of the report author this is a developer tool.  There are a couple major reasons why end users typically do not develop PerformancePoint dashboards:

  • The end result is often a highly visible executive level report that should be under the care and maintenance of IT
  • Dashboard Designer, the tool used for creating PPS objects, uses a lot of terminology that is MDX related (Not end user friendly)

Having said that I have seen some corporate environments where a highly technical power user uses Dashboard Designer to create PerformancePoint dashboards after a little training.  They would of course need to have a strong understanding of how the data source is structured.

How is it consumed

Anything developed in PerformancePoint (at least in the current incarnation) can only be consumed through SharePoint.  In SharePoint 2010 the tool is already part of the installation process so after configuration you are ready to start.  In SharePoint 2007 you had to installed PerformancePoint 2007 separately which was a painful and tedious process.  Without SharePoint unfortunately you are out of luck.

Limitations

While the product that results from PerformancePoint can be impressive the limitations you will find while developing can form a pretty hefty list:

  • Filtering dashboards can only be done by the report objects of Filters and Scorecards.  Meaning you cannot have a bar chart filter a scorecard when you select something.
  • Analysis Services drillthroughs have a lot of special conditions if you plan to use them.  I detailed that here.
  • You’re very limited on visual customization.  Things like bar chart colors cannot be changed.  What you see is what you get.
  • Showing two types of charts on the same report item is not ideal.  Meaning if I want a bar chart with a trend line through it then the trend line has to be a percentage value.  Otherwise it shows up as another bar.
  • Export options are limited.  You can only export items to either PowerPoint or Excel.

And there are a few more I haven’t listed but I think you get the idea.  Despite these I do really like the tool and most people find a way to work within the limitations when creating dashboards because it is generally the best tool for that purpose.

Summary

As we go through this series remember these high level characteristics about PerformancePoint

  • Used for creating dashboards
  • Only consumable through SharePoint
  • Analysis Services data source is preferred
  • Ideally not developed by end users
  • Highly interactive visualizations
  • Limited customization

I hope you’ve found this helpful and stay tuned for the Part 4 in this series on Power View. To read any of the other parts to this series follow the links below.

Comments

Leave a comment on the original post [www.bidn.com, opens in a new window]

Loading comments...