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
SSAS versus PowerPlay / Transformer
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
Issue with joins using SSAS cube for cognos report
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.
That's What I'm Here For ...
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 the Great Staging Opportunities ...
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
tools to convert MDL, PYI to SSAS?
Anyone aware of any tools that automate the process of converting Cognos Transformer models to MS SSAS? Thank you!
I Did It with the MDL File ...
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