Exporting Dimension into flatfile/SQL Server Datbase.

[santosh361]

Hello everyone,

I would like to export Dimension & Cubes to Relational Datbase to use for reporting purpose.
I am trying to export TM1 dimension to flatfile before exporting to SQL Server DB. Gatherd some code in this forum and modified it to use as unique code when I have different levels in the dimension.

Please provide your comments on below code.

Code: Select all

Dim = 'Product';
Path = 'C:\Honey\TM1dim2.csv';
DimSize = DIMSIZ(Dim);
i = 1;
p = 1;
#Loop through each element
WHILE (i <=DimSize);
Element = DIMNM(Dim,i);
NumParent = ELPARN(Dim,Element);
IF (NumParent = 0);
ASCIIOUTPUT(Path,Element,Element); 
ELSE;
#Ouput every parent in multiple hierarchies
WHILE (p<=NumParent);
Parent=ELPAR(Dim,Element,p);
parent1=elpar(dim,Parent,p);
parent2=elpar(dim,parent1,p);
if(ellev(Dim,parent2) = 3 ) ;
ASCIIOUTPUT(Path,Element,Parent,parent1,parent2);
endif;
p = p +1;
END;
p = 1;
ENDIF;
i = i +1;
END;

Thanks,
Santosh

[Martin Ryan]

What kind of comments are you looking for? At first glance the code looks like a perfectly reasonable way to handle the problem. Are you having a problem with it? If so, what?

Martin

[jstrygner]

Hi,

Looking to your post and your code I have following assumptions:

• - you want to have as many rows, as many leaf elements you have in dimension (because finally you plan to have the structure in RDB),
  - for each of such leaf elements you want to have a list of following direct parents to the very top of the hierarchy (becomes tricky if any element could have more than one direct parent).

Anyway, I think you will find this code helpful:

Code: Select all

# Parameter to decide if only leaf elements should get exported
nListLeavesOnly = 1;
# Initial variables set
sDim = 'Product';
sDelimiter = ';';
sPath = 'C:\Honey\TM1dim2.csv';
# Iteration trhough elements
nDimSize = DimSiz ( sDim );
nElement = 1;
While ( nElement <= nDimSize );
  # Determine element name
  sElement = DimNm ( sDim, nElement );
  # Determine if for this element export should be performed
  If ( nListLeavesOnly = 0 %
      ( nListLeavesOnly = 1 & DType ( sDim, sElement ) @<> 'C' ) );
    sOutputRowContent = sElement;
    sCurrentChild = sElement;
    While ( ElParN ( sDim, sCurrentChild ) > 0 );
      sParent = ElPar ( sDim, sCurrentChild, 1 );
      sOutputRowContent = sOutputRowContent | sDelimiter | sParent;
      sCurrentChild = sParent;
    End;
    ASCIIOutput ( sPath, sOutputRowContent );
  EndIf;
  nElement = nElement + 1;
End;

Additional comments:

• If any of elements will have more than one direct parent, the output will give you always only the "first" one.
  Remember in TM1 you can have a consolidated element with no children - exporting leaves only will in such a case ignore such an element and maybe you do not want this.

If something was misunderstood by me, it would be good if you give exact example of your structure and the output you are expecting.

And please be aware I had no option to compile and test this code, so there might be some typos, or maybe even logical errors, but I think even then you should be able to come up with how to fix it.

Regards.