Connecting CRM 2016 to Power Query in Excel 2013 with updated Odata feed

In my previous posts we had discussed about how we can connect Dynamics CRM with Power Bi and Power Query. But with the introduction of API in CRM 2016, there is slight change in the OData feed URL generated for CRM 2016.

Using the same process mentioned in older posts but making use of this new OData feed URL, we can achieve same results for CRM 2016 as well.

The url format is [Your Organization Root URL]/XRMServices/2011/OrganizationData.svc.

e.g.: https://u———–y.api.crm5.dynamics.com/XRMServices/2011/OrganizationData.svc

Advertisements

Migrating data to a live on-premises CRM system?

While working on one of the project, we were required to move data from legacy system into a live production system with limited amount of black out window. One can calculate the time required based on number of records, server configuration, etc. and can even have a buffer. But you never know when Murphy will play his role.

Below are few points that, if considered can help Murphy stick to its seat and don’t show up. (Just some pre-checks that helps the processJ)

  • Confirm disk space on SQL, web and application server. Have some buffer space as logs will grow.
  • As your target system is a live production system, surly there will be some scheduled maintenance jobs running to maintain server health as part of disaster management. These jobs are life savers but problem with them is that these are scheduled in down time as they consume high resources. And unfortunately this is the only time when we can perform our import. Thus you may have to consider pausing the maintenance jobs. But do remember to turn them on once done with the import. Some of the resource consuming jobs are: consistency check, database backup, async-operation cleanup, POA, etc. Pausing this job helps the import utilize maximum available server resources.
  • Check the database log size and clear. The log will grow with the import and if it reaches the maximum available threshold the import processes throws timeout errors. Also check the shrink process, preferred if simple.
  • Check for CPU and Memory usage on SQL, web and application server.
  • Check for any blockage on SQL server, if any script is blocking or slow running queries.
  • Clear AsyncOperationBase table.
  • You may run into scenario where it is required to restart the SQL service. Make sure that this do not affect any other process. Also in case of NLB, upon restarting the SQL service switches the active node. Thus you will also have to consider that node for performance check.
  • You may have to disable the user logging and turn off the workflows and plugins.

And last but not the least is FULL database backup before start of the process. Also you make to do this in several passes due to amount of data and limited amount of down time. Identity the steps that can be performed outside black out that do not affect live system. This provides some extra time for completing the critical steps.

I would recommend to do the import in multiple small passes which helps in keeping the buffer and reduces the chances of breaking things or running on edges. After all “Rome was not built in a day”.

These are some of the steps that helped me. As always, these may not match exactly to your requirement but some of them will surly. And I don’t guaranty of anything from the steps as risk will be yours as it’s your production system.

If you have anything to add, please write in comment and I will update the content. Thanks!

Configuration Migration tool for CRM 2015

Note: Contents re-blogged as it is from TechNet blog.

The Configuration Migration tool enables you to move configuration data across Microsoft Dynamics CRM instances and organizations.

The Configuration Migration tool allows you to:

  • Select the entities and fields from where you want to export the configuration data.
  • Avoid duplicate records on the target system by defining a uniqueness condition for each entity based on a combination of fields in the entity, which is used to compare against the values on the target system. If there are no matching values, a unique record is created on the target system. If a matching record is found, the record is updated on the target system. If no duplicate detection (uniqueness) condition is specified for an entity that is being exported, the tool uses the primary field name of the entity to compare against the existing data on the target system.
  • Disable plug-ins before exporting data and then re-enable them on the target system after the import is complete for all the entities or selected entities.
  • Validate the schema for the selected entities to be exported to ensure that all the required data/information is present.
  • Reuse an existing schema to export data from a source system.
  • Embed the exported modules created from this tool (schema and data files) in other programs. For example, you can use the exported data in Microsoft Dynamics CRM Package Deployer along with other solutions files and data to create and deploy packages on a CRM instance.
  • The Configuration Migration tool does not support filtering of records in an entity. By default, all the records in the selected entity will be exported.

The following diagram illustrates how the Configuration Migration tool is used for migrating configuration data.

i

DID YOU KNOW? What happens to Audit History on performing database level update?

062915_1445_DIDYOUKNOWW1.jpg

Note: This is all about UNSUPPORTED database level updates. These kind of updates are not recommended (unless you are left with no options).

Though any kind of database level updates are not recommended in CRM, there are scenarios where we are required to do so. I had one such requirement where I was required to perform a simple update (setting two options field value to Yes) for millions of records. Doing same using a supportive way (bulk update or bulk workflow execution) used to take days due to CRM service calls but same updates using SQL script was done in few hours.

I had auditing enabled for these records in CRM, but as this was unsupported db level update I was not expecting any help from audit history. What was expected form auditing was either of below:

  1. Change track as new update request in audit history. (If there is any kind of trigger return at db level to maintain auditing)
  2. No updates at all in audit history. (Assuming auditing are maintained by some kind of workflow/plugin that trigger at UI level)

Unfortunately, none of this happened and what happened was not at all audit friendly or expected. This is what happened:

The change made by SQL script was tracked at create event itself in audit history. But only the field value change was tracked and not the change date/time. The change date/time was still showing when the record was created.

Though these kind of changes are not recommended, but I still feel that in some cases these are required and this behavior of auditing will affect the consistency. I feel that things should be either completely baked or not baked at all than having them half baked. But again this is only what I feel. J

