• 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

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

Cognos Relative Dates in 11.2

July 15, 2021 by Ryan Dolley 7 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!

  • 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

Cognos Analytics 11.1.6 What’s New

April 27, 2020 by Ryan Dolley 7 Comments

Cognos Analytics 11.1.6 is live! This release features a ton of great quality of life enhancements. Chief among them is a great dashboard UI refresh, a new interactive data table in report authoring and some very welcome changes to data modules. There are also some shifts in IBMs design language with refreshed icons, and a great new ‘help’ section that pulls support directly into the Cognos UI. Overall I feel this is a strong release. So let’s take a look at what’s new in Cognos Analytics 11.1.6.

Cognos Analytics 11.1.6 UI and usability updates

Cognos Analytics 11.1.6 brings two changes to the UX that you will notice across all features and help keep Cognos looking fresh while enabling end users with much improved support features.

UI and design tweaks

Cognos Analytics 11.1.6 brings updates to icons, fonts and other items
Cognos Analytics 11.1.6 features more elements from the Carbon Design System

IBM continues to apply the Carbon Design System to Cognos Analytics. For those of you who are unfamiliar, this open source system for products and experiences drives the UX of many IBM products including Cognos, Planning Analytics, Watson Studio and Cloud Pak. For Cognos Analytics 11.1.6 this means first and foremost the adjustment of icons and fonts in the UI as well as more subtle tweaks located throughout. I personally think the new icons look fresh and appreciate the hard work of the IBM design teams in Toronto and Ottawa.

New ‘learn’ pane

The 'Learn' pane adds lots of end users support to the Cognos Analytics 11.1.6 UI
The new ‘Learn’ pane brings guides and videos directly into Cognos

The ‘learn’ pane replaces the ‘help’ section and basically does a 360 degree dunk in its face. The improvement here is dramatic. Accessed via the new learn icon in the upper right corner, the learn pane is a context dependent help section that surfaces guides, documents and even videos directly in the Cognos UI. It understands what feature you are currently using and suggests related content – in the example above it suggests data modules assistance when accessed from the data modules UI.

Cognos Analytics 11.1.6 Dashboard Changes

New dashboard UI

Dashboards receives a most welcome UI refresh. It is much easier to understand visualization composition and formatting options thanks to changes to context menus and a new ‘fields’ section. Subtle tweaks also bring the tool closer to the carbon design standard with new icons.

Cognos 11.1.6 dashboards received a great makeover
Dashboards receive a subtle yet significant makeover
  1. New icons in both the side menu and in the data tree. Cognos looks more and more modern with each release.
  2. The visualization properties are now pinned to the top of the screen like in reporting. This makes interacting with visualization so much easier!
    1. Left clicking on a visualization will highlight the visualization. The pinned visualization properties at the top of the screen will apply to the select visualization
    2. Right clicking on a visualization element like a bar, line, point or label will bring up the interactive options for just that element
  3. Access to options like linking visualizations, filters and properties has been grouped and simplified. There is also a new ‘Fields’ button
  4. The ‘Fields’ button brings up a new fields view, which shows the fields of the selected visualization. If you select a new visualization this section will change to reflect your new selection

This impactful redesign makes navigating dashboards so much easier compared to previous releases because you can always tell what visualization is selected and easily and quickly access all visualization elements. You no longer have to rely on hidden menus and focus mode, which has received a redesign.

Revamped focus mode

Focus mode is now for focusing on a visualization rather than making changes to it
Focus mode now functions as ‘full screen’ for individual visualizations

Focus mode has been repurposed and now functions basically as a ‘full screen’ button for an individual visualization. As a result of the general UI changes all the old focus mode features reside in the new ‘Fields’ view. Focus mode is now also available to dashboard viewers, not just in edit mode.

Expand/collapse in crosstabs

Expand/collapse in Cognos Analytics 11.1.6 dashboards brings back some old Power Play favorites.
Expand/collapse brings me fond memories of PowerPlay

This is one of those features that people have been asking for since 2005 and suddenly it has arrived! And better yet, it functions exactly like you hoped it would. Expand and collapse currently function only for OLAP sources – not for navigation paths made in data modules. I’ve been told this will be fixed in an upcoming release.

Enhanced unit formatting

Enhanced unit formatting further builds out the utility of dashboards.
Enhanced unit formatting brings additional flexibility to measure display

With enhanced unit formatting, dashboard users now have the ability to append a custom label to the end of measures displayed inside Cognos visualizations. For example, if you have a field that is measured in units you can label them as such.

View source in dashboards

It's much easier to source a field in Cognos Analytics 11.1.6
It is now possible to identify the exact field used in a visualization

This is a very welcome addition. A new popup appears whenever you hover over a data item in the fields view. This solves a major problem. In previous versions it was impossible to tell which table supplied a field in a visualization, leading to much confusion when there were multiple fields with the same name in a model.

Be sure to read Matt Denham’s great overview of Cognos 11.1.6 dashboards.

Cognos Analytics 11.1.6 Reporting Changes

Cognos 11.1.6 doesn’t contain a ton of reporting changes but the ones we get are very good. The data table in particular gives a ton of functionality to end users that we’ve been requesting for a looooooooong time.

Meet the data table

The data table is a new object in Cognos report authoring that provides the type of interactivity that end users crave without javascript hacks. This is possible thanks to changes in how the data table queries and processes information. Much like Cognos 11.1. visualizations, the data table issues a single query to fetch data. The browser then stores, filters and renders that data based on user input.

Data tables are another slam dunk addition to Cognos 11.1.6
Data tables do things that you’ve wanted since 2004…
  1. Expand/collapse for OLAP data sources
  2. Each column features interactive filter and search capability
  3. Color/size/image indicators for KPIs
  4. Scroll bar exists in the data table rather than for the page

All this flows from changes to how Cognos queries data and renders the data table. This is a preview of where reporting is headed, and you should expect this paradigm for many objects including prompts in the near future. For some reason most of this functionality defaults to ‘off.’ To enable it you must:

  • Set ‘Show column filters’ to ‘Yes’ in the data table properties
  • Click ‘enable expand and collapse’ in the grouping and summary popup found in the data table properties

I haven’t had time to play around with the data table as much as I’d like, so I cannot comment about specific formatting options that may be missing – I assume there are many. However even if it’s not as ‘pixel perfect’ as the list object, the data table is a killer addition to your authoring toolkit.

11.1 reporting visualization enhancements

Cognos 11.1 visualizations receive a number of enhancements in 11.1.6 to bring the authoring experience more in line with the interactivity available in dashboards.

  • Measure groups in report authoring (this was already available in dashboards)
  • Drill up and down (again, already available in dashboards)
  • Categorical map coloring

You can read more about these changes in Rachel Su’s helpful blog post here.

Cognos Analytics 11.1.6 Data Module Changes

Data modules didn’t receive new features in this release. Instead there are three major quality of life enhancement for authors related to interaction with data servers. Small changes like these save authors a ton of headaches. I’m glad IBM consistently improves product usability rather than just piling on new capabilities.

Add individual fields to data modules

The ability to add individual fields to a data module rather than entire tables is a game changer
Adding data to data modules just got so much easier

Data modules are easy to build but surprisingly frustrating to edit. A big part of the problem came from the fact that you could only add tables to data modules, not individual fields. As a result you sometimes had to re-add hundreds of fields then manually delete them just to get a single new field into your module. Consider this solved – you can now add individual fields directly to the tables within a data module.

See unused fields in data sources

Seeing unused fields makes it easy to adjust Cognos 11.1.6 to match the structure of your database

The ‘show unused items’ feature makes it easy to identify which fields in your data source are not currently in your data module.

Reload metadata schema from data modules

Reloading metadata directly from data modules saves a ton of clicks and much confusion
Reloading metadata is much easier for module authors now

The disconnect between data modules and data servers causes frustration for modelers for two reasons. First, it requires a ton of clicks to leave the module interface to make data server changes. Second, modelers frequently find themselves locked out of data server settings entirely and must ask administrators to make server changes. Cognos Analytics 11.1.6 alleviates part of this issue by giving authors the ability to reload metadata directly from the data modules UI.

Small changes are a big deal

These three changes are small but add up to a significantly improved modeling experience. It is now significantly easier to load metadata, identify new fields and add only those that you need to your model.

Cognos Analytics 11.1.6 AI & Advanced Analytics Enhancements

The AI assistant gets smarter with each release. Cognos Analytics 11.1.6 brings two significant enhancements to what is rapidly becoming the flashiest feature in the solution.

Automatic dashboard creation is a major strength of Cognos Analytics 11.1.6
This dashboard was created from a single command, filters and all

AI learning for visualizations

This subtle but very cool change makes dashboards and explore much better for end users. Cognos Analytics will now learn your visualization preferences whenever you click the ‘save’ button. It uses this information to suggest visualization types as per your preferences in the future. For example Cognos suggests a bar chart and you change it to a column, Cognos becomes more likely to suggest a column in the future. Right now this works on an individual level rather than system wide. You can control this with the AI>Learning capability

Automatic dashboard creation enhancements

Guided dashboard creation shows extremely well and is getting more useful with each release. The AI Assistant can now accept conditions alongside the ‘create dashboard’ command such as ‘Create dashboard for products by average profit in Florida’. This will generate a complete dashboard based on the criteria passed in, including the state = Florida filter as well as converting profit from a sum to an average. Very impressive stuff.

Stand alone calculations in advanced analytics

This enhancement is important for anyone using Framework Manager… so basically everyone. The AI assistant and explore capabilities in previous versions could not factor in standalone calculations. Framework Manager often requires a standalone calculation to aggregate correctly. The only way around this was to build a data set – which I still strongly urge you to do for a myriad of reason outlined in my article What are Cognos Analytics data sets. However as of 11.1.6 the AI Assistant and the Explore features now consider these important calculations when they do their magic.

See this interesting update from Jason Tavoularis to learn more about Cognos Analytics 11.1.6 AI advancements.

Support for R kernel in notebooks

Cognos Analytics 11.1.6 notebooks now support the R kernel in addition to python. IBM’s implementation of Jupyter is very good in so far as they wisely choose to implement standard Jupyter rather than some kind of Cognos themed reskin. Now you have another reason to check it out.

Don’t miss our Cognos Analytics 11.1.6 YouTube livestream

Want to see 11.1.6 live? PMsquare livestreams each Cognos Analytics release on YouTube. The Cognos Analytics 11.1.6 release stream goes live on 4/28/2020 at 3:00PM Eastern and will remain available afterwards so be sure to watch!

See Cognos Analytics 11.1.6 live

Keep reading to level up your Cognos game!

  • 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

When To Use Cognos Data Sets

April 14, 2020 by Ryan Dolley 14 Comments

I introduced you to Cognos Data Sets in Part 1 of this series and you recognize some intriguing possibilities. You want the massively improved performance, simple presentation for end users and quick road to Cognos modernization that Data Sets offer, but you’re not sure how to start. Well I’m here to help you understand when to use Cognos Data Sets – how to recognize each situation and how Data Sets help.


Prepare Data for Advanced Analytics

Preparing data for Cognos Analytics Explore is a great example of when to use Cognos Data Sets
Advanced Analytics require high quality data to function properly

Advanced Analytics features like forecasting in Cognos often work much better with Data Sets than other data source types. A narrowly focused, in-memory source dramatically enhances the speed, interactivity, accuracy and usefulness of features like Explore or the AI Assistant. This is especially true compared to giant Framework Manager models.

Recognizing poorly prepared data

The need to prepare data is most apparent when the advanced features of Cognos Analytics fail to provide meaningful suggestions or build garbage output. This manifests in the following ways:

  • The AI Assistant cannot understand which instance of ‘customer’ you want and picks it from an incorrect namespace
  • The AI Assistant makes very poor suggestions
  • AI generated visualizations do not filter properly because they contain different versions of the same data item – ‘customer’ from 3 different tables
  • The ‘generate dashboard’ command creates a nonsense dashboard
  • The forecasting feature does not appear in line, bar or column charts
  • Explore takes a very long time to load or interact with

Using Data Sets for advanced analytics

