IBM Blueview

Cognos Analytics and all things IBM

Cognos

Cognos Union Queries in Reports

by Leave a 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 Leave a Comment

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!

What are Cognos Data Modules?

by 20 Comments

Cognos Data Modules are a web-based data acquisition, blending and modeling feature available in Cognos Analytics. They first hit the scene as part of Cognos 11 and are meant to supplement and eventually replace Framework Manager for both self-service and IT data modeling needs. I’ll pause for a second to let you long-time Cognoids hyperventilate a little… is everyone back? Good. Through this and subsequent posts I’ll try to dispel misconceptions about this awesome feature of Cognos while making you comfortable and – dare I say – excited to use them.

Cognos data module in action
Data modules – the wave of the future

Data Module Features

Imagine a data modeling solution that has the following features:

  • Easy to install and manage
  • Join dozens or hundreds of tables across multiple databases
  • Execute cross-grain fact queries
  • Build simple or complex calculations and filters
  • Build alias, view, union and join virtual tables
  • Secure tables by groups, roles and data elements
  • Create OLAP-like dimensional hierarchies
  • Enterprise governance, auditability and security

 ‘Okay easy, I’m imagining Framework Manager’ you’re thinking right now. Yes! But, add in:

  • Natural-language and AI powered auto-modeling
  • Automatic join detection
  • Easy integration of excel data
  • Automatic extraction of year, month, day from date data types
  • Automatic creation of relative time filters (YTD, MTD, etc..) and measures (YTD Actuals, MTC Actuals, etc…)
  • In-memory materialized views (data sets)
  • In-memory query cache
  • Direct access to members for relational sources!

‘Well that’s not Framework Manager… it must be Tableau, right!?’ No, in fact Tableau doesn’t offer even half of these capabilities. This is what every Cognos Analytics customer gets out-of-the-box in data modules today, with more features being added all the time.

Who are Data Modules for?

Many of my longtime customers have the misconception that data modules are for ‘end users’ only and that real data modeling can only be accomplished in Framework Manager. Conversely my new customers have built entire BI practices while having no idea what Framework Manager is. Clearly something is out of sync here, so let me make it very clear: Who are data modules for? If you’re reading this, the answer is you.

The Business User

The line between ‘end users’ and the BI team has gotten fuzzy in the last few years as increasingly complex models are built by people outside the IT department. Data modules are ideal for someone who wants to quickly and easily combine enterprise data with departmental data or excel spreadsheets and cannot wait for IT to build an FM package or SSAS cube. The interface is clean and easy to use and the ease of creating custom groups and building relative time calcs makes data modules an ideal place to combine data – even easier than Excel in many cases. As an added bonus, it’s very simple for the IT team to take a ‘self-service’ data module and incorporate into enterprise reporting without significant development work.

The Cognos Pro

Many Cognos pros kicked the tires in 2016 and could only see the yawning chasm of functionality that separated data modules from Framework Manager, myself included – for years I encouraged my clients to consider them for niche applications but to rely on FM for anything important or difficult. No longer! As of the 11.1 release, data modules have reached feature parity with Framework Manager is almost all respects and even surpassed FM in important modeling automation tasks like relative time automation. It is no longer the obvious choice to default to Framework Manager for new Cognos development.

Data Modules vs Framework Manager

Given the enhancements to data modules, which should you choose? As of the 11.1 release my recommendation is to do all new development in data modules for the following reasons:

  • Significantly easier and faster to create
  • Great features like relative time, date column splitting, grouping
  • Target of all future development
  • Unlock modern BI workflow

These points are explored in detail here – for now I’ll leave you with a final thought. My new clients use the same ol’ Cognos to deliver with the speed and scale you’d expect from Tableau or Power BI – my friend Vijay can tell you all about it. The key differentiation between them and legacy Cognos installations with orders of magnitude more resources is the embrace of data modules and the iterative, build-it-in-prod approach to BI delivery that data modules enable.

Alias Shortcuts in Cognos Data Module

by 4 Comments

