Step By Step Approach To Increase the performance of the report.

·         Server Tuning

o   Disk – IO Stat

o   RAM -VMStat

o   CPU - Top

·         Instance Workload Tuning

o   Parameters – Optimizer Settings

o   MetaData –Optimizer Statistics Tuning

o   Cursor Management  - Cursor Tuning

·         Instance Object Tuning

o   Row Packing – PCTFree- PCT Used

o   Concurrency -Freelists

o   Table Row Location  - Sorted hash clusters

o   Caching – Objects into KEEP pool

·         SQL Statement Tuning

o   Rewrite SQL – Optimizer SQL with faster syntax formats.

§  Current Baseline From Production, and test environment.

§  Understand the current query completely and thoroughly.

§  Break the Data export query into 5/more section (We will focus section wise, each section we will go with below and that we will do today).

§  This is the order of the SQL execution, our analysis of data extract query must be in the order by mentioning current implementation and target implementation.

·         Where

·         Join/union

·         Order/group by

·         Select

§  List of solution identified.

·         Low

·         Medium

·         High

SQL Profiles – for static execution plans.

Process Performance - CA PPM - Process Engine Maintanence

For oracle run SHRINKS on NMS_MESSAGES daily

- often those tables get heavily fragmented due to bulk imports. when that happens, they grow in size consuming many blocks. That data is eventually deleted and normal usage does not need that many blocks.

• ALTER TABLE NMS_MESSAGES ENABLE ROW MOVEMENT
• ALTER TABLE NMS_MESSAGES SHRINK SPACE CASCADE

• ALTER TABLE NMS_MESSAGE_DELIVERY ENABLE ROW MOVEMENT

ALTER TABLE NMS_MESSAGE_DELIVERY SHRINK SPACE CASCADE

SQL Performance - CA PPM - Inline Views

• Inline views can be very beneficial but can severely affect performance
• LEFT JOIN to large inline views is typically not a good idea

Gel Script Performance - CA PPM - Process Design

Create multiple generic process XOG users

• Process XOG users would be restricted to just the rights required for the their particular area (XOG PROJ, XOG RESOURCE, etc)
• Keep start condition, pre-condition and post condition expressions as simple as possible
• for an on -update start condition ensure actions the process takes result in the start condition evaluating to FALSE by the time the process finishes executing.
• This will prevent the process from firing over and over again. 
• Keep the total number of active process for an object minimal 
• use a single "create" process with branching rather than having multiple "create" processes.
• Use the delete process instance job to clean up old process 
• Some may need to be kept for auditing purposes. Identify processes that can be removed and delete them when they have completed via the job.
• Target frequently run processes, which are good candidates to delete as they typically carry almost no auditing benefit.
• During back ground services start up, the process engine loads, compiles, and caches all active processes. 
• This can cause an initial spike in database CPU usage. 
•  Do not use <gel:log> excessively,  Each log call results in a row in BPM_ERRORS
•  Do not use <gel:log> with in a loop with large numbers of iterations. This will affect the performance of your script.
• use a "debug" parameter on your gel script and wrap your calls to <gel:log> with a <core:if>.This will allow you to enable logging when you need it. 
• <core:if test="${debugFlag=1}">
• ⋛: log level ="debug">my debug log message </log>
• </core:if>

Gel Script Performance - CA PPM - Avoid Excessive use of the Core:Set

In complex GEL scripts that build XOG XML from SQL query output, avoid excessive use of the <core:set> tag for performance reasons. This tag use JAVA mbeans for variable storage. Multiple GEL scripts using frequent <core:set> calls will result in significant synchronization performance hit.Instead of using <core:set>, consider using a native Java Object such as a Hash map to store local variables insider tight loops.

Native Hashmap example:

<-Create a HashMap instance -->

<Core:new className="java.util.HashMap" var="myHash"/>

<-Place some values in this hashMap from a previous query -->


<core:invoke on="${myHash}" method="put" >

<core:arg value="projectid" >
<core:arg value="${myQuery.rows[0].ID}" />

</core:invoke >

<core:invoke on="${myHash}" method="put" >

<core:arg value="projectName" >
<core:arg value="${myQuery.rows[0].NAME}" />

</core:invoke >

<-Log this value -->

<gel:log level="info">Processing project ${myHash.get('NAME')}</gel:log>

Gel Script Performance - CA PPM - Util Sleep

Avoid using <util:sleep> in GEL script. This tag puts the thread executing the GEL Script to sleep. There are only 12 GEL threads allocated per process engine instance.  if all 12 are sleeping, other GEL steps will be unable to execute and processes will appear to hang.

SQL Performance - CA PPM - Union Queries

