Feed when rule includes if statement

Post Reply
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Feed when rule includes if statement

Post by Mark RMBC »

Hi all,

I have a technical question regarding feeders.

I have the following situation and want some advice as to the best way to feed.

I have the following example of a rule used in a relatively small cube designed to calculate Employers NI rates:

Code: Select all

['st_to_pt']  =
	DB( If( ['Annual_Salary'] = 0, '',
	'lkup_NIRates'),attrs('F_Employee',!F_Employee,'NIRATE'),'ST_to_PT',!C_Year,!B_Versions,'BandWidth');
So basically this says if the Annual_Salary element is 0 then ignore otherwise return a value from another cube, a cube which holds NI rates..

My question is am I better to use Annual_Salary as the feeder or am I better to put the feeder in the other cube and feed from there?

My current thinking is that I should use Annual_Salary, which seems to be working perfectly fine but just wanted some best practice advice.

cheers, Mark
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Feed when rule includes if statement

Post by ascheevel »

I would use Annual_Salary as your feeder like you're planning. Annual_Salary is the dependent cell, so using it as a feeder source will ensure that only employees with an annual salary will have this measure fed. If you feed from your lookup cube, you'll have to feed all employees. I don't know your cube dimensionality, but it's possible you'd need to feed all time periods and numerous versions from the one rate as well causing more cells than necessary to be fed. My preference is to use internal feeders before cross cube whenever possible. With an internal feeder, the chances are more likely that the feeder source and target will be in the same grain which will save you from having to point a feeder at something like "TOTAL EMPLOYEES".
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Feed when rule includes if statement

Post by Mark RMBC »

thanks for the response ascheevel.

Yes that was broadly my thinking and yes the alternative does include using Total_Employees. For some reason I always think feeders should be done from where the actual value is derived, in that x*y = z should use either x or y to feed z, but I always have doubt when the feed is coming from something other than x or y!

cheers, Mark
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: Feed when rule includes if statement

Post by ascheevel »

The feed from cell doesn't need to be a cell referenced in the rule, it can be a cell that will not be zero when you'd expect the rule calced cell to evaluate to something other than zero. A little doubt is good, it helps you make absolutely certain the alternative cell you want to feed from will work. People describe feeders in different ways and someone will likely tell me my interpretation is off, but here goes: a feeder is just a flag for TM1 that says, "hey, when this cell is something other than zero/blank, this other cell will evaluate to something you shouldn't ignore."

Here is an oversimplified example for illustrative purposes: if you have a P&L measure Gross Margin % with rule (Net Sales - COGS) \ Net Sales and you wouldn't expect to have Net Sales without Sales Lbs, you could feed the Gross Margin % measure from Sales Lbs instead of Net Sales or COGS. This is beneficial if say Net Sales and COGS are both consolidations of multiple measures. Feeding from those consolidations would result in every cell within those consolidations with a value feeding Gross Margin %. I realize you could make Gross Margin % a consolidation of Net Sales and COGS, write a C: rule, and skip the feeder entirely, but then I wouldn't have an example anymore :D
Post Reply