Data Modules

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:

Open the data module that requires relative dates
Slick the ‘add source’ button
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.
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.
Now select any date or date/time column in your data and open the properties. Select your calendar table under the ‘lookup reference’ property.
Congratulations, you have configured your relative date filters!
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.
Congratulations again, you have configured your relative date measures!
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.

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.

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:

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

Cognos relative dates in summary

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

IBM documentation references

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

Keep reading for more awesome Cognos content!

What are Cognos Data Modules?

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.

Relative Time in Framework Manager

December 31, 2019 by Ryan Dolley 6 Comments

Kamil asks an excellent question about relative time in framework manager in response to my Framework Manager vs Data Modules article:

Great article. I have one question, is it possible to use relative dates with packages from framework manager?
– Kamil

Like all questions in Cognos, the short answer is ‘no’ and the long answer is ‘yes!’ Let’s take a quick dive into relative time in Framework Manaager.

Relative time in FM? No!

The enrich package screen of Cognos — The Enrich Package interface

There is no ‘easy button’ for using the new relative time functionality with Framework Manager, unfortunately. I was briefly hopeful that this is possible using the enrich package functionality but it’s not there.

Enrich package is an important piece of the Cognos pie so it’s worth talking about briefly. Enriching a package provides needed context that allows Dashboards, Explore and the AI Assistant to do their thing. Most notably, enriching a package will allow Cognos to properly display time and geographic data types and will collect the sample data that allows the AI assistant and Explore to function properly. Enriching a package taxes your system so consider restricting the query subject or running it off hours.

If easy relative time does come to FM this is where I’d expect it to go. It’s worth reiterating that FM itself will receive no changes going forward so it’s time to start making the change to data modules. It’s easier than you think!

Relative time in FM? Yes!

Here’s where things get a little trickier and using relative time with your FM model becomes possible. To make this work we’re going to need to use https://ibmblueview.com/what-are-cognos-data-modules/Data Modules, custom tables and the lookup reference feature.

Step 1: Add a package source to a data module

Adding a package as a source to data modules — A package has been added as a data source to this data module

Click the ‘new’ icon and select data module
Navigate to your package in the folder structure, click on it and click ‘OK’
The data module screen will open with the package visible in the data tray

Step 2: Create a custom time table

Building a custom table in Cognos — Building a custom time table from a package

Click the ‘Custom tables’ tab and click ‘Create custom table’
Click ‘select tables’ and click the package source. All the tables in the package are displayed on the left.
Click ‘create a view of tables’ and click ‘Next’
Don’t forget to give your custom table a new name!
Click ‘invert’ then select only the table with which you want to use relative time
Click finish. Your custom time table will appear in the data tray

Step 3: Add relative time functionality

Adding relative time to the custom table in Cognos — Relative time can be added to your custom time table

Click the ‘Add sources and tables’ button and select ‘Add more sources’
Navigate to the ‘Calendars’ folder in the samples and select the ‘Fiscal calendar’ data module
Click ‘OK’. The FiscalCalendar table will appear in the data tray, hidden by default
Expand your custom time table, click the date you wish to use for relative time and click the ‘properties’ button in the upper right. The properties window will open.
In the properties window, select ‘FiscalCalendar’ in the ‘Lookup reference’ drop down menu.
You now have relative time functionality in your data module!
Rinse and repeat for any additional time or measure fields that require this functionality

Step 4: Join the custom time table to the package

Joining a custom table to a framework manager package — The custom time table can now be joined to the package

Click your custom time table and choose ‘New… Relationship’ in the pop up menu
Select the appropriate table to relate the custom time table to the rest of the package. Oftentimes this is a fact table.
Select the appropriate field(s), cardinality and join type for the join.
Click ‘OK’

There you have it! Relative time in Framework Manager (sort of)

At this point you can save and use your data module, which is made of your pre-existing package plus one or more custom tables. This doesn’t solve the obvious problem that your existing content references the package and not the new data module, but new content can be built off this module. The module will even inherit changes made to the package. And there you have it – relative time in Framework Manager… sort of!

Read on to level up your Cognos skills!

Cognos Analytics Data Servers

December 30, 2019 by Ryan Dolley 2 Comments

Joe Schmoe asks a fairly common question about Cognos Analytics data servers in the comments to my Framework Manager vs Data Modules article:

In Cognos Analytics 11.1.5, I don’t see a way to use a data source to feed a data module. It looks like in order to use data from a database, you need to use a package — which means you need to use Framework Manager anyway. Am I missing something?

Let’s take a look at how data servers work in Cognos Analytics to (eventually) answer Mr. Schmoe’s question.

What is a data server?

A data server is simply a connection to a database that has been defined within Cognos Analytics. This definition contains the database url, connection parameters, username and password necessary for Cognos to authenticate with, issue queries to and receive data from the desired database. A data server can be re-used by an infinite number of data modules to provide self-service users and report authors controlled access to desired data while maintaining high quality row-level security. Longtime Cognoids will no doubt say ‘Ryan, that sounds exactly like what we used to call a data source!’ Yes. Yes it does.

Cognos Analytics data server vs Cognos 10 data source

Cognos Analytics data servers and Cognos 10 data sources are fundamentally the same while having some differences in where and how they are configured. If you’re familiar with Cognos 10 you can breath a sigh of relief as your existing knowledge is almost 100% applicable. You just need to learn a slightly new UI.

Cognos Analytics data server

Introduced in Cognos Analytics
Provides a definition which Cognos uses to connect to a database
Configured via the manage menu
Requires JDBC drivers and uses exclusively JDBC connectivity
Uses Dynamic Query Mode exclusively
One data server can be used by infinite data modules
Source for data modules only – no Framework Manager [Editors note: Commentor Jerzy points out below that you can use data servers as a source for FM for DQM models only. I didn’t know that! Thanks Jerzy!]

Cognos 10 data source

Introduced in Cognos 8? 7? Reportnet? I was in college, it’s all very hazy
Provides a definition which Cognos uses to connect to a database
Configured via legacy administration console
Uses a wide variety of connection types including JDBC, ODBC and others
Uses Dynamic Query Mode or Classic Query Mode
One data source can be used by infinite Framework Manager packages
Source for Framework Manager only – no data modules (we’ll cover a simple workaround later in this article)

As you can see, data servers and data sources serve the same function within Cognos – defining a reusable database connection for Cognos – and have many of the same features. They differ primarily in the types of databases to which they can connect, the connectivity standard they use and how they are configured within Cognos.

Configuring data servers

Configuring a data server is easy and is – for the most part – a one time, ‘set it and forget it’ task most frequently done by Cognos administrators. Like everything in Cognos you have a few options for how to proceed. Before we dig into that though, let’s take a look at the info we need regardless of how we’re configuring data servers.

Data server configuration checklist

Identify which type of database you want to connect to
Make sure you have permission to connect to it!
Check to make sure it’s supported by Cognos
Check to see if there are any special considerations for that database
Acquire the relevant JDBC driver and install it in the ‘drivers’ folder of the Cognos install directory
Ask your DBA for the following information:
- URL for connecting to the database
- Database name
- Schema name
- Username for authentication
- Password for authentication

Configuring a data server from scratch

Now that we have our ducks in a row we can build the data server. This is incredibly easy, though you might not know it from reading these instructions. Let me break it down for you:

Click the manage icon then select ‘Data server connections’
Click the plus button in the upper right of the data server connections window
Select your database type from the list that appears
The data server configuration window will automatically open for you to input your connection url from above.
In authentication method, select ‘use the following signon’ and input your username and password
Click ‘Test’ and ensure Cognos can connect to your database
Click on the ‘Schemas’ tab and set the following:
- In ‘Load options’, choose whether or not you want Cognos to retrieve statistical samples from the database. I generally leave this alone. Turning this off will disable certain advanced features of Cognos – like auto joins in data modules
- In ‘Tables’, select which tables you want to expose via the data server. I highly recommend you select only the tables which you need. Allowing Cognos to profile your entire production database during work hours is an extremely bad idea…
Click ‘Save.’ Your data server is ready for use in data modules!

Converting Cognos 10 data sources to Cognos Analytics data servers

While it’s true that a data source configured in Cognos 10 cannot directly feed a data module, you can easily convert a data source to a data server provided the administrator has configured a jdbc connection for the data source. To do so follow these steps:

Click the manage icon then ‘Administration Console’
Click ‘Configuration’ tab and ‘Data Source Connections’
Click the ‘set Properties’ icon for the data source you wish to convert
Check the ‘Connection’ tab
Check the box next to ‘Allow web-based modeling’
Click ‘OK’
Exit the administration console and open ‘Data Servers’ in the ‘Manage’ menu. You should see your data source list as a data server.

Using data servers in Cognos analytics

By following the process outlined above we can easily use data servers to get our data into data modules and then into reports, dashboards or the new exploration feature without ever needing to reference Framework Manager. I hope this satisfies Mr. Schmoe and as always, contact me if you have any questions!

Read on to level up your Cognos skills!

Framework Manager vs Data Modules

November 20, 2019 by Ryan Dolley 22 Comments

I have spent a lot of time showcasing data modules to audiences across the world in-person and in livestreams, helping people understand how and why to use them as part of Cognos Analytics. The most consistent question I receive – by far – is about understanding framework manager vs data modules. There are a lot of outdated opinions and outright misconceptions floating around so let me outline the exact feature differences between framework manager and data modules as of Cognos 11.1.4.

What do data modules and framework manager have in common?

The answer is ‘a lot’ but this wasn’t always the case. In the 11.0 releases data modules were missing many essential framework manager features and didn’t offer compelling reasons to switch. Of course that has changed. As of 11.1.4 framework manager and data modules both:

Produce data models that can be used with all Cognos 11 features
Join dozens or hundreds of tables across multiple databases
Execute cross-grain fact queries (aka the dreaded determinants)
Build simple or complex calculations and filters
Build alias, view, union and join virtual tables
Secure data by groups, roles and users
Create OLAP-like dimensional hierarchies
Offer enterprise governance, auditablity and security

Oftentimes people washed their hands of data modules a couple years ago and are surprised to see virtual tables, cross-grain fact queries and security by groups. These features may exist in both but the implementation in data modules is superior from a usability perspective.

Column dependencies can handle degenerate dimensions, unlike framework manager — Column dependencies go beyond what was possible using determinants in FM

What do data modules offer that framework manager does not?

Again, the answer is ‘a lot’. The 11.1. release takes data modules beyond what is possible in FM with a host of powerful capabilities and quality of life enhancements. The following features are either exclusive to data modules or done infinitely better in data modules.

Natural-language and Ai powered auto-modeling
Automatic join detection
Easy integration of excel data
Ability to easily clean data
Flexible hierarchies that go up, down and across (navigation paths)
Easy measure binning and attribute grouping
Easy extraction of year, month, day, etc… from data data types (split)
Automatic creation of relative time filters (YTD, MTD, PYMTD, etc…)
Automatic creation of relative time measures (YTD actuals, PYTD actuals, etc…)
In-memory materialized views within Cognos Analytics
In-memory query cache
Easy multi-model inheritance for single source of truth
Degenerate dimension aggregation (column dependencies)

Some of these features are absolute game changers for how I craft highly performant, easy to use and self-service friendly data models. Consider ~~the coconut~~ relative time; because this was such a titanic brain buster in framework manager only the most skilled developers could deliver. Now it takes minutes for end users to implement.

Building a dozen relative time filters can be done in as little as five clicks. — It took five clicks to build the relative time filters that take ~1 trillion years in FM

What are data modules missing?

There are still some things data modules lack:

Object level security
DMR capabilities
Parameter maps
Multiple connections for data servers

If I’m being honest, I don’t really recommend you use many of these features for new development in 2019 unless you absolutely have to, particularly DMRs. DMRs are very powerful for those who know MDX but a true maintenance and self-service nightmare in the long run. I cannot count the number of clients who are stranded with critical DMR based reports they cannot understand. In any case, a little bird told me that DMR-like functionality will grace data modules soon.

Going beyond the feature list

Comparing framework manager vs data modules feature for feature, we can see how data modules have few shortcomings and offer huge advantages. While this is a common way for IT folks to think (and I would know, I’m one of them!), I argue that it badly misses the point. By using data modules an IT professional can do weeks of FM work in an afternoon while a self-service user can easily accomplish tasks that will otherwise be done in Power BI. I repeat it often but I’ll say it again – data modules are the key to modernizing your Cognos Analytics environment and delivering content with the speed modern users demand.

What do you recommend?

I’ll parrot Cognos offering manager Jason Tavoularis and say, ‘use data modules unless you can’t.’ And as you can see above, the list of reasons you can’t has become quite short. I start ALL consulting engagements under the assumption that we’ll be building data modules and I’m always happy with the results.

Cognos Analytics Linked Modules

October 29, 2019 by Ryan Dolley 4 Comments

If you’ve been following this blog you know that I love Cognos data modules to death. They are easy to learn, easy to use and full of functionality. Today we’re going to explore using Cognos Analytics Linked Modules to maintain enterprise data governance while enabling self-service. This entry is meant to give a straightforward implementation guide to reinforce concepts outlined in the posts Data Modules as a Single Source of Truth and How to Organize Cognos for Self-Service.

Companies do not have to choose between governance and self-service — Like Jedi vs Sith… can you truly have both?

How and why of linked modules

Using linked modules is a slam dunk way to enable self-service analytics on a foundation of trusted and governed data. When you link two modules you are a creating a relationship in which:

The IT expert can build and maintain a complex module with high quality data that requires significant skill to create
The power user can utilize high quality data as a foundation for analysis but customize and extend at their own pace, not IT’s
The casual user can build dashboards and run reports that meet their needs without waiting for IT

This works so well because the modules of the power user automatically inherit all the complex relationships, transformations and logic from the enterprise module prepared by IT in near real-time without having to do anything. It’s magic.

Step 1: Build an enterprise data module

The enterprise module is typically – but not always – built by IT, more often than not the result of a much longer process of data warehouse design and ETL. Regardless of how its created or what it contains the enterprise module will server as our example though any two modules can be linked together.

Any data module can serve as a source for another data module — The enterprise module serves as the source / parent when linking data modules

Our example uses ‘Great outdoors data module’ from the Cognos samples. This module is located in the data folder. Once built the enterprise module resides in a folder where self-service users can easily access it. I advocate creating a data library folder for this purpose.

Step 2: Use a data module as a source

IT has built and deployed an enterprise module with validated and accurate data, which takes a long time. Now a power user needs to extend that module with some additional context and can’t wait for IT to do it. The solution is simple: ~~use Tableau~~ Build a new data module using the enterprise module as a source!

Adding a data module source is no different from adding any other source — Adding a data module source is extremely easy, fun and profitable

When creating a new data module, users can select other modules as sources in the ‘select sources’ screen. In our example, select ‘Great outdoors data module’ and hit ‘OK’. That’s it. We’ve built a linked module.

Data module sources appear in teal and have a link icon — A linked module has visual cues to let you know that it is indeed a link

The tables above appear in teal rather than blue and have a link icon, indicating that they are actually inherited from another module. At this point any changes made to the tables of ‘Great outdoors data module’ will automatically flow through to this module with absolutely no intervention.

Step 3: Extend the enterprise module

You’ll notice that the teal linked tables do not offer much in the way customization by design. They inherit everything from the parent module. This means users cannot change what comes from the enterprise module but they can extend it with additional logic and data.

Linked tables don't have all the options of normal tables as you're trying to preserve governance. — No options = no opportunity to mess with governed data

Cognos restricts us to creating data groups, navigation paths and editing some properties for product line – a far cry from what is available in the source module. However we still have considerable power to extend the enterprise module. Let’s look at some examples.

Creating calculations

In the example above the calculated field ‘Revenue Variance Amount’ contains the fields ‘Revenue’ and ‘Planned Revenue’ from the sales table. This custom calculation is built entirely using elements inherited from our enterprise module. At many of my customers such a simple request spends weeks or months in the IT backlog but here we accomplish it in minutes while still using centrally modeled and governed data. Yowza!

Adding additional data

Here I join a Cognos data set called ‘Sales Staff Analysis’ to the tables from the enterprise module via two conjoined dimensions. Joining a spreadsheet is just as easy, which is what the vast majority of power users want to do.

These are just two examples of extending an enterprise module using the linked module functionality. Users can do almost everything data modules have to offer while still relying on trusted data, including building table views and aliases, creating drill paths, custom grouping and filter, row level security, etc…

Step 4: Watch the linking magic in action

Before the module can be used it must be saved. Users typically save modules to ‘My content’ but it is critically important that you allow them to save self-service modules to a location where they can easily share with coworkers. Now let’s close this module and test our module link.

Almost all changes made to the source module flow through

Here I create a new calculation in our enterprise module called ‘Revenue Percent Variance’ and click ‘Save’. I then immediately create a dashboard using the self-service module as a data source. Keep in mind that I made absolutely no updates to the self-service module before doing so.

The changes flow through just like magic!

As you can see, ‘Revenue Percent Variance’ is immediately available for use in Dashboards even when they reference the self-service module rather than the enterprise module.

Putting it all together

Allow me to summarize what we just did:

An enterprise module is made available as a data source
Power users extend the enterprise modules with their own logic and data in self-service modules
Content is built using both the enterprise and self-service modules as a data source
Updates to the enterprise module flow through to all content regardless of which data source was used

Single source of truth meets self-service

Nobody but Cognos offers this combination of data governance and self-service. It’s a game changer for organizations who truly embrace it. Book some time with me to explore these ideas further (there’s a link on the screen) and be sure to check out my live impressions of the recent 11.1.4 release.