Admins admin Mon, 2019-11-18 11:13


Backup admin Tue, 2019-11-19 08:55

Before running the following script you need to setup a chore with a TI process to execute that line in Prolog:


/!\ Avoid using the SaveTime setting in tm1s.cfg as it could conflict with other chores/processes trying to run at the same time

here is the DOS backup script that you can schedule to backup your TM1 server

netsvc /stop \\TM1server "TM1 service"

sleep 300

rmdir "\\computer2\path\to\backup" /S /Q
mkdir "\\computer2\path\to\backup"
xcopy "\\TM1server\path\to\server" "\\computer2\path\to\backup" /Y /R /S
xcopy "\\TM1server\path\to\server\}*" "\\computer2\path\to\backup" /Y /R /S
netsvc /start \\TM1server "TM1 service"

Documenting TM1

Documenting TM1 admin Tue, 2019-11-19 08:57

A closer look at chores

A closer look at chores admin Tue, 2019-11-19 08:58

if you ever loaded a .cho file in an editor this is what you would expect:

530,yyyymmddhhmmss ------ date/time of the first run
531,dddhhmmss ------ frequency
532,p ------ number p of processes to run
6,"process name"
533,x ------ x=1 active/ x=0 inactive

In the 9.1 series it is possible to see from the Server Explorer which chores are active from the chores menu.
However this is not the case in the 9.0 series, also it is not possible to see when and how often the chores are running unless you deactivate them first and edit them. Not quite convenient to say the least.
From the specs above, it is easy to set rules for a parser and deliver all that information in a simple report.
So the perl script attached below is doing just that: listing all chores on your server, their date/time of execution, frequency and activity status.

Procedure to follow:

  1. Install perl
  2. Save in a folder
  3. Doubleclick on
  4. A window opens, enter the path to your TM1 server data folder there
  5. Open resulting file chores.txt created in the same folder as


ACT /     date-time     /    frequency   / chore name
 X   2005/08/15 04:55:00 007d 00h 00m 00s currentweek
 X   2007/04/28 05:00:00 001d 00h 00m 00s DailyS
 X   2007/05/30 05:50:00 001d 00h 00m 00s DAILY_UPDATE
 X   2007/05/30 05:40:00 001d 00h 00m 00s DAILY_S_UPDATE
 X   2005/08/13 20:00:05 007d 00h 00m 00s eweek
 X   2006/04/06 07:30:00 001d 00h 00m 00s a_Daily
 X   2007/05/30 06:05:00 001d 00h 00m 00s SaveDataAll
 X   2007/05/28 05:20:00 007d 00h 00m 00s WEEKLY BUILD
 X   2005/05/15 21:00:00 007d 00h 00m 00s weeklystock
     2007/05/28 05:30:00 007d 00h 00m 00s WEEKLY_LOAD

A closer look at subsets

A closer look at subsets admin Tue, 2019-11-19 09:02

if you ever loaded a .sub file (subset) in an editor this is the format you would expect:

283,2 start
11,yyyymmddhhmmss creation date
274,"string" name of the alias to display
18,0 ?
275,d d = number of characters of the MDX expression stored on the next line
278,0 ?
281,b b = 0 or 1 "expand above" trigger
270,d d = number of elements in the subset followed by the list of these elements, this also represents the set of elements of {TM1SubsetBasis()} if you have an MDX expression attached

These .sub files are stored in cube}subs folders for public subsets or user/cube}subs for private subsets.

Often a source of discrepancy in views and reports is the use of static subsets. For example a view was created a while ago, displaying a bunch of customers, but since then new customers got added in the system and they will not appear in that view unless they are manually added to the static subset.

Based on the details above, one could search for all non-MDX/static subsets (wingrep regexp search 275,$ in all .sub files) and identify which might actually need to be made dynamic in order to keep up with slowly changing dimensions.


Beam me up Scotty: 3D Animated TM1 Data Flow

Beam me up Scotty: 3D Animated TM1 Data Flow admin Tue, 2019-11-19 09:05

Explore the structure of your TM1 system through the Skyrails 3D interface:

If you do not have flash, you can have a look at some screenshots
/!\ WARNING: your eyeballs may pop out!

This is basically the same as the previous work with graphviz, except this time it is pushed to 3D, animated and interactive.

So the visualisation engine Skyrails is developed by Ph.D. student Yose Widjaja.
I only wrote the TM1 parser and associated Skyrails script to port a high level view of the TM1 Data flow into the Skyrails realm.

How to proceed:

  • Download and unzip skyrails beta 2nd build
  • Download and unzip (attachment below) in the skyraildist2 folder
  • In the skyraildist2 folder, doubleclick (you will need perl installed unless someone wants to provide a compiled .exe of the script with the PAR module)
  • Enter the path to (a copy of) your TM1 Data folder
  • Skyrails window opens, click on the "folder" icon and click TM1
  • If you don't want to install perl, you can still enjoy a preview of the Planning Sample that comes out of the box. Just double-click on raex.exe.

w,s,a,d keys to move the camera

Quick legend:
orange -- cube
blue -- process
light cyan -- file
red -- ODBC source
green sphere -- probably reference to an object that does not exists (anymore)
green edge: intercube rule flow
red edge: process (CellGet/CellPut) floww

Changelog and Downloads:

1.1 a few mouse gestures added (right click on a node then follow instructions) to get planar (like graphviz) and spherical representations.
1.2 - edges color coded, see legend above
- animated arrows
- gestures to display different flows (no flow/rules only/processes only/all flow)





Dimensions updates mapping

Dimensions updates mapping admin Tue, 2019-11-19 14:18

When faced with a large "undocumented" TM1 server, it might become hard to see how dimensions are being updated.

The following perl/graphviz script creates a graph to display which processes are updating dimensions.

The script is looking for functions updating dimensions (DimensionElementInsert, DimensionCreate...) in .pro files in the TM1 datafolder and maps it all together.
Unfortunately it does not take into account manual editing of dimensions.

This is the result:

Dimensions Flow

processes = red rectangles
dimensions = blue bubbles

The above screenshot is probably a good example of why such map can be useful: you can see immediately that several processes are updating the same dimensions.
It might be necessary to have several processes feeding a dimension, though it will be good to review these processes to make sure they are not redundant or conflicting.

Procedure to follow:

  1. Install perl and graphviz
  2. Download the script below and rename it to .pl extension
  3. Doubleclick on it
  4. Enter the path to your TM1 Data folder (\\servername\datafolder)
  5. This will create 2 files "" and "dim.gif" in the same folder as the perl script
  6. Open dim.gif with any browser / picture editor

Duplicate Elements in Rollups

Duplicate Elements in Rollups admin Thu, 2019-11-21 14:12

Large nested rollups can lead to elements being counted twice or more within a rollup.
The following process helps you to find, across all dimensions of your system, all rollups that contain elements consolidated more than once under the rollup.

The code isn't the cleanest and there are probably better methods to achieve the same result so don't hesitate to edit the page or comment.

It proceeds like this:

loop through all dimensions
  loop through all rollups of a dimension
    if(number of distinct elements in rollup <> number of elements in rollup)
      then loop through all alphanumerically sorted elements of the rollup
        duplicates are found next to each other

The process takes less than 30 sec to run on a 64bit 9.1.4 system with 212 dimensions, one of the dimensions holds more than a 1000 consolidations and some "decent" nesting (9 levels).

/!\ This process will not work with some TM1 versions because of the MDX function DISTINCT, it has been successfully tested on v. 9.1.4.
DISTINCT does not work properly when applied to expanded rollups on v. 9.0.3

You can just copy/paste the following code in the Prolog window of a process.
Just edit the first line to dump the results where you would like to see them.

/!\ The weight of the duplicated elements is also shown, just in case identical elements cancel each other out with opposite weights.

report = 'D:\duplicate.elements.csv'; #config the line above. #caveat: empty or consolidations-only dimensions will make this process fail, because empty subsets cannot be created allrollups = '}All rollups'; subset = '}compare'; maxDim = DimSiz('}Dimensions'); i = 1; #for each dimension while (i <= maxDim); vDim = DIMNM('}Dimensions',i); #test if there are any rollups #asciioutput(report, vDim); SubsetDestroy(vDim, allrollups); SubsetCreatebyMDX(allrollups,'{TM1FILTERBYLEVEL( {TM1SUBSETALL([' | vDim | '])},0)}'); If(SubsetGetSize(vDim,allrollups) <> DimSiz(vDim)); #get a list of all rollups SubsetDestroy(vDim, allrollups); SubsetCreatebyMDX(allrollups,'{EXCEPT(TM1SUBSETALL([' | vDim | ']), TM1FILTERBYLEVEL( {TM1SUBSETALL([' | vDim | '])},0))}'); maxRolls = SubsetGetSize(vDim,allrollups); rollup = 1; #for each rollup in that dimension while (rollup <= maxRolls); Consolidation = SubsetGetElementName(vDim, allrollups, rollup); If(SubsetExists(vDim, subset) = 1); SubsetDestroy(vDim, subset); Endif; SubsetCreatebyMDX(subset, '{DISTINCT(TM1DRILLDOWNMEMBER( {[' | vDim | '].[' | Consolidation | ']}, ALL, RECURSIVE))}' ); Distinct = SubsetGetSize(vDim, subset); SubsetDestroy(vDim, subset); SubsetCreatebyMDX(subset, '{TM1DRILLDOWNMEMBER( {[' | vDim | '].[' | Consolidation | ']}, ALL, RECURSIVE)}' ); All = SubsetGetSize(vDim, subset); if(All <> Distinct); SubsetDestroy(vDim, subset); #alphasort consolidation elements so duplicates are next to eachother and a 1-pass is enough to find them all SubsetCreatebyMDX(subset, '{TM1SORT( {TM1DRILLDOWNMEMBER( {[' | vDim | '].[' | Consolidation | ']}, ALL, RECURSIVE)},ASC) }' ); maxElem = SubsetGetSize(vDim, subset); prevE = ''; nelem = 1; while(nelem <= maxElem); element = SubsetGetElementName(vDim, subset, nelem); #if 2+ identical elements next to each other then these are the duplicates If(prevE @= element); AsciiOutput(report,'',vDim,Consolidation,element,NumberToString(ELWEIGHT(vDim,Consolidation,element))); Endif; prevE = element; nelem = nelem + 1; end; Endif; rollup = rollup + 1; end; SubsetDestroy(vDim, allrollups); If(SubsetExists(vDim, subset) = 1); SubsetDestroy(vDim, subset); Endif; Endif; i = i + 1; end;

Graphing TM1 Data Flow

Graphing TM1 Data Flow admin Tue, 2019-11-19 14:26

This is the new version of a little parser written in perl that will create a input file for graphviz from your TM1 .pro and .rux files then generate a graph of the data flow in your TM1 server...

TM1 Data Flow

(the image has been cropped and scaled down for display, the original image is actually readable)

TM1 Data Flow legend


ellipses = cubes, rectangles = processes
red = cellget, blue = cellput, green = inter-cube rule

Procedure to follow:

  1. Install perl and graphviz
  2. Put the genflow perl script in any folder, make sure it has the .pl extension (not txt)
  3. Doubleclick on it
  4. Enter the path to your TM1 Data folder such as: \\servername\datafolder where \\servername\datafolder is the full file path to your TM1 data folder
  5. Hit return and wait until the window disappears
  6. This creates 2 files: "" and "flow.gif" in the same folder as the perl script
  7. Open "flow.gif" in any browser or picture editor


.display import view names along the edges
.display zeroout views
.sources differentiated by shape

.CellPut parsing fix
.cubes/processes names displayed 'as is'

This is still quite experimental but this could be useful to view at a glance high-level interactions between your inputs, processes, cubes and rules.


Indexing subsets

Indexing subsets admin Tue, 2019-11-19 14:44

Maintaining subsets on your server might be problematic. For example you wanted to delete an old subset that you found out to be incorrect and your server replied this:

Delete Subset failed popup

This is not quite helpful, as it does not say which views are affected and need to be corrected.

Worse is that, as Admin, you can delete any public subset as long as it is not being used in a public view. If it is used in a user's private view, it will be deleted anyway and that private view might become invalid or just won't load.

In order to remediate to these issues, I wrote this perl script that will:

  • Index all your subsets, including users' subsets.
  • Display all unused subsets (i.e. not attached to any existing views)

From the index, you can find out right away in which views a given subset is used.

I suppose the same could be achieved through the TM1 API though you would have to log as every user in turn in order to get a full index of all subsets.

Run from a DOS shell: perl \\path\to\TM1\server > mysubsets.txt


Processes running history

Processes running history admin Tue, 2019-11-19 14:49

On a large undocumented and mature TM1 server you might find yourself with a lot of processes and you wonder how many of them are still in use or the last time they got run.

The script answers these questions for you.

One could take a look at the creation/modification time of the processes in the TM1 Data folder however you would have to sit through pages of the tms1msg.log to get the history of a given process which is what the script below does.

Procedure to follow for TM1 9.0 or 8.x

  1. Install perl
  2. Save in a folder
  3. Stop your TM1 service (necessary to beat the windows lock on tm1smsg.log)
  4. Copy the tm1smsg.log into the same folder where is
  5. Start your TM1 service
  6. Double click


Procedure to follow for TM1 9.1 and above

  1. Install perl
  2. Save in a folder
  3. Copy the tm1server.log into the same folder where is
  4. Double click

That should display the newly created processes.txt in notepad and that should look like the following:

First, all processes sorted by name and the last run time, user and how many times it ran.

processes by name:
2005load run 2006/02/09 15:02:33 user Admin [x2]
ADMIN - Unused Dimensions run 2006/04/26 14:02:58 user Admin [x1]
Branch Rates Update run 2006/10/19 15:23:29 user Admin [x1]
BrandAnalysisUpdate run 2005/04/11 08:09:13 user Admin [x33]

Second, all processes sorted by last run time, user and how many times it ran.

processes by last run: 
2005/04/11 08:09:13 user Admin ran BrandAnalysisUpdate [x33]
2005/04/11 10:26:29 user Admin ran LoadDelivery [x1]
2005/04/19 08:44:22 user Admin ran UpdateAntStockage [x19]
2005/04/26 14:18:17 user Admin ran weeklyodbc [x1]
2005/05/12 08:34:16 user Admin ran stock [x1]
2005/05/12 08:37:59 user Admin ran receipts [x1]



The case against single children

The case against single children admin Tue, 2019-11-19 14:55

I came across hierarchies holding a single child.
While creating a consolidation over only 1 element might make sense in some hierarchies, some people just use consolidations as an alternative to aliases.
Either they just don't know they exist or they come from an age when TM1 did not have aliases yet.

The following process will help you identify all the "single child" elements in your system.
This effectively loops through all elements of all dimensions of your system, so this could be reused to carry out other checks.

#where to report the results
Report = '\\tm1server\reports\single_children.csv';

#get number of dimensions on that system
TotalDim = Dimsiz('}Dimensions');

#loop through all dimensions
i = 1;
While (i <= TotalDim);
  ThisDim = DIMNM('}Dimensions',i);

  #foreach dimension
  #loop through all their elements 
  j = 1;
  While (j <= Dimsiz(ThisDim));
    Element = DIMNM(ThisDim,j);
    #report the parent if it has only 1 child  
    If( ELCOMPN(ThisDim, Element) = 1 );
    #report if consolidation has no child!!!
    If( ELCOMPN(ThisDim, Element) = 0 & ELLEV(Thisdim, Element) > 0 );
      single = single + 1;
      AsciiOutput(Report,ThisDim,DIMNM(ThisDim,j),'NO CHILD!!');
  j = j + 1;
  i = i + 1; 

Dynamic tm1p.ini and homepages in Excel

Dynamic tm1p.ini and homepages in Excel admin Tue, 2019-11-19 14:59

Pointing all your users to a single TM1 Admin host is convenient but not flexible if you manage several TM1 services.
Each TM1 service might need different settings and you do not necessarily want users to be able to see the development or test services for example.

mytm1.xla is an Excel addin that logs the users on a predefined server and settings as shown on that graph:

Excel add-in load TM1

With such a setup, you can switch your users from one server to the other without having to tinker the tm1p.ini files on every single desktop.
This solution probably offers the most flexibility and maintainability as you could add conditional statements to point different groups of users to different servers/settings and even manage and retrieve these settings from a cube through the API.

This addin also includes:
- previous code like the "TM1 freeze" button
- it loads automatically an excel spreadsheet named after the user so each user can customise it with their reports/links for a faster access to their data.

The TM1 macro OPTSET, used in the .xla below, can preconfigure the tm1p.ini with a lot more values.

The official TM1 Help does not reference all the available values though.
Here is a more complete list, you can actually change all the parameters displayed in the Server Explorer File->Options with OPTSET:

and more:


this also applies to OPTGET

Make sure that all hosts in the AdminHost line are up and working otherwise Architect/Perspectives will hang for a couple of seconds while trying to connect to these hosts.


Locking and updating locked cubes

Locking and updating locked cubes admin Tue, 2019-11-19 15:09

Locking cubes is a good way to insure your (meta)data is not tampered with.
Right click on the cube you wish to lock, then select Security->Lock.
This now protects the cube contents from TI process and (un)intentional admins' changes.
However, this makes updating your (meta)data more time consuming, as you need to remove the lock prior to updating the cube.

Hopefully, the function CubeLockOverride allows you to automate that step. The following TI code demonstrates this,
.lock a cube
.copy/paste the code in a TI Prolog tab
.change the parameters to fit your system

# uncomment / comment the next line to see the process win / fail

Dim = 'Day';
Element = 'Day 01';
Attribute = 'Dates 2010';
NewValue = 'Saint Glinglin';

if( CellIsUpdateable('}ElementAttributes_' | Dim, Element, Attribute) = 1);
    AttrPutS(NewValue, Dim, Element, Attribute);
    ItemReject('could not unlock element ' | Element | ' in ' | Dim);

Note: CubeLockOverride is in the reserved words listed in the TM1 manual but its function seems to be only documented in the 8.4.5 releases notes.
This works from 8.4.5 to the more recent 9.x series

Managing the licenses limit

Managing the licenses limit admin Tue, 2019-11-19 15:12

One day you might face or already faced the problem of too many licences being in use and as a result additional users cannot log in.
Also on a default setup, nothing stops users from opening several tm1web/perspectives sessions and reach the limit of licenses.
So in order to prevent that:

.open the cube }ClientProperties, change all users' MaximumPorts to 1
.in your tm1s.cfg add that line, it will timeout all idle connections after 1 hour:
IdleConnectionTimeOutSeconds = 3600

To see who's logged on:
.use tm1top
.open the cube }ClientProperties
all logged users have the STATUS measure set to "ACTIVE"
.in server manager (rightclick server icon), click "Select clients..." to get the list

To kick some users without taking the server down:
in server explorer right click on your server icon -> Server Manager
select disconnect clients and "Select clients..."
then OK and they are gone.

Unfortunately there is still no workaround for the admin to log in when users take all the slots allowed.


Monitor rules and processes

Monitor rules and processes admin Tue, 2019-11-19 15:14

Changing a rule or process in TM1 does not show up in the logs.
That is fine as long as you are the only Power User able to tinker with these objects.
Unfortunately, it can get out of hand pretty quickly as more power users join the party and make changes that might impact other departments data.
So here goes a simple way to report changes.

The idea is to compare the current files on the production server with a backup from the previous day.

You will need:

  • Access to the live TM1 Data Folder
  • Access to the last daily backup
  • A VB script to email results you can find one there
  • diff, egrep and unix2dos

Save these files in D:\TM1DATA\BIN for example, or some path accessible to the TM1 server.

In the same folder create a diff.bat file, replace all the TM1DATA paths to your configuration:

@echo off
del %~1
rem windows file compare fc is just crap, must fallback to the mighty GNU binutils
diff -q "\\liveserver\TM1DATA" "\\backupserver\TM1DATA" | egrep "\.(pro|RUX|xdi|xru|cho)" > %~1
rem make it notepad friendly, i.e. add these horrible useless CR chars at EOL, it's 2oo8 but native windows apps are just as deficient as ever
unix2dos %~1
rem if diff is not empty then email results
if %~z1 GTR 1 sendattach.vbs mailserver 25 "[TM1] daily changes log" " " "D:\TM1DATA\BIN\%~1"

Now you can set a TM1 process with the following line to run diff.bat and schedule it from a chore.

ExecuteCommand('cmd /c D:\TM1DATA\BIN\diff.bat diff.txt',0);

Best is to run the process at close of business, just before creating the backup of the day.

And you should start receiving emails like these:

Files \\liveserver\TM1DATA\Check Dimension and \\backupserver\TM1DATA\Check Dimension differ
Files \\liveserver\TM1DATA\Productivity.RUX and \\backupserver\TM1DATA\Productivity.RUX differ
Only in \\liveserver\TM1DATA: Update Cube Branch

In this case we can see that the rules from the Productivity cube have changed today.

Monitoring chores by email

Monitoring chores by email admin Tue, 2019-11-19 15:18

Using the script in the Send Email Attachments article, it is possible to set it up to automatically email the Admin when a process in a chore fails.

Here is how to proceed:

1. Setup admin email process
First we create a process to add an email field to the ClientProperties cube and add an email to forward to the Admin.

1.1 create a new process
---- Advanced/Parameters Tab, insert this parameter:
AdminEmail / String / / "Admin Email?"

--- Advanced/Prolog tab
if(DIMIX('}ClientProperties','Email') = 0);

--- Advanced/Epilog tab

1.2 Save and Run

2. Create monitor process

---- Advanced/Prolog tab
MailServer = '';
LogDir = '\\tm1server\e$\TM1Data\Log';
ScriptDir = 'E:\TM1Data\';

NumericGlobalVariable( 'ProcessReturnCode');

If(ProcessReturnCode <> ProcessExitNormal());

If(ProcessReturnCode = ProcessExitByChoreQuit());
Status = 'Exit by ChoreQuit';
If(ProcessReturnCode = ProcessExitMinorError());
Status = 'Exit with Minor Error';
If(ProcessReturnCode = ProcessExitByQuit());
Status = 'Exit by Quit';
If(ProcessReturnCode = ProcessExitWithMessage());
Status = 'Exit with Message';
If(ProcessReturnCode = ProcessExitSeriousError());
Status = 'Exit with Serious Error';
If(ProcessReturnCode = ProcessExitOnInit());
Status = 'Exit on Init';
If(ProcessReturnCode = ProcessExitByBreak());
Status = 'Exit by Break';

vbody= 'Process failed: '|Status| '. Check '|LogDir;
Email = CellGetS('}ClientProperties','Admin','Email');
If(Email @<> '');
S_Run='cmd /c '|ScriptDir|'\SendMail.vbs '| MailServer |' 25 '|Email|' '|Email|' "TM1 chore alert" "'|vBody|'"';

2.1. adjust the LogDir, MailServer and ScriptDir values to your local settings

3. insert this monitor process in chore
This monitor process needs to be placed after every process that you would like to monitor.

How does it work?
Every process, after execution, returns a global variable "ProcessReturnCode", and that variable can be read by a process running right after in a chore.
The above process checks for that return code and pipes it to the mail script if it happens to be different from the normal exit code.

If you have a lot of processes in your chore, you will probably prefer to use the ExecuteProcess command and the check return code over a loop. That method is explained here.


Monitoring chores by email part 2

Monitoring chores by email part 2 admin Tue, 2019-11-19 15:22

Following up on monitoring chores by email, we will take a slightly different approach this time.
We use a "metaprocess" to execute all the processes listed in the original chore, check their return status and eventually act on it.
This allows for maximum flexibility as you can get that controlling process to react differently to any exit status of any process.

1. Create process ProcessCheck
--- Data Source tab
choose ASCII, Data Source Name points to an already existing chore file, for example called Daily Update.cho

Monitor process

--- Variables tab
Variables tab has to be that way:

Monitor Variables tab

--- Advanced/Data tab

#mind that future TM1 versions might use a different format for .cho files and that might break this script
If(Tag @= '6');

MailServer = '';
LogDir = '\\server\f$\TM1Data\myTM1\Log';

#get the process names from the deactivated chore

NumericGlobalVariable( 'ProcessReturnCode');

ErrorCode = ExecuteProcess(Process);

If(ErrorCode <> ProcessExitNormal());

If(ProcessReturnCode = ProcessExitByChoreQuit());
Status = 'Exit by ChoreQuit';
#Honour the chore flow so stop here and quit too
If(ProcessReturnCode = ProcessExitMinorError());
Status = 'Exit with Minor Error';
If(ProcessReturnCode = ProcessExitByQuit());
Status = 'Exit by Quit';
If(ProcessReturnCode = ProcessExitWithMessage());
Status = 'Exit with Message';
If(ProcessReturnCode = ProcessExitSeriousError());
Status = 'Exit with Serious Error';
If(ProcessReturnCode = ProcessExitOnInit());
Status = 'Exit on Init';
If(ProcessReturnCode = ProcessExitByBreak());
Status = 'Exit by Break';

vbody=Process|' failed: '|Status|'. Check details in '|LogDir;
Email = CellGetS('}ClientProperties','Admin','Email');
If(Email @<> '');
S_Run='cmd /c F:\TM1Data\CDOMail.vbs '| MailServer |' 25 '|Email|' '|Email|' "TM1 chore alert" "'|vBody|'"';


The code only differs from the first method when the process returns a ChoreQuit exit. Because we will be running the chore Daily Update from another chore, the ChoreQuit will not apply to the later, so we need to specify it explicitly to respect the flow and stop at the same point.

2. Create chore ProcessCheck
just add the process above and set it to the same frequency and time as the Daily Update chore that you want to monitor

3. Deactivate Daily Update
since the ProcessCheck chore will run the Daily Update chore there is no need to execute Daily Update another time



Monitoring users logins

Monitoring users logins admin Tue, 2019-11-19 15:28

A quick way to monitor users login/logout on your system is to log the STATUS value (i.e. ACTIVE or blank) from the }ClientProperties cube.

View->Display Control Objects
Cubes -rightclick- Security Assignments
browse down to the }ClientProperties cube and make sure the Logging box is checked
tm1server -rightclick- View Transaction Log
Select Cubes: }ClientProperties

All the transactions are stored in the tm1s.log file, however if you are on a TM1 version prior to version 9.1 and hosted on a Windows server, the file will be locked.
A "Save Data" will close the log file and add a timestamp to its name, so you can start playing with it.

/!\ This trick does not work in TM1 9.1SP3 as it does not update the STATUS value.


Pushing data from an iSeries to TM1

Pushing data from an iSeries to TM1 admin Tue, 2019-11-19 15:36

TM1 chore scheduling is frequency based, i.e. it will run and try to pull data after a predefined period of time regardless of the availability of the data at the source. Unfortunately it can be a hit or miss and it can even become a maintenance issue when Daylight Saving Time come into play.
Ideally you would need to import or get the data pushed to TM1 as soon as it is available. The following article shows one way of achieving that goal with an iSeries as the source...

prerequesites on the TM1 server:

  • Mike Cowie's TIExecute
  • iSeries Client Access components (iSeries Access for Windows Remote Command service)


Procedure to follow

  1. Drop TM1ChoreExecute, TM1ProcessExecute, associated files and the 32bit TM1 API dlls in a folder on the TM1 server (see readme in the zip for details)
  2. Start iSeries Access for Windows Remote Command on the TM1 server, set as automatic and select a user that can execute the TM1ChoreExecute
  3. In client access setup: set remote incoming command "run as system" + "generic security"
  4. On your iSeries, add the following command after all your queries/extracts:

RUNRMTCMD CMD('start D:\path\to\TM1ChoreExecute AdminServer TM1Server UserID Password ChoreName') RMTLOCNAME('10.xx.x.xx' *IP) WAITTIME(10)
10.xx.x.xx IP of your TM1 server
D:\path\to path where the TM1ChoreExecute is stored
AdminServer name of machine running the Admin Server service on your network.
TM1Server visible name of your TM1 Server (not the machine name of the machine running TM1.
UserID TM1 user ID with credentials to execute the chore.
Password TM1 user ID's password to the TM1 Server.
ChoreName name of requested chore to be run to load data from the iSeries.

You should consider setting a user/pass to restrict access to the iSeries remote service and avoid abuse.
But ideally an equivalent of TM1ChoreExecute should be compiled and executed directly from the iSeries.


Quick recovery from data loss

Quick recovery from data loss admin Tue, 2019-11-19 15:30

A luser just ran that hazardous process or spreading on the production server and as a result trashed loads of data on your beloved server.
You cannot afford to take the server down to get yesterday's backup and they need the data now...
Fear not, the transaction log is here to save the day.

  • In server explorer, right click on server->View Transaction Log
  • Narrow the query as much as you can to the time/client/cube/measures that you are after
  • /!\ Mind the date is in north-american format mm/dd/yyyy
  • Edit->Select All
  • Edit->Back Out will rollback the selected entries


Alternatively, you could get the most recent backup of the corresponding .cub of the "damaged" cube:

  • In server explorer: right-click->unload cube
  • Overwrite the .cub with the backed up .cub
  • Reload the cube from server explorer by opening any view from it

Store any type of files in the Applications folder

Store any type of files in the Applications folder admin Tue, 2019-11-19 15:41

The Applications folder is great but limited to views and xls files, well not anymore.
The following explains how to make available just any file in your Applications folders.

1. create a file called myfile.blob in }Applications\ on your TM1 server
it should contain the following 3 lines:

