Creating Monthly Goals in Bulk via Importing
Have you ever run across a solution that requires goals to be created every month within Dynamics CRM? If you have, then you know that recreating these goals on the first of every month, one by one, can become time consuming. Using "waiting" workflows create goals can become an inefficient use of CRM resources. In this blog we'll discuss an easy alternative for using Excel to import these goals in bulk using the following steps.
- Setting up your goals
- Creating your spreadsheet
- Importing goals from a spreadsheet
Let's get started!
Setting Up Your Goals
Let's pose a hypothetical scenario. Let's say you want to utilize goals for tracking the number of phone calls completed by each user each month, with the target being 1,000 calls a month. You have 50 users and the idea of setting up 50 phone call goals individually is an inefficient use of your time.
Before we go any further, some steps to this process need to already be in place. For starters, any Rollup Fields need to be setup in order to be used correctly for the appropriate Goal Metric (which should also setup properly). A common scenario for this example would be to have a Parent Goal to rollup totals for each month. This Parent Goal would need to be created prior to importing a spreadsheet of our 50 phone call goals, aka Child Goals.
Some things to note of this Parent Goal as it interacts with our Child Goal spreadsheet creation:
Creating Your Spreadsheet
We are now ready to create our spreadsheet of 50 goals. Fields to be included, along with some notes:
- Name – unique
- Goal Owner – user that exists
- Goal Metric – metric must exist
- Parent Goal – goal must exist
- From *
- To *
- Metric Type *
- Goal Period Type* – Custom for this monthly example
- Manager – user that exists
* Must match that of its Parent Goal
When formatting the columns for this data, all fields can be General with the exception of From and To, which will need to be Date. A big advantage of creating goals in bulk like this comes within Excel's abilities to Copy and Paste, as well as Find and Replace. When your spreadsheet looks accurate and complete, save it as a CSV (comma delimited) file:
Importing Goals from a Spreadsheet
Next, import into your CRM as usual using the Import Data Wizard. Map to Goals, which brings to you to mapping the fields individually. The wizard does most of the dirty work here by matching up the correct source and CRM fields. The exception for this example would the Target field, which we want to be an integer for phone call count, as opposed to a decimal or money.
And just like that, you have created multiple goals in Dynamics CRM using a much more time efficient process. Note that the goals will need to be recalculated to see results. And if you are feeling really ambitious (we know you are out there), you could create goals for months and months out in advance! Now, head off into the sunset and set those goals.
Performing Bulk Operations Using Scribe Insight Adapter for Dynamics CRM 2011
You can speed up your data migration using the new Scribe Insight Adapter for Dynamics CRM, Version 5.4.0. How? By doing a bulk operation! The new adapter comes with Scribe Insight 7.6, which now allows you to perform bulk insert, update, and delete operations.
A bulk operation processes a block of rows in one transaction, minimizing the number of calls between the Scribe source and the CRM destination. The block of rows (as specified in the screenshot below) is sent in a bundle once to CRM for commit.
With row-based processing, each row needs to be committed before it sends another. This does not mean that if you set number of rows per operation to 1000 that it will be 1000 times faster than row-based operations. Scribe does not work that way yet! There are a number of factors that determine how many records are pushed in one transaction, including type of connection, cross reference keys, update source, or rejected rows.
Here is how you enable bulk operation in a DTS package step:
- Go to Configure Steps.
- Select an insert, update or delete step and then click on the Operation tab.
- You should see a check box labeled Use Bulk mode. Check it!
- Close this window and save the package.
You are all set!
There are certain things you'll want to consider before you use bulk operations in your Scribe package. Some of the very handy features of Scribe becomes unavailable when we use bulk operation. Be aware of the following:
- If you have a step in the DTS file that depends on the step before it, bulk operation cannot be used. This is because blocks are committed independently. You might get into a situation where a block in step 2 gets committed before a block in step 1, which will make it out of sync. A common use of this dependency is when you want to update your source table with the GUID of the record that you created or updated in CRM—bulk operation does not work in this situation.
- If you have variables that are populated from your steps, they will not be populated because processing of records happens in block.
- If you have lookup functions in DTS that depend on a value from a step that is set to operate on bulk mode, then it will not work.
The speed advantage
Speed is the primary reason why we look for bulk operation. If the DTS steps are simple and need to process a large number rows (say in billions), then it is definitely a good idea to use bulk mode.
Below are performance comparison statistics compiled by PowerObjects using Microsoft Dynamics CRM Online:
||Number of threads (packages running in parallel)
||Number of rows per operation(bulk)
||Rows per minute
||5.3 X Row-Based insert
||6.0 X Row-Based update
Note: These are not Scribe's official numbers. These are the numbers that PowerObjects got when we tested this new feature.
The focus here is not on how big these numbers are—the actual numbers can vary based on number of processes running in parallel, number of fields updated, and so on. Instead, look at the difference between the rows per minute for these two types of operations. Our test shows that the bulk insert operation is at least 5.3 times faster than row-based inserts. Similarly, bulk update is 6 times faster than row-based updates. In either case, the results clearly show that bulk operation outperforms row-based operations by multiples of 5 or more.
In conclusion, you can get more performance using bulk operation than normal row-based operation. At the same time, bulk mode has some limitations. So, to utilize Scribe Insight's bulk mode feature of the new Adapter for Dynamics CRM 2011, consider simplifying the Scribe package steps. You can mix and match row-based or bulk mode steps in a single package.
Bulk operation is a very powerful feature and can be best utilized in migrating/integrating to CRM online where the number of rows processed is large.
Happy migrating and integrating to Dynamics CRM!