Migrating from Cognos to Microsoft BI – revisited

My original post on migrating from Cognos to Microsoft BI has consistently been the most popular on this blog pretty much since I wrote it, so I’ve decided to come back to the topic now that 2008R2 is out in the field and 2011 (Denali) is on the horizon. I also have some more detail and insights, especially thanks to the many comments made on the original post.

One thing to take away is that both stacks have their passionate supporters and detractors, but on high level functionality there really isn’t a coherent technical argument for either product. It still remains a case of what is appropriate for your business requirements. Both product sets meet most requirements. From the comments on my original post, it’s clear that some people’s understanding of the MS BI stack is not always great, and often still seems rooted in what was offered in SQL2000 (a decade ago!).  The biggest gripe from the Cognos away team was the lack of a Framework Manager equivalent in the SQL Server stack. I think this is fair comment – however BISM in Denali I think is going to bop that one on the head pretty swiftly.

So if there’s no real technological lever to drive you one way or the other, what factors should be considered when choosing between the two stacks?

Cost

There’s no escaping the fact that IBM/Cognos licencing costs are significantly higher than Microsoft’s, and their licencing structures are more complex. When people talk to me about migration, cost is always the #1 factor. I recently was talking to a BI Architect who had just joined a major company who cited a licence cost for their Cognos Reporting Server which would have funded a SharePoint and SSRS installation and a significant number of report builds – all with no loss in functionality. I’m not sure he was best pleased with the use of that investment.

The reality is most Enterprises and SME’s have licencing agreements that cover all MS BI tools to some extent anyway – SQL Server, SharePoint and Office all usually have a presence somewhere and IT can squeeze more value out of these existing licences. Remember that every dollar spent on licencing is a dollar less available for actually delivering BI.

Implementation costs tend to be cheaper too – Microsoft skilled staff are cheaper and more numerous, so you are less likely to be stuck trying to find a capable resource and they will cost you less when you do.

End User Adoption

In the coming world of Self Service BI, the end user is king. More often than not, Excel is the throne upon which they sit. I’m sure I don’t need to point out which vendor offers the tightest Excel integration. With SharePoint 2010, Excel and its new friend PowerPivot become a powerful offering (see my earlier post on PowerPivot) that genuinely breaks the ties between IT and the business. People love to work in Excel and Microsoft are always going to have this advantage when it comes to selling the solution to consumers. SharePoint becomes the icing on the cake, with rich collaboration features making BI much more interactive between users. 

The IBM/Cognos suite still seems to be driven by a IT delivered philosophy – and the unfamiliar tools and interfaces make it difficult for the business users and Cognos to make friends. To be fair, when they do become friends it all works out rather well, but that initial hurdle still has to be overcome.

Innovation Shortfall

This is probably the most contentious argument, seeing as Microsoft have a long history of “buying” innovation into their BI stack, but the reality is that IBM/Cognos has stagnated and is in a position where it will have to play some significant catch-up. Hindered by a product line with a lot of overlap, sooner or later IBM/Cognos are going to have to choose to innovate and advance some products and can others – which will cost goodwill in some customers. But until they do, they are going to lag further and further behind.

Final thoughts

Since my original post, my position hasn’t moved a great deal. The Microsoft suite simply offers better value and lower long term TCO. With the ever increasing SharePoint and Excel integration end users are going to favour Microsoft’s offerings. Finally, unless Cognos really raise their game soon, the technical argument which is currently evenly balanced will soon tilt in Microsoft’s favour – then the decision becomes too easy.

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:

  1. 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.
  2. Existing SQL Assets – Many times companies have SQL Server licences for their databases and want to extract more value from those licences
  3. 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.
  4. Performance- many Cognos users report unsatisfactory performance from deployments
  5. 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
  6. Skills availablity – there are simply more Microsoft BI developers out there, and generally they cost less to employ and are cheaper to train.
  7. 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 8) – 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 (Update 04-feb-11: SSNS is effectively dropped from 2008 onwards so there is no out of the box equivalent). 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!

Update 22/11/2010: I’ve added a followup post here which focuses more on the business reasons for migrating / choosing