2. place your file, tutorial.pdf in this case, in }Externals or whatever path you defined in ENTRYREFERENCE

3. restart your TM1 service

ENTRYNAME is the name that will be displayed in Server Explorer.
ENTRYREFERENCE is the path to your actual file. The file does not need to be in the folder }Externals but the server must be able to access it

/!\ avoid large files, there is no sign to tell you to wait while loading, impatient users might click several times on the file and unvoluntarily flood the server or themselves.
/!\ add the extension in ENTRYNAME to avoid confusion, although it is not a .xls file, it will be displayed with an XLS icon.


TM1 Sudoku

TM1 Sudoku admin Tue, 2019-11-19 15:45

Beyond the purely ludic and mathematical aspects of sudoku, this code demonstrates how to set up dimensions, cubes, views, cell formating, security at elements and cells levels all through Turbo Integrator in just one process.

Thanks to this application, you can demonstrate your TM1 ROI: none of your company employees will need to shell out £1 ever again for their daily sudoku from the Times.
Alternatively, you could move your users to a "probation" group before they start their shift. It is only by completing successfully the sudoku that the users will be moved back to their original group.
This way you can insure your company employees are mentally fit to carry out changes to the budget, especially after last evening ethylic abuses down the pub.

Of course it exists many sudoku available for Excel, this is one is to be played primarily from the cube viewer, but you could also slice the view and play it from Excel too.

How to install:
.Save the processes Create and Check  in your TM1 folder and reload your server or copy the code directly to new turbo integrator processes.
.Execute "Create Sudoku". That creates the cube, default view and new puzzle in less than a second.

Sudoku in cube viewer
The user can input numbers in the "input" grid only where there are zeroes. The "solution" grid cannot be read by default.
.Execute "Check Sudoku" to verify your input grid matches the solution.
If you are logged under an admin account, you will not see any cells locked, you need to be under the group defined in the process to see the cells properly locked.

You might want to change the default group allowed to play and the number of initial pairs that are blanked in order to increase difficulty.

The algorithm provided to generate the sudoku could be quickly modified to solve by brute force any sudoku. Provided the sudoku grid is valid, it will find the solution, however some sudokus with too many empty cells will have more than one solution.

TM1 services on the command line

TM1 services on the command line admin Tue, 2019-11-19 15:43

removing a TM1 service
in a DOS shell:
go to the \bin folder where TM1 is installed then:
tm1sd -remove "TM1 Service"
where "TM1 Service is the name of an existing TM1 service
or: sc delete "TM1 Service"

removing the TM1 Admin services
sc delete tm1admsdx64
sc delete TM1ExcelService

installing a TM1 service
in a DOS shell:
go to the \bin folder where TM1 is installed then:
tm1sd -install "TM1 Service" DIRCONFIG
where DIRCONFIG is the absolute path where the tm1s.cfg of your TM1 Service is stored

manually starting a TM1 service
from a DOS shell in the \bin folder of the TM1 installation:

remotely start a TM1 service
netsvc /start \\TM1server "TM1 service"
sc \\TM1server start "TM1 service"

remotely stop a TM1 service
netsvc /stop \\TM1server "TM1 service"
sc \\TM1server stop "TM1 service"



TM1Top admin Wed, 2019-11-20 08:44

TM1Top provides realtime monitoring of your TM1 server, pretty much like the GNU top command.


It is bundled with TM1 only from version 9.1. You might have to ask your support contact to get it or get Ben Hill's TM1Top below.

. Dump the files in a folder
. Edit tm1top.ini, replace myserver and myadminhost with your setup


. Run the tm1top.exe

X exit
W write display to a file
H help
V verify/login to allow cancelling jobs
C cancel threads, you must first login to use that command
Keep in mind all it does is to insert a "ProcessQuit" command in the chosen thread.
Hence it will not work if the user is calculating a large view or a TI is stuck in a loop where it never reads the next data record, as the quit command is entered for the next data line rather than the next line of code. Then your only option becomes to terminate the user's connection with the server manager or API. (thanks Steve Vincent).

Ben "Kyro" Hill did a great job developing a very convenient GUI TM1Top.

GUI TM1Top from Ben Hill