6 Replies Latest reply: Mar 17, 2014 3:54 PM by Ilyas Mohammed RSS

Performance Issues with Webi Reports

Kasturi Rangan
Currently Being Moderated

Hello,

 

Recently we upgraded from BOXI r 3.1 FP 1.3 to BOXI r3.1 SP4 and we ve been hit with major performance problems with respect to our webi reports. Reports which used to refresh within 10 minutes are now taking 40 minutes and eventually time out.We have maintained the same configuration parameters at the Universe level and CMS servers level. We have had the DBA review and perform sql tuning and indexing as well. But still the same. Also we had migrated around 200+ reports from the old environment to new environment. Could it be that there might be compatibility issues with objects and other custom variables created from one version to another version?

Any tips on how to improve performance would be very helpful.

 

Regards,

Kasturi rangan

  

  • Re: Performance Issues with Webi Reports
    Daya Jha
    Currently Being Moderated

    Hi Kasturi,

     

    Please provide the following details regarding your environment:

    • BOBJ Environment (Distributed or Not)
    • Web Application Details
    • Hardware setup is as per SAP recommended (Regarding number of users & reports)

     

     

    Thanks,

    Daya

    • Re: Performance Issues with Webi Reports
      Kasturi Rangan
      Currently Being Moderated

      Hello Daya,

       

      BOBJ env is not distributed

      Web Application used: Tomcat 5.5.20

      Not sure if hardware is SAP recommended since the setup was done by third party(BMC Software).

       

      Let me know in case you need further information. Thanks in advance

       

      Regards,

      Kasturi Rangan

      • Re: Performance Issues with Webi Reports
        Daya Jha
        Currently Being Moderated

        Hi Kasturi,

         

        Regarding Performance Tuning of Web Application(Tomcat Apache)  you can follow the below Steps for Other details refer JinChong Comments:

         

        Step I: Go to the following Path of Tomcat installed with BOE folder is:

         

        <BO install folder>\Tomcat55\conf

         

        Step II: Take a backup of server.xml file before doing any changes.

         

        Step III: Open the server.xml file for edit.

         

        Find the non-SSL HTTP/1.1 Connector and add following lines into it:

         

        compression="on"

        compressionMinSize="2048"

        nocompressionUserAgents="gozilla, traviata"

        compressableMimeType="text/html,text/xml,text/plain,text/css,

        text/javascript,text/json,

        application/x-javascript,

        application/javascript,application/json"

         

        Save the file and close it.

         

        Step IV: Restart Tomcat so the changes take effect.

         

        Hope this will help you as well. After Completion of Activity please close the thread

         

         

        Thanks,

        Daya

      • Re: Performance Issues with Webi Reports
        Simone Caneparo
        Currently Being Moderated

        Only trace log analysis can determine where time is spent, you should ask who provided the installation to do the analysis.

         

        thanks

         

        Simone

  • Re: Performance Issues with Webi Reports
    JinChong Tsai
    Currently Being Moderated

    Hi, Kasturi,

     

    It'd be better to understand where the WebI performance bottleneck is located if you can follow Daya's comment to provide the detail on your BOXI3.1 SP4 environment.  There could be various reasons that your webi report turnaround time is slower than before the upgrade.  For example, it could be the CMS CPU usage is spiking; the network bandwidth is busy; the backend database is slow to execute the WebI query; the number of the WIreportServers is undersized; etc.

     

    With this said, several things you can do to improve the WebI performance in general.

    - To share a common cache pool between separate physical server boxes in a cluster for the WIReportServers:

    -storageRoot <CommonDirectory>

     

    Where Output Cache Directory is a UNC formatted (Do not use drive letters) share or mount location.

     

    If pre-caching is enabled, to utilize this cache amongst different users the advanced right of "Merge dimensions for synchronization" must be applied to the folder or the report itself for a given user or group.

     

    To enable pre-cache sharing from within the CMC > Home > BusinessObjects Enterprise Applications > Web Intelligence > Advanced Rights : "Merge dimensions for synchronization". Grant the users or groups so they can use the pre-cached files.

     

    - SAP BO KBase Article ID:6853398

    EXECUTE IMMEDIATE 'alter session set optimizer_index_caching=0';  (100)

    EXECUTE IMMEDIATE 'alter session set optimizer_index_cost_adj=100';  (100)

    EXECUTE IMMEDIATE 'alter session set query_rewrite_enabled=TRUE'; TRUE

    EXECUTE IMMEDIATE 'alter session set "_always_semi_join"=CHOOSE'; CHOOSE

    EXECUTE IMMEDIATE 'alter session set "_b_tree_bitmap_plans"=TRUE'; TRUE

    EXECUTE IMMEDIATE 'alter session set "_hash_join_enabled"=TRUE'; TRUE

     

    - Aggregate awareness is the ability of a universe to make use of aggregate tables in a database. These are tables that contain pre-calculated data. You can use the @Aggregate_Aware function in the Select statement for an object that directs a query to be run against aggregate tables rather than a table containing non aggregated data.

    Using aggregate tables speeds up the execution of queries, improving the performance of SQL transactions.

     

    You set up aggregate awareness as follows:

    Define the Select statement for an object using the @AggregateAware function.

    Specify which objects are incompatible with the aggregate table and cannot be used in a query that is run against that table.

     

    - many-to-many (N,N)

    For each one or multiple rows in table 1, expect one or multiple rows in table 2.

     

    Many-to-many cardinalities are rare in relational databases and will return duplicate rows, causing slower performance and potentially inaccurate results. If you have (N,N) cardinalities, you should re-check the concerned joins, and ensure that you understand the relationship between the tables.

     

    - Using Hints (Oracle only)

    A Hint is commented information that is embedded in a query, and used by the Oracle optimizer to choose an execution plan. You should consult your Oracle documentation for full information on the Hints that can be used, and how they can be used to optimize queries.

     

    You enter the value for a Hint as a comment. A plus sign is used at the beginning, immediately after the comment delimiter, with no space between the comment delimiter and the plus sign:

     

    /*+ <hint> */

     

    Some useful Hints that you can use are as follows. Consult your Oracle documentation for a full list:

     

    FIRST_ROWS: Optimizes response time.

     

    RULE: Use rule-based optimization and not cost.

     

    FULL: Does a full table scan on the table.

     

    ROWID: Scans table by rowid.

     

    INDEX_FFS Perform a fast full scan on the index rather than on the table.

     

    FIRST_ROWS

     

    On the Custom page, click Hints and type /* FIRST_ROWS */ in the values box under the parameter list, and click on Set. The SQL is:

     

    SELECT /* FIRST_ROWS */

     

    1. RESERVATIONS.CUST_ID

     

    FROM RESERVATIONS

     

    - Shortcut Joins

    A shortcut join is a join that provides an alternative path between two tables. Shortcut joins improve the performance of a query by not taking into account intermediate tables, and so shortening a normally longer join path.

     

    What do you want to do?

     

    Read about how shortcut joins are used in Designer

     

    Create a shortcut join

     

    Shortcut joins in Designer

    A common use of shortcut joins is to link a shared lookup table to another table further along a join path. The join path comprises several different tables in the same context.

     

    In such a case, the shortcut join is only effective when the value being looked up has been denormalized to lower levels in a hierarchy of tables, so the same value exists at all the levels being joined.

     

    Designer does not consider shortcut joins during automatic loop and context detection. However, if you set the cardinality for a shortcut join you avoid receiving the message 'Not all cardinalities are set' when detecting contexts.

     

    To create a shortcut join

     

    Identify the two tables in a join path that can be linked directly.

     

    Create a join between the two tables.

     

    Double click the new join.

    The Edit Join dialog box appears.

     

    Select the Shortcut join check box.

     

    Select or type other join properties as required.

     

    Click OK.

    The shortcut join appears joining the two tables. A shortcut join is shown as dotted line in the Structure pane.

     

    Note

    You should set the cardinality of a shortcut join to the same cardinality as the join path it replaces.

     

    - WebIEventMaxTimeinSeconds

    2009/03/10 18:34:01.748|>>|E| | 5119|1128074160|

    |||||||||||||||**ERROR:CCDZMgr:Terminator: terminated by timerEvent thread

    locking > WebIEventMaxTimeinSeconds

     

    vi $BOBJEDIR/../setup/boconfig.cfg

    "WebIEventMaxTimeinSeconds"=ulong:57600

     

    - vi $BOBJEDIR/enterprise120/linux_x86/fonts/fontalias.xml or edit <install dir>:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\fonts\fontalias.xml

    1. Locate Tahoma
    2. Replace Tahoma.ttf with tahoma.ttf
    3. Replace Tahomabd.ttf with tahomabd.ttf

     

    - Increase the WRC java heap size.

    HKEY_LOCAL_MACHINE\SOFTWARE\Business Objects\Suite 12.0\default\WebIntelligence\RichClient\JVMOptions and change the value -Xmx512m to -Xmx848m (windows 2003) or -Xmx664m (Windows XP).

     

    - To improve the Webi save to PDF/Excel turnaround time

    1. vi $BOBJEDIR/../setup/boconfig.cfg (for UNIX) or regedit (for Windows)
    2. Add the MaxNodes to

    [Software\Business Objects\Suite 12.0\default\WebIntelligence\Calculator]

    "MaxNodes"=string:"124"

     

    Note that you can go above 124 but not advisible as WIRS will drastically increase in mem consumption.  0 disables the optimization and meaning that this will yield best PDF generation time at the expense of higher Webi Server memory usage.

     

    1. Restart WIReportServer(s).

     

    - SAP BO KB 1549622 - WIReportServer reports run out of memory WIS 30280 and WIS 30285

    1. edit <installdir>\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86\ncs.conf

     

    1. Locate instrument_level_threshold=10 and change it to 0

    instrument_level_threshold=0

     

    Note that the ncs.conf file controls the ncs.dll. The ncs.dll is used when Willy Introscope is used, almost exclusively with Solution Manager. This was added in SP3. If Solution Manager is not being used, making this configuration change will have no adverse affects.

     

    - The WIReportServer is using dynamic memory allocation and the hoard library is not able to recover/free the allocated memory space.

    1. Add -nocrheapalloc switch to the command line to the WIReportServer property in CMC
    2. Use default upper threshold to 1500 MB and max threshold to 1800 MB, and
    3. Restart the WIReportServer

     

    Note that the purpose of using -nocrheapalloc switch is to disables the hoard hook. i.e. The server won't replace new/delete/malloc/free with crheapalloc.dll and will use standard C/C++ library to allocate/free memory.

     

    - Webi LOV sorting

    1. Import the universe.
    2. Double click on the objects.
    3. Edit the object and apply sorting on the object.
    4. Click on Save and Exit.
    5. Click on the display button.
    6. Uncheck Export with universe.
    7. Click Ok and Save and export the universe.
    8. i. Import the universe again double click on the same object,Click on the display button and check the Export with universe option, Save and export the universe.

     

    At last, you can extract the Webi SQL query from one of your existing Webi report and run the SQL query directly in a database client from your BOXI3.1 SP4 server cmd or xterm and measure the turnaround time and compare that SQL query execution time to the Webi Report turnaround time in InfoView and WRC to get you an understanding how much difference is spent on the SQL query execution and report rendering.

     

    Hope this helps,

    Jin-Chong

Actions