locked
Naive CRM Database Question RRS feed

  • Question

  • Hello, I've been working with the Microsoft Dynamics 4 system for around 5 months now and recently began looking at data integration with other products. One thing that puzzles me is the process of writing data back into the CRM from an outside source. Essentially, if I put a field, 'Year to Date' sales on the account form, and wanted to populate this with data calculated every night in an external SQL database, why can't I just manually update the SQL? I seem to be able to find no end of comments detailing that changing the CRM system in SQL is completely unsupported, and shouldn't be done except as a last resort, but I'd like some explanation as to way. What if the update is just changing a static value based on an accountid, I fail to see how this will ruin anything? Is this advice purely to stop people from changing the database structure, say modifying table structures or stored procedures? I would greatly appreciate an example scenario in how this fails. Thanks in advance. 
    Friday, July 16, 2010 3:20 PM

Answers

  • Hi,

     

    The advice not to touch the database (not to insert data directly) stems from the fact that Dynamics CRM utilizes the metadata layer. So, first of all, that means each record is splitted into two tables in the database. Secondly, the metadata is aware of the language version of some fields, of the meaning of indices in the dropdowns and so on.

    I understand your concern that updating a simple static field is not undoable. However, due to the abovementioned fact that CRM heavily utilizes the metadata layer, the database should remain abstracted. Thanks to that, you don't have to worry e.g. that a new rollup or a new build will name the field that you would like to update in another way.

    There is no scenario that would show you that an update of one single and simple field would fail - it will not. It is generally unsupported and it is conidered a bad practice, because you operate data via the metadata layer thrugh web services in the platform layer. And that's it.

     

    I hope that helps,

    Jakub


    -- Kuba Skałbania, Netwise
    Friday, July 16, 2010 3:30 PM

All replies

  • Hi,

     

    The advice not to touch the database (not to insert data directly) stems from the fact that Dynamics CRM utilizes the metadata layer. So, first of all, that means each record is splitted into two tables in the database. Secondly, the metadata is aware of the language version of some fields, of the meaning of indices in the dropdowns and so on.

    I understand your concern that updating a simple static field is not undoable. However, due to the abovementioned fact that CRM heavily utilizes the metadata layer, the database should remain abstracted. Thanks to that, you don't have to worry e.g. that a new rollup or a new build will name the field that you would like to update in another way.

    There is no scenario that would show you that an update of one single and simple field would fail - it will not. It is generally unsupported and it is conidered a bad practice, because you operate data via the metadata layer thrugh web services in the platform layer. And that's it.

     

    I hope that helps,

    Jakub


    -- Kuba Skałbania, Netwise
    Friday, July 16, 2010 3:30 PM
  • Thank you for that answer, just one side question before I drop it, if I run into an instance where the web services aren't performing fast enough, say updating a large amount of fields in multiple different entities. What is the next step, do I look at possibly changing the interaction with the webservice (running on multiple threads?), do I add another async server or sql database mirror. What is the best practice way to increase throughput into the CRM system? I know there is no magic answer, but just looking for the best place to start.
    Friday, July 16, 2010 3:41 PM
  • It depends on how much time you can spend on updates :). We have customers that require an update with resolving all conflicts on 70000 orders daily. And we accomplish it in about 4 hours each night. However if you want to increase the throughput, you may try to run several instances of your app, but remember then to split your source data! Otherwise, you will do the same updates several times. Running in multiple threads also sounds good.

     

    Unfortunately there is no bulk update yet :)

    Kind regards,

    Jakub


    -- Kuba Skałbania, Netwise
    Friday, July 16, 2010 3:45 PM
  • Jakub,

    You raise some interesting arguments, and forgive me I do not mean to belittle the point, but I too, like Nick, am new to AX (I'm actually newer than he is...).  However, I have been a SQL Server developer since version 4.5 and I don't understand the issue with direct update of the underlying data.  If, for example, I have data that is extracted from AX every evening that I want to update in CRM and given that it may take 3-4 hours, which I can't afford, if I know that that the fields I need to update are the CreditLimit and CreditOnHold in the AccountBase table and that the remaining 5 fields are user-defined fields in the AccountExtensionBase field, am I going to cause harm to the database?  Based on your answer, I suspect the answer is "no", it may not be a "Best Practice", but sometimes a "Best Practice" is not the "best way".

    If I am going to "split the source data" in order to split the threads, my effort at programming the solution (using BizTalk to communicate between the two applications) could be a week or two.  However, I can write a simple update query to handle this in under 30 minutes.  Is the cost-benefit advantage of the direct SQL update so dangerous that Microsoft is going to say "Sorry, that's not supported.  You are on your own..."?  Or, is the fact that I have written well-conceived and well-organized queries going to override the perceived danger?

    Thanks,

    Steve

     

    Monday, July 19, 2010 12:24 PM
  • Hi,

    Even though it's correct that it's unsupported to update data directly in the database, there are no problems doing it - if you know what you are doing..:-). I'm also updating data directly in the database once in while and have never seen a problem with it.

    As long as you know what you are doing and know the database architecture, I can't see a problem with it. Be aware though that sometimes you have to update more than just one filed to "mimic" what the API is doing. E.g. if you need to update Account Name on Accounts, then you'll also have to update e.g. RegardingObjectIDDesc on Appointments etc. That's not very obvious when you just look at the table design..:-).

     

     


    Steen Schlüter Persson (DK)
    Monday, July 19, 2010 1:29 PM