What is the best BI Cubing engine from IBM – TM1 or PowerPlay/Transformer?  Here is a comprehensive analysis of the two dominant Cognos methods IBM uses for creating cubes for use in Business Intelligence.

This was originally written by Norman Bobo – Corporate Performance Management Professional, Certified IBM Cognos Solution Expert, Mentor and Trainer in Nashville, USA.  Norman can be contacted via LinkedIn here.

The two tools, though they have overlapping functionality are actually quite different. Here are the differences I see:

1) Purposes

Transformer was designed as a read-only BI solution. In this role, it has always been at the top of the market. TM1 was designed as a financial planning application — maybe best described as an in-memory calculation engine. The fact that TM1 can also be used as a BI tool is almost an afterthought. In fact, one of my current clients is doing exactly that — using TM1 as a BI solution. But TM1’s architecture is focused on supporting real-time, in-memory calculations.

2) Dates

Transformer handles dates extremely well. It is Transformer’s greatest strength (but also can be a weakness when you have complex calendar requirements). TM1 does not even have a date data type (though there are a few date functions you can leverage in rules and TI processes). Every dimension in TM1 is just like every other one — even measures are just regular dimensions, more about this later. From this standpoint, the original developers of TM1 took an extremely purist OLAP approach where as the developers of Transformer took a more practical, business-focused approach — again indicating the different heritages of the two solutions. In TM1, you must build your own date rollups. This can be complicated, but once you have done it for one cube, it can generally be re-used for other cubes. If I were IBM, this is the first place I would focus in the next major release of TM1 — it should be helpful not only for BI solutions but also for financial planning solutions. But because of the purist approach taken in the underlying design of the tool, this will likely be a challenge for the developers, both in the underlying technology and on the changes required for the cube design interface (Architect).

3) Levels.

Transformer makes heavy use of levels and metadata for levels. In fact, you can see an underlying assumption in the tool that all dimensions are supposed to have levels and that items in a dimension should belong to a level. In this assumption, there is the further assumption that in most cases, the dimensions will be balanced, though Transformer does provide for unbalanced hierarchies.

TM1, on the other hand, does not even have the concept of levels. The developers of TM1 clearly took the approach that many, if not most, dimensions will be unbalanced and thus the concept of “levels” is not important. This design requirement comes through when you think of the account dimension, which is usually unbalanced, complicated and contains multiple alternate rollups. TM1 handles that dimension extremely well. Transformer does not handle that dimension as well — in fact, it can be downright complicated to construct.

When TM1 was integrated into the Cognos BI platform, the Cognos BI interfaces required that there be levels. Thus a few new metadata components were added in the TM1 control cubes to hold level metadata for dimensions. But these attributes are clearly “quick fixes” and not integral to the design of TM1

4) Attributes

Even though Transformer dimensions have strong support for levels, Transformer has never supported business attributes for the levels (other than basic descriptions). I believe part of the reason for the lack of support for attributes is that the original core BI interfaces from Cognos which used Transformer (PowerPlay, PowerPlay Web and now Analysis Studio) were analysis-oriented and not reporting-oriented. Now that Report Studio is accessing PowerPlay cubes, the need for business attributes is much greater and you see the demand for attributes increasing among the user base.

What is interesting is that TM1 has strong support for attributes, yet because there is really no concept of levels, the attributes are not associated to a level. So, for instance, you may have weeks in a date dimension and thus a need for many level-specific attributes, such as the number of the week within the year. You can create this attribute in TM1, but that attribute is available for all elements in the dimension – even if that attribute is “not applicable” for a year, quarter or month element. This makes using level-specific attributes very confusing in the Report Studio interface. Again, if I were running things at IBM, this would be a high priority enhancement for TM1.

5) Cube building

PowerPlay cubes, as most of you know, are built in batch mode. Although over the years Transformer functionality has been greatly increased to allow cube builders to add incremental data to cubes even throughout a day, the underlying architecture is still background builds of what are essentially static cubes held in files on disk and then loaded into memory to boost query performance. Over the years, much has been done to improve cube build performance, but when you are loading large cubes (tens of millions or hundreds of millions of rows of source data), there is not much getting around that this may take a while. Many companies have developed strategies for performing weekly full builds with daily incremental builds. One thing to note is that if the cube requires calculations, the calculations are performed during the cube build and the results are stored in the resulting cube file.

TM1, being an in-memory solution, is completely different. When you perform a “build” you are actually just loading data into memory. The memory usage simply grows as you build more cubes and load more data. Clearly, having enough memory is critical. The data is held in cells which represent the intersection of dimensions in each cube. Because each cell is “indexed”, it is possible for a user or a batch process to update individual cells. Unlike Transformer, which performs calculations and even some consolidations in batch and stores them, TM1 performs consolidations and business logic (rules) only when a user view includes a consolidated value or a calculated value. Because of this, changes to the data in a cell has the effect of causing the entire cube to be “virtually recalculated”. TM1 cubes can be updated heavily real-time — you are just storing values in cells — but only as long as there are not users also simultaneously requesting views containing consolidated or calculated values. In this way, TM1 has issues similar to relational databases in that when updates are occurring, reads can be held up for write locks. The 9.5.2 release of TM1 has largely resolved issues with locking which in prior release was a significant issue to tackle and was a large factor in the scalability of the TM1 solution.

6) Data Scalability

Since I just mentioned scalability, I will talk about that as well. There are two parts of scalability — the amount of data and the number of users. We will tackle data here and user scalability next.

Regarding the data, both solutions can hold vast amounts of transactional data. Transformer can split large cubes across multiple 2 GB files. The user (and the BI reporting interfaces) see the cube as a single large dataset. TM1 can be installed as a 64-bit application and thus can also hold extremely large amounts of data. In both cases, though, there are some practical limits to the size of the cubes because of the amount of time it takes to re-load cubes. In general, TM1 will load cubes more quickly and, because of this speed, can build larger cubes in reasonable timeframes. If you are able to perform incremental data loads, you may not be limited to cube size as both cube technologies can maintain historical data without having to perform full re-builds except on a very occasional basis.

However, there are practical (and even hard) limitations in Transformer to the size of a single dimension. Transformer simply does not manage large dimensions very well. By large, I mean 100,000 nodes or more. When you start getting over that size, TM1 is a much better solution as it can (and frequently does) handle dimensions in the hundreds of thousands (even millions?) of elements.

Regarding the calculations, as mentioned earlier, Transformer performs most (if not all) business calculations during the cube builds and stores the results. You can perform calculations in your analyses/reports as well. Though powerful calculation rules are available in Transformer, they are oriented more strongly around row-level (intra-record) calculations. The absence of dimensional attributes is a factor in the calculation capabilities. Thus Transformer developers sometimes find themselves doing some calculations outside of the tool and loading the results into Transformer. There are also some capabilities in Transformer around dimensional calculations, but these are limited to a single dimension at a time.

With TM1, calculations can occur at two levels. The first level is during the cube load in Turbo Integrator. You can perform the intra-record calculations as well as calculations that take into account attributes on one or more dimensions. But during Turbo Integrator processes, you are essentially still performing calculations on one input row at a time, just like Transformer. One advantage of using TI to create calculations, as opposed to TM1 cube rules, is that the result of the calculations can be stored and thus will not have to be calculated by the engine when users request that calculated result in their reports. In the case of TM1 cube rules, these calculations are purely dimensional. They are true, pure OLAP calculations expressed with dimension formulas. They are not intra-record calculations such as is used in Transformer or in Turbo Integrator. Unlike Transformer, cube rules can leverage attributes on more than one dimension. The possibilities are almost endless. Also unlike Transformer, one cube can leverage data from another cube in calculations. In this way, TM1 is much more like the Cognos Planning technologies or other Financial planning tools on the market. You simply cannot perform calculations across cubes in Transformer.

7) User Scalability

Transformer/PowerPlay have a clear advantage over TM1 in this area. Since the data is largely pre-aggregated and pre-calculated, the work is all done during the cube build and all the PowerPlay data engine has to focus on is delivering the data to users. This makes the application linearly scalable. For each new group of users, you can either scale up (memory and processors) or scale out (more servers). BI applications which leverage PowerPlay cubes can easily scale to thousands or even tens of thousands of users.

TM1 is not so easily scaled. This is a complicated subject. And at this point, the comparison between TM1 and Transformer becomes an apples and oranges comparison because Transformer is essentially ready only while TM1 also allows real-time updates.

Because of TM1’s in-memory architectures, an entire cube (actually an entire TM1 remote server containing one or more cubes) must be held in a single memory space. Granted, with a 64-bit server, this could be a very large data space.

However, it must be a single memory space and thus must be on a single server (I’ll talk about replication in a second). Before 9.5.2, if updates are occurring to the cube (e.g., user contributions or batch data loads), the users reading the cubes at the same time may be locked out. This was a significant limitation and affected scalability dramatically. However, with the release of 9.5.2, the locking mechanisms were greatly enhanced which has created a significant improvement in the user concurrency of the application. This has allowed more users to access a cube simultaneously, especially during periods when data is being updated. Prior to TM1 9.5.2, a single TM1 application on a single server was limited to a few hundred users. With 9.5.2, you can have many more users (I won’t venture a number … but it is a significant increase).

Both before and after the release of 9.5.2, it is also possible to scale out a TM1 application using cube replication and other techniques to essentially make copies of cubes on multiple servers. This is a huge topic which I won’t broach any further. Suffice it to say that if you need more than a few hundred users in TM1, you will likely have to consider replication, which is much more complicated than the simple scaling out you see with PowerPlay cubes.

8) Cognos BI Integration

Realize that both Transformer/PowerPlay were built long before Cognos BI (ReportNet, Cognos 8 BI and Cognos 10 BI) were released. Thus both products have been “integrated” into the Cognos BI solution. Of course PowerPlay was a part of Cognos when ReportNet and the subsequent versions of Cognos BI were released, so the integration has been strong from the start. But PowerPlay has always been treated as “just another OLAP data source” by the designers of Cognos BI. Having said that, there are features and even “underlying assumptions” in the Cognos BI studios which are built around PowerPlay. TM1, having been purchased by Cognos long after Cognos 8 BI was released, has not been as tightly integrated. You will see more issues when attempting to use TM1 as a data source. I know that Cognos/IBM are working hard to fill the gaps you seen in the Studios. I suspect we will see changes in both the studios and in the TM1 application itself as TM1 is pulled more tightly into the Cognos products.

9) Futures

It is not true that Transformer is not seeing investments. There have been many improvements to the tool since the release of Cognos 8 BI. For instance, Transformer now reads Framework Manager packages. IBM/Cognos also continue to see to it that Transformer will not only access new versions of databases but also take advantage of query improvements with SQL extensions and other such techniques. Transformer has unique advantages and continues to serve an important role in BI applications.

However, it is true that we are unlikely to see the same level of investment in Transformer that we will see in TM1. But that is the nature of TM1 — it has a much broader set of features that allow it to serve many more purposes. As a more general purpose tool, IBM is right to make a stronger investment in this tool. In fact, I expect to see IBM slowly expand the role of TM1 so that it can better serve a pure BI role more efficiently and effectively. But this will take time.

Which is better? Neither. They both have their weaknesses and strengths and for the foreseeable future they both have a place in the Cognos solution set.