Data Warehousing and Business Intelligence

4 January 2010

Comparing Excel 2007 and ProClarity

Filed under: BI Tools — Vincent Rainardi @ 9:03 pm

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.

1 Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

You are commenting using your 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

Blog at

%d bloggers like this: