TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

'Flipping' Element and Alias in Accounts Dimension

https://www.tm1forum.com/viewtopic.php?t=1942

Page 1 of 1

'Flipping' Element and Alias in Accounts Dimension

Posted: Tue Dec 22, 2009 4:15 pm
by John Hammond
We have an accounts dimension that has

Element - AccountDescription eg 'Cost of Sales' (Technically speaking the 'Principal Element' )
Alias - AccountCode eg '501600'

We wish to flip the dimension so we have

Element - AccountCode
Alias - AccountDescription

As far as we can see there obvious repercussions in that

1. All cubes that reference this dimension will have to be re-created (dumped or re-ETL'ed)
2. All rules that reference this dimension will have to have the accountcode substituted for the accountdescription.
3. All TI processes that reference this dimension will have to be recoded with the correct primary element name.

Looking away from these repercussions and simply concentrating on the practicalities of 'flipping' the actual dimension.

We have a multilevel hierarchy in our account structure with several alternate hierarchies for example

Code: Select all


1. Stock Provisions 

net assets - total assets - inventory - stock and WIP Provisions - stock provisions 

2. Stock Provisions

all provisions + stock provisions 
               |
               + liability provisions
Here stock provisions is part of the trial balance structure but is also part of another structure where we look at provisions as a whole.

My plan is to dump and reload the dimension using the follow pseudo code:

Code: Select all

Procedure Main
Forall hierarchy_starts (ie loop through dimension finding elements without parents ) 
      write dummy parent AccountCode AccountDescription, child  AccountCode AccountDescription to sequel table with weighting and all other attributes of child
      call output_hierarchy(hierarchy_start)
Endfor

Procedure output_hierarchy 

Count number of children 
if childrencount  = 0 then 
   return 
else 
  Forall children   
      write parent AccountCode AccountDescription, child  AccountCode AccountDescription to sequel table with weighting and all other attributes of child
       call output_hierarchy(child)
   Endfor
Endif
Once the sequel table is written out it will be a simple matter of maintaining the output order and then reinputting the dimension with a very simple TI process. (It could be a flat file but sequel table will allow any other manipulation that might arise).

Now I am sure this will work but I have a few questions to ask to the more experienced TM1'ers out there:

1. Is there an easier way of doing the flip?
2. Are there any pitfalls in flipping that people have encountered on their TM1 travels?
3. Is recursion as required by output_hierarchy possible in TI?

Thanks to all on forum so far. All responses gratefully received and acknowledged.

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Tue Dec 22, 2009 5:43 pm
by ajain86
Here is my code that works pretty well in extracting a dimension. I have modified it for your purpose of flipping the alias and member name.
I have never had to do anything like this but have to rebuild models plenty of times. This process has been useful in creating a file for dimensions that were once created on the fly.

I personally like to create a new TM1 server when doing rebuilding, this way I can have the old version available to me at all times.

The following code is placed in the prolog of a TI process:

Code: Select all

#==================================================================================================
# Creates file that can be used to recreate the dimension
# File output: Parent, Child, Alias, Weight
# Using to flip the Member name and Member Alias
# Top level output is Dimension, Alias, Member, 0
# >=1 parent output is Parent Alias, Child Alias, Child Member, Weight
#==================================================================================================

# Dimension Name
sDim = 'period';

# Dimension Size
nSize = DIMSIZ ( sDim );

# Output File Name
sFile = sDim | '.txt';

# Count Variables
nX = 1;

# File header
ASCIIOutput ( sFile, 'PARENT', 'CHILD', 'ALIAS', 'WEIGHT' );

# Loop through the dimension
While ( nX <= nSize );

# Member Info
# Member name, alias, number of parents
sMem = DIMNM ( sDim, nX );
sAlias = ATTRS ( sDim, sMem, 'Alias' );
nPar = ELPARN ( sDim, sMem );

# If top level member, then use dimension name as parent. 
If ( nPar = 0 );

# output to file
ASCIIOutput ( sFile, sDim, sAlias, sMem, '0' );

# If 1 parent
elseif ( nPar = 1 );

# Get parent details
sParMem = ELPAR ( sDim, sMem, 1 );
sParAlias = ATTRS ( sDim, sParMem, 'Alias' );
nWeight = ELWEIGHT ( sDim, sParMem, sMem );
sWeight = NumberToString ( nWeight );

# output to file
ASCIIOutput ( sFile, sParAlias, sAlias, sMem, sWeight );

# If >1 parent
elseif ( nPar > 1 );

# Count Variable
nY = 1;

# Loop through the parents
While ( nY <= nPar );

# Get parent details
sParMem = ELPAR ( sDim, sMem, nY );
sParAlias = ATTRS ( sDim, sParMem, 'Alias' );
nWeight = ELWEIGHT ( sDim, sParMem, sMem );
sWeight = NumberToString ( nWeight );

# output to file
ASCIIOutput ( sFile, sParAlias, sAlias, sMem, sWeight );

# increment Parent count variable
nY = nY + 1;

# end Loop
END;

# end If statement
endif;

# increment Index count variable
nX = nX + 1;

# end Loop
END;

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Tue Dec 22, 2009 7:25 pm
by John Hammond
Thanks Ajain for coming back to me with your code. I will certainly use some of the principles outlined.

Your code indicates a bottom upwards traversal of the tree rather than my top down approach. This is a legimate approach since alternate hierarchies build up a network and that can be traversed as a number of trees top down or bottom up provided all the nodes are visited.

There are a couple of problems with your approach .

1. Your code will only handle a two level hierarchy. You would require additional nested while loops to handle higher levels of parents.

2.Once you go beyond two levels in the hierarchy if you were to code additional while statements the parent child pairs are not emitted in the natural order you would use to rebuild the tree unlike the top down approach.

But the nested while loops to handle stuff down to a certain level would be a way of doing things if recursion does not work.

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Tue Dec 22, 2009 7:53 pm
by ajain86
John,

My while loop to handle multiple parents for a child should give you the alternate hierarchies. I have used the code to recreate dimensions that have more than 5 levels with multiple alternate hierarchies.

Anyways, I hope my code can help you in your work and welcome any issues you find with it.

- Ankur

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Wed Dec 23, 2009 12:06 am
by Martin Ryan
John Hammond wrote: 1. Is there an easier way of doing the flip?
2. Are there any pitfalls in flipping that people have encountered on their TM1 travels?
3. Is recursion as required by output_hierarchy possible in TI?
Hi John,

1. My method for this type of task was to export the elements into one ascii file and the relationships in a second file, in the format "child, parent". Attributes were in a third file. To get your relationships this way it means setting your TI source as the subset "All" for your dimension then cycling through the parents (or children, makes no difference) of each element and spitting them out. Your metadata code would be:

Code: Select all

numPar=elparn(dim, elem);
i=0;
while(i<numPar);
  i=i+1;
  par=elpar(dim, elem, i);
  asciioutput(fileLocation, elem, par);
end;
2. Just the ones you've mentioned, though you don't have to change the rule references where aliases are used as TM1 will cope with either.
3. Not easily, the only way to do recursion in TI is to have one TI process call another. This can be a little slow, so I would try to avoid it on an enormous dimension.

HTH,
Martin

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Wed Dec 23, 2009 7:12 pm
by John Hammond
Thanks Martin for your assistance - probably going to go with the separate element information and element structure routine now - it means the details of each element are only output once.

In terms of recursion the classic factorial coded in TM1 seems to work

Code: Select all

# factorialcontrol 
NumericGlobalVariable('result');
result = 1 ;
ExecuteProcess('factorial', 'P1', P1);

Code: Select all

#factorial 

NumericGlobalVariable('result');

IF (P1 > 1) ;
   result = result *  P1 ;
   ExecuteProcess('factorial', 'P1', P1 - 1);

   asciioutput('c:\prolog'   | numbertostring(p1) |  '.txt' ,'factorial of ' | numbertostring(p1) |  ' is ' |  numbertostring(result) ) ;

ENDIF ;
Now to try the tree walk.

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Sat Jan 09, 2010 3:55 pm
by John Hammond
Eventually got my code working.

Did not need a recursive procedure after all. Apologies to Ankur for saying his code did not cope with more levels than 2 - once I looked at it I saw it was fine.

Here is the code FYI with no warranty etc.

Prolog of proc1 - based on Wim Gelis' dimension copier - thanks Wim

Code: Select all


IF(LONG(pDimensionNew)=0);
	pDimensionNew=pDimensionOld | '_Copy';
ENDIF;

# this does not work because of various commit problems. You will have to delete new dimension manually in UI or it may require an alldatasave to be e
xecuted.
# deleting the dimension from the UI cascades remova of  the attributes but programmatically both have to be removed explicitly.
CubeDestroy('}ElementAttributes_' |  pDimensionNew);
DimensionDestroy('}ElementAttributes_' |  pDimensionNew);
DimensionDestroy(pDimensionNew);

IF ( DIMENSIONEXISTS(pDimensionOld) = 0 ) ;
                asciioutput('c:\prolog.txt','Old dimension does not exist' ) ;
	PROCESSQUIT;
ENDIF;

# old dimension must have attributes - should also check attribute exists
IF ( DIMENSIONEXISTS('}ElementAttributes_' | pDimensionOld) <>1 ) ;
	asciioutput('c:\prolog.txt','Old dimension has no attributes' ) ;
	PROCESSQUIT;
ENDIF ;


# loop through attributes to check if we have alias
j=1;
vFoundAliasOld = 0 ;
WHILE (j<=DIMSIZ('}ElementAttributes_'|pDimensionOld));

	vAttributeName=DIMNM('}ElementAttributes_' |  pDimensionOld,j);
	vAttributeType=SUBST(DTYPE('}ElementAttributes_'|pDimensionOld, vAttributeName),2,1);

	# insert attribute itself but we replace the old alias with the new
	IF (vAttributeName @= pDimensionOldAlias & vAttributeType @= 'A' );
                       vFoundAliasOld = 1 ;
	ENDIF ;
               j  = j + 1 ;
END ;

# old dimension must have attributes - should also check attribute exists
IF ( vFoundAliasOld <>1 ) ;
	asciioutput('c:\prolog.txt','Old dimension does not have Alias=' |  pDimensionOldAlias  ) ;
	PROCESSQUIT;
ENDIF ;


# Use this for testing. NB no sort order for new dimension will be set it will be set to manual
# DimensionSortOrder(pDimensionOld, 'ByName', 'Ascending', 'ByLevel' , 'Ascending');

DIMENSIONCREATE(pDimensionNew);


# loop through the elements of the original dimension
# to copy to the new dimension
i=1;
relationcount = 0 ;
hierarchycount = 0 ;

WHILE(i<=DIMSIZ(pDimensionOld));

	# CHILD
	vChildElement=DIMNM(pDimensionOld,i);
	vChildType=DTYPE(pDimensionOld,vChildElement);
	vChildAlias = ATTRS (pDimensionOld, vChildElement, pDimensionOldAlias );
                # if the default value of the alias (same as element) is not overridden it is stored as an empty string so set it to the element
	IF (vChildAlias @= '' ) ;
		vChildAlias = vChildElement ;
	ENDIF ;
                vChildLevel =  ELLEV(pDimensionOld, vChildElement) ;

	asciioutput('c:\prolog.txt',numbertostring(i) | ' Child  Element ' | ' Level=' | numbertostring(vChildLevel) | ' Type=' | vChildType |  ' [' |  vChil
dElement | '] Replaced with [' | vChildAlias | ']' ) ;
	DIMENSIONELEMENTINSERT(pDimensionNew,'',vChildAlias,vChildType);

	# PARENT (loop through them)
	j=1;
                parentcount = ELPARN(pDimensionOld,vChildElement) ;
                IF  (parentcount = 0 ) ;
                               hierarchycount = hierarchycount + 1 ;

        	               asciioutput('c:\prolog.txt','%%% Hierarchy Start #' | numbertostring(hierarchycount) | ' [' |  vChildElement | '] / [' | vChil
dAlias | ']  level=' | numbertostring(vChildLevel) | ' Type=' | vChildType) ;
               ENDIF ;

	WHILE( j <= parentcount );
		vParentElement = ELPAR(pDimensionOld,vChildElement,j);
		vChildWeight = ELWEIGHT(pDimensionOld,vParentElement,vChildElement);
		vParentAlias = ATTRS(pDimensionOld, vParentElement, pDimensionOldAlias );

		# if the default value of the alias (same as element) is not overridden it is stored as an empty string so set it to the element
		IF (vParentAlias @= '' ) ;
			vParentAlias = vParentElement ;
		ENDIF ;
		asciioutput('c:\prolog.txt',' --- Parent Element #' | numbertostring(j)  | ' [' |  vParentElement  | '] Replaced with [' | vParentAlias | ']' ) ;
		DIMENSIONELEMENTINSERT(pDimensionNew,'',vParentAlias,'C');
		relationcount = relationcount + 1 ;
		asciioutput('c:\prolog.txt',' ------ Parent/Child rel #' | numbertostring(relationcount)  | ' [' | vParentAlias | '] / [' | vChildAlias | ']') ;
		DIMENSIONELEMENTCOMPONENTADD(pDimensionNew,vParentAlias,vChildAlias,vChildWeight);
		j=j+1;
	END;

	i=i+1;

END;
and the epilog which copies the attributes

Code: Select all

# loop through attributes

vAttributeCount = DIMSIZ('}ElementAttributes_'|pDimensionOld);
asciioutput('c:\epilog.txt','Count of Attributes to be processed ' | numbertostring(vAttributeCount) ) ;
j=1;

WHILE (j<= vAttributeCount);
	# process attributes in reverse order so they have the same order as original
	vAttributeName=DIMNM('}ElementAttributes_' |  pDimensionOld,vAttributeCount - j + 1 );
	vAttributeType=SUBST(DTYPE('}ElementAttributes_'|pDimensionOld, vAttributeName),2,1);

	# insert attribute itself but we replace the old alias with the new
	IF (vAttributeName @= pDimensionOldAlias);
		asciioutput('c:\epilog.txt','+++ Creating alias ' | pDimensionNewAlias | ' Type A' ) ;
		ATTRINSERT(pDimensionNew,'',pDimensionNewAlias,'A');
	ELSE ;
		asciioutput('c:\epilog.txt','+++ Creating alias ' | vAttributeName | ' Type ' | vAttributeType ) ;
		ATTRINSERT(pDimensionNew,'',vAttributeName, vAttributeType);
	ENDIF ;


	i=1;

	WHILE (i<=DIMSIZ(pDimensionNew));

		# nb both dimensions will not have the same order - see note about sorting
		vElementNew=DIMNM(pDimensionNew,i);
                                # use the alias to find the old element
                                 vElementOld=DimensionElementPrincipalName( pDimensionOld, vElementNew ) ;

		# update attribute values for the element
		IF(vAttributeType@='N');
			vNumericAliasOld = ATTRN(pDimensionOld,vElementOld,vAttributeName) ;
			asciioutput('c:\epilog.txt',numbertostring(i) | ' Type=N [' | numbertostring(vNumericAliasOld) | '] for Element [' | vElementNew | ']' ) ;
			ATTRPUTN(vNumericAliasOld,pDimensionNew,vElementNew,vAttributeName);
		ELSE;
			IF (vAttributeName @= pDimensionOldAlias);


				vAliasOld = vElementOld ;
				asciioutput('c:\epilog.txt',numbertostring(i) | ' Type=' | vAttributeType | ' [' | vAliasOld | '] for Element [' | vElementNew | ']'  ) ;
				ATTRPUTS(vAliasOld,pDimensionNew,vElementNew,pDimensionNewAlias);
			ELSE ;
				vAliasOld = ATTRS(pDimensionOld,vElementOld,vAttributeName) ;
				asciioutput('c:\epilog.txt',numbertostring(i) | ' Type=' | vAttributeType | ' [' | vAliasOld | '] for Element [' | vElementNew | ']'  ) ;
				ATTRPUTS(vAliasOld,pDimensionNew,vElementNew,vAttributeName);
			ENDIF ;
		ENDIF;

		i=i+1;
	END;

	j=j+1;
END;
Finally this recreates the non MDX subsets

Code: Select all


Pathname =  pDimensionOld | '}subs\*.sub' ;
PriorFileName = '' ;

SubsetNameOld = WildcardFileSearch( Pathname, PriorFilename);
PriorFileName = SubsetNameOld ;
i = 1 ;
WHILE (PriorFileName @<> '' ) ;
     #
     IF (SCAN('$',SubsetNameOld)  =  LONG(  SubsetNameOld)  ) ;
          asciioutput('c:\prolog.txt','+++ MDX based subset cannot be copied automatically  [' | SubsetNameOld  | ']') ;
     ELSE ;
          SubsetOld =  SUBST(SubsetNameOld,1,LONG(  SubsetNameOld) - 4 ) ;
          asciioutput('c:\prolog.txt','+++  [' | SubsetOld  | ']') ;
           SubsetOldSize =   SubsetGetSize(pDimensionOld, SubsetOld);

          SubsetDestroy(pDimensionNew, SubsetOld);
          SubsetCreate(pDimensionNew, SubsetOld);
           j = 1 ;
          WHILE ( j <= SubsetOldSize ) ;
                SubsetOldElement =    SubsetGetElementName(pDimensionOld, SubsetOld,  j);
                asciioutput('c:\prolog.txt','Element ' | SubsetOldElement ) ;
                SubsetOldAlias = ATTRS (pDimensionOld, SubsetOldElement , pDimensionOldAlias );
                # if the default value of the alias (same as element) is not overridden it is stored as an empty string so set it to the element
	IF (SubsetOldAlias @= '' ) ;
		SubsetOldAlias = SubsetOldElement  ;
	ENDIF ;
                asciioutput('c:\prolog.txt','Alias ' | SubsetOldAlias ) ;
                SubsetElementInsert(pDimensionNew, SubsetOld, SubsetOldAlias,  j);

                j = j + 1 ;
                IF ( j > 10000 ) ;
                    PROCESSQUIT ;
                ENDIF ;

           END ;
     #
     ENDIF ;
     SubsetNameOld = WildcardFileSearch( Pathname, PriorFilename);
     PriorFileName = SubsetNameOld ;
    # safety feature
     i = i + 1 ;
    IF ( i > 1000 ) ;
        PROCESSQUIT ;
    ENDIF ;
END ;

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Mon Jan 11, 2010 10:09 am
by garry cook
Just incase anyone searches for this in the future, maybe I'm missing something obvious here (it is a Monday morning and I'm in the middle of supporting the same training session for the 18th time so brain may not yet be fully engaged) but the other obvious way of doing it that nobody seems to have mentioned (or at least it appears so on a scan read) is to simply export the dimension worksheet, DBRA in a seperate column, repaste over column B and resave to the server.

