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

Question

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.

Answer 1

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!

Answer 2

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.

Answer 3

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.

Answer 4

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.