Calculation Script Duration Execution Issue

Question

Summary

Calculation script takes too time to be done

Content

Hi everybody,

I have an issur on one Calculation script that takes about 5 to 6 hours to perform claculation on the cube.

See attached features of the cube.

Essbase version 11.1.2.3

OS : AIX 7.4.1

Please could you help me to understand possible root cause of this bad performance?

Thank you in advance

Best Regards,

Version

11.1.2.3.500

Def_Calc_csc.sql

Rachid Barhoune · Answer

Gleen,

Thank you for your precious explanations.

I will try with reviewing my script and make testsd with FIXPARALLEL and CALCTASKDIM.

Best regards,

GlennS_3 · Answer

So most of the time is on the calc script(which I expected). In the application log, you should be able to find where the calc script starts and there will be breaks as it changes steps. Knowing how long each step takes helps to determine where the issue lies.

There are some certainly things that could help improve performance

1. Make your fix more restrictive to only calculate level zero members from all dimensions not in the calculation formula

2. I am not sure why you are doing all of the @idescendants (that will agg the children to that member.) Is it necessary do that to get the calculation to work?

Without being able to detail te problem, these are just general observations. One would need to spend time knowing the outline and what the calculations are doing to help optimize it. IF you don;t have that skill set yet, I suggest you bring in someone for a day or two to help optimize it

Rachid Barhoune · Answer

Hi Glenn,

Thank you for your reply.

let's see your questions:

2) see Dimensions_ECC.png for details

3) In the application log, you have 2 sub scripts one for build dimensions (4 min) , second for data (take the remain 9 or 10 hours).

in the script data building : there are rules to load data and take about 30 mn and the remain consummed by calculation script:

below the maxl executed for second step (data):

set message level error;

login admesb identified by 'xxxxxx' on 'localhost';

spool on to /essbase/prd/FIN/log/Data/ECC_maj_data_msh.log ;

/* disconnect users */

alter system logout session on application 'ECC' force;

/*desactivation des connexion*/

alter application ECC disable connects;

/*-------------------------------------------------------------------*/

/* Load data  */

/*-------------------------------------------------------------------*/

alter system load application 'ECC';

alter application 'ECC' load database 'ECC';

import database ECC.ECC data

connect as 'DTMFI' identified by 'xxxxxxxxxxxxxxxxxxxx' using server rules_file 'BUD.rul'

on error write to '/essbase/prd/FIN/log/Data/ECC_BUD_201905070403.err';

import database ECC.ECC data

connect as 'DTMFI' identified by 'xxxxxxxxxxxxxxxxxxxx' using server rules_file 'REA.rul'

on error write to '/essbase/prd/FIN/log/Data/ECC_REA_201905070403.err';

import database ECC.ECC data

connect as 'DTMFI' identified by 'xxxxxxxxxxxxxxxxxxxx' using server rules_file 'REA_LE.rul'

on error write to '/essbase/prd/FIN/log/Data/ECC_REA_201905070403.err';

import database ECC.ECC data

connect as 'DTMFI' identified by 'xxxxxxxxxxxxxxxxxxxx' using server rules_file 'FCT_FR.rul'

on error write to '/essbase/prd/FIN/log/Data/ECC_FCT_201905070403.err';

import database ECC.ECC data

connect as 'DTMFI' identified by 'xxxxxxxxxxxxxxxxxxxx' using server rules_file 'NFData.rul'

on error write to '/essbase/prd/FIN/log/Data/ECC_NFData_201905070403.err';

import database ECC.ECC data

connect as 'DTMFI' identified by 'xxxxxxxxxxxxxxxxxxxx' using server rules_file 'NF.rul'

on error write to '/essbase/prd/FIN/log/Data/ECC_NF_201905070403.err';

/*-------------------------------------------------------------------*/

/* Script de calcul */

/*-------------------------------------------------------------------*/

execute calculation ECC.ECC.Calc_Fin;

/*-------------------------------------------------------------------*/

/* Enable Login */

/*-------------------------------------------------------------------*/

/*activation des connexion*/

alter application ECC enable connects;

echo spool off;

logout;

exit;

==> All steps concerning data load consums 30 mn

==> execute calculation ECC.ECC.Calc_Fin : takes more than 5 hours (today is still running since this morning at 04:00 am!!!!

Do you need more information ?

Thanks in advance