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:

• Reporting Services
• Excel
• PowerPivot
• PerformancePoint
• Power View

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

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.

• Part 1: Reporting Services
• Part 2: Excel
• Part 3: PowerPivot
• Part 4: PerformancePoint
• Part 5: Power View
• Part 6: Making the Decision

Choosing the Right Microsoft Reporting Technology Part 5: Power View

In part five of this on going series about choosing the right reporting tool we will discuss the newest of tools called Power View.  While Power View is part of Reporting Services in SQL Server 2012 I’ve decided to split it as it’s own topic to help understand when you would use it over the traditional SSRS reports.  Now, because this is one of the newest reporting tools you can count on much of this post to get outdated over time.  In fact, a lot of the limitation you may see me describe are rumored to be fixed in future releases.

I’ve mentioned in previous posts that there’s not one tool that can solve all reporting needs so you may find yourself using multiple tools to provide a well rounded complete reporting solution.  This will be the last part of this series that describes tools, but there will be a part 6 that will help guide you in making the appropriate decision on which tool to use for each report you design.

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. Here’s the complete list of tools I’ve reviewed:

• Reporting Services
• Excel (From the perspective of building PivotTables without PowerPivot)
• PowerPivot
• PerformancePoint
• Power View

So let’s now review the newest and final Microsoft reporting tool Power View.

Power View

What it is

Power View is Microsoft’s answer to some of the popular third party tools like Tableau.  In a nutshell Power View is a highly visual ad hoc reporting tool for end users.  It is designed with the end user in mind and because of that it is incredibly simple to create really impressive reports.  Within a half dozen clicks a user can create a report similar to what you see below.

Power View is a tool that is now part of Reporting Services in SQL Server 2012 but you can only create or view the reports from SharePoint 2010. The tool used for designing the reports is actually dependent solely on Silverlight 5 (subject to change) being installed. So there’s no extra download like all of the other reporting tools on the market.

Power View is a highly metadata driven tool, which means it is important to to have a well defined data source.  With the current version of Power View you can only use data that is either sourced from a PowerPivot document that has been deployed to SharePoint or a BISM Tabular model.  BISM Multidimensional coming in the future according to several forum posts I’ve seen:

http://social.msdn.microsoft.com/Forums/en-US/sqldenreportingservices/thread/663283d7-a89c-44a3-97a2-5e406c1d635f

http://social.msdn.microsoft.com/Forums/en-AU/sqlkjpowerpointforsharepoint/thread/41b183ea-2642-487a-8dbb-8d44cdf71025

You can easily create multiple views on a report so that report consumers can view the data represented on multiple pages and in multiple ways.  Also, Power View reports can easily be exported into tools like PowerPoint so users no longer have to take screenshots of reports to embed in PowerPoint slide decks.  With Power View you will be able to look at live views of the report inside the slide deck and interactive with it like you would in SharePoint.

The quickest way to get started with the tool is to use the SharePoint 2010 template site called a PowerPivot Site which has all the components needed to begin development. After deploying any PowerPivot workbooks or BISM Tabular models to the PowerPivot Gallery you will be able to begin creating Power View reports.

If you would like to get started with learning Power View there are several live demos available. If you go to http://tinyurl.com/PowerViewDemos you will find several examples of completed reports and datasets that you can create your own reports against.

What it isn’t

Since the release of Power View people have often asked me if I think that it means an end for PerformancePoint.  Personally I don’t believe PerformancePoint is going anywhere because they have different purposes and also it is used by different people.  I think many people think they’re similar because they both use SharePoint and are both very visual tools but when you look at the details they really have different purposes.

PerformancePoint I consider more of an executive level dashboarding tool while Power View is for user created ad hoc reporting.  You probably don’t want end users developing executive level dashboard reports.  I prefer to have IT managing and controlling content that is exposed to executives and large scaled deployments to the entire company.  PerformancePoint also has the capability to do KPIs and Scorecards, which Power View does not have natively built-in.

Who Uses it

Power View has been created for the purposes of end users designing their own reports.  With the first version of Power View that is made very clear because everything is drag and drop.  There is no place within the tool to actually write queries like most other reporting tools so it’s very dependent on a strong data source.  While this may be seen as a limitation by some if fulfills the requirement to be an end user tool.  Don’t be surprised if the ability to query inside Power View changes in the future though,

How is it consumed

Power View can be viewed through SharePoint 2010 Enterprise with the Reporting Services add in for SQL Server 2012.  With a PowerPivot Gallery inside SharePoint you can easily select to view existing Power View reports or create new ones off of PowerPivot workbooks and BISM Tabular models.

Limitations

Power View has a very direct purpose so some of the limitations it holders are understandable.  Some of the other limitations are likely to change as the tool matures.  Keep in mind this is the first release for the tool  Here’s the limitations I’ve come up with:

• Limited number of data sources that can be used.  Currently only BISM Tabular or PowerPivot that is deployed to SharePoint.  Should be expanded in the future.
• All metadata driven, so no direct query option right now.
• Can only view Power View reports through SharePoint

Despite these limitations I think you will find a place for this tool because of it’s impressive results.  Also, as Power View goes through some growing pains of being a new tool many of these limitations will be fixed.

Summary

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

• Highly visual End User reporting tool
• Current version must use either BISM Tabular or PowerPivot as a data source
• Requires SharePoint 2010 and SQL Server 2012.  Enterprise for both
• Metadata driven so things like images are actually a good thing

I hope you’ve found this helpful and stay tuned for the Part 6 in this series where we’ll wrap up and talk about how to make your decision. To read any of the other parts to this series follow the links below.

• Part 1: Reporting Services
• Part 2: Excel
• Part 3: PowerPivot
• Part 4: PerformancePoint
• Part 5: Power View
• Part 6: Making the Decision

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:

• Reporting Services
• Excel (From the perspective of building PivotTables without PowerPivot)
• PowerPivot

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.

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.

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.

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).

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.

• Part 1: Reporting Services
• Part 2: Excel
• Part 3: PowerPivot
• Part 4: PerformancePoint
• Part 5: Power View
• Part 6: Making the Decision

Choosing the Right Microsoft Reporting Technology Part 3: PowerPivot

Today I’ll be continuing in a blog series designed to help you decide which presentation tool is best for your reporting needs.  So far I’ve discussed in previous posts:

• Reporting Services
• Excel (From the perspective of building PivotTables without PowerPivot)

You learned how these two tools are almost opposites of each other.  Reporting Services is generally thought of as a static reporting tool, while Excel is usually used for ad-hoc reporting.  The products still left to cover are:

• PowerPivot
• PerformancePoint
• Power View

Part three of the series will focus on PowerPivot.

PowerPivot

What it is

PowerPivot is much more powerful than any of the other reporting tools that will be discuss in this series because it does much more than just product reports.  While it does have the ability to create some impressive reports it really cannot create any addition visualization that regular Excel PivotTables can’t already do.

The real impressive part about PowerPivot is the modeling part of it.  With PowerPivot you actually design a modeling layer that brings in the different objects that you choose.  I purposely use the word objects generically here because you can import any data source that you can think of into PowerPivot.  Whether you need to bring in a table from SQL Server, a flat file or even something from DB2 it can all be done very easily with PowerPivot.  In fact, even if I needed to bring in all three of those objects into the same PowerPivot document and relate them to each other that is possible too.  Within the tool you have the ability to create logical relationships that may not exist on the source systems.  The view below shows how the new PowerPivot 2012 has improved the way relationships are designed.  Previously designing these relationships was done through a single dialog box instead of the new graphic diagram view shown here.

PowerPivot uses a fairly new in-memory technology called xVelocity (formally called Vertipaq) to handle all of the report processing requests.  With xVelocity Excel is able to process hundreds of millions of rows with amazing response times from a desktop machine. The xVelocity engine uses in-memory column-oriented storage and a highly compression data storage to produced the results you see today.

Another key thing to understand about PowerPivot is that the data pulled into PowerPivot is actually stored in the document an import is performed.  That allows it to use the store engine described earlier.  The one problem with this is that the data is static until manual update is kicked off.  When the update is run all of the data in the PowerPivot document is reloaded backed back in file.  Unfortunately, there is not incremental update yet so if you have a significantly large data source it may take a while update.  Later when I discuss how PowerPivot can be consumed I’ll talk about how SharePoint can assist in automating this data refresh process.  SharePoint is really the true way to scale out PowerPivot so other users can utilize the reports you build.

What it isn’t

