TM1 Forum

Posted: **Tue Aug 28, 2012 1:08 am**

Ok, I had another post on this but thought I'd start afresh... in a nutshell, I am trying 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 internal cube

The main rule is:

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,!Measures) 
;

This works ok (on an unsuppress zeroes test), however, the problem is with the feeders. I am attempting to feed the $ balances, but the only feeder I have gotten to work is:

Code: Select all

['Actual',SectionA:'NA']=>['APRA Actual',SectionA:'UK'] ;

...however UK is only 1 of 100s of domiciles. Attempting to feed to All domiciles causes the server to hang:

Code: Select all

['Actual',SectionA:'NA']=>['APRA Actual',SectionA:'Total Domicile'] ;

Have attached a screenshot of what the view looks like - but at this stage am looking for creative ways of circumventing the issue if anyone has any ideas

Thanks!

Matt

: tm1rule3ml.JPG (133.46 KiB) Viewed 4593 times

Posted: **Tue Aug 28, 2012 8:41 am**

fleaster wrote: (...)

This works ok (on an unsuppress zeroes test), however, the problem is with the feeders. I am attempting to feed the $ balances, but the only feeder I have gotten to work is:
Code: Select all
['Actual',SectionA:'NA']=>['APRA Actual',SectionA:'UK'] ;
...however UK is only 1 of 100s of domiciles. Attempting to feed to All domiciles causes the server to hang:
Code: Select all
['Actual',SectionA:'NA']=>['APRA Actual',SectionA:'Total Domicile'] ;

If you want to feed this rule from balance, you need to think about which elements in 'SectionA' you need to feed. Presumably this is more than just 'UK' but less than all elements (ie 'Total Domicile').as I suggested on the other thread, this probably has something to do with whether there is a value for proportion for a given SectionA. Once you've got your head around what you want to feed, you can work on the implementation.

fleaster wrote: Ok, I had another post on this but thought I'd start afresh...

By the way, it probably made more sense to add this to the original thread so people could read it in context.

Posted: **Tue Aug 28, 2012 10:02 am**

The other thing to watch out for is your definition of "hang".

Feeders take time to execute and this happens when you save the rule, save times will increase the more data there is in the cube but it's quite normal for a save time to be greater than 10 mins and up to an hour is not that unusual.

Cheers,

Posted: **Thu Aug 30, 2012 4:34 am**

thanks again for the tips

asutcliffe wrote: If you want to feed this rule from balance, you need to think about which elements in 'SectionA' you need to feed. Presumably this is more than just 'UK' but less than all elements (ie 'Total Domicile').as I suggested on the other thread, this probably has something to do with whether there is a value for proportion for a given SectionA.

Unfortunately the domiciles used for alot of the accounts is closer to "Total Domicile" than "UK" - so i think i probably need to feed to total domicile.

Steve Rowe wrote:The other thing to watch out for is your definition of "hang". Feeders take time to execute and this happens when you save the rule, save times will increase the more data there is in the cube but it's quite normal for a save time to be greater than 10 mins and up to an hour is not that unusual.

Well I finally got ['Actual',SectionA:'NA']=>['APRA Actual',SectionA:'Total Domicile'] ; to save (about 20-30min), however it still doesn't feed properly - presumably because the Accounts are not fed correctly.

To clarify - the Section A domciles have a one-to-many relationship (hence I can do 'NA' => 'Total Domicile').

However, the Accounts have a few-to-many relationship - so am trying to figure out how the best way to feed this, short of doing 'Total Chart of Accounts' => 'Total APRA Accounts' ...

Posted: **Thu Aug 30, 2012 11:50 am**

fleaster wrote:However, the Accounts have a few-to-many relationship - so am trying to figure out how the best way to feed this, short of doing 'Total Chart of Accounts' => 'Total APRA Accounts' ...

Although I don't usually recommend using conditional feeders, this sounds like the type of situation where it can save you a lot of time and wasted RAM. If you can test for the condition that would indicate where there is data in the account and needs to be fed you can feed with an IF:

['Actual',SectionA:'NA']=>IF(SomeCondition or Value), CubeName, ''),!Dim1, !Dim2, !Dim3.....);

Posted: **Thu Aug 30, 2012 9:41 pm**

