Saturday, October 17, 2009

Excel 2007 OLAP PivotTable Tips

I am a great fan of Excel as an ad hoc cube browser, even though it lacks a few features I would like to see. Many people don't appreciate some of the functionality available within Excel. Here are a few of my favourite features.

  1. Right click somewhere in the pivottable and select PivotTable Options. Get familiar with all the options. My favorites are Refresh data when opening the file and disabling row/column totals.

  2. Use conditional formatting within the cube. Excel, will honor these cell formatting options by default.

  3. Use Excel's conditional formatting on top of number formatting. Excel will hold the conditional formatting until you change what's on rows.

  4. When you have a cube with multiple measure groups (don't they all now?) use the Show fields related to: to filter measures and dimensions pertinent to your query.

  5. Use the Design tab to remove subtotals by row or columns. Removing sub totals here, will be preserved when you continue pivoting, whereas removing sub totals from rows/columns will not persist.

  6. Also use the Design tab to insert banded rows (or columns)

  7. Right click on the Row or Column header to request sort by measure columns (ascending or descending) or even some other column value.

  8. Right click on the Row or Column header to request Top 10 list. Note, this is really a Top n list, as you can determine how many rows/columns to display. Particularly useful when you have too many columns (rows) and you just want to show the top n.

  9. Right click on the Row or Column header and use the Show/Hide Fields option to remove hierarchy levels from the display. For example, you may want to show cities, without the higher levels of state, country and region.

  10. Right click on the Row or Column header to display member properties. Particularly useful for properties such as phone number

  11. If there are additional actions set up in the cube (a real drill through, for example) right click on the cell (or member) to trigger an Additional action. I find this particularly useful when you want to drill through to a report that shows all the underlying facts, with all their associated details. It can also be useful to take you to an independent application that takes some information from where you are in the cube. For example, Google search (or for a member name.

  12. Right click on a bunch of rows (or columns) to Group them together, as one member.

  13. Get your cube designer to create useful sets within appropriate dimensions. You can drag a set onto rows/columns. For example, Last 10 weeks, or New Customers etc.

  14. If you have a large cube (or poorly designed one) that is slow to query, then check the Defer Layout Update box on the bottom of the field list. In this way, you can design your report without waiting for the data to display. Just click on the Update button when you are ready.

  15. If your query is taking too long, just click on your Esc button, and the query will be cancelled immediately.

  16. In the Options tab, select Offline OLAP under OLAP Tools, to save a copy (or subset) of your cube locally. Note, this will only be available if you have been granted local copy permission. Can be really useful, if you want to take your laptop away and analyse the data.

  17. For the very advanced user, in the Options tab, select Convert to formulas from the OLAP tools button. This will convert your entire pivottable to individual cell formulas of CUBEMEMBER and CUBEVALUE. From this point you will be able to create a report with full control on what is in every cell of the report. It really is quite powerful, although you lose the flexibility of a pivottable. A hybrid option I like is to keep the filters from the pivottable and have a report using the CUBEMEMBER and CUBEVALUE formulas, which gives you a bit of the best from each.
  18. To remove a row or column member, right click and select Filter, then Hide Selected Items (or Keep Selected Items). This is much easier than navigating through a selection list and checking the items you want (don't want).

Of course, there is a lot more functionality within Excel OLAP pivottable than I have highlighted above, but you can see that there is quite a lot of power hidden within the tool.

For online real-time OLAP, data mining, Excel Services, PerformancePoint demonstrations goto


danh said...

Interesting article here about Excel 2010 Pivotting ;

Now, it leads me to ask you - could you please clear up just how the Value Field Settings/Show As is supposed to work ? Only seems to work on % of row in my quick test...

Richard Lees said...

I think you are confusing Excel's OLAP cube browser, PivotTable, with Excel's pivotting tool, PivotTable. That is easy to do, they look very similar (on purpose) but the OLAP tool connects to an OLAP server, while the local tool pivots data resident in Excel.

danh said...

Hi Richard.
I am referring to...

In the OLAP pivot table;
* Right click on the measure,
* Select Value Field Settings.
* Show Values As tab
* There are now a range of options in the drop down.

Most of them seem to return #N/A, but the "% of row" works ok.

This may be my cubes setup, or an Excel issue as you indicate, but it is intriguing...

The implication from postings are that this will all work in 2008 R2/Excel 2010 with all the additional options.

Rob said...

Hi guys. At MS I worked on Excel, specifically on PivotTables and related stuff, for a number of years. I now work on the PowerPivot team (still at MS), and you might want to look into it, because there are a lot of goodies on the way :)

You can start with my site,

I'm happy to field questions on the site, as well, or in email.


Shirley said...

Hi danh,
I'm having the exact same problem as you with the N/A appearing whenever I try to do a % Of value field setting on a measure in my pivot table that is browsing a cube. When use the same function on worksheet data, it works fine. Did you ever find a solution? Thanks.

sowmya123 said...

Good and interesting post to excel learners.

Gabriel M said...


I'd like to add a SSAS action that opens a folder based on a pivot cell (displaying a dimension).

I can't achieve this with Excel, thus, with proclarity it's possible.

Whenever I enter a network path in the Action Expression, Visual Studio prompts me the following msg:

"URLs that doen't begin with HTTP or HTTPS are considered unsafe and will not be displayed in most applications"

Is there a workaround in Excel in order to avoid this stuff?