Relationship expressions
The release of Cognos Analytics (CA) 12.1.1 sees the implementation of relationship expressions, freeing the modeller from the confines of matching column pairs for the relationships between tables in his model.
For example, I have a lookup table with records for each product for each language.
Up until now, I have been modelling this by putting a filter on the lookup table’s language column. The filter gets the value of the runLocale session parameter and filters the table to only return those records where the language column value matches the run locale value. I then have a 1.1 relationship between the product lookup table to my products table.
Now, with the ability to write relationship expressions, I have the flexibility to put the filter into the relationship expression. The relationship expression below matches the values of the product number columns of the product dimension table and the product lookup table and filters the latter to only use the records where the language column values match the runLocale value. The macro takes the runLocale session parameter value, trims off all but the first two characters, converts them to upper case characters, and then, encloses them in a single quote. That value (in my case ‘EN’) is then put into the relationship expression.
(SLS_PRODUCT_LOOKUP.PRODUCT_NUMBER = SLS_PRODUCT_DIM.PRODUCT_NUMBER)
And
SLS_PRODUCT_LOOKUP.PRODUCT_LANGUAGE =
#sq(toupper(substr($runLocale,0,2))) #
A variant of this scenario would be if you were working with a reference or shortcut, where you can not filter the table. You could write an expression in the relationship.
You, no doubt, have other cases where you would want to write an expression defining the relationship between tables rather than matching column pairs.
A relationship expression can reference third tables, not just objects from the tables being related.
The relationship expression must resolve to a Boolean.
Workflow
When you edit a relationship, you will now see a new button on the bottom of the relationship editor, labeled use expression.
After you have created an expression-based relationship you will see, in addition to the display of the relationship expression, 3 buttons. They are match columns, which brings you to the UI to match column pairs to form a relationship, edit expression, and reset expression.
The latter will allow you to reset the expression to its previous state in the session of the editor. Once you create an expression you can edit it. If you don’t like the edit, you can press the reset expression button, and it will revert to the original expression.
You can validate the relationship expression in the expression editor. You have three panes in the editor. One contains the tables of the relationship. The second contains all the objects of the data module. The third contains functions which you can use.
You can toggle between matching columns and using an expression. If you toggle between match columns and use expression and then back again the operators of the matched columns are used. If you edit the operator and then toggle back to match columns the original operator is used. You can then toggle back to using an expression as the expression you wrote previously is preserved. Once you press OK to save and close the relationship the option you had last will be written into the model.
Previewing the results of the relationship using matched columns will show the matched columns with coloured borders in the preview. Preview using an expression will not.
This is the preview of a relationship which uses matched columns. In this case, day key and ship day key are the matched columns of the relationship. They are shown on the right of the editor, highlighted with purple and green.
This is the preview of a relationship expression.
Usage
You need to understand why you would want to use a relationship expression rather than matching column pairs. I am insufficiently knowledgeable to give general guidance on the matter. One scenario could be if you are working with shortcuts and thus cannot define filters on the shortcuts, which requires filtering via the relationship expression.