End-User Microsoft BI Tools – Clearing up the confusion

Given two types of underlying data sources (a data warehouse and a OLAP cube built from the data warehouse) there are many different possible presentation layers (client tools) that serve different user communities with varying usage profiles.  There is a lot of confusion on what is the best tool to use.  This blog post will hopefully clear up the confusion and give you some guidance on the best tool for each situation.

I recommend a blend of presentation layers as each organization typically has a wide range of performance management needs ranging from structured reporting to ad-hoc analysis capabilities to strategic scorecards and dashboards.  I am assuming a security paradigm within the data warehouse and OLAP cube that will show only the data that each user has access to, allowing various boilerplate views to be shared throughout the organization.

A lot of the same tasks can be done in more than one tool, but you want to choose the tool that specializes in that task.  Below I will first describe all the tools, and then follow with different categories of users and the tools they might use, then end with the different types of BI reporting styles that describes how users want to interact with and present information and the recommended tools for each style.

The Tools

Excel (PivotTables, Services) – Good for ad hoc, one-off analysis.  Excel is the most widely deployed BI tool in the world for the self-service analysis style.  Excel has come a long way from a business intelligence point of view.  Ever since it started to support “Pivottable” integration with OLAP cubes, it became the Ad hoc reporting application within the Microsoft BI stack.  With each release of Excel the integration with OLAP cubes tightened and new features grew, with Slicers being one of the latest and best features.  Excel has become a professional tool which users can use to create professional data models as well as ad hoc models (meant just for the reporting need of that moment).  To use Excel the end-user will need the Excel application installed on his or her computer.  Excel Services is a SharePoint Server feature that allows users to publish Excel workbooks to SharePoint so others can view it even if they don’t have Excel installed.  See video Demo: Creating an Excel 2010 PivotTable Using a SQL Server 2008 R2 Analysis Services Cube and video Working with Excel Services in SharePoint 2010.

ProClarity - Good for ad hoc, one-off analysis.  ProClarity is a tool that Microsoft acquired in 2006 but no longer upgrades, with support ending in 2017.  Many of the ProClarity features have rolled into PerformancePoint.  There is ProClarity Desktop, which is a rich client desktop authoring tool, ProClarity Web Professional, a thin client web-based authoring tool, and ProClarity Analytics Server (PAS), a server component which stores authored reports/dashboards, controls connections to data sources and renders dashboards.  Despite it no longer being upgraded, it is still in wide use and many power users love it.  It is easier to use than Excel and more powerful for creating reports and interactive dashboards.  Anything created in ProClarity can be published to a dashboard in SharePoint, which many power users do without IT involvement.  Note you can only download ProClarity if you have an MSDN subscription.  It also has two add-ons: Selector and KPI Designer.  More info is at What Future for ProClarity Analytics? and The Microsoft ProClarity Team Blog.  For a product that is similar to ProClarity and is backwards compatible, check out bioXL Analytics (from the former Proclarity folks who were at Microsoft and were involved with PerformancePoint Server).

SQL Server Reporting Services (SSRS) - SSRS provides a complete, server-based platform designed to support a wide variety of reporting needs.  It is a powerful report authoring and management environment allowing creation of static and parameterized reports.  The reports are built using Visual Studio.  All reports can be accessed via a portal as well as automatically generated and distributed.  Users can perform analysis through parameters, filters, drill-down, and drill-through.  Reports and be scheduled and distributed via portal, file share, email, or printer, and may be generated as PDF, Excel, XML, comma delimited text file, TIFF image, HTML, or Microsoft Word formats.  See video Introduction to SSRS 2008.

Report Builder - Report Builder provides an intuitive report authoring environment for tech-savvy business users and IT professionals to develop highly formatted reports using an Excel-like ribbon.  Report Builder is the light weight substitute for Visual Studio.  Visual Studio was formerly required if you wanted to make reports.  Report Builder supports all of the features that Visual Studio does.  With SharePoint, Report Builder can be offered to users as a “click once” application.  You still require technical skills in order to make any interesting report.  Frequently Report Builder is being used as a “sand box” tool for power users.  He or she would create a report and then hand it over to IT for further development, which is easy to do because the published reports are actually SSRS reports.  The IT department typically integrates it into an existing solution and saves it to a source control (like Team Foundation Server).  Report Builder is easiest for the non-technical user if a Report Model (that is, a business oriented semantic layer) is in place so they can understand and consume information without requiring knowledge of the underlying source tables.  A SQL Server Reporting Services environment is required because Report Builder uses that environment and can therefore use all the feature of SSRS, such as data-driven distributions.  See video Introduction to Report Builder 3.0.

