We learned how to create a simple sample data for Microsoft Dynamics CRM 2011 in the blog post titled Creating Custom Sample Data for CRM 2011. In this post, we will take a look at some of the more advanced sample data building techniques.
Building a Sample Data Set with Multiple Entities and Related Records
You may want to create multiple related sample data files and then import them into CRM 2011 together. For example, let’s say you create sample data for Account and Contact. You can then create a compressed folder (.zip), drop these sample data files into this folder, and then import the .zip file into CRM from Workplace > Imports. To do so, click on Import Data on the ribbon, and select the .zip file in this dialog below:
Then go through the Import Data Wizard flow. At the end of this process, you would have installed multiple sample data files into your CRM system in one go.
You can create very rich and complex data sets using this method since it allows you to create sample data records that are related to each other. For example, you can set the parent customer field of your sample contacts to sample accounts. The import infrastructure will make multiple passes through your data set and ensure that the records are related properly. The account and contact sample data I created contain the following records:
First Name: Yvonne
Last Name: McKay (sample)
Parent Customer: Variety Store (sample)
Account Name: Variety Store (sample)
Primary Contact: Yvonne McKay
Upon importing the Account.xml and the Contact.xml sample data files, the system makes sure that the contact Yvonne McKay (sample) and the account Variety Store (sample) are linked.
Please keep in mind that for the linking to work, the contacts First Name concatenated to its Last Name must be the exact same string as the account’s Primary Contact field. Similarly, the Account Name needs to be the exact same string as the contact’s Parent Customer.
If you create sample data with date fields (such as Actual Close Date for Opportunities), your sample data will look stale if you install it much later compared to when it was created. To make sure your data always looks fresh, no matter when it is installed, you can use dates relative to the time of install. For example, if you want the Actual Close Date of your sample emails to be relative to time of install, you need to do the following:
1. Download the opportunity.xml template as described in the Creating Custom Sample Data for CRM 2011 blog post (follow steps 1 through 4). Then add the following 6 columns to the end of the opportunity.xml file (I prefer using Microsoft Excel for editing sample data files):
2. Now you need to create data that has relative start dates. For each opportunity record, enter appropriate values in the Delta Actual Close columns so that the records are relative. For example, the following opportunity’s close date will always be one year in future from the time when the sample data is installed.
3. You now need to create a map that will allow you to use these columns to make the actual close date of your opportunities relative. To do so, import the opportunity.xml file into your CRM system - navigate to Workplace > Imports and click on Import Data on the ribbon, select the opportunity.xml file and click Next. You will see the following screen, in which, change the CRM Fields dropdown to Show Unmapped:
4. Change each field’s drop down to Ignore and click Next:
5. Click Next on this screen:
6. Type in the Data Map Name. In this example, I will call my map RelativeOpportunityMap, and click Submit:
You have just created a basic map for importing opportunities that you will now modify to take relative actual close dates into account.
7. Download the RelativeOpportunityMap by clicking Settings > Data Management > Data Maps, selecting it, and clicking Export (after exporting, delete it from CRM):
8. Save the RelativeOpportunityMap on your desktop. You now need to modify this map so that it adds the values from the 6 Delta Actual Close Date columns to the system time to calculate actual close dates for your sample opportunities. The import infrastructure allows you to add the delta values to the system date by using the Microsoft.Crm.Transformations.AddToCurrentDate transformation in your map. When given the 6 Delta Actual Close Date columns as inputs, and actualclosedate (the actual close date field of an opportunity) as the output, this transformation will add the delta values to the current system date and set that as the actualclosedate for the sample opportunity. To add this transformation to the RelativeOpportunityMap, open it using an XML editor (even though I use Microsoft Excel for editing the sample data files, I prefer using Microsoft Visual Studio for editing the map files). Add the following node as a child node of the EntityMap node:
<TransformationMaps> <TransformationMap> <TransformationTypeName>Microsoft.Crm.Transformations.AddToCurrentDate</TransformationTypeName> <ProcessCode>Process</ProcessCode> <InputParameterMaps> <SingletonInputParameterMaps> <SingletonInputParameterMap> <ParameterSequence>1</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Year</Data> </SingletonInputParameterMap> <SingletonInputParameterMap> <ParameterSequence>2</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Month</Data> </SingletonInputParameterMap> <SingletonInputParameterMap> <ParameterSequence>3</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Day</Data> </SingletonInputParameterMap> <SingletonInputParameterMap> <ParameterSequence>4</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Hour</Data> </SingletonInputParameterMap> <SingletonInputParameterMap> <ParameterSequence>5</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Minute</Data> </SingletonInputParameterMap> <SingletonInputParameterMap> <ParameterSequence>6</ParameterSequence> <DataTypeCode>Reference</DataTypeCode> <Data>Actual Close Second</Data> </SingletonInputParameterMap> </SingletonInputParameterMaps> <ArrayInputParameterMaps /> </InputParameterMaps> <OutputParameterMaps> <OutputParameterMap> <ParameterSequence>1</ParameterSequence> <Data>actualclosedate</Data> </OutputParameterMap> </OutputParameterMaps> </TransformationMap> </TransformationMaps>
9. Now that you have modified the map to calculate actualclosedate by using a transformation, you need to remove the original mapping for it. Find the following node in the RelativeOpportunityMap and change its ProcessCode from Process to Ignore and delete actualclosedate from its TargetAttributeName:
<SourceAttributeName>Actual Close Date</SourceAttributeName>
<TargetAttributeName> actualclosedate </TargetAttributeName>
<SourceAttributeName> Actual Close Date</SourceAttributeName>
10. Save and Close the RelativeOpportunityMap file
11. Import the RelativeOpportunityMap file into CRM from Settings > Data Management > Data Map
12. Select the RelativeOpportunityMap file and click ok:
13. Congratulations! You have successfully created and imported the map that will allow you to install opportunities with relative actual close dates.
14. You are now ready to import the opportunity.xml file to your CRM system. To do this, go to Workplace > Imports > Import Data. Select opportunity.xml as the data file name and click Next until you see the following screen:
15. Click Back on three screens in a row, starting with this screen:
16. You will see the following screen. Select RelativeOpportunityMap and click Next:
Keep on clicking Next until you see Submit
17. Navigate to Sales > Opportunities to check that opportunities with relative dates have been imported into the system.
Things to keep in mind:
There are a few things you need to keep in mind as you are creating sample data. The following list is not exhaustive, but contains some of the issues you may face as you create and import sample data for your system:
- You cannot import the lookup field for a N:N (Many to Many) relationship.
- If records in a source file contain multiple values in a field that contains relationship data, edit the data so it contains one value, otherwise data import will fail.
- Verify that required data in your source files exists. For example, Account Name is a required field for Account, and therefore must be included in Account sample data.
- If your source file contains columns that do not map to existing fields, create a custom field as part of the import process using the Import Data Wizard.
- To avoid import errors, verify that all date values are in the correct format before starting the Import Data Wizard. Each date must use the format YYYY-MM-DD.
- Verify that data in the status and status reason columns matches valid values in Microsoft Dynamics CRM.
I hope that this blog helps you create a pretty rich set of sample data using the techniques described above to meet all your needs.