• UNION queries perform poorly as they can scan through the same data multiple times.
• Require any logic changes to be made in multiple locations 

SELECT 

INVI.CODE, 

INVI.NAME, 

SUM (FORECAST_COST) FORECAST_COST, 

SUM(BUDGET_COST) BUDGET_COST FROM 

(SELECT INVI.CODE, INVI.NAME, 

FP.TOTAL_COST,

FP.FORECAST_COST

0 BUDGET_COST FROM INV_INVESTMENTS INVI

JOIN FIN_PLANS FP ON INVI.ID=FP.OBJECT_ID AND

INVI.OBJECT_CODE =FP.OBJECT_CODE 

WHERE FP.IS_PLAN_OF_RECORD =1 AND FP.PLAN_TYPE_CODE="FORECAST"

UNION ALL

SELECT INVI.CODE, INVI.NAME, 

0 FORECAST_COST,

FP.TOTAL_COST BUDGET_COST FROM INV_INVESTMENTS INVI

JOIN FIN_PLANS FP ON INVI.ID=FP.OBJECT_ID AND

INVI.OBJECT_CODE =FP.OBJECT_CODE 

WHERE FP.IS_PLAN_OF_RECORD =1 AND FP.PLAN_TYPE_CODE="BUDGET")

WHERE INVI.CODE ="PR0J01793"

GROUP BY CODE,NAME

* Most UNION queries can easily replaced with logic

SELECT 

INVI.CODE, 

INVI.NAME,

SUM(CASE WHEN FP.PLAN_TYPE_CODE="FORECAST" THEN FP.TOTAL_COST END) FORECAST_COST

SUM(CASE WHEN FP.PLAN_TYPE_CODE="BUDGET" THEN FP.TOTAL_COST END) BUDGET_COST

FROM INVI.INVESTMENTS

JOIN FIN_PLANS FP  ON INVI.ID=FP.OBJECT_ID AND INVI.ODF_OBJECT_CODE=FP.OBJECT_CODE

WHERE FP.IS_PLAN_OF_RECORD=1 AND INVI.CODE="PR0J01793"

GROUP BY INVI.CODE, INVI.NAME

SQL Performance - CA PPM - Distinct vs Exists

• Both methods are commonly used to derive the unique values for dimension keys of portlets.
• EXISTS are preferable to DISTINCT.
• DISTINCT produces the entire result set (including duplicates),sorts, and then filters out duplicates 

SELECT DISTINCT SRMR.FULL_NAME FROM SRM_RESOURCES SRMR JOIN PRTEAM TM ON SRMR.ID=TM.PRRESOURCEID 

Exists proceeds with fetching rows immediately after the sub-query condition has been satisfied the first time 

 SELECT SRMR.FULL_NAME FROM SRM_RESOURCES SRMR 

WHERE EXISTS (SELECT 1 FROM PRTEAM TM WHERE SRMR.ID=TM.PRRESOURCEID) 

SQL Performance - CA PPM - In vs Exists

IN is typically better when the inner query contains a small result set

