Informatica TDM (1): architecture, the tool tour, discovery and subsetting

A data subset is a small slice of production data that still hangs together: if you keep a customer, you also keep that customer's orders and the order lines inside them.

A full copy of production is slow to load, expensive to store and full of real people's data. A random sample of rows is cheap but useless, because a sampled order may point to a customer that was not sampled. A subset gives you the middle path: small and consistent.

4.39 Words to know first

Data subset

A small, targeted copy of production data that keeps related rows together, so it can be used in development, test or training.

Referential integrity

The rule that every foreign key points at a row that exists. An order whose customer is missing breaks it.

Entity

In TDM, a set of tables that are related by physical or logical constraints, built around one starting table. You filter the entity to say "which customers".

Driving table

The one table you pick as the starting point of an entity. Also called the "driver table".

Group

A set of tables that are not related to each other (or that you want copied without filtering), added to a plan as they are.

Template

An optional container that holds entities and groups. The guide mentions it but gives almost no detail (see 13.5).

Constraint

A rule linking two tables, usually a foreign key from a child table to a parent table. It can be physical (declared in the database) or logical (added in TDM because the database does not declare it).

Major / minor constraint

A severity setting on a foreign key. Major moves in both directions (child to parent and parent to child). Minor moves only child to parent.

Criteria parameter

A named placeholder (for example $$CRITERIAVALUE) used in an entity filter so the value can change at run time without editing the entity.

Plan / workflow

A plan lists what to do (which entities, groups, rules, connections). A workflow is the runnable job TDM generates from the plan.

4.40 What a data subset is, and what TDM can subset

4.40.1 The idea in one example

ShopSphere's production database holds 2 million customers. The QA team needs a test environment that holds about 10 percent of them, with every order and order line for those customers, and the products those lines refer to. Nothing else. If you did this by hand you would write SQL joins for each table and hope you did not miss a foreign key. TDM lets you describe the shape once (an entity) and the filter once (criteria), then repeats it on demand.

4.40.2 What the guide says

• You create a subset when you need "a small, targeted, and referentially intact copy" of production data for a non-production environment such as development, test or training. The guide's own example is financial data for one region or one time period (User Guide 10.5.10, p.112).
• Sources can be relational databases, flat files or XSD (XML structure) files (p.112).
• You define data subset components and add them to a plan. You then generate a workflow from the plan, and the workflow loads the subset into a target database (p.112).

4.41 The big picture: process flow and components

4.41.1 The tasks, in order

4.41.2 The three component types

A component is a building block you define before you build the plan. The guide names three (User Guide 10.5.10, p.113 to 114) :

4.42 Entities and the driving table

4.42.1 What an entity is

An entity is a shape: "start at this table, and include everything connected to it". The guide says an entity "consists of a driving table and related tables" (User Guide 10.5.10, p.114) . The driving table is the starting point. TDM works out the other tables from the constraints that involve the driving table and adds them by default. You then put a filter on columns of the tables in the entity to say which rows you want.

Here is the same idea on ShopSphere. We choose CUSTOMERS as the driving table because the business question is "give me some customers and everything they did". ORDERS points at CUSTOMERS through customer_id, ORDER_ITEMS points at ORDERS through order_id, and ORDER_ITEMS points at PRODUCTS through product_code. (The product_code link is assumed for this example: in the source it is a column that names a product, treated here as a foreign key.)

4.42.2 The three views of an entity

Once saved, an entity can be seen three ways in the Define | Data Subset view of a project (User Guide 10.5.10, p.114 to 116) .

The Entity Map can show All tables, All parents of the selected table, All immediate relationships (one level up and down), All children, or All cyclic relationships (TDM shows at most 1000). By default it shows only enabled relationships; tick Show excluded tables to see disabled ones (p.115). Selecting a link in the map highlights the matching row in the Relationships view, and the other way round.

4.42.3 Where the relationships come from: physical and logical constraints

TDM can only follow links it knows about. Those links are the constraints stored in the project (User Guide 10.5.10, p.107 to 109) . Foreign keys that already exist in the database are physical. If the database does not declare a foreign key (common in older systems), you can accept one that data discovery inferred (see the Informatica discovery and profiling chapter), or add one by hand as a logical constraint. Adding keys in TDM does not change the source database (p.109). The guide's advice before you build an entity is: run data discovery to identify constraints, and add constraints to the source to define the child tables (p.116).