PowerPivot (Excel, SharePoint) – Good for ad hoc, one-off analysis.  A free add-in to Excel.  It extends the capabilities of the PivotTable data summarization and cross-tabulation feature by introducing the ability to import data from multiple sources.  With PowerPivot you can also work with data sets much larger than the Excel 2010 limit of 1 million rows per sheet.  PowerPivot can scale to hundreds of millions of rows by using the xVelocity engine.  The xVelocity engine performs query processing and implements a column-based data store with efficient compression algorithms to get massive amounts of data directly into memory.  With all the data in memory, PowerPivot can perform its query processing, data scans, calculations, and aggregations without having to go to disk.  A non-technical person can use PowerPivot to prototype a solution quickly before involving IT to build a more traditional BI infrastructure like a SSAS cube.  It allows a user to visualize all solutions and know exactly what they want before the specialists come in.  There is also PowerPivot for SharePoint, which supports Excel 2010 workbooks that contain PowerPivot data that are published to a SharePoint site and can be refreshed automatically.  As a result, other users who do not have the PowerPivot add-in installed can view and interact with the workbook.  This requires two components to be installed, PowerPivot System Service and Analysis Services in Tabular mode (which stores data using the xVelocity engine, which is an in-memory columnar database, check out When to choose tabular models over PowerPivot models).  See video Introduction to PowerPivot and video Business Intelligence in SharePoint 2010.

PerformancePoint Services (PPS) – PPS is a component of Microsoft SharePoint Server 2010 and is a performance management service that is used to create dashboards, scorecards, reports, and KPIs.  In a dashboard users can interact with SSRS reports, cube-based graphs, performance maps, decomposition trees, and Visio diagrams.  This flexibility gives users many ways to navigate through their data and derive new information useful in making decisions.  Filters can be applied across all dashboard components to update them simultaneously.  With cube-based graphs and charts, users can perform ad-hoc analysis, slice and dice dimensional data, navigate through hierarchies, and pivot and change chart types quickly and seamlessly in just a few clicks.  Development may be performed much faster by using the structured layout and predefined design options available in PPS.  See video Developing and deploying PerformancePoint scorecards and dashboards.

Power View (formerly known as Project Crescent) - Good for ad hoc, one-off analysis.  Power View is a new interactive data exploration and visual presentation experience coming in the next version of SQL Server 2012.  It will offer a fun, visual, and powerful drag-and-drop ad hoc reporting experience.  It is an web-based end-user BI tool based on Silverlight.  At first glance it resembles the SSRS report builder, but the resemblance ends there as the features and functionality FAR out reach anything currently available.  Users are able to build and format reports based on “models” that are deployed to the server.  Each Crescent report is based on a PowerPivot model that can be created within Excel or Visual Studio 2010.  The models are deployed to SharePoint and from their users can create their reports through the web front end.  It is done all in a browser – there is nothing to deploy.  It is presentation-ready, meaning there is not a design mode.  Power View uses the xVelocity engine and DAX via BISM.  It requires SharePoint and SSRS and works in SSRS SharePoint Integrated mode (not in native mode).  See SQL Server 2012 (“Denali”): Power View (“Project Crescent”) and video Abundantly “Crescent”: Demos Galore.

Data Mining - An add-in for Microsoft Excel.  Data mining is the process of analyzing data to find hidden patterns using automatic methodologies.  Where all the other tools are ways to see where you have been, data mining is a tool to predict where you are going.  It is usually not put into use until the BI solution has been in place for a while, and you want to take it to the next level.  With the add-in, you can mine historical data to provide new insights and form a reliable basis for accurate forecasting.  See Why Use Microsoft Data Mining? and video Data Mining with the Data Mining Plugin for Excel and video Introduction to Data Mining in SQL Server Analysis Services and video Enterprise Data Mining with Microsoft SQL Server.

SQL Server Management Studio (SSMS) – This is for those rare individuals who want to use SQL against the data warehouse or MDX against the cube to return the raw results.

The Categories of Users

A single person may perform many roles within an organization, so this description is intended to relate to job function more than a certain role within the organization.

Strategic Users (Executive, Power User, Subject Matter Expert, Regional/Area General Manager)

• Typically require high-level, quick snapshot view with minimal (no) technical maneuvering
• Typically interested in Scorecards and KPIs and current performance against them
• Typically desire to take action on problem areas
• Typically require high collaboration to make decisions in teams and with decision context shared
• To increase team accountability and alignment
• Then recommend PerformancePoint Services (PPS) as a dashboard within Microsoft SharePoint Server
• Views could include ProClarity Analytic Server (PAS) views or PPS views
• Typical trade-off is PAS gives more functionality (drill-down) and will launch ProClarity Web Pro
• PPS is better if don’t have/want ProClarity
• Consider importance of consistent look and feel

