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
Preventing Data Spread to Every Intersection Point
-
- Posts: 46
- Joined: Mon Jul 26, 2021 12:55 pm
- OLAP Product: TM1
- Version: 2.0.0
- Excel Version: Office 365
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Preventing Data Spread to Every Intersection Point
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.
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
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Regular Participant
- Posts: 197
- 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
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
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
-
- Posts: 46
- 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
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?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
Is this something that we would need to add manually?
Thank you,
Michael
-
- Posts: 46
- 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
- The directive that I have is to have it enabled, so unfortunately, I am not able to disable it.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.
- 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
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Preventing Data Spread to Every Intersection Point
The information can be found in the documentation: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.
https://www.ibm.com/docs/en/planning-an ... ozerocells
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Preventing Data Spread to Every Intersection Point
Good catch !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
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
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 46
- 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
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.Wim Gielis wrote: ↑Wed Oct 13, 2021 8:05 pmGood catch !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
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 !
Thank you Wim and burnstripe! I appreciate the insight and help!
Thank you,
Michael