Create table relationship (PK/FK) on Synapse dedicated SQL pool tables

Question

the BI tool (Cognos) connects directly to the Synapse dedicated pool tables and I need to show table relationship for the BI application to find them.

I understand the table constraints are not being enforced, but my issue is that FK cannot be created on dedicated SQL pool tables ([https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-table-constraints)

how can I simulate these relationships?

Looking forward for your input and if any expertise with this.

Many thanks,

Answer 1

Hi HSever,

Thanks for using Microsoft Q&A forum and posting your query.

As Synapse dedicate SQL pool doesn't support Foreign Key constraints, your BI tool (Cognos) cannot determine the relationships automatically. In such case, you will have to manually create relationship in your BI (Cognos) tables/datasets after importing the Synapse tables.

Here is a SO thread where a similar topic has been discussed: Relationships in Azure synapse (DWH)

[Please note my knowledge on Cognos is very minimal, and if you need more assistance regarding the relationships in cognos, I recommend reaching out in Cognos community forum]

Hope this info helps.

Answer 2

Hi @KranthiPakala-MSFT yes thank you. I was aware of adding relationship in model (worked on SSAS in the past). but I thought that Synapse would have tables/keys relationship.