31   Data Aggregation and Stratification

This chapter covers the following topics:

·        Overview of Data Stratification

·        Setting up Stratification Definitions

·        Executing a Stratification Rule

·        Creating New Aggregate Table

Overview of Data Stratification

Data Aggregation and Stratification enables you to summarize large volumes of financial instruments to a manageable scale for processing purposes.  The engine that does the Aggregation and Stratification Processing is the Data Stratification Rule.  This application enables you to perform mathematically intensive calculations on a relatively small number of instrument pools that are proxies for a much larger number of individual instrument records.  Using this feature is a multi-step process, which includes:

·        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

This section covers the following topics:

·        Aggregation Action

·        Stratification Action

·        Stratification Tips

Stratification Action Operations

When creating pools of instruments, you need to identify the operation for each of the attributes.  You have the following choices in terms of how to populate each attribute in the instrument pool from the Instrument Data:

·        Discrete:  Directs the Aggregation and 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 processing purposes.  Choosing the discrete action for an attribute ensures that only instruments with matching values are grouped in a pool.  The discrete action may be the correct choice for important code fields.  Be careful when using the discrete action on the Amount, Date, or Rate Fields.  These types of fields tend to have many discrete values.  A typical value for a discrete group by is a dimension ID field or a yes/no flag.

·        Group by: Use for dimensions or other attributes that you want to use to group the detailed data.

This option allows you to select columns that you want to use to group the instrument data.  This will typically be used for dimension columns like Product, Org Unit or Currency, or any other dimensions that are needed for grouping the data.

·        Tier:  If you want to group the data by ranges of values, you can define the ranges using Stratification Tiers.

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

These are the default operations:

·        Average: Calculates the average value of an attribute for all instruments in the pool.

·        Weighted Average: Calculates the average weighted value for an attribute by the current balance.

Examples of attributes for which you may want to calculate the weighted average are interest rate fields: interest rates, cap rates, floor rates, and interest rate margins for floating rates.  Other possibilities include remaining term, original term, loan to value, and so on.

·        Min, Max (minimum, maximum): Select one of these operators to populate the pool with the minimum or maximum value of a given attribute for all the instruments that are part of the pool.

·        Median: Applies the Median Date value for all date values in the pool.

·        Sum (The sum action calculates the total sum value for a numeric attribute.

The most common attributes that you want to calculate the sum for are the balance and payment amount fields.  By calculating the total of the balance and payment amounts, the total of all pool balances should reconcile to and represent the totals of all instruments that are in the portfolio.

·        Default: Applies a specified default value.

Stratification Tips

Here are some criteria to keep in mind 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 BSP Engine uses only the current balance amount fields for its calculations.  As far as the BSP 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 of the instruments that are grouped into a pool should share common and pertinent financial characteristics.

Setting up Stratification Definitions

This section discusses how to setup Stratification Definitions

Page used to setup Stratification Configurations:

Setting up Stratification Definitions

This section discusses how to set up Stratification Definitions.

Pages Used to Setup Stratification Rules 

Stratification Configuration Page

Use the Stratification Configuration Page to define the aggregation process at any level of detail of complexity.

Navigation: Common Object Maintenance > Data Stratification> Stratification Definition > New or Edit.

This example illustrates the fields and controls on the Stratification Definition Page.  You can find definitions for the fields and controls later on in this section.

To configure Stratification, follow these steps:

1.     Establish how you want the Stratification engine to process amounts, rates, dates, and integers by assigning a Stratification Action to each.

Default input applies a default value into the action settings on the Definition Page.

2.     Select a Source and Target Instrument Table, Default Values, Aggregation Actions, and Stratification Actions to complete the definition for each instrument table that you want to aggregate.

Setting up Stratification Tier Structures

An alternative to incremental grouping is to group according to tiers.  You can use tiers on numbers, amounts, and rate fields.  The main difference between using tiers versus other aggregation actions is that you can define the specific tiers that you want to stratify the data into to match business logic, reporting requirements, or both.  Tiers also give you more control in terms of limiting the number of strata created for any given attribute to only ranges that are pertinent for processing or reporting purposes.  For example, you could define a tier rule that groups the initial balance amounts in increments of 10,000.  However, this could result in hundreds of strata if the balance amounts range anywhere from 1 to 100,000,000.  A more efficient and logical way to group balance amounts may be to set up tiers so that the first tier contains records with amounts between 1 and 50,000; the second tier contains records with amounts between 50,000 and 1,000,000, and the third tier contains records with amounts between 1,000,000 and 100,000,000.  Tiers are ideal for handling balance amounts.

Page Used to Set Up Tier Structure 

Tier Definition Page

Use the Tier Definition Page to define Stratification Criteria for creating a finite number of pools that are used by the Stratification Definition – Group by page.

Tier Definition Page

This example illustrates the fields and controls on the Tier Structures Page.  You can find definitions for the fields and controls later on this page.

Input the lower and upper boundary amounts for each tier and then add them to the definition.  The lower bound range must be greater than the prior upper bound amount.  These values cannot be equal and do not overlap.  The initial lower bound and last upper bound should be small enough and large enough to capture all possible values.

Note that under Tier Type, “Discrete” is also available to select.  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 processing purposes.  Choosing the discrete action for an attribute ensures that only instruments with matching values are grouped in a pool.  The discrete action may be the correct choice for important code fields.  Be careful when using the discrete action on the amount, date, or rate fields.  These types of fields tend to have many discrete values.  A typical value for a discrete group by is a Dimension ID Field, code field, or a yes/no flag.

Defining Stratification Rules

Use the Stratification Rules > Tabs to define the Stratification Definition

Navigation: Common Object Maintenance >  Data Stratification > Stratification Definition

Stratification Rules – Definitions Page

This example illustrates the fields and controls on the Stratification Rules – Definition Page.  

To define Stratification Definition, follow the below steps:

1.     Provide a unique name and description for the Stratification Definition Rule.

2.     Select a source instrument table.  The list of values will include all seeded and properly registered instrument tables which contain account-level data records.

3.     Select a target aggregate instrument table.  This is the table that will be populated with the results of the Stratification.

4.     Specify whether or not to include a catch all bucket in the process.  This will ensure that all records from the source table are accounted for in the target table.

5.     Select Apply to save your inputs and navigate to the next tab.

Stratification Rules – Defaults Page

This example illustrates the fields and controls on the Stratification Rules – Defaults Page.  

To define Stratification Defaults, follow these steps:

6.     Select the Amount default. Possible selections are MAX, MIN, SUM, AVG. Recommended selection is SUM.

7.     Select the Date default.  Possible selections are MAX, MIN, MEDIAN.  Recommended selection is MEDIAN.

8.     Select the AlphaNumeric default. Possible selections are MAX, MIN.  Recommended selection is MAX.

9.     Select the Rate default.  Possible selections are MAX, MIN, SUM, AVG, and Weighted Average.  Recommended selection is Weighted Average.  If Weighted Average is selected, then additionally select the balance column to use for weighting.

10.  Select the Integer default.  Possible selections are MAX, MIN.  Recommended selection is MAX.

11.  Select Apply to save your inputs and navigate to the next tab.

Stratification Rules – Aggregation Page

This example illustrates the fields and controls on the Stratification Rules – Aggregation Page.  

To define Stratification Aggregation details, follow these steps:

12.  Select the specific row you want to define from the bottom half of the page.

13.  You have the option to use the default values previously input on the Defaults tab or you can input a different Aggregation Action at the top of the page.

14.  Use the pagination selections at the bottom of the page to address/review all columns or alternatively, type the column name or a portion of the column name to navigate directly to a specific column.

15.  Select Apply to save your inputs and navigate to the next page.

Stratification Rules – Stratification Page

This example illustrates the fields and controls on the Stratification Rules – Stratification Page.

To define Stratification/Group by details, follow these steps:

16.  Select the Column(s) and Stratification Action that you want to use for grouping the detailed records into aggregate/pooled records. Typical columns used for grouping will be dimension columns, balance or rate columns, or any other meaningful columns that you want to be unique for each pool record.

17.  For each Stratification column, select the Stratification Action.  Options are Tier, Discrete, and Group by.   Both the Tier and Discrete options require an additional Stratification Tier definition to be selected.

18.  Select Apply to save your inputs and remain in the rule or select Save to save your inputs and close the rule.

Defining Stratification Tiers

Use the Stratification Tiers page to define the Stratification Tier Definition

Navigation: Common Object Maintenance > Data Stratification > Stratification Tier

Stratification Tiers Page

This example illustrates the fields and controls on the Stratification Tiers Page.  

To define Stratification Tiers, follow these steps:

1.     Provide a unique name and description for the Stratification Tier.

2.     Select the Tier Type, that is Tier or Discrete.

3.     Select the column Data Type that this definition will apply to.  This selection will limit the columns where this Tier rule will be available for selection in the Stratification Definition Rule.

4.     Define each tier with lower and upper bounds and Add as a defined range.  Additionally use the Update or Remove buttons to manage the individual tier definitions.

5.     Once all tier ranges have been defined, select the Save button to save the assumptions and exit the Tier Screen.

NOTE:   

The tier definitions created in this step are mapped to the individual columns in the Stratification Definition rule on the Stratification Tab.

Creating New Aggregate Table

The following aggregate tables are seeded with an Application Data Model:

If you want to create a new aggregate table then, apart from the basic instrument and leaf columns, the following mappings must be done in the Data Model:

·        Super-type

§       INSTRUMENT_AGGREGATE 

§       INSTRUMENT_AGGREGATE_BASIC 

·        Table classification

§       EPM Aggregation (701) 

This may be required when you create a custom instrument table and want to use stratification for it.

Executing a Stratification Rule

Stratification Rules can be executed from the Stratification Rule Summary Page:

To execute a Stratification Rule, follow these steps:

1.     Navigate to the Stratification Rule Summary Page.

2.     Select the Stratification Rule you want to process.

3.     Select the Run icon.

4.     Select the as of date for the instrument data you want to aggregate.

5.     Select OK to begin the process.

You can also execute the stratification rule either from SQL*Plus OR from within a PL/SQL block OR from ICC Batch UI within the OFSAAI Framework.  

Using SQL*Plus

To run the procedure from SQL*Plus, login to SQL*Plus as the Schema Owner. The function requires four parameters. Following is the syntax for calling the procedure:

declare

result number;

begin

 ---- Call the function

 result := fn_insert_into_data_strat_tab(batch_id => :batch_id,

  mis_date => :mis_date,

  stratification_id => :stratification_id,

  user_name => :user_name);

end;

·           batch_id : any string to identify the executed batch

·            mis_date: in the format YYYYMMDD 

·            stratification_id: numeric System ID of Stratification Rule 

·            user_name: OFSAA User ID of the user executing the function 

Example

declare

result number;

begin

---- Call the function

 result := fn_insert_into_data_strat_tab(batch_id => 'abc',

  mis_date => '20180601',

  stratification_id => 123,

  user_name => AUTOUSER);

end;

Using ICC Batch Framework

To execute the procedure from the OFSAAI ICC Framework, create a new Batch with the task as TRANSFORM DATA and specify the following parameters for the task:

·        Datastore Type: Select appropriate datastore from the list

·        Datastore Name: Select appropriate name from the list

·        IP address: Select the IP address from the list

·        Rule Name: Data_Stratification

·        Parameter List: stratification_id, user_name

After the Stratification Rule execution is complete, you can compare balances from the source table and target table to ensure all data has been accounted for, that is the sum of the current balance from the source table should equal the sum of the current balance in the Target Table.

After the data has been populated and verified in the aggregation tables, it is ready for processing by the BSP and/or FTP Applications.  You can select either regular instrument tables or aggregate instrument tables in the BSP and TP Processes under Source Selection.  You should select one or the other. Selecting both in the same process will lead to double counting of the balances.