Calculation Script Duration Execution Issue

Rachid Barhoune

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

Rachid Barhoune

Hi,

Could Someone help please?

Thanks in advance,

Best regards

 

GlennS_3

There is too much info missing and inaccurate to help

1. Your stats don;t make sense. IT say the number of existing blocks is 281328949440 but the existing 0 level blocks is 0 and upper level blocks are 0

2. You give us the list of dimensions , but I have no idea which ones are attributes and which ones are base dimensions

3. From the application log, can you tell us how long each step is taking so we can concentrate on long running parts?

 

I will say from the calc, it looks to be pretty inefficient. You really don't fix on much so the calculation pretty much at ever level of the database and then will over wirte the upper levels with the Agg.

Rachid Barhoune

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

GlennS_3

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

Gleen,

Thank you for your precious explanations.

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

Best regards,