I’ve been all over the place the past couple of months. One interesting project I started during this time is helping prove the viability of using Microsoft’s SSAS cube tech to replace Cognos Transformer. The application is retail and there is quite a lot of data in some of the fact tables (one is nearing a billion rows). Some of the dimensions are quite large but not in the millions fortunately. The challenge for this customer has been that Transformer, being what it is, at these volumes of data, has started to become overwhelmed. Build times are getting ridiculously long and the failure rate, while not high, is enough to be troubling and the recovery from any failure is painful given the long build times. I know there are ways to work around some of the limitations and people have been very resourceful. Go there if you have to I guess. But there are options.
Seeing this I felt compelled to recommend SSAS as the next step. We’re starting to see more Cognos clients in this situation making this decision. The results are just too compelling to ignore. The SSAS tool has become robust, feature rich and is very scalable. Our initial proof of concept confirmed everything we expected. We were able to design and build cubes at the lowest levels of detail, providing a more seamless user experience, and do it with build times that were far, far shorter than those of any comparable Transformer cube.
The combination of SSAS to design and build big, detailed cubes, combined with the BI management and presentation capabilities provided in Cognos is an awesome combination. If you simply do this to deliver cubes to Excel users you are missing the point here. Transformer is just no longer able to handle the increasing volumes of data some companies are collecting. So, fix that by plugging in SSAS, but don’t abandon all the other great things Cognos has to offer. It’s a great marriage.
There is a BUT here. No question that SSAS requires more technical skills than Transformer. Knowledge of the MDX language is mandatory. Technical staff will have a non-trivial learning curve to climb but the alternative is not pretty either. In an environment where SQL Enterprise is already present it may well be an easy decision to make the move to SSAS. Get some help, get some training and do it. This stuff works.
