descrepany with the rule calculation
Posted: Thu Aug 02, 2012 5:09 am
I have two cubes with different cardinality/granularity.
1)OH_Budget Load
Budget data (which is category level) has to spread to the OH_Nature and OH_Accounts level based on the available mix (mix is calculated Actuals of Nature and Account level). Budget Category level values are spreading to the Nature and Account level if there is a mix. In case of mix absence, the values are not spreading to that nature and account for that category, budget values are available for that category in budget cube though.
Rules @ OH_Trial Balance
Rules @ OH_Budget Load
Sample data:
1)OH_Budget Load
After spreading the values from category to Nature+Account level.
2) OH_Trial Balance
Budget-Final 260 AUD D-OCPM IT External Cost O1211 OFEGA22-E002 201206 Value is missing.
Since the above strategy is not working. I took an interim cube where it store the flag for each and every Nature+Account for a particular category.
3) OH_Report Header
Feeders @ OH_Report Header cube
Still I couldnt find any difference in the rest for OH_Trial balance cube. I appreciate if you could guide me to write a better feeder.
Thanks in advance.
1)OH_Budget Load
- OH_Version
OH_Company
OH_Business Unit
OH_Category
OH_Currency
OH_Period
OH_TB Measure
- OH_Version
OH_Company
OH_Business Unit
OH_Category
OH_Nature
OH_Account
OH_Currency
OH_Period
OH_TB Measure
- OH_Nature + OH_Account roll-up to OH_Category.
We have Budget data @ Category level.
All elements roll-up to All Natures in OH_Nature Dimension
All elements roll-up to All Accounts in OH_Account Dimension
Budget data (which is category level) has to spread to the OH_Nature and OH_Accounts level based on the available mix (mix is calculated Actuals of Nature and Account level). Budget Category level values are spreading to the Nature and Account level if there is a mix. In case of mix absence, the values are not spreading to that nature and account for that category, budget values are available for that category in budget cube though.
Rules @ OH_Trial Balance
Code: Select all
SKIPCHECK;
['Actual','Bal Mix']=N:
IF(DB('OH_Version Control',!OH_Period,'PeriodStatus')=100 & Subst(!OH_Period,1,4)@='2012',
['Actual','2012','Balance']\['Actual','All Accounts','All Natures','2012','Balance'],
Continue);
['Actual','Bal Mix']=N:
IF(DB('OH_Version Control',!OH_Period,'PeriodStatus')>=200 & Subst(!OH_Period,1,4)@='2012',
DB('OH_Trial Balance','Actual',!OH_Company,!OH_Currency,!OH_Business Unit,!OH_Category,!OH_Nature,!OH_Account,ATTRS('OH_Period',!OH_Period,'PrevPeriod1'),'Bal Mix'),
Continue);
['Budget-Final','Balance']=N:
IF(Subst(!OH_Period,1,4)@='2012' &
['Actual','Bal Mix']=0 & DB('OH_Budget Load','Budget-Final',!OH_Company,!OH_Currency,!OH_Business Unit,!OH_Category,!OH_Period,'Balance')<>0,
DB('OH_Budget Load','Budget-Final',!OH_Company,!OH_Currency,!OH_Business Unit,!OH_Category,!OH_Period,'Balance'),
Continue);
['Budget-Final','Balance']=N:
IF(Subst(!OH_Period,1,4)@='2012',
['Actual','Bal Mix']* DB('OH_Budget Load','Budget-Final',!OH_Company,!OH_Currency,!OH_Business Unit,!OH_Category,!OH_Period,'Balance'),
Continue);
FEEDERS;
['Actual','Balance']=>['Actual','Bal Mix'];
['Actual','Bal Mix']=>
DB('OH_Trial Balance','Actual',!OH_Company,!OH_Currency,!OH_Business Unit,!OH_Category,!OH_Nature,!OH_Account,ATTRS('OH_Period',!OH_Period,'NextPeriod1'),'Bal Mix');
['Actual','Bal Mix']=>['Budget-Final','Balance'];
Code: Select all
SKIPCHECK;
FEEDERS;
['Budget-Final','Balance']=>
DB('OH_Trial Balance','Budget-Final',!OH_Company,!OH_Currency,!OH_Business Unit,!OH_Category,'All Natures','All Accounts',!OH_Period,'Balance');
1)OH_Budget Load
Code: Select all
Version Company BU Category Period Currency Value
Budget-Final 260 D-OCPM IT External Cost 201206 AUD 1000
Budget-Final 260 D-ODSI IT External Cost 201206 AUD 53000
2) OH_Trial Balance
Code: Select all
Version Company Currency BU Category Nature Account Period Balance
Budget-Final 260 AUD D-ODSI IT External Cost O1211 OFEGA22-E002 201206 53000
Since the above strategy is not working. I took an interim cube where it store the flag for each and every Nature+Account for a particular category.
3) OH_Report Header
- OH_Company
OH_Business Unit
OH_Category
OH_Nature
OH_Account
OH_TB Measure
Code: Select all
SKIPCHECK;
['Actual','Bal Mix']=N:
IF(DB('OH_Version Control',!OH_Period,'PeriodStatus')=100 & Subst(!OH_Period,1,4)@='2012',
['Actual','2012','Balance']\['Actual','All Accounts','All Natures','2012','Balance'],
Continue);
['Actual','Bal Mix']=N:
IF(DB('OH_Version Control',!OH_Period,'PeriodStatus')>=200 & Subst(!OH_Period,1,4)@='2012',
DB('OH_Trial Balance','Actual',!OH_Company,!OH_Currency,!OH_Business Unit,!OH_Category,!OH_Nature,!OH_Account,ATTRS('OH_Period',!OH_Period,'PrevPeriod1'),'Bal Mix'),
Continue);
[color=#BF0000]'Budget-Final','Balance']=N:
IF(Subst(!OH_Period,1,4)@='2012' &
['Actual','Bal Mix']=0 & DB('OH_Budget Load','Budget-Final',!OH_Company,!OH_Currency,!OH_Business Unit,!OH_Category,!OH_Period,'Balance')<>0,
(1\DB('OH_Report Header',!OH_Company,!OH_Business Unit,!OH_Category,'All Natures','All Accounts',!OH_Account,'Flag'))*
DB('OH_Budget Load','Budget-Final',!OH_Company,!OH_Currency,!OH_Business Unit,!OH_Category,!OH_Period,'Balance'),
Continue);
[/color]
['Budget-Final','Balance']=N:
IF(Subst(!OH_Period,1,4)@='2012',
['Actual','Bal Mix']* DB('OH_Budget Load','Budget-Final',!OH_Company,!OH_Currency,!OH_Business Unit,!OH_Category,!OH_Period,'Balance'),
Continue);
FEEDERS;
['Actual','Balance']=>['Actual','Bal Mix'];
['Actual','Bal Mix']=>
DB('OH_Trial Balance','Actual',!OH_Company,!OH_Currency,!OH_Business Unit,!OH_Category,!OH_Nature,!OH_Account,ATTRS('OH_Period',!OH_Period,'NextPeriod1'),'Bal Mix');
['Actual','Bal Mix']=>['Budget-Final','Balance'];
Code: Select all
FEEDERS;
['Flag']=>
DB('OH_Trial Balance','Budget-Final',!OH_Company,'All Currencies',!OH_Business Unit,!OH_Category,!OH_Nature,!OH_Account,'201206','Balance');Still I couldnt find any difference in the rest for OH_Trial balance cube. I appreciate if you could guide me to write a better feeder.
Thanks in advance.