Rules / Feeders Oddity

[RJ!]

Hi All,

I've been trying to build a Cube to store all of our Headcount data so that our analysts can review the movements of staff within Cost Centres & businesses.

As seems to be typical, I had it all working sort of OK, all my rules were working OK (from what I could tell) but a screw up in the Data load mean somethings weren't loaded so silly me deleated the Cube and started again. Though this time something isn't quite right.
ISSUE #1: I was using the "UNDEFVALS" in my Rules development so that I can see what cells were being fed from my rules, in my new Cube, I don't see any "White" cells that indicate no data is being fed to the cell. Have I not written my Feeders / Rules properly?

Below is the Rules for the Cube:

Code: Select all

# 221012 - Rules to Feed the Account Structure

SKIPCHECK;


UNDEFVALS ;


# New
['950191BA'] = N:	If( [Account:'Direct Staff (DS) (L1) (940198PA)',MeasureType: 'Balance'] = 1 & 

		DB('Biz_Staff',!Version,!Staff,!Product,'Total RCs','Direct Staff (DS) (L1) (940198PA)',attrs('Period', !Period, 'Prior_Mth'),'Balance') = 0
		, DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'Direct Staff (DS) (L1) (940198PA)',!Period,'Balance') , STET ) ;	

# Attrition
['950192BA'] = N: 

# Check to see if the RC doesn't have a valid Local Code for the current month in Biz_Staff_Map2:

		If (DB('Biz_Staff_Map2',!Period,!Staff,'Local Org Code') @<> ''
		, STET , 

# Check Current Staff Count <> Last month Count & prior Month/RC combo are =1 & Next months Month/RC combo are <> 1

			If (DB('Biz_Staff_Map',!Period,!ResponsibilityCentre,!Staff,'Direct Staff Count') <> 1 & DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!ResponsibilityCentre,!Staff,'Direct Staff Count') = 1
			& DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),DB('Biz_Staff_Map',attrs('Period', !Period, 'Prior_Mth'),!ResponsibilityCentre,!Staff,'Local Org Code'),!Staff,'Direct Staff Count') = 1
			& DB('Biz_Staff_Map',attrs('Period', !Period, 'Next_Mth'),!ResponsibilityCentre,!Staff,'Direct Staff Count') <> 1
			, DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'Direct Staff (DS) (L1) (940198PA)',attrs('Period', !Period, 'Prior_Mth'),'Balance')*-1 , STET )	
		) ;
				

		
#Transfer In
['950193BA'] = N:	

# Balance Exists in TOTAL RC's in Last Period:
		If( DB('Biz_Staff',!Version,!Staff,!Product,'Total RCs','Direct Staff (DS) (L1) (940198PA)',attrs('Period', !Period, 'Prior_Mth'),'Balance') <> 0
# Balance Exists in this period:
		& [ Account: 'Direct Staff (DS) (L1) (940198PA)', MeasureType: 'Balance' ] <> 0
# RC in Last Period is not the same as current Period:
		&  DB('Biz_Staff_Map2',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Local Org Code') @<> DB('Biz_Staff_Map2',!Period,!Staff,'Local Org Code'),
# If True
		 [ Account: 'Direct Staff (DS) (L1) (940198PA)', MeasureType: 'Balance' ]
		, STET
		) ;
		



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

# Check to see if the RC does have a valid Local Code for the current month in Biz_Staff_Map2:

		If (DB('Biz_Staff_Map2',!Period,!Staff,'Local Org Code') @= ''

# Check last period RC is not equal to current month RC:

		& DB('Biz_Staff_Map2',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Local Org Code') @<> DB('Biz_Staff_Map2',!Period,!Staff,'Local Org Code')

# Check Current Month DS balance is not equal to 1:

		&  DB('Biz_Staff',!Version,!Staff,!Product,DB('Biz_Staff_Map2',attrs('Period', !Period, 'Prior_Mth'),!Staff,'Local Org Code'),'Direct Staff (DS) (L1) (940198PA)',!Period,'Balance') <> 1, 
		-1, STET
		) ;	

# BOP
['950190BA'] = N: DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'Direct Staff (DS) (L1) (940198PA)',attrs('Period', !Period, 'Prior_Mth')	,'Balance') ;

# EOP
#['950195BA'] = N: DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'Direct Staff (DS) (L1) (940198PA)',!Period,'Balance') ;

			
#----------------------------------------

FEEDERS;

['940198PA'] => DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950190BA',attrs('Period', !Period, 'Prior_Mth'),'Balance') ;
['940198PA'] => DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950191BA',attrs('Period', !Period, 'Prior_Mth'),'Balance') ;
['940198PA'] => DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950192BA',attrs('Period', !Period, 'Prior_Mth'),'Balance') ;
['940198PA'] => DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950193BA',attrs('Period', !Period, 'Prior_Mth'),'Balance') ;
['940198PA'] => DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950194BA',attrs('Period', !Period, 'Prior_Mth'),'Balance') ;
#['940198PA'] => DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950195BA',attrs('Period', !Period, 'Prior_Mth'),'Balance') ;


#['950191BA'] ;

ISSUE #2: My Rule for the BOP doesn't seem to be populating in the Cube, even though when looking at the Trace Calculations, it is calculating fine?
The BOP Rule is picking up the prior Months balance for the Direct Staff Account, though at a consolidated level, it does not seem to be doing so?







This is how it's displayed:


Anyone have any suggestions on what is going on?

Thanks,

RJ

[Duncan P]

Two things.

Firstly if you are using UNDEFVALS you should make sure you have read up on what the characteristics are. Searching the forum will give a good idea. Doing intelligent experimentation is best though.

Secondly there is a problem with your feeders ...

Code: Select all

FEEDERS;

['940198PA'] => DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950190BA',attrs('Period', !Period, 'Prior_Mth'),'Balance') ;

What this is saying is that a value against the '940198PA' account is being used in the calculation for '950190BA' for the previous period. That is that the calculation refers to the next period.

But ...

Code: Select all

# BOP
['950190BA'] = N: DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'Direct Staff (DS) (L1) (940198PA)',attrs('Period', !Period, 'Prior_Mth')   ,'Balance') ;

... which refers to the previous period.

Calculation offsets are relative to the target. Feeder offsets are relative to the source. What you need to do is to create a month attribute 'Next_Mth' and use that in the feeder, e.g.

Code: Select all

['940198PA'] => DB('Biz_Staff',!Version,!Staff,!Product,!ResponsibilityCentre,'950190BA',attrs('Period', !Period, 'Next_Mth'),'Balance') ;

The other thing is that it's not clear whether your 'Biz_Staff' cube has any other measure than 'Balance'. If it does then you are populating all of the measures with the same DB statement in the calculation section, but only feeding 'Balance' in the feeders section.

[RJ!]

Thanks Mate!!

Cube now calculates perfectly

[Steve Rowe]

One small thing, it looks like you are using alias references in your rules.
It's really dangerous, one change to an alias and your whole rule sheet dies.

Cheers

[RJ!]

Alias like this: "Direct Staff (DS) (L1) (940198PA)"?

I have cleaned them out as well once I relaised I was doing that