String rule taking forever to calculate

[Martin Ryan]

I have a twelve dimension cube that I want to apply security against. I want to apply security on only three of those dimensions though. So I've set up a string cube to enter the information against it. The dimensions are Company, Scenario, Period and String.

In my Cell Security cube for the cube with the data I've set a rule up that says

Code: Select all

[]=S: if(db('zSecurity', !Company, !Scenario, !Period, 'String')@='', 'READ', db('zSecurity', !Company, !Scenario, !Period, 'String'));

I've then set up a view that is very high level, but as far as I can figure out it still only needs to calculate one cell as S: rules don't consolidate (do they?), so to my mind it should take all of a nanosecond. However it's taken about five minutes so far and the CPU on the server is up to capacity.

What have I done wrong?

Martin

[David Usherwood]

Without fully understanding what you're after I would say your security rule looks to be recursive and the engine may be happily disappearing up its own .... you get the picture.

[Martin Ryan]

Shouldn't be. The rule is in the }CellSecurity_MI cube and retrieving values from the zSecurity cube. It's not internally querying itself, nor are there any rules in the zSecurity cube.

I just tried FeedStrings, but that failed with a Server out of Memory error. Not really surprising as I was feeding from a cube with three dimensions to one with thirteen so had ten consolidations to feed.

I have noticed that if I calculate the cube at a low level it's not a problem. As I add in more consolidations it takes longer and longer. It would appear that it is in fact trying to consolidate something.

As I'm only interested in differentiating between read and write (dim security takes care of read vs no access) I'll try writing the rule so it only applies to bottom level elements.

Martin

[Martin Ryan]

As I'm only interested in differentiating between read and write (dim security takes care of read vs no access) I'll try writing the rule so it only applies to bottom level elements.

That seems to have done the trick, but I'd be interested to hear opinions on why my original rule took so long.

Martin

[paulsimon]

Martin

An interesting one. I guess that if you are only bothered about controlling READ vs WRITE on numeric data, and you handle NONE elsewhere, then you only need to control things at the N: level since TM1 only allows numeric data to be entered at the N: level.

I think that the reason that your original rule took so long was that, because as it was a string rule, there was nothing to restrict TM1 to calculating only at the N level. Therefore it would have tried to calculate every possible intermediate consolidation in the cube with I think you said 11 dimensions. That could get quite large, especially if you have deep hierarchies. TM1 cannot handle string data as well as numeric data, since string data is naturally variable length. I believe that it probably stores a pointer to the string and then the string data itself is stored on a heap memory store somewhere. Therefore TM1 has to do a lot of memory management and pointer jumping, which can get quite processor intensive.

Anyway, glad you found the answer.

Regards

Paul Simon