Full load - Single table

[RonFusionHSLLC]

From time to time, we have issues with Replication and miss records from the source.  Full Replication load of the ODS from the source solves that problem, yet the DW/DM are still missing the records without some additional actions.

We typically run the DW in incremental load from the CT tables, so the missing replicated records never get into the CT tables and thus never get into the DW. 

I've run a full load (without drop/recreate) but that doesn't pick up the records, which is somewhat confusing to me.

Are there any tricks to reloading missing records?

I could write code to 'touch' the missing records in the source and trigger replication to CT tables, but that seems awkward, but certainly will work.

I know I can brute force the issue by doing a full drop/recreate/full load of the DW and the DM's, but that has its own set of issues.

Ideally, I'd like to only drop/recreate/full load only a few tables that are missing records, vs having to reload the entire DW.

Any help would be appreciated.

ron

[Steve_Nguyen]

if you want : I'd like to only drop/recreate/full load only a few tables that are missing records,

then from the running task, click on the full load, and highlight the table that you want and reload. (assuming that your task is setup full load drop / recreate) this would reload the highlighted table only.

[RonFusionHSLLC]

Sorry Steve...I dont quite understand the answer, can you elaborate a bit?

[Steve_Nguyen]

@RonFusionHSLLC ,,

i just now see that you are talking about Compose Data Warehouse. let me see if i can get a Compose person to look at this for you.

[RonFusionHSLLC]

Thanks Steve...yep, Compose.  I see you meant - how to reload from the ultimate source into the ODS via Replicate.

[Nanda_Ravindra]

 @RonFusionHSLLC

If you have done a Full Replication load of the ODS from the source, then the FL load in Compose should pick your missing records. If this is not happening then we need to take a closer look at  Compose/Replicate to see why it's not picking your missing records. But are you sure the replicate is picking up the missing records when you do a full load upon the missing record?

We should be able to trick Compose just to do an FL for a few tables. Let me think a little bit about this, and I'll get back to you tomorrow on this. In the meantime could you tell me the version of Compose you are running ?

Thanks,

Nanda

[badgerriaan · Accepted solution]

Hi,

We replicate from SQL sources and this is the process I follow to add missing records if for some reason I see them in the source but they did not make it into Replicate (connectivity issues or whatever reason).

NB. Do check the "attrep_apply_exceptions" table in your Replicate landing to also make sure that it is not some other issue that is keeping the records from applying 🙂

For only some of your tables:

1. Stop the replicate Task the table/tables are in.
2. Then Run my compose CDC to make sure I get any current __ct records processed (As a full load of table clears that)
3. Then from the Replicate Task Monitor tab pick the tables you want to reload and click the Reload button and see them populate on the Full Load tab
   
4. In Compose I usually have a separate task (just regular not CDC) or create a new one for these maintenances loads.
5. Against this task select the mappings for table/tables that you just reloaded in Replicate with handle duplicates also ticked.
6. Generate this task and Run it. It will then bring in the snapshot tables from the replicate and add the missing lines. (you will see it does generate statements for any linked tables to be able to manage ghost entries if needed)
7. Now reschedule or resume your CDC Compose jobs and they will pick up the new __ct records as per usual.

For full Compose refresh without a drop and recreate:

Say there was a connectivity issue and you are not sure what tables are out of sync in Compose and replicate.

Follow the same process as above, but just for all tables:

1. Stop Replicate tasks
2. Run Compose CDCs
3. Reload Target on Replicate (Make sure the issue is not because of major DDL or structure changes in Source because this will bring Replicate Landing in sync and you will need to apply the appropriate changes to compose model too )
   
4. Run your Compose Full Load task with Handle Duplicates (This might take quite a while)
5. Resume CDC tasks (As the changes will be captured in replicate still while the full load is running)

[Nanda_Ravindra · Accepted solution]

As I mentioned in my previous post, Full Load on Replicate following the Compose Full load should bring back your missing records. If this is not happening, then I think it's better to open a support case and the team should be able to find the root cause and fix it.

In any case, if you just want to reload a single table s on Compose, then you’ll need to follow these steps. Please implement and test this in your lower environment before applying this to production.                      

1. On Replicate Server: Stop the task for the table in question

2. On Compose server:

             A. Run the CDC task  for the table in question

              B. Under ‘Manage task’, duplicate the Full load task for the table in question

               Note: Make sure to select the ‘Full load Only’ option under task type

             C. Enable the ‘handle duplicate’ check box for the newly created task from  Step#2B

             D. Generate instructions for the newly created task

 3.  On the replicate server: Reload the table in question and start the task

 4.  On Compose server:

         A. Run the newly created Full load task <-- This is a one-time activity

        B.  Verify the data

        C. Remove/Delete the newly created  Full load tasks in   Step#2B     

Hope this helps! If you need help with the above steps just let me know and I'll set up a call to go over this together.

Thanks,

Nanda

[badgerriaan]

Hi @RonFusionHSLLC, can you be nice and please mark my answer as a solution too. It basically outlines exactly what @Nanda_Ravindra  posted, only mine was a whole day earlier 😉