Excel Performance with OLAP Server cube

[Dave]

I'm a new user to OLAP Server but have an extensive background with
other BI products. I've built a cube with approx 6000 rows in the
fact table and four dimensions. One of the dimensions is flat but
quite large with over 8000 records. When I look at the Metadata
screen in Analysis Mgr, it tells be the cube is only .5 mb.

I'm using Excel PivotTables with Microsoft OLE DB Provider for Excel
v8.0 to display the data. When I layer in the large dimension with
several other dimensions, I get a "Running OLAP Query Message" that
run forever.

I know an 8000 member dimension is excessive but I've worked with
other tools that could do this easily. Is the problem in the cube
design (it seems very small in terms of size) or in Excel as a query
tool. Any feedback appreciated.

Dave

[[MS] Richard Tkachuk]

An 8000 member dimension is in no way excessive. It's actually pretty small.
Your cube should fly with nominal hardware (eg, the kind my kids use to play
Age of Empires at home:).

The size doesn't seem out of whack. A simple test is to compare the size of
the cube with the data in your source tables. Typically, Analysis Services
takes about 1/3 of the space the relational source data requires.

A couple things to check what's killing your performance and some possible
remedies:
- That large and flat dimension. If a member has 1000s of children (don't
confuse this with 1000 members at each level - what you want to avoid are
individual members have lots and lots of children) performance will be
slower. If you can restructure that dimension to introduce another level to
distribute its leaf members differently, you'll see better performance.
- If you can't restructure your dimension, you can try playing with the
"Large Level Threshold" property. This is the property that determines
whether a level is sent from the server to the client en masse, or whether
each query brings down what it needs. When queries bring the members it
needs, it ups the chattiness of the client/server communications and slows
things down. The default value 1000. This means that any level with <1000
members is not sent to the client at connection time. You can change the
default in the Properties dialog in Analysis Server. If you up this, you'll
pay some cost at connection time (when your large level is sent to the
client) but your queries will be faster.
- Network bandwidth. If the client and server are exchanging lots of
information, a slow network can destroy performance. To test this, try your
query from the server itself.

Hope this helps,
Richard

--
This posting is provided 'AS IS' with no warranties, and confers no rights.

"Dave" <davi...@hotmail.com> wrote in message
news:4e5222b5.02081...@posting.google.com...

[Bill Koran]

Much of this is a repost from my past newsgroup responses and questions--

With calculated members, Excel can be much slower (orders of magnitude!)
than other clients. We were using Excel (PivotTables) for our sole OLAP
client, because we are working with engineering rather than business data.
The broad capabilities of Excel for charting and analysis, plus the easy
customization via VBA, made it a great choice for us. However, it can be
much slower than other clients when querying for calculated members.

The MDX Property of the PivotTable Object
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/htm
l/xlproMDX.asp
shows how to get the MDX "that would be sent to the provider to populate the
current PivotTable view", whatever that means. (Would?) The MDX shown by
using this property is verbose and ugly.

Excel is fantastic in other respects, but, compared to some other OLAP
clients, the query performance with calculated members is terrible. Network
bandwidth limitations will greatly exacerbate the problem. We added
ProClarity to our toolbox almost solely so we could get our queries returned
more quickly.

Before spending much time on cube redesign, try the same queries using
another client or Excel add-in that generates its own MDX. Downloads were
recently available for Excel addins XL3 (www.xlcubed.com), BIXL
(www.bixl.com), and MIS Plain (www.misag.com/plain).

Details on MDX issue:
Excel apparently generates MDX that requests data from the (All) level of
dimensions even when not requested, and even though it isn't displayed in
the PivotTable. Many of our calculated members go to the most granular data
to calculate what we need, so the Excel problem can cause a query using
calculated members to look at every record in the fact table. Other
browsers do not suffer from this, but limit the result set to what was
actually requested.

--Bill

"Dave" <davi...@hotmail.com> wrote in message
news:4e5222b5.02081...@posting.google.com...

[Hangyal Zoltan]

OLAP NaviGo is another great Excel add-in.

You can download it form www.olapnavigo.com

Bill Koran írta:

[John Keeley]

Hangyal,

Olapnavigo appears to be a simple to use add-in that is good for
viewing data; much, much better than pivot tables.
It doesn't have writeback like BIXL & XL3 though.
Two different market segments; BIXL/XL3 for creating disjoint reports
that support writeback, & Olapnavigo for more straightforward slicing
of cube data.
Would you agree?

Regards,

John

Hangyal Zoltan <han...@novosys.hu> wrote in message news:<3D5ACA49...@novosys.hu>...

[Bill Koran]

Hangyal,

I thought OLAP NaviGo looked very good, so I downloaded the trial version.
The documentation looked good, and installation appeared to go smoothly.
However, Excel failed to open every time I tried to open it after enabling
OLAP NaviGo. I eliminated all other add-ins, and items in the XLSTART
directory, and the problem persisted. Alas, though I am of Hungarian
descent, I do not know any Hungarian so I could not use the Novosys web site
to look for tech support.

I tried it with Excel 2000 on Win90SE; I don't like trying new things on my
production machine, which is Excel XP on Win2000.

--Bill

"Hangyal Zoltan" <han...@novosys.hu> wrote in message
news:3D5ACA49...@novosys.hu...