dynamics 365 fast track incremental load implementation cdc tables created

Wayne Bartkowski 676 Reputation points
2023-03-15T06:30:14.36+00:00

I am following the process in the https://github.com/microsoft/Dynamics-365-FastTrack-Implementation-Assets/tree/master/Analytics/SynapseToSQL_ADF url to set up the Incremental load synapse pipeline from the zip file in this site. I have successfully implemented the Full load from the zip file.

The incrementalload.zip is a template for the creation of a synapse pipeline which has a couple of steps.

GetLastChangeFeed file - this goes off to the storage blob created for the dynamics 365 datalake, secifically, it goes to the ChangeFeed folder and looks for files that have new data, this is the incremental data storage folder. This is working fine as I can see in the pipeline it gets the most recent file when in debug mode.

The next script looks for the FilteredChange table where it retrieves the table and the CDC table for the file eg

"TABLE_NAME": "DBO.VendTable",
                    "CDC_TABLE_NAME": "DBO._cdc_VendTable",
                    "COLUMN_NAMES": " RECID, ACCOUNTNUM,

which also works fine.

Then we start the CDC copy activity which is where it fails for me. At the first step it appears to be going to look for data for the specified table - VendTable - but from the CDC table DBO._cdc_VendTable

SELECT *, ROW_NUMBER() OVER (PARTITION BY RECID ORDER BY Start_LSN DESC, Seq_Val DESC, DataLakeModified_DateTime DESC) AS ROWNO FROM DBO._cdc_VendTable

I assume this cdc table is somehow populated with the small amount of changed data but I cannot find this cdc table.

Please tell me how the cdc tables are created and populated as I can't see any instructions in the url.

Thanks

Azure SQL Database
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,431 questions
Accepted answer
  1. JJ Yadav 75 Reputation points Microsoft Employee
    2023-03-21T05:12:59.5533333+00:00

    Hi Wayne,

    For Dynamics F&O, When you enabled near real time update feature with Export to data lake https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/azure-data-lake-change-feeds , after the full export under table folder, incremental data is available in the data lake under ChangeFeed folder . I am assuming that you are creating the openrowset views or external tables using the CDMUtil for Tables. To use the template to copy the incremental data to SQL, using the cdmutil you can also create view or external table on changefeed folder (by simply adding the manifest file path that is under changefeed folder). This will create the *cdc{*TableName} openrowset view or external table that can be leveraged by the template to copy incremental data to sql. Hope this helps.

    https://github.com/microsoft/Dynamics-365-FastTrack-Implementation-Assets/tree/master/Analytics/CDMUtilSolution

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. AnnuKumari-MSFT 31,241 Reputation points Microsoft Employee
    2023-03-16T16:15:42.89+00:00

    @Wayne Bartkowski ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

    As I understand your query, you are trying to perform incremental load using synapse pipeline , however your query is regarding the creation of cdc tables in the Az SQL database.

    The CDC (Change Data Capture) table is created automatically by the SQL Server database when you enable CDC on a specific table. The CDC table contains the changes made to the source table, including the type of change (insert, update, or delete) and the old and new values of the columns that were changed.

    To check if CDC is enabled for the database or not , you can use the following query:

    SELECT name, is_cdc_enabled FROM sys.databases

    To enable CDC on a table, you can use the sys.sp_cdc_enable_table stored procedure. Here is an example of how to enable CDC on a table named VendTable:

    EXEC sys.sp_cdc_enable_table

    Once CDC is enabled on a table, the CDC table will be populated with the changes made to the source table. You can query the CDC table to retrieve the changes.

    You can refer to the following article for more information on how to enable CDC and query the CDC table: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15

    You can also refer to the following article for more information on how to use the CDC feature in Azure Data Factory to incrementally copy data: https://docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-change-data-capture-feature-portal

    Hope it helps. Please accept the answer and mark it as helpful. Thankyou