How to Configure the Synthetic Test Data Configuration Control Sheet

Search Knowledge Base by Keyword

< Back

This section describes how to Configure the Test Data Generation Configuration Control sheet that will be used to create the VIP Flow which will be used to execute the Data Generation. This is explained below through a set of SampleCommerce Database examples.

Overview of the Spreadsheet

At this point in the process  (assume completion of the previous section : Generate VIP Model and Configuration sheet), an ExternalEntityAssemblies folder has been  created in the following folder:

C:\Users\USERNAME\AppData\Roaming\VIP\ExternalEntityAssemblies\SampleCommerce.

In that folder four files will be generated, but we are only interested in the following two files:

  1. The model that VIP will use – dll
  2. Configuration spreadsheet – xlsx

Next do one of two things:

  • Move both the .dll and .xlsx files to a folder of your choice in preparation for configuration (this step is not mandatory):
  • Generate a VIP flow (after the Configuration sheet has already been configured, not for the first time)

Configuration

This section aims to familiarize you with possibilities in the Configuration Control sheet by showing you how we configured a sample sheet for the SampleCommerce DB.

First, Open the Configuration Control spreadsheet.  Next we will fill in the details for the Configuration Table which you can find by looking at all the tables on the bottom of the window.

Select the Configuration Table.

Configuration Table

Complete this table by completing the following details:

Getting Familiar with the SampleCommerce Configuration Control Sheet

You can see that there are sheets for each of the tables in the SampleCommerce database. Those are  Customer, Order, OrderItem, Product and Supplier.  There is also a Country sheet which is not really needed as Country associated with a Master Table*. There is a lot of information here related to the Database including SoftKeys, Foreign Keys etc.  Things in the Database can be changed directly by making changes in the Configuration sheet.

*A Master Table is any category which does not need data generation. A Category that already has data e.g. Country or Currency – list is fixed.

Now if we look at the Mapping tab, we will see the following:

In VIP a pairing is a specified relationship between two tables – in the case above, it is a Parent-Child relationship. Note that there should only be one pairing at a time.  So in this example,  the line specifying the relationship between the Product and the OrderItem (fourth line) should be removed because the line below  links the Supplier with the Product which in turn is also linked to OrderItem.

Since we are not using Country Table as it is a Master Table, this can also be removed.

Customer Tab in the Configuration spreadsheet

Customer Table in SQL Database

Note: In general, if there is an id Column, simply remove it in the Configuration sheet. Even if you are using PCP ( Patch Creation Properties File), it’s Ok to remove.

Using Data Generation Functions in the Control Spreadsheet

This section shows how to use VIP functions rather than DataGen functions to configure the sheets.

Data Gen Functions are Excel functions which immediately generate data in Excel. Not for configuring the Control Spreadsheet.

VIP Functions give an expression that can be resolved by VIP and hence should be used when configuring Data Gen Control spreadsheet.

Example in OrderItem sheet: In our SampleCommerce DB example, we can use a Random Function to generate any Quantity.

Example in Supplier Sheet: Use the VIP function, Random Function to get the Company Name

Now, look at the Product tab:

Remove the line containing id as it’s not needed.

  Note: In the video some Errors are left in the Configuration spreadsheet to demonstrate how to fix those in a later section. We also cover these in the Fixing Errors section.

More Detailed Look at the Configuration Sheet for SampleCommerce

This section provides several example-based walk-throughs of a Sample Commerce Database model.

In the figure above, if we look at the Database model under SampleCommerce using SSMS Object Explorer, we see that the database contains tables for Customer, Order, OrderItem, Product and Supplier (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:

What follows is an example of creating some synthetic data for SampleCommerce by first looking at Customer Records.

 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 auto-fill 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 later 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 Configuration Control sheet Examples

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

  • Order Date Example
  • Order Number Example
  • Customer Id Example