Improving Excel's Cube Performance

I am often asked to help with Excel's query performance. The cube is fast and efficient, but some Excel queries are slow due to the MDX that Excel is generating.

This is unfortunate, but there a couple of things you can do about it. Let me describe this particular issue. Essentially, you have a dimension with a large number of attributes. In Excel, you have crossjoined many of these attributes on rows, which you feel is reasonable since you have also added a filter so that the query will only return a small number of rows. For example, here is a query of the FoodmartFull database, where I have filtered on just one customer surname (Peacock) and asked for many customer attributes on rows. This query takes 9 seconds for SQL Server 2008 to execute. The reason it takes so long is that the MDX is asking for a crossjoin of all the attributes, at all levels. Note, this issue can be so bad that the results are not returned before timeout.

I have traced the MDX using SQL Profiler. See the MDX in the picture to the right. (I have formatted the MDX. Wouldn't it be a nice thing if SQL EM would format SQL/MDX for us? That's a topic for another blog.) If you execute this MDX, you will receive 3840 rows! That is a huge number, when you consider there is only one customer and Excel is only displaying 1 row. (I asked for a tabular format. But even if I ask for a compact format, I only get 14 rows.) Understandably, this MDX takes 14 seconds to execute.

The reason I chose this particular query is that all of the attributes you see in the Excel report are from the same dimension. Having all these attributes from the same dimension should be very easy for Analysis Services to return quickly, since it can determine, from the dimension, exactly which Customer cells/aggregations it needs.

I can edit the MDX to simply ask for the crossjoin of the leaf members, which will execute in just 1 second. This is much easier for Analysis Services since we are only asking for asingle row rather than 3840. See the second query.

Of course it is not possible to edit Excel's MDX to make the query faster, but we do have other tricks we can employ. Since the problem is that Excel is inefficiently crossjoin-ing all of the attribute hierarchy sets, we can help Excel by creating a "whopper" user hierarchy that includes all (or many) the attributes we want in our report. If we had a Customer hierarchy with these attributes, Excel wouldn't need to ask for any (or so many) crossjoins and the query will be sub second. Of course, I am not suggesting that you create "whopper" hierarchies all over the place, but it is a consideration. Also note, you do not need to create one "whopper" hierarchy, just a couple of multi-attribute hierarchies might do the trick. This is because the cost of adding (or reducing) the number of hierarchies has an exponential effect on query performance. Each attribute you add will crossjoin the new set (it is not a single member set) with all other rows.

Another, much cleaner, option, if possible, is to simply show properties of existing members, rather than ask for a crossjoin. I say, if possible, as this is only possible if the parent attribute is in the report. Naturally, this is much more efficient, as the generated MDX is simply asking for the member properties of existing members (crossjoin not required).

Whenever there is an Excel performance issue, I would encourage you to trace the MDX using Profiler. Even though you can't directly change Excel's MDX, it will help you understand the problem and give you some ideas to work around it.

For a list of Excel PivotTable tips see http://richardlees.blogspot.com/2009/10/excel-2007-olap-pivottable-tips.html

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

What's preventing Excel from being the ubiquitous cube browser

I have said earlier that Microsoft’s Excel 2007 is not the ubiquitous cube browser that it should be. What’s wrong with it? Well nothing is really wrong with it, but there are several features, that I would expect from a product that is 10 years old. With OLAP services first release 1998, there was an update for Excel PivotTable. As I understand it that first version was largely the result of one great developer (TC). However, since that release, the upgrades have been unimpressive. Excel 2007 is a great improvement on Excel 2003, but it still has a long way to catch up to the sophistication that is available in Analysis Services. Here are a few of the things I would like to see in Excel

• Full support for calculated members. They are partially supported, calculated measures are OK, but other calculated members are treated as second class citizens.


• Ability to create calculated members that are stored within Excel, and available like any other calculated member for that user.


• Support for 3 dimensional queries. Currently, a user can only ask for a two dimensional report. Excel, is inherently a 3 dimensional space when you include worksheets. So I would like to be able to create a PivotTable with a dimension placed on the worksheet axis.


• Support for naive users who open up a dimension member list with a million members. For example, ask them if they really want the one million rows (columns) and perhaps defaulting to a top 100 list.


• More optimised MDX. Sometimes I despair with the performance of pivottable queries, and when I look at the MDX I see that it could run quickly with more efficient MDX.


• Ability to see properties of ancestors


• Better integration into Excel spreadsheets. For example, ability to format measures and PivotTable to remember these, even after drill down, nesting etc.


• Ability to define sets.


• Eposure to some set functionality in a gui fashion. For example, it might allow the user to select a crossjoin on rows with an exception function to exclude some members based on a filter criteria.


• Ability to right click on members to “select only” or “deselect”


