What is a Determinant?

Resolving The Problem

What is a Determinant and when do I need to use it?

Determinants are a feature of Cognos 8 designed to provide control over granularity in a similar, but not identical means to Dimension Information in IBM Cognos.

Determinants are most closely related to the concept of keys and indexes in the database. There is no concept of hierarchy in determinants, although the order in which they are specified does govern the order in which they are evaluated. A determinant can define the set of database columns (query items) that uniquely identify a set of data or it can identify a set of columns that identify a non-unique set within the data.

What is the difference between Dimension Information and Determinants?

Dimension information was a quasi dimensional concept introduced in IBM Cognos ReportNet, primarily as a means of controlling granularity. With the introduction of true dimensional capabilities in Cognos it became necessary to draw a clear line between capabilities and concepts native to relational database and those that belong in the realm of OLAP. The end result of this is determinants for query subjects and hierarchies for dimensions.

When do I need to use Determinants?

There are a very limited set of cases where you will need to use determinants.

• Joins at multiple levels of granularity on a single query subject
  A classic example of this is the Time dimension in Go Data Warehouse. There are joins to the Time dimension on the day key and on the month key. This is mainly an issue when doing multi-fact querying and you need to prevent double counting.
  
• BLOB data types in the query subject
  Querying blobs requires additional key/index type information. If this information is not present in the data source then you can add it using determinants.

Example 1: Time Dimension (Unique foreign keys)

It is possible to define three determinants for this data set, two non-unique determinants (Year and Month) and one unique determinant (Day). The concept is similar but not identical to the concept of levels.

Determinants:

Interpretation:

Day key is the unique key of the table therefore you can associate all the columns in the table to this key. Because it is a unique key, we check the Uniquely Identified box and do not check the Group By. Group By is a property that we do not need when data is unique.

Month key is also a key but is not unique, so the Uniquely Identified box is not checked for this determinant. However, the Month Key is all that is needed to identify a month in the data. If you wanted to query month from this time table you would write a query that used select distinct syntax and grouped by Month key since the values are repeated, this is why we check the Group By box.

Similar logic applied to the Year determinant.

There is no concept of a hierarchy in determinants but there is an order of evaluation. What that means to you is that when you write a query that uses a column from the table above the query engine will look for reference to that column one determinant at a time and will stop when it finds it:

Evaluation example:

Write a query that uses Month Name. Month Name is an attribute of the Month determinant and the Day determinant. Since the Month determinant is specified earlier in the list than the Day determinant it will be selected. This means that you will likely see a select distinct in the query and in the group by clause you will see Month Key.

Example 2:Time Dimension (Non-unique foreign keys)

It is possible to define three determinants for this data set, two non-unique determinants (Year and Month) and one unique determinant (Day). The concept is similar but not identical to the concept of levels.

Determinants:

Interpretation:

Differences from Example 1:

The Month Key is not enough to identify a particular Month in the data. If you wanted to query month from this time table you would write a query that used select distinct syntax and grouped on Year Key and Month Key.