Cognos Archives | IBM Blueview

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.

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

SirM is right.

What is an Alias in Cognos

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

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

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

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

Alias in Data Modules

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

Copy Tables

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

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

View Tables

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

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

Linked Tables

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

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

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

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

So What’s the Solution?

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

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

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

How to Proceed

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

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

What are Cognos Data Modules?