List TI processes

Example files with this article:
  • TM1 list TI processes
  • Introduction

    Last two blog articles dealt with documenting a TM1 model and backing up a TM1 model. This article and the next will continue on the same topics, focused on TI processes (Turbo Integrator). Specifically, this article will show you a strategy to list all TI processes of a given TM1 model. The next article will endeavour to backup source files for TI processes.

    Data sources

    TI processes can have a number of different data sources. The most widely used are:

    1. text files (.csv, .cma, .txt, …);
    2. TM1 cube views or TM1 dimension subsets;
    3. ODBC (relational tables);
    4. None (the process is scripted).

    VBA code

    What follows now, is custom VBA code to list processes and some of its details:

    Sub List_TI_Processes()
    ' Wim Gielis '
    ''''' ' VBA-code to list TI processes ' 08/10/11 '''''
    Const FolderName = "D:\TM1DATA" 'CHANGE TO SUIT ActiveSheet.UsedRange.Offset(1).ClearContents fName = Dir(FolderName & "*.pro") Do While Len(fName) If Right(fName, 4) = ".pro" Then lRow = Range("A" & Rows.Count).End(xlUp).Row + 1 Range("A" & lRow) = Split(fName, ".pro")(0) Open FolderName & fName For Input As #1 sProcessText = Input(LOF(1), #1) Close #1 Range("B" & lRow) = GetInfo(sProcessText, 562) Range("C" & lRow) = GetInfo(sProcessText, 586) Range("D" & lRow) = GetInfo(sProcessText, 585) Select Case Range("B" & lRow) Case "VIEW" 'view name Range("E" & lRow) = GetInfo(sProcessText, 570) Case "SUBSET" 'subset name Range("E" & lRow) = GetInfo(sProcessText, 571) Case "CHARACTERDELIMITED": On Error Resume Next 'file date time Range("F" & lRow) = FileDateTime(Range("C" & lRow)) 'file size Range("G" & lRow) = Round(CreateObject("Scripting.FileSystemObject"). _ GetFile(Range("C" & lRow)).Size / 1024, 2) On Error GoTo 0 End Select End If fName = Dir() Loop Columns.AutoFit [A1].CurrentRegion.Sort [A1], 1, Header:=xlYes
    End Sub

    You will notice that I used 1 custom function, here it is as well:

    Function GetInfo(sText, sID) As String
    Const DQ = """" Const Ret = vbCrLf GetInfo = Replace(Split(Split(sText, Ret & sID & ",")(1), Ret)(0), DQ, "")
    End Function

    Ease the work

    You can download an example file including the code on top of the article. When using the code, do not forget to change the constants in the code to your own TM1 model.


    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links