Data multiplier and data bulking from Grid-Tools

Generate hundreds of millions of referentially correct rows in minutes!

Datamaker™ allows you to generate very rich sets of data directly into your database. Once you have created the "data shapes" you need, you can then bulk up the data using the data multiplier script. This method is very useful when you wish to explode transactional data for performance testing - you would not use it with reference tables such as 'customer' or 'products'. However, you would use it to build a 100 million Orders and Items.

The data multiplier script contains SQL statements which can be run directly against the database to double the amount of data each time. So, if you have generated a million rows you could then double this to 2 Million, then 4 Million, then 8, then 16, then 32 and then 64 Million.  By running the script 5 times you will create an extra 63 Million rows.  This method allows you to create data very quickly as there is no external file activity to slow the creation.

The generated script needs to be saved and run against the database. The script will handle assigning new IDs and creating rows with referentially intact relationships. You can also add in defined Function Maps to the generated scripts. These functions maps allow you to add in extra randomization or data conditioning to the multiplied data.  You could, for example, quickly create many customers based on your existing customers but assign new random names and addresses to the new customers.

To build the script choose or create a Test Case with the transactional tables you need. Make sure that the table relationships have been defined and that there is one row of data containing ∼NEXT∼ and ∼PARENT(1)∼.  Click on publish to file and then choose Data Multiplier Script.

A script will be created, for example:

The script will vary from database type to database type. The above example is an Oracle script that should be run using SQLPLUS. Other database types will create temporary tables to identify the increment to any primary key columns.

The generated script will calculate the difference between the minimum and maximum values of a key column. So, for example, if ORDER_ID had a minimum value of 10 and a maximum of a 100, the difference would be 90. The INSERT clause would select the existing Orders but add 91 to the ORDER_ID, so you would get values of 101 to a 191 added. The ORDER_ITEMS would similarly have the LINE_ITEM_ID incremented. However, the ORDER_ID would be incremented by 91.

The result of this is that the new orders are created with a new set of order_lines. The new Orders & Order_lines would refer to the original products and have exactly the same data apart from the Primary keys and any key values linking the data.

If you were running a query on Products, you would now have twice as many orders for the product. So as long as you have created a rich set of order types for each product, you can then bulk up the number of orders using this technique.

This is an example of a script output:

Here is an example of a SQL Server data bulking output:

 

Back to the top