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.