• More control over charts. For example mixing the dimensionality of measures so that one measure is cumulative on series and the other is unrelated to the series.

Don’t get me wrong, even with this wish-list, I am a great fan of Excel PivotTable. As a rich client cube browser, it is still my favourite. It’s just that it isn’t keeping up to Analysis Services, which is truly a market leader in the OLAP field. In a later blog, I will write about my favourite features of Excel pivottable, many of which are not immediately obvious.

For real-time OLAP, Data Mining, Excel Services and related demonstrations goto http://RichardLees.com.au/Sites/Demonstrations

Which cube browser for Microsoft OLAP

I am often asked which cube browser I recommend for Analysis Services cubes. The answer is typically a few minutes long, since there isn't one ubiquitous cube browser that satisfies everyone. It's a bit like asking someone which vehicle you recommend. It really depends on what the purpose is and what you value. A high performance sports car might be appropriate for some people, but would be inappropriate as a family car or to tow the boat to the coast. For me, the best vehicle is a sports bike, but this would be inappropriate for most road users. The same applies to cube browsers, some users need something very simple and easy, while others need sophistication and flexibility. Having said that if Microsoft Excel had a few more features, it could quite easily be the ubiquitous browser.

Two cube vehicles that complement each other well, unsurprisingly, are Microsoft Excel and SQL Server Reporting Services (RS). Excel is very flexible, while RS can deliver tailored reports, with flexible parameters and easy links to drill down/across to other reports.
In this way, RS is perfect for naive users that need easy to consume information with click-through for more detailed and associated information. Note; RS does not support ad hoc cube browsing. Microsoft also have PerformancePoint, which is a great tool for delivering visual data to a thin client. It has functionality somewhere in-between Excel and RS and many features not available in either. PerformancePoint supports (very rudimentary) ad hoc cube browsing, but it is very powerful at delivering visual and interactive data. So PerformancePoint is ideal for presenting visual data to naive users, and for data savvy users there can be a great deal of “data flexibility”. PerformancePoint can deliver this in a semi structured way, so that users don't drive a cube browser, rather, they drive a dashboard of their data. There is a good example of rich PerformancePoint dashboard on SQLS Live Dashboard. Notice how you can click on scorecard items, and they act as filters on the charts.

Another important cube browser (although not yet truly ad hoc) is Excel Services. Excel Services is essentially an Excel spreadsheet accessed through a thin client, Internet Explorer. Any spreadsheet can be published, even one that is attached to an OLAP cube, so it can be a useful vehicle to deliver live cube data. There are a couple of good Excel Services examples on NZ Census. Notice how you can interact with the spreadsheet by drilling down hierarchies and applying filters. Note too; how the data is live (you might want to switch to the weblogs spreadsheet). However, in the current release of Excel Services you cannot change what is on rows, columns and filters. I have many customers that love Excel Services, since it permits their Finance dept to design and publish reports entirely within Excel, and their report users always see live data.

There are hundreds, if not thousands, of third party cube browsers. They really aren't that hard to develop.
I wrote the first thin client cube browser, ThinSlicer, which is still used by many organisations. When I wrote the first version of ThinSlicer, in 1998, I thought that its shelf life would be short; surely Microsoft will offer a highly functional, low cost, thin client cube browser? How wrong could I be? It is now 10 years on, and the only ad hoc thin client cube browser we have from Microsoft is PerformancePoint, which while it has an ad hoc cube browser, it is not the reason anyone would buy it. An example of a more functional thin client cube browser would be Report Portal. This product supports quite strong ad hoc cube browsing and charting. I must add that there are many other commercial cube browsers, and I really don’t want to list them, since the list and relative strengths are continually changing.

If you are embarking on a data warehouse or business intelligence project, I would encourage you to leave the cube browser choice until quite late in the process. (Just like you shouldn't purchase your production hardware until you are almost ready to deploy a solution.) This is particularly true if you are not familiar with cubes and are not sure what you and your users will need. Microsoft’s SQL Server is probably the best foundation for a data warehouse/business intelligence solution. Particularly its OLAP cube and related technologies such as data mining. I would encourage you to use Excel 2007 and Reporting Services (which is free with any version of SQL Server) as the initial cube delivery vehicles, and trial other products when, and if, you find the need. If you are looking for better thin client visualisation, do try PerformancePoint and Excel Services before you engage with third party vendors. These products are very useful, and they are likely to be greatly improved in future versions. I should add that PerformancePoint dashboards will be incorporated into SharePoint MOSS in future, which makes a lot of sense to me. One other consideration, if you are delivering to users outside of your Intranet/internal network is licensing. SQL Server RS and Excel are quite easy to licence for use over the Internet. Unfortunately, it’s not so easy with PerformancePoint and Excel Services. If that is what you would like to do, I suggest you talk to your Microsoft reseller early on.