IBM Cognos TM1 is a high performance, in-memory, OLAP database, and as such, an OLAP database differs dramatically from a relational database in how data is stored and accessed internally. Data in an OLAP database is (fundamentally) stored as an array, and to access data in this array, you need the coordinates (or address) of an item in the array in order to access it.

What does all this mean? It means that when you perform a query that returns 10 columns and 50,000 rows, TM1 has to effectively perform 500,000 lookup operations for each data point in order to render this view. The more columns and rows you add, the more time it takes to render the query. Consider this in conjunction with the typical behavior of relational based applications, such as QlikView or Tableau, where the natural behavior is to operate on the transaction level detail. In essence, relational style applications want to have access to ALL the data from the cube and this wreaks havoc on OLAP databases. A transactional level query in an OLAP system is tantamount to The Query From Hell.

TM1Connect SmartCache solves this problem using three separate caching mechanisms which dramatically improve query performance and significantly reduces the load on the TM1 server. TM1Connect SmartCache is comprised of three distinct cache stores:

• Session Cache
• Dimension Cache
• Global Cache

Session Cache

The Session Cache is a temporary store which usually only lives long enough to service data discovery queries. Many relational style applications interrogate data by performing several queries in rapid succession to get an understanding of the content and structure of the data, such as:

SELECT count(*) FROM SampleView
SELECT distinct(Product) FROM SampleView
SELECT Customer, sum(1) FROM SampleView GROUP BY Customer

Only the first query actually retrieves data from TM1. The subsequent queries use the Session Cache to service all of the subsequent queries. Once the Session Cache time limit is reached, the next query will pull once again from TM1.

::: tip Session Cache is unique and not shared between users in order to comply with TM1 security policies. This means that if two users request the same view, each (initial) request will result in a pull of data from TM1. If security is not required on the data, consider using Global Cache so that multiple users can leverage the same data but pulled only once from TM1. :::

Dimension Cache

When TM1Connect pulls information from TM1, it does so in a manner to optimize performance. When a view is requested that contains hierarchy information, or dimension attributes, TM1Connect pulls dimension information separately from cube data; and it does so in a parallel fashion.

Because the dimensional information is pulled separately from cube data, large or frequently used dimensions can be cached in advance of the view requests and thereby improving performance, even on uncached views.

::: tip Dimensions that are frequently changed should not be cached, such as attributes which users control (i.e. Employee Name) or a view may not return the correct information.
:::

Global Cache

Global Cache is a temporary store that typically has a longer lifespan than Session Cache, but generally operates in the same manner. The difference between Session Cache and Global Cache is that the data is shared amongst all users and user based security does not apply.

Views configured in Global Cache are loaded from TM1 using the administrative credentials supplied during the installation of TM1Connect. Depending on how the view is configured in the Global Cache, the information can be pulled from TM1 on TM1Connect server startup or on the first request. Once it is in the Global Cache, all users can leverage the data stored in TM1Connect without burdening the TM1 server.

Globally cached views may also be configured with two types of refresh methods:

• Interval - Periodic (time based) data refreshes
• On Data Changed - As data changes in TM1

Interval uses a fixed time period for when data should be refreshed in the global cache. TM1Connect will automatically query the data in TM1 at each interval and keep the data for the duration of the lifespan of the cached view. This method works well for frequently updated cubes.

On Data Changed refresh method periodically checks TM1 to determine if the cube data has been updated. If data has changed, TM1Connect will pull the new information and store it in the global cache. This method works for cubes with low volatility.