From the Stone Age to xRM…..

Data migration and data integration are one of those subject that you either love or hate – the Marmite (Vegimite)  syndrome . I’m one of those people who love it.

Whilst working on a project that required both, I tried to explain the process to my wife – ‘cos she asked. As I went into the intricacies of staging and transformations, scheduled process etc.. I noticed I had lost her at the staging database – the clue was she was on facebook!

So, rewinding, I started again – using CRM, data migration terms and a real world example:

The Objective

We are on a family holiday for 2 weeks, beach , sun, self-catering etc…We take suitcases full of clothes. The usual holiday approach of we take too much, clothes we won’t use, clothes we think we will need and the pair of shorts you live and die in.

When we get home , my daughter and I would like to find our clothes washed , ironed and placed in the correct room, wardrobe, chest of drawer and draw.

The Client.

My daughter and me!.

Disparate Data sources.

On arriving home, neither myself or my daughter have any interest in the “laundry” cycle. We just assume there is a laundry fairy that makes things happen. So, as suitcases get emptied, piles of clothes appear – general in the same location of the opening of the suitcase.

Staging Database.

My wife will collect all the piles of laundry and move these many piles to one big pile in the utility room.

Cleansing & De-Duplication.

From the big pile , she will sort the big pile into various smaller piles based on colour, material etc.. These piles enter the washing machine. The washing cycle takes a few hours and can only take the sorted pile of laundry of the same colour and type.

Scope Creep.

My daughter , in boredom mode from returning from a fun filled holiday, finds the PE bag in the corner of the room with 2 week old kit which she forgot about. This is mainly due to the excitement of the impending holiday. So, she dutifully bring the previously unknown bag of laundry down to the utility and dumps it in the middle of the other segmented piles of laundry.

The usual discussion happens along the lines of “Why didn’t you mention this earlier”, “I didn’t think it would matter”.

The effect of scope creep.

My wife, now has to sort the new bag of laundry and assess whether each item can be added to the existing pile or a new pile should be started. In some instances the new pile is not viable for a full load in the washing machine so she will make the decision to defer that pile for another week.

The net result of the sudden appearance of the PE kit is Dinner will be late “or go and get a take away”. The latter is more expense after a holiday.

Deltas.

The washing ironing cycle will take a few days to complete, largely due to the volume of clothes taken on holiday , bad planning on what was actually needed and even worse planning for the return trip when everything was thrown into the first opened suitcase.

However, during the weekend, we are still wearing clothes and still placing in the linen basket. Yes, we have been trained to place in the appropriate basket now instead of a pile on the floor. My wife periodically will collect the baskets content and move these to the now small piles of laundry in the utility room.

Data Loading.

After a few days of the weekend, the washing , iron cycle is almost complete. Clothes are clean, ironed and have now been placed in the correct wardrobe etc. As part of the housework process, my wife will match socks – so I have a pair! and throw out old socks that are no longer in use.

The sign-off process of the bulk data load.

In this instance, wine. As the holiday bulk washing load has been completed.

Batch Data Integration as a scheduled task.

The daily repetitive weekly cycle of laundry basket to utility pile to washing machine to iron to wardrobe, happens as part of our weekly routine. It just gets done, its so mundane we don’t know we are doing it because it so automated.

On-Demand data integration.

The usual, 6am alarm clock as I get up to visit a client in London. “Do I have a shirt ?”. I won’t complete this discussion….

Summary.

At the end of the “real” world example – it was clear , she had got it.

I have a requirement to force “waiting” system jobs to start and continue working.  My scenario is that I have 200k records in the async queue and its stopped!.

I found that resuming a job allows it to run to completion. However, resuming too many jobs cause the job to fail.

My solution is to write a simple app that searching for “Waiting” jobs and resumes them. Mindful of a limit (to be discovered) that too many will cause the jobs to fail.

My fetch returns all the jobs:

The SDK shows statecode =1 is Suspended and statuscode =10 is Waiting

My FetchRecords using a parallel paging that will return ALL the pages, in this case about 130000 records in about 20-30 secs.

Now for the resume code. Simply, I need to set the statecode to Ready and the statuscode to Waiting for Resource

My resume function works a follows: batch 100 records at a time (across 2 threads), process and wait for minute for the queue to clear. The Parallel function Partitioner is another favourite from the parallel library :

and this will get the next block of records:

Running this process also reduces strain on the CRM Server therefore allowing other jobs to be actioned.

“The user authentication passed to the platform is not valid”

Over the Xmas break I decided to rebuild my lab servers from the ISO upwards. Limited on HW and memory, I planned on the the following config:

Box 1 : AD, SQL, CRM

Box 2: Windows 7 client

Simple?

In my haste, I installed CRM 2011 on the DC using the “Network Service” account. All appeared to be fine until I tried to access the discovery service. OK, a quick repair to a “proper” account failed also.

My only solution was to uninstall CRM and reinstall with a “Domainuser” account.

Lesson learnt : Don’t install CRM 2011 on a DC using “Network Service” as the CRM accounts.

I’m reading a lot of blog posts where people are starting to use the parallel libraries in CRM development. I’ve been using the C# .NET 4.0 parallel library for a while.

Firstly, the Parallel.Invoke method, This allows the running of processes in parallel. A simple example of this is:

The above snippet just demonstrates that I want to fetch currencies, providers and holdings at the same time.  The Invoke is completed when the longest running routine is complete. Time saving = “sum of the time to complete the slowest functions”

Secondly, the Parallel.ForEach method, this simply allows the iteration through a collection of objects and runs each on a separate thread.

The above runs through a collection of incomeprofiles and actions each incomeprofile on a separate thread. The current thread can be identified by the

“Thread.CurrentThread.ManagedThreadId”

The Parallel library will manage the threadpool and ensure you will have a new thread, once the thread has been completed its returned to the pool.  In the context of CRM this can be quite useful.

Within the above process I use a CRM library (that connects to CRM). I can either instantiate the library outside the loop – this is not a good idea as it becomes not very thread safe and a bottleneck – invalidating the use of parallelism. My preferred approach is to store instantiated services and index them by the current managed threadid, by doing this I can reuse CRM library services:

This code snippet controls this process:

The routine locks the dictionary object therefore preventing cross thread issues. A property on the main class allows for a new instance or existing instance of the service class to be returned:

Note: the use of the ManagedThreadId to determine the index into the dictionary object.

The use of the parallel library improves performance considerably, however, it still can be tuned that little bit further!.

Increasing the throughput via the DefaultConnectionLimit (MSDN), increasing this to 20 will give that little boost. I would, however, monitor the servers performance as this may have an adverse impact on the server!.

Hopefully, the above code snippets will inspire you to investigate further and lead you to writing faster and more efficient code.