wimgielis.com

List TI processes

• Oct. 08, 2011

	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
' https://www.wimgielis.com
'''''
' 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.

• « Previous TM1 article
• Next TM1 article »