Re: Using Excel Pivot table as front viewer

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mark Landry (mlandry_at_tampabay.rr.com)
Date: 04/06/04 

Date: 6 Apr 2004 15:03:45 -0700

Be especially careful with calculated members on dimensions other than
[Measures]. There is no equivalent property like "Non Empty Behavior"
for other dimensions so there's no way to short-circuit the NON EMPTY
processing included in Excel-generated MDX.

For example, consider a simple dimension [CalendarYear] with month
members from [January] through [December]. It's often useful to have
this dimension to crossjoin with a [Time] dimension to yield months
vs. years on rows and columns.

Create the calculated member, [CalendarYear].[YTD] as
'Aggregate([January]:[March])'. It's convenient because it slices
across all years and measures, making it easy to get year-over-year
YTD values for any measure. And it avoids creating and managing all
those annoying "YTD_measure" calculated measures as shown in all the
MDX books.

For a sparse cube (and let's face it, all cubes are sparse), as you
add more dimensions to the pivot table more cells are empty.
PivotTableServices will gobble CPU on the client calculating the YTD
for all these cells only to discard the empty results because of the
"NON EMPTY" adjective in the MDX statement.

Even trying to push calc-member evaluation to the server with
"Execution Location=3;Default Isolation Mode=1;" won't help much
because an agent thread inside Analysis Services will do all the same
calculations on your behalf. The benefits are possibly a faster CPU
and avoiding locking-up the Excel user's desktop.

And aggregates don't help either because the base query is resolved in
several milliseconds while evaluating the calculated members can take
minutes or hours pegging the CPU at 100%.

Furthermore, calculated members on regular, non-Measures dimensions
are not displayed on rows and columns in the pivot table. You can only
see them in the "PAGE" area.

And if the dimension containing the calc-member is in the pivot table,
whether or not it's displayed, calculated members are always
calculated. They are included in the MDX generated by Excel in the
AddCalculatedMembers function call. As far as I can tell there's no
way to turn this off in Excel 2K or XP.

The well-written Performance Guide cautions against using calculations
in large cubes in the hundreds of gigabytes. But I've seen very poor
performance with simple calculated members (as described above) in
cubes with 13 dimensions and only 87MB.

This is unfortunate because one of the strengths of AS is the rich
environment to create calculated members with functions and operators.
And having learned their power and elegance, to banish them from
anything but a trivial cube. Sad.

Relevant Pages

  • Calculated members slow performance unacceptably
    ... I need to be able to nest several dimensions on the rows, ... addition of 4 base members and so the Non Empty Behaviour setting will ... I have tinkered with the aggregations on the cube but it has made no ... it all seems to be linked to the calculated members. ...
    (microsoft.public.sqlserver.olap)
  • Re: MDS 101 : Please help - basic question
    ... >calculated members and has a distinct count wizard only allows me to select ... > to treat it as a measure, but the OLAP tool I have which allows for ... > dimensions as a place to create it. ...
    (microsoft.public.sqlserver.olap)
  • Re: OLAP and EXCEL 2000
    ... calculated members (customer member formula) to your dimensions. ... if possible have your client upgrade to Excel XP ...
    (microsoft.public.sqlserver.olap)
  • Re: CALCULATED MEMBERS IN EXCEL 2000
    ... Excel 2000 only supports calculated members in the measures, ... It does not support calculated member in one of the dimensions. ... Excel 2002 and later do support calculated members in dimensions. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Create empty 3-by-3 matrix
    ... an empty matrix (as defined by ... 3-by-3 matrix does not have size 0 in any of its dimensions, ... Note that this is not a matrix but an N-D array, ... after the first deletion, A will be reshaped to an ...
    (comp.soft-sys.matlab)