1. User Guide
  2. Business Rules Administration
  3. Application Specific Rules
  4. Allocation Specification
  5. Allocation Examples
  6. Examples of Dynamic Allocations
  7. Transaction Summary Tables

5.3.1.3.9.5 Transaction Summary Tables

Each row within an Instrument Table describes a unique Customer Account or position at a point in time. Instrument rows are “wide” or “horizontal”, that is, they contain potentially hundreds of columns containing Attributes or Measures. By contrast, Transaction Summary Tables (each Instrument Table has a corresponding Transaction Summary Table) also describe unique Customer accounts or positions at a point in time, but they include one or more (meaningful) dimensions in their primary keys that are not populated in the corresponding Instrument Table. In this sense, Instrument Tables and Transaction Summary Tables have a parent-child relationship; each row in an Instrument Table may have one or more child rows in its corresponding Transaction Summary Table; parent and child records share the same business date and “account identifier” (ID-NUMBER), but the “child” records vary in the “differentiating” dimension or dimensions. Each child row in a Transaction Summary Table contains only two fact columns: Volume & Cost (you may, however, customize your Transaction Summary Tables). Unlike Instrument Tables, Transaction Summary Tables are “tall” or “vertical”.

Transaction Summary Tables as vertical expressions of Instrument Tables. Each numeric measure within an Instrument row could be expressed as a single row within a Transaction Summary Table. Used in this fashion, define a Transaction Summary Table to have the same primary key as its parent Instrument Table with the addition of one additional Key Processing Dimension called “Measure Name”. In this case, each member in the Measure Name dimension would correspond to a column in the parent instrument table. Note that Key Processing Dimensions are present in all the Business Fact Tables (Instrument Tables, Transaction Summary Tables, and the Management Ledger). When you actively utilize a Key Processing Dimension within a Transaction Summary Table, you typically do not “actively” use that dimension at the instrument level (that is, the value in the Instrument Table would be “N/A”). Although this is not how Transaction Summary Tables are intended to be used, it may help in understanding their structure.

Another way of conceptualizing Transaction Summary Tables is as follows. At the Instrument level, the value of General Ledger account for a given row is meant to express the principal balance General Ledger account for that row. When you aggregate all instrument-level current book balances, the resulting total balance should reconcile to your General Ledger principal balance. You may, however, want to reconcile balances other than simply principal balances. You might wish to reconcile average book balances, par balances, deferred balances, interest income or expense balances, accrued interest receivable or payable balances, or fee balances. To accomplish this, you might store all your balances in Instrument records but store selected balances in child Transaction Summary Tables under their respective General Ledger accounts that will reconcile back to your General Ledger.

Table 5-15 Mortgage Instrument Record (hundreds of additional columns not depicted

Loan # As-of-Date GL Account Book Balance Par Balance Interest Income Fee Income
1 Jan 2011 Mortgages, Book Balance 100,000 99,734 713 14

Table 5-16 Associated Transaction Summary Child Records for Selected Balance

Loan # As-of-Date GL Account Balance
1 Jan 2011 Mortgages, Book Balance 100,000
1 Jan 2011 Mortgages, Par Balance 99,734
1 Jan 2011 Mortgages, Interest Income 713
1 Jan 2011 Mortgages, Fee Income 14

These examples explain the basic structure of Transaction Summary Tables. The primary purpose of Transaction Summary Tables is to support bottom-up profitability models. For example, the “differentiator” between an Instrument Table and its child Transaction Summary Table might be a user-defined Key Processing Dimension called Transaction or Activity. If your source systems can provide account level volume statistics for different kinds of activities, you might develop unit costs for each activity to calculate account-level costs for each activity. For example, you can collect the following account level statistics (counts over time, typically over a month) from your source systems:

  • ATM Inquiries
  • ATM Withdrawals
  • ATM Deposits
  • ATM Transfers, In-Network
  • ATM Transfers, Out of Network
  • Other ATM Transactions
  • Direct Deposits (Electronic)
  • E-Banking Auto-transfers
  • E-Banking Bill Pay
  • E-Banking Transfers, In Network
  • E-Banking Transfers, Out of Network
  • Teller Inquiries
  • Teller Withdrawals
  • Teller Deposits
  • Teller Transfers
  • Checks Processed
  • Overdrafts Processed
  • Paper Statements Processed

You could store these volume and cost statistics using user-defined columns within the Liability Instrument table. The listing of such activities and costs might number in the dozens or even in the hundreds, and each activity would require its own extended Cost (typically populated in “rate times volume allocations” or directly via the ETL process). Moreover, when you have a large number of such activities, many activities might have a count of zero resulting in wasted storage in your instrument columns. Finally, if your list of activities changes over time, you would have to restructure how you use user-defined instrument table columns corresponding to activities & costs you no longer use.

Alternatively, you could store these volume & cost statistics in the Liability Transaction Summary Table utilizing a user-defined Key Processing Dimension called Activity to differentiate child records from parent records. The dimension members within the Activity dimension would correspond to your list of activities.

There are many other advantages to this Transaction Summary approach. First, since your Activity dimension would be a Key Processing Dimension, you could construct an Activity Hierarchy. The Activity Hierarchy might be useful in a reporting context, but more importantly, higher-level rollup points within your Activity Hierarchy are likely to be much more stable than individual activities (leaf members within the Activity dimension). For example, you may wish to construct an account-level profitability model for demand deposits in which you want to calculate and report on higher-level cost elements that have a channel orientation such as ATM Expense, Branch Expense, and E-Banking Expense. You might choose to store your volumes and compute your costs for each (leaf level) activity at the Transaction Summary level and then construct Instrument level columns for ATM Expense, Branch Expense, and E-Banking Expense. Using unit costs, you can construct Allocation Rules to compute your Transaction Summary level costs. Subsequently, you can use other Allocation Rules to roll up your Transaction Summary levels costs to target columns within your Instrument table that correspond to rollup points in your Activity Hierarchy.

Using this approach, you do not pay any storage penalty if many activities frequently have a zero count for any given account (you do not have wasted Instrument columns that have zero counts and zero costs, and Transaction Summary rows only exist for non-zero counts). Also, note that if you add new activities, you need only construct a new Activity member and update your Activity Hierarchy to indicate its rollup point. No further maintenance is required in either terms or your data model or your Allocation Rules.