Home

	Expand Search

WarehouseArchitect - User's Guide

Part 3 WarehouseArchitect Model

Chapter 22: Database Creation and Modification

Using test data

Using test data

You normally use test data to verify the performance of a database that is being developed. You can use test data to do the following:

• Verify the performance of the database when it is filled with large amounts of data
• Verify the performance of the database when it is accessed by different applications or users
• Estimate the amount of memory space the database will take up
• Examine data formatting in the database

You can generate test data for selected tables in a WAM, or for all the tables in a WAM.

You can add test data to an empty database or to a database that already contains data.

Data profileYou generate test data for a table based on data profiles. A data profile is a named class of data types that has a defined data generation source. A data profile can be assigned to one or more columns.

When you assign a data profile to a column, it acts as a representative for the data type of that column. Test data for the column is then generated using the data profile and its defined data source.

You can also select a data profile to apply to a domain. You can then choose to have the profile automatically assigned to all columns that use that domain.

A data profile uses one of the following classes of data types:

• Number
• Character
• Date/Time

ExampleFor a column named Employee Location, you create a data profile named Address using the Character class. You then define a test data source for Address. You can also assign it to other columns, such as Store Location, and Client Address.

Default profilesIf you do not assign a data profile for a column, a default data profile is assigned to the column when you generate test data for the table. You define default data profiles for each class that you use to generate test data.

Test data restrictionsThe following objects are not taken into account when you generate test data:

• Nested alternate keys
• Nested foreign keys
• Business and validation rules
• Binary, sequential, OLE, text or image data types
• Trigger contents

• To use test data:
  1. Create a data profile for one or more columns.

  2. Define a test data generation source for each data profile.

  3. Define column fill parameters for columns that need to be populated with data in a particular way.

  4. Assign data profiles to all relevant columns.

  5. Use the data profiles to generate test data.

Creating a data profile

When you create a data profile you need to define the following properties:

Property	Description
Name	Name of the profile
Code	Code of the data profile
Class	You can assign one of the following data type classes for each data profile: Number Character Date/Time

• To create a data profile:
  1. Select Dictionary-->Profiles-->List of Profiles.

     The List of Profiles appears.

     

  2. Click a blank line in the list.

     or

     Click the New button.

  3. Type a Profile name and code.

     or

     Type a profile name and click the Equals button in the code column.

     or

     Type a profile code and click the Equals button in the name column.

  4. Select a data type class from the Class dropdown listbox.

  5. Click OK.

     or

     Click another profile name line.

Test data generation source parameters

You define a test data generation source for each data profile. You can use the following test data generation sources:

Generation source	Test data values are generated
Automatic	Automatically by PowerDesigner
File	From a source file
List	From a list of test data values
ODBC data source	From a target ODBC data source

Each data generation source has its own set of options for defining generation parameters.

Automatic test data generation source parameters

There are different automatic test data generation source parameters for each profile class.

Number

You have the following options to define generation source parameters for the profile class Number:

Option	Description
Random	Indicates to generate random data
Sequential	Indicates to generate sequential data
From/To	Indicates the range for random or sequential data
Step	Indicates interval between each sequential number step
Generate decimal numbers	Generates decimal numbers
Decimal digits number	Indicates the maximum number of decimal places to be generated

Character

You have the following options to define generation source parameters for the profile class Character:

Option	Description
Valid characters	List of authorized characters
All	Accepts all characters
Invalid characters	List of unauthorized characters
No accents	Replaces accents with unaccented characters
Mask	String of mask characters.
Default	When selected, sets default values for Valid, Invalid and Mask characters
Uppercase Lowercase Mixed case First uppercase	Indicates authorized letter cases
Exact	You indicate exact character length
From	You indicate character length range

Character selectionYou can specify valid characters to accept and invalid characters to refuse. You use a comma ( , ) to separate each single character, character interval, or string.

The following syntax applies to valid and invalid characters.

Character set	Syntax	Example
Interval of characters	Characters in single quotation marks separated by a dash	'a'-'z'
Single character	Character in quotation marks	"a"
Character string	String in quotation marks	"xyz"

Mask charactersA mask character is a pre-defined character that indicates to a user that they need to enter a particular piece of information. The test data that is generated respects the following mask characters:

Mask character	Prompts to enter
A	Letter
9	Number
?	Any character

Date/Time

You can define the following generation source parameters for the profile class Date/Time:

Value	Parameter	Description
Type	Random	Generates random values
	Sequential	Generates sequential values
Date range	From/To	Indicates range of date values
Time range	From/To	Indicates range of time values
Step	Date/Time step	Indicates date and time intervals for sequential data values.

Defining an automatic test data generation source

• To define an automatic data generation source:
  1. Select Dictionary-->Profiles-->List of Profiles.

     The List of Profiles appears.

  2. Select a data profile.

  3. Select the Automatic radio button.

  4. Click the Properties button.

     The Profile dialog box appears.

     

  5. Specify test data generation parameters.

  6. Click OK.

     You return to the List of Profiles.

  7. Click OK.

     or

     Select another profile.

Defining a file as a test data generation source

You can import a CSV file to define a data generation source for a data profile.

CSV import restrictionEach CSV file that you import for each data profile must come from the same directory. If you have not previously used this directory as a CSV file test data source, you will need to select the correct directory to define the file path.

Example test data files PowerDesigner provides example test data files in the PowerDesigner 6 TESTDATA directory.

• To define a file as a test data generation source:
  1. Select Dictionary-->Profiles-->List of Profiles.

     The List of Profiles appears.

  2. Select a data profile.

  3. Select the File radio button and click the Ellipses button.

     A dialog box asks you to select a file.

     
     

     Specifying the CSV directory If you are importing a CSV file for the first time from a specific directory, you need to select the appropriate directory.

     
     

  4. Select a file and click OK.

     You return to the List of Profiles.

  5. Click OK.

     or

     Select another profile.

Defining a list as a test data generation source

You can use information from a list to define a test data generation source for a data profile.

• To define a list as a test data generation source:
  1. Select Dictionary-->Profiles-->List of Profiles.

     The List of Profiles appears.

  2. Select a data profile.

  3. Select the List radio button and click the Ellipses button.

     A List of Values appears.

  4. Enter list values.

  5. Click OK.

     You return to the List of Profiles.

  6. Click OK.

     or

     Select another profile.

Defining an ODBC data source as a test data generation source

You can use information from an ODBC data source to define a test data generation source for a data profile.

• To define an ODBC data source as a test data generation source:
  1. Select Dictionary-->Profiles-->List of Profiles.

     The List of Profiles appears.

  2. Select a data profile.

  3. Select the ODBC radio button and click the Ellipses button.

     An ODBC Source dialog box appears.

     

  4. In the Database Connection groupbox, enter target ODBC database connection details.

  5. If you want to specify a single table and column as a data source, type the table and the column codes in the Table Code and Column code text boxes.

     or

     If you want to specify multiple tables as a data source, or specify a criteria for the data source, select the User-Defined Query checkbox and type a SQL query in the textbox.

  6. Click OK.

     You return to the List of Profiles.

  7. Click OK.

     or

     Select another profile.

Defining column fill parameters

Fill parameters determine how a column is filled or populated with test data. You can define column fill parameters when you assign a data profile to the column, or as an independent procedure.

You can define the following fill parameters:

Value	Description
Null values	Percentage of column entries for which a null value is permitted
Distinct values	Indication of the percentage of column rows that contain unique entries. This is a maximum value, and can change automatically depending on the referential integrity parameters of primary key columns

Average Length The Average Length box is only used for the Compute data base size function. The value that is displayed by default, is the maximum length for the data type defined for a selected column.

Indicated column propertiesThe following column properties are indicated on the Fill Parameters sheet:

Indicator	Property	When selected, indicates that...
M	Mandatory	Column must be assigned a value. The fill parameter Null value is automatically defined as 0%
U	Unique column	Column is the only column in a primary key, alternate key, or unique index. The fill parameters Null Values and Distinct Values are automatically defined as 0% and 100% respectively
F	Foreign	Column is a foreign key column. You cannot assign a data profile to this column. It automatically takes the data profile of the corresponding primary key column in the parent table

• To define column fill parameters:
  1. Double-click a table in the model with the Pointer tool.

     or

     Click the property tool and click a table in the model.

     The Table property sheet appears.

     

  2. Click the Fill button.

     The Fill Parameters dialog box appears.

     

  3. Select a column in the list.

     An arrow appears at the beginning of the line.

  4. Enter values for the following:

     Null values

     Distinct values

  5. Click OK.

     or

     Click another column in the list.

Assigning a data profile

When you assign a data profile to a column, the data profile defines the type of test data that you generate for that column.

You can assign a data profile to each column in a table, except for the foreign key columns. The data profiles for foreign keys are assigned by default.

When you assign a data profile to a column that contains check parameters, the generated test data respects the column constraints.

There are three approaches to assigning data profiles:

• Assign a data profile from the table property sheet
• Assign a data profile from the list of profiles
• Select a data profile for a domain, so that all columns attached to that domain are automatically assigned the data profile. For information on how to select a data profile for a domain, see the chapter "Building Physical Data Models".

Assigning a data profile from the table properties sheet

• To assign a data profile from table properties:
  1. Double-click a table in the model with the Pointer tool.

     or

     Click the property tool and click a table in the model.

     The Table property sheet appears.

  2. Click the Fill button.

     The Fill Parameters dialog box appears.

     

  3. Select a column in the list.

     An arrow appears at the beginning of the line.

  4. Select a profile from the Profile dropdown listbox.

  5. Click OK in each of the dialog boxes.

     or

     Select another column in the list.

Assigning a data profile from the list of profiles

You can assign a data profile from the list of profiles

• To assign a data profile from the list of profiles:
  1. Select Dictionary-->Profiles-->List of Profiles.

     The List of Profiles appears.

  2. Click the data profile that you want to assign.

     An arrow appears at the beginning of the line.

  3. Click the Attach button.

     The Select Objects dialog box displays available and selected objects. Columns are the only objects available from the Available Objects listbox.

  4. Select one or more columns from the list.

  5. Click the Add button.

     The selected columns appear in the Selected Objects list.

     

  6. Click OK.

     You return to the List of Profiles.

  7. Click OK.

     or

     Click another data profile from the list.

Importing a data profile

You can import data profiles in PRF format from another WAM. The imported profiles appear in the list of profiles.

• To import a data profile:
  1. Select Dictionary-->Profiles-->Import Profiles.

     The Import Profile Definitions dialog box appears.

  2. Select or type a PRF file name.

  3. Click OK.

Exporting a data profile

You can export data profiles to a PRF file. In another WAM, you can then import the data profiles defined in this PRF file.

• To export a data profile:
  1. Select Dictionary-->Profiles-->Export Profiles.

     A list of profiles appears.

  2. Select one or more data profiles that you want to export.

  3. Click OK.

     A Save As dialog box appears.

  4. Type a PRF file name.

  5. Click OK.

Generating test data

You can generate test data for all the tables in a WAM, or for selected tables.

ParametersYou can define the following test data generation parameters for a table:

Parameter	Result of selection
All	Generates test data for all tables in model
Selected	Generates test data only for tables selected graphically in model
List	Generates test data only for tables selected from a list of tables in model
Delete old data	Deletes all existing test data
Default number of rows	Indicates default number of rows for table
Default number profile	Indicates default number profile for table
Default character profile	Indicates default character profile for table
Default date/time profile	Indicates default date/time profile for table

Number of rowsYou can indicate the number of rows of test data that are generated for each table in the model:

List item	Description
Table	Tables in the database
Defined	Number of rows indicated for each table as defined in the table property sheet
Generated	Number of rows of test data for each table that you want to generate. You can modify this value. The default number of rows is the value defined for the table in the Parameters page. If you have not defined a default value, the number 10 is assigned automatically

OptionsYou can define the following test data options for a table:

Option	Result of selection
Uppercase	Script contains all uppercase characters
Lowercase	Script contains all lowercase characters
No accent	Non-accented characters replace accented characters in script
Codes	Script refers to objects by their codes
Names	Script refers to objects by their names
Windows(ANSI)	Script uses ANSI character set
DOS (OEM)	Script uses DOS character set
Add title	Each section of the script includes commentary in the form of titles
ODBC syntax	Generates script in ODBC syntax instead of DBMS-specific syntax
Check model	Check the WAM before generating the test database or script, and stop generation if an error is found
CSV Format	Generates script in CSV file format. The decimal and list separators used in CSV file format are defined in the regional settings configuration of your computer
Auto	Commit automatically during script generation
At end	Commit at end of script generation
By packet	Commit at defined intervals during script generation

• To generate test data:
  1. Assign data profiles to the columns in each table that you want to generate test data for.

  2. Select Database-->Generate Test Data.

     The Test Data Generation dialog box opens to the parameters page.

     

  3. Specify test data generation parameters for Tables.

  4. Click the Number of Rows tab.

     The Number of Rows page appears.

     

  5. Enter the number of rows of test data that you want to generate for each table.

  6. Click the Options tab.

     The Options page appears.

     

  7. Select generation options.

  8. Click the Create Test Data button.

     A confirmation box asks you to confirm the deletion of all existing data.

     

  9. Click OK.

     A dialog box asks you to identify a data source and connection parameters.

  10. Select a data source from the dropdown listbox.

  11. Type your user name and password.

  12. Click Connect and, if prompted by your data source, enter additional connection parameters.

      A message window shows the progress of the test data generation process.

      

  13. Click OK.

  14. Click the Close button in the Test Data Generation dialog box.

Generating a test data script

You can generate a test data generation script from the Test data generation dialog box.

• To generate a test data generation script:
  1. Enter test data generation information in the Parameters, Number of Rows and Options pages.

  2. Click the Generate script button.

     A confirmation box asks you to confirm the deletion of all existing data.

     

  3. Click OK.

     A message window shows the progress of the test data generation process.

     A confirmation box asks you if you want to view the completed script.

  4. Click No.

     You return to the message window.

  5. Click OK.

     You return to the Test data generation dialog box.

  6. Click the Close button.