Data Sets make it easy to simplify the data used for advanced analytics. Because they are quick to make and perform very well I use them any time I want a great experience for my end users. The goal of using Data Sets for advanced analytics are:

  • Remove any duplicates in the data. Each field should occur only once
  • Identify a specific subject of analysis and include only measures and fields that help understand that subject. The explore feature helps immensely with this
  • Help the AI assistance shine by producing meaningful results
  • Improve performance across the board, especially in Explore

Improve Performance of Existing Models

Most long time Cognos customers have at least some models that perform slowly. Maybe it’s logic processing at run time. Maybe it’s the underlying database. Whatever the cause, you can’t let your end users watch a wheel spin for minutes on end whenever they make a slight change to a dashboard. Oftentimes customers solve this problem by locking Dashboards, Stories, Explore and anything else new and cool away from users. That’s a big mistake.

Recognizing poor performance in Cognos

This is fairly straightforward. You know performance is poor because Cognos is slow, right? Generally yes but there are some situations where poor performance manifests in surprising ways.

  • People call you and say ‘Cognos is slow’
  • You check Thrive and it tells you ‘Cognos is slow’
  • User adoption for self service features is lacking
  • Schedules are frequently late or are challenging to maintain
  • Source systems process dozens or hundreds of similar queries
  • You just keep staring at that damn spinning wheel

Improving Performance with Data Sets

This is an area where Data Sets shine because you’ve already got a model with all sorts of embedded business logic. It’s extremely easy to generate data sets as needed, and they automatically inherit all that Framework Manager logic. Very little data rework results in huge performance gains. Your goal is to:

  • Take advantage of in-memory processing and server RAM
  • Summarize detailed data to a higher grain to decrease row counts and better target analysis
  • Sort data by commonly filtered data items
  • Filter out unnecessary records
  • Decrease load on underlying data bases
  • Banish the spinning wheel forever

Imagine a query that processes for 15 minutes and runs 100 times a day. You are spending 1,500 minutes processing that data. By moving to a data set, the query runs once for 15 minutes to load the data. All subsequent executions load in ~1 second as data pulls from memory, not the database. You just saved 1,445 minutes of processor time. And you saved the sanity of your end users.

A real world performance example

My friend Rory Cornelius gave me the following quote about Data Sets. Rory actually did this with one of our clients. It shows how these techniques work to solve all sorts of Cognos problems.

Not your typical use case, but my client has this huge set of scheduled jobs. There was one job that had 10 reports each that queried almost the same data. Each report took about 45 minutes to run and they wanted them done sequentially to limit load. I pointed the reports to a Data Set instead, and they took 2 minutes to run instead. The Data Set still takes quite a while to load, but even with that, the total time was cut by at least 5 hours with significantly less load on the database.

Rory Cornelias, Senior Solution Architect with PMsquare

Combine Data Sources in Cognos

Throughout my career the number one impediment to analytics delivery is the struggle to combine data from multiple databases or applications. Data exists at different levels of detail with messy, mismatched keys and incompatible query languages. It’s just tough out there. However Data Sets radically streamline this process, especially for data already in Cognos. They provide a form of lightweight ETL and query processing to supplement for fully featured tools like Incorta or IBM ADP/Trifacta.

Data sets can be used to combine SSAS cubes with data warehouse tables.
A real world example of combining data sets from one of my clients

Recognizing data source mashup bottlenecks

Whether it’s a lack of clear requirements or an IT bottleneck for ETL, projects often wait for months or years at this stage. Faced with mounting delays, frustrated end users often choose to export data from Cognos and go it alone in Power BI. But you can learn to recognize the signs of data mashup bottlenecks

  • The data warehouse request backlog grows to many multiples of the Cognos backlog
  • End users export tons of data to excel
  • Advanced metrics are challenging to build because you are missing key calculation components
  • You often make model or data warehouse changes to add just a few columns or tables

Combining data with Cognos Data Sets

