I created the Subsets on the fly and create the correct View on the fly and define this as DataSource. Is it guaranteed that I have the following order:
Willi wrote: ↑Wed Aug 29, 2018 2:11 pm
Is it guaranteed that I have the following order:
No. At least I'm not 100% sure but pretty sure you can't.
TM1, if I am not mistaken, uses the index numbers of elements.
And possibly the order of the dimensions in the cube can have precedence over the way you have them in your view (created by TI I assume ?).
You will have to do some tests to see what is the logic, unless anyone else already did the tests and can share the findings.
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
The only way you can guarantee the order is going to be to create the subsets in the order you want (which you have already done), but instead of creating a view you cycle through all the combinations with a nested loop and do a CELLGETN statement to get the value. You'll be giving up zero suppression (which means you'll be processing through a lot of empty cells) but you'll be guaranteeing the order.
Willi wrote: ↑Wed Aug 29, 2018 3:32 pm
I experience a massive, no, a MASSIVE, performance-issue by using a while-loop against a View. So I need to do it via view.
That's indeed as expected in any cube of a decent size and memory consumption.
So you have your answer, you cannot be sure about the order of processing.
IIRC, I did this requirement in the past with a custom exe in the Epilog tab, which sorted the text output.
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 wrote: ↑Wed Aug 29, 2018 2:37 pm
TM1, if I am not mistaken, uses the index numbers of elements.
And possibly the order of the dimensions in the cube can have precedence over the way you have them in your view (created by TI I assume ?).
Yes the order of dimensions in the view is irrelevant. TM1 uses the (reindexed or "optimized") oder of dimensions, rotating from last to first, and then within each dimension processes the elements in index order.
So yes technically you can "control the exact order of processing" but only by controlling the index order of dimensions in the cube and index order of elements. For most practical purposes no you can't really control the order of cells if a view is the source.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Okay, got it. Thx. But, to stay with my example. I tested that A is processed before C. So can I say that all C's are processed before another A is selected or is this mixed?
Willi wrote: ↑Wed Aug 29, 2018 6:30 pm
Okay, got it. Thx. But, to stay with my example. I tested that A is processed before C. So can I say that all C's are processed before another A is selected or is this mixed?
If you really want know what order you are going to get just take your view, use it as a data source, and instead of writing out the values in the cube, write out the element from each dimension, like this (assuming you named the variables the name of the dimensions):
When exporting a cube or using a view extract, the order is determined by the physical order of the elements in the dimensions. Using a subset with a different order does not affect this.
The order of the dimensions is determined by the physical order of the dimensions in the cube. If you re-order the dimensions to a different physical order (Right Click on cube and use Re-order dimensions), then the exported order will reflect this change and this will override the original logical order of the dimensions. By this I mean that if you have a cube with dimensions A B C and you change the physical order to A C B, then the data will come out sorted by the elements of A first, then of C, then of B. However, I would not recommend doing this just to get a different order as physically re-ordering the dimensions can affect the cube size, and response time. However, in some cases if you want things to come out in a set order and it doesn't make much difference then it is an option.
You can control the physical order of the elements in the dimension using DImensionSortOrder, though I usually find it best to put the options directly into the }DimensionProperties cube. I typically use SORTELEMENTSTYPE as BYHIERARCHY and SORTCOMPOENTSTYPE as BYNAME and the two SENSES as ASCENDING. This is good for the typical hierarchical dimensions like Account and Cost Centre. For Time dimensions you will typically have defined things in a particular order, although you can use the same BYHIERARCHY if you have chosen a sensible naming convention for the elements eg Year first.
If all else fails, write the data to SQL database or text file, and sort that, then bring the data back in. In that way you can control what is sorted within what and the order in which the elements are sorted.