TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

Staff Cube with Movement Analysis - Cube Performance

https://www.tm1forum.com/viewtopic.php?t=8566

Page 1 of 1

Staff Cube with Movement Analysis - Cube Performance

Posted: Tue Feb 19, 2013 6:54 am
by RJ!
Hi All,

I have finally built my first Cube in TM1!! :P

While everything is calculating & consolidating properly, the performance of the cube is quite bad, it takes anywhere from 5 - 8 sec's for any refreshes or browsing that requires calculations of the rules :cry:
We have much bigger cubes with more complicated rules being able to be refreshed/ browsed with minimal hang while things are being calculated so it's not a Server thing.

Below is the current set of Rules attached to the Cube, does anyone have any suggestions on improving the performance of the cube by tweaking the Rules / Feeders?
If I delete out the rules & feeders, the cube refreshes within 1sec...

Code: Select all

# Direct Staff (DS) (L1) (940198PA)
#    Direct Staff Full Time-GDW (L2) (940190PA)
#    Direct Staff Part Time-GDW (L2) (940191PA)
#
# Full-Time Equivalents (FTE) (L1) (940199PA)
#    FTE Full Time-GDW (L2) (940192PA)
#    FTE Part Time-GDW (L2) (940193PA)
#    FTE Overtime-GDW (L2) (940194PA)
#    FTE Temporaries-GDW (L2) (940195PA)
#    FTE Contractuals-GDW (L2) (940196PA)
#    FTE Consultants-GDW (L2) (940197PA)

SKIPCHECK ;

# ML define prior period data for working/temp elements
['EOP Calc Prior'] = N: DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,Attrs('Period',!Period,'Prior_Mth'),'EOP',!MeasureType) ;
['EOP Calc Prior Total RC'] = N: DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth') ,!Staff,'Local Org Code'),!Account,attrs('Period',!Period, 'Prior_Mth'),'EOP',!MeasureType);


# New
['New'] = N:	

# Exclude if the Account has Overtime (940914PA) as an ancestor
	If (ELISANC('Account','940194PA',!Account)=1,STET,
# If the Prior month Direct Flag is not Null, then its a Reclassification:
	If (DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Direct Staff Flag') @<> '' , STET ,
# Does the SOE ID have a current Balance in any account:
	If( [BalanceType:'EOP','Headcount'] <> 0 & 
# Does the SOE ID have a Valid RC last month:
	DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Local Org Code') @='',
# If True
	[BalanceType:'EOP','Headcount']
# Else
 	, STET ))) ;

# Attrition
['Attrition'] = N: 

# Exclude if the Account has Overtime (940914PA) as an ancestor
	If (ELISANC('Account','940194PA',!Account)=1,STET,
# Is the SOE ID missing a valid Local Code for the current month in Biz_Staff_Map2:
	If ( DB('Biz_Staff_Map',!Period,!Staff,'Local Org Code') @<> ''	
#If True
	, STET , 	
# Else
# Ignore if Current Period is greater than Current Month in z_ctrl_measures:  
	If (DIMIX( 'Period',!Period)-1 < DIMIX('Period',DB('z_ctrl_measures','Current Month','String 1')),
# No balance Exists in TOTAL RC's in Current Period:
	If (['Total RCs',BalanceType:'EOP','HeadCount'] = 0 ,
# If True			
	['EOP Calc Prior'] *-1
# Else
	, STET) , STET ))) ;
	
#Transfer In
['Transfer In'] = N:

# Exclude if the Account has Overtime (940914PA) as an ancestor
	If (ELISANC('Account','940194PA',!Account)=1,STET,
# Balance Exists in Last Period:
	If( DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Local Org Code') @<> ''
# Balance Exists in this period:
	& [BalanceType:'EOP','Headcount'] <> 0
# RC in Last Period is not the same as current Period:
	&  DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Local Org Code') @<> DB('Biz_Staff_Map',!Period,!Staff,'Local Org Code') ,
# If True
# Determine if DS or FTE:
	If (ELISANC('Account','940198PA',!Account)=1, 
# Direct Staff:
	If (DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Direct Staff Flag') @= DB('Biz_Staff_Map',!Period,!Staff,'Direct Staff Flag'),
	[BalanceType:'EOP','Headcount'], STET)

# FTE Staff:
	, [BalanceType:'EOP','Headcount']) ,
	 STET )) ;


# Transfer Out
['Transfer Out'] = N: 