The process of combining data sources using Data Sets could hardly be easier as I outlined in Part 1 of this series. Instead the challenge lies in working through the logic of how best to combine two sources. The main things you will need to do are:

  • Identify the fields required for your analysis and locate them in your data sources
  • Create a data set for each source
  • Aggregate data at a compatible level of detail
  • Perform necessary data cleansing to make joins possible
  • Add filters, calculations or other logic at the Data Set level, not in Data Modules or Reports/Dashboards
  • Schedule data sets so that they build in the correct order
  • Combine them by joining together in a data module

This technique allows you dramatically simplify some complex ETL tasks with large and complex databases by first boiling each source down to just the fields you need. The key thing is to embed as much logic into the Data Set load process as possible. This minimizes query cost at run time and makes building and maintaining your Data Modules as easy as possible.

Simplify Presentation for Self Service

Framework Manager models typically exist for IT and accumulate years or even decades worth of developer focused design decisions. As a consequence they often require crucial yet undocumented context to generate accurate and timely queries, with a host of conditional flags, hidden filters and inscrutable calculations. Self service becomes impossible when end users don’t understand the structure or context of data. This is the number one objection I hear to rolling out Dashboards or Explore in Cognos Analytics

Recognizing overly complex models

An overly complex model stands between you and the evolution of your BI practice like an unbridgeable chasm. It’s calling card is the list of things you cannot accomplish because ‘the data is too complex.’ You know it by:

  • End users cannot effectively use the model, or you have locked them out of it due to data quality concerns
  • Self service feature roll out met with limited success due to data complications
  • Use of your data is always accompanied by caveats, ‘You have to include flags x,y,z to get meaningful results’
  • Debugging data problems is extremely confusing or time consuming
  • New hires to the BI team require weeks or months to get up to speed with the data

Preparing Data for Self Service

Data Sets are the bridge to this chasm. Because they are so easy to make and inherit all the logic from your Framework Manager source, you embed and effectively hide the underlying complexity with a well designed Data Set. You will need to build multiple Data Sets from the same model to effectively simplify the presentation – this is a factor of your design. Remember, the goal for Data Sets is to break a ‘one size fits none’ model into smaller, usable components. Let your data sets multiply!

  • Break your large model into smaller, digestible subject areas based around the types of questions your users need to answer
  • Build a Data Set for each subject area
  • Don’t be shy about overlapping data in multiple Data Sets. The end goal is to make something easy to use for an individual subject area
  • Don’t be shy about building lots of Data Sets
  • Remember – the Data Set inherits your Framework Manager logic. You should have a high degree of data consistency across Data Sets as a result
  • Always be willing to alter, change, abandon and create new data sets based on evolving user needs.

Your instincts from Framework Manager probably tell you to come up with a grand, cross – Data Set design to ensure consistency and eliminate re-use of fields. Don’t do this. Remember, tailor each Data Set to the needs of its user community and be willing to adapt as those needs change. This is the key to modernizing Cognos to compete with Tableau or Power BI

Modernize Your Cognos Practice

By following all these steps you will modernize much of your Cognos Analytics practice without intentionally doing so. A modern BI practice requires two modes of operation, often called ‘Mode 1’ and ‘Mode 2’. Mode 1 is the traditional enterprise BI way of doing things; ETL, ODS, EDW, monolithic Framework Manager models, IT authored reports. It remains a vital component of our work. However Mode 2 is equally important; Agile data mashup, in-memory processing, collaboration with self-service users and above all, speed.

The techniques outlined above will get you to mode 2 rapidly, even if it seems daunting or impossible today. Because you’ve done so much great work building your Framework Manager models you have an incredible foundation for self service – you just haven’t realized it yet. Using Data Sets in combination with Data Modules and Dashboards will give you the performance, simple data presentation and agility you need. Try it! And as always if you need some help along the way reach out to me and PMsquare. The answer to ‘When to use Cognos Data Sets’ is ‘Now!’


  • 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

What are Cognos Data Modules?

April 8, 2020 by Ryan Dolley 23 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.


  • 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

What Are Cognos Data Sets?

April 7, 2020 by Ryan Dolley 16 Comments

I’ve explored Data Modules in depth on this blog over the last year with the hope of showing you how awesome data modeling in Cognos Analytics can be if you really embrace it. There is, however, an additional piece of the Cognos data puzzle that you need to understand to unlock the full potential of the platform – the Data Set. So let’s answer the question – just what are Cognos Data Sets?


