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

HSever 136 Reputation points
2023-01-24T21:20:45.7433333+00:00

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,

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,141 questions
{count} votes

Accepted answer
  1. KranthiPakala-MSFT 46,407 Reputation points Microsoft Employee
    2023-01-30T09:00:27.2333333+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. HSever 136 Reputation points
    2023-02-02T02:45:04.02+00:00

    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.

    0 comments No comments