Migrating from Cognos to Microsoft BI

[James Beresford]

If you are considering moving from Cognos BI to Microsoft BI (and as a Microsoft BI guy, i’ll usually reccomend 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 – 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 superceded 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 alot 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 probbaly 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 its 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!



Get More from the original blog...