Sometimes you cannot use existing data and you have to generate new data. The generation module now supports generating new rows inside existing tables. 

The Generation section allows the user to create and configure different generations sets that generate new data. A generation set is a specific set of tables that can be configured to generate data that covers specific test data requirements. For example; 10 female customers between the age range 45 - 65, with specific contracts on specific products. 

When executed, each generation set will generate its rows into a temporary table. After all temporary tables of the generators sets are filled, the data will be inserted into the original table. 

To add a new generation set, click the Add generaton set button in the toolbar. This will create an empty generation set. You can change the name and add a functional description. 

After adding a generation set, you can specify which tables you want to configure. You can expand the tree view to see which tables are part of the generation set. 

Table settings

To specify how many rows you want to generate, select the specific table and change the Rows value. By default there will be 100 rows generated. You can also specify if the table should be truncated. Please note that when you generate data for the same table in different generation sets, you should uncheck the Truncate checkbox. 

Cloning generators sets and copying generator specifications

Specifying different generators on a lot of columns and tables can be time consuming. Luckily you can easily clone generation sets that you can than change for a different purpose.  Select the generation set and click the Clone generation set button. Each table and their generator properties are copied to the cloned generator set. Make sure to update the primary key columns to prevent duplicate keys. 

There is also an option to copy the generator specification of a table or column and paste it in a different generator set. Right click a table or column and select Copy generator specification, then right click a target table or column and click Paste generator specification. If you copy the complete generator specification of a table, the table on which you can paste it should contain the exact same columns. Otherwise the paste option is greyed out. 

Foreign Key 

There are different methods to fill foreign key values! You can choose a constant if you want to refer to a specific value in the parent table. In that case you have to make sure that the referring key is present in that table. The Foreign Key generator however uses a lookup to actually get values from the parent table. There are two possible scenarios;

  • The parent table is part of the same generation set; in this case keys are generated that refer to the new parent records generated in the same generation set

  • The parent table is not part of the same generation set; in this case keys will be generated using current data content in the parent table

Random versus Deterministic Masking

All generators that use seed lists (for example; First name, Last name, Company, Job, User-Defined), can be randomly or deterministic replace the original values. When replacing original values in Random mode, each value will be replaced with a random value from the seed list. When the Replace the same original values with the same generated value is checked, it will make sure that multiple occurrences of the same original value, will be randomly replaced with the same seed value. 

In Deterministic Mode each occurrence of the original value will be replaced consistently with the same value. Each time that you execute the masking template the value will be masked consistently. When masking for example names in Oracle and SQL Server with different templates, you can achieve consistent results by using the same seed list(language), generator salt and global salt without the use of any translation tables. 

If you change the global salt in the Deployment - Settings this will affect all generators that are configured as deterministic. For each individual generator you can also specify a separate salt. 

About Generators

The Generator options displayed in the drop-down list will reflect the datatype of the column in focus for masking.  For instance, an Integer datatype will display the following available techniques:


Standard Generator Functions

The available generators as provided by Privacy differ slightly based on the data type of the underlying column. Because there are (database specific) variations on data types, we’ll be grouping these together in a few groups. Strings(all data types focusing around text, from varchar fields to BLOBs), numerical and date & time data types.

Function name

Supported Data Types

Description

Constant Value

String, Numerical, Date & Time

Creates a constant value in the generated dataset that is identical for every row.

I.e. inputting MyFavoriteValue here will generate ‘MyFavoriteValue’ for every row in the resulting column’s dataset.

Random Date/Time

String, Date & Time

Generates a random Date/Time value per row between a specified minimum and maximum datetime that corresponds with the underlying column’s datatype.

Sequential Date/Time

String, Date & Time

Identical to the Random Date/Time generator except that this generator creates a sequential datetime for every row. Supplying a maximum datetime is optional.

A number to increment the starting date is required, and can only accept whole numbers. Any unit of time to increment by can be chosen, from seconds to years.

Random Decimal Number

String, Numerical (except for integer variants)

Generates a random decimal number between a supplied minimum and maximum value for every row.

Using the Scale setting the decimal accuracy can be defined. For instance, a generator with a minimum value of 0 and a maximum value of 1000 using a scale of 4 might generate 132.4202.

