Feed based on Selected element from Picklist cube

[bunchukokoy]

Good day!

Hi Guys,

May I just ask regarding feeders.

I have cubes with almost the same structure as of cubes below:


A. REPORT Cube

-Version
-Month
-Report Account (consists Expenses element)
-Measure (consists December Closing element)


B. SOURCE Cube

-Version
-Year (consists ten initial year elements)
-Month
-Source Account (consists 1111 and 2222 accounts as Expenses accounts)
-Measure (consists Actual element)


C. PICKLIST Cube

-Year Selection (consists Year element)
-Measure (consists Select element)




In the REPORT cube, the December Closing measure pertains to the Year selected in PICKLIST cube minus one.
So in my rules for REPORT cube to get the December Closing Expenses for Year 2012, rules should be



['Expenses','December Closing']=N:
DB('SOURCE Cube',!Version,DB('PICKLIST Cube','Year','Select'),'Dec','1111','Actual')+
DB('SOURCE Cube',!Version,DB('PICKLIST Cube','Year','Select'),'Dec','2222','Actual');



My question is, how will I feed ['Expenses','December Closing'] in a way that is not overfed?
What I put in the Feeders in SOURCE Cube rule file is this


Feeders;

['1111','Actual']=>DB('REPORT Cube',!Version,!Month,'Expenses','December Closing');
['2222','Actual']=>DB('REPORT Cube',!Version,!Month,'Expenses','December Closing');


Isn't it gonna over feed, I'm thinking all the year elements will feed ['Expenses','December Closing']?
Is there a way in which my feeders will just identify what Year will feed the ['Expenses','December Closing'] based on what is selected in the PICKLIST cube?


Please help.

Thanks,


Bunch

[bunchukokoy]

Sorry, just a correction.

['Expenses','December Closing']=N:
DB('SOURCE Cube',!Version,DB('PICKLIST Cube','Year','Select'),'Dec','1111','Actual')+
DB('SOURCE Cube',!Version,DB('PICKLIST Cube','Year','Select'),'Dec','2222','Actual');

should be

['Expenses','December Closing']=N:
DB('SOURCE Cube',!Version,attrs('Year',DB('PICKLIST Cube','Year','Select'),'Prev'),'Dec','1111','Actual')+
DB('SOURCE Cube',!Version,attrs('Year',DB('PICKLIST Cube','Year','Select'),'Prev'),'Dec','2222','Actual');

Thanks.

[rozef]

Hi bunchukokoy,

to avoid overfeeding, I see no other choice to make conditionnal feeders, but it can take a lot of time to compile so sometimes it can be better to overfeed.

['1111','Actual']=>DB( IF( !Year @= attrs('Year', DB('PICKLIST Cube','Year','Select') , 'Next'), 'REPORT Cube' , 'nofeed'),
!Version,!Month,'Expenses','December Closing');

You will also have to put in .cfg file ReevaluateConditionalFeeders = T

[bunchukokoy]

Ok Sir. Is 'nofeed' string a reserved one? Or just a variable value to purposely not feed if when condition's false?

Thanks.

[rozef]

The second one, you can put here anything else then a cube name.

You're welcome.

[bunchukokoy]

Many Thanks.

[Gregor Koch]

Hi
I guess my post comes too late and might be off the mark anyway, but...

From what I understand, what you are doing in you reporting cube is
A. Consolidate the numbers of the source accounts to a higher level
and
B. and at least for the calc that you show you are only ever using a specific year as you have left out the year dimension in the reporting cube
and
C. calculating [December Closing] for every month in your reporting cube.

Why???

Even if you really need [Expenses] to be N level in your reporting cube, you could still create a consolidation in your Source Account dimension that is called [Expenses] with the required elements (e.g. 1111 and 2222), please note that consolidations will perform better that several '+'s in a rule.
The 'missing' year dimension in the reporting cube...I am really curious what advantage that would give you. Please do let me know.
From my point of view you don't even need a rule but just one cube with consolidations and proper Version, Measure approach, YTD and Opening Balance etc.

As for the given solution from rozef. For a start shouldn't it read

['1111','Actual']=>DB( IF( !Year @= attrs('Year',DB('PICKLIST Cube','Year','Select'),'Prev'), 'REPORT Cube' , ''),!Version,!Month,'Expenses','December Closing')

because you only want to feed if you have data in that year (which is what you are showing in your reporting cube) and don't actually have a year in your reporting cube at all.
And on the overfeeding:
The way the feeder above is written you kind of are feeding from every year and month (more later) and then check whether you really need to feed from that year. If you do, the flag/feeder is set if you don't you shoot a blank. Btw, most people leave the cube name as '' if the condition is not true.
Now, you will only feed less if all the source accounts that are used in a total, say Expenses, have no balances in one year vs the other year - I guess for Expenses the chances are pretty low but obviously your example is a simplification. So I am not sure whether this is the case but I doubt very much that you will gain much performance improvement (less feeders) and even if you do set ReevaluateConditionalFeeder=T the feeder above won't re fire and you will have to compile your rule or use CubeProcessFeeders TI function after you change the year and in that case the time it takes to evaluate the condition would have a negative effect.

If you do choose to create a [Expenses] element in your Source Account dimension and you don't have big swings in how many source accounts actually have data between years you might as well write it like this.

['Expenses','Actual', 'Dec']=>DB('REPORT Cube',!Version,'The month you want to feed','Expenses','December Closing');

and wouldn't have to worry about writing a feeder for every source account. If you need to feed all months, the original Feeder will only be correct for month December anyway (!), then use a consolidation of all months where I wrote 'The month you want to feed'.

Cheers