Expression Rules
Expression rules use expression functions and columns to define rule logic. Create expression rules and add them to a profile in the Analyst tool.
Use expression rules to change or validate values for columns in a profile. You can create one or more expression rules to use in a profile. Expression functions are SQL-like functions used to transform source data. You can create expression rule logic with the following types of functions:
- •Character
- •Conversion
- •Data Cleansing
- •Date
- •Encoding
- •Financial
- •Numeric
- •Scientific
- •Special
- •Test
Expression Rules Process
Use the Edit Profile wizard to create an expression rule and add it to a profile.
The Edit Profile wizard includes an expression editor. Use the expression editor to add expression functions, configure columns as input to the functions, validate the expression, and configure the return type, precision, and scale.
The output of an expression rule is a virtual column that uses the name of the rule as the column name. The Analyst tool runs a column profile on the virtual column. For example, you use an expression rule to validate a ZIP code. The rule returns 1 if the ZIP code is valid and 0 if the ZIP code is not valid. Informatica Analyst runs a column profile on the 1 and 0 output values of the rule.
You can perform the following steps to create an expression rule:
- 1. Open a profile.
- 2. Configure the rule logic using expression functions and columns as parameters.
- 3. Configure the profiling options.
Creating an Expression Rule
Use the Edit Profile wizard to create an expression rule and add it to a profile. Create an expression rule to validate values for columns in a profile.
1. In the Navigator, select the project or folder that contains the profile that you want to add the rule to.
2. In the Contents panel, click the profile to open it.
The profile appears in a tab.
3. Click Actions > Edit > Column Profiling Rules.
The Edit Profile dialog box appears.
4. Click New.
5. Select Create a rule.
6. Click Next.
7. Enter a name and optional description for the rule.
8. Optionally, choose to promote the rule as a reusable rule and configure the project and folder location.
If you promote a rule to a reusable rule, you or other users can use the rule in another profile as a predefined rule.
9. In the Functions tab, select a function and click the right arrow to enter the parameters for the function.
10. In the Columns tab, select an input column and click the right arrow to add the expression in the Expression editor. You can also add logical operators to the expression.
11. Click Validate. You can go to the next step if the expression is valid.
12. Optionally, click Edit to configure the return type, precision, and scale.
13. Click Next.
14. In the Column Profiling panel, select the columns you want to run a profile on. The columns include any rules you applied to the profile. Optionally, select Nameto select all columns.
The Analyst tool lists the name, datatype, precision, and scale for each column.
15. In the Sampling Options panel, configure the sampling options.
16. In the Drilldown Options panel, configure the drilldown options.
17. Click Save to create the rule or click Save & Run to create the rule and then run the profile.