IBM Blueview

Cognos Analytics and all things IBM

Cognos Analytics

Cognos Union Queries in Reports

by 1 Comment

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 Relative Dates in 11.2

by 4 Comments

Relative Dates are a powerful feature of Cognos Analytics data modules that enable quick and flexible comparisons between time periods. For example, comparing revenue for the current year version the previous year, or the current week of the month vs the same week last month. These comparisons are popular across all lines of business but especially in the office of finance, which relies on period over period analytics as a bedrock of analysis. The video below covers how to set up and use relative dates in Cognos Analytics.

Live demonstration of relative date functionality in Cognos Analytics.

Relative dates are a feature exclusive to data modules. If you want to use relative dates in Framework Manager, your best bet is to extract the necessary fields into a data set. You can then import the data set into a data module and set up relative dates there. In all likelihood you’ll also get better query performance because data sets are great!

Setting up relative dates

There are two components to the relative date functionality in Cognos Analytics, Relative date filters and relative date measures. They are both easy to configure and use. To set them up you need the following:

  • The ability to use data modules
  • Access to the ‘Calendars’ folder that ships with Cognos
  • Data that includes a ‘date’ or ‘date/time’ data type column
  • The ability to use the linked module feature of Cognos

Your administrator can turn on the relevant permissions. Once that’s done, it’s simply a matter of following the steps in the video above, but I’ll summarize them here:

  1. Open the data module that requires relative dates
  2. Slick the ‘add source’ button
  3. Navigate to and select the calendar you want. Cognos ships with three by default, Fiscal, Gregorian and 5-4-5. You can create custom calendars as well.
  4. Your data module will now contain a new linked calendar table. This table is easy to recognize as it will be slightly transparent and turquoise.
  5. Now select any date or date/time column in your data and open the properties. Select your calendar table under the ‘lookup reference’ property.
  6. Congratulations, you have configured your relative date filters!
  7. Now select any measure column in your data and open the properties. Select the date you just configured in step 5 under the ‘lookup reference’ property.
  8. Congratulations again, you have configured your relative date measures!
  9. Click on the arrow next to either column to see the filters or measures you created.

Relative date filters

Relative date filters provide drag and drop capability to instantly filter any visualization, table or crosstab to the selected period. By default there are 23 relative date filters, but you can create more by editing the calendar files that ship with Cognos.

A list of all relative date filters in Cognos Analytics
Create all these relative date filters with just a few clicks. Amazing!

Relative date measures

Relative date measures are similar to relative date filters but represent the measure value on a visualization rather than a filter. Think of it as the line on a line chart. There are 23 relative date measures by default but by all means, customize.

A list of all relative date measures in Cognos Analytics
Relative date measures galore!

Relative dates in action

If you’re having a difficult time picturing the final product of relative dates, here’s a simple chart to make it easy to understand how useful they are:

Current year vs previous year is a common relative date comparison that you'll build using the Cognos relative date function
Relative dates in action on a line chart

Above you can see a common but powerful analysis. On the X axis we have the months of the year, and on the Y revenue totals. Using relative date measures I can easily plot two lines, one for current year and one for previous year for comparison. I am able to do this with zero coding or SQL. As a bonus, these relative periods will always be up to date – when the year rolls over into 2022, the value of Prior Year automatically updates to 2021.

As-of-date analysis

By default Cognos Analytics assumes the current date as the point of reference for relative date analysis. Therefore, the final piece of the relative date puzzle is enabling as-of-date analysis. This global parameter allows users to select the ‘as of date’ for the relative time feature. It’s easiest to think of this by asking yourself, relative to what date am I counting ‘year to date’ or ‘month to date’. Is it today? Last Friday? December 19th, 1982?

The as-of-date global parameter allows users to easily select the target date for relative date analysis
The as-of-date selector in Cognos Analytics 11.2

Configuring the as-of-date global parameter is a task for administrators and is very easy:

  1. Open the manage menu and select ‘Customization’
  2. Select the ‘Parameters’ tab
  3. Click ‘New’
  4. Type ‘_as_of_date’
  5. Select the ‘More’ menu (the ellipsis that appear when you hover over the parameter) and click ‘Properties’
  6. Click ‘Applied to all roles’ if you want everyone to have this capability
  7. Log out of Cognos
  8. Log back in to see the global parameters button appear in the upper-right corner of the UI

Cognos relative dates in summary

There you have it. Reading back over this blog post there are a lot of steps included, but don’t get intimidated. This is remarkably easy compared to writing relative date calculations or using SQL to do it. The video above walks through every single step included in this blog post so watch and follow along. And don’t forget to like and subscribe to the Super Data Brothers channel!

IBM documentation references

Relative date documentation home
Sample calendars
Creating a data module for relative date analysis
Creating relative date filters
Customizing the reference date

Keep reading for more awesome Cognos content!