Question & Answer
Question
This Technote will explain what determinants are and how they are used in Framework Manager
Answer
Determinants are a feature designed to provide control over granularity in a similar, but not identical means to Dimension Information in IBM Cognos Analytics.
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.
Year Key Month Key Month Name Day Key Day Name 2005 200501 Jan 05 20050101 Sunday, Jan 1, 2005 2005 200502 Jan 05 20050102 Monday, Jan 2, 2005 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:Name Key Attributes Uniquely Identified Group By Year Year Key None No Yes Month Month Key Month Name No Yes Day Day Key Day Name Month Key Month Name Year Key Yes No
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.
Evaluation example:
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: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)Year Key Month Key Month Name Day Key Day Name 2005 01 January 20050101 Sunday, Jan 1, 2005 2005 02 January 20050102 Monday, Jan 2, 2005 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:Name Key Attributes Uniquely Identified Group By Year Year Key None No Yes Month Year Key, Month Key Month Name No Yes Day Day Key Day Name Month Key Month Name Year Key Yes No
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.
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg22000144