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/

2 Responses to “PowerPivot and SSAS”

  1. Aaron Says:

    Agreed. My IT department has gone with the full Mircrosoft strategy, leaving the data analysts (Me) needing to create user complex interfaces. In the end, I spend more time training people on how to use powerpivot, excel, access, and sharepoint than presenting figures. The complexity makes most clients run in fear of hearing any technical terminology, and my hope after every action is that the software doesn’t have an intermitent crash and reboot to start over.

    We have an old version of Qlikview available (only compatible with Access 2003 files) and it is shuned from use. Funtastic!

  2. Naren Says:

    With 2013 Office, I guess most of the problems that you have stated has now been resolved. With Power View drill-down is made possible. More than anything now Power View is integrated completely into Excel making Excel one of the most sought after BI Tools. Over and above all of this, Organizations need not worry spending thousands of dollars on getting BI Tools, its anyways an integrated suite available in Office.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 260 other followers