dalya zwaihed 0 Reputation points

Hi, Does Azure Purview show table relationships(references) for captured SQL Server tables? The related tab shows all tables in the schema!

Even though I've specified all foreign key relationships in SSMS, the ingested relationships in Purview are zero!

Microsoft Purview
Microsoft Purview
A Microsoft data governance service that helps manage and govern on-premises, multicloud, and software-as-a-service data. Previously known as Azure Purview.
997 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Anushka 320 Reputation points

    Hello friend, I hope you're doing good.

    Azure Purview provides data lineage and relationship discovery capabilities, but it may not automatically capture all table relationships from SQL Server tables. Here are a few points to consider:

    1. Metadata Collection: Azure Purview relies on metadata collection to understand and represent data relationships. If the foreign key relationships are not reflected in the metadata captured by Azure Purview, it won't be able to display them accurately.
    2. Configuration: Ensure that Azure Purview is configured correctly to capture metadata from your SQL Server tables. This involves setting up data sources, scanners, and classifiers appropriately to extract metadata, including table relationships.
    3. Data Lineage and Relationship Discovery: While Azure Purview can show table relationships based on metadata, it might not always capture complex relationships automatically, especially if they involve dynamic SQL queries or are defined in a way that's not easily interpretable by the metadata scanner.
    4. Manual Configuration: In some cases, you may need to manually configure or supplement the metadata captured by Azure Purview. This can involve defining custom classifiers or annotations to help Azure Purview understand the relationships better.
    5. Troubleshooting: If Azure Purview is not capturing the expected relationships, you may need to troubleshoot by checking the configuration settings, reviewing the metadata captured, and ensuring that foreign key relationships are correctly defined in the SQL Server database.

    In summary, while Azure Purview offers powerful capabilities for data discovery and lineage, it may require configuration and manual intervention to accurately capture and represent table relationships, especially in complex database environments. I hope this helps. Have a good day!!

  2. PRADEEPCHEEKATLA-MSFT 82,271 Reputation points Microsoft Employee

    @dalya zwaihed - Thanks for the question and using MS Q&A platform.

    Based on the information you provided, it seems that Purview is capturing all metadata related to column names and their data types, but not the foreign key relationships among these columns, even though they are specified in SSMS.

    It is possible that Purview does not support capturing foreign key relationships specified in SSMS. However, without more information about your specific configuration and the metadata extraction process, it is difficult to say for sure.

    I would recommend checking the configuration of the SQL Server instance and the metadata extraction process to ensure that the foreign key relationships are correctly defined and accessible to the user account used for metadata extraction. You may also want to check the logs for any errors or warnings related to the metadata extraction process.

    If you are still unable to capture the foreign key relationships in Purview, you open a support ticket for further assistance. I hope this helps! Let me know if you have any other questions.

    Hope this helps. Do let us know if you any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

    0 comments No comments