• Skip to main content

IBM Blueview

Cognos Analytics and all things IBM

  • The Blog
  • Cognos Glossary
  • Cognos Resources
  • About Me
  • Categories
    • Cognos
      • Data Modules
      • Administration
      • Framework Manager
      • Dashboards
    • Opinion
    • Community Spotlight
  • PMsquare
  • Subscribe

Cognos Union Queries in Reports

July 15, 2021 by Ryan Dolley 2 Comments

Union queries are the most common design pattern I see in Cognos Analytics reports. A fledgling report author will encounter a challenging problem and, one way or another, find a way to solve it with a union query. Brimming with justified confidence, they will apply this technique to increasingly complex data riddles with some success. Unfortunately as the number of Cognos union queries goes up, the performance and supportability of the report goes down fast. Years of consulting have taught me that excessive unions are a surefire sign of a developer in need of some coaching. Well Coach Ryan is here to iron things out. So let’s take a look at how you can replace all those union queries to build blazing fast, easy to understand Cognos reports.

This video walkthrough will teach you how to ditch union queries once and for all

What is a Cognos union query

For our purposes a ‘union query’ will refer exclusively to unions done in Cognos report authoring. A union is built in the query editing screen and it combines two identically structured queries into one. What I mean by this is that the two queries have the same column names and data types in the same order. You’re basically stacking the results on top of one another in a data sandwich. You can then union two unions together. And then union two unioned unions. It gets complicated fast.

How union queries go wrong

Union queries in and of themselves are not necessarily a problem. The issue arises when you use union queries to solve data challenges that are much better solved with calculations. The number of union queries in a single report quickly spirals out of control and performance suffers. I see this all the time at clients. It appears to be the industry standard at this point, and my goal here is to change that

Example of a bad Cognos union query

The following example is an instructive one that I explore in more detail in the video above. We want to build a report that allows us to compare current ‘Retailer Type’, ‘Month Revenue’ and ‘Year Revenue’ in the same line of a report to get something that looks like this:

An example of a table commonly built with a Cognos union query. Contains Retailer Type, Month Revenue and Year Revenue.
Many ways to build this simple table, and some are better than others

However, our underlying data does not contain the relative measures ‘Month Revenue’ and ‘Year Revenue’. All we have is a single ‘Revenue’ column. We need a way to manufacture these columns in Cognos. This is the most common scenario in which unions are used poorly. Many report developers will build something like this:

An example of a Cognos union query combining YearQuery, MonthQuery and YearMonth.
The union query pictured here is often the first solution for developers. There are better options!

In this example, we have created two queries: YearQuery contains the data necessary to produce the ‘Year Revenue’ column, while MonthQuery produces the ‘Month Revenue’ column. The queries have the following characteristics:

  1. Both queries have the exact same columns
    1. Retailer Type
    2. Month Revenue
    3. Year Revenue
  2. The detail filters are customized per query to filter revenue down to just the needed values.
    1. For the YearQuery, year = ‘2011’
    2. For the MonthQuery, year=’2011′ and month = ‘October’
  3. The value of the two Revenue fields are as follows
    1. For YearQuery
      1. Year Revenue = Revenue
      2. Month Revenue = 0
    2. For MonthQuery
      1. Year Revenue = 0
      2. Month Revenue = 0

Each query now returns a result set that consists of the ‘Retailer Type’, a manufactured column for the relative revenue number (year or month revenue) and a dummy column that holds the value 0. At this point we construct the union and build the ‘data sandwich’ which stacks the results of YearQuery and MonthQuery on top of one another. Cognos is then able to auto-group them together based on ‘Retailer Type’ to produce the chart you see above.

Calculations: A better way to solve the Cognos union problem

The above technique works, and in my simple example it’s fairly harmless. However often develops into a crutch for developers who over apply it to all scenarios. The number of union queries rapidly proliferate as report performance suffers. And more importantly, developers who over rely on this technique are losing an opportunity to grow. So how do we solve this problem? Like this:

An example of replacing a Cognos union query with a calculation.
The calculation above accomplishes the same thing as the union queries, but does it better.

Whenever you are faced with the need to construct a column in Cognos that doesn’t exist in the underlying data, build a calculation! In our example, the entire union query methodology can be eliminated with two simple calculations that I will share with you now:

Month Revenue Calculation

CASE WHEN [C].[C_Great_outdoors_data_module_Relative_Time].[page_7].[Year_] = 2011
AND [C].[C_Great_outdoors_data_module_Relative_Time].[page_7].[Month_] = ‘October’
THEN [page_6].[Revenue]
ELSE 0
END

Year Revenue Calculation

CASE WHEN [C].[C_Great_outdoors_data_module_Relative_Time].[page_7].[Year_] = 2011
THEN [page_6].[Revenue]
ELSE 0
END

These are done in a single query. Performance is improved, comprehensibility is greatly enhanced. This technique expands far beyond relative time comparisons. I often see the union technique uses to accomplish all sorts of aggregations within Cognos. These can almost always be replaced with CASE and FOR statements as well as aggregate functions. Cognos has all these tools in the toolbox – use them!

Relative date and lookup reference

In Cognos Analytics 11.1 and up, there’s another sneaky way to solve this issue, and that’s using data module relative dates and the lookup reference functionality. I cover this in greater detail here so I won’t dive too deep, but you can super easily produce the report above without any queries or calculations. Look at the out of the box relative measures that are drag-and-droppable into your reports or dashboards:

A list of relative time measures generated in Cognos data modules.
All of these can be yours at the click of a button.

So when should you use union queries

There are always edge cases where you should ignore this advice and union away – Cognos Paul and I were discussing one earlier today. But in general, here is the scenario that calls for a union. You have two sources with identical structure and need to combine them. Oftentimes these are tables or excel sheets differentiated by time – i.e., one table contains all 2020 transactions, one contains all 20201 transactions. In this case, you need to make the ‘data sandwich’ to get them into a single table structure for analysis. A union is your best bet. Otherwise, I almost always avoid a Cognos union query in favor of the techniques listed above.

IBM documentation references

Create a union query

  • Cognos Union Queries in Reports
  • Cognos Relative Dates in 11.2
  • The 2021 Gartner BI Magic Quadrant is Broken for Cognos Analytics
  • Data Modeling for Success: BACon 2020
  • Cognos Analytics 11.1.6 What’s New

Filed Under: Cognos, Reporting Tagged With: Cognos, Cognos 11.2, Cognos Analytics, Reporting, Union query

Reader Interactions

Comments

  1. Greg Curtis says

    July 22, 2021 at 9:51 am

    hi Ryan
    Thankyou for a very useful and interesting article – it resonates with because I am using a Union at the moment to solve a business issue – However it just won’t work. Basically its one measure from one datamart (Q1) and one measure from another datamart (Q1), The union query presents the measure from the first Query correctly in a column but the second measure (from Q2) appears as members spread across hundreds of columns – very strange! That said we are still using CA11.0.13. I am frantically googling the topic but do you know of any known bugs? (p.s. The two queries come from 2 relational sources but the output is onto a crosstab!)

    Reply
    • Angela says

      June 15, 2022 at 4:42 pm

      Hi Ryan,

      From my current understanding, if you’re trying to make any kind of aggregate/summary report using crosstabs, you are likely to experience a lot of bugs and issues. It’s supposed to be Cognos’s automatic summary tool, but due to detail/summary aggregation issues (especially if you have multiple queries). You’re much better off making a list and using query calculations for every column to generate an aggregate/summary report using (count/total function and nesting “case when” for conditionals when necessary) I hope that makes sense.

      Cheers

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Copyright © 2023 · Atmosphere Pro on Genesis Framework · WordPress · Log in