My recent What are Cognos Data Modules post generated some interesting discussion around the alias shortcut functionality of Framework Manager and whether or not it is available in data modules. I initially responded by saying you could make data module alias tables, however SirM challenged me that it isn’t the same at all.

SirM is right.

What is an Alias in Cognos

In my experience the primary reason to build an alias is to tightly control how Cognos executes joins. Imagine you have two tables, Sales_Fact and Time_Dim. Sales_Fact contains the fields order_date and shipped_date, both of which you wish to join to Time_Dim. You could join the tables twice and be done with it, but then you are at the mercy of Cognos as to which join it will include when generating SQL. This will introduce unintended or completely incorrect results when the ‘wrong’ join is used.

Enter Framework Manager alias shortcuts. An alias shortcut is essentially a pointer to another table that has the following properties:

  • Inherits all fields and all changes from the target table automatically
  • Ignores all relationships from the target table
  • Can be repeated as often as necessary

In our example above, we make an alias shortcut of Time_Dim called ‘Shipped_Date’ and join the alias to Sales_Fact. Shipped_Date will inherit all fields from Time_Dim but will have an independent relationship to Sales_Fact.

Alias in Data Modules

Can we do this in data modules? In Cognos 11.0.x the answer was basically ‘No way!’ However recent releases have gone a long way to close this gap, especially once the custom table functionality was added in the 11.1.x release stream. Let’s explore the three options I recommend for building alias shortcuts in Cognos Analytics data modules and see which one fits best.

Copy Tables

Copying a table does exactly what you’d expect – it makes a copy of the table in question. You can create as many copies of a tables as you want, however changes you make to the target table do not flow through to the copied table in any capacity – whether you add or remove fields, rename fields or change calculation logic.

  • Inherits all fields and all changes from target table automatically: NO
  • Ignores all relationships from the target table: YES
  • Can be repeated as often as necessary: YES

View Tables

View tables are like copy tables with a couple very important differences; views can be composites of many underlying tables and they do inherit some changes from the target automatically. Specifically, they will inherit calculation changes made to the fields in the target table but will not inherit name changes or added/removed fields without manually editing the view definition. I will do a deep dive on their usage in a future post.

  • Inherits all fields and all changes from target table automatically: SORTA?
  • Ignores all relationships from the target table: YES
  • Can be repeated as often as necessary: YES

Linked Tables

Linked tables are essentially pointers to tables built and maintained in other data modules. They are extremely useful for BI teams concerned about data quality in Cognos Analytics, as you will see below.

Linked tables inherit all properties from the target table in the model in which they were built. This means that any changes I make in the target module, whether I add or remove fields, create new calculations or edit existing logic will automatically flow through to all linked tables that reference it. This would appear to solve our alias shortcut problem, so what’s the catch?

You can only import a linked table into a data module once. It cannot be copied and any views you build on it have the view limitations outlined above.

  • Inherits all fields and all changes from target table automatically: YES
  • Ignores all relationships from the target table: YES
  • Can be repeated as often as necessary: NO

So What’s the Solution?

Whenever you find yourself reaching for the alias shortcut button in data modules, ask yourself which alias table feature is most important for the task at hand.

  • If the most important thing is automated inheritance of all future changes, build a link table
  • If the most important thing is re-using the same table over and over, build a view table
  • In most instances, do not build a copy table

In reality, a view table should fit your needs in most circumstances. Yes, you will need to manually intervene to inherit certain changes listed above, however this process takes about 30 seconds per table. Not ideal but something most of us can live with.

How to Proceed

Alias tables have historically filled a very important role in building large scale Cognos models in Framework Manager, and their absence in data modules creates challenges to modeling the way we have for over a decade. Certainly many of my most skilled customers feel that without this functionality data modules don’t have much use.

It’s worth considering then how it is that Tableau and Power BI managed to dominate the mid-late 2010s BI market with a total absence of an equivalent to alias shortcuts or many other ‘enterprise’ BI modeling features? The answer lies in the culture and practice that these solutions enabled which delivered faster results for business users. How to apply these practices to Cognos using data modules will be a major theme of this blog going forward.

Level up your Cognos skills with these helpful articles!