Generating Sample Data for Dynamics CRM – Chapter 1

If you work in presales, like I do, you may want to showcase the self-service BI capabilities of Dynamics CRM, or use PowerBI for even richer visual exploration of data. The problem is: quite often the sample data in our demo environments do not provide enough volume or richness to allow for great visualisation and analysis. So, let’s invoke that trusted companion Microsoft Excel to generate some rich data – in this series of posts, we will eventually generate a large volume of Cases for existing Contacts, with a good distribution of Dates, Case Types, Case Origins, Priorities, Subjects and Current Statuses. To do this, we start by investigating the technique for generating a random selection from a list in Excel – let’s capture a list of values for Priority, and to make it easy to refer to this List, we turn it into a Named Range by selecting the entire column and then adding the range name on the top left: samOne 100 We can now use Excel functions against this list, for example this formula will yield the count of the number of cells in the Named Range “Priority” that are not empty. Use =COUNTA(Priority) as shown: samOne 200 A useful helper function returns the value at a specific position in the range, in this case the value at the 2nd position, i.e. “High”. Use =INDEX(Priority,2) as shown: samOne 300 The last helper function we will need generates a random (integer) number between two values, in this example it would randomly generate one of 2,3 or 4. Use =RANDBETWEEN(2,4) as shown: samOne 400 Every time the spreadsheet is recalculated, a new random value will be generated. We are now in position to select a random value from this list, by combining formulas. Use =INDEX(Priority,RANDBETWEEN(2,COUNTA(Priority))) as shown: samOne 500 This is the enabling formula that allows us to generate meaningful data. Consider the following worksheet: samOne 600 The Contact column is generated from the data in column A of this worksheet, exported from CRM. Use =INDEX(Contact,RANDBETWEEN(2,COUNTA(Contact))) as shown: samOne 700 Similar formulas on the various columns now provide the start to a random set of Case data, drawn from the following lists: samOne 800 In order for the data set to be more realistic, we can add a better distribution to some of these lists, for example: samOne 900 The same formulas now yield a better distribution: samOne 1000 The Case Number and Title fields shown above were populated with these formulas: =”CAS”&”-“&RANDBETWEEN(1000,9999)&”-“&RANDBETWEEN(100,999) =E2&” logged by “&C2. In the next chapters we will look at expanding this with additional useful columns and a few other techniques, but this here is really the basis for generating good quality sample data.

Advertisements
This entry was posted in Sample Data. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s