This video introduction to Data Sets covers everything you need to know!

The IBM Blueview Data Set Series

What are Cognos Data Sets?
When to use Cognos Data Sets


What is a Data Set in Cognos?

The Cognos Data Set screen is easy to understand and use.
Data Sets offer an in-memory data processing option for Cognos Analytics

Simply put, a Data Set is data source type in Cognos Analytics that contains data extracted from one or more sources and stored within the Cognos system itself as an Apache parquet file. The parquet file is then loaded into application server memory at run-time on an as-needed basis. This (usually) greatly enhances interactive performance for end users while reducing load on source databases. When combined with Data Modules, Data Sets offer incredible out-of-the-box capabilities like automatic relative time, easy data prep and custom table creation.

Data Sets are also extremely easy to build from your existing Framework Manager or Transformer packages making them an excellent option for getting the most out of your legacy Cognos 10 models. In fact this is probably the #1 use case for the Data Set technology and is the absolute fastest way to modernize your environment and turn Cognos into a rapid-fire data prep and visualization machine.

I’m going to write a full blog post about the exact situations that suggest a Data Set solution, but in short you should consider using Data Sets whenever:

  • Excellent interactive performance is a critical part of your deliverable
  • You wish to limit extremely costly SQL queries by re-using results
  • You must join multiple data sources together or accomplish other ETL tasks within Cognos rather than source systems
  • Existing Framework Manager or Transformer models are too complex or too slow for self-service
  • Someone tells you Cognos is slow but Tableau or Power BI are fast (those tools use Data Set-like technologies to enhance interactive performance)
  • You just want to do something really cool

Which Features can use a Data Set

There is one small limitation to Data Sets – while they function as a data source for all Cognos Analytics features they cannot be used directly to author reports. The solution to this is simple – wrap them in a Data Module and import the Data Module to Report Authoring. You should be doing this anyway for all Data Sets as it provides maximum deployment flexibility and ease of upkeep. I will cover best practice topics like this in a future article.

How to Build a Data Set

The 'create data set' capability is pretty well hidden in Cognos Analytics
The ‘Create data set’ capability is hidden among model options

Building a Data Set is simple, especially if you have existing Framework Manager or Transformer models available in Cognos. In fact Data Sets can only be built on top of existing models or Data Modules- not directly on data servers. IBM has helpfully hidden the ‘Create data set’ capability in the ‘more’ menu of model objects in the environment, so it’s surprisingly easy to miss.

Cognos Data Set Creation

Creating a Data Set is a straightforward process, especially for experienced Cognoids. The UI is actually a re-skinned version of Report Authoring and many of your favorite tricks will work here. Building a Data Set is as simple as dragging columns into the list object, saving and loading data. Of course there are additional options you can take advantage of.

The Cognos Analytics Data Set creation screen shares many features with the Report Authoring interface
  1. Source View: Browse the tables and fields in your data source exactly as you would in Report Authoring
  2. Data List: The data table shows a live view of the Data Set as you build it. It queries new data as you make changes
  3. On Demand Toolbar: The on demand toolbar appears when you click on a column, giving you the ability to filter and sort.
    1. Filtering: Filters help you focus the data in your Data Set to just what you need. Fewer rows = better performance.
    2. Sorting: Sorting by the columns most used in report or dashboard filters (for example, time data) can greatly improve performance
  4. Query Item Definition: The query item appears when you double click a column header. You have access to query item functionality from Report Authoring, which means you can really accomplish a lot from this popup.
  5. Preview: Unchecking the preview button switches the data table into preview mode which turns off automatic data query as you make adjustments to your Data Set.
  6. Summarize and Row Suppression: The summarize function rolls your data up to the highest level of granularity, for example rolling daily data up to the month. Row suppression is honestly a mystery to me Special thanks to Jason Tavoularis at IBM for an explanation – row suppression in data sets only applies to dimensional data sources and does the same thing as using row suppression in Report Authoring.

Once you’ve imported your desired data, set your filters, sorts and summaries and maybe added a few calculations for good measure it’s time to save, load and deploy your Data Set.

Saving and Loading a Data Set

Data Set save options include Save, Save As and Save and Load Data
Data Sets must be saved and loaded to be available

When you save a Data Set you will see the option to ‘Save and load data.’ This will allow you to select a directory in Cognos to house the Data Set object. It will also issue one or more queries to retrieve data and populate a parquet file. This file is stored in Cognos and loaded into memory upon request when users access the Data Set. Check out the ‘Flint’ section of this in depth article to understand what happens under the hood during Data Set creation and Query

Scheduling and Managing Data Sets

Data Sets only contain data from their last load; it is good practice to get in the habit of scheduling and monitoring Data Sets to ensure they contain relevant data and continue to perform well.

Data Set Scheduling Options

Data Sets and Reports have all the same scheduling options
Data Sets have the same scheduling options as reports

The easiest way to schedule Data Sets is via the ‘schedules’ tab in Data Set properties. Data Sets and Reports share all the same scheduling options, including the ‘by trigger’ option. Scheduling via a trigger makes it easy to ensure Data Sets only load after your ETLs complete. This works great for simple or one-off scheduling tasks.

For more complex schedules, Data Sets are available in the Job feature. Again, they function as if they were reports as far as building Jobs is concerned.

Data Set Management

The Advanced Properties view contains the statistics you need to manage Data Set performance.
Manage Data Sets using their advanced properties

The Data Set properties screen contains the info you need to effectively maintain fresh and performant data for your end users. At the top of window you can see the last load date of the Data Set, while expanding the ‘advanced’ exposes the following:

  • Size: The compressed size of the parquet file on disk
  • Number of rows: The number of rows in your data set. Keep this under ~8 million for best performance
  • Number of columns: The number of columns in your data set. No hard limit here, just don’t include columns you don’t need
  • Time to refresh: The time it takes for the Data Set to load
  • Refreshed by: The name of the person who last refreshed the data set

I will write a longer post about Data Set tuning and troubleshooting. For now it’s key to keep in mind the row and column suggestions above. And while ‘Time to refresh’ is important, this represents the time it takes to load data and has no impact on the performance end users will experience. The beauty of Cognos Data Sets is that by front-loading the processing, you can create a complex result set that takes hours to load but offers sub-second response time to end users.

A Real World Example of Data Sets in Action

I have used Data Sets in many successful client engagements to greatly improve performance, simplify presentation or accomplish ETL tasks in an afternoon that their DW team had put off for years. Here is a simple example for you.

The Problem: Metrics, metrics everywhere!

This customer came to us with a very, very common problem. The sales support team had identified a need for some new advanced metrics and built out a prototype dashboard. However, the underlying data divided between two Microsoft SSAS cubes and a handful of tables in the EDW. The data warehouse had given an estimate of many months to create the necessary tables and cubes.

The Solution: Cognos Analytics Data Sets

The customer brought in PMsquare on a 40 hour contract to make this happen. If your initial reaction to that contract length is skepticism I don’t blame you. In Cognos 10 this would have been impossible. However thanks to Data Sets I was able to do the following:

  • Extract the needed data from each SSAS cube and the EDW into a Data Set. There were 3 Data Sets total, one from each data source.
  • Join the Data Sets together into a Data Module and add in all the Data Module goodies like relative time
  • Create a new, final polished Data Set from that Data Module to simplify presentation and improve performance
  • Build out the customer’s dashboard

The customer was extremely satisfied with the end result, which looked something like this:

A real world data flow from a project I successfully completed.
A cavalcade of awesomeness awaits you with Data Sets

Cognos Analytics Data Sets in Summary

As you can see, I really was able to accomplish months of work in a single week using Data Sets. Obviously this technology cannot replace all ETL tasks however Cognos Analytics is now an option for low to medium complexity transformations. And you now have a slam-dunk option for rapidly simplifying presentation or improving performance vs even the simplest database view.

Be sure to check back next Tuesday, 4/14/2020 for part two of this series: When To Use A Data Set!


Catch up on all things Cognos:

  • 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
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Go to page 4
  • Go to Next Page »

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