'Flipping' Element and Alias in Accounts Dimension

Post Reply
John Hammond
Community Contributor
Posts: 295
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: PAW/PAX 2.0.72 Perspectives
Version: TM1 Server 11.8.003
Excel Version: 365 and 2016
Location: South London

'Flipping' Element and Alias in Accounts Dimension

Post 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.
Last edited by John Hammond on Tue Dec 22, 2009 7:00 pm, edited 1 time in total.
ajain86
Community Contributor
Posts: 132
Joined: Thu Oct 15, 2009 7:45 pm
OLAP Product: TM1
Version: 9.4.1 9.5 9.5.1
Excel Version: 2003 2007

Re: 'Flipping' Element and Alias in Accounts Dimension

Post 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;
Ankur Jain
John Hammond
Community Contributor
Posts: 295
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: PAW/PAX 2.0.72 Perspectives
Version: TM1 Server 11.8.003
Excel Version: 365 and 2016
Location: South London

Re: 'Flipping' Element and Alias in Accounts Dimension

Post 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.
ajain86
Community Contributor
Posts: 132
Joined: Thu Oct 15, 2009 7:45 pm
OLAP Product: TM1
Version: 9.4.1 9.5 9.5.1
Excel Version: 2003 2007

Re: 'Flipping' Element and Alias in Accounts Dimension

Post 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
Ankur Jain
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: 'Flipping' Element and Alias in Accounts Dimension

Post 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
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
John Hammond
Community Contributor
Posts: 295
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: PAW/PAX 2.0.72 Perspectives
Version: TM1 Server 11.8.003
Excel Version: 365 and 2016
Location: South London

Re: 'Flipping' Element and Alias in Accounts Dimension

Post 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.
John Hammond
Community Contributor
Posts: 295
Joined: Mon Mar 23, 2009 10:50 am
OLAP Product: PAW/PAX 2.0.72 Perspectives
Version: TM1 Server 11.8.003
Excel Version: 365 and 2016
Location: South London

Re: 'Flipping' Element and Alias in Accounts Dimension

Post 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 ;


User avatar
garry cook
Community Contributor
Posts: 209
Joined: Thu May 22, 2008 7:45 am
OLAP Product: TM1
Version: Various
Excel Version: Various

Re: 'Flipping' Element and Alias in Accounts Dimension

Post 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.
User avatar
kwijibo
Posts: 15
Joined: Mon Aug 02, 2010 10:17 am
OLAP Product: TM1, Cognos Express
Version: [9.4.1] [9.5.1] [9.5.2] [10.1]
Excel Version: [2003] [2007]

Re: 'Flipping' Element and Alias in Accounts Dimension

Post 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
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: 'Flipping' Element and Alias in Accounts Dimension

Post 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!
User avatar
kwijibo
Posts: 15
Joined: Mon Aug 02, 2010 10:17 am
OLAP Product: TM1, Cognos Express
Version: [9.4.1] [9.5.1] [9.5.2] [10.1]
Excel Version: [2003] [2007]

Re: 'Flipping' Element and Alias in Accounts Dimension

Post 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
st2000
Posts: 62
Joined: Mon Aug 15, 2016 8:48 am
OLAP Product: TM1 (Windows) & SSAS 2014 Ent.
Version: 10.2.0 FP3
Excel Version: Excel 2013
Location: Hamburg, DE, EU
Contact:

Re: 'Flipping' Element and Alias in Accounts Dimension

Post by st2000 »

With 10.2.0 it works fine.
-----------------------------------
Best regards,
Stefan
Sighurd
Posts: 30
Joined: Mon Oct 03, 2011 9:18 pm
OLAP Product: TM1
Version: 10.2.2fp6
Excel Version: 2016

Re: 'Flipping' Element and Alias in Accounts Dimension

Post by Sighurd »

One more option:
Bedrock.Dim.Attr.SwapAlias
Post Reply