Applied Dimensionality

Cognos BI and MS SQL — not meant for each other?

Posted at — Sep 27, 2008
Cognos BI and MS SQL — not meant for each other?

We’ve encountered an annoying Cognos 8 bug recently. 

Usual scenario if you’re creating reports over Enterprise Planning data includes “unioning” data from several applications. 

For example, you have an Operating Expenditures planning application and a Sales Planning application. To make a simple P&L, you just have to show up data from both these applications. So you can either:


I am a big fan of the second approach, of course. And it works nicely with Oracle, but not with Ms SQL, as it turned out.   

Turns out that unioning query subjects from different databases requires local processing from Cognos viewpoint (checked in 8.2,8.3). But if you create a native query subject with required union sql inside — it’ll push nicely to database and work fast. I’ve depicted situation in details at communities. It generally turns out that cognos sql optimization for microsoft sql is weaker (it triggers local processing more often) than for oracle, so all those database agnostic marketing claims are just a bit untrue.


comments powered by Disqus