OBIEE Not Able To Run Excel ODBC Based Analysis When The Excel Is Stored Over A Network

(Doc ID 2131766.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.9.5 and later
Information in this document applies to any platform.

Symptoms

On :  11.1.1.9.5 version, Analytics

OBIEE is not able to run excel based analysis when the excel is stored over a network.
The excel file is on the network (For Example: associated to a drive letter Y). The machine where the OBIEE is installed has the access to the drive and file. OBIEE administrator is able to open this file from the server box. If  the same file is used from the local machine there is no issue.
But when the excel file is on the network it give an ODBC error.
The same setup was working in 11.1.1.7.

ERROR
-----------------------
[nQSError: 43113] Message returned from
OBIS. [nQSError: 16001] ODBC error state: IM006 code: 0 message:
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed.
[nQSError: 16001] ODBC error state: S1009 code: -1023 message:
[Microsoft][ODBC Excel Driver]'(unknown)' is not a valid path.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Install OBIEE 11.1.1.9.5
2. Install Excel 2010 or 2013.
3. create a network drive and store an excel sheet in this drive
\\abc\TESTODBC2
y:\Book1.xlsx
3. Create a datasource called test1
ODBC > System DSN
Data Source Name : test1
Workbook: y:\Book1.xlsx
4. Open RPD in offline mode.
5. Add this datasource to the Physical layer of the RPD using Import Metadata option.
6. Right click on the test1 > y:\Book1.xlsx > Sheet1$ in the physical layer of the RPD and select "View Data"
Confirm the data can be seen
7. Upload the RPD to OBIEE server.
8. Run a report based off of this datasource.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms