Olap cubes: MSAS and Cognos

can anyone comment on difference between MS Analysis Services and Cognos transformer cubes? Are there differences in storage, processing, and the many files that transformer utilizes and creates, or anything else pertaining to processing or maintenance of OLAP structures.
thanks, Kim

Hi, Kim:

As OLAP expert Nigel Pendse (of www.olapreport.com) notes, Microsoft SQL Server Analysis Services (SSAS) provides more sophistication and greater scalability than PowerPlay / Transformer. Having implemented all major Cognos applications for over thirteen years (I certified on Impromptu and PowerPlay / Transformer, among other apps), and MS OLAP 7.0 / Analysis Services 2000 and 2005 / other components of the MS BI solution since inception in very large organizations, I am finding he integrated MS BI solution to excel in many ways. (I am doing migrations from Cognos to MS BI at an increasing rate, as the contracts of the once-dominant player begin to expire amid the dramatically growing market share of MSSQL Server 2005 / SSAS, et al. I also frequently contrast the two in my articles, referenced below.)

Pendse goes on to note that SSAS implementations can be dramatically larger than those of PowerPlay / Transformer. An SSAS cube can comprise gigabytes of data, including > 100 dimensions (not including virtual dimensions, which would provide even more extensibility). Dimensions can contain millions of members in SSAS. Scalability is also enhanced by the capability to perform cross-cube calculations and to join cubes (even to perform cross-Internet joins). He emphasizes that SSAS processing optimization can be enhanced dramatically with SSAS’ strong parallelism. SSAS also provides writeback capability, which can become extremely useful in situations where we wish to input budget numbers to a cube, and things of this sort.

SSAS can have a larger learning curve – at least to attain to true efficiency in their use, as well as to use features that PowerPlay / Transformer doesn’t offer, like Data Mining– and the MDX query language is difficult to many upon first encounter - although the capability to directly access MDX in SSAS is a strong point when it comes to precision tailoring the solution to a specific environment, as well as from the perspective of tuning and optimization. (The “drag and drop” environment of PowerPlay / Transformer will seem a godsend to unsophisticated users who are attempting to build a basic cubes and reports, but this apparent ease of use soon loses its savor among significant limitations, as is typically the case with graphical front ends of this kind.)

SSAS offers a much richer security model than PowerPlay / Transformer, according to Pendse. Moreover, SSAS cubes can have many partitions, which can be spread across multiple servers. Further, SSAS cubes can access many diverse data sources (Oracle, DB2, SQL Server, Access, etc.) simultaneously (while this is not impossible with PowerPlay / Transformer, it is much more difficult to accommodate). SSAS allows us to manage data with differing granularity, which becomes highly useful within the context of budgeting at different (typically rolled-up) levels than the levels that contain actual values., etc. As I mentioned earlier, forecasts can be updated easily via writeback, among other methods.

