Tops-down child/parent dimension export

[image2x]

I thought I'd share the following process as I wasn't able to find anything in the archive that quite fit the bill.

Objectives:
1) For a given consolidated member and its descendants, output:
Child, Parent, Child_Alias, Weight
2) Output in a tops-down manner where parents are always defined before children
3) Support alternative rollups

The last two objectives conflicted with most of the previous export examples I'd read in the forum. They typically worked from the bottoms-up and relied on elpar( pDim, pElem, 1) which isn't going to play well with alternative hierarchies.

To accomplish this, I utilized two approaches that aren't great for performance, but get the job done: ExecuteProcess recursion and an ExecuteCommand system call to append output rather than use ASCIIOUTPUT.

One important callout is that you do need to delete the output file before calling the process:

Code: Select all

# sys_dim_export_child_parent_alias_weight
# 
# Required Parameters
# pDim = DimensionName
# pElem = Starting Consolidated Element
# pAlias = Name of Alias to Include
# pFile =  Filename

numChild = ELCOMPN ( pDim, pElem );
vChildAlias = ATTRS ( pDim, pElem, pAlias );
if ( pParent @<> '' ); 
  vChildWeight = NUMBERTOSTRING ( ELWEIGHT ( pDim, pParent, pElem ) );
else;
  vChildWeight = '0';
endIf;

vOutput = pElem | ',' | pParent | ',' | vChildAlias | ',' | vChildWeight;
# adjust to just 'echo' if using Windows
vCmd =  '/usr/bin/echo "' | vOutput | '" >> "' | pFile | '"';

ExecuteCommand ( vCmd, 1 );

if ( numChild > 0 ); 
  i = 1;
  while ( i <= numChild ) ;
    vChild = ELCOMP ( pDim, pElem, i );
    # recurse 
    ExecuteProcess ( 'sys_dim_export_child_parent_alias_weight',
      'pDim', pdim,
      'pElem', vChild,
      'pParent', pElem,
      'pAlias', pAlias,
      'pFile', pFile
     );
    i = i + 1;
  end;
endIf;

I'd love to hear suggestions on if and how this can be accomplished more efficiently.

-- John

[Wim Gielis]

One important callout is that you do need to delete the output file before calling the process:

Can you use the AsciiDelete function?

[Wim Gielis]

Another function, GetProcessName() to remove the hardcoded TI process name inside of the code.

[image2x]

One important callout is that you do need to delete the output file before calling the process:

Can you use the AsciiDelete function?

Not in this case as the process calls itself repeatedly and the text file needs to persist.

Instead, you need a starting script which does the AsciiDelete and then kicks off the process.

Another function, GetProcessName() to remove the hardcoded TI process name inside of the code.

Yes, thanks. I knew such a function existed and even looked briefly for it before hardcoding the process name.

-- John

[Wim Gielis]

Instead, you need a starting script which does the AsciiDelete and then kicks off the process.

Or one additional parameter, like pMode, set to 1 initially. When doing a next call to the process, set the pMode = 0.
In the code, test if pMode = 1. If yes, AsciiDelete. If not, no AsciiDelete.

[image2x]

Instead, you need a starting script which does the AsciiDelete and then kicks off the process.

Or one additional parameter, like pMode, set to 1 initially. When doing a next call to the process, set the pMode = 0.
In the code, test if pMode = 1. If yes, AsciiDelete. If not, no AsciiDelete.

Yes, good idea. Here's a code update with the incorporated suggestions as well as determining the delimiter character:

Code: Select all

# sys_dim_export_child_parent_alias_weight
# 
# Required Parameters
# pDim = DimensionName
# pElem = Starting Consolidated Element
# pParent = Leave blank 
# pAlias = Name of Alias to Include
# pFile =  Filename
# pDelim = Delimiter character
# pFirst = Always set to 1

if ( pFirst = 1 );
  ASCIIDelete (pFile);
  pFirst = 0;
endIf;

vProcess = GetProcessName;
numChild = ELCOMPN ( pDim, pElem );
vChildAlias = ATTRS ( pDim, pElem, pAlias );
if ( pParent @<> '' ); 
  vChildWeight = NUMBERTOSTRING ( ELWEIGHT ( pDim, pParent, pElem ) );
else;
  vChildWeight = '0';
endIf;

vOutput = pElem | pDelim | pParent | pDelim | vChildAlias | pDelim | vChildWeight;
# adjust to just 'echo' if using Windows
vCmd =  '/usr/bin/echo "' | vOutput | '" >> "' | pFile | '"';
ExecuteCommand ( vCmd, 1 );

if ( numChild > 0 ); 
  i = 1;
  while ( i <= numChild ) ;
    vChild = ELCOMP ( pDim, pElem, i );
    # recurse 
    ExecuteProcess ( vProcess,
      'pDim', pdim,
      'pElem', vChild,
      'pParent', pElem,
      'pAlias', pAlias,
      'pFile', pFile,
      'pDelim', pDelim,
      'pFirst', pFirst
     );
    i = i + 1;
  end;
endIf;

-- John

[Wim Gielis]

Thanks John.

Very very minor point but I would just remove the line "pFirst = 0;" in the IF test, and hardcode the value 0 in the ExecuteProcess statement. I said very very minor point

Regarding the alias: I would leave the option to not use it (leave it empty). Also, you can test whether the provided alias is correct and if not, do not output it in the file.

On another note: can you add the *.PRO file please? That seems easier to download and drop in a TM1 model.

FYI, I haven't been able to run the process. The ExecuteCommand is causing issues. He always says "The system cannot find the file specified." with respect to the ExecuteCommand code.

[image2x]

Hey Wim,

Nice to chat with one of the TM1 legends.

As for the pFirst check, I've actually found it useful to be able to disable the file delete. For example, if you want to export two different consolidated members of the same hierarchy, you run the export on the first member and then pass in pFirst = 0 on the second member export. This way you can selectively choose what to export and, in my case, pass just one file to a downstream system.

Concerning the ExecuteCommand code, in my second post of the updated code, I left out this comment (I'll add it back next):

Code: Select all

# adjust to just 'echo' if using Windows
vCmd =  '/usr/bin/echo "' | vOutput | '" >> "' | pFile | '"';

I am running on Unix and can't test the TI process on Windows. However, I did test the echo command with an append redirect (">>") in a Windows cmd window and the syntax worked.

The no alias option is a good one. I'll add it and will post the .pro the next time I'm back on my company VPN.

-- John

[Wim Gielis]

Nice to chat with one of the TM1 legends.

That's indeed what I always say to myself. You're a TM1 legend Wim, nothing more, nothing less

That option of not deleting the file is a good one!

echo: I changed it to echo, I'm running Win 7. I'll try to play with it again later. Or with your *.PRO file