Relationship operators

Contents

Summary.

Upgrade.

Usage example.

Null safe equal comparison operator ( =N ) usage.

Example of an expression to transform a date into an integer value.

Summary

In Cognos Analytics 11.1.4, the Cognos Analytics modelling tool added support for relationship operators.

Prior to 11.1.4, the only way to relate a query subject to another query subject was using an implicit equals operator.

The relationship operators are:  =, < , > , <> , <= , >= , and =N.

These operators allow you to have a greater choice about how keys, and their parent query subjects, are related to each other.

Amoung other things, they allow you to define relationships using windows or buckets, segregating data. The most intuitive type would be a window of dates, which will be illustrated below.

Upgrade

Existing relationships will not be altered unless you edit them.

A relationship which is not edited in any way will continue to have implicit equals operators for each key pair in the relationship.

A relationship which has an existing key pair and is augmented with another pair will be upgraded to have explicit relationship operators for each of the key pairs.

New relationships will have explicit operators.

When a relationship which was defined in a release prior to 11.1.4 is edited, the default relationship operator which will be shown for the related columns will be the equals operator. This is because this is the analogue to the implicit equals operator.

Usage example

In this example, I have a dimension of Employees and have created a relationship to a fact table which incorporates a time window. The relationship to the fact table, in this case Sales, uses 3 keys. The keys are the employee key, the employee record start date, and the record end date.

This Employee dimension is a type 2 slowly changing dimension and, consequently, has the historic data representing each employee preserved. For example, employees can change position or get promotions. Each time this happens, a new record is created in the Employee table and the old record which represents the previous current record is modified to add a value to the record end date field.

In order to generate values for the record start date and end date keys, calculations to transform the values of those dates to match the structure of the Sales fact table’s order day key values are needed. Your data may not require such actions.

The expressions take the elements of the date and forms it into a value of the same form as the order day key of the sales fact table. I have included a picture of the expression used to create the relationship key for record end date, which is slightly more complex as it needs to handle for nulls but should be simple enough to illustrate the point.

An illustration of this relationship is shown in the picture below. The relationship editor for the relationship between employee and sales is open. The keys used in the relationship are shown. The relationship operators which define the relationship between each key is also shown.

One of the keys in the relationship is Employee key. It matches to the Sales fact’s Employee key. The relationship uses an Equals operator. This means that any value in Employee key must match to a value in the Sales fact table. If a key value does not have a matching value in the other table then those records will be not returned in a query, unless an outer join is chosen. A discussion of that is outside the scope of this paper.

Another key is based on the employee table’s Record start date. This key matches to Sales fact’s order day key. The relationship uses the Less than or equals operator.

The third relationship key is Record end date. It also matches to Sales fact’s order day key. The relationship uses the Greater than or equals operator.

This means we want to know, for any Employee, what sales were generated during the period bounded by the start of a record and the end of the record.

The Cognos SQL for the join will look something like the following.

Employee.EMPLOYEE_KEY = SLS_SALES_FACT.EMPLOYEE_KEY

AND

Employee.Record_start_date_key <= SLS_SALES_FACT.ORDER_DAY_KEY

AND

Employee.Record_end_date_key >= SLS_SALES_FACT.ORDER_DAY_KEY

I had some difficulty wrapping my head around that until I used the relationship editor’s switch left and right tables button to switch the positions of the tables.

The expression becomes this:

SLS_SALES_FACT.EMPLOYEE_KEY = Employee.EMPLOYEE_KEY

AND
SLS_SALES_FACT.ORDER_DAY_KEY >= Employee.Record_start_date_key
AND

SLS_SALES_FACT.ORDER_DAY_KEY <= Employee.Record_end_date_key

This formulation, hopefully, makes the time window nature of the relationship expression clearer.

Null safe equal comparison operator ( =N ) usage

The null safe equal comparison operator will match nulls on both sides of the relationship, which can produce unwanted results, as every null in one table will be equated with every null in the other.

It creates a relationship expression in this format.

QS1.Qi1 = QS2.Qi1

OR

QS1.Qi1 IS NULL

AND

QS2.Qi1 IS NULL

Example of an expression to transform a date into an integer value

This is an example of an expression which casts a datetime data type column to be an integer for a key. It is included mostly so that you don’t need to reinvent the wheel but have a pattern to guide you for this and other similar expressions.

As you can see on the bottom in the results, on the right is the record end date column’s values, including one record where the value is null. One the left is the results of the expression.