How to use dynamic subsets in rules?

[tcasey]

Hi...

I'm new to the forum and am a new TM1 developer.

My question is: Can you use dynamic subsets in business rules?

For example I would like to replace the following code in our Payroll Budget cube rule:

['Contract Increase',
{'01.71.2752000','16.71.2100000','16.71.2756000','16.71.2758010',
'16.71.2758020','16.71.2758040','16.71.2760100','16.71.2760200',
'16.71.2760300','16.71.2760400','18.71.2750000'},
{'70200928U','70201028U','70205029U','70205530U','70453026U','70469026U','70469527U','70470030U'},
{'0380','0400','0420','0440','0460','0500','0560','0600','0660','0680','0700','0730','2750'}]
=N:DB('Job Code Measures',!Fiscal Year,!Job Code,'Contract Increase');

with:

['Contract Increase',
{Depts.[OCCUPANCY DEPTS].Children},
{'70200928U','70201028U','70205029U','70205530U','70453026U','70469026U','70469527U','70470030U'},
{'0380','0400','0420','0440','0460','0500','0560','0600','0660','0680','0700','0730','2750'}]
=N:DB('Job Code Measures',!Fiscal Year,!Job Code,'Contract Increase');

Where a dynamic subset for OCCUPANCY DEPTS was created in the subset editor expression window:
{Depts.[OCCUPANCY DEPTS].Children}

I can’t find any info on this in the TM1 documentation or in this forum.

Thanks,

Tom

[David Usherwood]

Simple answer - you can't. (Use subsets in rules - dynamic or otherwise.)
This is probably because they can change and move your numbers, but so can hierarchies and attributes, which are usable. So not entirely logical.
But there it is.

[Steve Rowe]

Hi Tom,
That's not possible I'm afraid.
But..
You may be able to put whatever conditions that drive your MDX subset into an IF statment on the right hand side of the rule. Typically you would set up an attribute / lookup value and test against this.
From the you have written probably the best approach is to
1. Create a look up cube dimensioned by Depts and Dept Measure, the new dimension Dept Measure has the numeric element Child.
2. Write a rule in the lookup cube ['ChildString']=N: ElIsAnc('Depts', 'Occupancy Depts', !Depts) ; This should return a 1 when we are on a child of Occupancy Depts.
4. Test against the lookup value in your main rule.

The reason we use a lookup cube we have built ourself rather than an attribute cube is that we can only put string rules in an attribute cube. String values are not cached. This means that everytime the main rule is evaluated the ElISAnc is recalculated if we put it in an attribute cube. This will hit the performance.

HTH

[tcasey]

Hi Steve and David...

Thanks for your prompt and knowledgeable responses.

I've tried your workaround Steve and it works well. However I was trying to keep my example simple and what I am struggling with now is trying to do this for the 3 dimensions: Dept, Job Code and Earning.

My Dept Lookup cube has this rule:

# the below function returns a '1' if the Dept is an ancestor of Occupancy Depts All
['OccupancyChild' ] =N:elisanc('Depts','Occupancy Depts ALL',!Depts);

My main cube rule contains this:

['Contract Increase' ] =N:if(DB('Dept Lookup', !Depts, 'OccupancyChild')=1, 5,0);

Now Is it possible, using 3 lookup cubes for Dept, Job Code, and Earning, to modify the right hand side of the rule to check for three conditions (i.e Dept is an Occupancy Dept, Job Code is an Occupancy Job Code and Earning is an Occupancy Earning)?

The reason I want to do this is that the business rules otherwise would be so ridiculously long and hard to maintain. I've built my application using a small sample of data (for 1 director of a population of 60 directors). I've basically converted an excel program I wrote in VBA to TM1. If I have to list every combination of dept, job code, and earning for all my calculations, the rules for all the cubes that make up the application would be extremely long and hard to maintain.

Thanks for your help.

Regards,

...Tom

[kielmc]

Now Is it possible, using 3 lookup cubes for Dept, Job Code, and Earning, to modify the right hand side of the rule to check for three conditions (i.e Dept is an Occupancy Dept, Job Code is an Occupancy Job Code and Earning is an Occupancy Earning)?

['Contract Increase' ] =N:if(DB('Dept Lookup', !Depts, 'OccupancyChild')=1, 5,0);

I believe you're saying you want the value for cells addressed by the "Contract Increase" element to be eqaul to 5 if 3 conditions are true, correct?

You could use nested if statements:

['Contract Increase' ] =N:if(DB('Dept Lookup', !Depts, 'OccupancyChild')=1,
if(DB('Job Code', !Jobs, 'OccupancyJobCode')=1,
if(DB('Earning Code', !Jobs, 'OccupancyEarning')=1,
5,
0),
0),
0);

OR you could use three rules statements with the CONTINUE function:

['Contract Increase' ] =N:if(DB('Dept Lookup', !Depts, 'OccupancyChild')<>1,0,CONTINUE);
['Contract Increase' ] =N:if(DB('Job Code', !Jobs, 'OccupancyJobCode')<>1,0,CONTINUE);
['Contract Increase' ] =N:if(DB('Earning Code', !Jobs, 'OccupancyEarning')<>1,0,5);

Keep in mind that using if statements on the right side of a rules equation are more "expensive" than declaring the elements on the left side. The more precise you can be on the left side of the rules equation the better, as the more specific you are, the fewer cells TM1 will have to evaluate when queried.

This is where having the ability to use dynamic subsets on the left side would be extremely beneficial. Look for it in a future release : )

[Steve Rowe]

Or with a single If

Code: Select all

['Contract Increase' ] =N:if(DB('Dept Lookup', !Depts, 'OccupancyChild')=1 & DB('Job Code', !Jobs, 'OccupancyJobCode')=1 & DB('Earning Code', !Earnings, 'OccupancyEarning')=1, 
5,
0);

No idea if there is any difference in the three different flavours of this rule once this is compiled though...

You may want to consider creating a single lookup cube that has all three of your test dimensions.
['Contract Increase' ] =N:if(DB('Big Lookup', !Depts, !Jobs, !Earnings, 'Child')=1 ,
5,
0);

This should be more efficient but it depends how big your system is and how complex the logic that drives the result of the "Big Lookup" cube is. If the structures that drive your logic are slow changing and/or strongly controlled you might consider writing a TI that copies the results of the ruled logic into a static measure. If you test against this then you should get a performance improvement, provided you can live with stepping away from a properly dynamic system and can make sure you update the static values at the right frequency.

HTH,

[lotsaram]

I would like to replace the following code in our Payroll Budget cube rule:

['Contract Increase',
{'01.71.2752000','16.71.2100000','16.71.2756000','16.71.2758010',
'16.71.2758020','16.71.2758040','16.71.2760100','16.71.2760200',
'16.71.2760300','16.71.2760400','18.71.2750000'},
{'70200928U','70201028U','70205029U','70205530U','70453026U','70469026U','70469527U','70470030U'},
{'0380','0400','0420','0440','0460','0500','0560','0600','0660','0680','0700','0730','2750'}]
=N:DB('Job Code Measures',!Fiscal Year,!Job Code,'Contract Increase');

with:

['Contract Increase',
{Depts.[OCCUPANCY DEPTS].Children},
{'70200928U','70201028U','70205029U','70205530U','70453026U','70469026U','70469527U','70470030U'},
{'0380','0400','0420','0440','0460','0500','0560','0600','0660','0680','0700','0730','2750'}]
=N:DB('Job Code Measures',!Fiscal Year,!Job Code,'Contract Increase');

Where a dynamic subset for OCCUPANCY DEPTS was created in the subset editor expression window:
{Depts.[OCCUPANCY DEPTS].Children}

Getting back to the original post I think people have been a little guilty of answering Tom's question as opposed to analysing the problem. In this instance there would be no need to ever use a dynamic subset as standard rule syntax will do. By just modifying the area statement the rule can be rewritten as: (Edit: I must have written this on drugs.)

['Contract Increase', 'Depts':'OCCUPANCY DEPTS',
{'70200928U','70201028U','70205029U','70205530U','70453026U','70469026U','70469527U','70470030U'},
{'0380','0400','0420','0440','0460','0500','0560','0600','0660','0680','0700','0730','2750'}]
=N:DB('Job Code Measures',!Fiscal Year,!Job Code,'Contract Increase');

Edit: No it can't what rubbish, rule area statements are point sensitive, it's feeder statements that aren't. You do need the lookup test Tom, sorry to disappoint. Best off to write a more general rule ignoring the Depts dimension alltogether and then test for whether !Depts is a child of 'Occupancy Depts' either with Elisanc or an attribute or some other lookup mechanism.

It is also a good idea to prefix element names in the area statement with the dimension name and colon, especially if element names may not be unique across all dimensions. Doing this will guard against rules breaking in the future should element names become ambiguous.

The real need to do a test (attribute, lookup cube, whatever ...) comes when one needs to apply a different calculation to certain cells depending on the evaluation criteria within the same area statement. If subsets do ever make their way into rules then my guess is that it would only ever be on the RHS of the equation. Rules are assigned to cube areas on a top to bottom once off first come first served basis on server load or when the rule is recompiled. If area statements were subject to dynamic reassignment (outside of dimension structure changes) this would likely cause all sorts of problems.

[tcasey]

Thanks Steve and kielmc for expanding on how to write a rule testing 3 dimensions using either 3 lookup cubes or 1 big lookup cube. I tried this in my test cubes and it works great.

Lotsaram, you're knowledge and insight is greatly appreciated. I'm not sure if I know enough yet, but it seems you hit the nail on the head in analyzing my problem. I've had to read it a few times for it to sink in, and am eager to try it tomorrow. It appears my question has caused some interesting discussion on dynamic subsets and rules, as indicated in the split thread Steve started.

