Choosing the Right Microsoft Reporting Technology Part 6: Making the Decision

We’ve completed reviewing the major Microsoft tools and now you have to make “The Decision”.  Which tool(s) is the best fit for the reporting needs?  Remember just picking one tool and using it for all reports may not be the best decision because you are missing out on a lot of the benefits of other tools. 

One tool I like to use that helps me make a decision like this is a decision matrix.  With a decision matrix you provide the possible options, which for us has been the following tools:

You also provide the factors that are important to you in making a decision.  You can see in the screenshot below I’m considering tools based on:

  • Ad Hoc Reporting
  • Content Sharing
  • User Flexibility
  • Developer Flexibility
  • Time to Develop

image

Within the matrix you rate each of the tools (0-100) in their capabilities of the factors you’re considering.  This rating should only be done once. 

Some factors to making a reporting decision may be more important than others depending on the need.  For example, for one report Ad Hoc Reporting may be the most important thing while another report Content Sharing may be the most important thing.  That’s why you sign a relative weight (0-10) to each decision you need to make.  That way if Ad Hoc Reporting is most important than rate it higher than the other factors.  Again, this weighting system would be changed for each report you need to make a decision on choosing a tool.

Download a sample Decision Matrix here:

http://devinknightsql.files.wordpress.com/2012/04/decision-matrix.xlsx

Using a tool like a Decision Matrix can help you make decisions quickly and with more confidence.  I hope this series has been helpful.  If you need to catch up on previous posts follow the links below.

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.

PerformancePoint – Using SSAS Actions

The latest project I worked on had a heavy dose of PerformancePoint and one of the requirements was to launch Analysis Services Actions from PerformancePoint dashboard objects.  I knew this was possible but did not no all the limitations that exist when you want to do this.  Most of the available post reference PerformancePoint 2007 so I thought it was time to update the topic. 

I mentioned there are a lot of caveats to doing actions in PerformancePoint so I came up with this quick flowchart to first see if you can even consider doing them.

Yes at first is was confusing enough to me that I needed this!

PerformancePointActionsFlowchart

So assuming you meet the conditions above in the diagram let’s show you how to enable action usage. 

First we’ll look at the scorecard which can only use Drillthrough actions from Analysis Services.  Drillthroughs are simple enough to create so I won’t focus on that but a couple things you’ll need to make sure are set here are Measure group members cannot be set <All> for PerformancePoint to recognize it and you need to change the Default property to true.  The Default property is a little hidden because it is in the Additional Properties section at the bottom of the action.

image

Once the Drillthrough is setup properly in SSAS then you must build your PerformancePoint KPI so it will pickup the action.  This means that in Dashboard Designer you must change the Calculation property on each field on your KPI that you wish to allow users to launch the action must be set to Data value.  This is a change in the property value name which was called Source data in PPS 2007.

image

When you save the KPI your scorecard should automatically be updated.  Only any field you changed you can now right-click on and select Show Detail, which will launch your SSAS action.

image

Analytic grids and charts do not require this KPI change.  They can do URL, Drillthrough and Reporting Services actions by simply right-click on the measure you want to get details on then go to Additional Actions and select the Action name. 

image

So just to just to summarize if you’re still having problems doing actions in PerformancePoint here are your requirements:

  • You can only use actions that are regular measures (Cannot be a dimension attribute or calculation).
  • Scorecards can only do Drillthrough actions but the action you create must be the default.
  • Analytic Grid and Charts can do URL, Drillthrough, and SSRS actions.  Drillthrough action does not need to be set to the default to use here.
  • The SSAS action cannot be apart of the <All> Measure Group Member
  • If using an unattended service account for PPS make sure it has read definition on the cube.
  • Make sure you are in the visitor permission group on the SharePoint site.

Creating a PerformancePoint 2010 Data Source

In a previous post I wrote about getting started with PerformancePoint 2010.  In that post you learned about what PerformancePoint is and where to open in in SharePoint 2010.  Your next steps are to configure PerformancePoint which you can find a good guide on TechNet for.  Some of these steps listed may have already been configured if you ran the configuration wizard following the installation of SharePoint.  Many installation guides recommend not using the configuration wizard, like when following the PowerPivot  guide for SharePoint installation.

Once the configuration is complete you are ready to create a data source.  Open the PerformancePoint Dashboard Designer as shown here.  If this is the first time you’ve opened Dashboard Designer the tool will download automatically.  If it doesn’t open be sure that your SharePoint site is listed as a trusted site in your web browser.

When the designer opens you should see a Data Connections folder in the Workspace Browser.  Next you will right-click on this folder and select New Data Source.

You can then select the data source type that will be used in your dashboard design.  For all of the blog posts following this one I will demonstrate the tool using the Adventure Works DW 2008R2 Analysis Server database found at codeplex.com.  If you want to follow along pick the Analysis Services template

After selecting Analysis Services hit OK and the data source editor will open.  For the most part his will look like any other data source you have ever configured.  You will identify the server, database, and Cube.  The one major difference here is how access to the cube will be granted.  You can use either the Unattended Service Account method that was setup in the first paragraph of this post or use the individual user identity.

Once you’ve completed the configuration of the data source you can right-click on it in the workspace browser to rename and save it to the SharePoint site that you are currently connected to.

Getting Started With PerformancePoint

PerformancePoint is a great tool for monitoring and analyzing the performance of your business at a high level but also has the ability to dig into the details.  It makes it simple to create quick dashboards using KPIs, scorecards, charts and graphs.  PerformancePoint 2010 has also added a very impressive feature called a Decomposition Tree (shown below), which makes it possible to see what is driving your company’s numbers.  PerformancePoint has come a long way since its origin.  You can learn a little about those origins from  Mark Stacey  who has a great post on the timeline of how PerformancePoint came to its current existence.

When talking with BI specialists I find that more often than not they think PerformancePoint is a very intimidating tool to pick up.  Fortunately, it is not a difficult tool to understand but I think the reason it is so daunting to many people is because of the integration with SharePoint.  SharePoint seems to be the Achilles heel of BI developers but has become more necessary than ever to understand with the release of SharePoint 2010.

The easiest way to get started with PerformancePoint is to create a new SharePoint site using the Business Intelligence Center template.  This site template already has a PerformancePoint content list built in so all you have to do is select PerformancePoint content from the navigation pane and then hit Add new item.

When you hit Add new item it will take a few moments but will automatically download and open the PerformancePoint Dashboard Designer.  You’ll notice that PerformancePoint has the same looks and feel of other Office tools.  The Office ribbon is utilized here just like it would be in Excel or Word.

In this post on getting started with PerformancePoint I just wanted to introduce you to tool.  Look for my next post that will walk you through creating your first objects in PerformancePoint.  For more information on PerformancePoint make sure to pre order my latest book that focuses on the whole BI stack including SharePoint BI.

Follow

Get every new post delivered to your Inbox.

Join 37 other followers