End Users want performance and we have spent significant effort in getting speed in multiple areas.  Sometimes end users want to get very very large cellsets into Excel (many times they are not sure why !!).

At an Outline level performance consists of 3 elements

i) MDX Creation

ii) MDX execution and Retrieval

iii) Visual Display

At XLCubed we have spent significant time looking at each of these separate elements and optimising them as much as possible. We provide a significant number of options such that end users can get what they want in the fastest possible way.

We now provide 3 methods to get data from Analysis Services into Excel and in descending performance order .

a) MDX Direct (Raw Speed)

A simple VBA function call which operates in the fastest possible way and can bring hundreds of thousands of cells into Excel from Analysis Services.

 We have been able to get well over a million cells in under 10 seconds..

The Grid provides a highly interactive and fast row and column layout for the end user. It does not use Excel formulae yet provides for the insertion of columns, provides drill down, drag/drop and many other features.

And it can put over 100 thousands cells into excel in just a few seconds. The best comment we have had from our customers/prospects was that they were unsure that they had drilled down as it seemed too quick and this was on big sets of data not 20 rows and columns !!

The Grid was tested independently during 2003 when it was released as XLCubed Explorer. http://www.johnkeeley.com/excel_add-ins_speed_test.htm

The table above shows the results for XLCubed.

If you wish to repeat these tests yourself here are the details:

Query 1: Foodmart Sales cube - columns: Time.1997 & its descendents, Measures.Sales Count; rows: Customer.Name

Query 2: Foodmart Sales cube - columns: Customer.City, Gender.Gender, Measures.Sales Count; rows: Product.Name, Time.Quarter

Spec. Pentium 4 (2.39Ghz), 256 RAM, Windows XP, Office 2000, local cube on PC.

Since then we have made a number of additional improvements and with the release of version 2.5 (which is in beta as at 1/5/2004) further improvements have been made and we will be updating this paper when this is in the market.

c)     Formula Mode

The popularity of XLCubed version 1 was its versatility, ease of use and integration with Excel. Complex reports were literally painted inside Excel using all the techniques that Excel End users had used to construct there management reports over the years.

The 2 core XLCubed formulae are XL3Lookup and XL3MemberLookup.  End Users like the simplicity and understand by looking at the formulae how they work.

They consist of two elements

1: The Cube that is being referenced

2: A set of dimension and value pairs

XL3Lookup(1,"Time","2003","Measures","Hours") - Returns the total hours for 2003
XL3Lookup(1,"Time","2003","Measures","Hours","Department","Finance") - Returns the total hours for Finance in 2003

XL3Lookup(1,$G$2,$G$3,$H$2,$H$3) - Returns whatever is in the Cells G2,G3,H2 and H3

Why is this important to end users - the reason is transparency and consistency. One of the big problems of spreadsheets is that they grow and grow and that the inherent flexibility is maximised . End Users will cut and paste , add new columns, new rows, that each cell is understood.

One side benefit of the formulas that we have seen customers use is that they have been able to write custom macros which convert existing spreadsheet formulas into XLCubed formulas such that the exact look and feel of the spreadsheets are maintained.  In one instance where a previous well-known OLAP Engine set of cubes was replaced with a single analysis services cube, the consultants wrote a macro which each end user could use on any existing spreadsheet to convert to the new Cube and XLCubed.

In version 2, we have not changed the core formulas so far as the end user is concerned but we have changed the engine significantly.

1. The formulas are now exposed through an XLL
2. A new multithreaded data engine using OLEDB connects to the server
3. A new caching and MDX generation process is in place inside the data engine
4. A new on the fly optimisation process has been introduced

As a result formula reports with thousands of cells now refresh quickly.

There has been no compromise in functionality, the end user has the illusion that each cell operates independently so the worlds worst report can be created as every cell can get any number from any cell in any cube.