Tactical Users (Analysts, Advanced Users, Power User, Subject Matter Expert, Managers)

• Typically require maximum root-cause analysis and maximum ad-hoc capabilities
• Typically need full slice and dice capabilities and full drill-down
• Typically require some what-if analysis capabilities and visualization to manage by outlier
• Then recommend ProClarity or Excel Pivot Tables or Excel Services or PowerPivot or Power View
• Do not recommend SQL Server Reporting Services (SSRS) or Report Builder as analysis options are limited

Operational Users (Field Personnel)

• Typically need only limited ad-hoc analysis capability
• Typically refresh the same reports daily/weekly/monthly
• Typically static (or flat) reports with known rows and columns
• Typically does not require the collaboration of a team
• Then recommend SQL Server Reporting Services (SSRS) or Report Builder
• Excellent for mass distribution of canned reports
• Features subscription functionality to allow information to be pushed to the user on a schedule
• You may also recommend PerformancePoint Services (PPS) as a dashboard to drive alignment to org objectives
• Do not recommend ProClarity as it does not handle paging well

(Power View is not in this picture, but I would put it in the same spot as Excel 2010, leaning more toward Self-Service Analysis)

The BI Reporting Styles

Self-Service Analysis – This describes free-form reporting and analysis by users where they can integrate data from multiple sources and drill-down to understand the root cause for data anomalies.   These non-technical users are able to perform their own self-service reporting and analysis without relying on IT.  Knowledge of SQL or MDX is not needed.  Tool options: Excel, PowerPivot, ProClarity, Power View

Business Reporting – This style describes formatted reports that are created by advanced businesspower users.  Reports are usually based on approved and validated corporate data which is shared throughout the company.  IT involvement is moderate, usually limited to the setup and configuration of the reporting environment and the data source connections.  Knowledge of SQL or MDX is unlikely.  Tool options: Excel, PowerPivot, Report Builder

Parameterized and Operational Reporting – Similar to the Business Reporting style but goes one step further.  The reports are created by IT and usually use the same look and feel and add parameters that are used as filters on the reports.  Automatic distribution can also be setup.  Reports may be complex and require advanced knowledge of SQL or MDX.  Tool options: SQL Server Reporting Services, Report Builder

Performance Monitoring (Dashboards) – This describes dashboard-style reports so users can quickly and easily monitor the current health and performance of their business.  It is ideal for executive level employees who can see at-a-glance the health of the business with the option to interactively investigate further.  Data in multiple formats are combined on one page (i.e. tabular report, graphs, scorecards) and the information should be consumed within several seconds.  This style is generally found in more mature BI environments where data points of interest have been defined, key determinants of business success have been identified, and a comprehensive BI strategy exists.  Tool options: Excel Services/PowerPivot, SQL Server Reporting Services, Report Builder, PerformancePoint Services

Scorecarding – This offers highly summarized views with Key Performance Indicators (KPIs) measured against predefined targets.  The ultimate goal is to create a balanced scorecard.  Usually contained within a dashboard.  Tool options: Excel Services/PowerPivot, SQL Server Reporting Services, Report Builder, PerformancePoint Services (recommended)

Yet another way to categorize reports is: Enterprise Reporting (Financial statements; high level board report); Operational Reporting (Detailed reports for day-to-day decision-making); Analytics (Data exploration; statistical & predictive analytics; data mining); Performance Management (Scorecards & Dashboards); Self-Service & Ad-Hoc Reporting (Empowering end users).

More Info:

Video A Lap around Microsoft Business Intelligence

How to Choose the Right Business Intelligence Technology to Suit Your Style

How to Choose the Right BI Technology to Suit Your Style

Dashboards…How To Choose Which MSBI Tool?

Choose the Right Business Intelligence Style for your Project

Comparing Analysis Services and PowerPivot

Power View vs. other BI Tools

Choosing the Right Microsoft Reporting Technology Part 1: Report Services

Report Builder or Reporting Services?

Is Power View meant to replace ReportBuilder?

Building a BI Portal for the Tabular Model

The State of Microsoft Self-Service Reporting

SharePoint 2010 Business Intelligence

Types of BI

Making Sense of the “Power” and “Point” and “Pivot” Apps

Report Builder … the Red-Headed Stepchild of Self-Service BI Tools?