The strengths behind the PowerPlay Enterprise Server and Transformer combination lies within the fact that it has been around for some time (as I have said, I've worked with it since its earliest versions, and am well acquainted with its strengths and user-friendliness - as well as its hefty cost!). PowerPlay / Transformer integrates well with Cognos’ own front-end tools and presentation layer, etc., as well as with Cognos Impromptu as a SQL / relational query tool on the induction side of Transformer, but does not work with other tools otherwise, except if one counts the MS Excel Add-in, or if one tailors means of calling / activating / operating external executables, etc. (SSAS works with myriad front-end / reporting options – including Cognos PowerPlay - and therefore provides higher versatility. ) PowerPlay / Transformer works with UNIX as well as NT / Windows Server environments.

PowerPlay / Transformer can be extremely expensive, particularly with large user communities. Consulting is expensive and often not top notch, unless one goes direct to Cognos and gets the high dollar consultants. Training, too, is expensive, and often typically requires travel to public training locations to learn the tools on a simplistic training database – trainees often return to their own environments unable to write reports against their own data. SSAS, in contrast, ships with MSSQL Server, and, as long as it is installed on a server / servers properly licensed for MSSQL Server, incurs no incremental cost from a licensing perspective. Training and consulting options grow more plentiful by the day – I often train on client sites with their data, as an example, and can offer a great deal of assistance in conversions as I know both tools well.

Let us know as you develop specific further questions! You're choosing between the two best of breed, beyond doubt!

Bill

hi Bill, thanks for your insight on this. Are there many limitations to using SSAS with Congnos Reporting? Can Cognos FM translate everything properly to be used for prompting, filtering, and calculating. Does anything get lost in translation? any issues with datetime data types from SSAS cubes to RS?
Kim

Kim:

Connectivity is straightfoward and sourcing the SSAS cubes pretty seamless, in general - LOTS of redundancy of reporting costs, when you can do anything in RS / Proclarity - and more - which, of course, ship with MSSQL Server at no incremental cost. Vary hard to justify the extra expense to knowledgeable management and shreholders ...

Much of this is new, but my last exposure to the latest release of C8, within the context of using SSAS as a data source - turned me off due to 1) no direct MDX (Cognos is doing it's own variety, and it's not too bad - just the functions are kinda weird reworks of a few already existing MDX functions that are supposed to come across like something more "English" - it's painful to someone who already knows how to get what they want with MDX ... like a root canal! :) ;

2) Last I checked, some of the SSAS cube objects / capabilities are not exposed (kinda figures economically that the "Big Sista C" wouldn't want to make things in AS work in their reports that they can't do in their own cubes ... although they will virtually always say "coming in the next release, etc." Although that's just a cognitive disorder among sales weasels in software circles in general, I suppose ....);

3) Because direct MDX is not in the cards, many of the vehicles I use to tune / generate powerful prompting, etc., are not available (although I'm sure there are ways to get to it with research and workarounds.) This becomes especially tedious when doing superpowered reporting like histograms, etc. (although you can always posit the logic in the AS layer and simply pull it into the reports, if you know all the pieces ... I have an article dealing with a simple example, coming out in a month or so ...)

The primary reason my clients are tossing Cognos to go fully MSBI is the excessive cost of a redundant reporting layer for little advantage. If you hear people push Cognos for "easier cube design," look closely at exactly what they''re saying. That likely means Transformer (added cost), and Transformer is used to design proprietary Cognos cubes - you still have to design AS cubes via AS, although you can source them via the connection software into the Cognos reporting engine. So if you intend on using AS cubes, your paying a great deal for the reporting layer. (And Cognos cubes, as I'm sure you know, can only be sources to Cognos reporting ...)

Not aware, specifically, of any date / time issues. (The specificity of the question tends to lead one to sense there's some less-than-enjoyable experience there ... :) )

If anyone tells you that you can do ANYTHING in C8 or PowerPlay with an AS cube source that you cannot do in the integrated MS BI solution (where DB, AS, and Reporting are all part of the same license cost), send me a description of "what that might be" at my contact info below. I've never found it to be the case yet, and love the articles material that comes out of these "challenges." The problem is that not too many really know both toolsets that well, and so the claims are typically ill founded. and based upon an incomplete understanding.

Keep us posted on your process ... this is one area within which I intend to specialize for some time - at least until Cognos market share winnows away to "boutique" status ...

HTH,

Bill

Hi,

I am using a SSAS cube for reporting in cognos 8.2. But in my report when I display 2 dimension and a meausure every thing go in to a cross join though joins(relationships) are defined in the cube. If I use SSRS for reporting I am getting proper data. Please suggest.

Have you made any attempt to write your own query with the Cognos - MDX equivalent? Or are you simply attempting to "drag and drop?"

Thanks.

Bill

Bill,
I have been doing some research on Powerplay Transformer vs. SSAS for the last couple days, and I wanted to thank you for providing such detailed comments. I found several articles you authored on different websites, and they have helped immensely.

Concluding from this post, that SSAS is your preferred recommendation, I wanted to just check if that opinion has changed or continues to be the same as of this date. I have read a few posts on BusinessIntelligence.ITToolbox.com where Cognos reports have been slower when talking to SSAS cubes.
http://businessintelligence.ittoolbo...re-set-1594131
What do you think?

In our environment Cognos is the current end user reporting solution that is approved and accepted, and we could use either SSAS 05 or Cognos Transformer to build cubes. I am leaning towards SSAS given your recommendation, and also being biased towards microsoft tools and technologies since I do quite a bit of MS development, and assuming it is going to be easier to learn SSAS. What I don't want is - To build a cube in SSAS and find that Cognos reporting will not be optimal in some aspects. If any what are those?

Thanks again.

Kaushal

The Cognos / SSAS combination will dwindle over time, due primarily to marketing forces. If you go with the SSAS cube, you can prepare for this by 1) replicating the existing report set within the integrated MS BI solution (all of which you already have with the MSSQL Server license anyway - so you save the many thousands that Cognos is costing to replicate the reporting layer you could be generating / supporting better, and for no incremental cost, anyway); 2) when the organization abandons Cognos (and it eventually will), you will have already positioned yourself to continue uninterrupted operations.

You could also be a real champion (in management's eyes) and create a parallel reporting structure in the background (great way to learn the MS BI solution, BTW), then actually suggest the wisdom of shedding the huge cost of a redundant reporting system. Talk about job security - at the same time you enhance your marketable skill set!

Whatever you decide re: the reporting layer, developing MS cubes is far safer than using Transformer - if / when they do, indeed, ditch Cognos, assuming the proprietary Transformer cubes, you're left with cubes that cannot be read by other applications, while, with the Analysis Services cube you can continue to report unabated, should Cognos be given the old "heave ho."

I'm advising all my old Cognos clients, and the public at large, along these lines. You'll be glad you made the break, believe me, and preparing now with the MS Reporting layer means you can make the transition when you decide, and not be forced into a crisis for economic / other reasons.

Good Luck, and feel free to get back with specific questions in this arena - I convert Cognos to MSBI on a regular basis, and can offer a great deal of insight.

What is the version of Cognos?

Bill

Thanks Bill. The version is 8. I will certainly get back to you when in need. I appreciate your kind help.

Please feel free to consider me as a resource, particularly in a Cognos - to - MS BI migration, but also in getting up to speed in mirroring your current Cognos reporting structure within MS BI.

I have yet to find anything that I did over years of Cognos consulting that I cannot replicate in the integrated MS BI solution - and most everything I have been able to do far better, at a fraction of the cost and development time. I have done more Cognos - to - MS BI migration work than any consultant I know, and I am happy to assist in any way that I can ...

Thanks again.

Bill

Keep in mind that one of the best things about the MS BI solution is the fact that you can stage in the MS Reporting Services and other layers while still on Cognos as the "primary" package. This will afford you both learning opportunities and an "already there" status when you decide to dump those Cognos licensing costs!

Bill

Anyone aware of any tools that automate the process of converting Cognos Transformer models to MS SSAS? Thank you!

There have been times when I extracted structural info from the MDL file, stored it into tables, and then built AS cubes based upon the content, with varying degrees of automation.

What you have to keep in mind is that the AS environment will offer several opportunities to enhance the Transformer model to take advantage of capabilities that exist in AS that do not exist in Cognos. Morever, depending upon how old the Transformer models are, it might be an ideal time to re-examine their structures, perhaps to enhance / optimize to more closely align what the cube contains with current business requirements (the build logs can be helpful here, too, to a small extent ...).

I've done lots of this, and convert Cognos to the MS BI solution on a regular basis (this sort of work forms a large part of my practice, as it offers a way for many large companies to shed six-plus figures off their BI budgets quickly ...). Let me know if I can assist with specific needs in this arena.

Bill

Bill:

Does Cognos8 connects to AS 2008?

Vendor knows better.

Interesting commentary on the above thread. I try to be technology agnostic with all BI tools and am currently running into clients hitting the limitations of Cognos cubes. I wouldn't recommend building a large scalable BI solution using SSAS/SSRS when the tools are lacking a manageable meta data layer to produce a self serve reporting environment. (I do recommend the MS platform only if there are cost factors limiting an implementation - you get what you pay for) In addition, once a Cognos cube reaches the physical limitations of partitioned cubes - my expectation is another platform like Terradata or Netezza would be required.

Would be interested in hearing other new solutions before recommending the limited and labor intensive implementation of SSAS?

Regards
John