I'm using Excel Pivot table as front end viewer and connecting to OLAP at
backend.
If I drop more dimensions/grouping levels in, it will take long time to run
OLAP, the more levels the longer times it needs, even when the fact table is
small.
Is there any other ways around this to cut time to a reasonable level? Or
some other front end viewer as flexible as Excel Pivot table?
Thanks in advance!

AW

This is why there are specialised Frontends available. I would suggest
go for Panorama NovaView as webbased frontend. On our website
(www.gmsbv.nl) you will find some links to production OLAP websites
where you can test the features and performance over the internet. You
can even order on our website the 30-days Trial Panorama NovaView BI
3.5 Platfrom for free and test it with your own data on your own
server.

Regards,
Marco

www.gmsbv.nl

"awan" <afw340 (AT) hotmail (DOT) com> wrote

what type of storage are you using. MOLAP/HOLAP/ROLAP?
The speed with MOLAP will be the best.

Have you tried caching options?

Please let me know if you need further help.

Thanks,
Abhinav Kumar
akumar (AT) itmag (DOT) com.au

Excel does not use the NonEmptyCrossJoin clause, and this is the main reason
for poor performance, when you drop more dimensions. May be in Office 12....
Best
Szymon Slupik, CTO
CDN S.A., Krakow, Poland

"awan" <afw340 (AT) hotmail (DOT) com> wrote

If any calculated measures are dropped in the data area, then their "Non
Empty Behavior" Property should be set, where possible, to refer to a
base cube measure.

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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.