Planning Analytics - number of elements in a dimension

Post Reply
Wim Gielis
MVP
Posts: 1748
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Planning Analytics - number of elements in a dimension

Post by Wim Gielis » Wed Mar 07, 2018 3:32 pm

Hi all,

Just wondering. If we need to loop over all elements in a dimension (classical definition of a dimension),
is this the way forward ? Seems like overkill if you ask me.

Only to get the number of elements in a dimension we need to loop over all hierarchies and test if the element is new w.r.t. previous hierarchies ?

Should all our dimension manipulations and stuff be generalized to the n-hierarchy case ?

Code: Select all

# the number of elements in a dimension (classical definition)

# example from the Smartco TM1 server

# Wim Gielis - 2018-03-07

# loop over hierarchies
h = 1;
While( h <= DIMSIZ( '}Hierarchies_organization' ) );

    vHierarchy = Dimnm( '}Hierarchies_organization', h );

    # loop over elements
    i = 1;
    While( i <= DIMSIZ( vHierarchy ));
       
       vElem = Dimnm( vhierarchy, i );
       bFound = 0;

       # loop over hierarchies again
       hh = 1;
       While( hh < h );
       		vHierarchy_check = Dimnm( '}Hierarchies_organization', hh );
       		If( Dimix( vHierarchy_check, vElem ) > 0 );
            	bFound = 1;
        		break;
       		EndIf;
       		hh = hh + 1;
       End;

       If( bFound = 0 );
       ASCIIOutput( 'test.txt', NumberToString( i ), vHierarchy, vElem );
       EndIf;
       i = i + 1;

    End;
    
    h = h + 1;

End;
Thanks for your input.

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

lotsaram
MVP
Posts: 3094
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: Planning Analytics - number of elements in a dimension

Post by lotsaram » Thu Mar 08, 2018 12:06 pm

Hey Wim,

Just reading quickly your code it seems to be doing more than it needs to be doing if you just want to count the element in each hierarchy.
You can get a count of element in any hierarchy just with regular old DimSiz function DimSiz('dim':'hierarchy'). In PAL if alternate hierarchies are defined for a dimension then DimSiz('dim') gives the size of the default hierarchy not total (so the same as calling DimSiz('dim':'dim') ). So to get the grand total count of all elements you would need to just loop over }Hierarchies_dim and then call DimSiz for each hierarchy and sum. (This woudl lead to double-counting of leaf elements so if you wanted to be really correct at the end you could take DimSiz('dim':'LEAVES') * (count of hierarchies - 1 ) and subtract this from the total. assuming all leaf elements were present in all hierarchies.)

Or is your intention not actually to get the size of each hierarchy (and the total dimension) but rather to identify if a particular element is present in each hierarchy? I'm a bit confused since your description and the code seem to be a bit contradictory.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Wim Gielis
MVP
Posts: 1748
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Planning Analytics - number of elements in a dimension

Post by Wim Gielis » Thu Mar 08, 2018 12:36 pm

Hello Lotsaram,

Thanks for the information, I already applied it. If you look at the inner DIMSIZ, it will use dimension:hierarchy as an argument.
Maybe it's all about the new paradigm / philosophy of using several hierarchies in 1 dimension, but I am asking myself the question how we can perform an action on every element of a dimension (as we did in the past). Like for example, removing an element from its parents, exporting all elements to a text file, etc.

In questions such as this, we would need to loop over all elements of the dimension. This will require us to loop over all elements in all hierarchies ? And if no alternate hierarchies exist, it boils down to the same as before BUT with clearly more coding needed ?
Since any element can occur in several hierarchies, even with different children and/or weights.
So then I see what I have to change to my coding, I can add up all DIMSIZ'es for each of the hierarchies and I should not take out elements that already occurred in other hierarchies - since any element is different from the same-name element in a different hierarchy.

You seem to understand what I mean, now looking back at our 3 messages.

In the general case, not all leave elements will be used in every hierarchy.

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
Steve Rowe
Site Admin
Posts: 1783
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Planning Analytics - number of elements in a dimension

Post by Steve Rowe » Thu Mar 08, 2018 1:32 pm

Pretty sure this gives the minimum amount of looping.

If you want to work with all N levels irrespective of hierarchy then you can work with the automatically created Leaves hierarchy, this is supposed to contain all the N levels from all the hierarchies.

C levels exist independently in each hierarchy so you can loop over the non-Ns here.

Depends what you are trying to do and what purpose the hierarchies are fulfilling (of course!) how the code works.
In general our code base has to be hierarchy aware, i.e. new functions only.
Work on a single hierarchy at a time, since hierarchies are the new dimension. Dimension are just a containers for hierarchies.
New code to loop over the hierarchies of a dimension as required.

I don't think it is a given that just because the old flatten code used to flatten the entire dimension that the new flatten code has to flatten all the hierarchies in a dimension, that's very much a special case.

Interesting and exciting times (once we get a DBRW....).

Wim Gielis
MVP
Posts: 1748
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Planning Analytics - number of elements in a dimension

Post by Wim Gielis » Thu Mar 08, 2018 2:08 pm

Steve Rowe wrote:
Thu Mar 08, 2018 1:32 pm
Work on a single hierarchy at a time, since hierarchies are the new dimension. Dimension are just a containers for hierarchies
Thanks Steve. I totally agree.

Above I quoted the essence of the whole thing.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Post Reply