BO Analysis for Excel ODBC connection

[patrickbachmann]

Hi folks,

I have created a bunch of reports in Excel using Business Objects Analysis connecting to HANA via ODBC.  Since then we have upgraded our hana db and our ODBC connection info has changed.  So I updated my ODBC connection to point to the new db name.  Now when I refresh any of my old reports in Excel it attempts to connect to the original ODBC connection.  This makes sense and I expected that to happen however  I can't seem to find where this setting is saved.  Does anybody know how I can change this in the workbook without having to recreate from scratch?  The odbc connection seems to be somehow embedded in the workbook but I can't find how to change it.

Thanks!

[david_stocker · Accepted solution]

Hi Patrick,

Your observation is correct. The "connection information" is embedded in the workbook. Analysis does not look for the ID of the Hana ODBC connection, but rather the url.

So if you create a workbook with a DS_1, which consists of an analysis view on instance a Hana server at mylongdomainname:30215 and later on I change instance name to 01, AO won't be able to refresh the connection as it is keyed on mylongdomainname:30215, not mylongdomainname:30115.

Unfortunately, this is not stored in a user accessible way, so your best bet if you have a large number of workbooks and/or data sources is maintaining the Hana server urls.

Regards,
David Stocker

[patrickbachmann · Accepted solution]

David, that's what I expected, thanks.

[patrickbachmann · Accepted solution]

Ok I finally found the components tab, thanks Henrique.  I tried right clicking on the worksheet in the components tab and choose USE DATASOURCE and it allowed me to pick a different datasource but it did not work. 

David, the interesting thing is I do not see the connection listed explicitly in this components tab like your example say; 

mylongdomainname:30115.

All I see is the name of my ODBC datasource that is an alias to that connection info.  So to me it makes sense if I leave my ODBC datasource exactly the same in excel but change it in ODBC admin to point to mylongdomainname:33015 for example that it should work but it does not.  I get error 'communication link failure 10709 connection failed'

[patrickbachmann · Accepted solution]

ie: just to clarify.  My excel report uses ODBC datasource named XYZ.  In the ODBC administration there is an entry for XYZ that points to a specific server and port.  That server and port have now changed so I update the info in the XYZ entry.  But somehow it doesn't care that I updated the server & port.  It's like the server & port is stored somewhere else inside the excel spreadsheet.  But I do not see server & port anywhere in the components tab.  I understand it could be hidden but I would expect it to store only the ODBC alias name XYZ and not the db & port.

[henrique_pinto · Accepted solution]

Sounds like an issue/problem in BO Analysis for MS Excel itself, not with HANA.

Maybe creating a message in that space would be more useful:

http://scn.sap.com/community/businessobjects-analysis-ms-office

[patrickbachmann · Accepted solution]

Ahhh, I did not even know there was a BO/Analysis space!  Thanks Henrique, I will do that.

[henrique_pinto · Accepted solution]

Sure, no problem.

[Former Member]

Hi Patrick,

Have you tried changing the data source name?

[patrickbachmann]

Michael, how are you getting to this setting, I can't seem to find it in excel 'analysis' tab.  Thanks

[henrique_pinto]

It's in the "Components" tab of the Design Panel.

It will show after you select your data source in the upper part.