What's wrong with my rule? :(

[fleaster]

Hi all,
Am having a bit of a headache trying to get this rule to work, so if anyone is able to shed any light on where I'm going wrong it would be much appreciated

My target cube is in the following format:

Code: Select all

DB('Fin_GL',!Version,!SectionA,!SectionB,!SectionC,!Company,!Affiliate,!FunctionalCell,!Product,!ProductProcessor,
   !ResponsibilityCentre,!Account,!BalanceType,!Period,!Measures)

The section of rules I am having issues with is below - in a nutshell it should just be [Calculation] = [Proportion] * [Signage], but is a little more complicated than that because of all the dimension variables:

Code: Select all

[Version: 'APRA Calc' , Measures: 'Calculation'] =

      DB('Fin_GL','APRA Calc',!SectionA,!SectionB,!SectionC,'2400','NA','No Function','No Product',!ProductProcessor,
      'No ResponsibilityCentre','No Account','EOP',!Period,'Proportion')
      *
      DB('Fin_GL','APRA Calc','NA','NA','NA',!Company,!Affiliate, 'No Function','No Product',!ProductProcessor,
      'No ResponsibilityCentre',!Account,!BalanceType,!Period,'Signage')
;

FEEDERS;
['APRA Calc' , Measures: {'Proportion','Signage'}] => ['APRA Calc' , Measures: 'Calculation'];

Now when I change the "*" to a "+", i can return results (altho obviously not aligned the way I want). So am trying to decipher where it has gone wrong:

1/ rule is not being fed correctly?

2/ !Dimension variables not specified correctly?

3/ other...?

...anyone have any ideas?

Thanks,

Matt

[Catherine]

I would say your rule is not fed correctly.

1. For your information, you should build a view, go to a leaf level on all dimensions to validate if your rule works properly.
Then, if it works, choose a consolidation on any dimension. If you obtain 0 or a wrong result, then it means that you have not written the feeders correctly.

2. For a rule which is a mulitplication, you need to feed with only one of the elements of the multiplication, the one which has the lowest number of values (the rule is not so simple, but difficult to explain in a few words).
In your case, it's difficult to say without knowing functional context.

3. If Signage is the most appropriate to feed Calculation, then the feeder would look like:

Code: Select all

['APRA Calc' , Measures:'Signage'] => ['APRA Calc' , 'Total SectionA' , 'Total SectionB' , 'TotalSectionC' , 'Total Functions' , 'Total Products' , 'total Responsibility Centres' , Measures: 'Calculation'];

[fleaster]

Thanks Catherine for the tips... after going to zero level on all dimensions, I still obtain zeroes... so does this mean it is an issue with the rule itself rather than the feeders? possibly a problem with the area definition...?

Thanks!

Matt

[Catherine]

Thanks Catherine for the tips... after going to zero level on all dimensions, I still obtain zeroes...

Are you sure that there were some data on the corresponding Proportion and Signage ? Because your rule is a little bit complex regarding the crossings where you have your data.
I would suggest that you build 3 views:
-One where you are on level 0 on all dimensions, and where you would expect a result (but you have 0)
-The second one with the corresponding Proportion
-The third one with the corresponding Signage
Then attach a printscreen of your 3 views in this post. Maybe it will be be easier for us to find out what is wrong...

[asutcliffe]

Thanks Catherine for the tips... after going to zero level on all dimensions, I still obtain zeroes... so does this mean it is an issue with the rule itself rather than the feeders? possibly a problem with the area definition...?

Thanks!

Matt

Have you tried using "Trace Calculation" and "Check Feeders"? The first will show you how a value is calculated for a cell. The second will tell you if it's fed or not.

[Steve Rowe]

Using Trace calculation and feeders at the N level is excellent advice...

Other things that are not quite right.

1. Qualify the rule with a N, you don't want this allocation operating at the C level in the cube.
2. It appears all your rules are internal to the cube and since there are no attribute references and so on in the rule then you can use the simple square bracket reference. This will make your rule easier to read and easier to write the feeder for.
3. The feeders are wrong.

Code: Select all

[Version: 'APRA Calc' , Measures: 'Calculation'] =N:

      ['APRA Calc' ,'2400',dimname:'NA','No Function','No Product' , 'No ResponsibilityCentre','No Account','EOP','Proportion']
      *
      ['APRA Calc',dimname:'NA',dimname:'NA',dimname:'NA', 'No Function','No Product' ,'No ResponsibilityCentre','Signage']

Your feeder needs to have one of Proportion and signage on the LHS and then on the RHS for every hard coded reference on the left you need to provide one on the right for the range over which you want the calculation to apply. Often this will be the top element for that dimension but be very careful of over feeding.

['APRA Calc' ,'2400',dimname:'NA','No Function','No Product' , 'No ResponsibilityCentre','No Account','EOP','Proportion']=>[Version: 'APRA Calc' ,'Something different to to 2400', 'Total NA', 'Total Function', 'Total Product' , Total Resp Centre' , etc, etc, Measures: 'Calculation'] ;

Like I a said though be very careful with the above since you can easily overfed, but I can't give better advice without a lot more knowledge. Your design seems a little off as well, rather than have Proportion and Signage in your main cube with lots of redundant dimensions you are probably better off having them in their own cubes.

HTH

Cheers,

[fleaster]

Hi all, thanks so much for your help - is very much appreciated.

To explain the design - the end goal is to have [APRA Calc] = [Proportion] x [Signage] x [Balance]
[Proportion] - is % fed via rules from an external cube
[Signage] - is Account mappings fed via rules from an external cube
[Balance] - are $ in the main Fin_GL cube

...the aim being to bring everything back together into the 1 Fin_GL cube.

Ideally I would like to write everything as 1 big rule, but decided to split it up and try [Proportion], [Signage] and [Proportion]x[Signage] to start off with. So far the first 2 work and are returning values, but the last multiplication calc is not working for some reason.

As attached, I managed to find some calculated values via zero level elements, so it looks like the rule calculation is working, however it is not being fed correctly (also confirmed by Check Feeders)

TM1rule_ML.JPG (216.93 KiB) Viewed 7247 times

[Catherine]

The rule works fine: great !

Could you show us the feeder that is currently in the rules ?
I don't know if you are feeding your calculation from Proportion or Signage. But as this value (Proportion or Signage) comes from an external cube, it has to be fed itself correctly also...

By the way, is there an interest to first have a rule getting Proportion from another cube ?
Couldn't the Calculation rule read Proportion directly from the external cube (same question for Signage) ?

[David Usherwood]

Sounds as if Balance should feed APRA Calc and the other bits don't need feeding at all - remember:
Rates don't need feeding

[fleaster]

Hi all,
thanks again for the tips, am finally getting somewhere ...have taken the advice and combined everything into 1 rule formula
ie [APRA Actual] = [Proportion] x [Signage] x [Balance]
(where Proportion and Signage come from external cubes and Balance is internal)

...this seems to be returning a result when zeroes are unsuppressed, so looks like am having an issue with the feeders again - i suspect because the !Account is attribute calculated the feeder may need adjusting, or the rule rewritten in a different way...

Matt

Code: Select all

['APRA Actual',Measures:'LCYE'] = N:
   DB('Biz_Client','APRA Actual',!SectionA,!SectionB,!SectionC,'No Product',!ProductProcessor,'No ResponsibilityCentre','Total Client',!Period,'Proportion')
   *
   DB('Fin_Mapping','APRA GLMAP','NA','NA',!Company,!Affiliate,!ProductProcessor,'No ResponsibilityCentre',!Account,!Period,'Signage')
   *
   DB('Fin_GL','Actual','NA','NA','NA',!Company,!Affiliate,!FunctionalCell,!Product,!ProductProcessor,!ResponsibilityCentre,SUBST(!Account,1,10),!BalanceType,!Period,'LCYE');

FEEDERS;
['Actual',SectionA:'NA',SectionB:'NA',SectionC:'NA',Measures:'LCYE']=>['APRA Actual',Measures:'LCYE'];

TM1rule2_ML.JPG (201.98 KiB) Viewed 7221 times

[asutcliffe]

Hi Matt

I'm not sure I've followed everything on this thread. However your feeder isn't going to feed the right elements in SectionA, SectionB, SectionC and probably not the right elements in the Account dim. Eg, it's only ever going to feed 'NA' in SectionA (because you've hardcoded this on the left, but haven't specified something different on the right) but you're the example your checking the calculation for is 'AUSSI'.

I think the best way to approach this depends on things like how often Proportion is non zero and the relationship between the account you're taking balance from (eg 2306000000) and the account you're trying to feed (eg 2306000000-BSL00800) - eg is the latter a child of the former? I'd also be asking whether Proportion and Signage are as granular as the dimensionality of the cubes you're taking them from suggest. Eg, is Proportion really different for different Products and Responsibility Centres?

(Also note, having 'Measures':'LCYE' on the right side here is redundant since you've specified the same element of the measure dimension on the left hand side.)

I hope that helps a bit.