Order of Cube-Processing

Post Reply
Willi
Regular Participant
Posts: 151
Joined: Mon Oct 07, 2013 11:51 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Order of Cube-Processing

Post by Willi »

Hello,

i have a cube with 4 Dimensions:

Dim1
Dim2
Dim3
Dim4

Now I have a TI wich has to step thru some Elements of Dim1, 1 of Dim2, all of Dim3 and 1 of Dim4. So assuming we have to step thru:

Code: Select all

Dim1: A1
      A2
      A3
      
Dim2: B1

Dim3: C1
      C2
      C3
      C4

Dim4: D1
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:

Code: Select all

A1 B1 C1 D1
A1 B1 C2 D1
A1 B1 C3 D1
A1 B1 C4 D1
A2 B1 C1 D1
A2 B1 C2 D1
A2 B1 C3 D1
A2 B1 C4 D1
A3 B1 C1 D1
A3 B1 C2 D1
A3 B1 C3 D1
A3 B1 C4 D1
Thx and regards
Wim Gielis
MVP
Posts: 3105
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: Order of Cube-Processing

Post by Wim Gielis »

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
Willi
Regular Participant
Posts: 151
Joined: Mon Oct 07, 2013 11:51 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Re: Order of Cube-Processing

Post by Willi »

Thx for the answer. If TM1 uses the Index-Numbers for the elements it would meet my needs.

at least I need the process to process all C before it changes the A.
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Order of Cube-Processing

Post by tomok »

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Willi
Regular Participant
Posts: 151
Joined: Mon Oct 07, 2013 11:51 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Re: Order of Cube-Processing

Post by Willi »

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.
Wim Gielis
MVP
Posts: 3105
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: Order of Cube-Processing

Post by Wim Gielis »

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
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Order of Cube-Processing

Post by lotsaram »

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.
Willi
Regular Participant
Posts: 151
Joined: Mon Oct 07, 2013 11:51 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Re: Order of Cube-Processing

Post by Willi »

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?
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Order of Cube-Processing

Post by tomok »

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):

ASCIIOUTPUT('MyFile.txt', Dim1, Dim2, Dim3, Dim4);

Then just look at your text file and see what order the records are in.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Order of Cube-Processing

Post by paulsimon »

Hi

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.

Regards

Paul Simon
Willi
Regular Participant
Posts: 151
Joined: Mon Oct 07, 2013 11:51 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Re: Order of Cube-Processing

Post by Willi »

Thx all, that helped me a lot.
Post Reply