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!
Agile BI, or why Big Bang warehouses should (sometimes) be shot
Recently I wrapped up on a project that could have been a money pit with uncertain results, but managed to be steered into something that delivered results quickly and made the business very happy. To give a real high level overview, the client in question had a collection of mainframe delivered text files which fed Excel reports via Access via SQL2000. The project was being pushed by IT (usually a Bad Thing) primarily to solve a business problem (which meant it turned back into a Good Thing). The root business problem was mostly being the Access databases kept falling over for various reasons so the analysts couldn’t do their work.
As the requirements were scoped out, it became apparent that the resultant Data Warehouse was going to be a very large and complex thing that would take a long time to develop. We did a quick pilot to demonstrate that the ETL could deliver results in a structured and controlled manner into the Warehouse, which delivered about 5% of the files through from end to end. IT were pleased as it all went well. Their first thought was then to deliver the remaining 95% of data.
Do you need to put all the Data in the Warehouse?
I asked the client to pause for thought at this point. In delivering the 5% of data we had proved that the DW process was feasible to IT – but had given nothing to the business. We had solved an IT problem, not a business one. In order to get the business excited we needed to deliver someting to them. The IT side of the client saw the wisdom of this – if the business had something tangible and helpful it helped sell the project. Also, if they hated it, we could find out we were on the wrong track in a far cheaper manner than after delivering a monster DW that nobody wanted!
So what did we do next? We focused on satisfying just one business problem – in this case the data needs of a single department, and set about building the parts of the DW required to meet those needs. At the end of this we could throw them a shiny new cube and see if it stuck. This way we would:
- Deliver initial results at minimum cost
- Establish if we were on the right track from an early stage
- Get business visibility of the DW faster
If all went well, we could focus on delivering the next 5%, then the next… and probably find that there was probably a big chunk that wasn’t used anyway, thus giving more savings – or identifying areas for new insight. Much better that delivering the big bang DW that may actually be a damp squib!
So was that Agile BI then?
It was a form of it, yes! The idea of Agile Development is to deliver results quickly, allowing the business to see what they are getting sooner and tune the results. Now, this isn’t all good – it can lead to massive scope creep, drift of purpose and even failure to deliver anything at all. So there is a need to be very cautious and apply this methodology only where appropriate. Cases such as Cubes for analysis and Reports are things that can benefit from a rapid development cycle carried out in close conjunction with the business. A good example of where not to apply it is the ETL – it’s something that needs rigour and structure to be delivered effectively, and in practice is something business users neither understand or care about.
Sometimes with BI it is the right approach in greenfield sites because of one significant issue - clients can have a poor grasp on the capability of BI solutions and need leading into the wonderful world of BI. Often they can be skeptical about the promises made by consultants and by using an Agile approach on the first build you can get them into the BI mode of thinking rapidly. The client sees what they are getting and can get excited – whereas with standard waterfall delivery they can wait months to see results after an long analysis / design phasse, and if the consultants have misjudged the business needs – kill the DW in its infancy.
But how can we deliver BI without the Warehouse?
I’m now on another project which has become lost in the design phase and the client is wondering what is going to come out of it all. We shifted approach to an Agile method where instead of focusing on getting the solution letter perfect in Design (Waterfall style) we are going to deliver a functioning SSAS Cube based on a sample of real data and build the design off of that. This way the client gets to see the light at the end of the tunnel and we know we are building the right solution even before the design is finalised.
The key in this case is to establish what the end deliverable is as quickly as possible and demonstrate that we, as consultants, have identified the right target in order to give the business confidence. The DW can follow to support that deliverable.
So when should a project go agile?
There’s no hard and fast rule, but I would suggest the following indicators may guide you towards an initial Agile build:
- Requirements are sparse / undefined – and the goalposts can move
- It is the first BI project and BI capabilities are poorly understood
- Business users are highly available
- You need to give confidence you can deliver the right thing
In case you want to know more, here are a few additional articles on Agile BI that I have found:
- Agile Best Practices for Data Warehousing (DW)/Business Intelligence (BI) Projects from Scott W. Ambler @ agiledata.org
- Agile Development for BI from BI Guru
- Architecting an Agile Data Warehouse from Lee Arnett @ information-management.com
- Agile Data Warehousing: Delivering World-Class Business Intelligence Systems by Ralph Hughes – a comprehensive book on the subject
The Dangers of the Dashboard
On your shiny dashboard, you have some great traffic lights – green is good, amber is ok and red is bad. If it’s green you can be happy you are performing well, and let that part of that business alone because there’s some red warning lights and they need your attention now – before the boss comes down on you like a ton of bricks – or you miss your bonus. Or maybe with a little effort you can nudge an amber to green and things improve for you.
Can a Green KPI be a bad thing?
A green KPI can easily be a missed opportunity. Maybe sales are up in a sector, and there’s an opportunity for growth brewing – but no action is taken, because the KPI is green and there’s distracting red traffic lights urging action in somewhere else. The beauty of traffic light KPI’s is that they show stats in a simple, easy to grasp format – but the strength of their simplicity is also a weakness.
The traffic light KPI exploits an aspect of human nature – that we react to warning signals – and so the red light is there to urge people to act. The problem is because red pushes us to act, and green tells us all is well, we don’t think to act on a green KPI. This leads to a situation where if we don’t understand our KPI’s and what drives them we can make poor decisions. What is the point of acting on a red KPI because sales are down $20k in one sector if not acting on the sector with the green KPI means we miss out on a $100k market?
Can a Red KPI be a good thing?
The short answer to this is – of course – no. It means you are missing your targets and something needs to be done. However what is the value of a KPI that is always red? To give a personal example, many moons ago I was a call centre monkey (yup, still a simian). We had a call waiting board that beeped loudly and flashed red every time a call had been on the line over 5 minutes. Due to chronic understaffing, the only time that board didn’t beep was when it was switched off at the end of the day. So we had a red KPI all the time – it served no purpose at all.
The lesson to be learned from that example is that unless your KPI is defined with reasonable parameters – not an arbitrary value – it won’t tell you anything useful. If our board had beeped when the waiting time went over 15 minutes, maybe it would have spurred us on as a reminder we were getting behind. But a constant warning is no warning at all.
So should I just set all my KPI’s to Amber?
The implication of the above could imply that the KPI is useless – but like any tool, it has to be fit for purpose, and used well. There’s two points I hope you can take away from this:
- See Green KPI’s make as a chance to do better and just as worthy of review as the red lights
- Make sure your KPI’s are reviewed so they really are providing useful assessments of performance upon which you can act
Excellent video on the History of BI
Click here to access a funny and accurate 10 minute video on The History of BI from Nic Smith of Microsoft. It’s not a Microsoft puff piece (not until the last 30 seconds, anyway, which is forgivable) – which again drives home the message that seems to slowly being worked out by BI professionals that BI is a people driven business process, not an IT driven system. (This topic seems to be getting thrashed about on some of the LinkedIn groups I frequent, as BI Vendors using it for marketing are getting a deserved kick in the pants for being too tech focused)
Anyway, watch, enjoy. (via: Oz Analytics, but for some reason that particular page kept killing my browser, so I went to source)