Alias Shortcuts in Cognos Data Module

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

SirM is right.

What is an Alias in Cognos

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

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

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

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

Alias in Data Modules

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

Copy Tables

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

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

View Tables

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

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

Linked Tables

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

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

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

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

So What’s the Solution?

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

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

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

How to Proceed

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

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

---

Level up your Cognos skills with these helpful articles!

• 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