In some cases certain larger tables can contain record count that is over 500.000 records. When that is the case, we face some limitations in the Dual Write initial sync scenarios.
In order to bypass this we recommend a way to accomplish this in a relatively simple way.
In this case we will be dealing with A365 Devices. The table, A365Device contains more than 1.7 million records. In this case the records have been copied from the AMDeviceMasterTable to the A365Device. When dealing with standard MS tables you would just enter the appropriate table e.g. CustTable.
Enter the table in question in Table browser mode.
To achieve this past this text after the &mi in the URL, such as mi=SysTableBrowser&TableName=A365Device
Go to the RecId field that is the last column in the table. Press it and sort the records from smallest to largest. Copy the Recid value and paste it somewhere.
In our example the smallest Recid is: 5637144576
See screenshot 3 below for further details.
After storing the smallest RecId do the same but now sort by Largest to smallest.
Copy the Recid value and paste it somewhere.
In our example the largest Recid is: 5638905913
So by subtracting the largest with the smallest we can see that we are dealing with 1.761.337 devices (5638905913 - 5637144576)
As mentioned above, we are limited to 500.000 records so the 1.761.337 records need to be synced from F&O to CE in chunks of 499.000 records.
The most simple way to filter the records is to use the RecId to filter on. To achive this we take the smallest RecId we retrieved in Step 1 and we subtract 1 to include the first one.
To set the RecId range we can use the Calculator to get the next RecId.
So I put the smallest RecId (-1) and add 499.000 to that number.
So we will go from RecId 5637144575 to 5637643575. These values need to be put in the RecId filter in DW.
To apply the values to the filter, go to the mapping in question and press the Filter icon on the Finance and Operation apps. TableName (see screenshot 6 below)
Then apply the rec id values, mentioned above, to the filter.
((RECID > 5637144575) && (RECID < 5637643575))
IMPORTANT: After pressing the Accept button above it's very important to Save the mapping.
Press Save and then go back to the Dual Write list (see screenshot 8)
In the mapping list, find the appropriate mapping and press Run.
Make sure to select initial sync and select Finance and Operations apps as the Master for initial sync.
When the first run has finished, the same procedure that was done in Step 2 needs to be performed as many times as needed in order to move all the records between F&O and CE.
If the mapping ends with status Running, the mapping needs to be stopped, the filter changed, saved and then run again.
If the mapping ends with status Not running, the filter needs to be changed, saved and then run again.
This needs to be performed as many time as needed (until all records have been moved between F&O and CE).
This is the procedure that could serve as a template on how to accomplish a similar scenario.
Smallest recid 5637144576
Largest recid 5638905913
Subtract 1 from the smallest recid
Add 499.000 to the recid
Subtract 1 from the next recid (5637643575)
Add 499.000 to the next recid
Subtract 1 from the next recid (5638142574)
Add 499.000 to the next recid
Subtract 1 from the next recid (5638641573)
These are the query filters values used in the 4 steps needed to get the 1.7 million records over.
1. ((RECID > 5637144575) && (RECID < 5637643575))
2. ((RECID > 5637643574) && (RECID < 5638142574))
3. ((RECID > 5638142573) && (RECID < 5638641573))
4. (RECID > 5638641572)
NOTE: When dealing with a large number of records we need to be effective in order to reduce running/waiting time. Since we are dealing with initial sync it usually means that we are moving records from one system to the other. In this case we are moving records from F&O to CE (Device in this case). By setting the Master for initial sync as Finance and Operations apps we are saying that records should go from F&O to CE. But when a mapping has bi-directional mapping the system will try to update from CE to F&O when it has finished moving records from F&O to CE. In our example case, the system will try to move/update 1.7 million records from CE to F&O. That is unneccassery since we are doing an inital sync.
There are two ways to have the system only update from one system to the other.
You can change the direction on every field in the mapping to be one way, as shown below. This option is quite cumbersome and you would then also need to change the direction back when the initial sync has been completed.
A more appealing option would be to change the Query filter range on the CDS side in order to block the update back to F&O in this case. To do this, go to the filter sign on the Common Data Service side and set the device number to a value that will never be true (see screenshot below). By doing that we reduce the time that it would take the system to write 1.7 million records back from CE to F&O.