Data Visualization

Comparison of Qlikview and Spotfire vs. PowerPivot little bit unfair and it is not anybody’s fault, it is rather a huge strategic mistake by Microsoft. Microsoft currently do not have a “BI product” (like they had it before in form of acquired ProClarity or short-lived PerformancePoint Server). Instead of product Microsoft decided (unless you agree with Microsoft that SQL Server, Excel, Visio and SharePoint are “BI Products“) to give people the reason to upgrade to Office 2010 and promoted a shiny dream (or idea) of self-service BI (see above, because BI is just a marketing term and Intelligence is not an attribute of any software in existence, despite propagandists from AI, BI and other “XI” areas), which is “BI-stack” based on multiple tools (like Excel 2010 and SSAS – SQL Server Analysis Services) and components (some of them are brilliant like PowerPivot and some of them just a virus like SharePoint). In order for users to get something based on Microsoft “BI-stack”, comparable with Qlikview-based or Spotfire-based applications they need an expert consultant(s). And again: most clients still want to compare these three tools above.

PowerPivot is Microsoft’s 3rd attempt to commoditize the BI and DV market space:

• The ProClarity acquisition failed, there have been no new releases since v. 6.3
• PerformancePoint Server was introduced in November, 2007, and discontinued two years later.
• Current 3rd attempt focused on the burgeoning Data Visualization space, specifically at fast growing competitors such as Qliktech, Tibco Spotfire and Tableau Software.

In an attempt to mimic Qlikview successful in-memory columnar database, Microsoft used Excel as the front-end equivalent of Qlikview’s rich UI and Visualization controls.  The back-end API was available only if packaged with SharePoint and SQL Server (in an attempt to increase sales of these products) resulting in PowerPivot (on Client side) and VertiPaq/Tabular Mode (in-memory mode) on Server side. Some good words about PowerPivot and Excel 2010:

• PowerPivot is an excellent in-memory columnar database, it outperforms similar functionality in Qlikview and Spotfire.
• PowerPivot also simplifies the functionality available from SQL Server Analysis Service by including needed functionality which was previously available only in Visual Studio and SQL Server
• upcoming SQL Server 2012 is bringing up-to date the Visual Studio as BIDS (Business Intelligence Development Studio)
• Cube Functions in Excel allows access to any individual values, members and sets within any SSAS multidimensional cubes without using PivotTables;
• new DAX language can be used accross PowerPivot and SSAS (SQL Server 2012)
  

While this 3rd attempt has some value, Microsoft failed to match their competitor’s most important differentiating factor for front-end design:  ease of use and huge productivity gain. Specifically, the major problem is Excel 2010 has no visual drill-down capabilities.    Users cannot use a mouse as a drill down tool.  For example, it is not possible to outline a section of a pie chart or bar graph with a mouse, and have that outlined area be a drill-down selection.  Excel 2010 requires the addition of complicated and confusing “slicers” to allow users to drill into the data. Perhaps the problem is that Microsoft is too big and not well-managed.  Perhaps the Pivot team does not talk to the other BI development teams.

PowerPivot is forcing developers and/or users to construct and manage an intermediary “landing zone” for the data as it moves between PowerPivot and Excel. Let’s see what happens in this byzantine process:

• Whenever a user makes any attempt to drill into an Excel graph/chart connected to a PowerPivot database, Microsoft requires the developer of that application to manually create a landing zone (in the form of an extra worksheet).
• This landing zone has to be carefully tuned and connected for each appropriate list box “slicer” that the developer creates for the application (the list boxes in Excel 2010 are difficult for users to maneuver as it is not readily apparent how to select and deselect choices).
• This process generates huge overhead, as data moves from PowerPivot to the landing zone and from the landing zone to Excel. And it happens each time slicers are used to change the selection of the data.
• The problem is exacerbated whenever the user wants to synchronize and view multiple charts and/or graphs (which, of course, happens all the time), since each landing zone and chart need to be refreshed from scratch and completely repainted.
• Each slicer is either disconnected from a chart or connected only to a designated chart. In order to enable synchronization between all the charts and slicers, each slicer needs to be manually reconnected with all involved charts. Imagine that you have 20 charts and 30 slicers and assume each slicer is already connected with one (1) landing zone. In order for your application to work properly, the developer has to manually hard-code 30*19=570 manual reconnections.

Having said all of this, many companies have Excel and PowerPivot, and will decide to use it in hopeless attempt to solve their reporting and analytical problems… Be warned that Excel 2010 as a front-end for PowerPivot is (VERY!) limiting users’s ability to have a real Data Visualization and Dashboards. Two simplest examples of stone-age DV limitations from Excel 2010 are:

• none of Excel Charts are interactive and they cannot be a subject of Qlikview-like of “outlining of rectangle” instant drilldown operations in plot area(s) to trigger immediate and synchronous drilldown of Visible Data. In order to emulate that, Excel is forcing users to employ ugly Slicers and Pivots for filtering operations (and as I said above, Slicers had to be preliminary and manually reconnected to ALL Pivots who are datasources for Charts);
• many types of Charts (I am aware of at least of 15 types) are excluded from any relationships with Pivots and Slicers, e.g. all Scatters, Bubbles and Stock Charts.

In any case, it is clear that PowerPivot is a large part of the future of Microsoft’s BI stack.

October 2011 Update: In 2012 Microsoft looks much better with its updated SQL Server 2012, new Tabular Mode in SSAS (SQL Server 2012 Analysis Services), PowerView as interactive Report Designer/Viewer, Columnstore Index (which in effect makes SQL Server 2012 behave as a very fast columnar database), new DAX language across PowerPivot and SSAS, the very cool ability to use Excel 2010/PowerPivot as Cube Designer and publish PowerPivot Cubes into SSAS and of course ability to access “Excel and PowerPivot Services” directly from SQL Server 2012 without any SharePoint involvement

Permalink: https://apandre.wordpress.com/tools/powerpivot/