Just to put things in perspective, I'm a budget analyst (accountant) who dabbles in programming in VBA with excel. As I've said I wrote the program we currently use for generating our Payroll Budget for a billion dollar public health care system. It is about 50 pages of vba code, so a little complex. I am responsible for converting my Payroll Budget program to a TM1 application.

Our IBM consultant prepared a demo for us and that was how he had the code written: with area definitions comprised of long lists of department numbers, job code numbers, and earning numbers etc. The eight days of training we had for TM1 left a lot to be desired, and I find the documentation poor (this is just my experience, others experience may be different).

I was thinking there has to be a better way, like defining dynamic subsets and referring to them in the area definition. Lotsaram's pointer (wish I had lots of ram) : that I could use "'Depts':'OCCUPANCY DEPTS'" in the area definition to refer to cells for children departments of the OCCUPANCY DEPTS consolidation sounds very sensible.

My other concern was what will the performance of this application be like with the lookup cubes. It appears then that lotsaram's suggestion should result in no performance hit, compared to the long list of code I have now (the response time for it now is quite good after figuring out Skipcheck & Feeders).

Thanks again to all for your time in helping. This forum reminds me alot of a linux forum I am apart of - it has the same friendly helpful atmosphere. I can't wait till I become knowledgeable enough to help out with providing assistance to other TM1 users here. I will post back once I get to try this out and convert my business rules using lotsaram's suggestions.

Regards,

...Tom

[lotsaram]

Our IBM consultant prepared a demo for us and that was how he had the code written: with area definitions comprised of long lists of department numbers, job code numbers, and earning numbers etc.

Don't assume that just because a consultant prepared your original rules that this means they are well written. Code written in the context of a demo or a proof of concept or a "quick & dirty" implementation is likely to be in the vein of "let's just get the job done quickly" and quite different from what might normally be produced in the context of a well managed implementation where attention is paid to best practice coding, performance optimisation and documentation.

Using lookup cubes shouldn't degrade performance too much, of course the less complex a rule the better the performance. However in general a lookup is preferable (better performance) than complex conditional logic to achieve the same result.

Happy to have been of some assistance. If you have figured out skipcheck and feeders then you are well on your way as this is widely recognised as the most difficult part (by far) of TM1 to wrap your mind around. Best of luck coming up to speed and I look forward to your future contributions in an answering and well as asking capacity.

Edit: Note to self. Don't assume you always get reliable advice on forums either! (although I have since cleansed my drug hazed drivel, of course it could have been a lack of drugs ...)

[Steve Rowe]

Hmmmm....
lotsaram wrote

['Contract Increase', 'Depts':'OCCUPANCY DEPTS',
{'70200928U','70201028U','70205029U','70205530U','70453026U','70469026U','70469527U','70470030U'},
{'0380','0400','0420','0440','0460','0500','0560','0600','0660','0680','0700','0730','2750'}]

Is this syntax new? I've never seen a C level on the LHS of a rule statement used to refer to all the N levels below it before. I tested it briefly in 9.0 and it didn't seem to work.

It's of course common to do this with feeders.....

[lotsaram]

Hmmmm....
lotsaram wrote

['Contract Increase', 'Depts':'OCCUPANCY DEPTS',
{'70200928U','70201028U','70205029U','70205530U','70453026U','70469026U','70469527U','70470030U'},
{'0380','0400','0420','0440','0460','0500','0560','0600','0660','0680','0700','0730','2750'}]

Is this syntax new? I've never seen a C level on the LHS of a rule statement used to refer to all the N levels below it before. I tested it briefly in 9.0 and it didn't seem to work.

It's of course common to do this with feeders.....

Cheers Steve, hmmmm indeed.
Yes. Invented by my imagination or wishful thinking. Not enough caffeine between screen and keyboard, hangs head in shame, don't know what I was thinking.

Better go edit that post.

[tcasey]

Hey lotsaram no worries, probably just memory errors, nothing a reboot can't fix:)

So in summary, if I understand correctly, my best option is probably lookup cubes as they are better than using attributes as they are not cached and would result in a performance hit. It is questionable whether 1 big lookup cube is more efficient than 3 individual lookup cubes.

Since I will have to be testing many other combinations, I will go with using a single lookup cube for each dimension and a measure for each combination I will be testing (e.g. REG EARNINGSchild , OCCUPANCY EARNINGSchild, OTHER BASE RATE EARNINGSchild , etc...).

What I like about using the lookup cubes is that they are driven by the consolidations in the dimension and will make the code in the business rules much more compact and easy to read and maintain. If for example I add a department during the process of preparing our Payroll Budget during the budget cycle, all I have to do is put it in the dimension under the correct consolidation, and I won't have to change any of the business rules in any of the cubes that make up our budget application.

Let me know if you have any better suggestions as I will be working at this piece meal over the next number of weeks. My approach will be to copy my test cube and then replace each of my business rules, recalculating after each change as I go, and making sure after each change that I reconcile with the original application.
Thanks again for everyone's help.

Regards,

...Tom