Migrating from Cognos to Microsoft BI
If you are considering moving from Cognos BI to Microsoft BI (and as a Microsoft BI guy, I’ll usually recommend it), it’s worth knowing what’s what in each stack and where the similarities lie. It’s also important to understand how much effort is going to be involved to see if there’s a genuine business benefit. I’ll frame the discussion from a Cognos to Microsoft Equivalent perspective, though some matches are more approximate than others. My reference for the anatomy of the Cognos stack is here: http://www.cognos-bi.info/cognos8.html
Why Migrate from Cognos to Microsoft?
There are myriad reasons why anyone may want to move from Cognos to Microsoft, but i’ll probably surprise you by saying functionality isn’t one of the bigger drivers. Each suite has its own strengths and weaknesses and on balance there’s probably not enough strength on either side to move for that alone, unless addressing specific business needs. I’d view the main reasons as:
- Licensing- Cognos is a suite of separate applications that need to be licenced separately, as opposed to SQL Server which is a single product. There can be significant cost and administrative savings from moving to the Microsoft suite. How much this works out to be will depend on your licensing agreements, but reality is Microsoft is the cheaper option.
- Existing SQL Assets – Many times companies have SQL Server licences for their databases and want to extract more value from those licences
- Cognos 8 upgrade- Your business may be looking at the cost of moving to Cognos 8 from Cognos 7 and not seeing enough ROI potential.
- Performance- many Cognos users report unsatisfactory performance from deployments
- Uncertainty- With the IBM buyout of Cognos, the number of products likely to be in the mix has gone up – what’s going to stop being supported? What other tools will I need? With Microsoft you have the certainty of a single integrated platform with a long and clear roadmap
- Skills availablity – there are simply more Microsoft BI developers out there, and generally they cost less to employ and are cheaper to train.
- Application Integration- Microsoft applications are quite open in terms of connectivity, customisation and integration with other applications. Cognos is a closed box . For example anyone can talk to an Analysis Services cube, but no non-Cognos application can talk to a Cognos cube. Reporting services can easily be extended with custom controls.
Decision Stream / Data Manager = SSIS (Integration Services)
Decision Stream (Cognos 7) and Data Manager (Cognos 
– though there is minimal difference between the two – are Cognos’ ETL tools. They are showing their age badly, and are very “black box”. I personally loathe the clunky interface and untunable performance. But – they work. The SQL Server equivalent tool is Integration Services – a much more up to date, faster and configurable ETL tool. SSIS is a more generalist tool compared to Data Manager, and so it requires a bit more thought (though not a lot) for building Data Warehouses. However if you have large volumes of data to shift, Cognos simply cannot compete.
The reality is ETL is one of the hardest components to migrate. It often has a lot of business rules (and in Data Manager it’s possible to hide them in a million different places) and if you get the migration wrong, you stand to devalue your existing DW. In any migration scenario this would likely be the last on the list, unless addressing a specific need. I would generally leave this as a legacy system that gets superseded over time.
PowerPlay = SSAS (Analysis Services)
Cognos cubes are built in PowerPlay in both Cognos 7 and 8 (in fact there was no upgrade of Powerplay in Cognos 8). Whilst adequate, there are many stories of business migrating to SSAS for significant performance benefits – I am aware of one client whose cubes processing time went from 24 hours to 30 minutes by migrating. SSAS has significant market penetration – getting hard figures is difficult (without paying a lot of money – last public figures are to 2006) but Microsoft is the leading vendor by a significant margin – so obtaining expertise is less difficult (though as with ETL, true OLAP wizards are rare and expensive creatures).
Cubes are one of the easier components to migrate (unless you have very complex ones), as any well designed cube is usually sat on top of a robust ETL which handles the really complex business rules. In the best case scenario you just have to build your dimensions and measures and the job is done.
Report Studio = SSRS (Reporting Services)
Technically speaking, Visual Studio is the direct equivalent as that is the design environment, but Reporting Services is SQL Servers’ Reporting engine. It provides fixed parameterised reporting with the capacity to provide automated deliveries through easily managed dynamic lists. SQL2005 was functional but a little lacklustre, but in SQL2008 has come on leaps and bounds. It’s not the most stellar part of the Microsoft BI stack, but flat reports don’t usually need stellar functionality.
Migration of reports is usually pretty straightforward – you have the layout and formats already – and once the underlying cubes are set up it’s simply a matter of rebuilding each report.
Query Studio = SSRS Report Builder or Excel
Query Studio is a user tool for building simple reports, functionality which is carried out in the SQL world using a tool called Report Builder, or even just though Excel (see the section below on Analysis Studio). Personally, I don’t like Report Builder, though the next edition (3.0) is supposed to be an improvement – but then so was 2.0, so I’m a little sceptical. However if you have good cubes, or have access to the forthcoming PowerPivot, most analysts who want ad hoc data will migrate rapidly to Excel.
This is unlikely to be a significantly used application so the change in functionality probably won’t be an issue, but here Cognos has a better offering.
Analysis Studio = Excel
For working with cubes from an analytical context, Excel is Microsoft’s tool for the job. Using functionality based on Pivot Tables you get a capable (though admittedly not perfect) tool for drilling down, slicing and performing analysis on OLAP data. Unless you have very demanding users, Excel will meet most needs – especially if you have upgraded to 2007 (and if you haven’t, you really really should). If you do have demanding users, there’s a host of 3rd party applications which plug in to excel and can dress specific needs.
Further to this basic functionality, workbooks with connections to SSAS can then be uploaded to SharePoint and distributed via a set of technologies called Excel Services, allowing for fast dissemination of results.
Metric Studio = SharePoint
Metric Studio is aimed at Dashboarding and Scorecarding. Microsoft at one point had an offering called PerformancePoint to address this, but it suffered a premature demise, but the relevant components migrated into SharePoint, and anyone with an Enterprise licence can install them. This isn’t a particularly exciting tool for either suite so I’ll move on. The migration path is similar to that to SSRS.
Cognos Connection = SharePoint
Cognos Connection is the BI portal for the Cognos suite. Sharepoint is the same for the Microsoft stack… and an Enterprise CMS to boot, plus a whole heap of other things I have only limited awareness of. Cognos Connection will have better integration with the BI Metadata, Sharepoint will have better integration with your Enterprise generally. It depends what matters more to your business.
Migration here depends on the extent to which you have SharePoint deployed in the organisation already. If it’s there, it becomes a manual exercise in migrating content. If its not, then it’s a major enterprise change.
Bits that don’t match on either side
Of course there are areas where the two suites don’t quite tie up. Cognos has Planning, which Microsoft did, but now doesn’t. Microsoft has PowerPivot, an in memory analysis tool which Cognos hasn’t got a counter offer for. Framework Manager has an approximate equivalent in the Data Source Views that underpin SSAS cubes, but the implementation is quite different and more database oriented. Content Store, the Cognos metadata store has no match in the Microsoft stack and can be perceived as one of the MS BI stacks bigger weaknesses. Event Studio is approximately matched by SQL Server Notification Services. Of course Microsoft’s whole underpinning is based on SQL Server, a robust enterprise database system with scaling for massive warehouses and Master Data Management which Cognos has no direct offering for.
Final Comments
Undoubtedly i’ve got a few things wrong here – my knowledge of the Cognos stack is not as detailed as that of the Microsoft Stack – and I’ll welcome any corrections from the Cognos Community. This post is intended to help those with Cognos installations understand what Microsoft has to offer, the applications involved and what they translate to in Cognos speak. Some of the features I mention are due in SQL2008R2 which will be released in a few months.
If you are looking for an answer to the question “Should I Migrate?” then the answer will always be that it depends on your circumstances. As I said at the outset, each stack has its own strengths and weaknesses and each one may address your business needs better than the other. This is something that needs careful analysis from experienced BI professionals. I look forward to the debate this is likely to promote!
This is a very helpful post! I have no doubt that I’ll refer to this in the future. Hopefully there are additional comments/posts that further clarify any of the overview you’ve provided. But overall, your summary appears to be quite thorough.
For your information, I’ve posted links to all Microsoft BI Case Studies referencing Cognos migration and comparison:
http://capstonebiblog.blogspot.com/2009/12/microsoft-case-studies-performancepoint.html
Best Regards
Jason
Very informative article, almost a must have for any RFP when considering Cognos Vs MS BI. Thanks for sharing this informaiton.
My personal experience has been that SSRS (2008) has some reasonably significant functional limations compared to Cognos report studio (particularly around paramater pages but also in report layout).
Also, SSAS cubes have a lot of the metadata capability that is found in Cognos Framework Manager (and seems very similar conceptually if you set up your SSAS cube with real-time ROLAP).
Very informative. Thanks for sharing.
Do you have similar experience with migrating from BO to SSRS?
I haven’t but would consider that a BO Universe translates to a Microsoft DSV, and obviously report translates to SSRS. Migration depends on the complexity of the BO reports – if you are only using basic functionality, SSRS is a good replacement. If you are getting into the more advanced functions you need to consider if the reduction in cost is worth the loss of functionality.
From my understanding BO is eyewateringly expensive for what it is, so migration should be on the mind of any IT manager. I know within my consultancy we have means of automating these processes, but obviously I can’t share that here.
Note from the BI Monkey: These below comments were made on another forum by Rob Walker who has kindly agreed to let me post them on the blog:
Certainly an interesting article and I agree that careful analysis is needed before migrating to the toolset of choice and it does very much depend on your existing infrastructure, investments and skills.
If you currently have no BI investment but are using SQL Server then it would seem logical to seriously consider the Microsoft offering. However, if you are currently using SQL server and thinking of migrating to another platform, or you are already using Oracle or DB2 then less so. That is, would you invest in SQL Server to only use 10% of its features and also pay for your Oracle/DB2 licensing? In this respect Cognos has an advantage in not linking you into a specific database platform.
To be honest I didn’t quite agree with some of your main points
Licensing: The Cognos licensing strategy is handled somewhat differently from SQL Server. It tends to be a lot more granular and based on user role e.g. recipient, consumer, business author, administrator etc. Instead of licensing the whole product you can choose to pay for the studios you need and the roles that need to be performed. Why pay for something, such as Analysis studio that you have no business need to use? In this respect the licensing approach allows you to add and take away based on the business environment.
Cognos 8 Upgrade: I wasn’t sure if you were meaning the cost to upgrade licenses, the effort to upgrade or talking more generally. If you as a series 7 Cognos customer are still paying the annual maintenance then generally the upgrade path is a simple swap of like for like and the cost is minimised. In this respect it is the same as having a Microsoft agreement and upgrading SQL Server versions. If it is effort required then IBM provide a set of tools to handle the upgrade and minimise the impact. Generally though you tend to find that businesses use the opportunity to re-define their BI requirements instead of porting existing series 7 reports.
Performance: On the whole I’ve always found unsatisfactory performance to be a product of bad database, metadata or report design and less the application itself. Very rarely have I found the performance issue to be with the application itself. So I think in this case it is more about the workman than his tools. This is equally true of badly designed Microsoft implementations. I think you would need to provide specific examples to justify the performance comments.
Uncertainty: Maybe a couple of years ago when IBM purchased Cognos there was a degree of uncertainty but it is clear now that Cognos is core to the IBM Information Management suite. The road map is clearly defined and with Cognos 10 prepping for release it is likely to remain core to the strategy. If I was a Microsoft customer who had invested in Performance Point and then had to purchase Sharepoint I may not agree with your point about a long and clear Microsoft roadmap.
Skills: Generally agree with you. I did notice that IBM at long last has released a free developer version of the Cognos suite so hopefully this will lead to an improvement but Microsoft do have the edge.
Application Integration: I can’t agree with you that Cognos is closed. The software Development Kit and Mashup API means that it can be integrated with most applications. Also, free with the BI server is the Cognos Office application that integrates Cognos BI seamlessly into the Microsoft Office suite.
You make some useful comparisons with Microsoft and Cognos but then reference Sharepoint and therefore I think if you are going to make a like for like comparison you would also need to reference the IBM offerings that tend to get bundled with Cognos.
Maybe for discussion points of difference should be:
IBM DataStage vs SSIS: I think this would be a meaningful comparison.
Powerplay vs SSAS : I wasn’t sure about the comment of no upgrade in Cognos 8. Would you be able to expand on this. Possibly the nearest IBM offering would be cubing services in DB2 that does the same sort of thing as SSAS.
Metric Studio vs SharePoint. I wouldn’t exactly say that it’s not an exciting tool. You could view it as being a key application in the Cognos Performance management strategy. The Cognos offering is an integrated set of applications based on a common metadata layer defined in Framework Manager. For example, you can set plans/budgets in Cognos Planning, monitor compliance to plans in Metric Studio, examine the summary data via Analysis Studio, drill down to the detail in Report/Query Studio and finally re-plan if need be. The objective is to make a business more re-active to its surroundings.
Cognos Connection vs Sharepoint: Cognos connection is not a content management system although neither is Sharepoint part of SQL Server so you would need to do a comparison with the comparable IBM products.
Other Bits:
Cognos TM1 seems similar to PowerPivot.
Framework Manager is the common metadata modelling tool and is used to provide the glue for the Cognos application suite. I think Cognos Viewpoint may be a closer match to Data Source views in that it provides for the management of a common set of dimensions/hierarchies across the Cognos applications.
The Content Store is just a physical database than holds the metadata and configuration information for the system. It would be interesting to clarify how this is the biggest weakness?
SQL Server. Cognos is obviously not a database so you would need to compare like for like and introduce DB2 or the Balanced Warehouse into the equation. Again it all depends whether you like putting all your eggs in one basket or whether you prefer the flexibility to not be tied into a particular database vendor for your BI applications.
Rob, thanks for your comments – I’m glad of the input, and think you make some fair and reasonable points. I’ve got a few responses to these.
With regards the database platform, if you have Oracle / DB2 / any other DB platform, you don’t get any BI Tools without getting more licenses. Cognos may be platform independent but it’s still an additional cost. Suggesting BI is only 10% of SQL Server’s functionality is probably a bit off the mark – it’s a core part of the SQL Server offering as far as MS are concerned.
Licensing – your granularity argument is fair in the sense that you only pay for what you use – the problem I invariably see is that even with this granular pricing, it still ends up costing more than SQL Server.
Cognos 8 upgrade – to be fair, I’m referencing one recent client site. They felt that upgrade cost in terms of consultancy, effort, etc. wasn’t going to offer them much. It is very much a client specific thing, rather than a blanket statement.
Performance, true, architecture is more likely to blame, but Microsoft has clear case studies where similar setups simply performed better on SSAS. However, I don’t doubt Cognos have one’s in the other direction
Uncertainty – touché about PerformancePoint! My comment was based around feedback I heard from last years Cognos Forum here in Australia, where the prevailing customer feeling was confusion.
Application Integration: I’ll have to look at this, they’ve obviously changed tack.
When we get into IBM offerings it’s a different ballgame, I agree.
I don’t know enough about DataStage to make any comments.
PowerPlay vs. SSAS – I’m just going on a comment on my reference site, so it may not be accurate.
Metric Studio – I’m an ETL guy so I always think reporting tools are boring and simple
A fair point about SharePoint not being part of the SQL stack – that point was more to show MS had an approximate offering.
PowerPivot is definitely a very different beast to TM1 – think more like QlikView – Cognos definitely has nothing to match at this point.
Framework manager is I agree only a very approximate match to DSV’s – the MS stack doesn’t have a direct equivalent to it.
The content store isn’t a Cognos weakness, it’s a Microsoft one – i’ve clarified my post on this.
Once again, appreciate your input
Cheers, James
Thanks for a great analysis short enough and clear enough to share with my management.
I would an another to your list of “bits that don’t match on either side”. As far as I can tell, it is much easier to extend Analysis Studio into the Data Mining arena than it is Cognos PowerPlay. That will be something we will look more closely into as we decide this year whether to upgrade to Cognos 8 or make the switch to Analysis Servers and Reporting Services. Our underlying database is already SQL Server.
That’s true – Cognos doesn’t offer anything in the Data Mining space and the SSAS tools are pretty good – i’ve used them on a couple of projects. It’s a very under utilised part of SQL Server and undeservedly so, though the high barriers to entry in terms of skills may expalin why. Data Mining is as much an art as a science, and the right people aren’t cheap. That and it’s not a very well understood technology, meaning selling it to the business as a project can be pretty hard.
Just a quick update about the Data Mining and Cognos that I came across recently
Cognos doesn’t have anything like that at the moment but Rob Ashe, the IBM GM for Information Management ,in a recent article indicated that the newly acquired SPSS will be integrated into the next major release of Cognos. This will be directly available in Report Studio
Cheers Rob
Hey BI Monkey,
Thanks a lot for this article. I’m coming from the opposite direction (I’ve worked with Cognos BI tools for years and have been asked to scope possibly moving to Microsoft SQL Server 2008 BI tools).
This article has helped me tremendously, thank you.
One big black hole I have is how the Microsoft BI toolset handles security and restrictions… for example, we’ve set up cubes (and metadata – so when reporting off database tables) where users may only see a subset of data (like the typical cost centre scenario)…. is the Microsoft BI offering as flexible in restricting data (as well as functionality and objects)?
Our Cognos instance here goes off an LDAP server where we maintain cognos-specific security components….
The other thing I am interested in is your statement about SSRS not being as flexible as Report Studio – we have reports with fancy prompts (with one propagating to another), customised colours and logos, and finally prompts controlling whether to display tables in a report or not… are these the types of features I should be wary cannot be replicated to-date??
cheers!
No worries Wendy
To answer your question, SSAS cubes can be controlled at a very granular level using windows security. You can define Roles and restrict access to data based on those roles – e.g. they can only see certain levels of dimensions – for example French users only being able to see data from the France level of a geography dimension. You can restrict down to the cell level but that can carry performance considerations. I would say SSAS is far superior in terms of security and ease of administration. Start research from here: http://msdn.microsoft.com/en-us/library/ms175408.aspx
SSRS – all the features you talk about are present and capable. Report Studio can do a few things better, but not much, and ceratinly not enough to justify the additional cost. And the gap is constantly closing. Flat reports are old technology and the differentiators are small.
Good luck with the analysis, and if you need formal help please contact me.
Cheers, James
thanks heaps!
Definitely a one side argument. My synopsis would be totally different. Though i am predominantly a Cognos Consultant I have used and developed with the Microsoft suite. The quick and dirty is that Microsoft is cheap and limited in functionality. Cognos is a lot more expense but gives you a lot more in return.
Microsoft – SQL Server
Cognos – SQL Server, Oracle, DB2, Essbase, teradata, TM1, …..
Microsoft – Windows
Cognos – Windows, UNIX, Linux.
Microsoft – SSAS
Cognos – TM1 (OLAP DB like SSAS but runs in RAM. Desimates SSAS in performance.
Microsoft – Sharepoint
Cognos – Cognos connection, Integration with Sharepoint, Integration with Webspere, integration with Plumtree, integration with ….
Quick summary. If you are a complete Microsoft shop or have a very limited budget go Microsoft. If not go Cognos.
-Marc
Hi Marc, appreciate your comments, but on many counts you are wrong and spouting the sort of nonsense that comes from sales teams on other products’ sides, still trying to paint MS BI based on SQL2000… a product that is no longer even under support from Microsoft.
Microsoft BI solutions can connect to far more than just SQL Server – SSIS connects to just about anything, SSAS will feed off Oracle, Teradata and a heap of other systems, and likewise for SSRS.
TM1 is a product I respect, but SSAS is a market leader for a reason – it is very good, powerful, fast and easy to use. With the advent of Powerpivot as Microsoft’s in-memory engine (which is effectively SSAS under the hood) there is a new layer of competition in terms of performance, though SSAS is hardly struggling.
Cognos may live on non-windows environments, but Microsoft doesn’t particularly care for this niche market. They are interested in supporting their very widespread Windows Server environments. Similarly for Cognos’ integration with other non-SharePoint portals – given the ubiquity of SharePoint, Microsoft similarly doesn’t care for these niche markets.
The cost argument is frankly silly. Find me a customer without a limited budget and i’ll give them a call
Microsoft offers great value and a significant number of enterprises recognise that – which is why I get calls about migrating from Cognos to Microsoft, and not the other way round.
Whoa – Microsoft has nothing in their stack to address robust unified meta-data modeling (unless you are using SSAS – then forget the rest of this response, as SSAS is the crown jewel in the MS BI stack) . This is the power of Cognos Framework Manager. Comparing Microsoft’s Data Source View in BIDS to Cognos Framwork Manager is like comparing MS Access to Oracle 11g. Has anyone ever heard of “stitch queries”? You have if you work in the Business Intelligence world. They allow users to drill across multiple facts with the same dimensionality. Or model complex many to many relationships, in a logical layer? Only Cognos, BO, and OBIEE offer the ability to that (again, unless you are using SSAS). Drill across is crucial to analytics. This is all part of the sophistication built into Framework Manager meta-data modeler, and the other products I mentioned. Also, try building fully integrated dashboards in the Microsoft BI stack, where filters automatically replicate across components in the dashboard – cannot be done. That’s why 3rd part vendors like Tableau and QliqView are out there. I could go on and on Migrate from a Ferarri to a Chevy Cobalt? I don’t think so
DavidD – thanks – I acknowledge that the modeling is a weakness in the MS Stack that is only partially addressed by DSV’s / SSAS. However I will also point out that the level of functionality that Framework Manager provides is often not called for. I guess one pointer I would make about your comment though is if you have the MS stack, you have SSAS – it’s bundled, as opposed to Framework Manager being a separate purchase in Cognos.
Thanks for mentioning 3rd party vendors though – there’s a blog post out there I need to make about this – you perceive it as a weakness – i.e. that the vendor doesn’t meet all business requirements. It can easily be viewed as a strength – i.e. that it is possible to extend the capabilities of the stack.
Your product / car analogy is a bit weak though (except on pricing, perhaps…) – realistically BI products are commodities now and the Cognos / MS differences aren’t that big when looked at as a whole (If you tried to sell me Data Manager as a Ferrari grade product i’d laugh!). Any business who thinks they need spend Ferrari money to do Ford work isn’t a smart one. Ultimately, TCO is where Microsoft has its killer argument.