Applied dimensionality

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:

1) Create a 3d application, called "P&L" and add administration or system links that will populate it. And if  P&L for all company cannot fit into a single elist, you then publish this application and create reports on it. But there's a big chance that you'll need detailed sales report as well. So you'll publish sales planning application as well. And the same stands  for Operating Expenses. So, as the result, to create reports you'll have to run admin links populating P&L application and publish 3 applications. Ain't that fast, in my opinion.

2) Or you can publish Sales and OpEx application and create a Framework Manager query subject, which unions sales budget (throwing out products and other non-P&L dimensions). Then you can create a DMR, using this "union" as fact table. So you'll get a P&L that is updated dynamically when you publish data from application. One application and publish thrown out -- speeed rises. There are performance problems, but they are usually solvable (think 'materialized views' in Oracle). And you get drill-through reports from P&L into Sales and OpEx as a free gift, which is really usefull. 

 
I am a big fan of 2) 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.