Determine which columns should be updated during an incremental load

  • Hi Folks,

    I have the following Scenario: I receive customer data every day from an external Company over an ETL process. I store this data in a database called the Data Hub. The customer table has two columns to identify new and updated records: CreateDt (timestamp) and UpdateDt (datetime). I need to load these data into a Dynamics CRM. For the Initial load all is clear, but to perform incremental loads over the CRM entities I've proposed the following strategy: if a record has a CreateDT or UpdateDT subsequent to the last loading should cause an insert or an update respectively. The whole row content should be in the correspondent CRM entity updated.

    Up to here everything seems to be ok. The problem is, the CRM guys have complained :cool:, because the load is performed over a Web Service using a CozyRoc component and the process is very slow. Furthermore, the update of some fields Trigger several CRM internal procedures. So they ask me to update only the fields that have changed.

    I think this is time expensive too, and I couldn't figure out how I could do that without comparing row by row and column value by column value. I have about 20 columns. I can dynamically create each query checking the value column by column but for me this is the Flintstone’s Style way, and I find it even worst as the CRM latency Problems;-).

    Any other proposed solution? It will be appreciated it.

    Kind Regards

    Paul Hernández
  • Can you get to the database directly, or do you have to use the web service method?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi Phil thanks for your answer.

    Now I'm trying to persuade my colleagues to go directly to the database and not over the web service. They allege that write directly is unsupported and I say that the web service for this task is like to go deep in the jungle. The major problem is any modification in any CRM attribute triggers an activity (modification, modified by, modified on). Some fields have additional events.

    Even worst, I cannot dynamically configure the mappings in the component that calls the web service.

    I will try to convince them to use dynamic SQL queries intead.

    Kind Regards,

    Paul Hernández
  • Paul Hernández (4/4/2013)


    Hi Phil thanks for your answer.

    Now I'm trying to persuade my colleagues to go directly to the database and not over the web service. They allege that write directly is unsupported and I say that the web service for this task is like to go deep in the jungle. The major problem is any modification in any CRM attribute triggers an activity (modification, modified by, modified on). Some fields have additional events.

    Even worst, I cannot dynamically configure the mappings in the component that calls the web service.

    I will try to convince them to use dynamic SQL queries intead.

    Kind Regards,

    I suspect that they are correct in their allegations of no support for direct access. But that sure makes things difficult for you - those 'triggers' are presumably set off by the web service call and that's a black box - you don't know what they're doing, other than by observation.

    I'll sign off at this point because I don't think I can help. I would not expect the web service method to ever be quick for bulk update operations, so matter how much you refine things - maybe you should cross to the dark side and frequent a CRM forum to see whether they have any suggestions.

    Actually, I did have one idea. Writes to the database may be unsupported, but what about reads? These reads could, perhaps, be used to (relatively) quickly identify which columns need to be updated, with just the updates being pumped through the treacle-like update web service.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply