SQL Chick

View Original

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

I know I’m not the only one who stumbles over my words sometimes and says Power View when I meant to say PowerPivot, or maybe PowerPoint.  And, especially for those folks who don’t focus on BI full-time, the jargon can get a bit confusing.  Here’s a quick summary of the applications with “Power” or “Point” or “Pivot” in the name:

PowerPivot

imagePowerPivot is an in-memory data modeling tool which allows data analysts to combine (or mashup) different sources of data.  For example, perhaps the corporate BI environment has sales data, but the data analyst needs to perform an analysis of sales fluctuations based on weather – a tool like PowerPivot makes it pretty straightforward to extract the sales data and relate it to the weather data needed for the analysis.  This is, of course, assuming the data can be logically related.

PowerPivot is an add-in to Excel which is able to handle more volume of data than Excel normally does.  This is because it’s based on xVelocity column-store indexes which compress the data based on uniqueness of values in each column. 

The data in PowerPivot is embedded within the Excel workbook (i.e., the xlsx file).  This is convenient for the business user, but the reality is it does create multiple copies of the data.  These copies of the data have the potential for creating many versions of the truth – good processes & procedures for managing Self-Service BI tools such as PowerPivot mitigate the chaos that can ensue if not managed well.

More Info on PowerPivot:  PowerPivot


PowerPivot Gallery

image

The PowerPivot Gallery is a specialized document library in SharePoint which facilitates Self-Service BI.  It primarily stores PowerPivot models and Power View reports.  Because it’s Silverlight-based, this library displays thumbnail previews of what’s contained inside the file – this is really helpful for making sure it’s what you want before you open it.

When a PowerPivot model has been published to SharePoint, additional features are available such as scheduled data refreshes.  The SharePoint environment also facilitates other applications, besides Excel, using the PowerPivot model as a data source – tools like Power View, Report Builder, and PerformancePoint all support the creation of reports using data in PowerPivot. 

The PowerPivot Gallery can store whatever document types you allow it to, but it’ll only display the thumbnail for PowerPivot models and Power View reports. 

More Info on the PowerPivot Gallery:  TechNet


Power View

imageYup, this one has a space in its name and the rest don’t.  Sigh.  Now that I got that off my chest…

Power View is a visual reporting tool which strives for interactivity and data exploration.  It’s a Self-Service BI tool, so the primary users will be business people who develop reports, dashboards, presentations, and data analysis projects.  This tool introduces some new features not previously available in the Microsoft BI stack such as cross-filtering, highlighting, and a playable time axis.  It’s considered a complementary tool to Report Builder.

As of now (late Nov. 2012), the data source for a Power View report must be an Analysis Services Tabular Model, or a PowerPivot Model.  Support for Analysis Services Multi-Dimensional Models is currently in CTP; an actual release data isn’t known to the public at this time.

With the combination of SQL Server 2012, Office 2010, and SharePoint 2010, Power View has a dependency on SharePoint as it’s a shared service running under the Reporting Services umbrella.  However, with SQL Server 2012 SP1, Office 2013, and SharePoint 2013, you get an additional choice & that’s to use Power View as an add-in to Excel.  This removes the absolute dependency on SharePoint, and allows you to choose if you want to initially generate the Power View report inside of Excel (in which case the Power View report is embedded as part of the .xlsx file), or generate the Power View report from within SharePoint (in which case it’s an independent .rdlx file).

More Info on Power View:  Power View


PerformancePoint

PerformancePoint is the Microsoft BI tool typically known for developing dashboards and scorecards.  This tool isn’t always called by its formal name; if you hear SharePoint Insights, it’s very possible that it’s PerformancePoint being referred to.

Although the Dashboard Designer tool can be given to a sophisticated business user, PerformancePoint is not considered a Self-Service BI tool.  One of the PerformancePoint strengths is its ability to define Key Performance Indicators (KPIs) within the Dashboard Designer – that leaves a big decision to the BI team as to whether KPIs should be defined within PerformancePoint, or within the Analysis Services cube where other reporting tools can also take advantage of the common definitions.

Although it can use relational and tabular data sources, PerformancePoint is really meant to use an Analysis Services database as its data source, so it can deliver all of the right-click interactivity and drill-down features.  Because it’s a service in SharePoint (as of the 2010 version), there is a SharePoint dependency to use PerformancePoint.

More Info on PerformancePoint:  SharePoint


PivotViewer

PivotViewer is a very visually-oriented feature of Silverlight. Its strength is the display of large sets of data known as collections – which could easily include thousands of images – and allowing the user of the report to interactively zoom in, zoom out, filter, regroup, reorganize and so on to interpret and find patterns in the data.  It associates the data itself to the images (ex: clickthrough data for advertising programs could display an image of the actual advertisement, or athletic statistics could display images of each player).

There is a PivotViewer Extension for Reporting Services available, although it’s not clear to me if it ever made it out of CTP2. 

More Info on PivotViewer: Microsoft PivotViewer


Pivot

Pivot is extremely similar to PivotViewer (above).  However, Pivot is not dependent upon Silverlight; rather, it’s built upon Internet Explorer.

Unlike PivotViewer, the Pivot tool is an unsupported tool still in the experimentation phase.  It’s currently released as a CTP from Microsoft Research; it was formerly released from LiveLabs.  The functionality is the same from what I’ve been able to tell, just without the Silverlight component.

More Info on Pivot:  Microsoft Research

 

 


Power Tools

Visual Studio has Power Tools available for download, which are intended to improve developer productivity.  Power Tools includes all kinds of utilities and goodies.

More Info on Power Tools:  Productivity Power Tools  |  Team Foundation Server Power Tools


PowerPoint

Good old PowerPoint.  It, of course, is the tool we all know and love for developing slides to accompany presentations. It’s part of the Office suite of products, and has been around over 20 years now.  And it’s here in this list because sometimes this comes out of my mouth when I actually meant to say PowerPivot!

Power View (above) actually has a design environment a bit similar to PowerPoint – both are considered “presentation ready” because you don’t have to worry about setting page sizes, margins, and so on. Also, Power View is able to export its “views” to PowerPoint “slides.” These exported views even retain their interactivity if you’re still connected to the company server.

More Info on PowerPoint: Office PowerPoint 2010