Cognos Framework Manager is a powerful data modelling tool, but like most powerful tools it can be used correctly and incorrectly. Many users love to see the SQL interface and start coding in SQL the first chance they get. This is usually a mistake. Hard-coding SQL in Cognos Framework Manager can cause unnecessary metadata callbacks which can seriously degrade a data model’s performance, and it can also make a data model more complex to modify, maintain and understand.

Now to the question of a union operator. The first and obvious way to do this is to simply put a union directly in your SQL query. This will give you the desired result, but it is not a best practice.  For the sake of simplicity, let’s call this the wrong way.

A better way to do this, though less obvious to novice users, is to create a Query Set. Simply select the two tables you wish to union, then click on Actions and Define Query Set. You can do Union, Intersect and Except operations in a Query Set. You are limited to two tables at a time, but you will end up with a cleaner model if you take this approach. If you need to join multiple tables, you may be better off taking your desired operation back to the database in a database view or a merged table with ETL.  Any time you find yourself coding heavily complex SQL code in Cognos Framework Manager, ask yourself if an ETL solution could serve your FM data model better.

So, as usual, there is more than one way to complete a particular task in Cognos. It’s just some ways are better than others.