Rule Efficiency

[MarenC]

Hi,

I came across the following rule and I felt that it was inefficient but just wanted to check here in case I missed something.

The rule is as follows:

Code: Select all

[ 'Costing %'] =N:
IF( [ 'Forecast Flag' ] = 1 &
  DB('Appointment data',Version,Year,Cost Centre,Employee,Position,'Costing %') <> 0,
	DB('Appointment data',Version,Year,Cost Centre,Employee,Position,'Costing %'),
	STET
  );

and the feeder for this is:
[ 'Forecast Flag' ]=>[ 'Costing %'];

I don't get why the DB is needed in the above rule, so I was thinking this would be better:

Code: Select all

[ 'Costing %'] =N:
IF( [ 'Forecast Flag' ] = 1,
	DB('Appointment data',Version,Year,Cost Centre,Employee,Position,'Costing %'),
	STET
  );

Then I remembered that IF statements should be avoided if possible, so I was thinking this was even better:

Code: Select all

[ 'Costing %'] =N:
[ 'Forecast Flag' ] *
DB('Appointment data',Version,Year,Cost Centre,Employee,Position,'Costing %');

Can anyone confirm that the above is correct?

Maren

[declanr]

Hi,
I don't get why the DB is needed in the above rule, so I was thinking this would be better:

Technically it terms of performance DB performs slightly better than shorthand; you are unlikely to notice any difference from changing just 1 rule but it could add up if its every rule in a rule heavy system.
That being said it's a trade off... the shorthand does tend to make it easier to read. Which IMO is extremely important; especially in systems where there are multiple developers.

You can go for an in-between one by using shorthand but pre-fixing the element name with the dim name / hierarchy. Which again theoretically is slightly better performance than just the element name in shorthand as it means the system doesn't need to search the dims to find out where the element actually is.

In reality though - the performance difference will be incredibly negligible.

Then I remembered that IF statements should be avoided if possible, so I was thinking this was even better:

If statements should be avoided if they really aren't needed.

BUT in the case you have shown it looks to be making it so that the Rule is only active for forecast periods; and makes the cell editable for historic periods.
IF you also show data for history; generally keeping the IF and STET would be better as it means you can load the Actuals as real data instead of rule generated data.

Generally speaking for actuals (which don't change via user input) you are better off having everything generate by TI instead of rules... and remembering to apply security so that users can't change it!

[MarenC]

Hi Declan,

Thank you for the feedback.

My apologies and I should have made this clear, the DB is to another cube, so that is required.

What I meant is, does the rule need to to have the IF for the DB, does it really need to ask whether the value is 0? Rather wouldn't it be better to remove the if for that and have this instead:

Code: Select all

[ 'Costing %'] =N:
IF( [ 'Forecast Flag' ] = 1,
	DB('Appointment data',Version,Year,Cost Centre,Employee,Position,'Costing %'),
	STET
  );

[declanr]

It does look redundant.
The only reason I can think of to write the rule that way would be if you wanted to keep the cell editable so users can add a value in this cube for the non rule-derived cases.
E.g. if a % has been provided in the master cube we will use that one; otherwise users should/can add a value manually.

[lotsaram]

I think the answer depends if the STET is actually needed. As in; is the purpose of the STET to make the value 0, or to open the cell to data entry? If there are conditions where data entry is required then you need STET, otherwise I would avoid the IF and write like you did as
[ 'Forecast Flag' ] * DB('Appointment data',Version,Year,Cost Centre,Employee,Position,'Costing %')
As long as 'Forecast Flag' is a 1 or 0 Boolean then this is the way to go.

Also if you're taking efficiency then I would seriously question whether 'Costing %' needs to be fed. IMO it more than likely doesn't. It's a percentage which would indicate it is used as an intermediate driver in another calculation. Remember feeders exist in order to be able to consolidate so if there's no purpose in consolidating 'Costing %' then there's also no point in feeding it. The calculation which uses 'Costing %' will calculate just fine without 'Costing %' being fed. Whatever that downstream calculation is, that's what actually needs the feeder.

[MarenC]

Hi,

Thank you for the responses, very insightful.

I can say for certain the STET isn't required in this cube.

The reason the Costing % might be fed is so it can be copied in TI processes, my understanding is that if the cell is not fed then it will not show up in a TI data source, unless zero cells are skipped in the TI?

Maren

[qml]

Technically it terms of performance DB performs slightly better than shorthand;

Are you sure? I have always worked under the understanding (confirmed with some testing a while back) that it's the opposite: the short-form/bracket notation is faster than a full DB function, and measurably so (although the difference is more pronounced with a larger number of dimensions).

[gtonkin]

I was pondering Declan's statement too and went back to Cubewise's 7 tips for writing faster rules, point 6.

I have not had any cause to retest or dispute their 40% faster claim as I have seen similar results over the years.

Are there edge cases we should know about or has something changed e.g. hierarchies etc?

[declanr]

Interesting I will cede to standing knowledge if the DB is usually significantly slower.

I was basing it on a fairly small test case I had done (all in one model) where there are a lot of hierarchies with same named elements (between hierarchies and dimensions unfortunately) - so it probably skewed the results.
Normally I would stick to shorthand (with hierarchy referenced) because I find it the most readable and that is what I find most important the majority of the time. I just have one model I work on where I was working to eke out every last drop of performance (because its massive) and the testing was a case of try everything in steps to find what worked best there.