# Exclude if the Account has Overtime (940914PA) as an ancestor
	If (ELISANC('Account','940194PA',!Account)=1,STET,
# If Last Month EOP is 0, then there is no Transfer out!
	If ( ['EOP Calc Prior'] = 0 , STET ,
# Determine if DS or FTE:
	If  (ELISANC('Account','940198PA',!Account)=1, 
# If True
# Direct Staff:
# Does the RC does have a valid Local Code for the current month in Biz_Staff_Map:
	If (DB('Biz_Staff_Map',!Period,!Staff,'Local Org Code') @<> '' 
# Is the last period RC not equal to current month RC:
	& DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Local Org Code') @<> DB('Biz_Staff_Map',!Period,!Staff,'Local Org Code')
# Is the Current Month DS balance equal to Null:
	& DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,DB('Biz_Staff_Map',!Period, !Staff,'Local Org Code'),!Account,!Period,'EOP','HeadCount') @= '' 
# Direct Staff Flag in Last Period is the same as current Period:
	& DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Direct Staff Flag') @= DB('Biz_Staff_Map',!Period,!Staff,'Direct Staff Flag') 
# Is the Current Month DS balance not equal to 0 for current RC:
	& [BalanceType:'EOP','Headcount'] <> 1 ,
# If True
	['EOP Calc Prior Total RC'] * -1 ,
# If False
	STET), 

# FTE Staff:
# Does the RC does have a valid Local Code for the current month in Biz_Staff_Map:
	If (DB('Biz_Staff_Map',!Period,!Staff,'Local Org Code') @<> '' 
# Is the last period RC not equal to current month RC:
	& DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Local Org Code') @<> DB('Biz_Staff_Map',!Period,!Staff,'Local Org Code')
# Is the Current Month DS balance equal to Null:
	& DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,DB('Biz_Staff_Map',!Period, !Staff,'Local Org Code'),!Account,!Period,'EOP','HeadCount') @= '' 
# Is the Current Month DS balance not equal to 0 for current RC:
	& [BalanceType:'EOP','Headcount'] <> 1 ,
# If True
	['EOP Calc Prior Total RC'] * -1 , STET
 	)))) ;


['Reclassification'] = N: 

# Exclude if the Account has Overtime (940914PA) as an ancestor
	If (ELISANC('Account','940194PA',!Account)=1,STET,
# Is the last period Direct Staff Flag not equal to current month Flag:
#	If ( DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Direct Staff Flag') @= '' % DB('Biz_Staff_Map',!Period,!Staff,'Direct Staff Flag') @= '' , STET ,
# Determine if DS or FTE:
	If  (ELISANC('Account','940198PA',!Account)=1, 
# Direct Staff:
# Is the last period Direct Flag not equal to current month Flag:
	If ( DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Direct Staff Flag') @= '' % DB('Biz_Staff_Map',!Period,!Staff,'Direct Staff Flag') @= '' , STET ,
# Does the DS Flag Change
	If (DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Direct Staff Flag') @<> DB('Biz_Staff_Map',!Period,!Staff,'Direct Staff Flag') ,
# If True
	[BalanceType:'EOP','Headcount'] - ['EOP Calc Prior']  ,
# If False
	STET)) , 

# FTE Staff:
# Is the last period RC not equal to current month RC:
	If ( DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Local Org Code') @= DB('Biz_Staff_Map',!Period,!Staff,'Local Org Code'),
# Does the FTE Description Change
		If (DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'FTE Description') @= DB('Biz_Staff_Map',!Period,!Staff,'FTE Description'), 
# If the FTE Description does not change:
			If (DB('Biz_Staff_Map',!Period,!Staff,'FTE') = DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'FTE'), 
				STET,
# Insert Calc for Staff that have changed FTE total between months:
				DB('Biz_Staff_Map',!Period,!Staff,'FTE') - DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'FTE')),
# Else Insert Calc for Staff that has changed FTE Description between months:
			[BalanceType:'EOP','Headcount'] - ['EOP Calc Prior'] )
		, STET ))) ;


['OT Offset'] = N:

