Data Stratification

4.9 Data Stratification

Data Aggregation and Stratification enables you to summarize large volumes of financial instruments to a manageable scale for processing and reporting purposes. The stratification engine is used to intelligently group financial instrument records for processing. Grouping them in this way vastly reduces the processing time while retaining the necessary accuracy by still providing an instrument-level result. Using this feature is a multi-step process, which includes:

Financial Services users who load account level data into the Instrument/Account tables do not always have a need to process this data at the account level (for example, ALMCS). Many types of instruments carry the same financial characteristics or can be grouped along common lines without compromising processing accuracy. After grouping common data into stratified pools, processes run against this data requires significantly less processing time (that is, reduced from millions of individual records to several thousand, hundreds or even tens). Functional end users require functionality to define how grouping should occur and to execute this grouping (also known as stratification) at their discretion. They also require the ability to run stratification/aggregation multiple times against the same instrument level data set and retain all of the aggregated results as unique data sets.

Understanding what you want and how you want to aggregate and stratify data.
Optionally defining balance or rate tiers to be used for grouping data.
Configuring the Data Map metadata for the source and target data maps that are to be used on the Stratification Definition page.
Setting up stratification definitions and rules in the Stratification Rule pages.
Testing the rules that you create

User and Roles

Role Code	Role Name	Function Code	Function Name
RLSTRATTIERADMIN	Stratification Tier Admin Role	STRATTIERADD	Add Stratification Tier
		STRATTIERDEL	Delete Stratification Tier
		STRATTIEREDIT	Edit Stratification Tier
		STRATTIERCOPY	Copy Stratification Tier Data
		STRATTIERVIEW	View Stratification Tier
		STRATTIERTRACE	Trace Stratification Tier Data
RLSTRATTIERANALYST	Stratification Tier Analyst Role	STRATTIERADD	Add Stratification Tier
		STRATTIERDEL	Delete Stratification Tier
		STRATTIEREDIT	Edit Stratification Tier
		STRATTIERCOPY	Copy Stratification Tier Data
		STRATTIERVIEW	View Stratification Tier
		STRATTIERTRACE	Trace Stratification Tier Data
RLSTRATTIERAUDIT	Stratification Tier Auditor Role	STRATTIERVIEW	View Stratification Tier
RLSTRATRULEADMIN	Stratification Rule Admin Role	STRATRULEADD	Add Stratification Rule
		STRATRULERUN	Run Stratification Rule Data
		STRATRULEDEL	Delete Stratification Rule
		STRATRULEEDIT	Edit Stratification Rule
		STRATRULECOPY	Copy Stratification Rule Data
		STRATRULEVIEW	View Stratification Rule
		STRATRULETRACE	Trace Stratification Rule Data
RLSTRATRULEANALYST	Stratification Rule Analyst Role	STRATRULEADD	Add Stratification Rule
		STRATRULERUN	Run Stratification Rule Data
		STRATRULEDEL	Delete Stratification Rule
		STRATRULEEDIT	Edit Stratification Rule
		STRATRULECOPY	Copy Stratification Rule Data
		STRATRULEVIEW	View Stratification Rule
		STRATRULETRACE	Trace Stratification Rule Data
RLSTRATRULEAUDIT	Stratification Rule Auditor Role	STRATRULEVIEW	View Stratification Rule

Partial Aggregation

An extra filter option is given in the Stratification Definition, which limits the stratification, only for the accounts in the Source Table falling under the filter criteria. If the Filter is not being used, (case for all existing definitions), then the existing behavior remain as is.

For accounts in the Source Table, which does not satisfy the filter condition will not be aggregated and will be moved to the destination table as is. But whether column mappings which are defined in aggregation tab and under defaults will be applicable or not for non-aggregated records, will be dependent on the Apply Column mapping for Non-Aggregated Accounts check-box settings. If this check-box is selected, then the aggregated records column mappings will be applicable to the non-aggregated records. Else, the non-aggregated records will be moved to the destination aggregated table without using the column mappings.

Stratification Action Operations

To create the pools of instruments, identify the operation for each of the attributes. Following choices are avialable in terms of how to populate each attribute in the instrument pool from the instrument data:

Discrete: Directs the Stratification Engine to populate the instrument pool with discrete values.
This option populates the pool with discrete values for an attribute if there is no logical or mathematical way to group instruments with different values, and the attribute is significant for reporting purposes. Selecting the discrete action for an attribute ensures that only instruments with matching values are grouped together in a pool. The discrete action may be the correct choice for important code fields. Be careful when using the Discrete user on amount, date, or rate fields. These types of fields tend to have many discrete values. Typical value for a discrete group by is an ID field or a yes/no flag, amortization type, adjustable type, accrual basis.
Default: Used for attributes that user wants to set to a hard-coded value.
If you are defining a stratification rule that you know is used only by one class or type of product, and you want all pools that are generated from that rule to have the same value for a specific attribute, select this option to force the attribute to have that predefined value. The Default option should be used with caution and only when you are sure that the value is valid for all pools that are created by the stratification rule. When you are unsure, a better choice is to either drop the attribute entirely, or populate the field with discrete values.
Tiers: If you want to group the possible range of values into consistent ranges, the simplest grouping operation to use tiers.
Identify the lower and upper value of tiers, and Stratification engine that groups the instruments into that range. Tiers are typically used for balance and rate fields to assist in grouping similar data into tranches or pools. Tiers should be defined on columns that impact the way assumptions are made, such as prepayments, interest rate margins or other meaningful assumptions.

Aggregation Action Operations

The following are the default aggregation action operations:

Amount Action

The following options are possible when aggregating records for BALANCE type columns:

Average: Calculates the average value of an attribute for all instruments in the pool.
Max: Maximum value of a given attribute for all the instruments that are part of the pool.
Min: Minimum value of a given attribute for all the instruments that are part of the pool.
Sum: Total sum value for a numeric attribute.
Default: Applies a specified default value.

Rate/Percent Action

The following options are possible when aggregating records for RATE type columns:

Average: Calculates the average value of an attribute for all instruments in the pool.
Max: Maximum value of a given attribute for all the instruments that are part of the pool.
Min: Minimum value of a given attribute for all the instruments that are part of the pool.
Sum: Total sum value for a numeric attribute.
Weighted Average: Calculates the average weighted value for an attribute by another balance. Examples of attributes for which you may want to calculate the weighted average are interest rate fields: interest rate, cap rate, floor rate, and interest rate margin for floating rates. Other possibilities include loan-to-value, and so forth
Default: Applies a specified default value.

Date Action

The following options are possible when aggregating records for DATE type columns:

Max: Maximum value of a given attribute for all the instruments that are part of the pool.
Min: Minimum value of a given attribute for all the instruments that are part of the pool.
Median: Median Date value for all date values in the pool.
Default: Applies a specified default value.

Integer Action

The following options are possible when aggregating records for INTEGER type columns (example: payment frequency, reprice frequency, original term etc.):

Average: Calculates the average value of an attribute for all instruments in the pool.
Max: Maximum value of a given attribute for all the instruments that are part of the pool.
Min: Minimum value of a given attribute for all the instruments that are part of the pool.
Weighted Average: Calculates the average weighted value for an attribute by another balance.
Default: Applies a specified default value.

Text / Alphanumeric Action

The following options are possible when aggregating records for STRING, CODE type columns:

Max: Maximum value of a given attribute for all the instruments that are part of the pool.
Min: Minimum value of a given attribute for all the instruments that are part of the pool.
Default: Applies a specified default value.

Stratification Tips

Consider this criteria when evaluating how you want to stratify data:

When identifying attributes that you want to include as part of the instrument pools, keep in mind the primary use of the stratified data you are creating. For example, the ALM engine uses only the current balance amount fields for its calculations. As far as the ALM engine is concerned, designing a stratification rule that populates other balance or amount fields is optional.
You need to identify the stratification criteria for the attributes. This step is simplified by the fact that any attributes that you identify as needing to maintain discrete values for, or that are going to be dropped or defaulted by definition, cannot be used as grouping criteria for pools.
Maximize efficient processing by designing rules to summarize the instruments into as few pools as possible, while at the same time only grouping instruments that generate the same or similar cash flows. In other words, all instruments that are grouped into a pool should share common and pertinent financial characteristics.

Note:

Unique Constraint on all Aggregated tables is the same as other instrument tables with ID Number and Identity Code. In order to preserve uniqueness, DEFAULT value should not be used for ID_NUMBER and IDENTITY_CODE mappings. MIN/MAX should be used for these fields accordingly

Also See: