As the title says, I'm wondering if Cognos version 12+ supports binary fields as key fields between tables?
For example, an MD5 hash is used as the primary key to join the fact and dimension tables.
What about uniqueidentifier types instead of binary?
Hi
Uniqueidentifier in Cognos is specific to Framework Manager and is used to tell Cognos that the field will be used in joins and in conjuction with Determinants, Cognos will generate the appropriate SQL accordingly. Uniqueidentifier ca work with Numeric, Character etc. but that being said MD5 Hash may or may not work because it is considered unsafe and broken. MD5 also cannot be entirely binary because it can contain alphabets too during it's generation.
Uniqueidentifier in databases refers to a varchar type of field (GUID - Globally Unique Identifier) which is globally unique across the database, not across the table. While this is good for data integrity but it's really bad for the performance of the query because of it's size.
Depends on where you want to apply this. Outside of modelling in FM, you can definitely use the MD5 field in a hand written SQL to join the Fact and Dimension tables. Hand written SQLs do not face limitations of the Cognos versions