A couple of simple questions - cube manipulation

[nhavis]

- what is a leaf hold?

- how do I make a cell appear as blank when a value has been previously entered (when using undefvals) - eg. clear the cell?

[Alan Kirk]

- what is a leaf hold?

Easier to explain by illustration.

Suppose that you have values for a budget line item. The values appear in a month consolidation which is made up of four week (N level, or leaf) elements.

Week 1 has a value of $100, week 3 has a value of $400. Weeks 2 and 4 have values of 0.

You then apply a leaf hold to the month consolidation. This will put a hold on weeks 1 and 3, since those are the only leaves of the consolidation which have non-zero values at that time.

If you use data spreading to enter an amount of $1000 to the month, then weeks 2 and 4 will have their values changed. Weeks 1 and 3 won't because of the hold.

- how do I make a cell appear as blank when a value has been previously entered (when using undefvals) - eg. clear the cell?

To the best of my knowledge, once a cell has been populated, then it's populated. For Undefvals to work, the cell can't have been populated at all during that session. Accordingly rebooting the server is the only way that a cell which has been "zeroed out" will return to a state of being undefined. (Unless someone else is aware of a trick to do that...)

[nhavis]

Rebooting the server does not return the cells to 'blank'.

I would expect that if it did then simply unloading the cube within Explorer would do the same (is there a difference between unloading+reloading a cube as compared to restarting the server?).

[Alan Kirk]

Rebooting the server does not return the cells to 'blank'.

Perhaps you'd like to enlighten everyone as to how you came across that knowledge?

The .cub files do not store zero values. If they did, even a medium sized cube's .cub file would run to gigabytes. Consequently, when you reboot a server (after saving the cubes on shutdown), a value that was zeroed out in the previous session will not form part of the .cub file, yes?

So there will be no value to be loaded into that cell when the server loads the cubes, yes?

So it will, by definition, be undefined, yes?

This presupposes that there are no rules or feeders involved.

The possibility of unloading occurred to me after I posted the previous response, but unloading doesn't always work in the way that one would expect... again, especially if feeders are involved. There have been threads on this previously.

[nhavis]

Cube files must store zero values (at least when undefvals is used) otherwise there would be no way of distinguishing between zero and blanks (undefined cells). That is not to stay that they will store blank/undefined values though.

When i reboot the server and take a look at my cube, I see 0's instead of blanks where I have previously entered data. However I see blanks where I have not previously entered data.

[Alan Kirk]

Cube files must store zero values (at least when undefvals is used) otherwise there would be no way of distinguishing between zero and blanks (undefined cells). That is not to stay that they will store blank/undefined values though.

When i reboot the server and take a look at my cube, I see 0's instead of blanks where I have previously entered data. However I see blanks where I have not previously entered data.

And ARE there any rules or feeders at play here?

[nhavis]

My rule file is as follows:

skipcheck;
undefvals;
feeders;

[Steve Rowe]

Using undefvals in your rules will make TM1 behave very strangly since it will now distinguish between a 0 and an un populated cell. It's a very long tiem since I had a play with this and I never really found a practical use for it, though I'd love to here if you have one.

Treating a 0 as a value does I think have big implications for memory usuage in RAM and on the disc. Also I don't think there is a way of setting a cell back to undefined once it has contained a value, even if you set the cell to 0 and relaunch the server.

Like I said it's a long time since I looked at it but I could not get better performance from the system using undefvals and it messed up rules and feeders and cube displays.

I'd be intereseted to hear if you have a practical use for this or are you just having a play?

Cheers,

[Alan Kirk]

Also I don't think there is a way of setting a cell back to undefined once it has contained a value, even if you set the cell to 0 and relaunch the server.

Sorry Steve, I just don't see how that could be possible. The only way would be if the hypothesis about the zero values being stored in the .cub file held true, and I just don't believe that happens.

Try the following test (which I did on 9.4):
- Create a dummy cube with lots of cells. I created one which had 3 dimensions of 101 elements each plus a consolidation of those elements. That's 1,030,301 cells (at N level), agreed?
- Check the size of the .cub file on disk. Mine was 4,289 bytes, with a size on disk of 8,192 bytes.
- Do a spread into all leaf cells at the top level consolidations. If you have performance Monitor running, you should now see number of populated cells for that cube at the aforementioned 1,030,301. I spread the value 1000 into the top level consolidations for all of the dimensions.
- Do a data save. Check the size of the .cub file. Mine was now 14,469,711 bytes, 14,471,168 bytes on disk.
- Blow away the numbers by zeroing them out. I did this by data spreading since I didn't need an undo.
- Do a data save. Check the size of the .cub data file.

My numbers? Back to 4,289 bytes, with a size on disk of 8,192 bytes.

Every single cell in the cube had had a non-zero value entered into it, and every one of those values had been zeroed out and yet the .cub file size was identical, to the byte, compared to the virgin cube size if you'll pardon the expression.

If the .cub file is storing a record of those cells ever having been populated and reset to zero, then Applix has come up with the most remarkable file compression algorithm since Charles Babbage said to Ada "Hey babe, let's do lunch". Somehow it has managed to store over a million pieces of information without consuming one extra byte.

And if that's not the case, then I simply can't see how a freshly launched server could ever distinguish between a cell that had a value in a previous session and was reset to zero before the data save at shutdown occurred, and one which never had a value in the first place. The only conclusion would be that a cell is undefined if it didn't have a value at server startup, and hasn't had a value entered to it at any time during the current session, regardless of what may have happened in any previous session.

[Steve Rowe]

Are you using the undefvals; command in the rules?

UNDEFVALS

UNDEFVALS allows users to distinguish between data cells that are empty and cells that actually contain a zero. When a rule includes an UNDEFVALS statement, cells containing zeros display the value zero, but empty cells appear blank. This function can also be used to prevent data from being stored in a cube based on a logical test.
When used, UNDEFVALS must be the first statement in a rule without a SKIPCHECK statement. If a rule includes a SKIPCHECK statement, the UNDEFVALS statement must be the second statement in the rule.
IMPORTANT: When UNDEFVALS is used to distinguish between empty cells and those containing the value 0, cells containing the value 0 remain visible when zero suppression is applied to a view.
Syntax
UNDEFVALS
Arguments
None.
Example
When a rule includes an UNDEFVALS statement, cells containing zeros display the value zero, but empty cells appear blank.

Thats what the OP is talking about, I think.

[lotsaram]

Leaf holds simple yes. Distinguishing between blank and zero cell values definitely not, especially when also involving Undefvals.

My understanding is that when Undefvals is used in a cube's rule file then if a zero value is entered into a cell or a value is zeroed out then TM1 will distinguish between blanks and zeros and will store zero values in the .cub file (when otherwise this would never happen.) The corollary to this is that a zero entered into a cell would also fire a feeder.

I can think of plenty of instances where this would hamper performance but can't think of anywhere it would be of any benefit. Can't actually recall ever seeing a model using undefvals in action.

[Alan Kirk]

Are you using the undefvals; command in the rules?

Thats what the OP is talking about, I think.

OK, I'm prepared to put up my hands on that one.

If, and only if, you have UndefVals in the rules, it seems that the zeroes are saved in the .cub file. In addition when you restart the server and turn on performance monitor, all of the cells will be shown as being populated even though they show zeroes.