Rules / Feeders Oddity
Posted: Thu Nov 29, 2012 6:48 am
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:
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
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'] ;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
