Much Faster Processing with MTQ in TM1
TM1 traditionally uses a single core or thread per task. What this means is that when a user opens a view for a cube (or a TI or Cognos BI opens a view), that view is created by a single core. Recently IBM changed TM1 so that it now can use multi threaded queries and it is almost a linear improvement in performance, roughly equivalent to the number of cores on the machine minus 1. So if a query was previously taking 15 minutes and TM1 is set to use 4 cores and there is no other activity on the server, then the query should execute in approximately 5 minutes 15 / (4-1).
MTQ Configuration in TM1s.cfg
To allow the server to process multi-threaded queries (MTQ), specify the maximum number of threads to use when processing queries by adding MTQ=n to the Tm1s.cfg, where n represents the maximum overall number of threads per query.
A slightly more complex use of MTQ is to use MTQ = All, which assigns all cores to TM1 processing or to use a negative number. The negative number uses a calculation of T=M-N+1, where T is the number of cores used by MTQ, M is the number of cores available and N is the negative number. So, if for example you have an 8 core box and want 6 devoted to TM1, you would use MTQ=-3, as this would be 8-3+1=6 cores. Then if you increased the number of cores in the server to, say, 16, it would automatically increase the number used by TM1 to 16-3+1, which would be 14.
Then restart your TM1 server and away you go. Just be a little careful if you have TM1 and Cognos BI on the same server (like a Cognos Express installation) as you don’t want to lock up BI at the same time as TM1.
For more detailed discussion on using multi threaded queries in TM1, head on over to IBM here: https://www-01.ibm.com/support/docview.wss?uid=swg21960495
Testing of Multi Core Processing
Our friends at TM1 Tutorials did some testing of MTQ and came up with the following results (all times are in mm:ss format):
|TM1 Version/Number of threads||Server start up||Cube View Load||Rule Recalculation||Flat file load||Reprocessing feeders|
|TM1 10.1 / 1||18:13||1:00||17:40||6:33||18:54|
|TM1 10.1 / 4||18:01||0:58||17:35||6:31||18:47|
|TM1 10.2 / 1||18:05||1:12||18:55||4:32||17:26|
|TM1 10.2 / 4||15:50||0:23||18:10||4:37||16:47|
For more information, please see head over to TM1Tutorials here.
They also have a great write up of multi threaded queries here.