descrepany with the rule calculation

[ryan]

I have two cubes with different cardinality/granularity.

1)OH_Budget Load

• OH_Version
  OH_Company
  OH_Business Unit
  OH_Category
  OH_Currency
  OH_Period
  OH_TB Measure

2) OH_Trial Balance

• OH_Version
  OH_Company
  OH_Business Unit
  OH_Category
  OH_Nature
  OH_Account
  OH_Currency
  OH_Period
  OH_TB Measure

Note:

• 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'];

Rules @ OH_Budget Load

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');

Sample data:
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

After spreading the values from category to Nature+Account level.

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

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

• OH_Company
  OH_Business Unit
  OH_Category
  OH_Nature
  OH_Account
  OH_TB Measure

Enhanced 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);

[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'];

Feeders @ OH_Report Header cube

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.

[Steve Rowe]

Hi ryan,
You've posted loads of detail which is great but I think it might help the forum visualise what you are asking if you post some screen shots of cubes showing what is and isn't working.

It can be quite hard to give an answer just from the rules alone when the problem is pretty complex.

Cheers