4.42.4 Creating an entity, step by step

Procedure (User Guide 10.5.10, p.116 to 117), with the reason for each step.

1. Open the project, then click Define > Data Subset.

   The Define area is where all the "design" objects live: subset components here, masking assignments in Define | Data Masking (see the Informatica masking chapter).

   You see a list of entities and groups in the project, possibly empty.

2. Click Actions > New > Entities.

   This starts the New Entity dialog.

3. Enter a name and an optional description.

   Names matter later: plans list components by name. Something like ShopSphere_Customer_Entity beats Entity1.

4. Click Select Table, choose the driving table, click OK.

   This is the key decision. Choose the table that answers your business question: "customers" for ShopSphere.

5. Click Save.

   TDM now looks at the constraints of the driving table and adds every related table.

   A diagram of the tables and their relationships appears, and you can switch to the list views.

6. To filter, click Edit in the Properties pane. The Edit Criteria dialog opens.

   Without a filter, the entity copies everything it reaches. The filter is what makes it a subset.

7. On the Entity Criteria tab, click Add Criteria, select the columns to filter on, click OK.

   The columns you chose now appear in the list.

8. For each column, pick the attribute: Value (type a value) or Parameter (pick a parameter). Pick an operator. Then type the value or choose the parameter. Click Save for each criterion.

   Parameters let you change the value later without editing the entity (13.7).

9. Click Save to leave the Edit Criteria dialog.

   The entity is ready to add to a plan.

When you have several criteria in one entity the guide says the filter conditions act as "AND" (p.117). So customer_id <= 1002 and zip_code = '560001' together would keep only rows that satisfy both.

4.43 Groups, residual tables and templates

4.43.1 Groups

A group is "one or more unrelated tables" you want copied to the subset database. The guide gives two uses: add unrelated tables to a plan, or "copy unfiltered data to a target" (User Guide 10.5.10, p.116) . When you add tables to a group you can include residual tables: tables that are not yet in any group or entity. Tick them when you want every table of the source to be in the subset plan.

An entity only reaches tables linked by constraints, so unlinked tables go in a group. The guide's example: an employee-skills table and an organization-location table are unrelated, you want both, so you put them in one group and add the group to the plan (p.116).

4.43.2 Creating a group

1. In the project, open Define | Data Subset and click Actions > New > Groups.

   Same place as entities, different menu item (User Guide 10.5.10, p.123).

2. In the Create Group dialog, enter a name and optional description, then Next.
3. Click Add Tables, optionally filter the list to search, select tables, click OK.

   The filter box helps when a schema has hundreds of tables.

4. Click Save.

   The group appears in the Define | Data Subset list next to your entities. Editing a group later lets you add or remove tables (p.124).

4.43.3 Templates: what the guide actually says

The guide mentions templates only in passing: "an optional component that contains the entities and groups" (User Guide 10.5.10, p.32 and p.293) . The guide gives no steps for creating a template.

4.44 How related rows are pulled

This is the part that makes subsetting more than a filter. When your filter selects customer 1001, which other rows must come with it? The answer depends on the constraint severity and on the data integrity option you pick in the plan. Both are covered in the guide (User Guide 10.5.10, p.108 to 109, 119 to 123).

4.44.1 Major and minor constraints

When a foreign key is defined, you give it a severity. The guide's rules :

• Major constraints move in both directions, child to parent and parent to child. A selected child pulls in its parent, and the parent then pulls in all of its children. This keeps referential and transactional integrity (p.108).
• Minor constraints move one way only, child to parent. The parent row comes along, but the parent's other children do not (p.108 to 109).
• You define the constraint on the child table. Severity does not change the entity's structure. You can change it after the entity exists, and also at run time in a subset plan, which leaves the entity unchanged (p.108).
• By default every constraint in an entity is major (p.119).

The guide's own example (p.108 to 109) uses Customer as parent and Leads as child, with a filter on Leads that keeps only high-quality leads. With a major constraint, the subset receives each qualifying lead, its customer, and all leads of that customer. With a minor constraint it receives only the qualifying leads and their customers.

MAJOR (both ways)                     MINOR (child to parent only)

 CUSTOMER  1001                        CUSTOMER  1001
   |  ^                                  ^
   v  |  parent pulls ALL its children   |  parent comes along,
 ORDER 5001 (selected)                   |  siblings do not
 ORDER 5002 (comes along)              ORDER 5001 (selected)
 ORDER 5003 (comes along)              ORDER 5002 (left out)

4.44.2 The loop effect

With major relations the pulling can chain. The guide describes it (p.108, p.122): a selected child record pulls in its parents; each parent then selects all its related children; those children might point to other parents not yet in the subset, which pull in more children, "in a loop until there are no more related records to select". This is how a small filter can grow into a big subset. You will see exactly this in the ShopSphere example when PRODUCTS is related by a major constraint (13.11).

For schemas with cycles, or objects that have more than one major parent, TDM uses recursive evaluation to compute the subset, and that needs a staging connection. The guide recommends using the source connection as the staging connection (p.109).

4.44.3 The four data integrity options

The entity says how tables are connected. The data integrity option in the plan says how aggressively TDM follows those connections at run time. This lets you reuse one entity to produce different subsets. You choose it in the Advanced Settings area of the Plan Settings page when you create the plan; the field is called Data Integrity for Subset and its default is Transactional Integrity for Major Relation Type (User Guide 10.5.10, p.121, p.287) .

Two terms first. (A staging connection, mentioned below, is a database connection TDM uses for temporary working tables.) Referential integrity means no child row points at a missing parent. Transactional integrity goes further: if a parent is in the subset, all of its children are too, so a business transaction (a customer with all their orders) is complete.

4.44.4 Which option for ShopSphere?

• One criterion on CUSTOMERS, want complete customer histories: keep the default Transactional option. Every order and line of a selected customer comes along.
• Same criterion, want the smallest consistent database for a quick smoke test: Referential Integrity Only. You may get a customer with only some of their orders. For a test that checks "order total equals sum of lines" that is fine; for one that counts "all orders for this customer" it is not.
• Criteria on both CUSTOMERS and ORDERS (for example zip code and order value): consider Two-way so that both tables end up with data, or edit the criteria to OR (see the table).

Verify the choice on a trial run.

4.44.5 Optimizing relations

Because every relationship starts as major (p.119), a subset can pick up more rows than you need. Optimize Relations asks TDM to analyse the entity and propose which relationships can be minor while still reaching every table. The guide's procedure (User Guide 10.5.10, p.119 to 120) :

1. Make sure the entity already has a criterion.

   TDM raises an error if you optimize an entity with no criteria at entity level (p.119).

2. Open the entity and click Actions > Optimize Relations.

   TDM analyses the constraints. The Optimize Relationships page lists each parent and child with the current and the proposed type.

3. Optional: Download Optimizations.

   Saves the proposal as a CSV so you can review it with a colleague.

4. Click Optimize to accept all proposals.

   You cannot accept some and reject others (the guide says results cannot be applied selectively). A list of plans that include the entity appears; regenerate and rerun them.

5. Click Continue.

   The entity is updated. You can still edit single constraints by hand afterwards.

You can also optimize at run time in the plan (the "Optimize Relationship Type for Entities" setting, p.283). That changes the run, not the saved entity, and it also requires criteria at the plan level (p.283). When you choose Two-way or One-way integrity, optimization is ignored (p.122 to 123).

4.44.6 Disabling and enabling relationships

Sometimes you do not want TDM to follow a link at all, for example a link to a huge audit table. In the Entity Map or the Relationships view you can disable a relationship (p.120) . In the map, choose to view all relationships and show excluded tables; enabled links are solid, disabled links are dotted. Click the link, then Disable Relationship or Enable Relationship. If a table loses every link to the entity this way, it is excluded from the subset database (p.115). If you still want its data, put it in a group.

4.44.7 When constraints change after you built the entity

Entities do not update themselves. If you add a constraint after building the entity, the entity list marks it as having a change impact. Open it, click Review Changes, accept the relationship changes you want, then use Actions > Mark Changes Review as Complete so the notice goes away (p.120 to 121) . The guide's figure on p.121 shows a dialog with Added, Changed and Deleted groups and an Accepted action.

4.45 Choosing rows: criteria, operators, parameters and limits

4.45.1 Entity filter criteria and operators

You met entity criteria while creating an entity (13.4). A criterion is: a column, an attribute (Value or Parameter), an operator, and the value or parameter. The guide's examples use "greater than", "equals" and "IN" (p.118, p.119, p.127). The full operator list is not documented, so do not assume more than those three.

4.45.2 Subset with parameters

Typing the value into the entity means editing the entity every time the requirement changes. A criteria parameter lets you change the value when you run the workflow. The guide (User Guide 10.5.10, p.113, p.118, p.57) says:

• Create criteria parameters in the project (Projects, open the project, Parameters tab, Add) or use global parameters an administrator created. Global ones show an asterisk (*) in lists, and you can import one into the project first (p.57 to 58).
• Parameter types are criteria, connection and owner. Criteria parameter names begin with $$, connection ones with $DBConnection, owner ones with $Param. Data types are String, Numeric or Date. For a Date parameter you enter an expression that says how it is used, for example TO_DATE($$CRIT1,'MM-DD-YYYY') (p.57).
• You choose a default value and whether overriding is allowed. If you do not allow override, you cannot change the value when you run the workflow (p.57).
• Values can live in the project or in an external parameter file. The guide's example: a parameter CRITERIAVALUE, a parameter file line $$CRITERIAVALUE=100, and an entity criterion EMPID > CRITERIAVALUE. To get EMPID greater than 500, change the file to 500 and run the same workflow (p.118).
• For a string or date parameter on a relational source, put the value in single quotes in the parameter file (p.118).

Ignore flags. If you keep values in a parameter file you create yourself, add a flag for each criteria parameter, for example $$CRITERIAVALUE_IGNORE. "N" means use the criterion, "Y" means the workflow skips it. A file created from Test Data Manager already contains the flags, set to "N" (p.118, p.309 to 310).

To make the plan accept parameter values, tick Treat the subset criteria as a variable in the plan's connection options and give the parameter file name and path (p.279) . At run time, in the Execute Workflow window, you choose where values come from (p.310 to 311): the default file named in the plan settings, a parameter file you browse to, or Add Parameter Values from the project. Criteria parameters do not appear in that list by default: click Select Parameter and add them, because "a workflow does not include parameters that do not appear in the list" (p.311).

4.45.3 Database functions in entity criteria

You may want a date rule such as "hired in the last three months". The guide (p.118 to 119) warns that writing a database function directly in the criterion makes workflow generation fail, because TDM does not recognise it as a function and cannot pass it on. The documented workaround: choose the operator IN and write a SELECT query in the Value field, for example select HIREDATE from <table> where HIREDATE > add_months(current_date, -3). TDM then passes the query to the database. The ShopSphere version of this idea would be a query on ORDERS.order_date.

4.45.4 Filtering again inside the plan

You can also set criteria in the plan itself without editing the entity (User Guide 10.5.10, p.294, p.304) . In the plan's component list you pick a subset component, then a column in the Criteria panel. TDM shows existing criteria, which you can change or add to. You then choose a method:

The plan creation steps put it like this: "To limit the subset results, click Limit and choose to limit by percentage, by absolute value, or by defining an interval of rows to create" (p.304). You cannot override the subset criteria of an XSD component in the plan (p.304).

• Percentage: for example 10 percent. Natural fit for the ShopSphere goal, but check how TDM applies it to related tables.
• Absolute value: for example 500 rows. Handy when the test environment has a hard size cap.
• Interval: a defined interval of rows. The guide gives no example.

Ways of choosing rows: by condition (entity criteria and plan Basic/Advanced criteria), by parameterised condition, and by quantity (Limit).

4.45.5 Subset and masking in one plan

A plan can hold both subset components and masking components. The guide says the subset component gets priority: TDM does the subset first, then applies masking rules to the columns of the subset data. Masking is not applied to source data that is outside the subset, "even if the data is part of a cascade" (User Guide 10.5.10, p.275) . You can also keep them in separate plans (p.304). the Informatica masking chapter covers the masking half.

4.46 Subsetting XML: XSD sources in brief

XML data is a tree, not a set of tables, so entities and driving tables do not apply. Instead you put a filter on an element or attribute and set a scope: the level of the tree at which the filter is applied. You can apply one filter per hierarchy, and the scope changes the outcome (User Guide 10.5.10, p.114, p.124) .

The procedure (p.124): import the XML source definition from the PowerCenter repository into the project; click Discover > Files; open the XSD file; select an element or attribute; on the Criteria tab of the Details tab click Edit; choose an operator; enter a value (you cannot use a parameter here); choose the scope; click OK. Then add the XSD file to a plan and run it.

The guide's worked example (p.127 to 134): a master XML file lists departments, each with an address, employees and contractors, each with a name, address and skill. Test group A wants employees skilled in Java; group B wants departments located in BLR.

• Group A: criteria on the element Skill on the Employee branch, operator Equals, value JAVA, scope Employee. The output has all Java employees. Because Department is an ancestor of Employee, the departments that have such employees come along with all their child elements.
• Group B: delete the Skill criterion, then put a criterion on City: Equals, BLR, scope Department. The output has every department whose address city is BLR, with all child elements.

The lesson carries over to relational data: the level where you filter decides which ancestors and descendants appear. For the ShopSphere tables the equivalent of "scope" is your choice of driving table and relationship types.

4.47 Managing subset components

Components live in the TDM repository and are reused by plans, so changing them has side effects. The guide's housekeeping tasks (User Guide 10.5.10, p.124 to 126) :

4.48 The whole workflow, start to finish

The guide's own summary of "Creating a Data Subset" is seven steps (User Guide 10.5.10, p.126) .

1. Create a project and add the data sources.

   A project is the workspace that holds sources, parameters, components and plans. The guide advises separate projects for XSD sources and relational sources. If you ran discovery in the Informatica discovery and profiling chapter, it is the same project.

   The tables of the source appear in the project, with constraints from physical keys.

2. Optional: create connection, owner or criteria parameters.

   Do this now if you want the same plan to serve different values later (13.7).

3. Create the subset components for your source type.

   Relational and flat file: entities and groups. XSD: criteria with scope on elements and attributes.

   Entities and groups listed under Define | Data Subset.

4. Optional: optimize the relationships in the entity.

   Reduces over-pulling by turning unneeded major relationships into minor ones (13.6).

5. Create a plan and add the subset components and data sources.

   The plan ties together what to copy, where from and where to. Choose the integrity option, connections (source, target and a staging connection if needed), and any plan-level criteria or Limit.

6. Run the workflow.

   Generate a workflow from the plan (the PowerCenter Repository Service generates the mappings) and execute it (the PowerCenter Integration Service does the extraction and load). You can do both in one step with Actions > Generate and Execute (p.308, p.310).

7. Monitor progress.

   Watch status in the Workflow Executions view: In Queue, Running, Success or Error. Click the Job ID to open the workflow log (p.311 to 312).

Related: the Informatica plans and workflows chapter (plans and workflows), the Informatica discovery and profiling chapter (connections and constraint discovery) and the Informatica TDM tour (screens).

4.49 Worked example: ShopSphere down to 10 percent

4.49.1 The goal

ShopSphere's QA team wants a test database with about 10 percent of customers, with all their orders and order lines, and the products on those lines. The source has 20 customers (1001 to 1020), 36 orders, 80 order lines and 12 products. Target: 2 customers.

4.49.2 Before: the source rows we care about

(Order totals in ORDERS would be 1498.00, 249.50, 120.00, 1058.00 and 80.00. Customer 1003 and the others have their own orders that we want to leave behind.)

4.49.3 Steps

1. Confirm the constraints exist.

   An entity follows constraints. Open the project's tables and check that ORDERS to CUSTOMERS, ORDER_ITEMS to ORDERS and ORDER_ITEMS to PRODUCTS are listed. If the PRODUCTS link is not declared in the database, add it as a logical foreign key in TDM (p.109 and p.116; this does not alter the source).

   Three relationships visible, each with Input Type physical or logical.

2. Create a criteria parameter.

   Projects, open the project, Parameters, Add. Name MAX_CUST_ID, type criteria (TDM builds the name $$MAX_CUST_ID), data type Numeric, default 1002, override allowed (p.57).

   The parameter is listed in the project. Setting 1002 gives 2 of 20 customers, which is 10 percent.

3. Create the entity.

   Define | Data Subset, Actions > New > Entities, name ShopSphere_Customer_Entity, Select Table = CUSTOMERS, Save (p.116 to 117).

   The map shows a star on CUSTOMERS and links to ORDERS, ORDER_ITEMS and PRODUCTS. Every link is major by default (p.119).

4. Add the filter.

   Edit, Entity Criteria, Add Criteria, choose customer_id. Attribute Parameter, operator less-than-or-equal, parameter MAX_CUST_ID. Save (p.117).

5. Deal with PRODUCTS before you run anything.

   See "Option A versus a major relationship" below. Set the ORDER_ITEMS to PRODUCTS relationship to minor by clicking the link in the Entity Map and changing Type (p.117), or use Actions > Optimize Relations (p.119).

   The link is marked minor; the entity needs a criterion first for Optimize Relations to work.

6. Create the plan.

   Execute, Actions > New, enter a name, click Add Subset Components, select the entity, Next. Set source and target connections and a staging connection (recommended: the source connection). Tick "Treat the subset criteria as a variable" and give the parameter file name and path. Keep Data Integrity for Subset at its default, Transactional Integrity for Major Relation Type (p.279, p.287, p.304 to 305).

   The plan appears in the project.

7. Generate and execute.

   Open the plan, Actions > Generate and Execute, choose the Integration Service and run now. In the parameter section, select MAX_CUST_ID using Select Parameter so it is included, and keep the value 1002 (p.310 to 311).

   Status moves from In Queue to Running to Success in the Workflow Executions view.

8. Verify the target.

   Count rows and check there are no orphans (SQL below). TDM's job is to keep integrity, but you confirm.

-- Row counts in the target (general SQL, adjust schema names)
SELECT 'CUSTOMERS' t, COUNT(*) n FROM CUSTOMERS
UNION ALL SELECT 'ORDERS', COUNT(*) FROM ORDERS
UNION ALL SELECT 'ORDER_ITEMS', COUNT(*) FROM ORDER_ITEMS
UNION ALL SELECT 'PRODUCTS', COUNT(*) FROM PRODUCTS;

-- Orphan check: order lines whose order is missing (expect 0)
SELECT COUNT(*) FROM ORDER_ITEMS i
LEFT JOIN ORDERS o ON o.order_id = i.order_id
WHERE o.order_id IS NULL;

4.49.4 After: what lands in the target

With the minor link, the target holds exactly the two customers, their five orders, seven lines and the four products on those lines. Meera (1003) and the other 17 customers are gone, and nothing points at a missing row.

4.49.5 Option A versus a major relationship

Why change PRODUCTS to minor? With the default (all major), the loop from 13.6 applies: line 9001 pulls in its parent product P-100. As a major parent, P-100 then selects all order lines for P-100, from every customer. Those lines pull their orders, the orders pull their customers, and so on, until nothing new is added. A popular product like P-100 can connect nearly everyone, so "10 percent of customers" turns into most of the database (the right-hand column above). Minor means: bring the product along, but do not let the product pull its other lines.

Major PRODUCTS link                  Minor PRODUCTS link
customer 1001                        customer 1001
  order 5001 -> line 9001              order 5001 -> line 9001
     product P-100  --pulls-->            product P-100 (stops here)
        ALL lines with P-100
           -> other orders
              -> other customers

4.49.6 Option B: reference table as a group

PRODUCTS is small and looks like reference data. Another valid design: disable the ORDER_ITEMS to PRODUCTS relationship in the entity (p.120) so the entity is just customers, orders and lines, then create a group containing PRODUCTS (tick it from the residual tables, p.116) and add both the entity and the group to the plan. The target gets all 12 products (unfiltered) and still has no orphan lines. The trade-off: 8 unused products travel along, which is harmless here but would matter for a 2-million-row table. Pick Option A for big parent tables and Option B for small reference tables.

To mask the customers just selected, see the Informatica masking chapter.

4.50 Lab: subsetting ShopSphere

4.51 Common mistakes and gotchas

4.52 Recap