Cognos Analytics

Relationship operators in module relationships

By IAN HENDERSON posted Mon July 27, 2020 08:23 AM

  

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.

Operator

Operator name

Function

SQL expression example

=

Equals

Values in column A are equal to values in column B.

Employee.Employee_key = SLS_SALES_FACT.EMPLOYEE_KEY

<> 

Not equals

Values in column A are different (not equal) than values in column B.

Employee.PROV_STATE <> Employee_record_dates.Prov_State

Less than

Values in column A are less than values in column B.

Employee.Record_start_date_key < SLS_SALES_FACT.ORDER_DAY_KEY

Greater than

Values in column A are greater than values in column B.

Employee.Record_end_date_key > SLS_SALES_FACT.ORDER_DAY_KEY

<=

Less than and equals

Values in column A are less than or equal to values in column B.

Employee.Record_start_date_key <= SLS_SALES_FACT.ORDER_DAY_KEY

 

>=

Greater than and equals

Values in column A are greater than or equal to values in column B.

Employee.Record_end_date_key >= SLS_SALES_FACT.ORDER_DAY_KEY

=N

Null safe equal comparison

Values in column A are equal to values in column B or null values in column A are equal to null values in column B.

Employee.PROV_STATE = Employee_record_dates.Prov_State

OR

Employee.PROV_STATE

IS NULL AND

Employee_record_dates.Prov_State
IS NULL

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.

Relationship editor with 3 different types of operators

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.

 sample expression to transform a date into an integer to be used as a key

 


#LearnCognosAnalytics
#ca-modeller
#home
1 comment
26 views

Permalink

Comments

Sun August 02, 2020 08:55 AM

There are several helper expressions already built into CA such as _DATE_TO_INT

While SQL and database vendors have provided DATE and TIMESTAMP types for many years, some applications may have chosen to store those values in a numeric type. This may stem from the language their application was first written in etc. Consequentially, you may encounter a data source which presents an INTEGER type which holds the YEAR, MONTH and DAY value. Transposing a DATE (such as provided by a user) into an INTEGER can be achieved by using EXTRACT,  shifting left by powers of 10 and adding the values together. Now some persons may have a database which supports a type conversion function (i.e CAST) which will accept source and target types not supported by the SQL standard which they resort to. Others may even attempt very inefficient nested functions which convert to strings, extract character offsets, convert back to numbers etc. Now this task can be expressed using some simple helper function.

  • _DATE_TO_INT