Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OLAP .oqy Updates (2000)

    I've built a Pivot Table based on an OLAP cube built on the server by SQL Analysis Services. The Pivot Table has been tuned, tweaked, and formatted so that it looks great.

    Trouble is, the Cube on which the Pivot Table is based was just an experimental cube. Now I need to change the connection string for the data source so that the data is pulled from the production cube.

    How do I do that?

    I can see the connection string buried in the bits of the .xls file, but I haven't found any property, wizard, or menu option that will let me change it.

    Do I have to go into VBA to get access to the object model and update it that way? Or is there an easier way?

    Up to my hips in the wrong data...

    JMac

  2. #2
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OLAP .oqy Updates (2000)

    This works for me, but I am using Oracle. Select "Data, Data Range Properties", Uncheck "Save Password", save and close. Open the spreadsheet, refresh data. I am prompted for Username, password and server. In our case, we have 2 identical DB's as far as structure is concerned. One for production and one for development. As long as I change the server name I am OK.

    Reverse the above process to save the password in the spreadsheet for future use. "Data, Data Range Properties", Check "Save Password". In this case there is no need to save and reopen, since you do not care about the password which is cached for the session.

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OLAP .oqy Updates (2000)

    Nice try, but with the data coming from OLAP, those options aren't available.

  4. #4
    New Lounger
    Join Date
    Jul 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OLAP .oqy Updates (2000)

    Well, the answer seems to be VBA Code.

    Here's the short version:

    ActiveWorkbook.PivotCaches(1).Connection = strNewConnectionString

    Alternatively, you can do something very similar, but supply a connection string with some values missing (like server name or database name). When the spreadsheet is opened again, the "Multidimensional Connection" dialog is activated, and you can let it do all the work.

  5. #5
    Lounger
    Join Date
    Mar 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OLAP .oqy Updates (2000)

    Thanks for the suggestion.

Posting Permissions

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