TM1 Forum

Good Morning Everyone,

I'm not entirely sure if I will adequately explain the problem correctly, but here goes:

We had a user who entered a value into an intersection point that previously held a zero value. The user then performed the data spread function. which PA spread that new value to every dimension on a pro-rata basis. Since the intersection point previously held a zero value, PA did not have a reference point on how to spread the data, hence it spread it equally across every available intersection point.

What happened was the value was spread over 124,000,800 data points and an 11 MB cube ballooned into a 1.5 GB cube and the Feeders file went from 10 MB to 5 GB. I wrote a TI process with a single view that was entered set as the data source and zero out view, which cleaned up the data.

However, what approaches have you used in the past to avoid running into a similar issue? Does this come down to a training opportunity, or is there a way to put a restriction on the number of intersection points used within the data spread function?

Thanks in advance,
Michael

It is basically a training opportunity, as you put it so well.

Besides that, one could have options like:
- turning off the capability of spreading, however it holds for all cubes and also when the aggragated value is not zero.
- cell security. Haven't looked at that option but maybe it's not too bad of a solution, provided that you can manage the cell security (and continue to manage it in the future).
- depending on the client used and input reports used, etc: a warning message in bold and red font. But it's more a theoretical solution that will only have limited use cases - still it could apply sometimes.

Have you considered this parameter in tm1s.cfg
ProportionSpreadToZeroCells=F

It should still allow data spreading and population of leaves, but won't allow data spreading on consolidations where the leaves are all zero

burnstripe wrote: ↑Tue Oct 12, 2021 10:55 pm Have you considered this parameter in tm1s.cfg
ProportionSpreadToZeroCells=F

It should still allow data spreading and population of leaves, but won't allow data spreading on consolidations where the leaves are all zero

I haven't seen this option before and it is currently not added in my TM1s.cfg file. So, I assume this is enabled by default?

Is this something that we would need to add manually?

Thank you,
Michael

Wim Gielis wrote: ↑Tue Oct 12, 2021 8:31 pm It is basically a training opportunity, as you put it so well.

Besides that, one could have options like:
- turning off the capability of spreading, however it holds for all cubes and also when the aggragated value is not zero.
- cell security. Haven't looked at that option but maybe it's not too bad of a solution, provided that you can manage the cell security (and continue to manage it in the future).
- depending on the client used and input reports used, etc: a warning message in bold and red font. But it's more a theoretical solution that will only have limited use cases - still it could apply sometimes.

- The directive that I have is to have it enabled, so unfortunately, I am not able to disable it.
- With cell security, it would probably work, but I can see it requiring heavy maintenance.
- As far as client - they are using PAW to perform the data spread. A warning message here would be helpful, but I see your point about limited use cases and I wonder about long-term effectiveness.

Thank you,
Michael

michaelc99 wrote: ↑Wed Oct 13, 2021 1:27 pm
I haven't seen this option before and it is currently not added in my TM1s.cfg file. So, I assume this is enabled by default?

Is this something that we would need to add manually.

The information can be found in the documentation:
https://www.ibm.com/docs/en/planning-an ... ozerocells

burnstripe wrote: ↑Tue Oct 12, 2021 10:55 pm Have you considered this parameter in tm1s.cfg
ProportionSpreadToZeroCells=F

It should still allow data spreading and population of leaves, but won't allow data spreading on consolidations where the leaves are all zero

Good catch !

Michael,

the downsides are:
- it applies to all cubes
- new data (current value is 0 at aggregated level) can only be entered in level 0 cells. I can imagine that you are at level 0 in all the dimensions of the cube, but the Months dimension is showing total year or some YTD consolidation. When every underlying month needs the same value, the user must copy/paste or do a repeat spreading operation to populate let's say 12 cells. Overwriting cell for the total year will not work anymore.

But it will definitely solve your problem !

Wim Gielis wrote: ↑Wed Oct 13, 2021 8:05 pm

burnstripe wrote: ↑Tue Oct 12, 2021 10:55 pm Have you considered this parameter in tm1s.cfg
ProportionSpreadToZeroCells=F

It should still allow data spreading and population of leaves, but won't allow data spreading on consolidations where the leaves are all zero

Good catch !

Michael,

the downsides are:
- it applies to all cubes
- new data (current value is 0 at aggregated level) can only be entered in level 0 cells. I can imagine that you are at level 0 in all the dimensions of the cube, but the Months dimension is showing total year or some YTD consolidation. When every underlying month needs the same value, the user must copy/paste or do a repeat spreading operation to populate let's say 12 cells. Overwriting cell for the total year will not work anymore.

But it will definitely solve your problem !

I'm sure some end-users won't see it this way, but this is fantastic. I added the ProportionSpreadToZeroCells parameter in DEV and tested out the behavior. As expected, users will need to go down to the N-level member-set for each dimension in the cube to be able to enter a value. Once a value has been entered then I can adjust the values at the C-Level.

Thank you Wim and burnstripe! I appreciate the insight and help!

Thank you,
Michael