Exporting and Re-Importing Hierarchical Dimensions

[EddStew]

Hello,

I am trying to export a dimension to a .cma file and then re-import to re-create the same dimension and hierarchy. At present we amend directly within TM1 and I would like to secure the process and make it more robust.

I have exported the code but am having some trouble importing it back into TM1. I have searched the forums and web for the past few days to find something will fulfil this aim.

Does anyone have any generic code for exporting and re-importing hierarchical dimensions?

At present I have managed to export the data into Parent, Child format but am struggling to re-import, this is because for top level items I have listed the same element as the parent and child and the re-import was complaining about consolidated items but I have skipped that and no data is being re-imported into my dimension..

My current import code is:

Code: Select all

pDim = 'Test';
vChild = 'Test';
vParent = 'Test';

if (vParent @= vChild);
ITEMSKIP;
else;
if (DIMIX(pDim, vChild) =0);
  DimensionElementInsert(pDim, '', vChild, 'N');
  DimensionElementComponentAdd(pDim, vParent, vChild, 1.0000);
Endif;
EndIf;

I am new to this and any assistance would be much appreciated.

Thanks,

Edd

[John Hammond]

Think you need to define the .cma as an .xdi and then use perspectives to register it then you will have a version of your dimension in excel which you can use as your master.

I have not tried this myself but I think this is what I would do.

[Steve Rowe]

Hi Edd,
You need to explicitly add the vParent as an element before you can add a child to it.

DimensionElementInsert(pDim, '', vChild, 'N');
DimensionElementInsert(pDim, '', vParent, 'N');
DimensionElementComponentAdd(pDim, vParent, vChild, 1.0000);

Should get you to where you need to be.
Cheers

[EddStew]

Thanks for your responses,

I've added the vParent code and the dimension still does not re-build

Checking the server I can see that the .cma file is still populated with the dimension data even though my dataload process contains the following in the Prolog:

Code: Select all

DimensionDeleteAllElements('Test');

I have amended the export process to list a blank parent to no avail as well...

Any other suggestions?

[Steve Rowe]

Hi Edd,
I'm a bit confused...
You start off talking about needing to reproduce a dimension structure and in the end say that you have no data.

Anyway you should have the cma as the data source, wit the vars named and set to other
Your code goes in the metadata tab, but comment out your assignment of vChild and vParent in the code since this will override the feed from the cma.

If this doesn't ehlp you'll need to give somemore information abot what your trying to do and what is not happening.

Cheers

Code: Select all

pDim = 'Test';
#vChild = 'Test';
#vParent = 'Test';

if (vParent @= vChild);
ITEMSKIP;
else;
if (DIMIX(pDim, vChild) =0);
  DimensionElementInsert(pDim, '', vChild, 'N');
  DimensionElementComponentAdd(pDim, vParent, vChild, 1.0000);
Endif;
EndIf;

[EddStew]

I have managed to get closer to my target, initially the answer was to Insert vParent as a Consolidation element type and not numeric.

This, however, had two minor errors for the two top level elements in the hierarchy. I intentionally created a top level orphan to reflect some of our hierarchies and potential scenarios, I have quickly amended the code too:

Code: Select all

pDim = 'Test';

if (vParent @= vChild);
ITEMSKIP;
else;
if (DIMIX(pDim, vChild) =0);
Endif;
If (vParent @= '');
ITEMSKIP;
Else;
  DimensionElementInsert(pDim, '', vParent, 'C');
Endif;
  DimensionElementInsert(pDim, '', vChild, 'N');
  DimensionElementComponentAdd(pDim, vParent, vChild, 1.0000);
Endif;

The code now does not error but obviously will not show the orphaned element, I need to revise the code around skipping vParent if blank.

The premise is to export dimensions to .cma files, make any necessary adjustments. Upload into a test dimension for checking before then uploading into the live dimension.

[Martin Ryan]

At the risk of reinventing your wheel, here is some (untested) code for exporting and importing the dimension

Process 1: Export. Source data is the dimension subset "All" of the dimension you're exporting. The single variable is called 'elem'.

Code: Select all

# Prolog Tab
fileLoc='C:\temp\myFile.cma';
sDim='NameofDimension';
# Metadata tab
numChildren=elcompn(sDim, elem);
if(numChildren=0);
asciioutput(fileLoc, elem, '');
else;
i=0;
while(i<numChildren);
  i=i+1;
  child=elcomp(sDim, elem, i);
  asciioutput(fileLoc, elem, child);
end;
endif;

this will result in a flat file with each element present at least once, but if it has more than one child, then the element will appear multiple times, along with each of its children.

Process 2: Import. Source is the flat file we just dumped out. Variables are 'parent' and 'child'. You might need weight too, but I've ignored for simplicity

Code: Select all

# Prolog tab
sDim='DimensionName';
nWeight=1;
# Metadata tab
dimensionelementinsert(sDim, '', parent, 'n');
if(child@<>'');
dimensionelementinsert(sDim, '', child, 'n');
dimensionelementcompenentadd(sDim, parent, child, nWeight)
endif;

HTH,
Martin