Re: Slow member loading in excel 2003 pivot table

I give up

I have a test cube, it loads member quickly... (I did lot of testing, various platforms, version, method...)

apparently when I go over 20000 member in the same level, it works fine if the members are "small" (less than 10 caracters long)

in my real dataset, some of the members are actually long... (100+ caracters)... that's the only difference I could see.

I'm gonna add a transform step in my cube loading and just truncate the data... (actually it should be "file extensions"
strings... I don't really need the extra data with only 1 fact/member and exotic file number...

A cool thing would be to remove all the "File Extension" member with a fact count inferior to ... let's say 3 and assign them to a "misc" member... I don't really see how to make it work... during transformation, or should I create a view.
Maybe a simply filter another file extension dimension table with a "(count(id)>3)" and set all the fact with the missing key error to a default member... (that way I could still have the whole dimension available, just in case...)

if there is a method with calculation or mdx or any intelligent way to do it, I will gladly accept any help available.

best regards
Christophe

"Christophe Niel" <Christophe.Niel@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:71DF3ED0-116C-4CB0-87E3-3D8CC192D0F9@xxxxxxxxxxxxxxxx
hmpf... i'm a bit perlexed right now

I have the exact same behaviour with the 2 cubes (2005 and 2000) on my workstation.

Unfortunately, I didn't have the time to check with a workstation where I did not install sql 2005 layer (visual studio, olap 9 dll, etc...)

As I said in reply to Jeje, I upgraded to build 3215 (sp2 CU 5) to be sure.

The more I think about it, the more I fear the "almost instantaneous" display of this dimension member was just a dream :'(

