Unable to Refresh Webi on (direct) Excel having 5000+ rows

[Former Member]

Hi

Please suggest on the following...

Issue:

Unable to Refresh Webi on (direct) Excel when excel no. of rows is more than 5000 rows of data. (upto 5000 rows works fine)

Error Message is: "Unable to create or update the Excel personal data provider: the range selection is invalid. (WIS 10885)"

Observations:

- The size of the excel file does not seem to matter as adding 1 row more will not increase excel size significantly.

- I was looking for "Max rows retrieved" in Webi Rich client User Guide but no such setting can be set when webi is on excel.

Thanks

Deep

[Former Member · Accepted solution]

Hai Deep,

This error is due to the cell range in excel file is invalid

To rectify this you can verify the excel file or BO administrator.

[Former Member · Accepted solution]

Thanks.

I find Excel -> Webi is not a scaleable solution.

I've now taken Excel -> Universe -> Webi route which works like a charm.

-Deep

[Former Member · Accepted solution]

Hello, Deep.

Could you explain how to set up Universe based on Excel file in more detail?

Thank you

[Former Member · Accepted solution]

Hi Arkadiy Terzi

1. Place excel sheet in a share location (assuming that this sheet as as our datasource and it can be updated by anyone having access to READ/Write on this sheet).

2. Using "ODBC Datasource Administrator" on your computer (and BO Server), create a System DSN using Excel drivers to pointing to the shared drive excel sheet.

3. Create a Universe Connection using the above system DSN.

4. "Browse" for tables in Universe. This will return all the columns of the excel. Simply drag the whole table as a Class in Universe and make appropriate changes in names, aggregation of measures etc

5. Done.

Thanks

[Former Member · Accepted solution]

Thank you, Deep.

We should use c:\windows\SysWOW64\odbcad32.exe command to add an ODBC Data Source on 64 bit Server OS.

[Former Member]

Hi,

As it is mentioned in the note 1356508 :

Ensure that each cell of the first row contains text as these are passed on to Web Intelligence as Column Names.

Regards,

Anil Kumar Sharma .P