Export to excel format doesn't work for large summary table with 50 K rows in TSPS

Version 1
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    TrueSight Capacity Optimization


    COMPONENT:

    Capacity Optimization


    APPLIES TO:

    TSCO 11.5, 11.3.01, 11.0, 10.7.01



    PROBLEM:

    When exporting a large summary table in TSPS to Excel format, there are several performance issues and doesn't export it.
    Summary table which has server list and rows more than 50 K.


    1    


    2


    CAUSE:

    Improvement-DRCOZ-21195-Export to Excel - Scalability Improvement


    SOLUTION:

     

    TSCO 11.5.00 and earlier

    Improvement-DRCOZ-21195-Export to Excel - Scalability Improvement 

    This improvement will be addresses in 11.5.01 and won't be back ported to earlier versions.  

    TSCO 11.5.01

    One thing you can try is to change the 'chunk size' that the Capacity Views use when extracting the data from the Datamart during the export.  By default the chunk size of 1000 rows of data (so for a table with 400K rows 400 separate requests will be made to the TSCO Datahub to get the full dataset). 

    The primary concern with increasing the 'chunk size' too much is that if the underlying Datamart SQL performance is poor it could results in a timeout that you wouldn't see when the SQL export is chunked. 

    But, it would be a reasonable to test to increase the export chunk size (via the 'excel.export.pagination.size' property) and see if it helps with the export time of this large Capacity View. 

    You could do that via the TSCO UI: 

      (1) Under Administration -> System -> Global configuration -> Edit -> Click the 'this page' link in the "You can manually edit system properties from this page" text at the bottom of the page 

      (2) Search the page for a property named "excel.export.pagination.size".  You can do a 'CTRL-F' Search via the browser.  If you find the property skip to step #4. 

      (3) If you don't find a property named excel.export.pagination.size, entry "excel.export.pagination.size" in the "Add new property" edit box at the bottom of the page and click the 'Add' button.  That will add the property. 

      (4) Update the property named "excel.export.pagination.size" with a value of, say, '5000' 

      (5) Click the 'Save' button to save your changes. 

    Alternately, you could make the same update via SQL. 

      (1) Check to see if the property is defined: 

    SELECT * FROM CONF_PROPS WHERE NAME = 'excel.export.pagination.size';

      (2a) If the property does exist, update it with the new chunk size value: 

    UPDATE CONF_PROPS SET VALUE = '5000' WHERE NAME = 'excel.export.pagination.size';

    COMMIT;


      - or - 

      (2b) If the property doesn't exist, set it with the desired chunk size: 

    INSERT INTO CONF_PROPS (NAME,VALUE) VALUES('excel.export.pagination.size', '5000');

    COMMIT;


      (3) Restart the TSCO Datahub 

      ./cpit restart datahub

      

     


    Article Number:

    000169264


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles