Wednesday, February 12, 2014

Demystifying Microsoft Business Intelligence and Reporting Tools

When I present Microsoft Business Intelligence and Reporting tools to customers, I often encounter confusion about the options available from Microsoft and questions regarding what is the most appropriate tool to meet end user requirements and use cases.

Microsoft currently offers the following solutions:

· SQL Server Reporting Services (SSRS)

· Power Pivot (Excel add-in and SharePoint integration)

· Power View (Excel add-in and SharePoint integration)

· PerformancePoint (formerly Proclarity)

· Power BI (part of Office 365)

The following chart provides a high-level comparison of SSRS, Power Pivot, Power View and Power BI. I do not include PerformancePoint as this product does not appear to getting much in the way of additional investment from Microsoft, and we have not been recommending this lately to our clients.


SQL Server Reporting Services has been around for many years and provides a robust and scalable enterprise-ready reporting solution. Although SSRS provides a Report Builder tool for end users, we find that SSRS is ideally suited to more technical users who create high-volume, static reports which can be shared for via Report Server or SharePoint. SSRS can be used to create interactive dashboards and ad-hoc reports, but doing so requires a higher level of skill than the other tools highlighted above.

Power Pivot, available as an Excel add-in and integrated with SharePoint, is a highly flexible ad-hoc reporting solution. Calculations and measures can be developed using Microsoft’s Data Analysis Expressions (DAX) language, and the Power Pivot model can be migrated to SQL Server Analysis Services (SSAS) Tabular model for enterprise reporting. Power Pivot is ideal for experienced Excel users who desire to create their own reports from enterprise data.

Power View is a relatively new data visualization tool created by the SQL Server Reporting Services product team. The primary purpose of Power View is to allow end users to create compelling, interactive visual dashboards for consumption by executives and other key data stakeholders. Like Power Pivot, Power View is available via Excel add-in and integrated with SharePoint.

Power BI is the newest of Microsoft’s offerings having been released in February, 2014. Power BI is available as a stand-alone subscription or as an add-on for Office 365. Power BI is tightly coupled with Power Pivot and Power View. Power BI workbooks are created using Power Pivot and Power View and uploaded for display and interaction in Power BI. There is also a Data Management Gateway for integrating Power BI workbooks within on-premises data sources as well as mobile client that is browser-independent.


Hopefully this blog post has helped you better understand the Business Intelligence and Reporting offerings available from Microsoft and how they can best be used to meet user requirements.