Example:

  1. I created a Contact record on 7/6/2015 at 11:52 AM with ‘First R~~~~~ Created’ set to ‘No’. Audit history shows something like below.

     

  2. Now on 7/6/2015 at 12:00 Noon, I update the value for ‘First R~~~~~ created’ to ‘Yes’ using a database level update script. Ideally there should have been a new entry made in audit log to track this change and there should be no change made to existing audit history records. But CRM updates the changed value in create event itself without even tracking the change time.

 

Hope this helps and thanks if you have come all the way down here reading the article.

DID YOU KNOW? Value for overriddencreatedon is swiped with createdon at database level

One of my college recently had a requirement to import few records into CRM with back dated values. Isn’t it a simple task? Just map/set the back dated value for ‘overriddencreatedon’ field while create/import of records. But has anyone checked what happens to the value for ‘createdon’ field?

All views and fields in CRM displays ‘createdon’ field and not ‘overriddencreatedon’. Thus, wherever we set value for ‘overriddencreatedon’, the value is set to ‘createdon’ field and the actual date/time when the record is created into CRM is set to ‘overriddencreatedon’.

E.g.: I’m creating a record on 2015-06-16 11:45:23.000 UTC into CRM with value for overriddencreatedon set to 2015-06-16 11:44:39.000 UTC. Upon completion, if you query the database you will see that the value what I set to overriddencreatedon is set to ‘createdon’ and the value for ‘overriddencreatedon’ is replaced with actual date/time when the record was created.

Update request at 2015-06-16 11:45:23.000 UTC:

Createdon = null

overriddencreatedon = 2015-06-16 11:44:39.000 UTC

Result:

Createdon = 2015-06-16 11:44:39.000 UTC

Overriddencreatedon = 2015-06-16 11:45:23.000 UTC

date

Note: Setting ‘overriddencreatedon’ on update of record is not supported.

Top 10 Data Migration Traps

While planing for the data migration item for one of my project, found this great article by Bruce Buxton which explains top 10 data migration traps. It was good to know as prevention is always better than cure 🙂

http://www.xrmconsultancy.com/2013/10/top-10-data-migration-traps/

Invoice and Invoice Line data import in Dynamics CRM

Recently I found that many people have issues importing old Invoices either from legacy system into Dynamics CRM. This is manly in scenarios where Products are not maintained in CRM. In this case import of Invoice Line throws error as the data import template for Invoice line do not have relationship with Invoice. Below are the steps that can be followed to fix the issue.

Invoice lines is a relationship between invoice and products. Thus if you need to add invoice lines, you need to add products using the “Product.xml” template file. But in our case as we don’t maintain Products in CRM, while importing Invoice lines, add invoice number of imported invoices in CRM in a separate column and select the Product as write-in in import file.

For importing Invoice lines, you need to download the “Invoice Product.xml” template file.

All the mentioned templates can be downloaded from “templates for Data Import” in Data Management section in Settings.

  1. Export Invoice Product.xml
  2. Add Invoice Line data in the xml with below values.
    1. Set “Select Product” column value to “Write In”.
    2. Add a new column “Invoice ID” and set the value to Invoice ID field of respective Invoice.
  3. Save and import the file.

4. Import the above xml file.

5. During Import wizard, on Review Mapping Summary Page, click on Edit.

6. In Map Fields, for Invoice ID row click on the look up reference and select “Invoice ID” field and click Ok.

7. Click Next and progress with the import process.

Probably issue with many users follow is at step 7. Invoice ID field is by default map to Invoice name and Invoice GUID field.

Hope this helps!

Use Upsert to insert or update a record

You can reduce the complexity involved with data integration scenarios by using the UpsertRequest message. When loading data into Microsoft Dynamics CRM from an external system, for example in a bulk data integration scenario, you may not know if a record already exists in CRM. In such cases you will not know if you should call an UpdateRequest or a CreateRequestoperation. This results in your querying for the record first to determine if it exists before performing the appropriate operation. You can now reduce this complexity and load data into CRM much more efficiently using the new UpsertRequest (Update or Insert) message. This topic discusses how to use UpsertRequest and the process that unfolds when an UpsertRequest is received.

For Microsoft Dynamics CRM Online organizations, this feature is available only if your organization has updated to Dynamics CRM Online 2015 Update 1. This feature is not available for Dynamics CRM (on-premises).

**This topic is pre-release documentation and is subject to change in future releases.

https://msdn.microsoft.com/en-us/library/dn932135.aspx

[Error] – Data missing for few fields on all records for an entity in organization created using dbRestore

We have this strange issue where a new organization was created using a database backup of production. Issue here is, for Entity A in source (i.e. production) we have two fields of type decimal populated with a value for all the Entity A records, but the target environment created using dbrestore is missing these value for all the records.

          Audit history on source shows the field populated while creating the records.

 

         We do have same audit history record on target with same date and time but the field entry is missing there. Instead there are two unusual entries with attribute mask number appended by [deleted]. I’m not aware what this entry points too as this got added to the create step itself. This is for all such records of that entity on target.

         Two deleted records points to the two fields for which data was not populated.

 

         But strangely these two fields still exits on target and were never deleted.

I had also added the issue on Community forum (link) below few days back but no help from there too yet. I’m planning to go for a support ticket so any inputs will be helpful. Thanks in advance!

https://community.dynamics.com/crm/f/117/t/151328.aspx