Let's rewind a bit...
[APRA Calc] = [Proportion] x [Signage] x [Balance];

So the most correct feeder is the thing that is least populated on the RHS of your rule combined with having the most dimensions in common with the ruled area, let's assume that is Balance.
If we look at your rule without the Proportion and Signage info we get

Code: Select all

['APRA Actual',Measures:'LCYE'] = N:
   DB('Fin_GL','Actual','NA','NA','NA',!Company,!Affiliate,!FunctionalCell,!Product,!ProductProcessor,!ResponsibilityCentre,
         SUBST(!Account,1,10),!BalanceType,!Period,!Measures);

The feeder for this rule is pretty straight forward (conceptually). Just reverse the expression.

Code: Select all

   DB('Fin_GL','Actual','NA','NA','NA',!Company,!Affiliate,!FunctionalCell,!Product,!ProductProcessor,!ResponsibilityCentre,
         SUBST(!Account,1,10),!BalanceType,!Period,!Measures)=>['APRA Actual',Measures:'LCYE'];

but TM1 doesn't accept this syntax, so we start tidying up the syntax and get the following.

Code: Select all

   ['Actual', DimRef1:'NA', DimRef2:'NA', DimRef3:'NA', SUBST(!Account,1,10)]=>['APRA Actual',Measures:'LCYE'];

this still won't work since when we fix something on the left it is fixed on the right. So whenever we fix something on the left we need to say something on the right if we want it to be soemthing different.

Code: Select all

['Actual', DimRef1:'NA', DimRef2:'NA', DimRef3:'NA', SUBST(!Account,1,10)]=>['APRA Actual', DimRef1:'Consolidation', DimRef2:'Consolidation', DimRef3:'Consolidation', Measures:'LCYE'];

(Consolidation could be another N level if thats what you need).

The last bit to sort out is account where you need to feed (I think ) to the immediate parent of the populated element. This sort of looks like this

Code: Select all

['Actual', DimRef1:'NA', DimRef2:'NA', DimRef3:'NA', !Account ]=>['APRA Actual', DimRef1:'Consolidation', DimRef2:'Consolidation', DimRef3:'Consolidation', Measures:'LCYE' , SUBST(!Account,1,10)];

but obviously this won't compile...We drop the !Account on the left since its redundant and also because we use an expression in the formula on the right we need to use a DB reference. We then get.

Code: Select all

['Actual', DimRef1:'NA', DimRef2:'NA', DimRef3:'NA' ]=>
 DB('Fin_GL','APRA Actual','Consolidation','Consolidation','Consolidation',!Company,!Affiliate,!FunctionalCell,!Product,!ProductProcessor,!ResponsibilityCentre, SUBST(!Account,1,10),!BalanceType,!Period,'LCYE');

Once you get your head around that and logic of how to construct the feeder, then you can look at getting creative. Conditional feeders can be pretty useful but you need get the main piecce working first.

Phew HTH

Cheers,

Posted: **Fri Aug 31, 2012 12:31 pm**

Thanks for taking the time out to explain - well I eventually settled on this:

Code: Select all

['Actual',SectionA:'NA']=>
     DB('Fin_GL','APRA Actual','Total Domicile',!SectionB,!SectionC,!Company,!Affiliate,!FunctionalCell,!Product,!ProductProcessor,
           !ResponsibilityCentre,SUBST(!Account,1,10),!BalanceType,!Period,Measures:'LCYE') ;

...unfortunately the rule was still saving after 6 hrs, so I ended up killing it and restarting the service

At this stage am wondering whether it is is better to perform the load in 2 steps via a combination of TI and rules e.g.
1. create rule [APRA Temp] = [Balance] x [Signage] ...then hard load the data via TI + use a 2nd rule:
2. [APRA Actual] = [APRA Temp] x [Proportion]

Matt

TM1 Forum

What's wrong with my rule (part2) :(

What's wrong with my rule (part2) :(

Re: What's wrong with my rule (part2) :(

Re: What's wrong with my rule (part2) :(

Re: What's wrong with my rule (part2) :(

Re: What's wrong with my rule (part2) :(

Re: What's wrong with my rule (part2) :(

Re: What's wrong with my rule (part2) :(