To test the performance of a database, first get some data. There are two alternatives to keying in a large volume of test data. First, existing files can be used as a source of test data and imported into appropriate tables. Second, test data can be generated for each table. Here, we discuss the use of Excel to generate test data. This exercise assumes considerable Excel experience.
The RAND function is very useful for generating random test data because it computes an evenly distributed random number r in the range 0 <= r < 1.
When combined with the ROUND function, RAND can be used to generate random integers. The syntax for round is ROUND(value, digits), where value is the number to round and digits specifies the number of decimal places of the rounding (i.e., ROUND(5.4,0) equals 5). To generate an integer random number between a and b, use: ROUND(RAND()*(b-a) + a, 0)
Excel stores a date as an integer, and days since some starting point are numbered sequentially. Thus to generate random dates in a year, you can add a random integer between 1 and 365 to the last day of the previous year. For example to generate a random date in 1997, enter the following in the designated cells:
|2||=ROUND(RAND()*364+1,0) + A1|
If A2 is not a date, you need to set the format of the cell (Format>Cells ...)
Imagine that a company has locations in three cities (Atlanta, New York, and Chicago) and sales are distributed, respectively, in the following proportions: .4, .3,.3. To randomly select city names to match this distribution do the following.
Sometimes, you may want to select two values from a list (e.g., the description of an item and its price). VLOOKUP can be used as before. Consider the case where a company sells 10 items and the following price list is established. Here we would name two arrays: ITEM A1:B10 and PRICE B1:C10
Boots - snakeproof
Elephant polo stick
|5||5||Exploring in 10 Easy Lessons||20|
Geo positioning system
Hat - polar explorer
How to Win Foreign Friends
To randomly select an item from ITEM, use:
Now, another lookup is required to match the selected item to its price. Assume the selected item (e.g., Hammock) is stored in cell X100, then the lookup command would be:
This would look for the value of X100 (i.e., Hammock) in the first column of PRICE and return its price, which is stored in the second column.
Generate data for the following table:
|Date||In the range 1-Jan-98 to 31-Dec-98|
|Sales quantity||In the range 1 to 8|
|Location||Either Paris, London, or Singapore in the proportions .4,.4, .2 respectively|
|Item||One of five possible items (all equally likely)
|Price||Respective prices of the above items are:
|Type of sale||Catalog or store in a 3:1 ratio|
It is a good idea to create two worksheets.
This page is part of the promotional and support material for Data Management (sixth edition) by Richard T. Watson