Improving Actuate Reporting Performance in Clarity

Reporting Performance issue is very common and an challenge to address it.I faced that and managed to overcome with the help of experts advice and some ground work.i worked and tested in throwly in Test server and then implemented in Production.This is specific to Actuate Reporting.

1. Tuning the Sql
                In Most situations the poor performance of Reports is due to inefficient Sql.Pls extract the SQL queries to run them directly against the database to measure the Performance.Tuning your SQL is often the best way to increase overall Reporting performance.
    
2. Minimise the data
              Minimise the data in your report at every opportunity. Only bring back records that are required.That will improve the reporting performance significantly.Less Data More speed.Optimize the Tables & Indexes.

3. Run acverify utility Monthly.
                    The acverify command-line utility validates an Encyclopedia volume and optionally attempts to repair problems found in the volume. Use acverify with an offline volume.It cannot be used with an online volume.
Command:-  Run acverify check all repair

4. Archiving
         Clear the Old Archival Files which will Free enough space and will boost the Reporting server performance.

A) Setting archive folder
       Create a new environment variables in the actuate server called AC_ROOT_FOLDER and set the archive folder.
 
B) Setting the archive library
       Login to Actuate Management Console System Administration, click at System Volumes -> General. Enter the archive library path into the archive library name. The archive.dll usually located in the Actuate9\iServer\Examples\Archive folder.You need to restart the server for this field to take effect.

C) Setting archive rules
    Login to Actuate Management Console System Administration then go to the Files and Folder(where the roi files resides,usually  /cus/) and right click properties and then select the file type to archive (Actuate Report Document - roi) and choose the Archive files before deletion Option.
                    This option will archive the files to the archive folder before the file is deleted. This is useful to recover the report at a later stage.However, this will add on additional disk space in the server as it will be keep as an archive file.So probably after some time you can delete the Files.
 
D)Setting archive schedule
 Login to Actuate Management Console System Administration thenClick at Volume -> Archive Schedule then Select the start date and time to start the schedule. Advise to start the archive schedule during non peak hours and the time doesn’t clash with the backup schedule.

5. Increase the Buffer pool size
                                In Actuate Management Console System increase the Buffer pool size where the default is 10mb.Actuate iServer manages this information using a page buffer pool.Actuate recommends setting the buffer pool as large as possible to keep the buffer pool data in the Actuate iServer machine’s memory and not in the machine’s disk swap space.

6. Adding Factory processes (Important)
                           In both stand-alone and cluster configurations, the optimum number of Factory processes for Actuate iServer depends on the speed of the CPU that runs the Actuate iServer workload and the mix of reports in the workload.To begin, configure two Factory processes on a processor. Adding Factory
processes can enhance performance if the database that Actuate iServer uses to create reports is a performance-limiting resource.
                           Actuate iServer can use the multiple Factory processes to run multiple, simultaneous reports that use the database. When Actuate iServer runs multiple, simultaneous reports, some of the Factory processes access the database while other Factory processes perform report generation tasks that do not access the database.
 
7. Increase the View Server Performance.     
                      The default value is 50, which means 50MB.You can increase the file size accordingly.The View process maintains caches for reports and report executable files. You can set Actuate iServer parameters to configure cache sizes, time-out values, and other View process parameters. In most cases, you do not have to change the values for these parameters from the default values.
                The View process stores session information in a cache. Increasing cache size can improve View process performance when cache size is a limiting resource. The View process uses cache to store ROX files and view session information.
        
8.  Improve report generation for long running reports
                            Improve report generation for long running reports by increasing the value of the environment variable.The default is 15 seconds, you can  increase to 30 seconds.
     AC_FACTORY_PAGE_COUNT_UPDATE_INTERVAL -- default is 15 seconds.
      
9.  De fragmenting the Report Encyclopedia
                                    To improve Report Encyclopedia performance, run the ACDEFRAG utility on a regular basis. Alternatively, back up and restore the Report Encyclopedia using the ACEXPORT AND ACIMPORT utilities on a regular basis.
        
10.  Optimizing Memory
                    Actuate recommends a minimum of 1GB of RAM per CPU for Large Workloads.It is important not to use up all available memory.When CPU Requires more memory than available.There must be standards for each server like WebbLogic,Webshere and Tomcat,stick on that.

11.Run the Remove Job Logs and Report Library Entries
         This job removes job log and Report Library entries from databases that are
older than a specified number of days.Schedule it weekly.

12.Schedule the Reports
      During High Traffic Times Run large reports by scheduling  rather running immediately and also if possible
    schedule it on Non Peak Hours. 
 
13. Seperate Server for Reporting
                                    To provide for sufficient resources for both Application and Database servers, place the report server on a different CPU from the database or application server . Both report generation on the report server and database or application server tasks are CPU-intensive. If you cannot place the report server and database server on separate CPUs, plan for additional CPU, memory, and disk resources to support the report server.

14. Check All Resource Utilizing
               Check the Operating System resources including I/O ,Network ,CPU and Memory.
 
15. Monitor the Reporting Performance
              Monitor the Report performance via OS Utilities at regular Intervals which will help you to analyze Performance.

    *  For windows -- Windows Task Manager,Windows Performance Monitor.
    *  For Unix -- Top,Vmstat,iostat,netstat,sar  

Understanding WIP

WIP stands for Work In Progress, it is a table that holds the financial transactions before they go through to Billing and then onto Invoicing.
The three main tables that hold the data are  PPA_WIP,PPA_WIP_VALUES and PPA_WIPAPINFO .
PPA_WIP -->This table holds the header information for the WIP transaction, it doesn’t contain any money values, but it does contain the transaction type.
Some Important Columns are Highlighted below.

Transaction Type

    *  L. This is for Labour transactions.
    *  M. This is for Material transactions.
    *  Q. This is for Equipment transaction.
    *  X. This is for Expense transaction.

PROJECTTYPE --> This column indicates the project type.Transactions are not considered for billing for the project created of type INTERNAL.  

    *   Internal
    *   Standard
    *   Retainer
    *   Contract

SOURCEMODULE -->
 This column refers to the source module where the transaction originated from. Values are:

    * 50 = Transaction originated from CA Clarity XOG (unless otherwise specified)
    * 51 = Transaction originated from a timesheet in CA Clarity PPM.
    * 52 = Transaction originated from voucher entry of type VOUCHER_EXPENSE
    * 53 = Transaction originated from voucher entry of type (VOUCHER_OTHER)

 STATUS -->
  Indicates the current status of this transaction. Values are:

    *  0 = No adjustment, no pending approval for billing
    *  1 = Adjusted
    *  2 = Reversed
    *  4 = Updated
    *  8 = Processed by billing, approval pending 

 PPA_WIP_VALUES -->This table holds 5 records for each WIP transaction, each with a different currency type, these are

o HOME. This is the Accounts system setting for holding transactions, so if the system was in the USA, the HOME currency would be US$
o EURO. This holds the euro value for the transaction.
o BILLING. This is the currency in which billing is Done.
o NATURAL. This is the currency in which the transaction occurred.
o REPORTING. This is the reporting currency.

QUANTITY = Financial Transaction Entry quantity.This could represent hours or units.  This is the quantity entered on Timesheets or other Financial Transaction Entries.

NATURAL_STDCOST = Rate Matrix Standard Cost Value as it appears in UI
This is the Standard Cost value from the Rate Matrix Row no computations are done for this field.

STDCOST = NATURAL_STDCOST * COST_EXCHANGE_RATE
If a single currency environment is configured, the Exchange Rate = 1.00
Otherwise, the Exchange Rate is calculated from the configuration for selected Currency Code, and Exchange Rate Type.  Reference STDCOST_CURRENCY_CODE

NATURAL_ACTUALCOST = Rate Matrix Actual Cost Value as it appears in UI
This is the Actual Cost value from the Rate Matrix Row no computations are done for this field.

ACTUALCOST = NATURAL_ACTUALCOST * COST_EXCHANGE_RATE
If a single currency environment is configured, the Exchange Rate = 1.00
Otherwise, the Exchange Rate is calculated from the configuration for selected Currency Code, and Exchange Rate Type.  Reference COST_CURRENCY_CODE.

PRICE = Rate Matrix Actual Cost Value as it appears in UI
This is the Actual Cost value from the Rate Matrix Row no computations are done for this field. (Same as NATURAL_ACTUALCOST)

TOTALCOST = ACTUAL_COST * QUANTITY

NATURAL_BILLRATE = Rate Matrix Rate Value as it appears in the UI **IF no Cost Plus Code is defined on the Rate Matrix Row**

If a Cost Plus Code is defined on the Rate Matrix Row for the Project’s ‘Rate’ Matrix, then the NATURAL_BILLRATE does not use the Rate Matrix Rate Value, as described below:
NATURAL_BILLRATE = NATURAL_ACTUALCOST when a Cost Code applied to Actual Costs is defined on the Rate Matrix Row
NATURAL_BILLRATE = NATURAL_STDCOST when a Cost Code applied to Standard Costs is defined on the Rate Matrix Row

BILLRATE = NATURAL_BILLRATE * RATE_EXCHANGE_RATE
If a single currency environment is configured, the Exchange Rate = 1.00
Otherwise, the Exchange Rate is calculated from the configuration for selected Currency Code, and Exchange Rate Type.  Reference RATE_CURRENCY_CODE.

AMOUNT = BILLRATE * QUANTITY

When a Cost Plus Code is defined on the Rate Matrix Row that is used for calculating costs, the following fields are calculated, otherwise the values in these fields is zero.

FACTORAMOUNT = AMOUNT * Cost Plus Multiplier
BURDEN = AMOUNT * Cost Plus Burden
OVERHEAD = AMOUNT * Cost Plus Overhead
TOTALAMOUNT = AMOUNT + FACTORAMOUNT + BURDEN + OVERHEAD

Query for Retrieving Records from Wip.

The query below extracts a list of the transaction and adds together any which have been adjusted through timesheet adjustments etc. The query is filtered to only show the Test001 project.

select srm.unique_name,srm.name,wip.transdate,wip.transtype,sum(wip.quantity) hrs,sum(value.totalcost) cost,value.billrate
from srm_projects srm,PAC_MNT_PROJECTS pac,ppa_wip wip,ppa_wip_values value
where
wip.project_code = pac.project_code and wip.transno = value.transno
and value.currency_type= 'HOME' and PAC.project_code = 'TEST001'
and srm.id = pac.id
group by srm.unique_name,wip.transtype,srm.name,wip.transdate,value.billrate

PPA_WIPAPINFO
 
This table contains supplemental accounts payable (AP) information for transactions imported from an AP system.For Non Manday Resource Type Expense the transactions are stored in PPA_WIPAPINFO Table.GL Upload on Expense will fall in this Type.

SELECT
                        WP.PROJECT_CODE, WP.RESOURCE_CODE, UPPER(WP.EMPLYHOMELOCATION) EMPLYHOMELOCATION
                       , UPPER(WP.PROJECT_LOCATION) PROJECT_LOCATION, WP.PROJECT_DEPARTMENT, WP.WIPCLASS
                       , WP.TRANSDATE, UPPER(WP.TRANSTYPE) TRANSTYPE, UPPER(WP.TRANSCLASS) TRANSCLASS
                       , WP.CHARGE_CODE, WAI.VOUCHERNO, WAI.PONO, WAI.VENDOR_CODE, WPV.TOTALCOST
                FROM
                       PPA_WIP WP , PPA_WIP_VALUES WPV, PPA_WIPAPINFO WAI
                WHERE
                       WP.TRANSNO = WPV.TRANSNO AND WPV.CURRENCY_TYPE = 'HOME' AND WP.PPA_WIPAPINFO_ID = WAI.ID (+)
                       AND WP.TRANSDATE BETWEEN 'Start Date' AND  'End Date' AND WP.STATUS = 0
                       and WP.PROJECT_CODE = 'Project Code' and TRANSTYPE ='X'