OBIEE11g : Export to Excel issue

Hi All,

We have gone through a strange issue in the recent project with an export option. As we have huge amount of data on the reports which are retuning about more than 1,00,000 records, OBIEE is creating the excel files which has a size of 200 MB to 2GB. But the clients are more interested to do their analysis in excel only. As a work around we suggested to use export to CSV. This is working fine but catch up with another issue with descriptive data which has commas (,) in it. If we have any , in the column data the CSV is considering it as new column hence we are getting difference in row count and the formatting also. Again there is a workaround for this CSV issue which is nothing but replacing (,) with any other special character which is not serving the purpose. So we did not have any other option other than making the Export to Excel works as per the expectations. Our expectations are as follows...

1) Export to Excel should create the small files which ranges to KB to MB.
2) We should not miss any data or columns on exporting ( This also one known bug in OBIEE 11g all the versions)
3) The formatting should be proper which matches with the report.


Solution:

To solve this issue we have taken the help from Oracle support by creating an P1 Service Request. Even though its not a permanent solution its serving the requirements for now. Oracle said they are going to provide the permanent solution in the future version which is 11.1.1.7.0 ( Its going to be a bundle patch mostly). The solution is as follows...

1)      Go to the following path to edit controlmessages.xml file 

/<OBIEE_HOME>/Oracle_BI1/bifoundation/web/msgdb/messages

           Fix: do the following changes
          Change export "format=mht" to "format=excel"
Note: Replace the text  “mht” to “excel” . Don’t forget to take the back up for the controlmessages.xml file before editing.
Don’t place the back up in the same folder, save the back up in a different folder.

2)      Go to the following path to edit config.xml file 

<OBIEE_HOME>/instances/instance1/config/OracleBIJavaHostComponent/coreapplication_obijh1

Fix: do the following changes

Before:

<XMLP>

      <InputStreamLimitInKB>8192</InputStreamLimitInKB>

      <ReadRequestBeforeProcessing>true</ReadRequestBeforeProcessing>

   </XMLP>

<DVT>

      <InputStreamLimitInKB>8192</InputStreamLimitInKB>

</DVT>

After:

<XMLP>

      <InputStreamLimitInKB>0</InputStreamLimitInKB>

      <ReadRequestBeforeProcessing>true</ReadRequestBeforeProcessing>

</XMLP>

<DVT>

      <InputStreamLimitInKB>0</InputStreamLimitInKB>

</DVT>

Note: 0 represents unlimited. No Need to restart the services.

Result: Exporting to excel in OBIEE report is creating huge file size. Before fix for 5000 records the file size was 1.6 MB and after fix the file size was 800KB

 Hope this helps you......  :)