One worry that many IT staffers feel about PowerPivot is that power users will begin creating these documents and making decisions off of them while10 other users have created simpler but different documents and get different results.  If you see something like this happening in your environment, which is completely possible, you could create a full Analysis Services solution (either tabular or multidimensional) to replace the PowerPivot documents to ensure all these users a consuming a single source for their reports.

It is also important to note that PowerPivot is not a replacement for any ETL that performs data cleansing or applies business rules.  Most data warehouses have a set of ETL processes that perform these tasks and that way all users are looking at the same data set.  PowerPivot does not eliminate the need for this.

Who Uses it

The goal is to have power users the driving force behind the creation of PowerPivot documents.  This would be a person that understands the source database(s), business needs, understands the concept database relationships and understands Excel all fairly well.

While that may be the goal for who should be using PowerPivot that is not what I actually see happening in the field.  I am still seeing most PowerPivot implementations being completely controlled by IT.

Over the last year I have seen this start to change so it does become more user driven but I think the problem that is preventing more users from getting their hands on the tool is education.  A lot of clients that I visit either don’t know what PowerPivot is or if they do they’ve never be taught (even in a simple demo) how it works.  So until this changes we may see a lot of PowerPivot solutions started in IT.

How is it consumed

The two ways PowerPivot can be consumed is the either directly through Excel or through SharePoint.  PowerPivot documents that are used directly through Excel rely completely on the machines resources they are viewed from.  So for example if I create and use a PowerPivot document on my laptop then it use all the resources of my laptop for importing data and processing results.  If I wanted others to see this report using this method I would have to either place the file on a shared drive or email it to those I want to view it.  That sounds terrible!

The best way to scale PowerPivot so that it is usable by a larger number of users is to setup SharePoint integration.  When PowerPivot for SharePoint is installed any reports viewed from a PowerPivot Gallery (SharePoint library for PowerPivot) run using a special Analysis Services instance to do all report processing rather than your laptops resources as previously described.  You may have noticed going through the SQL Server 2008 R2 install that there is an option to install SQL Server PowerPivot for SharePoint.  Using this SharePoint integration not only allows you to using Analysis Services for report processing but also allows you to schedule data refreshes, which is a huge help because normally data refreshes are a manual process without SharePoint.

Limitations

The major known limitations with PowerPivot are experienced when SharePoint is not part of the solution.  All of these have been detailed previously but as a reminder:

• Refreshing report data in PowerPivot is a manual process
• PowerPivot uses the resources of the machine the file is on
• Sharing a PowerPivot reports can be difficult for large documents (shared drive or email)

PowerPivot for SharePoint of course addresses each of these limitations.  One other limitation that I have not detailed yet is working on 64 bit vs 32 bit PowerPivot.  I highly recommend if you use PowerPivot that you only do it with the 64 bit version.  You will find out very quickly when you begin importing data sources into you document that without 64 bit the process can be slow and painful.  You might even run into some limitations with the amount of data it will import on a 32 bit instance.  With 64 bit PowerPivot the sky is the limits though!

Summary

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

• For power users that can understand a little modeling and relationships
• Can bring in multiple unrelated data source together for reporting
• SharePoint is the right way to make PowerPivot scalable

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

• Part 1: Reporting Services
• Part 2: Excel
• Part 3: PowerPivot
• Part 4: PerformancePoint
• Part 5: Power View
• Part 6: Making the Decision

Choosing the Right Microsoft Reporting Technology Part 2: Excel

This post is a continuation of a series started to help you understand each of the Microsoft presentation layer tools purpose and when you should choose each for your reporting solution.  The decision to pick the appropriate tool is difficult but so very important as I detailed in part one of this series.  As I continue the discussion here again are the different tools I will detail:

• Reporting Services
• Excel (From the perspective of building PivotTables without PowerPivot)
• PowerPivot
• PerformancePoint
• Power View (Yes Power View is supposed to be considered part of Reporting Services in SQL Server 2012, but it really is an entirely different tool than SSRS is traditionally thought of as)

In part one of the series you learned about Reporting Services and the role it plays mainly as a static reporting tool.  Here in part two we will discuss the polar opposite, which is Excel.

Excel

What it is