I'm gonna sound crazy, bu I need to find a way to create various sized dimension and see how excel react and display them, I want to range from 2K to 30K with a 2K step...
maybe there is a limit (let's say 16K) in excel where the combo box stays in memory and does not need to reload everytime I close and open it.

Maybe I will try with a simple excel pivot table, it should do the trick... anyway, I'm rambling and maybe excel 2007 will solve my problem... or not

regards
Christophe

"Christophe Niel" <Christophe.Niel@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:%23m$0wYXTIHA.4752@xxxxxxxxxxxxxxxxxxxxxxx
concerning the 3GB limit didn't work really well with a dimtree file weighting 800MB
I couldn't process the cube anymore or load 2 smaller cube with 500MB dimtree.
(this was a parentchild dimension with 900K member with 37 level max)

now the server has 4GB memory, bi-xeon 3.8Ghz, a 100Mbps switched lan connection, 200GB Raid0+1 disk for the data and only this application

I designed the storage the same way I did with as2000 : full MOLAP, with a 99% aggregation.

Processing time takes 21minutes which is coherent with the processing time I had on as2000

the Data file on disk is about 1.6GB


So a new input : with the cube browser in Visual Studio, the first load is long, but then the add/remove from the browser became instantaneous.

I did a bit of digging and once the first load has been made, there is no cpu peak or any disk access on the server during the next data loading.
on the worstation there is a cpu peak during the filling of the filter combobox or during the loading of the 24K lines in the excel rows (again no data displayed)

and it is longer than a similar member data set (the only difference is this big parent child dimension which is smaller)


I even redesigned sql layout and my cube, now I have a nice star schema, with a view or table for each attributes and hierarchy (let's say the previous design was a bit more 'I have everything in my fact table, so why bother'...)
now I have, for the same granularity and dimensions, "only" 500MB of olap database file and a process time of only 8 minutes...

anyway, even with this "optimized" version, I still have the same problem.(the drilldown is better now though)

I really think it's an OLEdb for Olap 9.0 problem : the slowness is really visible during the the 24K member loading, I have a small progressbar in the excel statusbar, and excel is freezed during this time.
I didn't change my workstation config and the only thing I did was install the olap9 drivers to use pivot table 2003 with AS2005.

it must be the control or the way the driver is telling excel to load the data. here what happens step by step
I drag and drop the 24K member dimension on the "filter" section of the pivottable. I have a really small delay (<1s)
- If the filter combobox is opened for the first time, I see the "+All" entry directly, no wait time
- I click the "+", I wait 15-20 sec, a progressbar advance at the bottom
- when it's loaded, I can collapse/expand at will, it works fine, I can select members
- if I click OK, I have a small query with a progress bar and the filtered data is displayed
- if I click Cancel, I have a small query with a progress bar and the data doesn't change
- in both case il I reopen the combobox, I get the "+ All", I expand it, I get a reload during 15-20 sec.

I have the same time (15-20 sec) and the same progress bar if I drag and drop the dimension in the "row field".


in comparison, with the 1800 member dimension, the display time is really quick, almost instantaneous, quicker anyway, relatively speaking, than the 24K members.


I wrote this post along a few hours of test, reload, re-design, profiling etc... sorry if it was a bit confusing.

with the various tests I can now confirm it's not a serverside problem and not a query execution time problem. I was a bit confused the "excuting olap query" and the loading progressbar which if fact are unrelated.

Next step for me :
I will rebuild a as2000 server with a simple cube based on the same data with only the 2 dimension (24K member and 2K member) and create the same cube with as2005.
No YMD hierarchy, no huge parentchild dimension, and I will measure exactly with the query time to list the members of a dimension (I can do that in adomd and a simple vbs, i think)
and I will compare the loading time in excel... that's more important.

But that will be tomorrow, now it's 8h30 pm and I want to go home :)

thanks for reading


"entaroadun" <johnny.c.kwan@xxxxxxxxx> wrote in message news:38ea76e6-914f-4050-851b-38c97e794705@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
24K records is nothing... How much memory is on your server, and what
other applications are running on it?

Also, it doesn't make sense that it takes 15 seconds every time you
add the dimension to a report that doesn't have it. The first time it
does, the cache is cold, and you may have disk I/O. After that, the
cache should be hot, unless you have very little memory available.

BTW, the 2GB limit was raised to 3GB on 32-bit with extended
addressing (I think). To get more than that, you need the 64-bit O/S
and SQL Server.

It sounds like there's more going on... Maybe it's network I/O. Are
you running a thick client? Is it on a really slow LAN or VPN?

On Jan 2, 7:53 am, "Christophe Niel"
<Christophe.N...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi all

And happy new year.

I'm still reading, and I'm looking in this newsgroup history but I can't
really find an anwser so maybe one of you Guru of MS Olap 2005 will be able
to help me,... again ... :)

My problem is the time a dimension takes to load through the ole client for
msolap 9.0 in excel in comparison with the msolap 8.0 and good old as2000

I have one big dimension with about 24K contiguous members (bad, i know),
and smaller ones with 1-2K members. when I was using the same dimensions
with olap8.0 the loading time for the filter combo box, or when I
drag-and-droped the dimension in the row partof the pivot table it was
almost instantaneous for the small dimensions and took a few seconds for the
big one.

Now with msolap 9.0 it is three or four time longer (I haven't measured
precisely, let's say I go from 1-3 seconds to 15+). And the strange part is
that it takes the same time whether I have data displayed or not in the the
pivot table.
You'll say 15seconds is not a big deal, but it's 15 seconds everytime I
remove and add again the dimension in the pivot table. and that can be
really annoying when you keep doing it (I could change my way of working and
have 3 or 4 excel workbooks opened, each one with the dimensions I need, but
that's not really suitable for me)

To my (little) knowledge, as2000 was loading the dimension in memory at the
startup of the service (that's what was limiting me with my big parent-child
dimensions) and that's what was making the quick response time.
With as2005 I'm happy with the increased capacity, but not with the way the
server work with the dimension members.

Is there a way for AS2005 to pre-load the dimemsion member in the server
memory? (especially now the memory management is way better and allows me to
use more than 2GB memory...)
Maybe there is other options like keeping the members in a cache once
they're loaded that would be good enough (apparently there is a re-query
everytime I switch the dimension on and off my pivot table)

I'm going to try and setup the IIS UI to query the cube and see if I have
better results in the mean time.

Thanks in advance for your response.

best regards,
Christophe



.

Relevant Pages

  • Re: Possible ROLAP Cube Design Scenario
    ... > we have very limited time window for cube processing. ... > I am just curious if we have a way to design acube with both MOLAP & ROLAP ... >> While 2M is a big dimension, I don't see why it isn't doable. ... but so long as you keep it at 2M with no member properties then ...
    (microsoft.public.sqlserver.olap)
  • Re: Slow member loading in excel 2003 pivot table
    ... I didn't try excel 2007 because I don't have it. ... I have a test cube, it loads member quickly... ... (that way I could still have the whole dimension available, ...
    (microsoft.public.sqlserver.olap)
  • Re: Slow member loading in excel 2003 pivot table
    ... The more I think about it, the more I fear the "almost instantaneous" display of this dimension member was just a dream:'( ... I'm gonna sound crazy, bu I need to find a way to create various sized dimension and see how excel react and display them, I want to range from 2K to 30K with a 2K step... ... I couldn't process the cube anymore or load 2 smaller cube with 500MB dimtree. ...
    (microsoft.public.sqlserver.olap)
  • Re: Slow member loading in excel 2003 pivot table
    ... I couldn't process the cube anymore or load 2 smaller cube with 500MB dimtree. ... with the cube browser in Visual Studio, the first load is long, but then the add/remove from the browser became instantaneous. ... and it is longer than a similar member data set (the only difference is this big parent child dimension which is smaller) ...
    (microsoft.public.sqlserver.olap)
  • Re: Why would NonEmptyCrossJoin fail on a virtual cube which joins
    ... I had previously assumed that when defining a virtual cube containing ... default member from all missing dimensions would be used to define the ... as the coordinate for that dimension when defining the virtual cube to ...
    (microsoft.public.sqlserver.olap)
Loading