To begin, we need to generate a VIP Model and a Test Data Configuration Sheet. This is performed by following these steps:
Go to Register Model Menu
The Model Registration Process registers the Database so the system knows about Schemas, Tables*, Column keys and Default values. It also provides connection details, captures metadata and creates a Control Spreadsheet.
*Models for ALL tables will be generated, but it is possible to be selective about which tables to use.
Select Add Database Model (you can also add CSV, excel, SOAP, XML, Json and Odata Models)
In the main window, complete the following fields:
Specify a Model Name
This is the name of the Model which will be used in the Configuration Sheet and VIP flows.
Specify a Database Type
Database types are: SQL Server, Oracle, My SQL, Postgres, MS Acess.
Specify a Server
Localhost or the full qualified server instance name. ‘Note: ‘.’ can be used to refer to the localhost.
Specify a Database
Specify the name of the Database to use and select whether Integrated Security needs to
be used rather than Username and password.
Specify a Username and Password
If you do not know your username and password combination, please refer to the connection parameters section below.
Add Connection Parameters
If you want to specify any Database connection parameters. Example for SQL servers is Geo-Spatial features. You can also use the following connection string: Server=myServerAddress;Database=myDataBase;Trusted_Connection=True; (replace everything in italics). Alternative connection strings can be found here: https://www.connectionstrings.com/sql-server/.
Add Reference Assemblies
This is for the SQL server mostly.
Prior Data Sheet or Template
Usually not used, but can be used if you have Data Sheet or Template for this model. This usually happens when you are re-registering the model with a data sheet and you want to keep your last configuration. In that case, you can specify the name of the last configuration sheet in the box provided.
Provides an option to select tables for selective model generation)
Deselect this option if you would like to select all Tables.
Build Data Creation Spreadsheet
(A spreadsheet will be generated to configure data generation).
This would normally be selected to generate synthetic test data
Use Sample Data File
This option is used if you want to use a Sample Data File. The Sample Data File is a pre-configured sheet so if you have one (may be from a previously generated model for version 1 and now moving to version 2 you may like to leverage existing settings from the previously configured sheet.). If you have an existing data generation spreadsheet and want to use the configured values this option should be checked.
Use Existing Data
This option is used if you want to use a data file that has already been created. Please include the name of the repository file in the box provided.
If the Target Database already has some data (e.g. Master data for some tables) and the user wants to access the data during generation, this option should be used. If a Repository is specified as TargetRepo, the user can access the repository function using varTargetRepo variable.
Check if views should be included (Checking this option may make model generation slow). It includes database View information in the model. (https://www.essentialsql.com/what-is-a-relational-database-view/) Data cannot be generated for Views but can be queried.
Make Object Names Singular
This only works when the user wants to load data from a JSON file to a database. VIP provides a way to import JSON data to database directly. This is not a requirement for synthetic data generation.
For corresponding JSON model option: Inserting a JSON message to a relational database will be available.
Select Generate Model
To generate the model. The model will be generated in
After Generating Model the following two files will be created:
The data generation model .dll file which is the model that VIP will use.
The data generation configuration excel sheet.
The Data is generated in AppData\Roaming\VIP\ExternalEntityAssemblies directory.
Once you generate the model by selecting Generate Model, you will be presented with a window such as the one below (because we have used ‘Select Tables’ option) which provides us with all the tables in the DB. You can use filters on the tables to display all/none etc.
Once the chosen Tables have been selected, click on Register (top box). Example: For the SampleCommerce Database, we would select dbo.Customer, dbo.Order, dbo.OrderItem, dbo.Product and dbo.Supplier. The other tables are not needed including dbo.Country because this is a Master Table.
After Generating Model, the next step is to Configure the Test Data Generation Sheet. But before we do that, let’s have a look at our sample Database Model called SampleCommerce.
SampleCommerce Database model viewed using SQL Management Studio:
In this example the Database is a Sample Commerce database which contains 5 records. The records are Order, OrderItem, Product, Supplier and Customer.
Navigation through model Tables in the Configuration sheet can be done by selecting the Table name on the bottom of the window. Once a table is selected, the user can view all the fields and variables, represented as columns and rows respectively, that pertain to each table.
Any of the information in the tables can be edited manually. So for example, rows can be deleted and any values in the Table (represented as spreadsheet cells), can be changed.