(article published on the MVP Award Program Blog)
I really enjoy this topic. One of the reasons, I suppose, it’s such a hot topic is that a lot of people believe that there is a perfect ad hoc reporting solution out there, just barely beyond the current set of capabilities in all of the available tools. A lot of our customers and others in the industry ask what tools they should be using for ad hoc reporting and exactly what criteria to use to choose among them. There’s a lot of “it depends” to that kind of question but we can start with some guidelines and direction.
Before I get into the details, I’ll give you the bottom line: there are some great, powerful, easy-to-use tools available in the Microsoft toolset. There isn’t a single tool from any vendor that does it all but there are a few from Microsoft that do a lot. Business users are impatient and as soon as we show them how to use some really cool reporting application that meets most of their needs, they’ll complain that it doesn’t do that one thing that it wasn’t designed to do. Finding the perfect self-service, ad hoc, reporting tool is like this utopian vision for the future. Like in the X-Files, “it’s out there.” Like discovering the city of Atlantis or proving that there is extra-terrestrial life; we’re just one breath away from finding it.
The Microsoft self-service reporting stack:
PowerPivot and Power View
A few years ago, the product teams at Microsoft started working on a new generation of analytic reporting tools. After doing a bang-up job making SQL Server Analysis Services a world-class platform, they diverted a lot of their resources to developing the technology behind PowerPivot. At about the same time, the Reporting Services team started working on Power View, a very graphical and dynamic analytic data browser that lets regular ole users make sense out of information by just by dragging and dropping things into charts and nifty visual widgets. We saw the first generation of PowerPivot released as a free add-in for Excel 2010 and Power View was added to the SQL Server 2012 product suite when it’s integrated into SharePoint 2010 Enterprise Edition. Now, wait, before you react – I’ve been out there pimping this stuff for a couple of years and have heard a lot of knee-jerk reaction to this dependency on SharePoint Enterprise. If you’re in that camp, just hold on to your horses and understand that there’s more good stuff coming. The Power View integration in SharePoint was a first step. Microsoft is trying to take care of their enterprise customers and compete in the big BI marketplace but they are watching out for the little guy too.
Both PowerPivot and Power View will be integrated into the new version of Excel coming in Office 2013. Keep in mind that even though these two “Power…” tools are both add-ins for Excel, they are different things. You can use PowerPivot to combine data from different data sources and perform calculations over some pretty large sets of data right on your desktop. To view the results, business users work with all the features they already know how to use in Excel like pivot tables, charts, filters and slicers. Additionally, they can use a new expression language called DAX to perform calculations – which is more powerful but feels a lot like familiar Excel functions. The reason PowerPivot is so fast and can handle so much data is that all of the data is loaded into the computer’s memory in compressed form. Realistically, you can work with up to several million rows of data on a typical desktop computer.
If you would like to take these visuals to the next level, you can use Power View. Let’s take a look at PowerPivot and Power View in the publically available Excel 2013 Preview. The model shown below has several tables that are related together using keys, similar to a relational database like SQL Server, but the concepts are much more basic. Each of the tables can be imported from a different data source (including worksheets in the Excel workbook) and the data can easily be refreshed at any time. The pane below the data columns shows calculations that are also stored in the model.
The sample data I’m using is from the FAA with statistics about airline flight delays and aircraft incidents involving wildlife. You can download a copy of this PowerPivot model with the sample downloads for our Wrox Press book: Professional SQL Server 2012 Reporting Services. Just go to book support site at Wrox.com, and download the samples: http://www.wrox.com/WileyCDA/WroxTitle/Professional-Microsoft-SQL-Server-2012-Reporting-Services.productCd-1118101111,descCd-DOWNLOAD.html
A Power View report is added to the Excel workbook and all of the tables and fields in the model are listed on the right. Visualizing the data is a matter of choosing fields and adding them to the report surface.
After adding fields in various combinations, you choose how to visualize the data by selecting visualizations from the Design ribbon. Color themes, font selection and scaling, and background fill options were all added to the new version of Power View in Office 2013.
Another really nice addition is the Map visual, which can visualize values grouped by known geographical regions (like states), points on the map using latitude and longitude and even addresses. It utilizes the Bing Maps service and automatically geocodes data in the model. This example shows the number of aircraft service hours rolled-up by state. This is visualized both in the map and in a bar chart below. I’ve added a slicer on the right using the Species field which filters the results in all of the other report visuals. The usability for this tool is amazing. Most anyone can figure it out with little or no direction just by clicking and experimenting.
If you’re an enterprise user, these PowerPivot models and Power View reports can be uploaded to the corporate SharePoint site to be shared and used by lots of others. When the model is stored and runs on the server, it uses the memory and resources on the server rather than the desktop computer. To manage very large volumes of data securely and with more administrative control, the PowerPivot model can be loaded into an Analysis Services server configured for tabular storage. In essence, this is the enterprise version of PowerPivot.
There are a lot of new features planned for Power View but it’s not a reporting tool that is going to let you do absolutely everything. The goals of this product are to keep visual reporting simple and uncomplicated. It’s not ever going to be a tool for programmers to customize and it’s not designed for line-of-business styles of reporting. For the time being, a PowerPivot or tabular model is a requirement for Power View. In the near future, we will probably see support for multidimensional cubes but you can’t point Power View directly at a relational database without creating a model first.
Report Builder
Reporting Services is the gift that keeps on giving. When talking to consulting customers about report tool choices, I often tell them that I can do just about anything they want to do with Reporting Services. Even if the requirements are complex and they need really advanced features, there’s usually a way to get there with SSRS; although they may not get the exact behavior or user experience they may had imagined based on their experience with other software. One of the reasons that Reporting Services is so powerful is that reports can be extended with expressions and custom code. However, like Uncle Ben said, “with great power comes great responsibility.” Here in lies the challenge: if the IT folks do their job by setting up an appropriately-configured environment with data views, shared objects and semantic models; users can have a great experience designing their own reports with Report Builder. If regular business users are set free to hook up their reports to complicated business system databases, they’re not going to have a good experience and they’re going to make a mess.
Report Builder can be used with a standard report server or it can be integrated with SharePoint. Either way, a little bit of admin preparation is necessary to get them off the blocks and using the tool the right way.
From the end-user perspective, all they need to do is open Report Builder from a menu in their web browser, select a shared data source, dataset or semantic model (consisting of a cube or tabular model) and then run a wizard or drag and drop fields to assemble the reports. It’s as easy as that. Report Builder has many, easy-to-use features that, with just a little guidance and education, they can be off and running in no time. Reports can have drill-down paths, custom calculations, great visuals and can be exported to lots of formats (including Excel). This really is a great tool.
Another mode where Report Builder excels is the ability to use finished report fragments that have been saved to a server, called report parts, to assemble their own reports. This is a very handy way to let users assemble their own custom-built dashboards from components provided by others who have already invested the time and energy to design sophisticated report features. The user drags and drops, and viola!; a dashboard.
So, here’s the catch… Not only does Report Builder allow business users to design simple reports with relative ease, it also allows people to design really sophisticated and complex reports with very advanced features. Since the tool doesn’t prevent casual users from fiddling with the advanced features, unguided, they can get themselves into trouble. Report Builder is used most successfully in an environment where all users are schooled in the appropriate use of the tool to match their needs and their level of technical expertise. After a business user has created their own report that meets the majority of their needs and then their requirements have evolved beyond the scope of their capability (or desire) to go to the next level, the report design can be promoted to a specialist for enhancements.
There’s much more to be said about the capabilities and nuisances of Reporting Services and, in particular, the Report Builder design tool. In a nutshell, it can be used to create not only BI dashboards, scorecards and other analytical types of reports but it’s also a good tool for designing operational style reports like invoices, transaction summary and detail lists. It lacks the interactivity and simple elegance of Power View but it works well in a variety of settings.
PerformancePoint
This is one of those products that was created with some specific capabilities in mind. It does a few things really well but doesn’t provide a lot of latitude outside of its feature set. This is the product that survived development efforts to mimic features of ProClarity Web Professional, a product Microsoft acquired, along with the company that developed it, focused on visualizing Analysis Services multidimensional cubes. PerformancePoint is now baked into SharePoint Enterprise Edition. It’s ideally used to create business scorecard and dashboard reports from multidimensional models. It’s a little bit of a stretch to call it an ad hoc reporting tool but it does allow business users with intermediate design skills (and no programming or database background) to create basic charts and scorecards with some really cool drill-down features. It’s flexible enough to deploy one report to be used with different measures and hierarchy levels. One of the niftiest visualizations is the decomposition tree. PerformancePoint can also use existing Excel and Reporting Services reports as components of an orchestrated dashboard by using a common set of filters and corresponding parameters to filter the whole dashboard.
Some have predicted the demise of PerformancePoint just based on the theory that other Microsoft reporting tools have overlapping features and that we hadn’t seen a lot of new improvement to this product since it was released quite a few years ago. However, it now seems that the Office/SharePoint team are paying more attention to it and have announced a few feature additions for 2013: https://sqlserverbi.blog/2012/08/09/performancepoint-lives/.
PerformancePoint is a respectable product with some good capabilities. I think one of the reasons that a lot of people have not embraced it is because it’s one more tool to learn – and many people have their plates full with other reporting tools. In the enterprise, it’s good to designate a PerformancePoint expert. Let her learn the product, design the reports and dashboards and then others can use them for ad hoc browsing and data exploration.
Excel and Excel Services
There was an epiphany in the Microsoft BI community a few years ago when business reporting and BI professionals realized that many business people really want to do their reporting in Excel. For years, we’ve been trying to create reporting solutions and tools that either work with or export reports to Excel. So, for those business reports that should end up in Excel at the end of the process, why not actually use Excel as the reporting tool? There are many reasons that this hasn’t been feasible in the past. When multiple business users export data into Excel, manipulate it, perform calculations and use this for decision-support, you end of with several different versions of spreadsheets and datasets in the possession of multiple users.
The first thing we needed was an effective collaboration platform. Enter SharePoint. Now that we have the means to share documents without proliferating data sprawl, Excel had to be able to handle more data effectively. Thank you PowerPivot. The next thing was to be able to take visuals and reporting beyond, well, Excel. Several recent innovations have taken the Excel toolset to the next level. Of course Power View takes you to a whole new level but in the past couple of versions of Excel, we’ve seen a new-and-improved pivot table, the addition of slicers, several chart enhancements, conditional formatting and KPI indicator sets. Oh, I should mention that the worksheet row and column limits were expanded significantly. Yes, Excel can be misused and isn’t going to be the right choice for all business reports but it’s a much more capable reporting tool than ever before. Excel in SharePoint can really be an enterprise-scale solution. When hosted in a document library, a workbook actually runs on the SharePoint server farm instead of the user’s desktop. Excel Services visualizes the Excel report content as a web page with full interactivity. Hosted workbooks can also be secured and managed in SharePoint.
What to Use for What
The following table shows, in my view, some of the more ideal choices for different styles of reports. Every tool has its strengths and limitations which have been considered in the order that each tool is presented.
| Reporting Requirements | Report Tool | Summary |
| Operational line-of-business reports | 1. Report Builder | For live reporting with production databases or semantic models, Report Builder is an ideal tool. It works very well for table and form report styles and can be used with live product data. Requires technical expertise for reports beyond basic design and doesn’t prevent users from using advanced features. |
| 2. Power View | Has table, matrix and form views for textual data. Not as flexible for this style of reporting but very easy to use. Doesn’t export to Excel. | |
| 3. Excel | New chart and pivot table features make Excel a good choice for basic reporting styles (lists, pivots & charts). PowerPivot can be used to integrate data from multiple sources and can be refreshed frequently. | |
| Ad hoc data browsing | 1. Power View | Very easy-to-use, highly-visual and dynamic tool. Requires a semantic model created using PowerPivot or SSAS tabular mode. |
| 2. Excel | Used with a semantic model, Excel is an ideal tool for browsing and summarizing analytical data. Use slicers to interact with pivot tables and charts. | |
| 3. PerformancePoint | Chart reports and scorecards are first designed and published to SharePoint. Using these visuals, users can browse, drill-down and cross-drill dimensional data in a semantic model. | |
| 4. Report Builder | Using the report wizards and basic query designer, simple table, matrix and chart reports can be created with relative ease. Requires technical skills to use more advanced features and to debug design. Works well with shared datasets, views and semantic models. | |
| Business Dashboards | 1. PerformancePoint | Dashboards can be assembled from multiple report types including PerformancePoint charts, scorecards, SSRS reports and Excel reports. Common filters may be used to coordinate all dashboard reports. Design requires practice and some trial-end-error learning. |
| 2. Report Builder | Business users can create their own custom dashboard reports by assembling a report from prepared report parts. Advanced features can be added by IT professionals. |