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: 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: 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: 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: 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: This is the enabling formula that allows us to generate meaningful data. Consider the following worksheet: 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: Similar formulas on the various columns now provide the start to a random set of Case data, drawn from the following lists: In order for the data set to be more realistic, we can add a better distribution to some of these lists, for example: The same formulas now yield a better distribution: 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.
- Synchronous Rollup Fields and Routing Rules, Anyone? 24 February 2015
- Generating Sample Data for Dynamics CRM – Chapter 1 5 February 2015
- Rollup Fields for Meaningful Account Hierarchies 23 December 2014
- Opportunities: Calculated Fields for Cool Views and Maps 6 December 2014
- Follow Crafting the CRM Experience on WordPress.com
- 1,720 hits