Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2003
    Posts
    18

    Question Unanswered: Why it's so slow to display a cube in Excel

    When I display the cube with OWC ( Office Web Component ) in Visual Basic, it works fine. If i display the same cube in Excel with Pivot table, it became extremely slow, Can anybody help?

    Fishey

  2. #2
    Join Date
    Feb 2003
    Posts
    109

    uh what kindof cube

    are you talking about using olap/analysis services, or an offline cube?
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  3. #3
    Join Date
    Jan 2003
    Posts
    18

    Re: uh what kindof cube

    Both local cube (.cub) and analysis server cube.

    Got any idea?

  4. #4
    Join Date
    Feb 2003
    Posts
    109

    Difference in hardware i would guess.

    the OWC would be using resources on the Webserver, where Excel pivot table would be using resources on your desktop.

    Is your webserver faster CPU and more memory that your desktop?

    Does it have a faster network connection to the db server?

    i personally hate excel-- i think that it is a dog.

    i am an access guy, and i am currently writing an alternative to OCW-- what dont you like about OCW?
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  5. #5
    Join Date
    Jan 2003
    Posts
    18

    Re: Difference in hardware i would guess.

    Actually, OWC ( Office Web Component) does not only use on webserver, we can use it on local also. It's ActiveX controls.

    I didn't use it on web, just for local, in Visual Basic project.
    So i think it has nothing to do with webserver.

    I do like OWC, but our clients will use Excel to display cube. That's the reason.

    Any other ideas?

  6. #6
    Join Date
    Feb 2003
    Posts
    109

    i think that you need to turn off refresh on open

    in excel, if you select the pivot table, and then show the pivot table toolbar, and select table options, you can turn off 'refresh on open' and this should have a dramatic improvement in performance.

    is it slow on open, or is it slow everywhere?

    also, you can do things like process the cube more, etc....
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  7. #7
    Join Date
    Jan 2003
    Posts
    18

    Re: i think that you need to turn off refresh on open

    Actually, i didn't check 'refresh on open' in Excel.

    I have 5 dimensions in the cube and when i just put 2 of dimensions in Excel, the speed is OK, if i put a third one, the speed slowdown, i didn't get result for more than half an hour.

    How to "do things like process the cube more, etc", i didn't get it.

  8. #8
    Join Date
    Feb 2003
    Posts
    109

    ok.

    are you doing SQL Server Analysis Services or Offline Cubes?

    the last time i asked you, you said BOTH.

    i dont know if you can do this in Excel.

    if you are using SQL Server driven Analysis Servcies, then i can help you.

    also, are yuo using multiple ranges in your spreadsheet?

    is some in excel, and some in OLAP?
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  9. #9
    Join Date
    Jan 2003
    Posts
    18

    Re: ok.

    I created a cube with analysis manager or use DSO to create a cube in visual basic, this kind of cube is called server-side cube, it exist in analysis database, right?

    If using CREATE CUBE to create a cube, this kind of cube is called a local cube, it exsists in a .cub file, right?

    Then i can display both kinds of cube in Excel with Pivot table, right?

    But i got the speed is very slow if i put more than 3 dimension in the pivot table. Got any idea?

    "multiple ranges in your spreadsheet", i didn't get it. Could you explain it?

    Thank you so much.

  10. #10
    Join Date
    Feb 2003
    Posts
    109

    simplify your excel, shutoff drilldown?

    then id stop showing so much data in excel-- or turn off drilldown, or something.

    you either have too many rows or columns (which is causing a table scan on the server (the root of all performance problems))

    are you using ROLAP, HOLAP, MOLAP?

    when you calculate aggregations, have you tried calculating to a higher percent?

    have you done things like in Excel <TOOL>, <OPTION>, <CALCULATION>, Calculation = manual

    i dunno man. i wish i could help more. I HATE Excel more than anything (because there are WAY too many people that try to use it as a database, or for database reporting (and it cant compete with Access Reports for that)
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  11. #11
    Join Date
    Jan 2003
    Posts
    18

    Re: simplify your excel, shutoff drilldown?

    Actually i didn't use drilldown at all.

    THERE ARE thousands of rows in the cube if i put more than 3 dimensions in Excel.

    I use MOLAP.

    for performance, it's 80%

    <TOOL>, <OPTION>, <CALCULATION>, Calculation = automatically

    One question: if i put one dimesion in Excel, it will expand all data at the same time, is there a way to prevent it from doing that?

    Thank you very much.

  12. #12
    Join Date
    Feb 2003
    Posts
    109

    just for testing

    can you turn off totals on columns or totals on rows?

    (under table options in the pivot table)
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  13. #13
    Join Date
    Jan 2003
    Posts
    18

    Re: just for testing

    I turn off totals on columns and totals on rows, but nothing changed.

    I wonder the problem is: When put one dimesion in Excel, it will expand all data at the same time.

  14. #14
    Join Date
    Feb 2003
    Posts
    109

    how fast is the client machine?

    when you say it takes a half hour.. is that on a 233 or on a 1gig?
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

  15. #15
    Join Date
    Feb 2003
    Posts
    109

    sorry this is so long.. its from google groups

    From: Fishey (fish_funny@hotmail.com)
    Subject: Display cube in Excel extremely slowly


    View this article only
    Newsgroups: microsoft.public.sqlserver.olap
    Date: 2003-02-24 15:04:50 PST


    I created a cube with 5 dimensions. When i just display 2
    dimensions in Excel, the speed is ok, if i put one more
    dimension, it slow down extremely and got a message said:
    Low Virtual Memory. If i display the cube in Visual Basic
    with OWC, it works fine. Does anybody have idea about this?
    Post a follow-up to this message

    Message 2 in thread
    From: Andrea Worley (k_rage@hotmail.com)
    Subject: Display cube in Excel extremely slowly


    View this article only
    Newsgroups: microsoft.public.sqlserver.olap
    Date: 2003-02-25 06:16:05 PST


    What Excel are you using? Excel 2002 is much, much faster
    than 2000. It also has more features. If you're using
    Excel as a client, it's worth the upgrade to Office XP.
    Also, does the slowdown happen with any third dimension or
    are you always trying the same one? For example, I have
    two dimensions, CustomerName and CustomerNo. When I use
    them both, it is slow because there's actually a 1:1
    ratio. But any other dimensions are quick.


    >-----Original Message-----
    >I created a cube with 5 dimensions. When i just display 2
    >dimensions in Excel, the speed is ok, if i put one more
    >dimension, it slow down extremely and got a message said:
    >Low Virtual Memory. If i display the cube in Visual Basic
    >with OWC, it works fine. Does anybody have idea about this?
    >
    >
    >.
    >
    Post a follow-up to this message

    Message 3 in thread
    From: Fishey (fish_funny@hotmail.com)
    Subject: Display cube in Excel extremely slowly


    View this article only
    Newsgroups: microsoft.public.sqlserver.olap
    Date: 2003-02-25 07:13:53 PST


    Thank you very much, Andrea.

    I used Excel 2000. Because i created cubes for our
    clients, so i can't ask them to upgrade to Excel 2002 or
    Office XP.
    Yes, it slowdown when i add any third dimension. in the
    cube, actually, dimension3 and dimension2 have 1:n ratio.
    But if i display the cube in Visual Basic with OWC, it's
    very fast.

    If i use excel 2000 as client, is there any other solution
    for this?
    Post a follow-up to this message

    Message 4 in thread
    From: Andrea Worley (k_rage@hotmail.com)
    Subject: Display cube in Excel extremely slowly


    View this article only
    Newsgroups: microsoft.public.sqlserver.olap
    Date: 2003-02-25 08:54:08 PST


    Not that I know of. I have some users who use Excel 2000
    and it runs very slow.

    Some other reasons for Excel 2002:
    Excel 2002 allows you to select multiple items on the page
    filters. It also allows you to check and uncheck all
    items at once, making it easier to choose a few fields out
    of hundreds to filter on. And the grand totals actually
    change when you filter, as opposed to some of the
    filtering in Excel 2000. You can also add code so that
    the user can drillthrough to the detail records.

    But, if you can't switch, you can't switch. Sorry, I
    can't help more.


    >-----Original Message-----
    >Thank you very much, Andrea.
    >
    >I used Excel 2000. Because i created cubes for our
    >clients, so i can't ask them to upgrade to Excel 2002 or
    >Office XP.
    >Yes, it slowdown when i add any third dimension. in the
    >cube, actually, dimension3 and dimension2 have 1:n ratio.
    >But if i display the cube in Visual Basic with OWC, it's
    >very fast.
    >
    >If i use excel 2000 as client, is there any other solution
    >for this?
    >
    >.
    >
    Post a follow-up to this message

    Message 5 in thread
    From: Fishey (fish_funny@hotmail.com)
    Subject: Display cube in Excel extremely slowly


    View this article only
    Newsgroups: microsoft.public.sqlserver.olap
    Date: 2003-02-25 11:24:10 PST


    Andrea, Thank you very much.
    I appreciated your suggestion, i'll try to tell this to
    our clients.
    Post a follow-up to this message

    Message 6 in thread
    From: Bluetooth (szymon_dot_slupik@cdn.com.pl)
    Subject: Re: Display cube in Excel extremely slowly


    View this article only
    Newsgroups: microsoft.public.sqlserver.olap
    Date: 2003-02-25 11:20:02 PST


    > But if i display the cube in Visual Basic with OWC, it's
    > very fast.
    >
    [SS] It seems to be simple to explain. When you nest >1 dimension on an axis
    in Excel, it expands all the rows. Doing the same in OWC the rows are
    collapsed, just a "+" sign appears to notify an expansion option. This way
    the MDX generated by both tools is different. OWC is in many aspects
    superior to the big Escel brother.

    HTH
    Szymon Slupik, CDN S.A.
    Krakow, Poland
    Post a follow-up to this message

    Message 7 in thread
    From: Fishey (fish_funny@hotmail.com)
    Subject: Re: Display cube in Excel extremely slowly


    View this article only
    Newsgroups: microsoft.public.sqlserver.olap
    Date: 2003-02-26 08:40:23 PST


    Yes, i also think that's the reason.
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •