Cognos Analytics with Watson

 View Only

Aliases and shortcuts

By IAN HENDERSON posted 29 days ago

  

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.

targets



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.  

FM

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.

Roleplaying diagram

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.

shortcuts in action



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.

target col dep.png

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

alias col dep


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.

Object

Convert to types

Comments

Shortcut

Alias, view, table

If the shortcut target is a multi-table view, then converting to a table will be disallowed.

Alias

Shortcut, view, table

If the alias target is a multi-table view, then converting to a table will be disallowed.

View

Shortcut, alias

If the view is a multi-table view, then converting will be disallowed.

Reference

Shortcut, alias, table

References to unions, intersects, and excepts are not allowed to be converted

 

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.

Action

Result

Comment

Shortcut to Alias

Converts the shortcut to an alias that references the same table that the shortcut references.

 

 

Alias to Shortcut

Converts the alias to a shortcut that references the same table that the alias references.
The embedded filters of the alias will be removed.

 

Shortcut to a Table

Converts the shortcut to the type of table that the shortcut references.  If the shortcut is pointing to a view which is based on multiple tables then a view will be created during the conversion rather than a table.
Calculations in the shortcut are maintained in the new table.
Embedded filters in the table referenced by the shortcut are not applied in the new table.

This is because the table (or view) is a distinct object from the original target table.

Alias to a Table

Converts the alias to the type of table that the alias references. If the shortcut is pointing to a view which is based on multiple tables then a view will be created during the conversion rather than a table.
Calculations and embedded filters in the alias are maintained in the new table.
Embedded filters in the table referenced by the alias are not applied in the new table.

This is because the table (or view) is a distinct object from the original target table.

Shortcut to View

Converts the shortcut to a view that references the same table that the shortcut references.
Calculations in the shortcut are maintained in the view.

 

 

Alias to View

 

Converts the alias to a view that references the same table that the alias references.
Calculations and embedded filters in the alias are maintained in the view.

 

View to Alias

Converts the view to an alias that references the same table that the view references. If the view consists of multiple tables, the conversion action will be disallowed.
Calculations and embedded filters in the view are maintained in the alias.
Embedded filters in the table referenced by the view are applied in the alias. You will be able to edit those filters.
Columns excluded from the view in the edit table view dialog are restored in the alias.
Property changes in the view are not maintained in the alias.
query subject filters will be removed.

 

 

View to Shortcut

 

Converts the view to a shortcut that references the same table that the view references. If the view consists of multiple tables, the conversion action will be disallowed.

Calculations in the view are maintained in the shortcut.
Embedded filters in the table referenced by the view are applied in the shortcut. You will be able to edit those filters.

Columns excluded from the view in the edit table view are restored in the shortcut.
Embedded filters will be removed.
Property changes in the view are not maintained in the shortcut.
query subject filters will be removed.

 

Linked table (References) to Alias

Converts the linked table to an alias that references the same table that the linked table references in the source. That is, the alias will point to the same target in the source module which the reference pointed to. 

The behaviour of the alias will be the same as any other alias even though the target is in another module.

 

Linked table (References) to Shortcut

Converts the linked table to a shortcut that references the same table that the linked table references in the source. That is, the shortcut will point to the same target in the source module which the reference pointed to.

The behaviour of the shortcut will be the same as any other alias even though the target is in another module.

 

Linked table (reference) to Table

Breaks the link between the table and its source table.

 

 




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.

icons


Relinking

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

A relinking success toast will appear once validation is complete.

relinking dialog

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.

 

 

 

 

 

 

 

 

 

 

 

 



1 comment
19 views

Permalink

Comments

19 days ago

FM's star schema groupings also help remove join ambiguity, they aren't just about putting shortcuts into a folder.

For example if I used the two dimensions, Employee and Time, how should I join those tables together? FM used star schemas to know if you wanted them join by the Sales table, or the returns table.

Can you update the blog to say if your pseudo star schemas do this.