• 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

The Blog

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

The 2021 Gartner BI Magic Quadrant is Broken for Cognos Analytics

February 19, 2021 by Ryan Dolley 9 Comments

It’s that time of year again – time for the Gartner BI Magic Quadrant. Like a bad ex who just won’t back off, Gartner drops their annual market warping report around Valentine’s Day to ruin the mood. I’ve expressed strong opinions in the past about Gartner’s report and methodology and this year will be no different. Put simply, they did Cognos Analytics dirty again while rewarding some competitors based on evaluation criteria that have nothing to do with the capabilities or quality of the software. In particular I argue that:

  • Gartner heavily weights the number of inquires each tool generates for Gartner’s advisory services in determining the rankings. This is a self-reinforcing feedback loop.
  • Gartner is rewarding BI vendors that have attachments to ERPs, office suites and other enterprise software. Particularly SAP and Oracle.
  • Gartner’s write up of IBM doesn’t align with their ranking on the scatterplot, particularly in completeness of vision.
  • Gartner’s Peer Insights customer review site tells a completely different story about Cognos Analytics.
Gartner badly undervalues Cognos Analytics in the 2021 magic quadrant
The Gartner BI Magic Quadrant undervalues Cognos Analytics again

You may be thinking, ‘Ryan of course you feel this way – you’re the most public Cognos fanboy on Earth!’ Guilty as charged. But my issue isn’t with the placement of Microsoft or Tableau in the Leader’s Quadrant. Those tools are crushing it and deserve their placement. What I take issue with is IBM’s location in regard to legacy competitors like SAP or Oracle and the overall parameters that determine the ranking.

How does Gartner evaluate software?

Contrary to popular belief, Gartner is not conducting an in depth evaluation of what they call ‘ABI platforms’ to identify and compare the performance, features and ease of use of the software. Instead, Gartner typically relies on the number of inquiries each tool generates for their own consultancy in addition to surveys and interviews to determine this ranking. The natural outcome is a feedback loop where being a leader last year is the primary criteria for being a leader this year. You simply aren’t going to call Gartner to talk about IBM because Gartner is telling you not to bother.

This year they also seem to be penalizing enterprise tools that don’t come bundled with ERPs or other enterprise software. Unfortunately those tools are typically very poor BI tools – I would know, I’ve worked for them.

Cognos Analytics as a case study

This manifests in a yawning gap between IBM’s scatterplot coordinates and what they actually wrote about Cognos Analytics. Unfortunately the image is what carries all the weight in this report. Comparatively few people will read IBM’s entry, so I’ll summarize it for you below:

IBM Cognos Analytics Strengths

  • One of the only tools that offers comprehensive enterprise reporting and self-service. Gartner calls these ‘mode 1 and mode 2’
  • Strong product vision combining AI augmented analytics, traditional BI and planning capabilities
  • Deployment flexibility on prem and on all cloud platforms

IBM Cognos Analytics Weaknesses

  • People don’t call Gartner asking about Cognos
  • It doesn’t have ‘adoption drivers’ like associations with popular ERPs or office suites
  • It costs about the same as standalone BI tools but more than BI tools that are bundled with ERPs or office suites

Very curious. All of Cognos’ strengths are things BI developers, administrators and users care about. And all of those weaknesses are things that procurement people, industry analysts and – most importantly – Gartner themselves care about. It’s not that I think these are unimportant points, but it seems that software features and quality carry significantly less weight.

Where does Cognos belong?

I’m going to be as objective about this as possible and explain my reasoning, but first take a gander at the world premier of the Ryan Dolley BI Magic Quadrant!

A more realistic assessment values IBM for a great vision while recognizing execution challenges.
The 2021 Ryan Dolley BI Magic Quadrant is available free of charge!

Cognos Analytics completeness of vision

Gartner really got completeness of vision wrong. This is where Cognos Analytics shines. No other BI platform on Earth combines the breadth of present day capabilities with the visionary roadmap of IBM. Gartner sort of acknowledges this in their write up but it’s not at all reflected in the MQ image so let me break it down for you:

Cognos Analytics today

  • Integrated AI Assistant chatbot for NLQ capabilities and auto-visualization
  • ML-driven one-button forecasting
  • Correlation and causation engine suggests relationships in data
  • Jupyter notebooks for easy BI/ML integration
  • Absolute top enterprise reporting capability in the world
  • Huge scale bursting, schedule and event-driven data distribution
  • Robust extensions allow incorporation of custom visualization libraries, js code. You can make Cognos do damn near anything. Just ask Paul.
  • Easy to build self-service dashboards and visualizations
  • Easy self-service data modeling
  • Self-service storytelling/narrative BI
  • Unlimited scale
  • Deployable on prem and on any cloud

It can’t be stressed enough that none of the current ‘leaders’ offer such a wide spectrum of features. In fact they offer only bare bones enterprise reporting if they offer it at all.

Cognos Analytics roadmap

The Cognos Analytics roadmap is very strong. There’s a lot I don’t know and a lot I can’t say, but to give you some idea of where things are going:

  • Easy what-if scenario modeling and data science for end users
  • AI-driven data prep and data quality evaluation
  • Deeper integration with Planning Analytics to provide a single portal for enterprise BI and planning
  • Dramatically improved NLQ including ontological customization
  • Continued containerization and modernization of the platform

Cognos Analytics ability to execute

Let’s be honest. There are some real concerns with how existing Cognos Analytics customers are executing with the platform. I know this because I hear your struggles. And some of that comes from stability issues and UI/UX quirks with the software that IBM absolutely needs to address. The good news is that IBM knows this.

IBM's ability to execute score is a reflection of outdated practices, not bad software.
Note to the Cognos community – please start executing!

But the reality is that many long term Cognos customers are simply not able to realize the full capabilities and value of the platform and in many cases I think it’s their own fault. The culture and practice that grew up around Cognos was formed in the late 90s and 2000s and it shows. Features that get extensive and successful use in Power BI and Tableau are left virtually untouched by a majority of Cognos customers. Data modules and data sets have improved massively since their less-than-stellar debuts. Their usage in existing Cognos deployments has yet to catch up with just how darn good they are and it’s a shame. Consider this my plea to you to start delivering with Cognos the same way people deliver with Tableau or Power BI. The platform can support it, the technology is there. What needs to change is our collective ambition as a community of practice.

This is not to say that nobody is executing at a high level. There are some people who are using all of Cognos’ capabilities to the fullest and having huge success. And new customers typically do great. But until us old timers collectively start to take advantage of what this platform has to offer I can’t really fault Gartner’s analysis for ability to execute. It’s off, but only a little.

Cognos Analytics and Gartner Peer Insights

Cognos ranks much higher in Gartner's peer insights platform
Gartner Peer Insights tells a very different story about Cognos Analytics

Gartner has a lesser known product called Peer Insights and it tells a very different story. Peer Insights features product reviews by verified users rather than the opinion of analysts. Take a second to check out the Business Intelligence page and sort by average review. Scroll past the minor players with a single glowing review and look at the placement of the platforms included in the Magic Quadrant. As of this writing Cognos Analytics scores worse than Tableau Desktop. It scores better than Microstrategy, ThoughtSpot, Tableau Server, Domo, Power BI, Qlik Sense, Oracle, Sisense, Looker, Amazon, SAP and Board International.

What to make of all this?

I hope I’ve done a good job of walking you through why I strongly disagree with IBM Cognos Analytics’ placement on the 2021 Gartner Business Intelligence Magic quadrant. I’ve been as objective as I can in this analysis and I don’t think Gartner is wrong across the board. I really do like Power BI and Tableau. I’m increasingly impressed with Domo and very intrigued to see what AWS does going forward. But this report badly misses the mark on Cognos and it does so by disregarding what a strong enterprise BI platform it is in 2021 and penalizing IBM for the lack of market awareness that Gartner themselves have caused.


Want to continue the conversation? Connect with me on LinkedIn and check out the PMsquare YouTube channel. And don’t miss the great Cognos content below!

  • 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

Data Modeling for Success: BACon 2020

October 15, 2020 by Ryan Dolley Leave a Comment

Getting Started

Logging in and creating a folder for your work

  1. Because of high interest in today’s session we are using a single 96 core 768 GB RAM Cognos server with anonymous access allowed. This makes logistics much easier but means we don’t have access to the ‘My content’ feature of Cognos.
  2. Click here to access Cognos: http://3.216.29.72:9300/
  3. Click ‘Team content’ and navigate to the ‘BACon Users’ folder.
  4. Click the ‘+’ button in the navigation window and select ‘Folder’.
  5. Give your folder a name with the following format: Initials – favorite movie – favorite color. In my case it would be ‘RPD-Solyaris-Green.’ Hopefully this ensures a unique folder for everyone.
  6. We are now ready to begin today’s class.

Organizing for Self Service with Data Sets

In our first example we will prepare a data set for self-service. This data set is sourced from an existing Framework Manager package and is a good example of a technique PMsquare often uses to simplify presentation and improve performance for end users. We will cover the basics of creating a data set, optimizing its performance and using it in a dashboard.

Creating a data set off a relational package

  1. Click ‘Team content’ on the left side of the screen and Navigate to Team Content>BACon Modules.
  2. Hover over ‘Go data warehouse (query) package and click the ‘More’ button.
  3. Select ‘Create data set’ from the ‘More’ menu. The data set screen will launch.
  4. The data set screen might look familiar – it is a stripped down version of Cognos Analytics report authoring. You should see a ‘Source’ section with the ‘Go data warehouse (query)’ package loaded, and an area that reads ‘Add data here’.
  5. Expand the ‘Sales Target (query)’ folder under ‘Insertable Objects’.
  6. Expand the ‘Sales Target (query)’ namespace located within.
  7. From the namespace, drag the following fields to the ‘Add data here’ section of the screen. A list will appear and populate with data from the package:
  8. Retailer.Region
  9. Retailer.Retailer country
  10. Retailer.Retailer name
  11. Retailer.Retailer code
  12. Employee by region.Employee name
  13. Employee by region.Position name
  14. Time.year
  15. Time.Month key
  16. Time.month
  17. Time.month (numeric)
  18. Sales target fact.Sales target
  19. Click the ‘Page design’ drop down and select ‘Page preview’.
  20. Turn off  ‘Summarize detailed values…’ and observe the effect of summarization on the data displayed in the list. The underlying data is at the level of individual Target transactions.
  21. Turn detail summarization back on.
  22. Click the ‘Year’ column and select ‘edit layout sorting’ from the sorting drop down menu in the toolbar.
  23. Drag ‘Year’ and ‘Month (numeric) to the ‘Detail Sort List’ and click OK. We have sorted our data set by year and month. Sorting on commonly used columns can improve performance.
  24. Click ‘Position name’ and select ‘Create custom filter’ from the filter drop down menu in the toolbar.
  25. Click ‘Condition’ to access the conditional filter screen
  26. In the ‘Input condition’ next to ‘Contains’, type ‘Sales’. This will filter the Position Type column for values that contain the word ‘Sales’. Removing unnecessary rows or columns can improve performance.
  27. Click the ‘Save’ icon and select ‘Save as’.
  28. In the ‘Save as’ window, navigate to ‘Team Content > BACon user>your folder. This is the folder you created in step 5 of Getting Started.
  29. In the ‘Save as:’ text box, type ‘Sales Target Data Set’.
  30. Click ‘Save’. The ‘Save as’ window will close.

Finding and loading a data set

  1. Click the ‘Home’ icon to return to the Cognos Analytics welcome screen.
  2. Click ‘Team content’ on the left side of the screen and navigate to ‘Team Content>BACon users>your folder’.
  3. Hover over ‘Sales Target Data Set’ and click the ‘More’ button.
  4. Select ‘Reload’. The Data Set loading notification will appear at the top of the screen. It should load and disappear fairly quickly.
  5. In the ‘More’ menu, select ‘Properties’.
  6. In the ‘Properties’ menu, notice the created, modified and data refreshed dates & times in the upper right.
  7. In the ‘Properties’ menu, expand ‘Advanced’ and scroll down to see statistics about this data set:
  8. Size
  9. Number of rows
  10. Number of columns
  11. Time to refresh
  12. Refreshed by
  13. Close the properties menu.

Dashboard Creation

  1. Click the ‘More’ button next to ‘Sales Target Data Set’ and select ‘Create dashboard’ in the ‘More’ menu.
  2. Select the fourth available template in the ‘Create dashboard’ screen. It has a large rectangle in the center with four rectangles equally spaced at the top.
  3. Click ‘Okay’. The ‘New dashboard’ screen will load.
  4. On the left side of the screen, click on the ‘Assistant’ icon.
  5. Type ‘Show average sales target for country’ and hit enter. Note that Sales target total is a sum measure, not an average. Cognos is able to compute new aggregate types on the fly using natural language query.
  6. Click and drag the map that appears into the ‘drop here to maximize’ icon in the center of the screen.
  7. Drag ‘Retailer region’ next to the ‘This tab’ icon at the top of the screen to add it to the tab filters.
  8. Click the ‘Retailer region’ filter and select ‘Northern Europe’. The map will zoom to extent.
  9. Add additional visualizations to the dashboard as you see fit.
  10. Save your dashboard as ‘Employee Target Dashboard’.

Combining data sets with other sources using data modules

Data sets alone are great for simple requests; for more complex modeling requirements containing multiple tables, complex relationships or relative time you are better off creating a data module. Data modules can contain multiple data set, data base or excel data sources and are great for IT and power users alike.

Creating a data module

  1. Click the ‘Plus’ button in the lower left side and choose ‘Data Module’
  2. In the ‘Select sources’ menu, select ‘Team Content’. Navigate to ‘your folder in ‘BACon users’
  3. Select ‘Sales Target Data Set’ and click ‘OK’. The data modules screen will open.
  4. Click the table ‘Sales Target Data Set’ in the source view. This will load a preview of the data from your data set into the ‘Grid’ tab. Here you can easily make comparison for join compatibility and instantly see the results of any filters, groups or calculations you build

Adding additional data sources

  1. Click the ‘+’ icon in the data source view and select ‘Add new sources’
  2. Click ‘Data servers and schemas’ and click ‘GOSALESDW/gosalesdw’. Click ‘OK’.
  3. Click ‘Discover related tables’ and explore the natural language model generation capability a bit. Then click ‘Previous’
  4. Click ‘Select tables’ and click ‘Next’.
  5. Choose ‘Go Time Dim’ from the available sources and click ‘OK’. This will add the Go Time Dim table to our data module.
  6. Repeat the above process to add:
    • Great_outdoor_sales data server
      • GOSALES/gosalesrt schema
        • Retailer table
        • Retailer Site table
        • Retailer Type table
    • Team content>BACon>BACon files
      • Sales data excel.csv
  7. At this point you should have six tables in your data module:
    • Sales Data Excel.csv
    • Retailer
    • Retailer Site
    • Retailer Type
    • Go Time Dim
    • Sales Target Data Set
  8. Save the model as ‘Sales Analysis Date Module’ in ‘your folder’.

Data cleanup and prep

  1. Click the arrow next to the ‘Go Time Dim’ table to expand it. Notice the large number of fields with a ruler icon. Cognos has incorrectly identified these as measures.
  2. Ctrl-click each ruler icon the click the ‘properties’ icon in the upper-right corner of the screen. This will open the properties window.
  3. Change ‘Usage’ from ‘Measure’ to ‘Attribute’. Change ‘Represents’ to ‘Time’.
  4. Click the ‘Month De’ field then shift-click the ‘Weekday Tr’ field. This will select all the non-english fields in the table.
  5. Click the ‘more’ button and select ‘Remove’. This removes these fields from the model.
  6. Repeat this process in the ‘Retailer Type’ table to leave only ‘Retailer Type Code’ and ‘Type Name En’.
  7. Save your work.

Simplify table structure

  1. Click the ‘more’ button next to ‘Sales Analysis Data Module’ in the source view. Select ‘Table’ in the menu that appears.
  2. Click ‘Select Tables’ and select the three retailer tables.
  3. Click ‘Create a view of tables’ and click ‘Next’
  4. Rename the new table ‘Retailer Dim’
  5. In the ‘Selected Items’ menu, select the following fields:
    • Retailer Code
    • Company Name
    • Rtl City
    • Rtl Prov State
    • Rtl Country Code
    • Type Name En
  6. Click ‘Refresh’ to check your work. Click ‘Finish’ to create your new table.
  7. Examine your handiwork in the ‘Custom tables’ tab. This tab makes understanding the data flow in your model much easier.
  8. Save your work.

Create relationships

  1. Ctrl-Click ‘Sales Data Excel.csv’ and ‘Go Time Dim’.
  2. Right-click one of them and choose ‘Relationship’ under the ‘New’ header. This will open the new relationship screen
  3. Click each table to select it, then right click and select ‘Create relationship’. The create relationship screen will open.
  4. Ensure the 1 indicator is under ‘Go Time Dim’ and the N indicator is under ‘Sales Data Excel.csv’. You can swap these by clicking the wheel icon in the lower left corner of the screen.
  5. Select ‘Day key’ from each table and click ‘Match selected columns’ This will create the necessary join criteria.
  6. Click ‘Refresh’ to see a preview of your join.
  7. Click OK to build the join.
  8. Repeat this process to create the following joins:
    • Go Time Dim 1-N Sales Target Data Set
    • Sales Target Data Set N – 1 Retailer Dim
    • Retailer Dim 1 – N Sales Data Excel.csv

Refining and enhancing the model

Adding additional business logic can greatly enhance the value of your data module and make it much easier for professional and self-service authors to quickly create valuable content.

Creating calculations

  1. Ctrl-click ‘Quantity’ and ‘Unit sale price’ in the Sales Data Excel table.
  2. Click the ‘more’ button and choose ‘create calculation’.
  3. Name your calculation ‘Sale Total’ and select ‘x’ from the calculation drop down menu.
  4. Note the ‘Calculate after aggregation’ option on this screen.
  5. Click ‘OK’
  6. Repeat the steps above to create the following calculations:
    1. Cost total: Quantity x Unit cost
    1. Sale margin: Sale total – Cost total
    1. Plan variance: Sale total % Planned revenue
  7. Click the ‘more’ button next to the Sales Data Excel table and select ‘calculation’. This will open the advanced editor.
  8. Name your calculation ‘Sales Margin’.
  9. Copy-paste the following logic:
    1. (Revenue – Cost_Total)/ Revenue
  10. Click the ‘more’ button next to ‘Sales margin’ and select ‘Format data’.
  11. Select ‘Percent’ in the drop down. Click ‘OK’.
  12. Ctrl-click ‘Cost total’ and ‘Sale total’. Click the ‘more’ button and select ‘format’
  13. Select ‘Currency’ in ‘Format type’.
  14. Select ‘USD’ in the ‘Currency’ drop down.
  15. Click ‘OK’

Adding drill paths

  1. Expand the ‘Go time dim’ table and click the ‘more’ button next to ‘Current Year’.
  2. Select ‘Create navigation’ path.
  3. Name the navigation path “Time’
  4. Drag ‘Current Quarter’, ‘Current Month’ and ‘Day Date’ into the navigation path. Click ‘OK’
  5. In the source view, click the ‘Identify navigation path members’ button.
  6. Repeat the process to create the Retailer location path:
    1. Rtl Country Code
    1. Rtl Prov State
    1. Rtl City
  7. You can create any hierarchy you want – feel free to make something crazy!

Adding relative time

  1. Click the ‘Add new sources’ button and select ‘Add new sources’.
  2. Navigate to ‘Team content>Calendars’ and select ‘Fiscal calendar’. Click OK. The Fiscal Calendar table should be visible in your module.
  3. Expand ‘Go Time Dim’ table and click ‘Day date’. Open the column properties.
  4. Under ‘Lookup reference’ select ‘Fiscal Calendar’
  5. Expand ‘Day Date’ and note the large number of relative time filters.
  6. Expand the ‘Sales Data Excel.csv’ table and shift-click the calculations we made above.
  7. Click the ‘more’ button and open the properties
  8. Set the lookup reference to Go Time Dim.Day Date.
  9. Expand any of the calculations and note the large number of relative time calculations.

Cleaning up for consumption

  1. Open the properties for ‘Sales Data Excel.csv’ and change the name to ‘Sales’.
  2. Change ‘Sales target data set’ to ‘Sales target’.
  3. Click the ‘more’ button next to ‘Sales Analysis Data Module’ and select ‘Folder’ in the ‘New’ section.
  4. Name the folder ‘Retailer tables’
  5. Click and drag the ‘Retailer’, ‘Retailer Site’ and ‘Retailer Type’ tables into the ‘Retailer tables’ folder. This will nest the tables within the folder.
  6. Click the ‘more’ button next to the ‘Retailer tables’ folder and select ‘Hide from users’.

Managing modules

Securing models

  1. Click the ‘Source view’ button.
  2. Expand ‘gosalesrt’ data server.
  3. Click the ‘more’ button next to ‘Retailer’ and select ‘set data security’.
  4. Click ‘Add security definition’ in the properties window. This will open the security window.
  5. Name your security filter ‘Analytics users’
  6. Expand the ‘Cognos’ namespace and select ‘Analytics users’. Click ‘OK’.
  7. Selected ‘Company Name’ from the Filters drop down and click ‘Add a filter’. The filter window will open.
  8. Select ‘4 golf only’ and click ‘OK’
  9. This sets the data level security at the data server level, not on the level of your individual model! Changes made here will effect ALL models built on this data server, for better or worse.
  10. Click ‘Cancel’ to leave the security window.

Model inheritance

  1. Click ‘New’ and select ‘Data module’
  2. Navigate to the ‘Sales Analysis Data Module’ located in ‘your folder’. Click ‘OK’
  3. Notice how each table is now turquoise with a ‘link’ icon on it. These are linked tables and will inherit changes made to their parent module.
  4. Check the ‘more’ menu on the ‘Sales’ table. Note that many options are missing. The same is true for individual data items.
  5. Note the ‘Break link’ option in the ‘more’ menu. This will disconnect the linked table from it’s parent and it will no longer inherit changes. The ability to click this button can be turned off in security.

Conclusion

In this workshop we combined a data set sourced from a Framework Manager package tables from a Microsoft SQL Server database and an Excel spreadsheet into a single data module, which we made available to self-service and professional authors.

I hope this workshop has unlocked a deeper understand of how the Cognos Analytics data prep features can drastically reduce the time it takes to acquire, model and visualize data for both self-service users and IT professionals. As always, please contact me at rdolley@pmsquare.com with any questions!

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
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 7
  • Go to Next Page »

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