Comparing Excel 2007 and ProClarity

I use Excel 2007 and ProClarity to browse SSAS cubes. This is what I experienced.

1. In ProClarity you can put measures on the background so the user can pick which measure they want to use. In Excel 2007 you can’t; you can only put measures on the column or row.
2. If an attribute has 5 members, in ProClarity you can select [member3, member4 and All]. In Excel 2007 you can’t; you can only select All, or select several members, but not both.
3. If an attribute has 100,000 members, in ProClarity you can search for a member, to display only a certain member in the row. In Excel 2007 you can’t; you need to display all 100,000 members, then deselect All and select the one you want to display. Or, highlight several members, right click, select Filter, choose Keep Only Selected Item. But you can’t search without first displaying every single member, which can take a long time.
4. In ProClarity you can create a set of members. You can either pick the members manually, or define criteria for inclusion/exclusion using Selector. In Excel 2007 you can create a group containing several members. You can only pick the members manually. You can’t define criteria for inclusion/exclusion. Note on performance: creating a set containing 5 members out of 50 by picking them manually takes 1 second in ProClarity. In Excel 2007 it takes 55 seconds.
5. In both ProClarity and Excel 2007 you can link to a URL (action). However, I found that if we need to login to reach that URL, Excel 2007 failed to pass the parameter to the URL, so it landed on the front page of the app. Where as in ProClarity, after logging in it open the appropriate page.
   Note: after using ProClarity, if we use Excel 2007 again, Excel will open the correct page.
6. In SSAS we can have attach an action to the measure: target type = Hierarchy members, Target object = measures. For example, the action expression could point to a URL explaining the meaning of the measure. In ProClarity, we can right click the measure name and select the action. In Excel 2007, you can’t do it; the action is greyed out.
   Note: If the action is attached to Dimension members, Attribute members, or Hierarchy members, it works on both Excel 2007 and ProClarity.
7. In ProClarity you can drilldown: right click on a member, select Drill Down, and then select the attribute. In Excel you can’t. You need to manually move the attribute to background, set the background attribute to a certain member, and then put the attribute you want to drill down on the row or column.
8. In ProClarity you can create a calculated measure, such as ratio and percentage difference (A/B and (A-B)/A). Date-based calculation is also available, such as year-on-year growth, rolling average and year-to-date totals. We can then use this measure just like a normal measure, e.g. we can drill down on any attribute. In Excel 2007 we can’t define a calculated measure. If you format the area as a table, and define a new column on the right of the pivot table, the column will just be calculating the cells. It is not a measure. You can’t slice it.
9. In ProClarity there are no row headers. If you put 5 attributes on the Rows: order number, date ordered, date delivered, date invoiced, date paid, you will have no idea which dates are on which columns. Viewing it in ProClarity is not too bad, as you can see the attribute names on the Rows. But when you export the page to Excel, there is no indication all which attributes were put on the Rows. See below. Column attributes are fine by the way (measure1 to measure5 below).
   
   In ProClarity, Flattened Row Headers (on the Grid Properties) has nothing to do with row header. It’s when you put a hierachy (rather than an attribute) on the Rows and you want the value to be displayed not like a tree, but left justified.
   In Excel we have row header. So we can clearly see which attributes are on which column.

Qlikview vs PowerPivot: connecting to SSAS cube

In QlikView we can’t connect directly to SSAS like in PowerPivot. We need to create a OLE DB linked server first, then use OpenQuery. So the output is flattened rowsets. In PowerPivot we can specify MDX statement and get cellsets e.g. they have axis. For differences between flattened rowsets and cellsets, see this Brian Welcker’s post.

PS. For normal Excel table or pivot table (not PowerPivot) we can specify MDX query by editing the .odc file, see Chris Webb’s post here.

PowerPivot

PowerPivot is an in-memory OLAP, like TM1 from IBM and QlikView from QlikTech. It is a SQL Server 2008 R2 add-on installed on Excel. Like Analysis Services, using PowerPivot we can create a ‘cube’ in Excel, which we can then slice and dice. The data for this ‘cube’ can be from database tables, Excel files, SSAS cubes or a combination of them. That, I think is the most important feature of PowerPivot: it allows us to combine data from databases, Excel and cubes so that we can analyse the data.

