Fact and dimension relationship not allowed because a join field is greyed out[Linux]
Introduction:
When building a data model, you try to connect a field in a fact table to a field in a dimension table, but the field you want to join on appears greyed out and Sisense does not let you create the relationship. This most often happens when the two columns you are trying to join have different data types.
Step-by-Step Guide:
When you try to connect a key in a fact table to a key in a dimension table and the target field is greyed out, Sisense could not create the relationship because the two columns have different data types. Sisense only allows a relationship between columns whose data types match. The steps below align the types so the join can be created.
Step 1: Identify the two key columns.
Open the data model and locate the field in the fact table and the field in the dimension table that you want to join (for example, Fact.AssessmentId and the dimension's AssessmentId).
Step 2: Compare the data types.
Check the data type of each key column. A mismatch - for example, one column being text/string and the other numeric, or two numeric types of different width — is what causes the field to be greyed out.
Step 3: Standardize the data type on both sides.
Change the type so both key columns match. In the reported case, casting the key to BigInt resolved the issue. You can do this with a custom column expression, or correct the type at the data source.
SQL (custom column expression):
Step 4: Fix custom tables at the query level.
This also happens with custom tables when two columns produced by the table's query have different data types. Because the types come from the query, open the custom table, review its query, find the two columns used in the join, and cast both values to the same type so they align.
SQL (custom table query):
Step 5: Rebuild and create the relationship.
Rebuild the affected table(s) so the type change takes effect, then return to the model editor and create the relationship. The previously greyed-out field should now be selectable.
Conclusion:
Key takeaways:
A greyed-out join field almost always means the two key columns have mismatched data types.
Align the data types on both sides of the key (for example, cast both to BigInt), then rebuild the table.
For custom tables, fix the mismatch inside the table's query by casting both columns to the same type.
If types already match and the field is still greyed out, confirm both columns exist, are not hidden, and that a valid join path is detectable.
References/Related Content
Data Model Building Practices for Business Intelligence - Sisense product documentation.
