Treatment of problems at the value level
Data format conversion, key conversion, constants, etc.
Challenges
Problem | Examples or details |
Applying format masks to display or reinterpret values | ID'ing gender, ‚F‘ or ‚2‘ or mask date: mm/dd/yy to |
Homogenization of inconsistent production keys | Assignment of unique numerical substitutes for recognition and faster indexing |
Pivoting and unpivoting | Transpose columns to rows and vice versa without duplicates |
Inserting constants or literal character strings | Replacing or adding replacement values to protect or pre/re-label data |
Setting or labeling values | Evaluate a condition to determine a standard label such as ‘NULL'. |
Validation of values | Determine whether the input field is printable or has a specific data type. |
Reformulation of values for adjustment to reference standards | Standardization of different data representations (e.g. country names) |
Slowly changing dimensions in source tables (SCD) | Dissemination of new values (e.g. price change) in stock tables |
Challenges
Problem | Examples or details |
Applying format masks to display or reinterpret values | ID'ing gender, ‚F‘ or ‚2‘ or mask date: mm/dd/yy to |
Homogenization of inconsistent production keys | Assignment of unique numerical substitutes for recognition and faster indexing |
Pivoting and unpivoting | Transpose columns to rows and vice versa without duplicates |
Inserting constants or literal character strings | Replacing or adding replacement values to protect or pre/re-label data |
Setting or labeling values | Evaluate a condition to determine a standard label such as ‘NULL'. |
Validation of values | Determine whether the input field is printable or has a specific data type. |
Reformulation of values for adjustment to reference standards | Standardization of different data representations (e.g. country names) |
Slowly changing dimensions in source tables (SCD) | Dissemination of new values (e.g. price change) in stock tables |
Solutions
The ETL/data management platform IRI Voracity and the IRI CoSort-tool that powers Voracity solve all of the above data processing problems and beyond:
Keys and other data conversions
Data type and format conversions
Simple and advanced report generation
PII masking and user-defined synthesis of test data
with a single 4GL and a data manipulation program called SortCL, which is also available in the Eclipse GUI called IRI Workbench is supported.
With SortCL you can:
Reassign data and other data formats with new composite values and reusable data types
Reassignment of source key names to comply with new ontologies
Pivot (denormalize) for efficient queries and unpivot (normalize) before loading data into the warehouse
Use inline ‘data' statements to add strings at any position and with user-defined repetition numbers
Use conditional data or field logic to indicate qualifying values such as inserts vs. deletes vs. updates, etc.
Use the provided data validation functions or third-party libraries to evaluate data characteristics or quality
Use the integrated Perl Compatible Regular Expression (PCRE) logic to match and reassign data patterns
Edit and report on multiple types of slowly changing dimensions and other changed data
IRI software such as CoSort and RowGen (test data generation) help you to generate unique initial or replacement keys, with functions such as:
1. SEQUENCER, a specially named index field with user-defined start and increment values.
2. random data generation to create random numbers or character strings that can also be sorted and decoupled.
3. ROWID, a field with any name but specified as this attribute with characteristics outside SEQUENCER.
4. UUID (and GUID), a transformation/generation function at field level that can be used in any part of a job.