Cognos Analytics

Aliases and shortcuts

Shortcuts and aliases are new objects introduced in Cognos Analytics (CA) 11.2.3.  They are new types of objects in the class of table.

Shortcuts and aliases are conceptually similar to Framework Manager (FM) shortcuts, which have alias and reference property values.

Both objects point to a target query subject and derive their primary metadata from it.  

You can create aliases and shortcuts for tables, views, and SQL tables.

You can extend both aliases and shortcuts by adding calculations, data groups, and selectable (i.e. optional) query subject filters, also known as stand by filters, to them. You can also create embedded filters in an alias.  An attempt will be made to put the calculations and data groups into the correct column dependency group.  You can also create folders.  Only those calculations, data groups, and filters which you created in the alias or shortcut can exist in the folder.

Aliases and shortcuts automatically inherit changes made to their target.

Aliases primarily exist to perform role-playing. They have their own SQL, independent of their targets. You can use shortcuts to expose only those objects you want to have in a reporting application, similar to the function of a star schema grouping in FM.

You can convert an alias or shortcut to another type of object. You can also convert references, which are objects from one data module contained in another data module, into other types of objects, which extends the existing break link functionality, which would convert a reference to a table.

You can relink a shortcut or alias to point to a different target.

The properties of an alias or shortcut are read-only except for label, identifier, comments, screen tip, and hide from users. The properties of the query items of the alias and shortcuts are all read-only.

Aliases and shortcuts make working with multiple, linked modules cleaner and more modular. Techniques for working with multiple modules will be discussed here:
Working with aliases and shortcuts in multiple linked modules (ibm.com)

https://community.ibm.com/community/user/businessanalytics/blogs/ian-henderson1/2022/09/04/working-with-aliases-and-shortcuts-in-multiple-lin

Terminology

A referenced module is the module which contains the objects which are used in another module.  That module is called the referencing module, as it contains objects which are references to objects in another module.  Other terms for a referenced module are target module or source module.  Another term for referencing module is destination module.

I tend to use the terminology in an undisciplined manner but consistently.

In FM the object which is referenced by the shortcut is called a target.  We use the same terminology here.

A target is the query subject which was used as the basis for the shortcut or alias.  In the custom table diagram, it is the table to the left of alias or shortcut.  Any of tables, views, including aliases and shortcuts, and SQL tables can be used as the target.

Prior art

In FM you could create shortcuts to multiple objects, including query subjects, folders, and namespaces.  The treat as property of a shortcut would define whether it was an alias or a shortcut, which was called a reference (similar to the Data Module reference object).

The primary limitation of a FM shortcut is that you cannot augment or extend the shortcut.  In a data module, as mentioned, you can augment a shortcut or alias.  

Aliases

An alias is a separate instance of the target query subject. The alias exists to better enable role-playing, with unambiguous join paths between objects and clear roles for any object, which is one of the Cognos proven practices and is derived from Kimball principles.

The SQL generated for it will be distinct from the SQL generated for the target. Because you can modify the identifier, you can better define how the alias will be identified in the SQL.

Role-playing has been possible since 11.1.1, using copies of tables and views, but, because the copies do not synchronize with their source object, modifications would need to be done for each instance. Because aliases, as mentioned, automatically inherit modifications from their target, those changes need only be made in one place.

In addition to calculations, data groups, and selectable query subject filters, you can create embedded filters in an alias.  You cannot do so for a shortcut.

Shortcuts

A shortcut is a pointer to its target. The SQL which is generated for the shortcut is that of the target. The shortcut can be used in queries with other objects. The shortcut unwinds to its target at query time and any relationship the target has to other objects will be used.  

You can use collections of shortcuts in folders similar to the star schema grouping functionality in FM.

Shortcuts which have relationships to other tables will use those relationships in queries, in precedence to any relationship between the shortcut’s target and those tables.  This allows you so override the existing relationship.

In most cases it is probably the case that the appropriate object to use would be an alias rather than a shortcut. You can convert the shortcut to an alias by using the convert menu item.

The only class of object which has a shortcut are tables, which consists of tables, views, unions, excepts, and intersects.

Role-playing example

Here are some examples of role-playing with aliases.

At the top of the module tree, you will see two aliases, Time order date and Time ship date. They are aliases of Time, which is directly below them in the tree. You can see them in the diagram near the top centre.

Time is a view, comprised of the tables in the Time source tables folder, which is collapsed as it would clutter up things if it was expanded.

You will notice that Time does not have any relationships to other query subjects. Time order date and Time ship date have relationships to fact tables, which have been collected at the centre and piled on top of each other to make the diagram clearer.

Time order date has a relationship to Sales using the order date key. Time ship date has a relationship to sales with the ship date key. This allows you to have role-playing dimensions.

We would use aliases in a situation like this because, as independent objects, their SQL can be identified by the identifier of the alias and tracing through queries can be aided by that. Copies of a table could be used and has been a viable technique for role-playing since the introduction of copies in 11.1.x, but aliases are better as they ease the maintenance requirements for role-playing because they inherit from their source object.

Another way to role-play is within dimensions.

You can see this on the top left-hand corner of the diagram.

On the left are the source tables of the employee dimension.  They are Emp employee dim, Emp termination lookup, Emp position lookup, go branch dim, and go region dim_employee. Go region dim_employee has a one to many relationship to go branch dim and go branch dim has a one to many relationship to Emp employee dim. Emp termination lookup and Emp position lookup are lookup tables.

Go region dim_employee is an alias of go region dim, which is positioned just above the two aliases in the top left of the diagram.

In go region dim, are attributes such as country and region.

To the right of employee are the tables of the Retailers dimension. They are sls rtl dim and go region dim_retailers. Go region dim_retailers has a one to many relationship to sls rtl dim.

Go region dim_retailers is also an alias of go region dim.

This role-playing allows you to have attributes such as country in both your Employee and Retailers dimensions. As such, any query which uses country will be generated in a consistent, unambiguous, and predictable manner. If you use country from retailers, the query will be generated so that the joins will be generated using the objects from the Retailers dimension. 

You will notice that the Employee and Retailer dimension query subjects do not have relationships to any facts. You will observe, near the bottom right of the diagram, two query subjects labeled Employee and Retailers. Because neither dimension consists of either a single dimension table or a perfectly normalized snowflake, I needed to incorporate them into a view, where I defined column dependency to normalize the metadata. These views have relationships to the facts.   (A dimension table would still need column dependency defined if there are multiple fact grains involved)

Star schema groupings

If you want to have a star schema grouping, consisting of a fact table and its related dimensions you would need to create a folder and copy or move the shortcuts for the query subjects into the folder.

This enables you to expose folders containing only those dimensions for a particular fact.  This enables consumers of the module to know what dimensions have scope to that fact.  If they want to have a report with multiple fact tables in it, they can compare the dimensions in each folder to identify what dimensions are conformed to what facts. This will enable them to know if the report will work or not.

Unlike FM, there is no star schema grouping wizard, and you need to individually select each query subject for which you want to create a shortcut and create a shortcut. You would need to create folders for each star schema grouping you want and put the relevant shortcuts into each of them.

Column dependency

An alias or shortcut inherits, as read-only, the target’s column dependency.

New calculations in the alias or shortcut will be automatically added to the column dependency diagram as read only attributes at the appropriate level of the lowest level dependency group which has a query item in the expression. All the properties of the calculation are editable. Editing the calculation will move the calculation to the next appropriate dependency group. If it cannot be positioned, an attempt will be made to put it into the leaf level column dependency group or to put it into an independent column dependency group.

Here is an illustration of the column dependency diagram for a target view.

Here is an illustration of the column dependency diagram for an alias of that view.

In this illustration, my imaginatively-named calculation Calculation added in the alias, with the expression Current_Year ||’ ‘||Month_en, is added automatically to the month column dependency group.  This is because that’s the lowest-level group containing a reference in the expression.

Converting

It is possible that you could create something which you later on decide that you wished it was another type of object.  For example, you could create a shortcut but, later on, you realize that you really wanted to have an alias. 

Converting means that not all decisions are irrevocable, and you have the power to change an object into another, more appropriate object type.

Converting references to aliases or shortcuts is an important part of using aliases and shortcuts in linked modules.

If an alias or shortcut points to a multi-table view and you choose to convert to a table, a view will be created instead.

Here is a table of what happens for a conversion.

A shortcut will use the SQL of its target and the query information will show that SQL.  If you convert it to an alias the generated SQL will use the identifier of the shortcut.  For example, assume that you have a shortcut with an identifier of Time_order_date_sales, which is used in conjunction with other shortcuts representing the sales fact table and its related dimensions.  Assume that the shortcut has a target with an identifier of Time_order_date.   When you view query information for the shortcut it will have Time_order_date in the SQL.  If you convert the shortcut to an alias (or a table or view) the query information will show Time_order_date_sales in the SQL.

Aliases versus shortcuts

If you anticipate modifications in a referenced module, such as additional query subjects and relationships, using shortcuts rather than references would be advisable as the shortcuts would pick up the relationships.

If you anticipate needing to have relationships, then use aliases rather than shortcuts.

Since aliases are designed for role-playing they, rather than shortcuts, would be the appropriate objects to use for that use.

Identifier clash

It is possible to create a calculation in an alias or shortcut which has the same identifier as a column in the target of the alias or shortcut. If not corrected, the expression of the target column will be used in queries. A validation message is displayed for such cases.

This is easily dealt with, by either changing the identifier of the calculation or the target column to be unique.

In general, it helps to understand that there are two properties, label and identifier. The former is what is displayed for an object in the UI. Any number of things can have the same label. The latter must be unique. Identifiers are used in SQL and in object references in things such as calculations.

Icons

The icon representation of aliases and shortcuts in the reporting applications is the same as ordinary views and tables, as their distinction does not matter to report authors and consumers.

Relinking

You can relink an alias or shortcut to use a different target.

A relinking success toast will appear once validation is complete.

Limitations

There is no star schema grouping creation wizard, unlike FM.

If you want to create a presentation layer analogue you would need to manually create shortcuts for each object you want to have, create folders for them, and make copies for any of them which need to appear in more than one star schema grouping.

Conversion can only be performed on single tables at a time.

You can manually remove a calculation from a column dependency group, but you can’t add it back except via the undo action. You should not be able to remove the calculation from the column dependency group.

A referenced query subject with relationships to other objects in the referenced module will continue to display those relationships in the referencing module for the session of the module if it is converted to a shortcut.  The relationships will not appear in subsequent sessions.

The Exploration diagram is not available for shortcut and alias.

Converting adds a duplicate folder to the query subject.

Adding references temporarily changes the colour of the object in the source tree.

You cannot convert a reference to a union, except or intersect.

Shortcuts included in navigation paths do not always get resolved to their targets.