As you can see in my list I’ve made sure to differentiate Excel and PowerPivot as two separate tools even though PowerPivot is part of (free add in) Excel.  So to be clear when I say Excel I’m referring to Excel PivotTables off of an Analysis Services data source.  Of course any business user will tell you that PivotTables are possible without an OLAP data source, but to keep this relevant to Business Intelligence I will focus on Analysis Services as a data source.  With that focus in mind let’s discuss the tool.

Excel is customarily thought of as an ad hoc reporting tool that is great for getting a quick answer from your data source.  This is where quick analysis can be done without having to wait days for a report developer to define specs, write a source query and then finally build the report.  The report below for example was literally built in less than a minute.

Usually when I teach Analysis Services to a group my first question is, “How many of you have dozens of reports waiting for you or someone else to build when you get back to work?”  Predictably about half the room raises their hand and I go on to explain that part of the goal when building an OLAP database is to take much of the reporting burden off of you and move that to your more savvy end users that are comfortable in Excel.  This is possible because after you have built the Analysis Services layer Excel can consume it in a way that give users the ability to drag and drop fields into a PivotTable with very little training required.  This was one of the earliest forms of Self services BI.  So now many of those reports that are waiting in your queue can likely easily be created by the users themselves.  Of course this doesn’t solve all reporting needs for users but it can definitely relieve some stress.

Because Excel is most end users comfort zone you will find they need little training.  In fact admittedly I have learned a thing or two from end users about reporting in Excel.  Your diving into the end user’s bread and butter and that is why this is such a popular option for reporting.  Be wary though because end users love it so much they may start going beyond Excel’s intended purpose as a reporting platform.

What it isn’t

While Excel is great for cranking out reports quickly many think it lacks some customization features.  Now I happen to think this is partially a myth because we’re IT folks and don’t play in Excel all day.  Like I said earlier I’ve had too many experience where an end user has taught me something I didn’t think was possible in Excel.  Even though I identify it as partial myth I would still argue that tools like Reporting Services are far more customizable with with reports they produce.

Excel also is not necessarily the right tool for static reports.  As mentioned earlier it’s purpose is for ad hoc reporting, but if you use it for more permanent reports it’s not the end of the world.  You may be just miss out on the benefit of other reporting tools.  For example, let’s say your sales team looks at a weekly report of their previous weeks sales compared to last year.  If this report was in excel they could either open the file each week or view it in SharePoint with Excel Services, but the better solution may be to have it emailed directly to the sales team members using a Reporting Services report and subscriptions.  Having it in Reporting Services also opens the doors for features like report caching if performance becomes a problem.

Who Uses it

No long explanation needed here because this tool is for end users as previously detailed.

A couple caveats I’ll mention here are:

• Excel is a far superior tool for testing KPIs than the built in Analysis Services KPI browser.
• In SQL Server 2012 Excel becomes the primary way that developers test tabular BISM designs.

How is it consumed

The two typical ways of consuming Excel PivotTable reports are opening the .xls or .xlsx file itself or SharePoint with Excel Services.  Ideally your company would go the Excel Services route otherwise an unorganized chaos of workbooks being emailed or placed on shared drives can become a mess.

Excel Services allows you to place the workbook in a web facing version of Excel.  It does not require that Excel is installed on the users machine because they can view the Excel workbook directly through their web browser.  Any content deployed to Excel Services is managed through SharePoint content databases.

Limitations

One major limitation some find is depending on the version of Excel you are running that row limits exist.  I would argue that you are not using the Excel PivotTables for their intended purpose if you get anywhere near those limits.

I have also found that occasionally Excel can write some rather poor MDX against a cube with the drag and drop interface and unfortunately you do not have much control over it.  There is a nice free tool available called OLAP PivotTable Extensions which allow you to right-click on a cell and it provides the MDX that led to the results.  This can be pretty helpful when you’re first learning MDX (the query language for Multidimensional Analysis Services).

Summary

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

• Most end user preference
• For ad hoc report development
• Can be used for static reports but may not be best fit
• Excel Services with SharePoint best way to store and share reports

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

• Part 1: Reporting Services
• Part 2: Excel
• Part 3: PowerPivot
• Part 4: PerformancePoint
• Part 5: Power View
• Part 6: Making the Decision

Choosing the Right Microsoft Reporting Technology Part 1: Report Services

