Blog

What can TM1 Multi-threaded Queries do for you: An introduction

Posted by Mike Cowie

Find me on:

Mar 7, 2014 7:13:28 AM

I have a confession to make: the TM1 Server engine is, by far, my favorite thing about IBM Cognos TM1. Since I began working with TM1 in 1997 it is the TM1 Server that has, more than anything else, enabled us to deliver business analytics solutions that delight our customers. In TM1 10.2 IBM introduced a new feature to the TM1 Server called Multi-threaded Query (MTQ, for short) which most of us at QueBIT and in the TM1 community believe will be a ground-breaking enhancement to what is, arguably, already a best-of-class multidimensional database engine.

The main purpose of this blog post, the first in a series inspired by the release of MTQ, is to give you a high-level overview of MTQ and what it means for your TM1 applications. In addition, this post provides an introduction to some of the research happening in the QueBIT Labs to both investigate ways of maximizing the benefits of MTQ as well as measuring the extent to which it improves performance compared to prior TM1 Server releases.

Background: How does TM1 Multi-threaded Query (MTQ) work?

Prior to TM1 10.2, when you and other TM1 users request data or initiate operations on the TM1 Server a single execution thread is activated on the TM1 Server to fulfill each user’s request. Each execution thread in TM1 is typically serviced by one processor core at a time on the physical (or virtual) server. For example:

TM1_Server_request_processing_before_MTQ

Figure 1: TM1 Server request processing before MTQ

In this illustration, if your TM1 Server had 16 processor cores available it could use a maximum of 3 of those cores at one time to service all of the above user requests. That leaves 13 cores of processing power sitting idle on the TM1 Server. It also means that each request’s processing time is dependent on how fast a single execution thread can run on the physical or virtual server that hosts the TM1 Server application.

Continuing with the same example above, let’s look at what happens if we set MTQ=8 using TM1 10.2:

TM1_Server_request_processing_after_MTQ
Figure 2: TM1 Server request processing after MTQ

With MTQ active, notice that data query-type requests, such as getting data from a TM1 cube view, can now be handled by multiple processing threads on the TM1 Server at one time (8 in this example, since we set MTQ=8).

The payoff with MTQ: if that cube view retrieval took 2 minutes to return prior to TM1 10.2, it might only take 15 seconds to return in TM1 10.2 with MTQ active; if more cores were available and we set MTQ=12, it might take as little as 10 seconds to return! In addition, the TM1 Server is now making much more thorough use of the processing power that is available on the server.

I hope this helps to illustrate why many of us think MTQ is likely to be so ground-breaking for TM1 both for its potential performance gains as well as its ability to more fully utilize hardware resources. Although the concept of multi-threaded queries is not new to database applications, it is exciting to see IBM making those kinds of investments in improving the TM1 Server engine.

How much better off are we with TM1 10.2 and MTQ?

While I truly believe MTQ is going to be a big step forward for TM1 for the reasons highlighted above, I also see MTQ’s addition as an opportunity to measure just how much has the TM1 Server engine actually improved since I first started using it. I don’t want to sing TM1’s praises just because I’m a longtime, loyal TM1 practitioner and devoted fan of the technology – I think it’s important to consider the following key questions:

  • What if we’ve been wrong to assume that TM1 Server performance is actually improving significantly with each release?
  • Is it possible the performance improvements we’ve seen over the years are mostly a result of improvements to the hardware on which the TM1 Server runs and not a result of TM1 Server software innovation?

In future posts in this series I’m going to share with you some research undertaken in the QueBIT Labs to help answer these questions in light of the release of MTQ. In addition, I’ll be sharing some recommendations for taking the most advantage of MTQ in your applications.

Closing Thoughts: Are you considering an upgrade to TM1 10.2?

MTQ represents a significant change to the core TM1 Server engine. Consequently, caution is advised in these early days for anyone considering the use of MTQ in production – indeed, there have already been fixes released by IBM, like 10.2 FP1 and 10.2 FP1 IF1, the latter of which addresses potentially serious problems with MTQ found in the initial TM1 10.2 release.

Although it may be tempting to rush to upgrade to TM1 10.2 now, the reality is that it may take another Fix Pack or Refresh Pack before MTQ is truly ready for widespread deployment.

Topics: TM1

   

Blog Search

Subscribe to Email Updates

Follow Me