Set up a TM1 service with AutoHotKey

Introduction

In an earlier article on this website I blogged about running a TM1 model in different ways:

  1. TM1 runs as a service
  2. TM1 runs as an application
  3. TM1 runs as local server

This article focuses on running TM1 as an application, though many of the concepts can also be used for the other 2 options.

You also read about me using an automation tool called AutoHotKey. In fact, over the years of TM1 consultancy, I have set up so many models as applications that I wanted to automate the steps to do so. This involves:

  1. setting up a tm1s.cfg file
  2. setting up a database directory
  3. setting up a logging directory
  4. setting up a shortcut to launch the model

So 1 and 1 makes 2, I wrote my own AutoHotKey script to help me setting up a TM1 server as an application. In general, you would do so for different reasons, but the main reason is the temporary character:

  1. when I need to answer a query from my consultancy customers, I could fire up an older backup to check and validate
  2. for a smaller POC, you could suffice with an application
  3. to retrieve data from a cube that is located in a differen folder (for instance a backup) a temporary model will suffice
  4. etc.

Usually though you would set up a TM1 model as a service. The most important reason is that services are not as likely to be turned off as applications. If a user logs off his application will be stopped too and so does the TM1 model.

I posted my code on the TM1 forum (in the Useful code, tips and tricks section). Once you set up the tool (at least some knowledge of AHK is needed but not very much), please go to Windows Explorer and start the tool. I assign the shortcut Alt + F12 but you choose what you like best.

The first action of the tool is to navigate to the folder containing the tm1s.cfg file. The script contains a number of options, for example create a tm1s.cfg file if it does not exist, select the data directory by clicking the folder name, assigning random port numbers, create custom folders, etc. Communication with the user is organized via Inputboxes, Msgboxes and file explorers to select a folder.

To sum up, the rationale behind this script is saving time and effort because of typo's, repetitive actions, looking up folder names and so on, copying existing files and folders, ... after setting up a few 100s of these I got pretty tired :lol: You only need this code and no other libraries.

Enjoy !

#SingleInstance force