As Business Intelligence has evolved over the years the number of tools we have to choose from for presenting data has advanced drastically.  With so many tools to choose from it can be rather confusing (especially when your company is just getting their feet wet in BI) to determine which tool is the right one for an organizations reporting needs.  Just within the Microsoft suite of tools (not including third-party tools) you have Reporting Services, Excel, PowerPivot, PerformancePoint and Power View.

Many companies try to marry themselves to one or two reporting tools and fit their needs into the restrictions of the tool(s) they have chosen.  The truth is not a single one of these tools can solve all reporting needs.  While each one of these tools by themselves may be able to present your data, you will find that using a combination approach will conclude in a much more well rounded and impressive reporting solution.

That’s why in this blog series I hope to demystify the decision process and educate you a little bit on each of the tools so that you can easily determine which tool would be best in different scenarios.  Like I mentioned earlier the Microsoft reporting tools we will cover will be:

• Reporting Services
• Excel (From the perspective of building PivotTables without PowerPivot)
• PowerPivot
• PerformancePoint
• Power View (Yes Power View is supposed to be considered part of Reporting Services in SQL Server 2012, but it really is an entirely different tool than SSRS is traditionally thought of as)

After discussing each of the tools each individually I’ve bring it all together by showing you how to use a decision matrix to determine which tool makes sense for your work examples.

Reporting Services

What it is

Reporting Services is traditionally used for developing static reports.  The word static here is referring to the report layout rather than the report data (obviously the data will change).  For example, I want my sales team to be able to review a line item detail of each of their sales from the prior day.  With Reporting Services I could have the sales team either log onto a web front end to view a live version of the report or I could have the report emailed to them daily.

The reports developed with the tool are highly customizable.  In fact just about any object of the report can be manually changed or made dynamic with the expression language it uses.

If you’re looking for a tool for creating dashboards then Reporting Services also has the capability of filling that need.  With charts, gauges, indicators, sparklines and maps you have many of the necessary tools for creating an impressive dashboard.  There are other tools that like PerformancePoint that can create more impressive interactive dashboards but Reporting Services can get the job done.

Reporting Services is also highly scalable.  Without much effort the workload for report processing can be shared across multiple instances of reporting services.  After installing multiple instances the native wizard guides you through creating a farm of report servers.

Another thing worth noting because it will not be true of all the other tools I discuss in this series is that it can connect to virtually any data source you have.

What it isn’t

While there are some parts of Reporting Services that can provide ad hoc reporting capabilities the core of the tool is for static reporting.  If you really have a need for ad hoc reporting and could care less about report layout than you would likely pick another tool like Excel.

Who Uses it

While Reporting Services does have functionality for a power user to create their own reports it is typically thought of as a developers tool.  So report developers would use the tool Business Intelligence Development Studio (BIDS), which is part of the SQL Server install) to create incredibly detailed reports with almost endless flexibility when it comes the visualizations.

The tool that power users can use for creating reports is called Report Builder.  It has almost all the same functionality as BIDS but has been simplified a little for end users with more wizards and easier ways to bring in datasets.  Because it has a lot of the same functionality of the developers too you will find that this tool is for highly technical end user.

How is it consumed

Like I mentioned earlier Reporting Services reports can be consumed through a web front end of the native Report Manager, SharePoint, or even embedded in a custom application you’ve written with the Report Viewer control.

In addition to viewing the reports from a web browser they can also be delivered directly to the users via email or in a shared folder that they can access.  This is done using an incredibly useful part of the tool call Subscriptions.

Limitations

Again, Reporting Services is a static reporting tool therefore if your users find the need to change report layout frequently than that may eliminate this tool as an option for that particular report.

Another limitation you may run into is some of the functionality with Subscriptions, the report delivery tool mentioned earlier, are enterprise only features.  For example, if you wanted to make the reports delivered dynamic based off a list of emails you have in the database that must be done using Data Driven Subscriptions, which are Enterprise only.

Summary

As we go through this series remember these high level characteristics about Reporting Services:

• For static report development
• Extremely customizable
• Dashboard capable
• Highly scalable
• Traditionally thought of as a developers tool
• Has a native web front end but can also view reports through SharePoint and direct delivery with subscriptions

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

• Part 1: Reporting Services
• Part 2: Excel
• Part 3: PowerPivot
• Part 4: PerformancePoint
• Part 5: Power View
• Part 6: Making the Decision