Users opens Excel, fill in a few sheets with data (or import it from database or files), then build the ‘cube’. It looks like the normal PivotTable, but it has slicers, which enable us to ‘cut’ the cube. The ‘cube’ can be published on Sharepoint so other users can ‘browse’ it. It is striking how similar that ‘cube’ to a pivot table. Under the concept of self-service BI, PowerPivot is aimed not for IT department, but for business users (data analysts/power users), e.g. MIS / MI department. It is so important I will repeat it here: PowerPivot is not for BI developers, it is for business users.

The ‘cube’ is called “PowerPivot pivot table”. To contruct a PowerPivot pivot table we need to a) create some tables in Excel, and b) relate those tables. A ‘table’ in Excel, is an area of cells that we mark as table. There are 2 ways of creating a ‘table’ in Excel: a) type it in Excel then ‘format as table’ (it’s in the Home toolbar), or b) import from database, file, or other sources.

I am of the opinion that PowerPivot will increase the demand for SSAS, rather than reducing it. This is because:

a) it encourage users to create OLAP and use it. Then after a few months, there will be a mess: cubes everywhere. And there will be a need for project to pull that scattered mess into a big SSAS cube (or two, or three).

b) because you don’t need to spend 6 months (or a year) to build a data warehouse. If the users have the data in Excel, they can immediately analyze it with PowerPivot. They can even combine it with data from any database. How powerful is that? This will only go to 1 direction: more need for a data warehouse. Imagine a company with a hundred data islands, from New York to Singapore, in the form of PowerPivot tables and charts sitting on users’ Excel files. As sure as eggs is eggs, they have a greater need for a data warehouse. Then naturally, SSAS cube follows. Sitting on top of the DW. Accessed via http from everywhere, from New York to Singapore.

c) because of the limitation of PowerPivot. There is no hierarchy. If you can’t compose a hierarchy, how do you drill down? True, we can create calculated member using DAX, but the capability is very limited. As soon as this limit is reached, there is will be a conversation similar to this: [user to IT]: how can I do this in PowerPivot? [IT to user]: eerrm, you need MDX.

PowerPivot is deliberately intended/targeted for business users, probably with no background on warehousing. People who never heard about Kimball or Inmon (that debate is really getting more religious than rational isn’t it?). Listen well to this: “Create a lookup relationship between two tables”. That’s the terms PowerPivot use to refer to the relationship between fact and dimension. Perhaps it’s me who got it at the wrong end of the stick: I have a data warehousing background, but PowerPivot is not for DW. It can take any tables, not just fact and dim. (And so can SSAS btw – see my post here). If I do get it wrong, please remind me, correct me (vrainardi@gmail.com, or via comments). I’d be happy to be corrected.

Below I’ll illustrate how to compose a ‘PowerPivot pivot table’ — aah can’t we have a shorter name? Four words! Sod it I’m going to use the word cube, to differentiate to normal pivot table. To get a flavor of what PowerPivot is, I’ll illustrate how to compose a cube in PowerPivot, from Excel data. Of course the source can be replaced by query from DB tables, etc. Please don’t think it as a ‘how to’ but to enable us to see in a glance what it is. For ‘how to’ there are lots of resources, please refer to the links at the bottom of this post.

First, install Excel 2010 (download from here), then install PowerPivot (download from here). Open Excel, create some data, like this for example:

Format each ‘table’ as table by clicking on ‘Format as Table’ on the Home toolbar. Then, PowerPivot tab (see below) and for each table click on Create Link Table, which means ‘make this Excel table available in PowerPivot’.

Then we click on ‘PowerPivot window’ and rename the tables at the bottom of the screen: (this is so that we can identify which is which when we create the pivot table later on)



Then create relationship between the tables by clicking Table tab, Create Relationship:

PowerPivot resources:

1. PowerPivotPro: one of the first sites on PowerPivot. Contains introduction to PowerPivot, case studies, examples, and lots of blog posts by Rob Collie.
2. PowerPivot-info.com: the most comprehensive portal about PowerPivot containing links to (almost) every single articles, post, news, author, books, etc about PowerPivot. Thanks to Vidas Matelis for creating this site.
3. PowerPivot.com: Microsoft site. We can download the November CTP version of PowerPivot, try it in the virtual lab, and watch the video clips.
4. PowerPivot on Excel team blog: Introduction to PowerPivot by Excel team.
5. Bob Duffy presented PowerPivot at SQLBits5. His presentation is here (2.5 GB, pptx)