Skip to content
CA Test Data Manager - 4.3
Documentation powered by DocOps

Understand Data Multiplier and Data Bulking

Last update November 30, 2015

The Datamaker UI allows you to generate rich sets of data directly into your database. After you create the data shapes you need, you can add more data (data bulking) using the Data Multiplier script. This method is useful when you want to increase transactional data for performance testing. You would not use this method for reference tables, such as customer or products, though. This method is, however, useful for building a 100 million orders and items, for instance.

The Data Multiplier script contains SQL statements that you can run directly against the database to double the amount of data each time you run it. So, if you have generated a million rows, you can double this number to 2 million, then to 4 million, and so forth. Therefore, by running the script 5 times (for example), you can create an extra 63 million rows. This method allows you to create data quickly as there is no external file activity to slow the creation.

Save and run the generated script against the database. The script handles assigning new IDs and creating rows with referentially intact relationships. You can also add defined Transformation Maps to the generated scripts. These function maps allow you to add extra randomization or data conditioning to the multiplied data. You can, 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, follow these steps:

  1. Select or create a test case with the transactional tables you need. Ensure that the table relationships are defined and that one row of data containing ~NEXT~ and ~PARENT(1)~ exists.
  2. On the publish dialog, select the Data Multiplier option from the drop-down list in the top-right corner.
  3. Click the forward arrow icon next to the drop-down list.
    A script is created. 

An example of a script that is generated is as follows:

-- SQL Data Muliplier Script - 2009/09/02 - 11:43:00
spool data_multiplier

whenever sqlerror exit

COLUMN ORDER_ID_ORDERS__RANGE NEW_VALUE ORDER_ID_ORDERS__MOD
BREAK ON ORDER_ID_ORDERS__RANGE
SELECT MAX(ORDER_ID) - MIN(ORDER_ID) + 1 ORDER_ID_ORDERS__RANGE FROM ORDERS;

INSERT INTO ORDERS (
         ORDER_ID,
         ORDER_DATE,
         ORDER_SHIPPED_DATE,
         ORDER_STATUS_CODE,

         OBJECT_VERSION_ID,
         OBJECT_NOTES )
SELECT ORDER_ID + &ORDER_ID_ORDERS__MOD,
         ORDER_DATE,
         ORDER_SHIPPED_DATE,
         ORDER_STATUS_CODE,

         ORDER_TOTAL,
         OBJECT_VERSION_ID,
         OBJECT_NOTES
FROM ORDERS;
COMMIT;

SELECT to_char (COUNT(*)) || ' ORDERS rows' ORDERS_COUNT FROM ORDERS;
COLUMN LINE_ITEM_ID_ORDER_ITEM__RANGE NEW_VALUE LINE_ITEM_ID_ORDER_ITEMS__MOD
BREAK ON LINE_ITEM_ID_ORDER_ITEM__RANGE

SELECT MAX(LINE_ITEM_ID) - MIN(LINE_ITEM_ID) + 1 LINE_ITEM_ID_ORDER_ITEM__RANGE
FROM ORDER_ITEMS;

INSERT INTO ORDER_ITEMS (
           ORDER_ID,
           LINE_ITEM_ID,
           PRODUCT_ID,
           QUANTITY,


           LINE_TOTAL,
           OBJECT_NOTES )
SELECT ORDER_ID + &ORDER_ID_ORDERS__MOD,
LINE_ITEM_ID + &LINE_ITEM_ID_ORDER_ITEMS__MOD,
           PRODUCT_ID,
           QUANTITY,


           LINE_TOTAL,
           OBJECT_NOTES
FROM ORDER_ITEMS;
COMMIT;


SELECT to_char (COUNT(*)) || ' ORDER_ITEMS rows' ORDER_ITEMS_COUNT FROM
ORDER_ITEMS;
EXIT

The script varies from database type to database type. This example is an Oracle script that you must run using SQLPLUS. Other database types create temporary tables to identify the increment to any primary key columns.

The generated script calculates the difference between the minimum and maximum values of a key column. For example, if ORDER_ID has a minimum value of 10 and a maximum of a 100, the difference is 90. The INSERT clause selects the existing Orders, but adds 91 to the ORDER_ID, so you get values of 101 to 191 added. The ORDER_ITEMS similarly has the LINE_ITEM_ID incremented; however, the ORDER_ID is incremented by 91.

The result of this is that the new orders are created with a new set of Order_Lines. The new Orders and Order_Lines refer to the original products and have the exact same data apart from the primary keys and any key values linking the data.

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

The following segment includes an example of a script output:

    
data_multiplier.LST
ORDER_ID_ORDERS__RANGE
----------------------
482
old 26: SELECT ORDER_ID + &ORDER_ID_ORDERS__MOD,
new 26: SELECT ORDER_ID + 482,
423 rows created.
Commit complete.
ORDERS_COUNT
-----------------------------------------
846 ORDERS rows
LINE_ITEM_ID_ORDER_ITEM__RANGE
------------------------------
670
old 14: SELECT ORDER_ID + &ORDER_ID_ORDERS__MOD,
new 14: SELECT ORDER_ID + 482,
old 15: LINE_ITEM_ID + &LINE_ITEM_ID_ORDER_ITEMS__MOD,
new 15: LINE_ITEM_ID + 670,
646 rows created.
Commit complete.
ORDER_ITEMS_COUNT
----------------------------------------------
1292 ORDER_ITEMS rows
Was this helpful?

Please log in to post comments.

  1. Brian Weissman
    2017-11-16 10:24

    A nice addition to this page would be a list of supported DB types. Thanks!