Refer-to + rules?

Unknown

Hi all,

 

is it possible to use the refer-to in the layout and have the cube take the value of the rule that is used in combination?

Say I have a cube a, with a rule "measures" (in functions) and also a "refer-to" to a value that is computed in the rule. In my case the result is empty. How'd you do that?

 

BR, Ray

Unknown

Hi Ray,

 

you would have to actually "write" your rule into the cube with a dataflow, or a rule write-back - then you can use the refer to that will actually pick up the value that is now stored into the cube.

Since a rule is a calculation on-the-fly, the refer-to won't pick up any value as it only picks stored values.

 

Simple example just built on the fly - i have a simple PL cube with a measure entity and a simple calc done via rule.

Net revenues = Gross Revenues - Discount (calculated via a simple rule)

I have a dataview that needs to display the Net Revenues by month. I can build it in 2 ways:

• rewrite the calculation as a column algorithm (using refer-to blocks to gross revenues and discount and doing an a-b) algorithm
• store the calculated value of net revenues in the PL cube itself.

 

If i do option 2, i would then create a simple dataflow that writes the cube with the rule applied into itself

 

 

If i run the calculation, and in my Net Revenue by Month block i have the PL cube with refer to on Net Revenues - this is what i obtain

 

 

The PL cube has now stored the value of Net Revenues - so i can use it in the refer to.

 

Hope this helps

Michele

Previous Member

Thanks Michele Roscelli, this helps a lot. I had hoped to not have to keep the computed values in the cube in order to save space.

Unknown

Hi Ray,

 

You can still do it without using a refer to if you want. A typical way of doing it is with Nexel and Data-Pickers if you do not want to have to add the different cubes and rewrite the column algorithm.

In the previous case, what you would do is:

 

• Create a new Nexel Formula block in a dataview 
• Add a Datapicker Formula to do: @[V001]{Measure:"Gross Revenues"} - @[V001]{Measure: "Discount"} in that block (need to check syntax as i'm writing on the fly) 
• Nexel help article here: Nexel - Help

 

Consider that writing the result of a rule has usually a good impact on performance as you have do not run on-the fly calculations which are usually (depends on the model) more expensive than a direct data-retrieval from query.

A good strategy is to write all the values that are sum-based and not percentages / ratios, as they are aggregated directly in BOARD queries therefore return always the correct result. You can then just keep the rule to have extra "metrics" (ratios, percentages, etc) only.

 

TLDR: If you need to refer-to a calculated metric, then you'll need to work it via column algorithm or Nexel formula. Otherwise storing it is usually a good solution

 

Michele

Previous Member

Hi Michele Roscelli,

 

thanks again for sharing you tip! So I tried using it with the data picker in a Nexel formula. Unfortunately it is prohibitively slow. It seems as if the selected cube version is the most detailed one, but there is no need to go down on that level.

And as suggested, storing is useful, also for drill downs as those only work with stored values, not with computations.

 

BR, Ray

Unknown

You are absolutely right - forgot to mention drill downs, another plus of the stored calcs.

 

The datapicker executes a "layout in the layout" - so performance is impacted depending on the size and complexity of the dataset, and on the number of datapicker calls executed.

 

When executing a nexel, BOARD first executes the layout from the object, and then the formulas stored in the nexel are applied on top of the results of the original layout query. This is fantastic when creating complex calculation models but quite expensive for "simple calcs" - as always, the balance is in the middle

 

I'll take the opportunity to wish you and everyone reading this post a merry Christmas!

 

Michele