TM1 Forum

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

Staff Cube with Movement Analysis - Feeders

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

Page 1 of 1

Staff Cube with Movement Analysis - Feeders

Posted: Wed Dec 26, 2012 11:26 pm
by RJ!
I'm having issues with setting up the feeders to my Staff Cube...

My problem seems to be determining the logic for the Feeders for "Attrition" & "Transfer Out" Balance Types.
For the "New" & "Transfer In" the Feeder seems to be working properly when referencing the Balance Type "EOP" as the rules for those Types are largly based on the EOP.
For the Attrition & Transfer Out, the Rule references a 2nd cube (Biz_Staff_Map) that is like a mapping / DB Cube that stores the historical data as String Values so that I can query if the Employees ID existed last month and / or changed Cost Centre between months. Am I correct in saying that this should be the source for my Feeder?

Below is the Code I've currently got saved in the Staff Cube Rule:

Code: Select all

		# 201212 - JC Rules to Feed the Account Structure

SKIPCHECK ;


#UNDEFVALS ;

# New
['New'] = N:	

# Does the SOE ID have a current Balance in any account:
		If( DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,!Period,'EOP','HeadCount') <> 0 & 
# Does the SOE ID have a balance in any RC last month:
		DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,'Total RCs','940198PA',attrs('Period', !Period, 'Prior_Mth'),'EOP','HeadCount') = 0 %
		DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,'Total RCs','940199PA',attrs('Period', !Period, 'Prior_Mth'),'EOP','HeadCount') = 0
# If True
		,DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,!Period,'EOP','HeadCount')
# Else
 		, STET ) ;	

# Attrition
['Attrition'] = N: 

# 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

# No balance Exists in TOTAL RC's in Current Period:  
			If (DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,'Total RCs',!Account,!Period,'EOP','HeadCount') = 0 ,
# If True			
			DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,attrs('Period', !Period, 'Prior_Mth'),'EOP','HeadCount')*-1
# Else
			, STET )) ;
		
#Transfer In
['Transfer In'] = N:			

# Balance Exists in TOTAL RC's in Last Period:
		If( DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,'Total RCs','940199PA',attrs('Period', !Period, 'Prior_Mth'),'EOP','HeadCount') <> 0
#		% DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,'Total RCs','940199PA',attrs('Period', !Period, 'Prior_Mth'),'EOP','HeadCount') <> 0
# Balance Exists in this period:
		& DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,!Period,'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
		 DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,!Period,'EOP','HeadCount')
# Else
		, STET ) ;

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

# If Last Month EOP is 0, then there is no Transfer out!
		If ( DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,attrs('Period', !Period, 'Prior_Mth'),'EOP','HeadCount') = 0 , STET ,
# 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 0:
		& DB('Biz_Staff2',!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:
		& DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,'940198PA',!Period,'EOP','HeadCount') <> 1 , 
# If True
		DB('Biz_Staff2',!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','HeadCount') * -1 ,
# Else
# If Staff changed from Direct Staff to FTE
		If ( DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Direct Staff Flag') @= 'Y' & DB('Biz_Staff_Map',!Period,!Staff,'Direct Staff Flag') @= 'N' , 
#If True
		DB('Biz_Staff2',!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','HeadCount') * -1 ,
# If FTE Number changes between the months, there has been a transfer between being FT & PT
#	If ( DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!Staff,'FTE') <>  DB('Biz_Staff_Map',!Period,!Staff,'FTE') ,
# If True
#	DB('Biz_Staff2',!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','HeadCount') * -1 ,
# Else
		STET ))) ;

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

[BalanceType: 'EOP'] => [ BalanceType: 'New'] ;
[BalanceType: 'EOP'] => [ BalanceType: 'Transfer In'] ;

#Test 1:
#[BalanceType: 'EOP'] => [ BalanceType: 'Attrition'] ;
#[BalanceType: 'EOP'] => [ BalanceType: 'Transfer Out'] ;
#Test 2:
#[BalanceType: 'EOP'] => DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,attrs('Period', !Period, 'Prior_Mth'),'Attrition','HeadCount') ;
#[BalanceType: 'EOP'] => DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,attrs('Period', !Period, 'Prior_Mth'),'Transfer Out','HeadCount') ;
Per the Code, I have tried to reference just the Balance Type in Test 1 & then the tried to use Prior Month in Test 2.

Since the Attrition & Transfer Out rely on the results from references back to the Biz_Staff_Map Cube, should be Feeder be looking at that Cube for my Feeder?

An example of my Biz_Staff_Map is below:
Image

So for the employee above, they left in September and should be an attrition in my Cube in October so when looking at the leaf level I get this:
Image

Though as soon as i use the Zero Supress, I loose my Attrition:
Image

So the first one proves that my rule works, the 2nd is obvioulsy suggesting that I have issues with my existing Feeders (Whether I user Test 1 or Test 2 versions of their Feeders)

Any ideas on where I'm going wrong?

Re: Staff Cube with Movement Analysis - Feeders

Posted: Tue Jan 01, 2013 11:09 pm
by RJ!
Happy New Year Bumb!!

Re: Staff Cube with Movement Analysis - Feeders

Posted: Wed Jan 02, 2013 3:45 am
by beek
Hi RJ!,

You are asking why is the -1 not reflecting when you use the Zero Suppressed? have you tried to Trace Feeder & Trace calculation ? From there, you should be able to figure out what feeder you lack of, that is causing the -1 not reflecting ?

Re: Staff Cube with Movement Analysis - Feeders

Posted: Wed Jan 02, 2013 5:34 am
by RJ!
Hi Beek,

Yeah, I have been looking at the Trace Calculation & Feeder and it's fine for the lowest level stuff but I do the Trace Feeder for anything that is higher than that.

Re: Staff Cube with Movement Analysis - Feeders

Posted: Wed Jan 02, 2013 6:06 am
by beek
Did you add feeder at the Biz_Staff_Map cube ? Usually when the value is gone at Zero Suppressed, means the feeder is not fed.

Re: Staff Cube with Movement Analysis - Feeders

Posted: Wed Jan 02, 2013 6:27 am
by RJ!
Yeah, I know my feeder is not feeding properly, I'm just not sure why :oops:

Per your suggestion of feeding from the Sys_Staff_Map cube, how do you go about doing that?

Re: Staff Cube with Movement Analysis - Feeders

Posted: Wed Jan 02, 2013 8:30 am
by beek
just read your feeder again. I noticed, there's no feeder for Attrition. (even for biz_Staff2 cube)
If my understanding on your cube is correct, if there's EOP of the previous month, there should b Attrition this month. right? If so, the feeder can be something like
[BalanceType: 'EOP'] => DB('Biz_Staff2',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,!Account,attrs('Period', !Period, 'Next_Mth'),'Transfer Out','Attrition') ;

Pls check the dimensions. most importantly is EOP is to feed to the N+1 month for Attrition.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited