Azure Synapse Link for Dataverse Has Fewer Fields than Export To Data Lake

Steven Cardella 0 Reputation points
2024-07-17T21:53:12.0466667+00:00

I'm migrating Export to Data Lake to Azure Synapse Link for Dataverse. I know that in some occasions, Synapse Link does not replicate columns if it's sourced from a different table. I'm having an issue with missing columns from DirPartyTable, particularly "DataArea". There's a similarly named "DataAreaId", but it's coming across as all NULL values. I don't have direct access to the F&O data, but I'm pretty sure this is standard stuff. I just need the tables I need to pull to get the dataarea information for DirPartyTable. What's interesting is that DataAreaId is coming is perfectly on other tables. I can't seem to find the view definition that would point out which additional tables would supply the information.

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.
5,014 questions
Microsoft Dataverse Training
Microsoft Dataverse Training
Microsoft Dataverse: A Microsoft service that enables secure storage and management of data used by business apps. Previously known as Common Data Service.Training: Instruction to develop new skills.
39 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Darin Searcy 10 Reputation points
    2024-08-23T13:01:57.44+00:00

    It appears that Azure Synapse Link is fundamentally different than Export to Data Lake. If Microsoft is going to deprecate Export to Data Lake and replace it with Azure Synapse Link, I think they should function more similarly. We rely on the data lake for many processes like EDI, reporting, and alerts. Rebuilding everything we have in the lake using Azure Synapse Link is going to be a monumental effort since literally hundreds (if not thousands) of columns are missing. The Dirpartytable alone is missing around 140 columns. Everything we built in the lake is built on the axDB schema. Export to Data Lake mirrors that physical schema.

    Howie Fu's approach is doable but will require a huge amount of development to replicate the hundreds of tables we currently rely on in the Lake. My frustration is likely due to my inexperience with the AOT since my experience is more DBA than x++. Is there any guidance available to help determine the derived tables that will be required? For example, in Dirpartytable, the column OMOPERATINGUNITTYPE is missing. I read that it exists on a derived table called omoperatingunit. However, that table doesn't exist in the axDB dbo schema. It does exist in the ax schema but it is empty. How do I determine where omoperatingunit resides? Thanks experts!

    2 people found this answer helpful.
    0 comments No comments

  2. Amira Bedhiafi 26,971 Reputation points
    2024-07-17T22:39:56.2433333+00:00

    To address the issue of missing fields in Azure Synapse Link for Dataverse, particularly the "DataArea" column from the DirPartyTable, start by verifying the table schema in Dataverse to ensure the column is included.

    Check the Synapse Link settings to confirm that all required fields are selected for synchronization and that entity relationships are properly defined, especially if "DataArea" is sourced from a related table.

    If possible, query Dataverse directly to verify the presence of "DataArea" in the source. Consider using Power Query in Dataverse to create a custom view that includes all necessary fields, then sync this view with Synapse Link.


  3. Steven Cardella 0 Reputation points
    2024-07-18T18:42:25.1533333+00:00

    Ok, this was mostly confusion at work. Tables as they appear on the front end AND as they appear in Export to Data Lake are displaying views, not physical tables. These views can combine data from multiple physical tables. Synapse Link only exports physical tables. The distractions of course are 2-fold here.

    1. The physical tables and front-end views can be named the same thing.
    2. The function of dataarea and dataareaid are essentially identical, but they actually come from 2 different tables.

    DirPartyTable was showing a field called "dataarea" from CompanyInfo in the front-end as if it were part of DirPartyTable. Given the similarity of name and function, it seemed to be merely a relabeling of the field name on the Synapse Link side. Instead, Synapse Link kept the DirPartyTable information separate from the CompanyInfo information. This is a departure from the functionality of Export to Data Lake.

    0 comments No comments

  4. Howie Fu 0 Reputation points
    2024-08-23T03:40:08.9233333+00:00

    It seems to be a Table inheritance thing. The dirpartytable we see in AxDB or in Export to datalake is actually an extended view over many others children dir tables. I am writing a custom view in dataverse using AOT relationship to rebuild the dirpartytable in dataverse.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.