IBM Cognos for Office - Issue with publishing a 22MB excel spreadsheet

[Romeo]

Hi everyone,

I have a bit of a head ache with Cognos for Office and a cognos 10.1.1 FP4 Cognos environment.
I am trying to publish an Excel Document (of 22 MB in size) into the Cognos portal with the Cognos for Office tool.
After authenticating myself into the cognos environment from the Cognos for Office tool I click the publish button and select the Excel spreadsheet that I want to upload. After a few moments I get the following error:

The remote server returned an error: (404) Not Found.

System.Net.WebException: The remote server returned an error: (404) Not Found.
   at Cognos.Office.Framework.DataSource.Cognos8.HttpConnection.Send(String sSOAPRequestBody, String sAction, Boolean cacheResult)
   at Cognos.Office.Framework.DataSource.Cognos8.HttpConnection.Send(ISOAPRequest request)
   at Cognos.Office.Framework.DataSource.Cognos8.C8ContentStore.Publish_DoWork(String SearchPath, String Name, String Description, String ScreenTip, eDocumentClass type, String Data)
   at Cognos.Office.Framework.DataSource.Cognos8.C8ContentStore.Publish(String SearchPath, String Name, String Description, String ScreenTip, eDocumentClass type, Byte[] data)
   at Cognos.Office.Framework.Forms.Publish.PublishFactory.PublishDocument_DoWork(PublishDocumentArgs args)
   at Cognos.Office.Framework.Threading.AsyncCall.AsyncItem`1.Run()
   at Cognos.Office.Framework.Threading.AsyncCall.CallMethod()

Environment description:

Distribuited Cognos 10.1.1 FP4  environment with two BI dispatchers and one gateway. Also there are separate servers for Framework Manager and Transformer tools.

Where I am testing right now is DEV environment. (In prod we have the same behaviour)
This is using:
- Intel Xeon E5620 @ 2.40Ghz CPU.
- 98 GB of RAM
- Windows 2008 R2 Enterprise with SP1

What have I tried:

- Increasing the Governor limit from the default 10 MB to 30MB and restarting the cognos services. (the erros still appears)
- In IIS I changed the connection time-out from 120 seconds to 300 seconds. (the error still appears)

I have Admin rights so permissions should not be a problem.

Related IBM Technotes:

- http://www-01.ibm.com/support/docview.wss?uid=swg21574005

Unfortunatelly, Cognos 10.1.1 is out of support since April 2016. (Migrating to 10.2.2 is in plannig phase :))
I would be very gratefull if someone cold give me a hend with this.

Modifying the post to add the follwoing information:
The Excel Spreadsheet has several tabs and one of them has aproximately 130000 rows. It seems that in the past the end-users were able to upload a previous version of this document. This version had 110000 rows aproximately.
Could there be some row number limit rather than size in MB limit that needs to be changed?
If so, where could I modify this parameter?

Thank you very much!
Kind regards,
Romeo.

[Romeo]

I tried the following:

1. Go to Launch > IBM Cognos Administration
2. Go to System
3. Click on the arrow down next to System in the central panel > Set properties
4. click on the Settings tab
5. Advenced settings > Edit Advanced settings
6. Add the following entries:

   RSVP.EXCEL.EXCEL_2007_LARGE_WORKSHEET -> True
   RSVP.EXCEL.EXCEL_2007_WORKSHEET_MAXIMUM_ROWS -> 1048576

7. Restarted the Report Service and ReportDataService services.

Result: Still seeing the error. :( :( :(
I will keep investigating...

IBM TechNote:
http://www.ibm.com/support/knowledgecenter/SSEP7J_10.2.1/com.ibm.swg.ba.cognos.ug_cra.10.2.1.doc/t_config_lgwrkst_ex.html

[bdbits]

Repeat after me: Cognos is not an ETL tool for generating Excel databases.

I am pretty certain you have users that want you to dump data to a spreadsheet so they can analyze it with Excel. Which is a waste of Cognos. If they want to do that, just write some SQL queries to generate a CSV, skipping Cognos altogether. Yes, I am serious.

With that said, another thing you can check is whether you are running out of temp space on the Cognos server.

[Romeo]

Hi,

"Repeat after me: Cognos is not an ETL tool for generating Excel databases."

Yes, I know it's a waste of Cognos to use it this way. Unfortunately I am not the one taking the decisions.. :-\ :-\ :-\
Running out of temp space would have been an easy fix, but that is not the case.
I was thinking that being that it's Cognos 10.1.1 maybe there might be a bug that does not allow more than 110000 rows aproximately.
Since last april, Cognos 10.1.1 has ran out of support.
I guess that we'll have to wait untill the migration to Cognos 10.2.2 is done.

Thank you very much for the answer bdbits.
Cheers!

[bdbits]

Yes, it could be something about the memory required for the number of rows is exceeding some limit. Alas, support might have been the only ones who could find out.

Sorry I could not be of more help.

[Romeo]

No problem  :)
Thank you for taking the time to help me.
In the end I offered the user a workaround. Since he was using that Excel Spreadsheet page as historical data and he was not using it to for "real time" calculations, I told him to create another Excel Spreadsheet or a new page in the same Exce Spreadsheet.  ;D

Thank you again!