wimgielis.com

Recreating a TM1 dimension after an export

• Sep. 01, 2013

Introduction

TM1 shows us the option to export a dimension as a CMA text file. While this could be useful, obviously we need a way to import the file again to create or recreate a dimension. Here is the option to export a dimension:

You will create a CMA text file. To read the file using a Turbo Integrator process, is not all that evident if you look at the file contents:

What we observe is a number of possibilities:

• an N type element, followed by a comma and the name of the leaf element
• a C type element, followed by a comma and the name of the consolidated element
• an S type element, followed by a comma and the name of the string element
• a comma, followed by the name of an element (type N or C), followed by a comma, followed by the weight of the element in the element on the previous line of the text file

(Notice that only the dimension elements are exported, not aliases or other attributes.)

Turbo Integrator to load the text file

Let us now show the Turbo Integrator process to set up a dimension based on the text file. Start a new TI process and point to the generated text file using a comma as the field delimiter. Name the variables Type, Element and Weight, respectively (2 string elements and 1 numeric element). Now I show you the parameters to the process:

Parameter	Type	Default Value	Prompt Question
pDim	String		Dimension name ?
pFilename	String		File to be loaded ? (including full path)

The Advanced > Prolog tab contains the usual code to handle a new or existing dimension:

# Wim Gielis
# https://www.wimgielis.com
#####
# (Re)creating a dimension based on an export
# 09/01/13
#####
vDim = pDim;

# Parameter for the file to be loaded: it should also contain the path if the path is different than the TM1 Data Directory
# In case an invalid filename is supplied, I try a filename made up of
# the chosen dimension name in the TM1 Data Directory with extension .cma
If( FileExists ( pFilename ) > 0 );
   vFilename = pFilename;
ElseIf( FileExists ( vDim | '.cma' ) > 0 );
   vFilename = vDim | '.cma';
Else;
   DataSourceType = 'NULL';
   ProcessError;
EndIf;

# set the data source
DataSourceType = 'CHARACTERDELIMITED';
DatasourceNameForClient = vFilename;
DatasourceNameForServer = vFilename;

# dimension name parameter
If( DimensionExists ( vDim ) = 0 );
   DimensionCreate( vDim );
Else;
   DimensionDeleteAllElements( vDim );
   # (or use a generic TI process to unwind the dimension)
EndIf;

After the Prolog, the Metadata tab where the meat of the process is found:

# Wim Gielis
# https://www.wimgielis.com
#####
# (Re)creating a dimension based on an export
# 09/01/13
#####

If(Type@='C');
   vParent=Element;
   DimensionElementInsert(vDim, '', vParent, 'C');

ElseIf(Type@='');
   DimensionElementInsert(vDim, '', Element, 'N');
   DimensionElementComponentAdd(vDim, vParent, Element, Weight);

ElseIf(Type@='N');
   DimensionElementInsert(vDim, '', Element, 'N');

ElseIf(Type@='S');
   DimensionElementInsert(vDim, '', Element, 'S');

EndIf;

You can nicely see the use of the variable vParent, which is not a part of the Ascii text file, but which is retained from vParent of the previous line. We know that the Metadata tab is executed for each line of the data source and that TM1 will memorize the variables’ contents. We make use of this feature here.

A little bit of VBA code

While doing the research for this article, I found that the export in the text file shows strange characters. Here is what I did:

Sub ReadInTM1dim_error()
' Wim Gielis
' https://www.wimgielis.com
'''''
' VBA code to read in the file contents
' 06/30/12
'''''
    Open "C:\model.cma" For Input As #1
    MsgBox UBound(Split(Input(LOF(1), #1), vbLf))
    Close #1
End Sub

Oops, this throws an "error 62 input past end of file" while I attempt to read the file:

To cut a long story short, I read the file in in a binary way. This works fine when it comes to reading the file contents. After that, I parse the contents and skip all non-printable characters based on Ascii number. Lastly, I remove the .000000 in the weights that TM1 graciously but uselessly offers:

Sub ReadInTM1dim_correct()
' Wim Gielis
' https://www.wimgielis.com
'''''
' VBA code to read in the file contents
' 06/30/12
'''''
    Open "C:\model.cma" For Binary As #1
    sq = Split(Input(LOF(1), #1), vbLf)
    Close #1

    For i = 0 To UBound(sq)
        sOutput = ""
        For j = 1 To Len(sq(i))
            vChar = Mid(sq(i), j, 1)
            If Asc(vChar) > 31 And Asc(vChar) < 169 Then sOutput = sOutput & vChar
        Next
        sq(i) = Replace(sOutput, ".000000", "")
    Next
    Columns(1).ClearContents
    Cells(1).Resize(UBound(sq) + 1) = Application.Transpose(sq)

End Sub

Note the "Open method For Binary" instead of Input. Like that, we can successfully parse the CMA text file. Of interest too, is the fact that trying to remove .000000 does not work on the (potentially large) string Input(LOF(1), #1). And now you know why: that string .000000 does not exist as such, but contains all kinds of non-printable characters. My check on Ascii code number removes these excessive characters. As I demonstrated earlier in this article, do not bother with these characters if you only use a Turbo Integrator approach to read the file contents.

• « Previous TM1 article
• Next TM1 article »