Preventing Data Spread to Every Intersection Point

Post Reply
michaelc99
Posts: 20
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Preventing Data Spread to Every Intersection Point

Post by michaelc99 » Tue Oct 12, 2021 4:07 pm

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

Wim Gielis
MVP
Posts: 2787
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Preventing Data Spread to Every Intersection Point

Post by Wim Gielis » 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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis

burnstripe
Posts: 76
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Preventing Data Spread to Every Intersection Point

Post by burnstripe » 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

michaelc99
Posts: 20
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: Preventing Data Spread to Every Intersection Point

Post by michaelc99 » Wed Oct 13, 2021 1:27 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
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

michaelc99
Posts: 20
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: Preventing Data Spread to Every Intersection Point

Post by michaelc99 » Wed Oct 13, 2021 1:43 pm

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

Wim Gielis
MVP
Posts: 2787
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Preventing Data Spread to Every Intersection Point

Post by Wim Gielis » Wed Oct 13, 2021 3:19 pm

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
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis

Wim Gielis
MVP
Posts: 2787
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Preventing Data Spread to Every Intersection Point

Post by Wim Gielis » 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 !
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis

michaelc99
Posts: 20
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: Preventing Data Spread to Every Intersection Point

Post by michaelc99 » Fri Oct 15, 2021 2:41 pm

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

Post Reply