cMsg_Cancel = You cancelled. The program stops.
cMsg_Invalid_Choice = Invalid choice entered. The program stops.

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; Alt-F12 | Create a shortcut to run a TM1 model as an application
;
; Wim Gielis
; http://www.wimgielis.com
; April 2019
;
; Remarks:
; - initially, browse to the folder containing the tm1s.cfg file
; - the tm1s.cfg file can be created/updated
; - the config file for TM1 is not necessarily part of the TM1 data directory
; - there is only 1 folder for the TM1 data directory
; - the TM1 logging directory is part of the TM1 data directory, and is called "Logs", unless you choose the logging directory yourself
; - TM1 server name can be set too. A number of other settings are set in this script for your convenience
; - you can also delete all commentary lines (lines starting with #). For instance, IBM's cfg file is quite big and not needed for simple models
;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

!F12:: ; -- (Windows Explorer) create a shortcut to run a TM1 model as an application
{
cPath_TM1s_exe := "C:\Program Files\ibm\cognos\tm1_64\bin64\tm1s.exe"
IfNotExist, %cPath_TM1s_exe%
    {
    MsgBox, The TM1s.exe file could not be found at %cPath_TM1s_exe%. The program stops.
    return
    }

WinGetClass, class, A
if (class="CabinetWClass" or class="ExploreWClass" or class="Progman")
{
    ControlGetText, currentpath, ToolbarWindow323, ahk_class %class%
    StringTrimLeft, currentpath, currentpath, 9

    FileSelectFolder, Folder, *%currentpath%, 3, Select the folder containing the tm1s.cfg file`n(if missing it will be created)
    If ErrorLevel
    {
        MsgBox, %cMsg_Cancel%
        return
    }
    Folder := RegExReplace(Folder, "\\$")

    If currentpath =
       currentpath := Folder

    x = %currentpath%
    StringGetPos, p1, x, \ , R1
    Stringtrimleft, vCustomer, x, (P1+1)

    ; ask for the name of the shortcut
    InputBox, Customer, Shortcut name, Please provide the name for the shortcut `n(examples: customer name or model name),,,,,,,, %vCustomer%
    If ErrorLevel = 1
    {
        MsgBox, %cMsg_Cancel%
        return
    }

    c01 = %Folder%\tm1s.cfg

    ; check if cfg file exists
    ; treating the TM1 data directory
    IfExist, %c01%
    {
        IniRead, DataDirFolder,    %c01%, TM1S, DataBaseDirectory,
        If ( InStr( DataDirFolder, ".", false ) > 0 ) OR ( InStr( DataDirFolder, "..", false ) > 0 )
        {
            base = %Folder%
            rel = %DataDirFolder%
            DataDirFolder := % PathCombine(base, rel)
        }
        IfExist, %DataDirFolder%
            DD_exist := "(exists)"
        Else
            DD_exist := "(does NOT exist)"

        MsgBox, A TM1s.cfg file was found at:`n   %c01%`n`nIt uses as the data directory:`n   %DataDirFolder% %DD_exist%

        IfNotExist, %DataDirFolder%
        {
            FileSelectFolder, Folder_Data_Dir, *%Folder%, 3, Select the TM1 data directory
            If ErrorLevel
            {
                MsgBox, %cMsg_Cancel%
                return
            }
            IniWrite, %Folder_Data_Dir%           , %c01%, TM1S, DataBaseDirectory
        }
        Else
        {
            Msgbox, 4, Confirm, Do you want to set/overwrite the TM1 data directory ?
            IfMsgBox Yes
            {
                FileSelectFolder, Folder_Data_Dir, *%Folder%, 3, Select the TM1 data directory
                If ErrorLevel
                {
                    MsgBox, %cMsg_Cancel%
                    return
                }
                IniWrite, %Folder_Data_Dir%           , %c01%, TM1S, DataBaseDirectory
            }
        }
    }
    Else
    {
        MsgBox, The config file could not be found at %c01%. It will be generated now.
        ; the data dir equals the configuration folder
        ; IniWrite, %Folder%           , %c01%, TM1S, DataBaseDirectory

        ; asking for the database directory
        FileSelectFolder, Folder_Data_Dir, *%Folder%, 3, Select the TM1 data directory
        If ErrorLevel
        {
            MsgBox, %cMsg_Cancel%
            return
        }
        IniWrite, %Folder_Data_Dir%           , %c01%, TM1S, DataBaseDirectory
    }

    ; treating the TM1 logging directory
    IniRead, LoggingDirectory       , %c01%, TM1S, LoggingDirectory,
    IfNotExist, %LoggingDirectory%
    {
        Msgbox, 4, Confirm, Do you want to set TM1 logging directory ? If not you will have a "Logs" folder in the data directory.
        IfMsgBox Yes
        {
            FileSelectFolder, Folder_Log_Dir, *%Folder_Data_Dir%, 3, Select the TM1 logging directory
            If ErrorLevel
            {
                MsgBox, %cMsg_Cancel%
                return
            }
        }
        Else
            Folder_Log_Dir = %Folder_Data_Dir%\Logs
        FileCreateDir, %Folder_Log_Dir%
        IniWrite, %Folder_Log_Dir%      , %c01%, TM1S, LoggingDirectory
    }
    Else
    {
        Msgbox, 4, Confirm, Do you want to overwrite TM1 logging directory ?
        IfMsgBox Yes
        {
            FileSelectFolder, Folder_Log_Dir, *%Folder_Data_Dir%, 3, Select the TM1 logging directory
            If ErrorLevel
            {
                MsgBox, %cMsg_Cancel%
                return
            }
            FileCreateDir, %Folder_Log_Dir%
            IniWrite, %Folder_Log_Dir%      , %c01%, TM1S, LoggingDirectory
        }
    }

    ; the tm1 server name (the model)
    IniRead, TM1ServerName       , %c01%, TM1S, ServerName, E
    if TM1ServerName = E
    {
        InputBox, TM1ServerName, TM1 server name, Please enter the name for the TM1 server (model),,,,,,,,%Customer%
        if TM1ServerName =
           TM1ServerName = %Customer%
        IniWrite, %TM1ServerName%, %c01%, TM1S, ServerName
    }
    else if TM1ServerName =
    {
        InputBox, TM1ServerName, TM1 server name, Please enter the name for the TM1 server (model),,,,,,,,%Customer%
        if TM1ServerName =
           TM1ServerName = %Customer%
        IniWrite, %TM1ServerName%, %c01%, TM1S, ServerName
    }
    else
    {
        Msgbox, 4, Confirm, The TM1 server name is %TM1ServerName%. Do you want to change the TM1 server name ?
        IfMsgBox Yes
        {
            InputBox, TM1ServerName, TM1 server name, Please enter the name for the TM1 server (model),,,,,,,,%TM1ServerName%
            If ErrorLevel
            {
                MsgBox, %cMsg_Cancel%
                return
            }
            IniWrite, %TM1ServerName%, %c01%, TM1S, ServerName
        }
    }

    ; add/update other settings in the TM1 data directory
    IniWrite, localhost          , %c01%, TM1S, AdminHost
    IniWrite, 1                  , %c01%, TM1S, IntegratedSecurityMode
    IniWrite, F                  , %c01%, TM1S, UseSSL
    IniWrite, T                  , %c01%, TM1S, EnableTIDebugging
    IniWrite, T                  , %c01%, TM1S, PersistentFeeders
    IniWrite, T                  , %c01%, TM1S, EnableNewHierarchyCreation

    Random, OutputVar, 8005, 8100
    IniWrite, %OutputVar%        , %c01%, TM1S, HttpPortNumber

    Random, OutputVar, 5000, 49151
    IniWrite, %OutputVar%        , %c01%, TM1S, PortNumber

    IniWrite, T                  , %c01%, TM1S, ParallelInteraction
    IniWrite, T                  , %c01%, TM1S, EnableTIDebugging
    IniWrite, T                  , %c01%, TM1S, ForceReevaluationOfFeedersForFedCellsOnDataChange
    IniWrite, all                , %c01%, TM1S, MTQ

    EnvGet, ProcessorCount, number_of_processors
    ProcessorCount--
    IniWrite, %ProcessorCount%   , %c01%, TM1S, MaximumCubeLoadThreads

    IniWrite, F                  , %c01%, TM1S, MTFeeders
    IniWrite, Kerberos           , %c01%, TM1S, SecurityPackageName
    IniWrite, ENG                , %c01%, TM1S, Language
    IniWrite, T                  , %c01%, TM1S, MTQQuery
    IniWrite, 5000               , %c01%, TM1S, MaximumViewSize
    IniWrite, T                  , %c01%, TM1S, VersionedListControlDimensions
    IniWrite, T                  , %c01%, TM1S, LoadPrivateSubsetsOnStartup
    IniWrite, T                  , %c01%, TM1S, ReduceCubeLockingOnDimensionUpdate
    IniWrite, T                  , %c01%, TM1S, EventLogging
    IniWrite, 1                  , %c01%, TM1S, EventScanFrequency
    IniWrite, 0                  , %c01%, TM1S, EventThreshold.PooledMemoryInMB
    IniWrite, 5                  , %c01%, TM1S, EventThreshold.ThreadBlockingNumber
    IniWrite, 600                , %c01%, TM1S, EventThreshold.ThreadRunningTime
    IniWrite, 20                 , %c01%, TM1S, EventThreshold.ThreadWaitingTime
    IniWrite, T                  , %c01%, TM1S, PullInvalidationSubsets
    IniWrite, F                  , %c01%, TM1S, PerformanceMonitorOn
    IniWrite, F                  , %c01%, TM1S, AuditLogOn
    IniWrite, 1800s              , %c01%, TM1S, AuditLogUpdateInterval
    IniWrite, 2 GB               , %c01%, TM1S, AuditLogMaxFileSize
    IniWrite, 1 GB               , %c01%, TM1S, AuditLogMaxQueryMemory
    IniWrite, 10                 , %c01%, TM1S, ClientPropertiesSyncInterval

    IniRead, LoggingDirectory    , %c01%, TM1S, LoggingDirectory,
    IniWrite, %LoggingDirectory% , %c01%, TM1S, FileRetry.FileSpec
    IniWrite, 5                  , %c01%, TM1S, FileRetry.Count
    IniWrite, 2000               , %c01%, TM1S, FileRetry.Delay

    IniDelete                    , %c01%, TM1S, ServerCAMURI
    IniDelete                    , %c01%, TM1S, ClientCAMURI
    IniDelete                    , %c01%, TM1S, ClientPingCAMPassport

    ; the shortcut to launch the model
    StringGetPos, p1,Folder, \ , R1
    Stringtrimleft,a,Folder,(P1+1)
    FileCreateShortcut, %cPath_TM1s_exe%, %currentpath%\%Customer%.lnk, , -z"%Folder%"

    ; create additional folders
    Msgbox, 4, Confirm, Do you want to create a few folders for this TM1 model ?
    IfMsgBox Yes
    {
        FileCreateDir, %currentpath%\Install files
        FileCreateDir, %currentpath%\Project files
        FileCreateDir, %currentpath%\Project files\Control files
        FileCreateDir, %currentpath%\Project files\Documentation
        FileCreateDir, %currentpath%\Project files\Input
        FileCreateDir, %currentpath%\Project files\Input\Data
        FileCreateDir, %currentpath%\Project files\Input\Metadata
        FileCreateDir, %currentpath%\Project files\Output
        FileCreateDir, %currentpath%\Project files\Progress
        FileCreateDir, %currentpath%\Project files\Templates
        FileCreateDir, %currentpath%\Project files\Testing
        FileCreateDir, %currentpath%\Scripts
        FileCreateDir, %currentpath%\TM1Backup
    }

    ; delete unnecessary lines
    Msgbox, 4, Confirm, Do you want to delete all lines in the tm1s.cfg file that start with an # ?
    IfMsgBox Yes
    {
        all =
        Loop, Read, %c01%
        {
            StringLeft, tmp, A_LoopReadLine, 1
            If( tmp != "#" )
                all = %all%`r`n%A_LoopReadLine%
        }
        StringTrimLeft, all, all, 2
        FileDelete, %c01%
        FileAppend, %all%, %c01%
    }

    ; run the shortcut / TM1 model
    Msgbox, 4, Confirm, Do you want to start the TM1 model ?
    IfMsgBox Yes
        Run, %currentpath%\%Customer%.lnk

    Return
}
else
     msgbox First activate Windows Explorer.
}
return

PathCombine(abs, rel) {
    VarSetCapacity(dest, (A_IsUnicode ? 2 : 1) * 260, 1) ; MAX_PATH
    DllCall("Shlwapi.dll\PathCombine", "UInt", &dest, "UInt", &abs, "UInt", &rel)
    Return, dest
}



Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links