wheres the data source? (newbie question)

Question

Post reply

wheres the data source? (newbie question)

carolyn v mckechnie

SSC Veteran

Points: 286
More actions
September 11, 2004 at 1:50 pm

#163197

I have 4 pivot table spreadsheets that were written by someone else who is no longer with the company. The pivot tables go against OLAP cubes for the data source. I am stumped on where to find the explicit reference to the OLAP data source in the Excel spreadsheet. I have loaded additional data into some cubes and I want to refresh the data in the pivot tables. But I first want to confirm the OLAP cube, Login ID, and Server used by each pivot table. How can I do this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

The datasource string is kept in an *.oqy file. They're usually found in your Application Data directory under your documents and settings e.g. C:\Documents and Settings\steven\Application Data\Microsoft\Queries

You should be able to use notepad to edit these as/when required.

Just out of interest, there is an office acellerator solution that is a new OLAP Add-In for Excel. Depending on your version of Excel, you may find this 1. more functional and 2. the data source management is far easier.

Cheers,

Steve.

carolyn v mckechnie SSC Veteran Points: 286 More actions · Answer 2

Steve, thanks so much for answering my question. Unfortunately I have multiple .oqy files, multiple spreadsheets and multiple cubes. How can I determine which .oqy file goes with a given excel pivot table. That's my quandry. There must be some code embedded in the spreadsheet for the pivot table that says use this .oqy file at this path, correct?

I will look into the OLAP Add-In - thanks for that tip.

Cheers,

Carolyn

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

You would think so, the difficulty (as always) will be extracting it. The closest I can get is the following script which will let you change the server that each query is pointing to. You could modify this to just display the connection string, and then from these conxn string details deduce which oqy is in use. Alternatviely you could take some time and go through the properties of the PivotCache object and try to find a prop that gives the oqy that the pivottable is using. (BTW, the obvious one would be SourceConnectionFile but it seems to always be an emoty string for me)

Here's the link for the script -> http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D327572 if the link doesn't work, try searching msdn for KB ID 327572

HTH,

Steve.

carolyn v mckechnie SSC Veteran Points: 286 More actions · Answer 4

Thanks - I'll try editing that to display the connection string. I also found under Tools->Macro->Miscrosoft Script Editor, searching on the provider name "MSOLAP" brings up the connection string in the <x:connection> tag which is inside the <xTSource> tag. It still doesn't name the .oqy file, but I can use this.

Thanks again for your help!

Carolyn