MS Excel & PivotTable: location of MDX query execution (Server/Client)

Discussion in 'microsoft.public.sqlserver.olap' started by Vladimir Turenic, Oct 30, 2003.

  1. Hi guys!

    Please, does someone knows how I can tell to PivotTable in MS Excel where it
    should to execute an MDX query (on server or on the client)? Thanks a lot
    for help in advance!



    Vlado
     
    Vladimir Turenic, Oct 30, 2003
    #1
    1. Advertisements

  2. Vladimir Turenic

    Sanka Guest

    You can edit the Connection String of Excel Pivot table to
    include 'Execution Location=3;' property.

    The different values which you can specify for the
    Execution Location property is listed below.

    Value - Description
    0 - Default. For compatibility with earlier versions, this
    means the same as Value 1. The meaning of this default
    value is subject to change in future versions.

    1 - PivotTableĀ® Service selects the query execution
    location (client application or server) that will provide
    the best performance.

    2 - Queries are executed on the client application.

    3 - Queries are executed on the server. (Queries that
    contain session-scoped calculated members, user-defined
    sets, or user-defined functions are exceptions.)

    (Note: Inorder to change the Excel Pivot table connection
    string - Open the Excel Pivot Table - Save the file as XML
    Spreadsheet - Open the XML file in any text editor -
    Search for <Connection> tag and append "Execution
    Location" property to the conection string - Save the XML
    file - Open the XML file in Excel application and save the
    file back as Excel Workbook (.xls))

    HTH.
    Cheers,
    Sanka
     
    Sanka, Oct 30, 2003
    #2
    1. Advertisements

  3. Hi Cheers, thx for your quick response! It should by what I'm looking for.
    But I have to tell it could be a litle bit complicated procedure for our
    users. I have no idea how I'll learn it them. I'm afraid it won't be
    acceptable for them (and for me too). There's no another way how to do it
    via Excel GUI, or to do this change permanently for every next created XLS
    document with PT?

    Thx for any idea in advance!


    Vladimir Turenic


    You can edit the Connection String of Excel Pivot table to
    include 'Execution Location=3;' property.

    The different values which you can specify for the
    Execution Location property is listed below.

    Value - Description
    0 - Default. For compatibility with earlier versions, this
    means the same as Value 1. The meaning of this default
    value is subject to change in future versions.

    1 - PivotTableĀ® Service selects the query execution
    location (client application or server) that will provide
    the best performance.

    2 - Queries are executed on the client application.

    3 - Queries are executed on the server. (Queries that
    contain session-scoped calculated members, user-defined
    sets, or user-defined functions are exceptions.)

    (Note: Inorder to change the Excel Pivot table connection
    string - Open the Excel Pivot Table - Save the file as XML
    Spreadsheet - Open the XML file in any text editor -
    Search for <Connection> tag and append "Execution
    Location" property to the conection string - Save the XML
    file - Open the XML file in Excel application and save the
    file back as Excel Workbook (.xls))

    HTH.
    Cheers,
    Sanka
     
    Vladimir Turenic, Oct 30, 2003
    #3
  4. Vladimir Turenic

    Sanka Guest

    Hi Thomas,

    Although there is no default GUI option in Excel
    Application. I have a XLA provided by Thomas Greuel which
    helps in resetting the conection string parameters
    dynamically through a GUI.

    Let me know to which email ID should I sent the XLA file
    as Microsoft NewsGroups doesn't have a facility to attach
    any external files in the Newsgroup.

    Cheers,
    Sanka

    Thomas Greuel : Hope you don't mind if I share your XLA
    file.
     
    Sanka, Oct 30, 2003
    #4
  5. Vladimir Turenic

    lc Guest

    Let me know to which email ID should I sent the XLA file
    as Microsoft NewsGroups doesn't have a facility to attach
    any external files in the Newsgroup.

    I wouldn't mind having this xla either, please.
    Just out of curiosity, is it not possible to edit connection in .oqy file.
    Would that not be the same?

    Thanks.

    lc
     
    lc, Oct 30, 2003
    #5
  6. Vladimir Turenic

    lc Guest

    Well, nice try but I see no effect with Execution Location=3 (server). For
    whatever reason, SQL server CPU is down to 0% and, on client, Excel
    (2003/XP) is taking 100% for a really long period of time (over 2 hours).
    The same MDX (taken from log file), runs in MDX sample application in under
    2 minutes. Does anyone have **any** idea what's this all about?

    TIA

    lc
     
    lc, Oct 30, 2003
    #6
  7. Vladimir Turenic

    Martin Guest

    Hello

    Well according to Microsoft in a Webcast on:

    http://support.microsoft.com/default.aspx?scid=%
    2fservicedesks%2fwebcasts%2fwc120401%2fWCT120401.asp

    you also need to have "Default Isolation Mode=1" as a
    property in the connection string

    Quote:

    The next properties to use are the Execution Location=3
    and Default Isolation Mode=1. You will want to use these
    together to force the execution of calculated members and
    functions on the server side instead of the client. That's
    if your cube has many calculated members.

    End of quote

    Best regards

    Martin
     
    Martin, Oct 30, 2003
    #7
  8. Vladimir Turenic

    lc Guest

    Tried this but still no go. This option probably works only on AS (2000) and
    not on OLAP (7). I read the document and v7 isn't mentioned anywhere. Really
    too bad.

    Either way, thank a lot.

    lc
     
    lc, Oct 30, 2003
    #8
  9. Hello,

    You can send it to ! That's my business e-mail address. Thx!
    Certainly I'll try it!

    Cheers,
    Vladimir Turenic


    Hi Thomas,

    Although there is no default GUI option in Excel
    Application. I have a XLA provided by Thomas Greuel which
    helps in resetting the conection string parameters
    dynamically through a GUI.

    Let me know to which email ID should I sent the XLA file
    as Microsoft NewsGroups doesn't have a facility to attach
    any external files in the Newsgroup.

    Cheers,
    Sanka

    Thomas Greuel : Hope you don't mind if I share your XLA
    file.
     
    Vladimir Turenic, Oct 31, 2003
    #9
  10. Vladimir Turenic

    Wario78 Guest

    I have the same problem, using Excel 2K, AS2KSP3a and specifying
    'Execution Location=3;Default Isolation Mode=1' in my connection string.
    Using the exact same connection string in OWC2K is superfast.



    Gavin
     
    Wario78, Oct 31, 2003
    #10
  11. The difference between Execution Location and Default Isolation Mode is
    following:

    1. Execution Location determines where the AXES will be resolved
    2. Default Isolation Mode determines the Isolation Level for getting cells.
    When coupled with EL=3, it is effectively determines where the cells will be
    calculated.

    --
    ==================================================
    Mosha Pasumansky - http://www.mosha.com/msolap
    Development Lead in the Analysis Server team
    All you need is love (John Lennon)
    Disclaimer : This posting is provided "AS IS" with no warranties, and
    confers no rights.
    ==================================================
     
    Mosha Pasumansky [MS], Nov 2, 2003
    #11
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.