How to Configure the Synthetic Test Data Configuration Sheet

Search Knowledge Base by Keyword

< Back

This section provides several example-based walk-throughs of a Sample Commerce Database (used throughout this article) model. These examples shows  the correlation between the Application Database Tables and the Configuration spreadsheet as well as how to design/set the spreadsheet so as to generate the test data that is required for your application.

If we look at our Sample Commerce Database model on the left side of the screen, we see that the database contains tables for Customer, Order, OrderItem and Product (found under SampleCommerce on the left side of screen).

If we look back at our configuration sheet under Tables tab:

We see that we have a table for each of the Sample Commerce records: Customer, Supplier, Order, OrderItem and Products. These are the tables for which we are going to generate data.

If we look at the Mapping tab:

We can see the relationship of the records to each other. So in the table above, we see that

1. a Customer (Parent table, column A) can have an Order (Child Table, Column B),

2. an Order (Parent Table, Column A) has OrderItems (Child Table, Column B),

3. That a Supplier (Parent_Table, column A) is associated with a Product (Child Table, Column B). Note that a Product is not related to a Customer or an Order, and an OrderItem is not related to a Supplier.

Let’s look at the Customer Table first:

If we want to generate some Synthetic Customer data,  we would proceed as follows:

You can see that each Customer record has a First Name, Last Name, City, Country and Phone number.

First notice that when the column field for FunctionParameter is selected, the excel function value is displayed in the top bar. So for First Name, the value in the function bar near the top of the screen is =VIP.Name.FirstName.

The FunctionParameters (column D above) which connect to VIP functions can be added using (excel support) auto-completion from a list by typing the first few letters of the parameters you want to include and using autofill to select the full name as shown in the image below.

For a complete list of VIP Function and Parameters see the Functions List.

Column Names for each Table

  • Table_Name
    • The Table Names themselves are taken from the Database, so they cannot be changed in the Configuration sheet, but they can be removed or accessed by functions if the user so chooses.
  • Column_Name
    • These are the names listed in the DB table.
  • Function
    • The functions are pre-defined and can be displayed using auto completion. See the functions table for a complete list  Additionally, if the user wants to execute an SQL command, that can be specified in this field.
  • FunctionParameters
    • The Function Parameters are displayed when browsing the list of functions.
  • FixedValue
    •  Sets the Object to one value. Example: Age=22
  • DataSource
    • Generally not used
  • ORDINAL_POSITION
    • Default set to 0
  • COLUMN_DEFAULT
    • This is metadata information and used for easy reference
  • IS_NULLABLE
    • Default set to NULL
  • DATA_TYPE
    • Set to the Data Type  specified in the Database.
  • CHARACTER_MAXIMUM_LENGTH
    • Specifies the length of the Data Type string.  Set by default to 40. While generating the model VIP gets these details. The user can reduce it to generate smaller values e.g. instead of generate string of Length 100 user can generate string of Length 50 by changing CHARACTER_MAXIMUM_LENGTH to 50
  • NUMERIC_PRECISION
    • Number of digits
  • NUMERIC_SCALE
    • Scale of decimal like Maximum length for string
  • WRAP_IN_EXPRESSION
    • This feature can be very useful. This allows for the generated value of FunctionParameters to be wrapped in an expression so that in the example System.Convert.ToInt32(_VALUE_), the function parameters generated VALUE is converted to an Int. Other useful expressions are _VALUE_.Trim() – to trim the string value etc.

For example, if we want to generate synthetic customers, we use functions that generate each of the constituent parts of a Customer – First Name, Last Name, City, Country and Phone Number.   When the .vip file + configuration spreadsheet are executed (details in the final section of the documentation), you will see how the Synthetic Data that is generated.

It is worth noting again that the generated data, although randomly generated, is taken from real data for each application category – Customers, Orders, Products, etc.  In other words, in the Customer First Name category, there are first names generated that are actual names, not a random set of letters that pretend to be names which is a realistic feature of VIP Synthetic Data Generation.

Sample Commerce Examples

For the following detailed examples in the Sample Commerce application, please see the examples page:

  • Order Time Example
  • Order Number Example
  • Total Amount Example