Documenting processes and rules

Example files with this article:
  • Document PRO and RUX
  • Power Query, get files from folder
  • Introduction

    With TM1, just like many other customized solutions, it can be worthwile to document your processes and rules, next to your cubes and the main dimensions. More often than not, quite a lot of business logic is translated into code that sits in TI processes and business rules. A proper documentation of the not so obvious pieces of logic is a plus.

    You could do this in several ways, but to start off, it's not difficult at all. Just dump the list of processes and rules into an Excel sheet, add a few columns and you can start.

    • You can copy the process names and cube names in Architect / Perspectives (show the Properties pane) and paste in Excel
    • Or, a simple loop in a TI process could suffice (see below), or the REST API/tm1py
    • Or, Power Query in Excel could be used (see below)
    • Or, a Dir command in DOS or Get-ChildItem (gci) in PowerShell, or use VBA, or AutoHotKey script, ...
    • Or, many more options

    Once you have the lists, you would filter out the files you don't need (for instance, control objects in TM1).

    Hyperlinks for quick access

    The HYPERLINK function in Excel can be used to create, well, a hyperlink to the file. A simple click on the cell will bring you to the file (given access of course). However, as you will notice, Excel will throw a number of warning messages at you, each time you open such a hyperlink. As you know me, I want to get rid of these messages and in the next article, I shall definitely suggest my solution to you.

    What remains is (manually) typing the relevant and needed documentation. Add additional columns with information (like dates, responsibilities, etc.) where applicable.


    To conclude, below you can find my Power Query solution (M-code):

        Source = Excel.CurrentWorkbook(){[Name="tblPath"]}[Content],
        Path = Source{0}[Path],
        GetFiles = Folder.Files(Path),
        #"Filter out control objects" = Table.SelectRows(GetFiles, each not Text.StartsWith([Name], "}")),
        #"Retain only PRO and RUX" = Table.SelectRows(#"Filter out control objects", each ([Extension] = ".pro" or [Extension] = ".rux")),
        #"Sort names" = Table.Sort(#"Retain only PRO and RUX",{{"Extension", Order.Ascending},{"Name", Order.Ascending}}),
        #"Remove columns" = Table.RemoveColumns(#"Sort names",{"Content", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
        #"Renamed Columns" = Table.RenameColumns(#"Remove columns",{{"Extension", "Type"}}),
        #"Replace_PRO" = Table.ReplaceValue(#"Renamed Columns",".pro","TI process",Replacer.ReplaceText,{"Type"}),
        #"Replace_RUX" = Table.ReplaceValue(Replace_PRO,".rux","Rule",Replacer.ReplaceText,{"Type"}),
        #"Final output" = #"Replace_RUX"
        #"Final output"

    The advantage of the Power Query solution can be that the list is easily refreshable. My steps, change as suitable for your situation/documentation exercise:

    • The path for the TM1 model is obtained in the first 2 lines of code: it's the contents of the table (ListObject)
    • Then I ask Power Query to extract all files from the specified folder
    • Filtering out control objects in TM1 (those objects starting with }
    • Leaving only the PRO and RUX files in the output
    • Sorting the resulting files on extension, then filename
    • Throwing out all but 2 columns: filename and extension
    • Reworking extensions to type (.pro becomes "TI process", .rux becomes "Rule")

    Here is a bit of TI code to generate the file names:

    cFile = 'Document filenames.txt';
    TextOutput( cFile, 'Filename', 'Type' );
    # p loops over the TI processes
    p = 1;
    While( p <= Dimsiz( '}Processes' ));
       vProcess = Dimnm( '}Processes', p );
       If( Subst( vProcess, 1, 1 ) @<> '}' );
          If( vProcess @<> GetProcessName );
             TextOutput( cFile, vProcess, 'TI process' );
       p = p + 1;
    # c loops over the application cubes
    c = 1;
    While( c <= Dimsiz( '}Cubes' ));
       vCube = Dimnm( '}Cubes', c );
       If( Subst( vCube, 1, 1 ) @<> '}' );
          If( FileExists( vCube | '.rux' ) = 1 );
             TextOutput( cFile, vCube, 'Rule' );
       c = c + 1;


    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links