{"id":8439,"date":"2015-12-15T11:15:36","date_gmt":"2015-12-15T16:15:36","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=8439"},"modified":"2025-02-12T14:04:05","modified_gmt":"2025-02-12T19:04:05","slug":"iris-new-database-subsetting-wizard","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/vldb-operations\/iris-new-database-subsetting-wizard\/","title":{"rendered":"What Is Database Subsetting? How to Subset a Database."},"content":{"rendered":"

Once a database exceeds a certain size, it becomes expensive \u2014 and risky from a security perspective \u2014 to provide full-size copies for development, testing, and training. Most teams need smaller copies of the larger database, and often have the PII within masked.<\/p>\n

Database subsetting is that process of creating a smaller, referentially-correct copy of a larger database schema from real table extracts. Subsets can be used with, or in lieu of, masking or synthesizing test data to reduce the costs and risks associated with full sets.<\/p>\n

The process of creating meaningful subsets manually is complex and laborious. Normally you would have to populate smaller databases with random samples from each production table, and make sure that any relational structure between the tables was still correct in the subset.<\/p>\n

This article shows you how to subset a database in the fit-for-purpose, end-to-end database subsetting tool in the IRI Workbench<\/a> GUI, which simplifies and accelerates this process. The DB subsetting job wizard is available to licensed users of the IRI Voracity<\/a> data management platform, IRI RowGen<\/a> for test data generation, or IRI FieldShield<\/a> for data masking.<\/p>\n

By using the wizard, you can create secure database subsets for testing that are structurally and referentially correct.\u00a0 This is because the wizard preserves primary and foreign key relationships, and allows you to simultaneously obfuscate PII with data masking functions<\/a> like format-preserving encryption.<\/p>\n

The wizard for subset creation has the user select the source of the subset, the size, content, and sorting thereof, plus any masking or other functions that should apply to the target parent and child table data. After those options are specified, the wizard builds a series of job scripts (and optional workflow and transform mapping diagrams) which when run will generate and populate either subset tables or flat files.<\/p>\n

Job Options<\/h2>\n

This page defines the output type. If the loader is left empty, a flat file with a tab delimiter will be created. For database options, either ODBC or the specific database loader can be selected.<\/p>\n

\"Define<\/p>\n

Subset Options<\/h2>\n

This page is where you specify the details of the subset. Select the connection profile and the table that will \u201cdrive\u201d the created subset. Think of the driving table as the main table from where you want the subset to originate.<\/p>\n

For example, if you want to subset a table of sales and all tables connected to it, you would select the table with sales info here. Also, select the size of the subset. For example, to get a subset of the 100 highest quantities sold, you would sort on the quantity sold field (as in this example) and enter 100 in the number of rows. A filter can also be added on the driver table.<\/p>\n

A qualitative filter can also be added on the driver table to customize the subset based on business criteria. Such as the example below, allowing the filter to omit or include a condition type, that is either manually created to fit all needs, or can use an existing condition.<\/p>\n

\"Subsetting<\/p>\n

Sorting<\/h2>\n

This page is where you specify the sort order of the subset. If you want the rows selected randomly, however, leave the Key Fields list blank.<\/p>\n

The Sort page has 3 notable sections:<\/p>\n

    \n
  1. The Input Fields list<\/li>\n
  2. The Key Fields list<\/li>\n
  3. Key Options<\/li>\n<\/ol>\n

    \"Sort\"<\/p>\n

    Steps:<\/p>\n

      \n
    1. If you want a subset of sorted data, select the column to sort on and click “Add Key.”<\/li>\n
    2. A few options are available in the Key Options box:\n