Hi All,
I am facing a performance issue with my report which is having two measures from two different fact tables.
Report is having one prompt with the static values "1.Actual" & "2.AOP". Report is composed of 10 lists and when user runs the report and selects "Actuals" in a prompt, those 10 lists should show Actual amount, when user runs the report and selects "AOP" in a prompt then those 10 lists should show Budget amount. For this i have created one data item as follows:
case when ?Par_Amount?='Actuals' then (-1*[Actual Net Amount(USD)])
when ?Par_Amount?='AOP' and [PNL].[Report Structure Hierarchy].[Report Elements].[Report Elements].[Level1 Name]='SVC' then (-1*[Budget Amount(USD)])
when ?Par_Amount?='AOP' and [PNL].[Report Structure Hierarchy].[Report Elements].[Report Elements].[Level1 Name]='Sales' then ([Budget Amount(USD)])
else 0
end
when i select "Actulas" in prompt, report is running in less than 2 mins. if i select "AOP"(Budget) it is keep on running for a log time. I examined the generated SQL for all 10 list queries and they are giving data in few seconds in Database. Even when checked for view tabular data, i am getting data in seconds. i am not able to find the root cause for this performance issue.(Enabled "Local Processing" for some queries because i have used some cognos functions in some queries). Your valuable suggestions are highly appreciated.
Hi Sri,
It looks like the processing is being done on the Cognos server instead of the database, and the server isn't running as fast as it should. What's the level of granularity on your query? I'm guessing [Level1 Name] is lower than your list levels. I'm also assuming the aggregate functions are both set to total?
If your parameter is Actuals, the work is very simple; [Actual Net Amount] is already defined in your model for easy summing. But if your parameter is AOP, now the query processor has to break the rows down, evaluate each row to determine the [Level1 Name], and then decide whether the budget amount is positive or negative. It's obviously taking the cognos engine a lot of time to do this.
Try to experiment with your query to see if you can remove Level 1 Name from the equation, at least at the higher level. Also, create a data item called [AOP Amount] that just does the AOP calculations, and turn the below function to use one or the other. Hopefully that will tell Cognos to send this to the database.