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 Bing.com) 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 http://RichardLees.com.au/Sites/Demonstrations