# Include if the Account has Overtime (940914PA) as an ancestor
	If (ELISANC('Account','940194PA',!Account)=1,
	If (DIMIX( 'Period',!Period) > DIMIX('Period',DB('z_ctrl_measures','Current Month','String 1')), STET,	
	DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Local Org Code - OT'),!Account,attrs('Period', !Period, 'Prior_Mth'),'EOP','HeadCount') *-1),
	STET) ;

['OT Incurred'] = N:

# Include if the Account has Overtime (940914PA) as an ancestor
	If (ELISANC('Account','940194PA',!Account)=1,
	[BalanceType:'EOP','Headcount'],
	STET) ;


# Logic for displaying B/S or Nonfinancials when a multiperiod consolidated rollup is selected. Note:
#     current month:  DB('z_ctrl_measures','current month','string 1')
#     check if version is NOT forecast (ie Version is Actuals):   ELISANC('Version', 'Forecast Versions', !Version)=0
#     check if version has Variance Versions as a parent

[] = C:

# consolidate immediate children for versions that have Variance Versions as a parent
If ( ELISPAR('Version','Variance Versions',!Version)=1,

   ConsolidateChildren('Version'),

   #check no. of periods is multiple and the Balance Type is not AVG Calc (as this needs to be consolidated for AVG multiperiod calculation)
   If ( ELCOMPN('Period', !Period)>1 & !BalanceType@<>'AVG Calc',

      #if multiperiod attribute specifies "FIRST" (eg BOP # Accounts), then display 1st period in subset
      If ( Attrs('Account', !Account, 'MultiPeriod') @= 'FIRST' % !BalanceType @='BOP',

          DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,
                ELCOMP('Period', !Period,1),!BalanceType,!MeasureType) ,

          #if account is balance sheet or "LAST" attribute (eg EOP # of Accounts) then...
          If ( Attrs('Account', !Account, 'GL Category') @= '1' % Attrs('Account', !Account, 'GL Category') @= '2' % Attrs('Account', !Account, 'MultiPeriod') @= 'LAST' % 
	!BalanceType @='EOP' % !BalanceType @='EOP Calc',

              #if period rollup is an ancestor of current month AND version is actuals (ie not an ancestor of Forecast Versions rollup) then...
              If (  ELISANC ('Period', !Period, DB('z_ctrl_measures','current month','string 1') )=1 & ELISANC('Version', 'Forecast Versions', !Version)=0 ,

                     #...then use current month balance
                     DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,
                           DB('z_ctrl_measures','current month','string 1'),!BalanceType,!MeasureType) ,

                     #...else display balance of last period in subset
                    DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,
                          ELCOMP('Period', !Period,ELCOMPN ('Period', !Period)),!BalanceType,!MeasureType) ),

            CONTINUE) ),

      CONTINUE)
);

					
#----------------------------------------
		
FEEDERS;

[BalanceType: 'EOP'] => [ 'New'] , ['Transfer In'] , ['OT Incurred']  ;
[BalanceType: 'EOP'] => ['EOP Calc Prior'],['EOP Calc Prior Total RC'] ;

[BalanceType: 'EOP'] => DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,attrs('Period', !Period, 'Next_Mth'),'Attrition','HeadCount') ;
[BalanceType: 'EOP'] => DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,attrs('Period', !Period, 'Next_Mth'),'Transfer Out','HeadCount') ;
[BalanceType: 'EOP'] => DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,attrs('Period', !Period, 'Next_Mth'),'Reclassification','HeadCount') ;
[BalanceType: 'EOP'] => DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,attrs('Period', !Period, 'Next_Mth'),'OT Offset','HeadCount') ;
This is the Default screen looks like for reference:
Image

Regards,

RJ

Re: Staff Cube with Movement Analysis - Cube Performance

Posted: Thu Feb 21, 2013 3:48 am
by RJ!
Any suggestions welcome :)

Re: Staff Cube with Movement Analysis - Cube Performance

Posted: Thu Feb 21, 2013 11:24 am
by whitej_d
Hi,

It looks like there are a lot of conditional statements in there. That's probably what's slowing it down, but 5 - 8 seconds doesn't seem that bad in all honesty.

Unless you're prepared to rethink the logic to reduce the number of If statements, I would suggest the following to improve performance:

1. Reorder cube dimensions if you haven't already done so. It will have more of an effect on memory than calculation performance, but may still help.
2. Is the cube taking a lot of manual data entry? If not, then do a View Construct at the end of your data loads in a TI to cache all the calculations. You may also find that playing around with the VMM and VMT settings gives some improvement.
3. If the cube is taking a lot of manual data entry, then consider using a freeze process to push calculated data periodically to a non ruled cube for reporting purposes. Turning on personal workspace for users can also help by limiting the frequency that the cache is destroyed.

Re: Staff Cube with Movement Analysis - Cube Performance

Posted: Fri Feb 22, 2013 4:15 am
by RJ!
I'd already used the Cube Optimiser and it helped a little, I think it stated 13% improvement, though as you said, that may actually be just relating to memory.

This cube is not taking manual entry, it's data is coming from a TI process that loads to the EOP BalanceType, the rules then analyse the data to populate the other BalanceTypes that you can see under "HeadCount Analysis". Can you confirm what you mean by a "View Construct" to cache the calculations?

I "could" just set up a TI process to populate directly to each of the BalanceTypes, I'm sure that would be more efficient on terms of performance, though I'm sure that will grow the size of the cube by a decent margin. Though would leave this as a last resort...
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited