Inserting multiple queries in a workbook sometimes make the size of the workbook very huge, especially when the inserted query has more records.
Now, say we have a workbook with a query inserted in 20 sheets and twice in a sheet, which makes 40 queries in total and the size of the workbook is coming to 15MB.
Your client asks you to reduce the size of the workbook or this could be the case if you are working in formula mode and want to view the data in your result set without having to connect to the server (Provide Results Offline). To do this, you have to save the result set in the workbook first. If you do not do this, no data is displayed in the cells next time you open the workbook. Only the formulas will be displayed.
How to reduce the size of the workbook???
Zipping the file? Well, but it is not using BEx properties.
These users can do themselves without asking us, right?
Do we have an option to reduce the size of the workbook with BEx properties?
Yes, we do have an option with workbook settings!!
Open a workbook and click on Workbook settings icon, which will pop up a window.
Now you can see an option for compressing a Workbook, Select the option ” Use Compression When Saving Workbook” and save the workbook to see the difference. The setting made my workbook to reduce the size to 4MB from 12MB. Isn’t it good enough??
The workbook compression reduces the bytes transferred for the workbook and reduces the overall processing time, which is nothing but boosting the performance.
We do have another option with SP 800 for BI 7.0 and Excel 2007 for performance boost up.
Use optimized storage – With this setting checked bytes transferred for the workbook may increase slightly but the overall workbook processing reduces.
These settings reduce the processing time and make the workbook available immediately on refresh.
As the changes in settings are made in a particular workbook, settings affect only that workbook. To make them Global use TCode RS_FRONTEND_INIT and make ANA_USE_OPTIMIZE_STG = X alternatively you can modify the entries of table RSFRONTENDINIT.
Following note give all the parameter details of transaction RS_FORNTEND_INIT, Note 1510916 – Collective Note: RS_FRONTEND_INIT parameters
Tip: Best performance and no errors when the workbook is saved with “Excel 97-2003 Workbook” format (.xls).