Design subset definition

On the Design subset definitions screen user can create and define subset definition.

On the left side of screen there is a Toolbox, which contains all Tables from environment with possibility to make a search by table. Each table can contain table filters:

1. Global filter - They are used to create rules that exclude / include all data or just a part of the user-defined data (Custom Global Filter). Global filters can be set on Subset Table Filters screen and rules defined on Subset Table Filters apply to all subset definitions as a priority.
2. Local filter - Custom local filter is just a part of the user-defined data which can be set in Design subset definition page.

On the right side of screen there is a Design surface where user can design subset definition by dragging and dropping tables in it.

Table of contents
Design subset definition
Subset table details
Expand all nodes
Copy and Paste as loop
Subset relationship details
Execute Subset Definition

Design subset definition

On the left side of screen there is a Toolbox, which contains all Tables from environment with possibility to make a search by table. If a table contains global or custom filters, it will have an icon showing what type of filter the table has, Global, Custom or both filters.

Possible filter icons on the tables:

• - "Include all data".
• - "Exclude all data"
• - "Custom Global Filter"
• - "Custom local filter" - it is showing on SubsetTable element for Local custom filter, Global custom filter or both if defined.
• if no filtering is configured, then toolbox item doesn't have additional icon

Tables from all Data Source that have defined filter 'Include all data', through global filters and all their implicit parents are called PassTrough Tables.

On the right side of screen there is a Design surface where user can define subset definition by dragging and dropping tables in it.

Root tables are found in the Subset definition at the root level.

When the root table is created, we can drag the next table to it and create a subset definition. In case there is no relationship or if there is more than one relationship in database between the two selected tables, pop-up window ‘Subset relationship’ will be open automatically for entering Subset relationship details.

Figure 1: Design Subest Definition

Dragging existing subset table without relationship will create new relationship. When dragging existing subset table that had relationship with another table (dragging existing subset table as child to another table) - that relationship will be updated with the new data.

When table is dragged to Design surface filter icons will be shown on SubsetTables if they exsists.

User can adjust SubsetTable details by right clicking on subset table (Figure2). This will open menu with possibilities to:

• Subset table details – this will open pop-up window with Subset table details (Figure3)
• Expand all nodes – this will ekspand all related tables which are under selected table (Figure4)
• Copy (as loop) - this will copy selected node (Figure5)
• Paste (as loop) ‘TABLE_NAME’ – this will paste selected node

Subset table details

By choosing ‘Subset table details’ on right click on some table on the Design surface, pop-up window will be open.

Figure 2: Subset Table Details

The following table details can be entered:

• Local custom filter criteria - In Local custom filter criteria user can write some custom criteria for subset table. If local custom filter exists Icon for Custom filter criteria will be shown on SubsetTable on Design surface. E.g. 'COUNTRY_ID=’IT’'
• Global custom filter criteria - This field is protected and it represent global custom criteria if it is defined in ‘Subset table filters’. If global filter is defined as "Custom" then replacement strategies is mandatory. Default replacement strategy is ‘Report error’.
• Primary key columns - List of Primary key columns can be choosen from lookup. When one primary key is choosen, it won’t be displayed in next lookup. Duplicate primary keys are not supported.

Figure 3: Subset Table Details

To confirm inserted custom filter criteria press Confirm button or press Cancel button to exit without saving changes.

Expand all nodes

By choosing ‘Expand all nodes’ on right click on some table pop-up window this will ekspand all related tables which are under selected table.

Figure 4: Expand all nodes

To expand all open child nodes, user can also choose Expand button: .

Copy and Paste (as loop)

"Loop" relation is the relation that points to existing tree element in the parent hierarchy (arrow pointing to the left and to above). Copy (as a loop) can be used as much the user needs.

Figure 5: Copy As Loop

• When dragging existing loop: the only drag which is supported is to delete the relation.
• Copy - Paste operation:
  • Copy node is available for all nodes
  • Paste node (after copy) is available ONLY on the starting node (copy node) or nodes that are in the parent hierarchy of the starting node (but not on child nodes and not on different tree branches)
• When selecting relationship loop node related tables will be "highlighted" in project colour.

Figure 6: Loop

When relationship or table is selected and highlighted, with left key mouse pressing, it can be Deleted on two ways:

1. By pressing ‘Delete’ on keyboard
2. By dragging relationship or table to Toolbox

Figure 7: Delete subset node

Subset relationship details

When table is dragged user can adjust relationship dialog. Relationship dialog between two tables is a line with relationship button:

By placing the mouse on the relationship button the relationship between the two tables is shown. On left-click the relationship node is coloured as selected node. Both, source and target table will be "highlighted" in project colour.

Figure 8: Subset Relationship between the two tables

Right-click on the relationship button, a menu with one option opens: 'Subset relationship details' where the user can enter more details about subset relationship.

Figure 9: Subset Relationship

By selecting 'Subset relationship details', pop-up window for viewing or defining Subset relationship details will be open.

The following relationship details can be entered:

RELATIONSHIP

• Relationship - Possible relationship:

  • Custom relationship
  • possible relationship depends on chosen Subset relationship tables

• Parent columns - If Parent columns exist it will be automatically entered. User can also add Parent columns for parent relationship table.

• Child columns - If Child columns exist it will be automatically entered. User can add Child columns for child relationship table.

Figure 10: Subset Relationship

When selecting a new Parent-Child column relationship from the drop-down list, system opens a new key entry row. If there are no more input options, all keys from the drop-down list are used, a new relationship can no longer be added.

REPLACEMENT STRATEGY

• Replacement strategy -The user can choose the following strategies:

  • Report error - is the default value. Missing values will not be replaced, and error will be reported. So, when ‘Execute subset’ is started, system checks if there is any data that needs to be replaced (that references to records that are not in the subset). If there is any data that needs to be replaced execution will fail with error on 'VerifyExecution' step.
  • Random value - Missing values will be replaced with random value from the child table
  • Constanat value - Missing values will be replaced with constant predefined value
  • Value from list - - Missing values will be replaced with one of the predefined values from list
  • Values are entered in pairs depending on the list of parent-child columns in Relationship tab
  • column pairs from the "Relationship" tab are listed as headers (dynamic number of columns)
  • values are listed as table rows: the user can edit the values entered in the text boxes
  • In case the user wants to put a NULL value this can be done using ‘ALT + 0’
  • The entered values are not validated and in case the user enters a value that does not exist in the child table, then there will be an error when executing the subset

• Replacement values - If Replacement strategy is ‘Report error’ or ‘Random value’, Replacement value is protected and it is not mandatory. If Replacement strategy is ‘Values from list’, Replacement values fields are open and user must enter some constant value. In this case field is mandatory.

Figure 11: Replacement strategy

To delete entered values press Delete button.

To confirm inserted relationship details press Confirm button or press Cancel button to exit without saving changes.

If no replacement value is added and Execution subset definition is started this message will be shown: ‘Replacement strategy for relationship ‘Parent-Child column names’ requires values.’

Figure 12: No Replacement strategy

Execute Subset Definition

When Define Subset Defitinion is ready, user can execute it by pressing Execute button. This will open Subset Execution Parameters pop-up window. (Figure8) where user must enter Target database name.

• To start execution press Confirm button.
• To cancel execution press Cancel button

Figure 13: Execute Subset Definition