Random Whole Number

String, Numerical

Generates a random integer between a supplied minimum and maximum value for every row.

Random String

String

Generates a random string of lower- and uppercase letters for every row. The minimum and maximum length for strings can be defined.

Sequential Number

String, Numerical

Generates a sequential number for every row that starts at a specified value, and increments by the step value per row.

Additionally, you can define a Padding for your generated integers. This is a set number that will be affixed to the generated integer. For example, using a padding of 3, and a start of 8 with a step of 2 will generate the following:

008 → 010 → 012

Names

String

The Names section contains multiple generators that generate data based on CSV files supplied by DATPROF.

The available options are:

  • Brand

  • First Name

  • First Name (Male)

  • First Name (Female)

  • Last Name

  • Full Name

  • Company

  • Dinosaur

  • Random Word

For some options (such as First Name) there are localization options which determine which CSV file is used. These can be toggled on/off with the language(s) drop-down menu. Multiple languages can be used simultaneously.

2 Letter Country Code / 3 Letter Country Code

String

Generates a random country code per row either in a 2 letter country code, or a 3 letter country code format.

City

String

Generates a random city name per row. Here, you can specify for which countries you’d like to generate random names using the Countries drop-down menu.

Country

String

Generates a random country name per row. The Language(s) option specifies in which language the country will be written. Multiple options can be enabled simultaneously.

Street

String

Generates a random existing street name per row. The Countries option allows you to specify for which country street names will be generated.

A-Number / GBA number (Dutch township security number)

String, Numerical

Generates a 10 digit GBA number per row.

BSN

String, Numerical

Generates a valid Dutch social security number per row.

Color

String

Generates a random color per row. (Ex. Baby Blue, Sky Blue, Soft White)

Color Code

String

Generates a color hexcode (Both three-digit shorthand and six-digit full length hexcodes) per row.

Credit Card Account Number

String, Numerical

Generates a random credit card account number. Using Issuer(s), you must define one or multiple issuers to determine which syntax the generated account numbers adhere to.

Currency Code

String

Generates a three letter currency code for every row.

Currency Symbol

String

Generates a currency code for every row.

Genre

String

Generates a media genre for every row.

IBAN (International Bank Account Number)

String

Generates a valid IBAN number for every row. Using Country Code(s) you can specify which country codes you’d like your resulting IBAN codes to use.

Job / Profession

String

Generates a profession for every row. Using the Language(s) you can specify which language(s) you want your resulting job names generated in.

Military Rank

String

Generates a military rank name for every row. Using Department(s) you can specify which branches of the armed forces you’d like to include in your resulting dataset.

SSN (US Social Security Number)

String

Generates a SSN for every row. You can specify how you want to separate your resulting numbers.

You can choose one of the following formats:

None (Example: 101010101, 003122142)

Dashed (Example: 101-01-0101, 003-12-2142)

Spaced (Example: 101 01 0101, 003 12 2142)

User agent

String

Generates browser user agents like Mozilla/4.0 (compatible; MSIE 5.13; Mac_PowerPC), Opera/8.53 (Windows NT 5.2; U; en) per row.


Advanced Generator Functions

SQL Expression

The SQL expression generator is special in that it can use values from other columns. All other generators will generate it's values into a temporary table per generation set inside your database. The SQL expression on a column is used when the data in inserted back into the original table. That way a user can use the data that is generated in other columns.  No dependency specification is required since SQL Expression rules will be the last to be applied to the table in focus.

Value from seed file

Sometimes you may wish to provide your own replacement data to the masking process.  This could be because of language, country/region or specific business reasons.  You can generate your own seed (User Specified) file and specify it using this technique.  The generator will randomly source a row from the file for each row in the table which is qualified by the Condition clause.

Notes on Seed files

  • Seed files are generally saved as .txt files.

  • The Encoding must be explicitly specified.  The rule default is UTF-8.

  • An empty row in the seed file will randomly populate the column in focus with an empty value.

Value from multi-column seed file

Generate data across multiple columns that should be consistent based upon a multi column seed file. For example; replace a city, postalcode and street based on correlated seed file containing addresses. You can map multiple columns of the table to the specified seed file. This file shouldn't contain headers and must be a comma separated (.CSV) file.

Generator expression

Combine different generators together with static text. Each generator can be referenced with the $ symbol. Entering the $ symbol in the expression editor will open a list of available generators. Pick one from the list; dependent upon the generator you will be presented with options to specifiy the generator paramaters. 

Some examples are: 

Email address

$random-regex(regex="[A-Z]{1}").$random-last-name("US")@$weighted-random-generator(values="gmail.com,http://hotmail.com ",weights="30,70")

This will generate:

P.Sabella@gmail.com, O.Veach@hotmail.com, R.Levy@hotmail.com, etc

Generator

Generator expression syntax

Random Date/Time

$datetime-generator(min="1970-01-01 00:00:01",max="2038-01-19 03:14:07",format="yyyy-MM-dd HH:mm:ss")

Random decimal number

$random-decimal(min="0.0",max="1000",precision="0")

Random whole number

$random-integer(min="0",max="1000")

Random string

$random-string(min="5",max="20")

Sequential number

$sequential-number-generator(start="0",step="1")

Brand

$random-brand()

Company

$random-company("NL","DE")

Female first name

$random-first-name-female("IT","DA","DE")

Male first name

$random-first-name-male("ES")

Last name

$random-last-name("IT","DE","NL")

2 letter country code

$country-code-2()

3 letter county code

$country-code-3()

City

$random-city("NL","DE","US")

Country

$random-country("DE","FR","NL","US")

Street

$random-street("DE","NL","US")

A-Number/GBA Number

$gba-generator()

BSN (Dutch Social Security Number)

$bsn-generator()

Color

$random-color()

Color code

$random-color-code()

Credit Card account number

$credit-card-number(issuers="Maestro")

Currency code

$random-currency-code()

Currency symbol

$random-currency-symbol()

Genre

$random-genre()

IBAN

$iban-bic(countries="NL")

Job / Profession

$random-job("US")

SSN (US Social Security Number)

$ssn-generator()

User Agent

$random-user-agent()

Regular Expression

$random-regex(regex="[A-Z]{1}")

Value from seed file

$random-seed-value(file="C:\file.csv",charset="UTF-8")

Weighted list

$weighted-random-generator(values="F,M",weights="50,50")

Regular expression

Generates values based upon a regular expression.  Some examples are:

Title

Regular Expression

Address Type

Pick a random value from a list

Billing|Delivery|Home|Office|Primary|Shipping

Dutch Postal Code

3067ZG, 9372ED, 8423BE

[1-9][0-9]{3}[A-Z]{2}

US Zip Code

48357/4986 , 19414, 21237-6415

[0-9]{5}([- /]?[0-9]{4})?

IP address

250.171.5.8, 4.8.5.180, 91.252.251.253

((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])

For a full specification of the regular expression possibilities, check https://www.brics.dk/automaton/doc/index.html

Weighted list

Generates values based on user specified, comma separated, values.  The weights parameter is a comma separated list which gives each value a proportional weighting. For example:

Values: Male, Female

Weights: 60, 40

In this case 60% of the values that are generated are Male and 40% of the generated values are Female.

Generator settings

Post SQL Expressions

You can further modify data on a generator set by filling in the Post SQL text box on any generator function. This expression will trigger after a field has been generated, and can be used to further customize the generated data. You can also reference different fields in the table and use them in the expression here. In terms of functionality, the syntax is identical to regular SQL Expressions.

In this example we use the “Random Word” generator on our field named “word”. An expected result for this query could be “Magnanimous_04-10-2022”

Generator Seed

If you generate data, and need the data to be identical upon later execution, you can specify a seed value. Doing this will ensure that whenever you run this generator set again, your data will be identical. This can be useful if changing data can impact testing in a negative way. However, if you intend to execute a template multiple times on the same dataset, duplicate data will be generated.

Null Values %

Using this setting you can seed a percentage of NULL values into your resulting dataset.

Having NULL values can be an important part of testing, as missing data occurs regularly in production databases. However, if you specify a field as having a NOT NULL constraint in the database, attempting to seed NULL values in the resulting dataset will cause a run to fail.