TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

Rule Issue

https://www.tm1forum.com/viewtopic.php?t=11975

Page 1 of 1

Rule Issue

Posted: Fri Sep 11, 2015 6:59 pm
by jim wood
So I don't often hit rule issues but I've hit one that's making me scratch my head. Hopefully it's down to Fridayitis. I am however on the verge of doubting my sanity so I thought I'd better ask here before I go made.

So some background. I'm trying to build something that generates a sequential code based defined ranges. However numbers that have been already taken can expire and be reused. So I have built a cube that does this. I then have a summary cube that displays current status. The dimensions in each cube is as follows:

Summary
InCaps Facility
InCaps Test Code Result
InCaps Code Gen Test 3rd Parameter
InCaps Code Gen Test Code Status

Detail
InCaps Facility
InCaps Test Code Result
InCaps Code Gen Test 3rd Parameter
InCaps Code Gen Sequential Code
InCaps Test String and Numeric

The summary cube looks like this:
InCaps1.JPG
InCaps1.JPG (32.53 KiB) Viewed 8231 times
The number goes down to 999 but only for certain combinations does it use all of them. Only 999 has a weight of 1 the consolidation Available Sequential Code which contains the next digit available for code generation. Those marked with Y have already been taken. Those with N (none visible in the screen cap) are unavailable

Here are the rules for this:

Code: Select all

########
FeedStrings;
SkipCheck;
########

### Eliminates and combination where a code range not needed
['String'] = S: 
IF(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Range End')=0
    ,'N'
    ,Continue
   );

### Eliminates code ranges outside those specified
['String'] = S: 
IF(Numbr(!InCaps Code Gen Sequential Code)>DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Range End') %
    Numbr(!InCaps Code Gen Sequential Code)<DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Range Start')
    ,'N'
    ,Continue
   );

### Rule for the first in range as no previous
['InCaps Code Gen Sequential Code':'1','Numeric'] = N: 
IF(DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'String')@='N'
    ,0
    ,IF(DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'String')@=''
         ,1
         ,0
        )
   );

['Numeric'] = N: 
IF(DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'String')@='N'
    ,DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,
           Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0),'Numeric')
    ,IF(DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'String')@='Y'
         ,DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,
                Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0),'Numeric')
         ,IF(DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'String')@='' &
              DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,
                   Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0),'Numeric')=0
              ,Numbr(!InCaps Code Gen Sequential Code)
              ,DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,
                     Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0),'Numeric')
             )
        )
   );

######
Feeders;
######
In the Summary cube there is current status. This should point to the available sequential code. The summary cube looks like this:
InCaps2.JPG
InCaps2.JPG (36.07 KiB) Viewed 8231 times
As you can see I'm getting #N/A in the cubes. Here are the rules for the summary cube:

Code: Select all

########
FeedStrings;
SkipCheck;
########

### Pull current status from detail cube
['Current Status'] = N: 
DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Available Sequential Code','Numeric');

### Set code append
['Code Append'] = S:
  IF(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status')=0
      ,''
      ,IF(Long(trim(Str(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Range End'),3,0)))=1 &
            !InCaps Facilities@<>'R12'
            ,trim(Str(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
            ,IF(Long(trim(Str(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Range End'),3,0)))=2 &
                  !InCaps Facilities@<>'R12'
                  ,IF(Long(trim(Str(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0)))=1
                        ,'0'|trim(Str(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
                        ,trim(Str(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
                       )
                  ,IF(Long(trim(Str(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0)))=1
                        ,'00'|trim(Str(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
                        ,IF(Long(trim(Str(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0)))=2
                              ,'0'|trim(Str(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
                              ,trim(Str(DB('InCaps Test Code Status',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
                             )
                       )
                  )
             )
         );

######
Feeders;
######

['Range End']=>['Current Status'];
['Range End']=>['Code Append'];
['Range End']=>DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Available Sequential Code','String');
['Range End']=>DB('InCaps Test Code Status Detail',!InCaps Facilities,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Available Sequential Code','Numeric');
The bizzare thing is that if I calculate in the detail cube the #N/A disappears and the values appear.

I have tried saving rules and restarting the server, just in case there was an issue with cube dependencies.

Any pointers on why this is happening will be greatly appreciated,

Jim.

Re: Rule Issue

Posted: Fri Sep 11, 2015 7:21 pm
by jim wood
I tried changing Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0) to an attribute pull instead but still the result.

Re: Rule Issue

Posted: Mon Sep 14, 2015 5:48 am
by macsir
Why do you use Range End to feed Current Status?

Re: Rule Issue

Posted: Mon Sep 14, 2015 11:53 am
by jim wood
macsir wrote:Why do you use Range End to feed Current Status?
If there is no range defined then there is no code to be generated. It forms the basis of the rules. I had no feeders at all originally. I added them to see if that was the issue, but it wasn't.

Re: Rule Issue

Posted: Mon Sep 14, 2015 7:37 pm
by jim wood
The plot thickens. So when I get an error with the calculation when opening the summary cube the rule tracer gives me a circular reference error for the rule in a lower cube which is a simple check against different values:
InCapsErr1.JPG
InCapsErr1.JPG (79.18 KiB) Viewed 8127 times
InCapsErr2.JPG
InCapsErr2.JPG (271.83 KiB) Viewed 8127 times
However once I calculate the values in the sub cube and recalculate in the summary cube, the values come through and the circular ref goes away:

InCapsErr3.JPG
InCapsErr3.JPG (99.62 KiB) Viewed 8127 times
InCapsErr4.JPG
InCapsErr4.JPG (52.71 KiB) Viewed 8127 times

Re: Rule Issue

Posted: Tue Sep 15, 2015 5:52 am
by macsir
Hi, Jim
This is interesting and I have reproduced same error in my environment. I have tried to keep the rule as minimum as possible to make is simple.
To be summary, I just tell you what I have found. There are two circulations.
1. The Numeric function can be simplified and if you keep that condition, there will be '#N/A' showing for '999'. So I use the String condition to replace it. So there is no NA at all.
2015-09-15 15_11_13-Rules Editor.png
2015-09-15 15_11_13-Rules Editor.png (49.84 KiB) Viewed 8107 times
2. For Current Status, if you use the parent, then it will be always NA. If you use '916' and above, it still show NA. But if you use '915' and below, then everything would be perfect.
2015-09-15 15_44_58-Rules Editor.png
2015-09-15 15_44_58-Rules Editor.png (11.68 KiB) Viewed 8107 times
2015-09-15 15_55_01-Cube Viewer.png
2015-09-15 15_55_01-Cube Viewer.png (3.66 KiB) Viewed 8107 times
This makes me thinking that although I can't find an NA from my cubeview, TM1 somehow still can't aggregate them, which means there is still some NA in some elements above '916'. That's why I am taking the current status from a specific element.



I have attached the rule codes for your ref. (I use InCaps Facility rather than InCaps Facilities :))
Summary

Code: Select all

########
#FeedStrings;
SkipCheck;
########

### Pull current status from detail cube
['Current Status'] = N:
#DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Available Sequential Code','Numeric');
#DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'916','Numeric');
DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'915','Numeric');

### Set code append
#['Code Append'] = S:
#  IF(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status')=0
#      ,''
#      ,IF(Long(trim(Str(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Range End'),3,0)))=1 &
#            !InCaps Facility@<>'R12'
#            ,trim(Str(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
#            ,IF(Long(trim(Str(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Range End'),3,0)))=2 &
#                  !InCaps Facility@<>'R12'
#                  ,IF(Long(trim(Str(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0)))=1
#                        ,'0'|trim(Str(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
#                        ,trim(Str(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
#                       )
#                  ,IF(Long(trim(Str(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0)))=1
#                        ,'00'|trim(Str(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
#                        ,IF(Long(trim(Str(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0)))=2
#                              ,'0'|trim(Str(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
#                              ,trim(Str(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status'),3,0))
#                            )
#                      )
#                  )
#             )
#         );

######
Feeders;
######

#['Range End']=>['Current Status'];
#['Range End']=>['Code Append'];
#['Range End']=>DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Available Sequential Code','String');
['Range End']=>DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Available Sequential Code','Numeric');
detail

Code: Select all

########
#FeedStrings;
SkipCheck;
########

### Eliminates and combination where a code range not needed
#['String'] = S:
#IF(DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Range End')=0
#    ,'N'
#    ,Continue
#   );

### Eliminates code ranges outside those specified
#['String'] = S:
#IF(Numbr(!InCaps Code Gen Sequential Code)>DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Range End') %
#    Numbr(!InCaps Code Gen Sequential Code)<DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Range Start')
#    ,'N'
#    ,Continue
#   );

### Rule for the first in range as no previous
['InCaps Code Gen Sequential Code':'1','Numeric'] = N:
IF(DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'String')@='N'
    ,0
    ,IF(DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'String')@=''
         ,1
         ,0
        )
   );

['Numeric'] = N:
#IF(DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'String')@='N'
#    ,DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,
#           Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0),'Numeric')
#    ,IF(DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'String')@='Y'
#         ,DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,
#                Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0),'Numeric')
#         ,IF(DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'String')@='' &
#              DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,
#                   Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0),'Numeric')=0
#              ,Numbr(!InCaps Code Gen Sequential Code)
#              ,DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,
#                     Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0),'Numeric')
#             )
#        )
#   );

IF(DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'String')@=''
    #& DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0),'Numeric')=0
      & DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0),'String')@='Y'
              ,Numbr(!InCaps Code Gen Sequential Code)
              #,DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,'Numeric2')
              ,DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,Str((Numbr(!InCaps Code Gen Sequential Code)-1),3,0),'Numeric')
             );

######
Feeders;
######
#['Numeric2'] => ['Numeric'];
#['Numeric'] =>
#DB('InCaps Test Code Status Detail',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,!InCaps Code Gen Sequential Code,Str((Numbr(!InCaps Code Gen Sequential Code)+1),3,0),'Numeric');

['Available Sequential Code','Numeric']=> DB('InCaps Test Code Status',!InCaps Facility,!InCaps Test Code Result,!InCaps Code Gen Test 3rd Parameter,'Current Status');

Re: Rule Issue

Posted: Tue Sep 15, 2015 5:58 am
by macsir
Data in Detail cube.

Re: Rule Issue

Posted: Tue Sep 15, 2015 6:02 am
by macsir
Situations in Error for the first circulation.

Re: Rule Issue

Posted: Tue Sep 15, 2015 6:07 am
by macsir
Situation in Error for the second circulation.

Re: Rule Issue

Posted: Tue Sep 15, 2015 11:56 am
by jim wood
Wow. Many thanks. That's really helpful. I do however have 1 issue. The key requirement is that the codes can be generated from 1-999, so 915 is too low. I am however going to change things to a string and see if that helps. If I'm still getting an error I'm going to fire this one over to support. I'm also going to try and make sure we replicate it in 10.2.2 as we are running 10.1.1 at the moment, taking away their standard "upgrade" answer,

Jim.

PS. Again many thanks!! :)

Re: Rule Issue

Posted: Tue Sep 15, 2015 12:31 pm
by kangkc
Did you notice any stack overflow error in TM1 server log ?
I didn't really look through the code but I suspect you are doing referencing in a circular manner and hence resulted in typical stack overflow, which is not a TM1 bug.

Re: Rule Issue

Posted: Tue Sep 15, 2015 2:14 pm
by jim wood
There are indeed stack over flows in the code. while the rule is kind of circular in nature at no point is anything referring to itself. Hence my trouble understanding why I was hitting the issue.

Re: Rule Issue

Posted: Tue Sep 15, 2015 3:02 pm
by Steve Rowe
Hi Jim,
Isn't this is the same problem as you get in for example daily cash flow statements where day 2s opening balance is linked to day 1s closing balance and appropriately fed? Pretty sure what you are seeing is the same behaviour as I see when the break points in the rule and feeder flow are not set up correctly.

Once the stack is broken the whole (set of) cube will mis-behave and the exact nature of this has varied by version.

IMO the stack is far too small though I've no idea of the implications of extending it.

Re: Rule Issue

Posted: Tue Sep 15, 2015 3:12 pm
by jim wood
Thank you very much guys for all your feedback. It looks like I'm going to have to move some this in to a TI statement. This isn't a disaster as there is a TI in place that is used to create a code based on user selections anyway. While statement here I come.....

Re: Rule Issue

Posted: Wed Sep 16, 2015 1:26 am
by kangkc
I have to resolve to TI in some of our cases as there is simply no way to go around the stack limit. Can't blame TM1 though.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited