Informatica TDM (2): masking, generation, plans and workflows

Data masking replaces real sensitive values with realistic fake ones, so a tester sees "Divya Iyer" where production has "Asha Menon". The general idea is in the masking concepts chapter; this section covers how Informatica TDM does it.

5.1 Words to know first

Masking technique

The logic that replaces a value, such as "pick a name from a list" (substitution) or "swap values between rows" (shuffle).

Masking parameters

The options of a technique, such as the dictionary to use, the seed, or the range of dates allowed.

Masking rule

A technique plus its parameters, saved under a name. "FirstName_Substitution" is a rule. The same technique with different parameters is a different rule.

Data domain

A named group of columns that mean the same thing (for example "Social Security Number"), found by patterns in the data or in column names. You can attach preferred masking rules to it.

Policy

A bundle of data domains (and so their rules) that you reuse across projects. It holds no data source itself.

Deterministic / repeatable

The same input always gives the same masked output, so "Asha" becomes the same fake name in every table and in every run.

Seed

A starting number that makes repeatable output reproducible. Same seed, same rule, same input, same result.

Dictionary

A flat file or database table of replacement values used by substitution, for example a list of first names.

Mapplet

A reusable group of PowerCenter transformations with input and output ports. TDM can import one as a custom masking rule.

Value cascade / auto cascade

Ways to make related columns receive the same masked value: a value cascade for similar columns you group by hand, an auto cascade for primary and foreign keys.

5.2 The big picture: rules, domains, policies and assignments

5.2.1 Six objects and how they connect

Start with the ShopSphere problem. Your CUSTOMERS table has first name, last name, email, phone, SSN, date of birth, zip code and tax ID, all sensitive. You do not want to configure "how to mask an SSN" nine times in nine projects. TDM lets you define it once and reuse it. The guide's building blocks (User Guide 10.5.10, p.71 to 73, p.135 to 138, p.149 to 150) :

You can create rules, domains and policies in any order. The guide's high-level policy task flow (p.72): create masking rules, define data domains, assign the rules to the domains, create a policy, assign the domains to the policy. You can apply the rules to source columns, data domains, policies and plans (p.136).

5.2.2 Data domains and preferred rules

A data domain (User Guide 10.5.10, p.73 to 76) "represents the functional meaning of a column based on the column data or the column name". The guide's SSN example: social security numbers may sit in a column called SSN, in another table's SOCIAL_SECURITY and even in a COMMENTS column. You define a data expression for the format (regular expression [0-9]{3}-[0-9]{2}-[0-9]{4}) and metadata expressions for column names such as .*SSN* and .*SOCIAL*. Data discovery profiles (see the Informatica discovery and profiling chapter) use these patterns to assign domains to columns; without Data Discovery you assign columns by hand (p.73). Link back to discovery: profiling finds the sensitive columns, domains carry the rule to them.

When you attach masking rules to a domain they are called preferred rules. If you attach several, one is the default rule, applied to all columns of the domain, and you can change an individual column to another preferred rule (p.73). The guide's example: a Last_Name domain with shuffle as the default and substitution as another preferred rule for a different table.

TDM ships three policy packs, PII, PHI and PCI, each listing domains with a default rule (p.77 to 80). The PII pack, for example, pairs First Name Domain with First Name Rule, Phone Domain with Phone Number Rule and Social Security Number Domain with Social Security Number Rule. The guide does not say which technique each default rule uses, so open a rule in your install to see.

5.2.3 The task flow

A masking plan must contain at least one rule or one policy (User Guide 10.5.10, p.135). The PowerCenter Integration Service does the masking when the workflow runs. If a plan has both subset and masking components, subsetting happens first (p.275), as in the Informatica data subset chapter. Plans are explained in the Informatica plans, workflows and warehouse chapter, and the screens in the Informatica TDM tour.

5.3 Creating masking rules

A rule is created in the Policies view, then added to a project, then assigned to columns. The guide recognises three kinds (User Guide 10.5.10, p.136 to 138) :

5.3.1 Creating a standard rule

1. Click Policies, then Actions > New > Masking Rule.

   The Rule Wizard opens (User Guide 10.5.10, p.139).

2. Enter a name and optional description. Pick the datatype of the column the rule will be applied to.

   Techniques depend on datatype: you cannot blur a string by a percentage. A rule can be created for string, numeric and date types (p.72).

3. Select the Standard masking rule type. Tick Override Allowed if users may change parameters when they assign the rule. Click Next.

   Override is described below. The next screen changes depending on the masking technique you select (p.139).

4. Enter the masking parameters.

   These decide exactly how values are masked: dictionary, seed, range, mask format and so on. Section 5.6 lists them per technique.

5. Enter the exception handling options.

   Decide what to do with nulls, empty spaces and errors (14.5). Click Finish (p.139 to 140).

   The rule appears in the Policies view and can be edited, copied (Actions > Duplicate) or deleted there (p.139, p.146).

5.3.2 Previewing a rule: the Rule Simulator

The guide describes a built-in preview so you do not have to run a full workflow to see what a rule does (User Guide 10.5.10, p.137, p.140) . It works for standard rules only (the text says "simulate the output of a standard rule").

1. Policies view, click a masking rule name to open its Rule Properties, then click Rule Simulator.

   This opens the simulator tab.

2. Choose a source in Source Details: Default, My Test Data or Connection, then click Edit.

   Default uses sample data supplied by TDM. My Test Data lets you type rows (up to 100). Connection reads real rows (the Configure Source Details page opens).

3. If Connection: open the Connection tab, pick connection and owner, Browse to the table, choose the column.

   By default up to 10 random columns are listed; type the column name if yours is missing. Enter the number of rows: default 20, maximum 100. Click OK.

4. Click Go.

   The Rule Simulator tab lists original values beside masked values.

5. Adjust the rule and run the simulation again on the same data.

   The latest simulation configuration is stored in the browser cache, and clearing the cache deletes it (p.137).

5.3.3 Override

With Override Allowed ticked, whoever assigns the rule may change its parameters for one column (p.136, p.146): select the column, Actions > Override, edit, Save. The Override column then reads Yes-Overridden. Different overridden seeds on columns that must match will break the match.

5.3.4 Creating a mapplet rule

The wizard path (p.140 to 141): Policies, Actions > New > Masking Rule, name, datatype, Mapplet masking type, browse to the exported XML (for Hadoop, select Hadoop and browse to a Developer tool mapplet), Next, mark at least one input and one output column as mandatory, Finish. Details and limits are in 14.9, which also walks through building a small mapplet in PowerCenter first.

5.3.5 Creating an advanced rule

1. Policies, Actions > New > Masking Rule. Enter a name and description, choose Advanced, click Next.

   Advanced rules combine techniques (User Guide 10.5.10, p.141).

2. In Input Columns click Add New. Give the name, datatype, precision and scale. Choose whether assigning it is mandatory in every project.

   You cannot enter masking properties on input columns, because input columns just receive the source value.

3. In Variable Columns click Add New. For each, enter an expression, a masking rule or a dependent column.

   Variable columns are scratch fields for intermediate results. If you apply a masking rule, say which input column feeds it.

4. In Output Columns click Add New. Enter an expression, a masking rule or a dependent column.

   The output column receives the final value written to the target.

5. Click OK and finish.

   The column names in the rule do not have to match source column names; you map them at assignment time (p.156 to 157).

The full parameter tables for advanced rules are in 14.8.

5.4 Assigning rules to columns

5.4.1 Add rules to the project first

Rules live in the Policies area; a project only sees them after you add them. Projects, open the project, Overview > Policies, Actions > Add Additional Rules, tick your rules, OK; they appear under Additional Rules (User Guide 10.5.10, p.141 to 142) .

5.4.2 The Define | Data Masking view

Assignment happens in the project's Define | Data Masking view (p.138 to 139). Each row is one column of the source.

5.4.3 Assigning a standard rule, step by step

1. Open the project, click Define | Data Masking, select a column.

   Choose the column that needs masking, for example CUSTOMERS.first_name.

2. If Domain is blank, click the Policy column and pick a policy that contains the rule you want.

   The list of rules comes from the data domain's preferred rules, then the policy's rules (p.142, p.145).

3. Click inside the Masking Rule column and pick a rule.

   Domain preferred rules are at the top; the rest of the policy's rules are below. For a substitution rule you can set the rule assignment parameters (lookup condition, unique substitution column, see 14.6). For a mapplet or advanced rule you map columns (below).

4. Click Save for each column.

   If you move to another row without saving, TDM discards the assignment (p.145).

5. If you changed an assignment that other objects use, the Impacted Objects dialog lists plans, columns and domains.

   Click Export for a CSV, Continue to save. Regenerate and rerun the affected plans (p.142).

5.4.4 Assigning a mapplet or advanced rule (port mapping)

These rules have named input and output ports, so you map source columns to them (User Guide 10.5.10, p.142 to 143) . Pick the rule in the Masking Rule column; a Custom Rule Assignment or Advanced Rule Assignment dialog opens. Select a source column and a same-datatype rule input port, click Link (or Show Simple), then Next, link rule output ports to source columns, Save. For an expression cascade in an advanced rule, Add Associated Table lets you combine two tables with a join condition. Sybase to Hadoop limits a custom rule to 20 columns (p.143).

5.4.5 Many columns, many rules

• One rule on many columns: search for similar columns, for example all ID columns, tick them and click Single Rule Assignment. Pick a policy and a masking rule, click OK (p.147 to 148). The guide's figure on p.148 shows this dialog.
• Clearing: select the column and click Clear Assignment, confirm, and Continue if the Impacted Objects dialog appears (p.148).

5.4.6 Cascades: keeping values consistent between tables

The ShopSphere customer_id is a primary key in CUSTOMERS and a foreign key in ORDERS. If you mask the key but the foreign key keeps its old value, the join breaks. TDM handles this with cascades (User Guide 10.5.10, p.90 to 91) :

• Auto cascade: masks a primary key in a parent table and the related foreign keys in child tables with the same value. Applied by default to tables with key constraints; can be switched off or on. Needs key constraints between parent and child. Not available for XSD, MongoDB and Cassandra sources.
• Value cascade: for similar columns not linked by key constraints (the guide's example: employee_num in one table and EmpID in another). Select the columns in Discover | Columns, Actions > Set as similar value columns, and choose a driving column. A rule assigned to the driving column is assigned to all the group. The columns must have the same type. In the masking view the driving column shows the number of other columns in the cascade.
• Not for computed columns: you cannot apply masking or generation rules to computed columns or build cascades on them (p.90).

5.4.7 Changing or deleting rules after assignment

Editing or deleting a rule shows an Impacted Objects dialog, but changes do not flow into plans: regenerate and rerun them. Deleting a rule does not remove it from the columns, domains and plans using it. If you change a custom rule's field names or types, import the mapplet again (p.144 to 146) . For XSD sources you apply rules to XML elements and attributes in Discover > Files (p.144); shuffle is not allowed for XSD (p.135).

5.5 Parameters that most techniques share

Before the techniques, three ideas appear again and again (User Guide 10.5.10, p.153 to 155) .

5.5.1 Repeatable output and the seed

Repeatable output returns deterministic values: the same input gives the same masked value, every time you generate and run the workflow. Use it when the same value occurs in several tables and the target tables must show the same masked value. The guide's example: customer John Smith has account numbers 1234 and 5678 in many tables; masking turns John Smith into Frank Martinez everywhere, 1234 into 6549, 5678 into 3214, on every run.

The seed is the generator's starting point: 1 to 999, default 1 (p.154; the key masking string table says 1,000, p.168). Give the same seed to columns that must match, such as Cust_ID in four tables. You can put the seed in a parameter file variable. A TDM administrator can set a central seed that overrides every other seed (p.155).

Substitution and expression masking keep a storage table of source and masked value pairs; you name the storage connection in the plan (p.286: IDM_SUBSTITUTION_STORAGE, IDM_EXPRESSION_STORAGE), and expression masking fails without it (p.163). Key masking is deterministic from source, rule and seed (p.164). Repeatable means "same in, same out", not "reversible".

5.5.2 Exception handling

Every rule has an Exception Handling section that decides what happens with nulls, blanks and errors (p.155):

5.6 The masking techniques, one by one

The guide lists these techniques (User Guide 10.5.10, p.152 to 153): Advanced, Credit card, Email address, Encryption, Expression, IP address, Key, Mapplet, Nullification, Phone, Random, Shuffle, SIN, SSN, Substitution and URL. Advanced and Mapplet are in 14.8 and 14.9; the other fourteen follow.

5.6.1 Substitution masking

What it does. Replaces a value with a "similar but unrelated" value looked up in a dictionary, a flat file or database table of replacement values (User Guide 10.5.10, p.176 to 177) . Works on date, numeric and string columns. The guide calls it an effective way to replace production data with realistic test data; its examples are a dictionary of addresses, or the InvalidSSN dictionary of Social Security numbers that are not valid.

ShopSphere example. A flat file dictionary FIRSTNAMES.csv:

SNO,GENDER,FIRSTNAME
1,F,Divya
2,M,Arjun
3,F,Latha
4,M,Karthik

Gotchas.

• Choose Unique Substitution Data only if the dictionary is bigger than the number of distinct values in the column. A dictionary of 4 names cannot uniquely replace 20 customers; the operation fails.
• Two customers with the same first name get the same replacement only if Repeatable Output is on (the storage table remembers pairs). Without repeatability, each row can get a different value.

Repeatability. Tick Repeatable Output and set a seed. Name the storage connection in the plan.

5.6.2 Shuffle masking

What it does. Swaps values of one column between rows of the same table. The guide's example (p.173) uses four rows: Tom Bender, Sue Slade, Bob Bold, Eli Jones. After a shuffle of the first-name column they read Bob Bender, Eli Slade, Tom Bold, Sue Jones. You can restrict which values may be swapped by a lookup condition or a constraint. Works on date, numeric and string columns .

Gotchas.

• It cannot be used for XSD sources, and not when both source and target are Hadoop HDFS connections (p.135, p.173). A Spark engine cannot do it (p.62).
• Empty strings: set Null and Empty Spaces to Treat as Value, or the empty columns are skipped (p.173).

5.6.3 Key masking

What it does. Produces deterministic results: the same source value, rule and seed always give the same masked value. Works on date, numeric and string columns (User Guide 10.5.10, p.164 to 168) .

The mask format characters (shared with random masking; p.165):

If no mask format is given, each source character is replaced with any character. A mask format longer than the input is truncated; shorter than the input means the characters at the end are not masked. Lowercase letters you type are converted to uppercase (p.164).

Other forms the guide describes.

• Delimited strings: the guide's example masks "John Donne" consistently when it appears as the strings John and Donne in two columns and as "Donne, John" or "John Donne, General Manager" in two more. Assign delimited string masking with comma and space as delimiters to the last two (p.166 to 167).
• Dates: output dates fall between 1753 and 2400 and are always valid. A leap year maps to a leap year; a 31-day month to a 31-day month; February stays February (p.167). Give the same seed as another column to get matching dates.
• Numbers: to keep a primary-key to foreign-key relationship intact between two tables, give the key column in each table the same seed. Deterministic output means 1001 in CUSTOMERS and 1001 in ORDERS both become the same new number (p.167).

Gotchas. Output is not guaranteed unique when there are fewer source characters than result characters (p.165). By default a masked character may differ from the original only in case ("x" to "X"); Case Insensitive treats both cases as one character so it cannot. To get identical output for "John Brown" and "JOHN BROWN", convert to one case first with a preprocessing expression (p.166).

5.6.4 Random masking

What it does. Produces random, non-repeatable output: the same source value and rule can give a different result each run. No seed. Works on date, numeric and string columns (User Guide 10.5.10, p.169 to 173) .

Numeric blurring with a source value of 66 (guide table, p.170):

Bounds must be zero or greater. Date blurring takes a unit (year by default), a low and a high bound: unit year, bounds 2 and 2 on 02 February 2006 gives a date from 02 February 2004 to 02 February 2008 (p.170). The unit list differs between p.170 (to hour) and p.172 (to second); check your drop-down.

Gotchas. No seed, so reruns differ and the same customer will not match across tables. A short mask format leaves trailing characters unmasked: +++DD on a 6 digit zip leaves the last digit as it was. If you need repeatability use key masking.

5.6.5 Encryption masking

What it does. Applies an encryption algorithm to a string column (User Guide 10.5.10, p.161 to 162) . Two families:

The three format preserving options in the guide's own words (p.161 to 162):

• Preserve Format and Metadata: keeps format and length. Upper case becomes upper case, lower case lower case, digits digits, special characters special characters. Example: Abc123@xyz.com may become Mpz849#dje!kuw; with "@" and "." set as Do Not Encrypt Characters, Mpz849@dje.kuw.
• Preserve Metadata: keeps only length. Alexender may become jl6#HB91v.
• Change Metadata: length and format change. London may become Xuep@8f5 or 6ky#ke33h*we. Before using it you must raise the column precision in the database (and in TDM) using: Required Precision = (1.33 x Original Precision) + 24, rounded up. After changing the database column, reimport metadata or edit the precision in Test Data Manager.

Use it when you must be able to get the original back (decryption is described only for format preserving encryption) or the application checks the shape of a value. Reversibility is also a risk: anyone with the key or pass phrase and the rule can recover production values The guide does not say where the pass phrase is entered. Same key and configuration give the same output for the same input.

5.6.6 Expression masking

What it does. Applies a PowerCenter-style expression to a column to create or change data. You build the expression in the Expression Builder by clicking columns, functions, variables and operators. Any datatype (User Guide 10.5.10, p.162 to 163) .

Guide example: to join first names with the text ABC, enter CONCAT(FirstName,'ABC'). On ShopSphere Asha becomes AshaABC, a poor mask on its own but it shows the idea; a better use builds a value from other pieces, such as 'CUST' || customer_id.

Gotchas (p.163): use it for one column (use advanced masking for several); make the expression return the column datatype, or you get zero (numeric) or NULL (string); one expression's output cannot feed another; repeatable output needs a storage table in the plan or the operation fails; no repeatable expression masking for Hadoop.

5.6.7 Nullification masking

What it does. Replaces the value with NULL. Works on binary, date, numeric and string data. No parameters (p.168) .

Use it for columns the test environment does not need: the strongest privacy result. The target column must allow NULL, and the application may expect an SSN to exist.

5.6.8 SSN masking

What it does. Applies a built-in mask for Social Security numbers on string columns (User Guide 10.5.10, p.175) . It generates valid-looking numbers and tries to avoid numbers the US Social Security Administration has actually issued, using the High Group List. The service reads that file from the PowerCenter installation's infa_shared/SrcFiles directory; you can download the latest version from the Social Security Administration. Any nine-digit input is accepted, with any delimiters.

Parameters. Repeatable Output with a seed value; an SSN randomization option, based on the method the Social Security Administration developed to remove the geographic meaning of the first three digits. No other parameter names are documented.

Gotchas. TDM "cannot return all unique" SSNs, because it cannot return numbers already issued, so test for duplicates if ssn has a unique constraint. Any delimiter style is accepted; check the output style suits your validation. Repeatability. Tick Repeatable Output, tick Seed, enter a value.

5.6.9 SIN masking

SIN is the Canadian Social Insurance Number (general background). The technique handles a nine-digit SIN with any delimiters on string columns (p.175) : no delimiters in gives none out, otherwise xxx-xxx-xxx. Start Digit forces the first digit; Repeatable Output takes a seed. ShopSphere has no SIN column.

5.6.10 Phone masking

What it does. Changes phone numbers while keeping the original format; string columns only (p.169) . The guide's example: (408)382 0658 becomes (408)256 3106. Input may contain digits, spaces, hyphens and parentheses; alphabetic and special characters are not masked. Example: 9876543210 might become 9876012345. No parameters are listed, and which digits are preserved is not documented; in the guide's example the first three stayed, but do not assume that holds for every format.

5.6.11 Credit card masking

What it does. Applies a built-in format to credit card numbers on string columns, producing a logically valid number with a valid checksum (User Guide 10.5.10, p.158 to 159) . The source must be 13 to 19 digits with a valid checksum under card industry rules. It may contain numbers, spaces and hyphens. Invalid characters or length: the service logs an error in the session log and applies a default credit card mask. The six-digit Bank Identification Number (BIN) at the front is not masked. The guide's example: 4539 1596 8210 2773 may become 4539 1516 0556 7067.

5.6.12 Email masking

What it does. Generates realistic email addresses on string columns. It can build the user name from the masked first and last names, and use a constant or a dictionary-chosen domain (p.159 to 161) . Output is always ASCII. It can be repeatable between workflows or within one workflow.

Gotchas. The From mapping option needs the masked name columns, which usually means one advanced rule that masks the names and builds the email (14.8). An email still containing "asha.menon" defeats the name mask.

5.6.13 IP address masking

Splits an IP address into four numbers and masks the network number within its range; string columns (p.164) . Class A stays Class A and 10.x.x.x stays 10.x.x.x: 11.12.23.34 may become 75.32.42.52, 10.23.24.32 may become 10.61.74.84. No parameters are listed.

5.6.14 URL masking

Masks URLs on string columns by finding :// and masking what follows (p.177) ; the protocol is not masked. http://www.yahoo.com may return http://MgL.aHjCa.VsD/, ASCII only and possibly invalid. No parameters are listed.

5.7 Comparison and decision table

From the guide's technique sections (User Guide 10.5.10, p.152 to 177); "Not described" means the guide does not say.

5.7.1 Choosing a technique: questions to ask

• Does the test environment need this column at all? If not, nullification is the simplest and safest.
• Does the application check a format (SSN pattern, card checksum, phone layout, URL)? Use the matching built-in technique (SSN, SIN, credit card, phone, email, IP, URL) so the checks pass.
• Does it need real-looking text such as names and places? Substitution (new values from a dictionary) or shuffle (existing values moved between rows).
• Is the value an identifier that appears in several tables and must stay joinable? Use key masking with the same seed, or rely on auto cascade for keys with constraints, or repeatable substitution with the same seed (14.4, 14.5).
• Is it a number or date where only "near the original" matters? Random masking with blurring.
• Do you need to undo the masking later? Format preserving encryption, knowing the privacy trade-off.
• Does a masked value depend on more than one column? An advanced rule, or a mapplet if PowerCenter logic already exists.

5.8 Advanced masking rules in depth

An advanced rule is a small program built from columns of three types (User Guide 10.5.10, p.137 to 138, p.156 to 158) : input (receives the source value), variable (a scratch column that holds an intermediate result from an expression or a masking technique, so you can combine several techniques) and output (the target column that receives the final value). Column names inside the rule are your own; at assignment time you map them to real columns.

5.8.1 Worked example: a masked full name

The guide's example (p.158) builds a masked full name from first and last name, and it fits ShopSphere. Say the target has a full_name column derived from first_name and last_name:

Result for customer 1001: input Asha and Menon; variables Divya and Iyer; output "Divya Iyer". The guide's advice: build expressions by point-and-click to minimise errors, and check that the expression returns the output column's datatype.

5.8.2 Worked example: first names by gender and nationality (guide)

The guide's name example (p.177 to 182) keeps gender and nationality: a dictionary with SNO, COUNTRY, GENDER, FIRSTNAME; a string substitution rule with Lookup column GENDER; then an advanced rule with inputs in_Country, in_FirstName, in_Gender, variable columns var_FirstName_us (condition in_Country='us') and var_FirstName_jp (condition in_Country='jp') that each apply the substitution rule, and output o_FirstName. A condition on a variable column lets one rule choose a different substitution per row.

5.8.3 Worked example: shuffle addresses, keep ZIP (guide)

The shuffle address example (p.183 to 189) shuffles address lines together and leaves ZIP unmasked: a Random, Constrained shuffle rule, then an advanced rule where v_AddressLine3 applies the shuffle with lookup column i_ZIP, and v_AddressLine1 and v_AddressLine2 use dependent masking on it so they follow the same row.

5.9 Custom rules: mapplets and the Data Masking transformation

Three parts: what a mapplet is, a worked build in PowerCenter, and how Test Data Manager uses a mapplet once it exists. The build follows the LearningMart video "How to create mapplet in informatica"; the TDM side follows the User Guide.

5.9.1 The general idea

A transformation in PowerCenter is one processing step on rows of data (calculate, look up, mask). In the LearningMart video "How to create mapplet in informatica", the presenter defines a mapplet as a reusable object that you create in the Mapplet Designer. His motivating case: many mappings must join first name and last name into a full name, so you build that logic once and reuse it everywhere, like a function you write once and call from many programs. A mapplet therefore has input ports, some transformations inside, and output ports. A Data Masking transformation applies masking techniques to a port; a mapplet that contains one is how you bring a PowerCenter masking technique into TDM.

5.9.2 Build a mapplet: full name from first and last name

This follows the video's build step by step. It is a PowerCenter task done in PowerCenter Designer, not in Test Data Manager. The video does not state the PowerCenter version.

1. Open the Mapplet Designer and name the mapplet.

   The Mapplet Designer is the part of PowerCenter Designer where mapplets are built. Use a clear prefix such as MLT_ so mapplets are easy to tell from mappings.

   An empty mapplet workspace with your name on it.

2. Add a Mapplet Input transformation with two input ports, first_name and last_name.

   The video says a mapplet has two edge pieces: an input transformation whose ports carry data in, and an output transformation whose ports carry data out. It opens the Transformation tab at the top of the Designer, chooses the Mapplet Input transformation, names it and adds the two ports.

   A Mapplet Input box listing first_name and last_name.

3. Add an Expression transformation and drag the two input ports into it.

   Joining two values needs a calculation, and the video uses an Expression transformation for that (a step that computes a new value for every row). Dragging the ports copies them in as inputs.

   The Expression transformation shows first_name and last_name, linked from the Mapplet Input.

4. Create a new output port in the Expression transformation called full_name, with a size above 40.

   The video explains the size: this port holds two values joined together, so it must be longer than a single name. Make it at least the two input sizes plus one for the space, because a smaller port risks cutting off long names.

   A full_name port flagged as an output port.

5. Write the expression: first_name || ' ' || last_name.

   You can use the concatenation function or the pipe operator ||; the video picks the pipes, with a space between the two names so they do not run together. It then validates the expression and clicks Apply and OK.

   The expression editor reports that the expression is valid.

6. Add a Mapplet Output transformation, name it, and drag full_name into it.

   Without this step nothing leaves the mapplet. The output port name is what a mapping or a TDM rule will see.

   A Mapplet Output box with the full_name port, linked from the Expression.

7. Validate from the Mapplets menu, then save.

   Validation catches broken links and bad expressions before a mapping depends on the mapplet. The video does it at the Mapplets menu, Validate.

   A message that the mapplet is valid, then a saved mapplet.

8. Create a mapping with an EMPLOYEE source and a target table, and drag the saved mapplet in between.

   This proves the mapplet works. The video's target has a full name column. The mapplet appears with its input and output ports.

   Source, mapplet and target on one canvas.

9. Connect first and last name from the source to the mapplet inputs, and the mapplet's full_name to the target's full name column. Save.

   Each connection tells PowerCenter which column feeds which port.

   The mapping validates ("mapping is valid").

10. Create the workflow in Workflow Manager. Check the source and target connection values, and choose Normal load when the target has a primary key. Save.

    A workflow is what actually runs the mapping. The video gives the Normal-load rule without a reason; a common reason is that bulk loading skips some database checking and logging, so it may not suit a constrained target.

    A saved workflow with valid connections.

11. Run the workflow and check the target data.

    Reading the rows is the real test.

    The run succeeds and the full name column shows first and last name together, as in the table above.

5.9.3 From PowerCenter mapplet to TDM rule

Steps 8 to 11 above build a mapping and workflow by hand. TDM does not need those, because the PowerCenter Integration Service creates the mappings, transformations, mapplets, sessions and workflows itself when a plan runs, and the guide warns that editing generated components in the PowerCenter Client may give unexpected results (User Guide 10.5.10, p.141, p.150). What TDM needs is the mapplet. So the sequence is: build and validate the mapplet in PowerCenter (steps 1 to 7), export it to an XML file from the PowerCenter repository, then import it in Test Data Manager as a rule (p.137, p.140).

In the wizard you mark at least one input and one output column as mandatory, so for the video's mapplet you might mark first_name and full_name (p.141). When you assign the rule you link source columns to the input ports and the output port to a column (p.142 to 143).

The video's mapplet contains only an Expression, so it combines values and masks nothing. For masking, the guide says to include one or more Data Masking transformations in the mapplet and that all transformations must be passive (p.150); it does not show how to configure one. The guide's own full-name example is on the generation side: a custom generation rule from a mapplet that combines first and last name from an EMPLOYEES table (User Guide 10.5.10, p.196, p.211; see the Informatica data generation chapter). That is the closest fit for this mapplet.

5.9.4 What the guide says

• Purpose. Mapplet rules let you use PowerCenter masking techniques that are "not available in Test Data Manager" (p.150). The mapplet holds the logic to mask the input columns and return data to the target columns (p.137, p.152).
• How it gets in. You export the mapplet to an XML file from the PowerCenter repository or the Model repository, then import it as a rule (p.137, p.140). To mask Hadoop data you import mapplets built in the Developer tool (p.137). The TDM repository stores the mapplet logic, and you cannot change the mapplet inside Test Data Manager (p.137).
• What it must contain. One or more Data Masking transformations, and all transformations must be passive (they must not add or remove rows) (p.137, p.150). It can have multiple input and output columns (p.137).
• Required ports. Mark at least one input and one output column as mandatory; mandatory columns need source and target assignments (p.137, p.141, p.155).
• Custom masking parameters (p.156): Column Name, Column Type (input or output), Data Type, Precision, Mandatory, Group Name (input, output or another name you gave in the mapplet).
• Exporting the other way. You can also export rules as PowerCenter mapplets (p.150).
• Assignment. Select the rule in the Masking Rule column, then link source columns to rule input ports and rule output ports to source columns (p.142 to 143). Sybase to Hadoop limit: 20 columns.
• Hadoop engine limits. A Blaze engine allows Expression, Data Masking, Case Converter, Comparison, Decision, Labeler, Merge, Parser, Weighted Average, Standardizer and Java Passive transformations in a mapplet rule; Spark allows Expression, Data Masking and Java Passive; Hadoop mapplets cannot use sequence generator, lookup or classifier (p.62, p.137).
• Changes. If you change the name or type of a field of a custom rule, existing assignments do not see it; import the mapplet again (p.144).
• Data Masking transformation session properties (chapter 3, p.52 to 53): SSN High Group File path, DMO Cache Directory, Cache Size (default 8 MB), Shared Storage Table (default disabled) and Storage Commit Interval (default 100,000).

The architecture behind it, with PowerCenter running the workflows that TDM generates, is in the Informatica overview and architecture chapter. Video: "How to create mapplet in informatica" by LearningMart.

5.10 Putting it together: a masking design for ShopSphere

Compared with the originals, emails match the masked names, zip codes keep three digits, dates stay within two years, and the surnames are the same three moved around.

5.10.1 Running the masking operation: the guide's steps

The guide's list for performing a masking operation (User Guide 10.5.10, p.148 to 149) , with reasons:

1. Create a data masking rule.

   The rule is the recipe (14.3). Preview it in the Rule Simulator.

2. Create a project and import metadata.

   The project knows the tables and columns. Use separate projects for XSD and relational sources.

3. Optional: create parameters or import global parameters.

   For example a seed variable in a parameter file, so you can change the seed without editing the rule (p.155).

4. Add the rule to the project.

   Projects only see rules you add (14.4).

5. Assign the rule to a target column (or to an XML element).

   This is where you say "mask ssn with SSN_Rule". Save each row.

6. Create a plan, add the rule, and configure source and target connections.

   A plan holds at least one rule or policy. It states whether data is masked in place in the source database or in stream into a target (p.150). Add a storage connection if any rule is repeatable substitution or expression masking (p.286).

7. Generate and run the workflow.

   PowerCenter creates mappings, sessions and a workflow and runs them (p.150). Use Actions > Generate and Execute, then pick parameter values (p.310 to 311).

8. Monitor the workflow.

   Check Workflow Executions for Success, then open the logs if not (p.311 to 312).

   Target columns hold masked values; source untouched (when masking in stream into a target).

Plans and workflow settings are in the Informatica plans, workflows and warehouse chapter. If you also subset, masking runs on the subset result (p.275), as in the Informatica data subset chapter.

5.11 Hands-on labs

-- General SQL; adjust syntax to your database.
-- 1. SSN keeps its pattern (expect same count as total rows):
SELECT COUNT(*) FROM customers WHERE ssn LIKE '___-__-____';
-- 2. Phone keeps its length (expect all rows; null phones excluded):
SELECT COUNT(*) FROM customers WHERE LENGTH(phone) = 10;
-- 3. tax_id keeps length 10 (expect all rows):
SELECT COUNT(*) FROM customers WHERE LENGTH(tax_id) = 10;
-- 4. Nothing unchanged (expect 0), joining target to a copy of source on id:
SELECT COUNT(*) FROM customers t JOIN src_customers s
  ON t.customer_id = s.customer_id
WHERE t.ssn = s.ssn OR t.phone = s.phone OR t.tax_id = s.tax_id;

5.12 Common mistakes and gotchas

5.13 Recap

Table      Column         Type     Null  Generation Rule        Override
CUSTOMERS  customer_id    int      no    Seq_1001               No
CUSTOMERS  first_name     varchar  no    Dict_FirstName         No
CUSTOMERS  last_name      varchar  no    Dict_LastName          No
CUSTOMERS  email          varchar  no    Adv_Email              No
CUSTOMERS  phone          varchar  yes   Phone_10_digits        Yes-Overridden
CUSTOMERS  ssn            varchar  yes   SSN_like_pattern       No
CUSTOMERS  date_of_birth  date     yes   Rand_Date_1950_2005    No
CUSTOMERS  zip_code       varchar  yes   Set_ZipCodes           No
CUSTOMERS  tax_id         varchar  yes   (default string rule)  

threshold = 5 customers      total_amount band
zip band        under 500    500-2000    over 2000
56xxxx              9           7           0   <- gap
60xxxx              6           2           1   <- below threshold
68xxxx              0           8           5   <- gap

ilmcmd -wf -g -Project Infa_Project -pn Plan_NameMasking
ilmcmd -wf -gs -JobId 360
ilmcmd -wf -ex -Project TDM -pn Plan_NameMasking -is PCInteg
ilmcmd -r -dvn "RESET Version1" -rf "C:\Documents\ResetObject.json" -ris PIS

10:02:11  Info     Workflow wf_PLAN_MASK_SUBSET started
10:02:15  Info     Session s_PLAN_MASK_SUBSET_1 started
10:02:19  Warning  1 row rejected in table ORDERS
10:02:23  Error    Session s_PLAN_MASK_SUBSET_1 failed: could not enable constraint on ORDERS
10:02:24  Error    Workflow wf_PLAN_MASK_SUBSET failed