SELECT SRMR.FULL_NAME
FROM SRM_RESOURCES SRMR
WHERE SRMR.ID (SELECT TM.PRESOURCEID FROM PRTEAM TM WHERE TM.PRPROJECTID IN (5000,5001)

SELECT SRMR.FULL_NAME
FROM SRM_RESOURCES SRMR
WHERE EXISTS (SELECT 1 FROM PRTEAM TM WHERE TM.PRPROJECID IN(5000,5001) AND TM.PRRESOURCEID=SRMR.ID)

EXISTS is typically better when the inner query contains a large result set

SELECT SRMR.FULL_NAME
FROM SRM_RESOURCES SRMR
WHERE SRMR.ID IN (SELECT TM.PRRESOURCEID FROM PRTEAM TM)


SELECT SRMR.FULL_NAME
FROM SRM_RESOURCES SRMR
WHERE EXISTS (SELECT 1 FROM PRTEAM TM WHERE TM.PRRESOURCEID =SRMR.ID)

SQL Performance - CA PPM - OBS Filtering

• Seen many ways to filter based on OBS
• Many rely on complex logic, left joins to inline views, or multiple sub-queries.
• Using EXISTS and OBS_UNITS_FLAT_BY_MODE table provides an easy solution. 
• filter by unit only, unit and descendents, or units or ancestors

SELECT SRMR.FULL_NAME FROM SRM_RESOURCES SRMR WHERE (OBS_ID IS NULL OR EXISTS

(SELECT 1 FROM OBS_UNITS_FLAT_BY_MODE OBSM

JOIN PRJ_OBS_ASSOCIATIONS OBSA 

ON OBSM.LINKED_UNIT_ID= OBSA.UNIT_ID AND OBSA.TABLE_NAME="SRM_RESOURCES" 

WHERE OBSM.UNIT_ID=OBS_ID

AND OBSM.UNIT_MODE=NVL(OBS_MODE,'OBS_UNIT_AND_CHILDREN')

AND OBSA.RECORD_ID=SRMR_ID))

This will give the good performance than PRJ_OBS tables. 

SQL Performance - CA PPM - Restrictive Conditions

 Begin queries with the most restrictive conditions

SELECT SRMR.FULL_NAME, AV.SLICE_DATE,AV.SLICE FROM SRM_RESOURCES SRMR
JOIN PRJ_BLB_SLICES AV ON SRMR.ID=AV.PRJ_OBJECT_ID AND AV.SLICE_REQUEST_ID=7 WHERE SRMR.ID=1
ORDER BY SRMR.FULL_NAME, AV.SLICE_DATE

Instead of


SELECT SRMR.FULL_NAME, AV.SLICE_DATE,AV.SLICE FROM PRJ_BLB_SLICES AV
JOIN SRM_RESOURCES SRMR  ON SRMR.ID=AV.PRJ_OBJECT_ID AND AV.SLICE_REQUEST_ID=7 WHERE SRMR.ID=1
ORDER BY SRMR.FULL_NAME, AV.SLICE_DATE


In terms performance i use first query, because slice table will have more data generally.

SQL Performance - CA PPM - Sub Queries

Sub Queries can be handy

Sub queries are good in some situation but not all situation

How many projects is a resource assigned to

SELECT SRMR.FULL_NAME,(SELECT COUNT(*) FROM PRTEAM TM WHERE TM.PRRESOURCEID=SRMR.ID) TEAM_COUNT FROM SRM_RESOURCES SRMR
ORDER BY SRMR.FULL_NAME


Instead of LEFT JOIN to a large inline view

SELECT SRMR.FULL_NAME, TM.TEAM_COUNT
FROM SRM_RESOURCES SRMR
LEFT JOIN (SELECT TM.PRRESOURCEID, COUNT(*) TEAM_COUNT
FROM PRTEAM TM
GROUP BY TM.PRRESOURCEID) TM ON SRMR.ID=TM.PRRESOURCEID
ORDER BY SRMR.FULL_NAME

Avoid substituting sub-queries for table joins

SELECT SRMR.FULL_NAME,
(SELECT COUNT(*) FROM PRTEAM TM WHERE TM.PRRESOURCEID=SRMR.ID) TEAM_COUNT,
(SELECT SUM(TM.PRALLOCSUM)/3600 FROM PRTEAM  TM WHERE TM.PRRESOURCEID=SRMR.ID) TOTAL_ALLOC,
(SELECT SUM(TM.HARD_SUM) /3600 FROM PRTEAM TM WHERE TM.PRRESOURCID =SRMR.ID) TOTAL_HALLOC
FROM SRM_RESOURCES SRMR
ORDER BY SRMR.FULL_NAME

SQL Performance - CA PPM - Unnecessary Joins

Remove unnecessary tables from queries

Avoid falling back on common tables

• INV_INVESTMENTS
• SRM_RESOURCES

Query to find role allocation hours on a project

SELECT SUM(TM.PRALLOCSUM/3600) HOURS

FROM INV_INVESTMENTS INVI

JOIN PRTEAM TM ON INVI.ID=TM.PRPROJECTID

JOIN SRM_RESOURCES SRMR  ON TM.PRRESOURCEID=SRMR.ID

JOIN PRJ_RESOURCES PRJR ON SRMR.ID=PRJR.ID

WHERE INVI.ID=50000001 AND PRJR.PRISROLE=1

in this case not require to link with SRM_RESOURCES only PRJ_RESOURCES is enough.

SQL Performance - CA PPM - Using Table Aliases

Using Table Aliases

Improves readability of SQL

Use meaningful table aliases

SELECT A.ID FROM SRM_RESOURCES
JOIN ODF_CA_RESOURCE B ON A.ID=B.ID


Allows queries to be modified more easily

SELECT ID FROM SRM_RESOURCES
JOIN ODF_CA_RESOURCE ON SRM_RESOURCES.ID =ODF_CA_RESOURCE .ID


Help performance by eliminating the need for the datbase to search the tables for the referenced column

CA PPM 14.4 | The Armstrong Release

1. What’s Removed from CA PPM 14.4?

First, the moment we all expected has been confirmed: Business Objects is gone. Au revoir Crystal Reports, you complicated, beautiful things. Hello again, Jaspersoft. We’ve been enjoying you since 14.2 (especially since your dashboards stepped it up in 14.3.)

While there’s a handful of End of Life (EOL) announcements for add-ins and connectors, the other notable removal in CA PPM 14.4 is WebSphere, so you’ll want to migrate to Tomcat before upgrading. (See the product support page).

Let’s get to the fun stuff: what’s been added and enhanced.

2. What’s New in CA PPM 14.4?

Our favorite new addition to CA PPM 14.4 is for Advanced Reporting in Jaspersoft Studio. Along with the JasperReports Server Cumulative Patch, which corrects several issues, CA added a JDBC driver, which lets you develop reports faster in SaaS, since you’ll be able to access Jaspersoft studio without needing VPN access to the database servers.

CA PPM 14.4 Enhancements You Need to Know

While there are many enhancements in the Armstrong release, including to CA Agile Central Support (formerly Rally, see https://wiki.ca.com), there are several others we need to outline.

3. Portfolio Management Enhanced with ACAs

In CA PPM 14.4, it’s now possible to add aggregated calculated attributes (ACAs) to investment objects. Hooray. This will allow you to role up child investments, like projects, and view their summaries by portfolio.

4. Administration Enhanced with New Password Policy

The Armstrong Release operates with a new, out-of-the-box (OOTB) password policy. Industry standards are the new rule, unless you want to keep your existing passwords when you upgrade. That means you’re looking at eight characters with uppercase, lowercase, numeric, and special characters included. You know the drill.

5. Administration Enhanced with Load Data Warehouse Job Changes

Planning to run a Load Data Warehouse Job? CA PPM 14.4 now incorporates financial plans: the Plan of Record, the current budget plan, and all benefit plans.

6. Timesheet Auto-Populate Options

Here’s one you’ll probably enjoy. The Armstrong release has smoother timesheet options. Imagine opening up your timesheet and seeing all your tasks auto-populated. Now imagine seeing last week’s time entries auto-entered against those tasks. You can stick to just the tasks or have the time added.

It makes the whole process faster, and in this release the options are easier to find. Just check the box you want: populate assigned tasks and/or copy time entries from previous timesheet.

 

Clarity 14.4 Upgrade from Clarity 14.3 With Jaspersoft

Supported upgraded path from clarity 14.3 to 14.4

Export option using Clarity 14.3

This is regarding the export option of Clarity.

Export to Excel (Data) : Maximum we can download 1,50,000 now and we can extend by changing the settings in CSA for export to excel.

·         Each sheet only download 65536, If the data is more,  automatically new sheet will be created to accommodate new data.

Export to excel : We can download only 300 records with format as per CA. The idea of this option is to download the Export to excel (Data) and then do the format.

You can also refer  below link for 300 record count for export to excel.

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec1046117.aspx

Conclusion :

Business must use Export to Excel (Data) option for downloading more data, which is not more than 1,50,000 and its restricted now.

This can be extended by changing the settings in the CSA and Clarity (System Settings). We can’t set it to specific portlet.

Now we need to define do we require export of data more than 1,50,000.(Current Settings) or not.

This is the recommendation from CA for export and mentioned on premises and on demand.

http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec611566.aspx

Note: Clarity System settings is depend on CSA settings.

I filed, 20,00000 lakh in CSA, it was working. If you leave it blank, it become unlimited.

CA recommendation is very less, refer my yesterday link.

What you need to know about Jaspersoft

• Easy to get started
• Comes with Analytical Engine
• Most sort out functionalities like Email Report
• Locating Data is simplified
• Ability to Publish Jasper report as portlet.
• Comes with its own security and auditing services.

What is Jaspersoft?

• First ever tightly integrated advanced reporting solution with CA PPM
• Available from Clarity 14.2
• Offers true self service  and adhoc reporting 
• Utilizes data warehouse
• Offers mobile reporting. 
• Offers Web and Rest service to perform major option from client tool.
• Platform independent.
• Its java based system 
• You can fully customize the solution 
• It has forward compatibility 
• Clarity 14.3 using latest version of Jaspersoft 6.1

Rest Service Using Jaspersoft

The below are the sample Rest service and configuration in Dev environment.

Right now I have stopped looking at the solution on web service because, in the future road map of Jaspersoft Web service (soap) will be removed only supported Rest service.

You can also try below link directly and you can see the output. Ping me if in case asked any credentials and hope not.

https://www.dev.rep.clarity.test.com/reportservice/rest_v2/users

https://www.dev.rep.clarity.test.com/reportservice/rest_v2/roles

you can also access organization specific.

https://www.dev.rep.clarity.test.com/reportservice/rest_v2/organizations/organization_1/roles

https://www.dev.rep.clarity.test.com/reportservice/rest_v2/organizations/organization_1/users