Obviously it's limited to dims small enough to fit in excel's 65k line limit though.

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Mon Sep 27, 2010 1:38 pm
by kwijibo
Hi there,

although this thread is old, i want to mention the easy way of doing the flip.
Just use the undocumented TM1 function SwapAliasWithPrincipalName(Dimension,'new',0);

Here is some detail on it:
http://www.bihints.com/renaming_elements

Regards
Nils

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Tue Sep 28, 2010 12:27 pm
by lotsaram
kwijibo wrote:Hi there,

although this thread is old, i want to mention the easy way of doing the flip.
Just use the undocumented TM1 function SwapAliasWithPrincipalName(Dimension,'new',0);

Here is some detail on it:
http://www.bihints.com/renaming_elements

Regards
Nils
Choice! Never noticed this before but it works!

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Fri Dec 17, 2010 3:01 pm
by kwijibo
Use that funcion with special care!
I tested this on TM1 9.5.1 an 9.4.
When using SwapAliasWithPrincipalName(...) the object's name is apparently changed.
But the only thing that is changed is the display name and not really the internal ID.
I encountered the issue, that I 'renamed' an element through the function, deleted the alias entries and tried to create a new element with the old name of the renamed element.
That was not possible. Instead it lead to an error saying that this element already exists in that dimension.

Perhaps anyone can confirm this.

Best regards
Nils

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Wed Aug 23, 2017 11:14 am
by st2000
With 10.2.0 it works fine.

Re: 'Flipping' Element and Alias in Accounts Dimension

Posted: Wed Aug 23, 2017 12:08 pm
by Sighurd
One more option:
Bedrock.Dim.Attr.SwapAlias
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited