TEMENOS T24

Data Warehouse
ETL and Data Model

User Guide

Information in this document is subject to change without notice.

No part of this document may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of TEMENOS Holdings NV.

Copyright 2005 TEMENOS Holdings NV. All rights reserved.

Data Warehouse

Table of Contents
Overview .................................................................................................................................................. 4
The T24 Data Model and Data Warehouse Architecture ................................................................. 4
Philosophy and Standards for the Data Warehouse Data Model ...................................................... 36
Centralising all banking arrangements ........................................................................................... 36
Managing Balances ........................................................................................................................ 36
Categorisation of Tables in Data warehouse ................................................................................. 37
Maintenance of Reference Data .................................................................................................... 42
Standardisation of Segmentation across tables ............................................................................. 43
Difference between segments & hierachies within static attributes ............................................... 43
Updates & Calculations within the Data Warehouse ..................................................................... 43
Profitability ...................................................................................................................................... 44
Programming Requirements .......................................................................................................... 44
MIS reporting ..................................................................................................................................... 45
Profitability Analysis ....................................................................................................................... 45
Setup ..................................................................................................................................................... 47
jBASE Release .................................................................................................................................. 47
T24 ETL Architecture ......................................................................................................................... 47
Online Services ..................................................................................................................................... 47
Automated Extraction of Table Data .................................................................................................. 47
Selecting tables to populate the data warehouse .......................................................................... 47
Rules Routine ................................................................................................................................. 48
XML Schema generation in T24 ........................................................................................................ 51
T24 Transformation Tables ................................................................................................................ 51
EB.DDL.SCHEMA .......................................................................................................................... 51
1..1 EB.TRANSFORM ................................................................................................................ 53
T24 DW Loading Table ...................................................................................................................... 53
DW.CONNECT.PARAM ................................................................................................................. 53
T24-DW mapping mechanism ........................................................................................................... 54
Creation of DW schema ................................................................................................................. 55
Mapping using ALTOVA ................................................................................................................. 56
Virtual fields in Standard Selection ................................................................................................ 65
External Database Population ........................................................................................................... 66
Running the ETL process .................................................................................................................. 68
TSA.SERVICE record .................................................................................................................... 68
TSA.WORKLOAD.PROFILE .......................................................................................................... 68
DW API records.............................................................................................................................. 68

TEMENOS T24 User Guide Page 2 of 88

Data Warehouse

Close of Business Services. .................................................................................................................. 69

DW Bulk loading ................................................................................................................................ 69
DW.BULK.EXTRACT ..................................................................................................................... 69
TSA.SERVICE record .................................................................................................................... 69
TSA.WORKLOAD.PROFILE .......................................................................................................... 70
Overview of Input and Processing ................................................................................................. 70
Error Messages ..................................................................................................................................... 74
ETL error logging facility .................................................................................................................... 74
EB.LOGGING.PARAMETER ......................................................................................................... 74
ENQUIRY DW.RECONCILE.PARAM ............................................................................................ 76
Interfaces ............................................................................................................................................... 78
Altova Mapforce Mapping Guide ....................................................................................................... 78
Using ERWIN to build the Data Model............................................................................................... 78
Building a table ............................................................................................................................... 78
Identifying Relationship in Data Model ........................................................................................... 82
Non-Identifying Relationship in Data Model ................................................................................... 83
Generation of the DDL ................................................................................................................... 85
Creating the Data model in Database (Oracle) .............................................................................. 86
Glossary ............................................................................................................................................. 88

TEMENOS T24 User Guide Page 3 of 88

Data Warehouse

Overview
The Data Warehouse functionality in T24 enables the automated online extraction of data from T24
through online services. This data is mapped to fit an analysis data model, and is then loaded into an
external Data Warehouse.
Temenos Data Warehouse provides a defined Data Model, as well as the mapping required to convert
data from T24 into that Data Model. In addition to this, Temenos Data Warehouse provides a number
of enquiries and reports based on this data model, to fit a variety of banking reporting requirements.
The T24 Data Warehouse ETL (Extract, Transform, Load) functionality enables the automated
conversion of the OLTP Data in T24 into relational tables. These tables are stored on an external
separate RDBMS system. A separate driver is provided by Temenos to enable the automatic flow of
mapped data from T24 to the RDBMS.

T24 Automated Extraction & T24 Data

Mapping
Database Warehouse

However, it is also understood that a client may already have an existing Data Warehouse and
associated Data Model. Therefore, the mapping and enquiries provided by T24 are not mandatory but
are highly flexible; the bank may choose to specify their own mapping from T24 to fit their specific
requirements.
This document details the installation procedure for generating the T24 Data Warehouse, including the
mapping functionality for converting T24 Data into the T24 Data Model and the Loading functionality to
populate the external database.

The T24 Data Model and Data Warehouse Architecture

After completing the installation steps, the T24 data model should be automatically populated when
relevant applications in T24 are updated. Reports and enquiries can then be written based on these
Analysis tables on the RDBMS if the tables are being stored there.
The Data Model will gradually evolve over time to reflect refinements and requests for enhancements
from the user community. The extraction functionality will be similarly be gradually enhanced to
ensure it meets the needs of the data model. The architecture of the Data warehouse with the data
model is given below.

TEMENOS T24 User Guide Page 4 of 88

Data Warehouse

The below section explains the methodology behind the development of the T24 data model, to assist
in the development of reports and enquiries. :
The main entites of the data model are given below:

• Party is an entity (individual or organization) which is related to the Bank (Party itself) in
someway or the other. Examples: Primary and secondary account holders (customers), loan
brokers, employees, beneficiaries, household members, counterparties to an agreement,
obligors, claimants to collateral, securitization investors, guarantors

TEMENOS T24 User Guide Page 5 of 88

Data Warehouse

• Arrangement is contract between two parties like customer and Bank for product or service
like collateral. Examples: Checking Account, Savings Account, Time Deposit, Retirement
Account, Collateral agreement, Limit agreement, Loan, Credit Card, Collateral Agreement,
Internal Investment Holding, Letter of Credit, Brokerage Account w/ Portfolio, Commercial
Account, Services Provider contract
• Product is any marketable offering or service including terms and conditions by the bank
which can be used by the customers. Examples of product with product characteristics: Loan,
Mortgage, Certificate of Deposit, Line of Credit, Platinum credit card, Mutual Fund, Small
Business Loan, Investment Instrument (e.g. Temenos common stock)
• Event - Any incident that happened which may involve contact with the party. Examples:
Deposit, Withdrawal, Credit/debit card charge, Complaint/Inquiry, Address Change, Balance
Inquiry, Internet page view, Brokerage buy/sell, Installment Payment, Payment Request
(claim)
• Finance – The internal accounting of the Bank’s business is described along with the financial
reporting. GL_entry and GL_Reporting _balance Examples: Management reporting,
Product profitability, Business unit profit, Arrangement Profit
• Protection Asset – It is an asset of the party and will include guarantee, credit derivatives, etc.
which may be used for credit mitigation. Examples : Automobile, real estate, jewelry,
machinery, boats, inventory, financial instruments, securities, etc

The data model has the following characteristics

a) It has standard business nomenclature
b) All contracts and accounts are called arrangements in data warehouse
c) Multi values are mapped in multiple tables
d) The tables in Data warehouse have been segregated as fact and dimension tables
e) Star schema is generated as views and fed to reporting tool
The data model is extensible and table and columns can be added as per the user definition. Future
releases will contain data from other domains like Risk, CRM, etc.

Table
Name Comment
AGMT Highest level table for each contract. This holds information
relating to all sustained contracts in the database. The table holds
information which would not normally be expected to change
through the life of the contract - however, if the contract does
change, a new record would be generated, with the new details and
a new ARRANGEMENTCODE - while the ARRANGEMENTID
would remain the same.
AGMT_AGMT_RELATIONSHIP This entity identifies relationships between arrangements and
identifies the type of relationship. An example would be the
arrangements are related for collateral purpose or one arrangement
replaced another arrangement due to a refinance. There is one
instance for each arrangement-arrangement relationship.
AGMT_BAL_POST_VALUE_BOOKING This is an off-shoot of the Agmt_Balance table for the post value
dated booking amount. The primary key of this table is the
concatenation of the '@ID + POST.VALUE.DATE+ BOOKING
DATE.
AGMT_BALANCE This file holds the various balances related to the arrangement.
Examples of balances - outstanding balance, provision balance,
interest payable/recievable balance, overdue balance/in grace
balance etc. Outstanding balance is likely to be the principal one
used. Each balance is distinguished by Balance Type, which itself
holds information about the balance itself.
There will also be a rate at which the balance is gaining

TEMENOS T24 User Guide Page 6 of 88

Data Warehouse

Table
Name Comment
interest/depreciating in value.
There may also be a repricing/transfer price rate applicable to this
balance. There is likely to be a counterpart BALANCE record to
the ARRANGEMENT_BALANCE for interest details.
This will hold all data relating to the financial balances of contracts.
For example, there will be information holding the amount and
currency of balances, along with information detailing the rate that
the balances are accruing interest (if applicable), or possibly
depreciating in value, and the future date that these rates may
change, the date that this balance is closed (if known), as well as
reference to the parent Arrangement contract.
Every arrangement has at least one balance - generally for the
outstanding amount. However, there may be other balances such as
interest payable/receivable, payments past due/in grace/non accrual
etc.
A maximum of one record per balance type per contract per day is
sustained in the system. This has to be stored in order to enable
historic comparisons and reporting of amounts. Therefore,
whenever there is a new balance on a new day, a new record should
be generated to reflect this.
It is debateable as to whether there should be an 'enddate' for the
balance, if one balance (but not the contract overall) is due to be
'closed' at a particular time.

AGMT_BALANCE_POST_VALUE This table has the post value dated balance of the arrangement
thereby taking care of the back valued transactions. The primary
key of this table is the concatenation of '@ID +
POST.VALUE.DATE
AGMT_BALANCE_VALUE This table is an off shoot of the Agmt_balance table having the
value dated balances of the arrangement. The primary key of this
table is concatenation of @ID + VALUE.DATE
AGMT_PARTY This associative entity defines the relationships that this
arrangement has with parties. For example, relationships include
arrangement holder, beneficiary, trustee, the car dealer that
originated the account, the business or individual that funded the
deposit money, or another customer that referred this customer to
the bank.
AGMT_PARTY_ROLE It stores the description of various arrangement party role types.
AGMT_PROFIT This entity tracks the changes to an account's profitability over
time. This entity has the account profitability that is calculated
outside of this system. There is one instance for each account, date
and profit type combination.
This entity can be derived from other entities in the model that have
costs and revenue associated with it.
AGMT_RELATIONSHIP This minor entity describes the type of relationship between two
arrangements such as collateral, transfer, etc.
BRANCH This stores the information of the branch from where the
arrangement originated. It is a type of business unit
COUNTRY It stores the details about the country along with the region.
COUNTRY_CURRENCY This stores the relationship of the country with the currency
CURRENCY It stores details about the currency
CURRENCY_CURRENCY_MARKET This stores the relation between the currency market and the
particular currency.
CURRENCY_MARKET This stores the description of the various currency markets for a
currency.
CURRENCY_RATE It stores the currency rates along with the history
EMPLOYEE_DEPT It holds the department to which an employee belongs.
EMPLOYEE_PARTY It stores the details about the employee of the organisation
EMPLOYEE_RELATION It stores the emplyee relationship with the employer
EXTERNAL_IDENTIFICATION It stores the details of external identification of a party like
passport, driving license, SSN, etc.
GENDER It stores the gender description
GL_ENTRY It stores all the GL entries over time
GL_REPORTING_BALANCE The entity that indicates the actual account balance amounts for
defined GL accounts.
GLOBAL_PARTY This stores the unique identifier of Global party and the description

TEMENOS T24 User Guide Page 7 of 88

Data Warehouse

Table
Name Comment
INDUSTRY It describes the industry details of the party
INTEREST_BASIS This stores the various basis of interest calculation like A/a , A/360.
etc along with the description
INTEREST_BENCHMARK It stores the benchmark rate for a particular benchmark like LIBOR
over time.
INTEREST_BENCHMARK_DES This entity represents an interest index . An example of indices are
bank prime rate, 3-month bank CD, LIBOR 6-month etc. Interest
rates charged to customers are based on these indexes.
JOB_TITLE It stores the job title of the individual.
LIMIT This table stores all the limit details of an arrangement.
LIMIT_BRANCH This table stores the limit applicable to a particular branch
LIMIT_COLLATERAL This stores the mapping of collateral with the limit.
LIMIT_COLLATERAL_PERIOD This table stores the limit collateral relationship of LIMIT with
the COLLATERAL along with the period of validity and amount of
the Collateral.
LIMIT_COLLATERAL_RIGHT It stores the relationship between the Limit and Collateral right.
LIMIT_COMMODITY It stores the limit applicable to a particular commodity
LIMIT_COUNTRY This stores the the limit for each country in the overall limit
LIMIT_CURRENCY It stores the limit applicable to a particular currency.
LIMIT_GRADE This stores the the limit for each country in the overall limit
LIMIT_MATURITY It stores the limit applicable for a period.
LIMIT_PRODUCT This stores the relationship of the Limit with various products.
MIS_DATE It stores the calendar time date, week, month, quarter, half year and
year
OCCUPATION It stores the occupation of the individual.
PARTY A party is any individual or organization that is of interest to the
Bank.
PARTY_ADDRESS It stores the mail address of the customer or party
PARTY_EMPLOYMENT This table stores the employment details of the customer.
PARTY_LIMIT It stores the total limit for the Party
PARTY_NAME It stores the name of the Party over time.
PARTY_PARTY_RELATION It stores the relationship between 2 parties with the role code
PARTY_RATING It stores the rating of the Party over time.
PARTY_RELATION Types of relationships that parties can have to each other.
Examples of relationships :

For party :
Sibling
Cohabit
Parent

For companies :
Head office
Subsidiary
Supplier
Partner

General :
Trustee
POA

PARTY_RESIDENCE It stores the residence details of the party

PARTY_SEGMENT The Segmentation Concept is central to the Data Warehouse overall
design. The Party Segment consists of a logical grouping of
parties, which can be used as a standardised grouping elsewhere.
Examples of segments could be :
Young Married High Earner
Young Unmarried High Earner etc etc
Or
Medium Sized Services Industry Privately Owned Company
Medium Sized Information Technology (Software) Public Listed
Company
For this reason, it is not possible to detail the attributes at this point
- as they may be set by the client. However, the attributes for this
table are likely to be dimensions upon which the system is likely to
be reported.

TEMENOS T24 User Guide Page 8 of 88

Data Warehouse

Table
Name Comment
The rules for setting segmentation will be stored as part of the
extraction tool, so when a party is entered its segmentation is
identified, and this is attached to the DW table.

PARTY_STATUS This stores the status of the Party whether a defaulter , bankrupt,
etc
PRODUCT It stores the details about the Product offered by the bank
PROT_ASSET_RIGHT It stores the details about the Product offered by the bank
PROT_ASSET_RIGHT_BRANCH This stores the Collateral right for a branch
PROT_ASSET_RIGHT_LIMIT This stores the collateral right related to a limit.
PROT_ASSET_RIGHT_PARTY This stores the Collateral right for a customer
PROTECTION_ASSET This entity identifies all the known assets of Parties. These also
include equipment or vehicles being leased by the financial
institution. There is one instance for each asset. An asset could be
any item that is of value to the Party The term 'asset' is from the
customer's perspective.
PROTECTION_ASSET_GROUP This groups various collateral types into broader groups.
PROTECTION_ASSET_TYPE This stores the collateral type like securioties, physical, financial,
etc.
RATING It stores the description of a Rating of agency like S & Pwhich is a
type of Party for the Bank
RATING_AGENCY This stores the details of the particular rating agency.
REGION This stores the region details for the country
REGION_COUNTRY It stores the relation between a Country and a particular region like
Asia, Europe, EMEA, etc.
SECTOR It stores the sector description of the customer
TIME_PERIOD_TYPE It stores the description of time period type like daily, weekly,
monthly, etc.

Column(s) of "AGMT" Table

Name Data type Comment Is PK
Agmt_Pk CHAR(80) Identifier of the Arrangement Yes
which is contract between two
parties like customer and Bank
for product or service Primary
Key for record
A new record is generated every
time a relevant detail
(staff/branch/product segment
etc) is changed on this contract.
Date field may be included as
part of primary ID.
The start date/end date/change
date is also updated at this point.

Agmt_Code CHAR(10) Identifier for the record. Would No

appear on all party
communications.

This item does not change from

the date the contract is entered.

Arrangement_Start_Date DATE Starting date of arrangement - No

date contract became current
with bank. Default date would
be value date.

As the DW information is for

data analysis, rather than
processing, under current scope
it is not necessary to hold
assorted dates (exposure date,
value date, booking date,
available date).

Arrangement_Maturity_Date DATE Maturity date of the No

TEMENOS T24 User Guide Page 9 of 88

Data Warehouse

Column(s) of "AGMT" Table

Name Data type Comment Is PK
arrangement
Fwd_Move_Date DATE Scheduled date of payment. No
LMM.ACCOUNT.BALANCES
/ SCHEDULE tables for each
app. As applicable.
Limit_Pk CHAR(10) Refers to another arrangement. No
Therefore can only be entered
after limit entered.
Agmt_Rate DECIMAL(5,8) Interest rate of the arrangement No
Agmt_Spread DECIMAL(9,2) Spread for the arrangement over No
and above the Benchmark rate
like LIBOR
Portfolio_Pk CHAR(18) Portfolio into which No
arrangement belongs.

Parties may choose to group sets

of arrangements into portfolio,
so for example as savings
accounts, money market
deposits & long term bonds may
be grouped into a 'Retirement
Fund' while current account &
some penny stock shares would
be held in a 'trading fund'. This
field indicates which portfolio
an arrangement has been placed
into.

Limit_Type CHAR(18) Variety of limit (revolving/non- No

revolving/short term/long term
etc) being used.
Interest_Type VARCHAR2(20) Type of interest - Fixed Simple No
or Floating simple or Floating
Simple or Floating compound
Record_Start_Date DATE Date at which arrangement was No
changed. Necessary for
investigating movements of
amounts (if one report has
different data to another, may be
due to change of arrangement
information).
Time_Period_Cd CHAR(18) A code for a time period such as No
once, daily, weekly or monthly
specificall for the interest
Source_Application_Id VARCHAR2(10) Application identifier in the No
source T24 like AC, FX, etc
Source_Consol_Key VARCHAR2(20) Consolidation key in the source No
T24 for an arrangement which
may vary with time
Agmt_Value_Date DATE Value date of the arrangement No
Agmt_Tenor INTEGER Tenor of the arrangement No
Agmt_Repay_Amt DECIMAL(15,2) Repayment amount of the No
arrangement for a specific
period
Agmt_Interest_Basis VARCHAR2(20) Interest basis for calculation of No
interest accrual for an
arrangement
Agmt_Residual_Principal DECIMAL(15,2) Residual Principal of the No
arrangement after the
repayments
Agmt_Principal DECIMAL(15,2) Total principal amount of the No
arrangement
Agmt_Penalty_Rate DECIMAL(12,2) Penalty rate for the arrangement No
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Branch_Pk CHAR(40) unique identifier of the branch No

TEMENOS T24 User Guide Page 10 of 88

Data Warehouse

Column(s) of "AGMT" Table

Name Data type Comment Is PK
Interest_Benchmark_Des_Pk CHAR(40) Description of the Interest No
benchmark code like LIBOR
Currency_Pk CHAR(18) Unique identifier of the currency No
Employee_Party_Pk CHAR(40) Unique Identifier of the No
employee
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.

Column(s) of "AGMT_AGMT_RELATIONSHIP" Table

Name Data type Comment Is PK
Agmt_Agmt_Relationship_Pk CHAR(50) Unique identifier of the Yes
Arrangement which is a
concatenation of Agmt_pk,
Related_agmt_pk and
Agmt_relationship_pk
Record_Start_Date DATE Yes
Rel_Start_Date_Pk DATE Start date of the arragement No
relationship
Agmt_Relationship_Pk CHAR(50) Unique identifier of the No
arrangement relationship, like
collateral, limit, etc.
Related_Agmt_Pk CHAR(40) Unique identifier of the related No
arrangement
Agmt_Pk CHAR(80) Identifier of the Arrangement No
which is contract between two
parties like customer and Bank
for product or service Primary
Key for record
A new record is generated every
time a relevant detail
(staff/branch/product segment
etc) is changed on this contract.
Date field may be included as
part of primary ID.
The start date/end date/change
date is also updated at this point.

Rel_End_Date DATE End date of the arragement No

relationship
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Record_End_Date DATE No
Record_Status CHAR(18) No

Column(s) of "AGMT_BAL_BOOK_DATE" Table

Name Data type Comment Is PK
Agmt_Balance_Book_Date_Pk CHAR(40) This is a an offshoot of Yes
Agmt_Balance table storing the
balance as on booking date of
the arrangement
Agmt_Balance_Pk CHAR(40) Unique identifier of the No
Arrangement Balance
Booking_Date DATE No
Book_Dr_Movement DECIMAL(25,5) No
Book_Cr_Movement DECIMAL(25,5) No
Book_Balance DECIMAL(25,5) No
Source_System VARCHAR2(20) No
Record_Status CHAR(18) Status of the record - live or No
history

Column(s) of "AGMT_BAL_POST_VALUE_BOOKING" Table

Name Data type Comment Is PK
Agmt_Bal_Post_Value_Booking CHAR(100) This is an off-shoot of the Yes
_Pk Agmt_Balance table . Unique

TEMENOS T24 User Guide Page 11 of 88

Data Warehouse

Column(s) of "AGMT_BAL_POST_VALUE_BOOKING" Table

Name Data type Comment Is PK
identifier of the table and it is a
concatenation of
Agmt_Balance_Post_Value_Pk
and Post_Booking_Date
Agmt_Balance_Post_Value_Pk CHAR(50) This is a concatenation of No
Agmt_balance_pk and
Post_value_date of
Agmt_balance_post_value table
Post_Booking_Date DATE Post booking date No
Post_Dr_Movement DECIMAL(25,5) Post value date debit movement No
Post_Cr_Movement DECIMAL(25,5) Post value date credit movement No
Source_System VARCHAR2(20) Identifier of the source system No
Mis_Date_Pk DATE Extraction date No
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.

Column(s) of "AGMT_BALANCE" Table

Name Data type Comment Is PK
Agmt_Balance_Pk CHAR(40) Unique identifier of the Yes
Arrangement Balance
Mis_Date_Pk DATE Extraction date No
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.
Agmt_Pk CHAR(80) Identifier of the Arrangement No
which is contract between two
parties like customer and Bank
for product or service Primary
Key for record
A new record is generated every
time a relevant detail
(staff/branch/product segment
etc) is changed on this contract.
Date field may be included as
part of primary ID.
The start date/end date/change
date is also updated at this point.

Agmt_Bal_Rate CHAR(10) Interest Rate being generated for No

this balance. Necessary for
calculation of average
party/product rates. Only
applicable to balances which are
accruing rates.
Notice_Time CHAR(10) For many, this will be blank (no No
notice required/applicable).
Only populated for Accounts,
MM contracts where set no.
days required before outstanding
can be reduced.If contract
requires x days notice on
withdrawl/change to this
balance, this information can be
held here. Necessary for
calculation
Bal_Lcy DEC(25,7) Local Currency Balance. Note, No
this can be coordinated with the
CURRENCY file to show the
value at date and also the current
value, depending on
requirements.
Limit_Pk CHAR(40) Limit Arrangement balance used No
to support this arrangement. If
the arrangement is an item of
collateral, the reverse occurs
where the item of collateral
supports the limit.

TEMENOS T24 User Guide Page 12 of 88

Data Warehouse

Column(s) of "AGMT_BALANCE" Table

Name Data type Comment Is PK
Last_Billed_Amt DECIMAL(25,7) The last billed amount No
Interest_Type VARCHAR2(20) Type of interest - fixed or No
floating
Time_Period_Cd CHAR(18) A code for a time period such as No
once, daily, weekly or monthly
for interest calcuation
Open_Balance_Amt DECIMAL(25,7) Opening balance amount No
Dr_Movement DECIMAL(25,7) Debit movement of the balance No
amount during the period
Post_Open_Balance DECIMAL(25,7) Open balance post back valued No
entry
Post_Close_Balance DECIMAL(25,7) Closing balance post back No
valued entry
Cr_Movement DECIMAL(25,7) Credit movement of the balance No
amount during the period
Average_Balance DECIMAL(25,7) Average balance for the month No
which is taken as 30 days
irrespective of the days in the
month
Average_Balance_Mth DECIMAL(25,7) Average balance for the month No
which takes into account all the
days during the month
Avg_Debit_Balance_Month DECIMAL(25,7) Average debit balance for the No
month which takes into account
all the days where balance is in
debit or zero or credit during the
month
Avg_Credit_Bal_Month DECIMAL(25,7) Average credit balance for the No
month which takes into account
all he days where balance is in
debit or zero or credit during the
month
Avg_Debit_Bal DECIMAL(25,7) Average debit balance for the No
month which takes into account
only the days where balance is
in debit and does not consider
the days when balance is zero or
credit
Avg_Credit_Bal DECIMAL(25,7) Average credit balance for the No
month which takes into account
only the days where balance is
in credit and does not consider
the days when balance is zero or
debit
Avg_Bal_Ytd DECIMAL(25,7) It will be calculated as sum of No
all the balances [irrespective of
debit or credit] from beginning
of the financial year till date
divided by the number of days
till date from the beginning of
the financial year
Avg_Credit_Bal_Ytd DECIMAL(25,7) It will be calculated as sum of No
all the credit balances from
beginning of the financial year
till date divided by the number
of days till date from the
beginning of the financial year
Avg_Debit_Bal_Ytd DEC(25,7) it will be calculated as sum of No
all the debit balances from
beginning of the financial year
till date divided by the number
of days till date from the
beginning of the financial year.
Post_Value_Date DATE Value date of the back value No
entry
Post_Av_Bal DECIMAL(25,7) Average balance for the month No
which is taken as 30 days

TEMENOS T24 User Guide Page 13 of 88

Data Warehouse

Column(s) of "AGMT_BALANCE" Table

Name Data type Comment Is PK
irrespective of the days in the
month post back value entry
Post_Av_Bal_Cal DECIMAL(25,7) Average balance for the month No
based on the calendar days in
the month post back value entry
Post_Av_Debit_Bal DECIMAL(25,7) Average debit balance for the No
month which takes into account
only the days where balance is
in debit and does not consider
the days when balance is zero or
credit post new entry
Post_Av_Credit_Bal DEC(25,7) Average credit balance for the No
month which takes into account
only the days where balance is
in credit and does not consider
the days when balance is zero or
debit post new entry
Post_Av_Debit_Bal_Month DECIMAL(25,7) Average debit balance for the No
month which takes into account
all he days where balance is in
debit or zero or credit during the
month post new entry
Post_Av_Credit_Bal_Month DECIMAL(25,7) Average credit balance for the No
month which takes into account
all he days where balance is in
debit or zero or credit during the
month
Refin_Rate_Dr DECIMAL(12,6) Refinance rate or transfer price No
for the debit balance
Refin_Rate_Cr DECIMAL(12,6) Refinance rate or transfer price No
for the credit balance
Post_Avg_Bal_Ytd DECIMAL(25,7) It will be calculated as sum of No
all the balances [irrespective of
debit or credit] from beginning
of the financial year till date
divided by the number of days
till date from the beginning of
the financial year after the new
entry
Post_Avg_Debit_Bal_Ytd DECIMAL(25,7) it will be calculated as sum of No
all the debit balances from
beginning of the financial year
till date divided by the number
of days till date from the
beginning of the financial year
after new entry
Post_Avg_Credit_Bal_Ytd DECIMAL(25,7) It will be calculated as sum of No
all the credit balances from
beginning of the financial year
till date divided by the number
of days till date from the
beginning of the financial year
after new entry
System_Id CHAR(20) Identifier of the source No
application
Consol_Key CHAR(80) Consolidated key of the source No
application
Funding_Unit_Code VARCHAR2(20) Code of the funding unit for the No
deal
Credit_Days NUMBER Days the balance was in credit No
during a month
Debit_Days NUMBER Days the balance was in debit No
during a month
Zero_Days NUMBER Days the balance was zero No
during a month
Post_Credit_Days NUMBER Credit balance days of the No
balance after adjustment

TEMENOS T24 User Guide Page 14 of 88

Data Warehouse

Column(s) of "AGMT_BALANCE" Table

Name Data type Comment Is PK
Post_Debit_Days NUMBER Debit balance days of the No
balance after adjustment
Post_Zero_Days NUMBER Zero balance days of the balance No
after adjustment
Interest_Basis_Pk CHAR(40) Unique identifier of the interest No
basis record
Interest_Benchmark_Des_Pk CHAR(40) Description of the Interest No
benchmark code like LIBOR
Source_System VARCHAR2(20) Source of the extract No
Closing_Balance_Amt DECIMAL(25,7) Closing balance No
Value_Date DATE Value date of the balance No
Employee_Party_Pk CHAR(40) Unique Identifier of the No
employee
Party_Pk CHAR(40) Unique identifier of the party No
Product_Pk CHAR(40) Unique identifier of the product No
Currency_Pk CHAR(18) Unique identifier of the currency No
Branch_Pk CHAR(40) unique identifier of the branch No

Column(s) of "AGMT_BALANCE_POST_VALUE" Table

Name Data type Comment Is PK
Agmt_Balance_Post_Value_Pk CHAR(50) Unique identifier of this table. Yes
This is a concatenation of
Agmt_balance_pk and
Post_value_date of
Agmt_balance_post_value table
Post_Value_Date DATE The date of back valued No
transaction
Post_Value_Date_Balance DECIMAL(25,5) The balance after the back No
valued transaction
Mis_Date_Pk CHAR(18) Extraction date No
Source_System VARCHAR2(20) Source system identifier No
Record_Status CHAR(18) Status of the record - live , No
history, etc.

Column(s) of "AGMT_BALANCE_VALUE" Table

Name Data type Comment Is PK
Agmt_Balance_Value_Pk CHAR(50) This table is an off shoot of the Yes
Agmt_balance table. Unique
identifier of the table and a
concatenation of
Agmt_balance_pk and
Value_date
Value_Date DATE The value date of the transaction No
Dr_Movement DECIMAL(25,5) Debit movement No
Cr_Movement DECIMAL(25,5) Credit movement No
Value_Date_Balance DECIMAL(25,5) The balance on value date No
Agmt_Balance_Pk CHAR(40) Unique identifier of the No
Arrangement Balance
Source_System VARCHAR2(20) Unique identifier of the source No
system
Record_Status CHAR(18) No

Column(s) of "AGMT_PARTY" Table

Name Data type Comment Is PK
Agmt_Party_Pk CHAR(90) Unique identifier of the record - Yes
Concatenation of Agmt_pk,
Party_pk, Agmt_party_role_pk
Record_Start_Date DATE Start date of the record Yes
Start_Date_Pk DATE Start date of the arrangement No
and party association
Agmt_Party_Role_Pk CHAR(10) The role that parties have with No
this arrangement such as
arrangement holder, trustee,

TEMENOS T24 User Guide Page 15 of 88

Data Warehouse

Column(s) of "AGMT_PARTY" Table

Name Data type Comment Is PK
beneficiary, insured by.
Party_Pk CHAR(40) Unique identifier of the party No
Agmt_Pk CHAR(80) Identifier of the Arrangement No
which is contract between two
parties like customer and Bank
for product or service Primary
Key for record
A new record is generated every
time a relevant detail
(staff/branch/product segment
etc) is changed on this contract.
Date field may be included as
part of primary ID.
The start date/end date/change
date is also updated at this point.

Record_End_Date DATE End date of the arrangement and No

party association
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Record_Status CHAR(18) Status of the record No

Column(s) of "AGMT_PARTY_ROLE" Table

Name Data type Comment Is PK
Agmt_Party_Role_Pk CHAR(10) The role that parties have with Yes
this arrangement such as
arrangement holder, trustee,
beneficiary, insured by.
Record_Start_Date DATE Yes
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.
Agmt_Party_Role_Des CHAR(50) Description of the arrangement No
party role
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE No

Column(s) of "AGMT_PROFIT" Table

Name Data type Comment Is PK
Agmt_Profit_Start_Date_Pk DATE Arrangement and profitabilty Yes
type association start date
Agmt_Profit_Pk CHAR(40) Unique identifier - Yes
Concatenation of Agmt_pk,
time_period_cd
Agmt_Pk CHAR(80) Identifier of the Arrangement No
which is contract between two
parties like customer and Bank
for product or service Primary
Key for record
A new record is generated every
time a relevant detail
(staff/branch/product segment
etc) is changed on this contract.
Date field may be included as
part of primary ID.
The start date/end date/change
date is also updated at this point.

Agmt_Profit_End_Date DATE Arrangement and profitabilty No

type association end date
Agmt_Profit_Amt DECIMAL(10,7) Profit amount calculated No
Time_Period_Cd CHAR(18) Time period code which can be No
daily, monthly, etc.
Mis_Date_Pk DATE Extraction date No
Record_Status CHAR(18) Status of the record - live, No

TEMENOS T24 User Guide Page 16 of 88

Data Warehouse

Column(s) of "AGMT_PROFIT" Table

Name Data type Comment Is PK
history, etc.

Column(s) of "AGMT_RELATIONSHIP" Table

Name Data type Comment Is PK
Agmt_Relationship_Pk CHAR(40) Unique identifier of the Yes
arrangement relationship, like
collateral, limit, etc.
Record_Start_Date DATE Yes
Record_Status CHAR(18) Status of the record - live, No
historical, etc
Agmt_Relationship_Des CHAR(50) Relationship description of the No
both agreements
Mis_Date_Pk DATE Extract date No
Source_System VARCHAR2(20) Source of the extract No
Record_End_Date DATE End date of the record No

Column(s) of "BRANCH" Table

Name Data type Comment Is PK
Branch_Pk CHAR(40) unique identifier of the branch Yes
Record_Start_Date DATE Start date of the record Yes
Branch_Location VARCHAR2(40) Location of the Branch No
Branch_Type VARCHAR2(10) Type of the branch No
Parent_Branch_Pk VARCHAR2(10) The branch reference code of the No
branch to which this reports
Country_Pk CHAR(18) Unique identifier for the country No
Branch_Name CHAR(50) Name of the branch No
Branch_Short_Name CHAR(38) Short name of the branch No
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No
Currency_Pk CHAR(18) Unique identifier of the currency No
Record_End_Date DATE End date of the record No
Record_Status CHAR(18) Status of the record No

Column(s) of "COUNTRY" Table

Name Data type Comment Is PK
Country_Pk CHAR(18) Unique identifier for the country Yes
Record_Start_Date DATE Start date of the record Yes
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.
Geographic_Block_Pk CHAR(18) Unique identifier of the No
geographical block
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE end date of teh record No

Column(s) of "COUNTRY_CURRENCY" Table

Name Data type Comment Is PK
Country_Currency_Pk CHAR(18) Unique identifier of the record - Yes
concvatenation of Currency_pk,
Country_pk
Record_Start_Date DATE Start date of the record Yes
Currency_Pk CHAR(18) Unique identifier of the currency No
Country_Pk CHAR(18) Unique identifier for the country No
Currency_Start_Date DATE Start date of the currency in the No
country
Currecy_End_Date DATE End date of the currency in the No
country
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the data No
Record_End_Date DATE End date of the record No
Record_Status CHAR(18) Status of the record - live , No
history

TEMENOS T24 User Guide Page 17 of 88

Data Warehouse

Column(s) of "CURRENCY" Table

Name Data type Comment Is PK
Currency_Pk CHAR(18) Unique identifier of the currency Yes
Record_Start_Date DATE Start date of the record Yes
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.
Currency_Code_Des CHAR(40) Description of the currency code No
Local_Currency_Ind CHAR(1) Indiacator for the local currency No
Numeric_Ccy_Code VARCHAR2(20) Code of the currency No
No_Decimals INTEGER Number of decimals in the No
currency quotation
Quotation_Code CHAR(20) Kind of quotation like - buy, No
sell, etc.
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No
Rate_Allowance DECIMAL(15,2) Allowable difference in No
currency rate
Record_End_Date DATE End date of the record No

Column(s) of "CURRENCY_CURRENCY_MARKET" Table

Name Data type Comment Is PK
Currency_Currency_Market_Pk CHAR(50) Unique identifier of the record - Yes
Concatenation of Currency_pk,
Currency_market_pk.
Record_Start_Date DATE Start date of the record Yes
Currency_Pk CHAR(18) Unique identifier of the currency No
Currency_Market_Pk INTEGER Unique identifier of the currency No
market
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE End date of the record No
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.

Column(s) of "CURRENCY_MARKET" Table

Name Data type Comment Is PK
Currency_Market_Pk INTEGER Unique identifier of the currency Yes
market
Record_Start_Date DATE record start date Yes
Record_Status CHAR(18) Status of trhe record - live, No
historical, etc.
Currency_Market_Des CHAR(18) Description of the currency No
market
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE Record end date No

Column(s) of "CURRENCY_RATE" Table

Name Data type Comment Is PK
Date_Of_Last_Update_Pk DATE Date of last update Yes
Currency_Rate_Pk CHAR(40) Unique identifier of the record - Yes
Concatenation of
Currency_currency_market_pk,
Dest_currency_code
Currency_Currency_Market_Pk CHAR(50) Unique identifier of the record - No
Concatenation of Currency_pk,
Currency_market_pk.
Dest_Currency_Code_Pk CHAR(18) Identifier for the destination No
currency
Source_Currency_Code_Pk CHAR(18) Identifier for the source No
currency
Source_To_Dest_Curr_Buy_Rate NUMERIC(20,8) currency conversion rate from No
source currency to destination
currency

TEMENOS T24 User Guide Page 18 of 88

Data Warehouse

Column(s) of "CURRENCY_RATE" Table

Name Data type Comment Is PK
Dest_To_Source_Curr_Buy_Rate NUMERIC(20,8) currency conversion rate from No
destination currency to source
currency
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Source_To_Dest_Curr_Sell_Rate NUMERIC(20,8) currency conversion sell rate No
from source currency to
destination currency
Source_To_Dest_Curr_Mid_Rate NUMERIC(20,8) currency conversion mid rate No
from source currency to
destination currency
Dest_To_Source_Curr_Sell_Rate NUMERIC(20,8) currency conversion sell rate No
from destination currency to
source currency
Dest_To_Source_Curr_Mid_Rate NUMERIC(20,8) currency conversion mid rate No
from destination currency to
source currency
Record_Start_Date DATE Start date of the record No
Record_Status CHAR(18) Status of the record - live, No
history, etc

Column(s) of "EMPLOYEE_DEPT" Table

Name Data type Comment Is PK
Employee_Dept_Pk CHAR(40) Unique Identifier of the Yes
employee department or
designation
Record_Start_Date DATE Start date of the record Yes
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.
Employee_Dept_Des CHAR(80) Description of the employee No
department
Parent_Employee_Dept_Code CHAR(40) Identifier of the parent employee No
department
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Record_End_Date DATE End date of the record No

Column(s) of "EMPLOYEE_PARTY" Table

Name Data type Comment Is PK
Record_Status CHAR(18) No
Employee_Name CHAR(18) No
Employee_Party_Pk CHAR(40) Unique Identifier of the Yes
employee
Record_Start_Date DATE Start date of the record Yes
Record_Status CHAR(18) No
Emp_Branch_Start_Date DATE Date on which the employee No
association started with the
branch
Emp_Branch_End_Date DATE Date on which the employee No
association ended with the
branch
Parent_Employee_Dept_Code CHAR(40) The unique identifier of the No
employee in the host application
Business_Unit_Code CHAR(18) Identifier of the business unit No
Mis_Date_Pk DATE Extraction date No
Branch_Pk CHAR(40) unique identifier of the branch No
Employee_Dept_Pk CHAR(40) Unique Identifier of the No
employee department or
designation
Employee_Role CHAR(38) Role of the employee No
Source_System VARCHAR2(20) Source of the extract No
Employee_Name CHAR(50) Name of the employee No
Record_End_Date DATE end date of the record No
Record_Start_Date DATE Start date of the record No

TEMENOS T24 User Guide Page 19 of 88

Data Warehouse

Column(s) of "EMPLOYEE_RELATION" Table

Name Data type Comment Is PK
Party_Party_Relation_Pk CHAR(40) Unique identifier - Concatenate Yes
Employee_Party_pk,
Related_party_pk,
Party_relation_pk
Record_Start_Date DATE Start date of the record Yes
Relationship_End_Date DATE Relationship end date No
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Employee_Party_Pk CHAR(40) Unique identifier of the No
employee
Related_Party_Pk CHAR(40) Unique identifier of the related No
party
Party_Relation_Pk CHAR(40) Unique identifier of the party No
relation
Record_End_Date DATE End date of the record No
Record_Status CHAR(18) Status of trhe record - live, No
historical, etc.

Column(s) of "EXTERNAL_IDENTIFICATION" Table

Name Data type Comment Is PK
External_Identification_Pk CHAR(40) Unique identification of the Yes
record - Concatenation of
Party_pk,
External_identity_type_pk
Record_Start_Date DATE Start date of the record Yes
Ext_Identity_Start_Date DATE The date from which the No
external identification is
recognised
Party_Pk CHAR(40) Unique identifier of the party No
Ext_Identity_Number CHAR(40) The external identification No
number like Passport or SSN or
Driving license, etc.
Ext_Identity_End_Date DATE Expiry date of the external No
identity
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Ext_Identity_Doc_Name CHAR(50) External document name like No
passport, SSN, etc
Ext_Identity_Holder_Name CHAR(50) External document holder name No
Record_End_Date DATE end date of the record No
Record_Status CHAR(18) Status of trhe record - live, No
historical, etc.

Column(s) of "GENDER" Table

Name Data type Comment Is PK
Gender_Pk CHAR(18) Unique identifier of gender Yes
Record_Start_Date DATE Start date of the record Yes
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.
Gender_Des CHAR(50) Description of the gender No
identifier
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE End date of the record No

Column(s) of "GL_ENTRY" Table

Name Data type Comment Is PK
Gl_Entry_Pk CHAR(150) unique identifier of the GL entry Yes
Entry_Value_Date DATE Value date of the entry Yes
Entry_Booking_Date DATE Booking date of the entry No
Record_Status CHAR(18) Status of the record - live, No
historical, etc

TEMENOS T24 User Guide Page 20 of 88

Data Warehouse

Column(s) of "GL_ENTRY" Table

Name Data type Comment Is PK
Mis_Date_Pk DATE Extraction date No
Debit_Credit_Ind CHAR(10) Indicator for Debit or Credit No
Entry_Amount_Lcy DECIMAL(25,7) Amount of the GL entry No
Event_Pk CHAR(40) Unique identifier of the No
transaction
Gl_Repline_Pk CHAR(80) Unique identifier of the GL No
Reporting line
Legal_Entity_Book_Cd CHAR(18) Unique identifier of the Legal No
entity
Entry_Amount_Fcy DECIMAL(25,7) Entry amount in foreign No
currency
Source_System VARCHAR2(20) Application identifier in the No
source T24 like AC, FX, etc
Entry_Type VARCHAR2(20) Type of entry like CATEG or No
MI
Product_Pk CHAR(40) Unique identifier of the product No
Agmt_Pk CHAR(80) Identifier of the Arrangement No
which is contract between two
parties like customer and Bank
for product or service Primary
Key for record
A new record is generated every
time a relevant detail
(staff/branch/product segment
etc) is changed on this contract.
Date field may be included as
part of primary ID.
The start date/end date/change
date is also updated at this point.

Branch_Pk CHAR(40) unique identifier of the branch No

Pl_Category CHAR(20) Profit & Loss category of the No
entry
Entry_Exch_Rate DECIMAL(20,8) Exchange rate for the particular No
entry
Position_Type VARCHAR2(20) Type of position - trading or No
investment
Currency_Market_Pk INTEGER Unique identifier of the currency No
market
Employee_Dept_Pk CHAR(40) Department of the employee No
Currency_Pk CHAR(18) Unique identifier of the currency No
Party_Pk CHAR(40) Unique identifier of the party No
Employee_Party_Pk CHAR(40) Unique Identifier of the No
employee

Column(s) of "GL_REPORTING_BALANCE" Table

Name Data type Comment Is PK
Gl_Reporting_Balance_Pk CHAR(90) Unique identifier of the record Yes
Mis_Date_Pk DATE Yes
Record_Status CHAR(18) Status of the record - live, No
historical, etc
Balance_Start_Date_Pk DATE Start date of the GL balance No
Gaap_Code VARCHAR2(20) GAAP code for the GL code No
Balance_End_Date DATE End date of the GL balance No
Start_Balance_Amount DECIMAL(20,7) The monetary amount of the No
actual balance for the account at
the start of the accounting
period.
End_Balance_Amount NUMBER(20,7) The monetary amount of the No
actual balance for the account at
the end of the accounting period.
Adj_Balance_Amt DECIMAL(20,7) Balance after adjustment No
Legal_Entity_Book_Cd CHAR(18) Identifier of the Legal entity No
Gl_Source_Consol_Key CHAR(40) Consolidated key of the source No
GL - T24

TEMENOS T24 User Guide Page 21 of 88

Data Warehouse

Column(s) of "GL_REPORTING_BALANCE" Table

Name Data type Comment Is PK
Source_System CHAR(18) Source of the extract No
Currency_Market_Pk CHAR(18) No
Position_Type VARCHAR2(20) Type of position - trading or No
investment
Debit_Movement DECIMAL(20,7) Movement in Debit balance No
Credit_Movement DECIMAL(20,7) Movement in Credit balance No
End_Balance_Amount_Lcy DECIMAL(20,7) Balance amount in local No
currency
Debit_Movement_Lcy DECIMAL(20,7) Debit movement in local No
currency
Credit_Movement_Lcy DECIMAL(20,7) Credit movement in local No
currency
Balance_Amt_Ytd DECIMAL(20,7) Balance amount year till date No
Balance_Amt_Ytd_Lcy DECIMAL(20,7) Balance amouny tear till date in No
local currency
Currency_Pk CHAR(18) unique identifier of currency No
Asset_Type CHAR(40) Type of the asset / Liab No

Column(s) of "GLOBAL_PARTY" Table

Name Data type Comment Is PK
Global_Party_Pk VARCHAR2(40) Unique identifier of the Party Yes
who is there accross many
countries
Record_Status CHAR(18) Staus of the record - live, Yes
historical, etc.
Global_Party_Des CHAR(50) Description of the Global Party No
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source system of the data No
Record_Start_Date DATE Start date of the record No
Record_End_Date DATE End date of the record No

Column(s) of "INDUSTRY" Table

Name Data type Comment Is PK
Industry_Pk VARCHAR2(15) Unique identifier of the Yes
customer industry
Record_Start_Date DATE Record start date Yes
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.
Industry_Des CHAR(50) Description of the industry No
Parent_Industry_Pk CHAR(18) Industry code to which it reports No
to
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Record_End_Date DATE Record end date No

Column(s) of "INTEREST_BASIS" Table

Name Data type Comment Is PK
Interest_Basis_Pk CHAR(40) Unique identifier of the record Yes
Record_Start_Date DATE Record start date Yes
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.
Interest_Basis_Des CHAR(48) Descripotion of the interest basis No
Interest_Basis CHAR(18) Value of the interest basis No
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk CHAR(18) Extraction date No
Record_End_Date DATE Record end date No

Column(s) of "INTEREST_BENCHMARK" Table

Name Data type Comment Is PK
Interest_Benchmark_Des_Pk CHAR(60) Description of the Interest Yes
benchmark code like LIBOR
Mis_Date_Pk DATE Extraction date Yes

TEMENOS T24 User Guide Page 22 of 88

Data Warehouse

Column(s) of "INTEREST_BENCHMARK" Table

Name Data type Comment Is PK
Record_Start_Date DATE Start date of the record No
Interest_Benchmark_Rate DECIMAL(15,8) Rate of the Benchmark like No
LIBOR
Source_System VARCHAR2(20) Source of the extract No
Record_Status CHAR(18) No

Column(s) of "INTEREST_BENCHMARK_DES" Table

Name Data type Comment Is PK
Interest_Benchmark_Des_Pk CHAR(40) Description of the Interest Yes
benchmark code like LIBOR
Record_Start_Date DATE Start date of the record Yes
Interest_Benchmark_Pk CHAR(40) Unique identifier of the Interest No
Benchmark like LIBOR
Time_Period_Cd CHAR(18) Code for time period like daily, No
monthly, etc.
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Currency_Pk CHAR(18) Unique identifier of the currency No
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.
Record_End_Date DATE End date of the record No

Column(s) of "JOB_TITLE" Table

Name Data type Comment Is PK
Job_Title_Pk VARCHAR2(15) unique identifier of the job title Yes
of the employee
Record_Start_Date DATE Start date of the record Yes
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.
Job_Description CHAR(18) Description of the job title of the No
employee
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE End date of the record No

Column(s) of "LIMIT" Table

Name Data type Comment Is PK
Limit_Pk CHAR(80) Unique identifier of Limit Yes
Mis_Date_Pk DATE Extraction date Yes
Approval_Date DATE Date of approval No
Review_Freq CHAR(50) Frequency of limit amount No
review
Limit_Maturity_Date DATE Date of limit expiry No
Limit_Start_Date DATE Start date of Limit No
Av_Limit_Amount DECIMAL(15,2) Available limit amount No
Limit_Advised_Amount DECIMAL(15,2) Advised limit amount No
Limit_Grade VARCHAR2(20) Rating of the Limit account No
Amount_Sanctioned DECIMAL(15,2) Sanctioned amount No
Fixed_Variable VARCHAR2(20) Fixed or variable limit No
Max_Secure DECIMAL(15,2) Maximum Secured amount No
Max_Unsecure DECIMAL(15,2) Maximum Unsecured amount No
Limit_Amt DECIMAL(15,2) Secured amount of the limit No
Commt_Amt_Avl DECIMAL(15,2) Committment amount available No
Outstanding_Amount DECIMAL(15,2) Outstanding amount against the No
Limit
Secured_Amount DECIMAL(15,2) Amount Secured No
Source_System VARCHAR2(20) Source of the extract No
Currency_Pk CHAR(18) Unique identifier of the currency No
Other_Secured DECIMAL(25,5) Other secured amount No
Original_Limit DECIMAL(25,5) Original Limit amount No
Last_Reval_Date DATE Last revaluation date No
Liability_Id CHAR(40) Unique identifier of a particular No

TEMENOS T24 User Guide Page 23 of 88

Data Warehouse

Column(s) of "LIMIT" Table

Name Data type Comment Is PK
Credit line
Parent_Liab_Id CHAR(40) Unique identifier of the parent No
credit line
Product_Pk VARCHAR2(15) Unique identifier of the product No
Credit_Line CHAR(40) Credit line No
Record_Status CHAR(18) Status of the record No

Column(s) of "LIMIT_BRANCH" Table

Name Data type Comment Is PK
Limit_Branch_Pk CHAR(40) Unique identifier of the limit Yes
branch table. It is the unique
identifier of the Limit_pk and
Branch_pk
Mis_Date_Pk DATE Extraction date Yes
Limit_Pk CHAR(80) Unique identifier of Limit No
Branch_Pk CHAR(40) unique identifier of the branch No
Source_System VARCHAR2(20) Source system identifier No
Record_Start_Date DATE Start date of the record No
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.

Column(s) of "LIMIT_COLLATERAL" Table

Name Data type Comment Is PK
Limit_Collateral_Pk CHAR(90) Unique identifier of the Limit Yes
Collateral relationship -
Concatenation of Limit_pk,
Protection_asset_id
Mis_Date_Pk DATE Extraction date Yes
Limit_Pk CHAR(80) Unique identifier of Limit No
Protection_Asset_Group CHAR(20) Identification of the Protection No
asset
Coll_Reqd_Amt DECIMAL(15,2) Required Collateral amount for No
the limit
Coll_Reqd_Prcnt DECIMAL(15,2) Required Collateral percent for No
the limit
Collateral_End_Date DATE Expiry date of the collateral in No
case of secutity
Source_System VARCHAR2(20) Source of the extract No
Max_Secured_Amt DECIMAL(25,5) Maximum secured amount No
Party_Pk CHAR(40) Unique identiofier of Party No
Currency_Pk CHAR(18) Unique identifier of the currency No
Collateral_Amt DECIMAL(25,5) Collateral amount No
Collateral_Percent NUMBER(10,5) Percent of the Collateral No
Collateral_Location CHAR(50) Location of the collateral No
Collateral_Grade CHAR(20) Collateral rating No
Record_Start_Date DATE Start date of the record No
Mis_Date_Pk DATE Extraction date No
Record_Status CHAR(18) Status of record No

Column(s) of "LIMIT_COLLATERAL_PERIOD" Table

Name Data type Comment Is PK
Limit_Collateral_Period_Pk CHAR(90) Yes
Mis_Date_Pk DATE Extraction date Yes
Limit_Pk CHAR(80) Unique identifier of Limit No
Protection_Asset_Group CHAR(20) Identifier of the protection No
asset group
Collateral_Period_End NUMBER End of the collateral period No
Collateral_Period_Amt DECIMAL(25,5) Amount at the end of the No
collateral period
Collateral_Period_Percent NUMBER(10,5) Percent during the Collateral No
period
Source_System VARCHAR2(20) Source system identifier No
Record_Status VARCHAR2(20) Staus of the record - live, No

TEMENOS T24 User Guide Page 24 of 88

Data Warehouse

Column(s) of "LIMIT_COLLATERAL_PERIOD" Table

Name Data type Comment Is PK
historical, etc.

Column(s) of "LIMIT_COLLATERAL_RIGHT" Table

Name Data type Comment Is PK
Limit_Collateral_Right_Pk CHAR(80) Unique identifier of the table Yes
Mis_Date_Pk DATE Extraction date Yes
Prot_Asset_Right_Pk CHAR(40) Unique identifier of the No
protection asset right
Limit_Pk CHAR(80) Unique identifier of Limit No
Prot_Asset_Right_Sec_Amt DECIMAL(25,5) Protection assety right secured No
amount
Source_System VARCHAR2(20) Identifier of the source system No
Mis_Date_Pk DATE Extraction date No
Record_Start_Date DATE Start date of the record No
Record_Status CHAR(18) No

Column(s) of "LIMIT_COMMODITY" Table

Name Data type Comment Is PK
Limit_Commodity_Pk CHAR(80) Unique identifier of the table. It Yes
is the concatenation of Limit_pk
and Commodity_pk
Mis_Date_Pk DATE Extraction date Yes
Limit_Pk CHAR(80) Unique identifier of Limit No
Commodity_Pk CHAR(40) Unique identifier of the No
commodity
Commodity_Percent NUMBER(10,5) Percent of the commodity No
Source_System VARCHAR2(20) Source _system_identifier No
Record_Status CHAR(18) Status of the record - live or No
history

Column(s) of "LIMIT_COUNTRY" Table

Name Data type Comment Is PK
Limit_Country_Pk CHAR(80) Yes
Mis_Date_Pk DATE Extraction date Yes
Limit_Pk CHAR(80) Unique identifier of Limit No
Limit_Country_Start_Date DATE Start date of the limit for the No
country
Limit_Country_Percent DECIMAL(15,2) Percent of the limit in country No
Source_System VARCHAR2(20) Source of the extract No
Country_Pk CHAR(18) Unique identifier for the country No
Record_Start_Date DATE Start date of the record No
Record_Status CHAR(18) Status of the record - live or No
history

Column(s) of "LIMIT_CURRENCY" Table

Name Data type Comment Is PK
Limit_Currency_Pk CHAR(80) Unique identifier of the table Yes
which is a concatenation of
Limit_pk and Currency_pk
Mis_Date_Pk DATE Extraction date Yes
Currency_Pk CHAR(18) Unique identifier of the currency No
Limit_Pk CHAR(80) Unique identifier of Limit No
Limit_Ccy_Amt DECIMAL(25,5) Amount of the limit for the No
particular currency
Source_System VARCHAR2(20) Source System identifier No
Record_Start_Date DATE Start date of the record No
Record_Status CHAR(18) No

Column(s) of "LIMIT_GRADE" Table

Name Data type Comment Is PK
Limit_Grade_Pk CHAR(80) Unique key for the limit grade Yes

TEMENOS T24 User Guide Page 25 of 88

Data Warehouse

Column(s) of "LIMIT_GRADE" Table

Name Data type Comment Is PK
Mis_Date_Pk DATE Extraction date Yes
Limit_Pk CHAR(80) Unique key for the limit No
Limit_Grade_Start_Date DATE Start date of the limit grade No
Limit_Grade_Percent DECIMAL(15,2) Percent of the limit within this No
grade
Source_System VARCHAR2(20) Source of the extract No
Grade_Pk CHAR(20) Grade of the Limit No
Record_Status CHAR(18) Status of the record - live or No
history

Column(s) of "LIMIT_MATURITY" Table

Name Data type Comment Is PK
Limit_Maturity_Pk CHAR(80) Unique identifier of the table Yes
and is a concatenation of
Limit_Pk and Maturity Period
Mis_Date_Pk DATE Extraction date Yes
Limit_Pk CHAR(80) Unique identifier of Limit No
Maturity Period NUMBER Maturity period of the Limit No
Limit_Band CHAR(40) Limit Band No
On_Line_Limit_Amount DECIMAL(25,5) on line limit amount No
Sub_Alloc_Taken_Limit DECIMAL(25,5) Sub-allocation of limit taken No
Sub_Alloc_Given_Limit DECIMAL(25,5) Sub-allocation of limit given No
Commt_Amt_Avail DECIMAL(25,5) Commitment amount available No
Limit_Outstanding_Amount DECIMAL(25,5) outstanding limit amount No
Excess_Date DATE Date of excess limit No
Amount_Last_Excess DECIMAL(25,5) Amount of limit which was No
excess last time
Source_System VARCHAR2(20) Source of the extract No
Av_Limit_Amt DECIMAL(25,5) Limit amount available No
Record_Status CHAR(18) No

Column(s) of "LIMIT_PRODUCT" Table

Name Data type Comment Is PK
Limit_Product_Pk CHAR(80) Unique identifier - Yes
Concatenation of Limit_pk and
Product_pk
Mis_Date_Pk DATE Extraction date Yes
Record_Start_Date DATE Start date of the record No
Limit_Pk CHAR(80) Unique identifier of the Limit No
Product_Pk CHAR(40) Unique identifier of the product No
Source_System VARCHAR2(20) Source of the extract No
Record_Status CHAR(18) Status of the record - live or No
history

Column(s) of "MIS_DATE" Table

Name Data type Comment Is PK
Mis_Date_Pk DATE Extraction date Yes
Record_Status CHAR(18) Status of the record - live, No
historical, etc
Julian_Date DATE Julian Date for this date. In No
case reports need to be run from
this perspective
Time_Day_Of_Month NUMBER This will indicate the day of the No
month
Time_Day_Of_Week NUMBER This will indicate the numeric No
day of the week
Time_Week_Day_Desc VARCHAR2(15) This will indicate the weekday No
for the date.
Time_Day_Of_Year NUMBER This signifies the number of the No
day of the Year. Eg. January 1st
is the 1st Day of the year and
December 31st may be the
365th day of the year.

TEMENOS T24 User Guide Page 26 of 88

Data Warehouse

Column(s) of "MIS_DATE" Table

Name Data type Comment Is PK

Time_Week VARCHAR2(15) This signifies the number of the No

week of the Year. Eg. january
1st to 7th is the First week of the
year.

Time_Month VARCHAR2(15) This attribute indicates the No

month for which the processing
has been commenced. Eg: If the
processing is for 20000331, then
this attribute will indicate 3.

Time_Quarter VARCHAR2(15) This will hold the quarter of the No

calendar year in which this date
falls in

Time_Half_Year VARCHAR2(15) This will hold the half year of No

the calendar year in which this
date falls in
Time_Year VARCHAR2(15) This will indicate the year to No
which this date belongs to
Day_Type_Code NUMERIC It defines the type of day and the No
codes are :

1 : Week day
2 : Week end
3 : Holiday
Week_End_Ind CHAR(1) Indicator whether the day No
belongs to weekend
Month_End_Ind CHAR(1) Indicator whether the day No
belongs to month end
Quarter_End_Ind CHAR(1) Indicator whether the day No
belongs to quarter end
Half_Year_End_Ind CHAR(1) Indicator whether the day No
belongs to half year end
Year_End_Ind CHAR(1) Indicator whether the day No
belongs to year end
Fiscal_Year_End_Ind CHAR(1) Indicator whether the day No
belongs to fiscal year end
Time_Month_Quarter_Ind CHAR(1) A month is divided ointo 4 No
quarters. This refers to the
quarter number within the
month
Time_Total_Day_Count NUMBER The number of days till date of No
extraction
Time_Total_Week_Count NUMBER The number of weeks till date of No
extraction
Time_Total_Month_Count NUMBER The number of months till date No
of extraction
Time_Total_Year_Count NUMBER The number of years till date of No
extraction
Record_Start_Date DATE Start date of the record No
Record_End_Date DATE End date of the record No
Source_System VARCHAR2(20) Source of the extract No

Column(s) of "OCCUPATION" Table

Name Data type Comment Is PK
Occupation_Cd_Pk VARCHAR2(20) Unique identifier of the Yes
occupation
Record_Start_Date DATE Start date of the record Yes
Record_Status CHAR(18) Status of the record - live, No
historical, etc
Occupation_Des VARCHAR2(10) Description of the occupation No
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No

TEMENOS T24 User Guide Page 27 of 88

Data Warehouse

Column(s) of "OCCUPATION" Table

Name Data type Comment Is PK
Record_End_Date DATE End date of the record No

Column(s) of "PARTY" Table

Name Data type Comment Is PK
Party_Pk CHAR(40) Unique identifier of the party Yes
Record_Start_Date DATE Start date of the record Yes
Party_Code CHAR(18) Unique identifier in the host No
application
Party_Address_Pk VARCHAR2(15) Unique identifier of the address No
Branch_Pk VARCHAR2(15) Unique identifier of the branch No
Party_Segment_Pk CHAR(40) unique identifier of the party No
segment
Event_Pk VARCHAR2(15) Unique identifier of the event No
Portfolio_Pk VARCHAR2(15) Unique identifier of the portfolio No
Residence_Country_Pk VARCHAR2(15) Unique identifier of the No
Customer residence country
Employee_Ind CHAR(1) Indicator whether party is a No
straff
Sector_Pk VARCHAR2(15) Unique identifier of the sector No
Party_Start_Date DATE Start date of the party with the No
Bank
Party_End_Date DATE end date of the party with the No
Bank
Party_Type_Ind VARCHAR2(20) Whether active customer or No
Prospect
Record_End_Date DATE End date of the record No
Industry_Pk VARCHAR2(15) Unique identifier of the No
customer industry
Posting_Restriction_Ind CHARACTER(1) Indicator for the posting No
restriction
Party_Status_Pk CHAR(40) Unique identifier of the party No
status
Source_System VARCHAR2(20) Source of the extract No
Short_Name CHAR(40) Short name of the party No
Party_Name_1 CHAR(40) First name of Party No
Party_Name_2 CHAR(40) Second party of name of party No
Party_Nationality CHAR(40) Nationality of the party No
Birth_Date DATE Date of birth of the party No
Death_Date DATE Date of death for the party No
Job_Title_Start_Date DATE Start date of the job title of the No
individual
Job_Title_End_Date DATE End date of the job title of the No
individual
Job_Title_Pk VARCHAR2(15) unique identifier of the job title No
of the employee
Gender_Pk CHAR(18) Unique identifier of gender No
Occupation_Cd_Pk VARCHAR2(20) Unique identifier of the No
occupation
Mis_Date_Pk DATE Extraction date No
Country_Pk CHAR(18) Unique identifier for the country No
Party_Mnemonic CHAR(60) Mnemonic of the Party No
Party_Title CHAR(40) Title of the party before the No
name
Party_Name CHAR(40) Name of the Party No
Party_Surname CHAR(40) Surname of the party No
Party_Language CHAR(25) Language of the party No
No_Of_Dependants INTEGER Number of dependants of the No
party
Party_Occupation CHAR(40) Occupation of the party like No
doctor, engineer, etc,
Party_Salary DECIMAL(15,5) Salary of the Party No
Party_Bonus DECIMAL(15,6) Yearly bonus of the Party No
Residence_Status CHAR(40) Residence status of the Party - No
rented, owned, etc.

TEMENOS T24 User Guide Page 28 of 88

Data Warehouse

Column(s) of "PARTY" Table

Name Data type Comment Is PK
Residence_Type CHAR(40) Type of residence - 1 bedroom, No
Studio, villa, etc
Party_Previous_Name CHAR(60) Previous name of the Party No
Party_Domicile CHAR(40) Domicile of the Party No
Other_Nationality CHAR(40) Other nationality of the Party No
Party_Demo1 CHAR(40) party demo 1 No
Party_Demo2 CHAR(40) party demo 2 No
Party_Demo3 CHAR(40) party demo 3 No
Party_Demo4 CHAR(40) party demo 4 No
Party_Demo5 CHAR(40) party demo 5 No
Party_Demo6 CHAR(40) party demo 6 No
Party_Demo7 CHAR(40) party demo 7 No
Party_Demo8 CHAR(40) party demo 8 No
Party_Demo9 CHAR(40) party demo 9 No
Party_Marital_Status CHAR(40) Marital status of the Party No
Record_Status CHAR(18) No

Column(s) of "PARTY_ADDRESS" Table

Name Data type Comment Is PK
Party_Address_Pk CHAR(40) Unique address of the Party Yes
Record_Start_Date DATE Start date of the record Yes
Party_Address_Start_Date DATE Start date of address No
Street_Address CHAR(80) Address on the street No
Unit_Number NUMBER House unit number No
Floor_Number INTEGER number of the floor where flat is No
located
Block_Number CHAR(18) Number of the block where flat No
is located
Name_1 CHAR(40) description of the address No
Name_2 CHAR(40) description of the address No
City_Code CHAR(40) Pincode of the city No
Postal_Code VARCHAR2(20) Post box number No
Territory_Code CHAR(40) Code of the territory No
Country_Group_Code VARCHAR2(15) code of the country group like No
EU
Country_Pk CHAR(18) Code of the country No
Tel_Country_Code VARCHAR2(20) Country code of the telephone No
number
Tel_Area_Cd VARCHAR2(20) Area code within a country of No
the telephone number
Residence_Tel_Num CHAR(40) Residence Telephone number of No
the customer
Party_Address_End_Date DATE End date of the address No
Email_Id VARCHAR2(50) Email address of the party No
Mobile_Number CHAR(40) Mobile phone number of the No
customer
Business_Tel_Number INTEGER Business Telephone number of No
the customer
Fax_Number INTEGER Fax number of the customer No
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Branch_Pk CHAR(40) unique identifier of the branch No
Party_Pk CHAR(40) Unique identifier of the party No
Delivery_Address CHAR(120) Delivery address of the party No
Short_Name CHAR(60) Short name of the party No
Country_Name CHAR(60) Name of the country No
Record_End_Date DATE End date of the record No
Record_Status CHAR(18) Status of the record No

Column(s) of "PARTY_EMPLOYMENT" Table

Name Data type Comment Is PK
Party_Employment_Pk CHAR(50) Unique identifier of the Yes
party_employment and it is a

TEMENOS T24 User Guide Page 29 of 88

Data Warehouse

Column(s) of "PARTY_EMPLOYMENT" Table

Name Data type Comment Is PK
concatenation of the Party_pk
and employment_status.
Record_Start_Date DATE Start date of the record Yes
Record_Status CHAR(18) Status of the record - live or No
history
Employment_Status VARCHAR2(20) Status of the employment No
Occupation CHAR(40) Occupation of the Party No
Job_Title_Pk CHAR(40) Job title of the party No
Employer_Name CHAR(50) Name of the employer No
Employer_Business CHAR(40) Business of the Employer No
Employment_Start_Date DATE Start date of the employment No
Salary DECIMAL(25,5) Salary of the party No
Annual_Bonus DECIMAL(25,5) Annual bonus of the party No
Source_System VARCHAR2(20) Source system identifier No
Mis_Date_Pk DATE Extraction date No
Currency_Pk CHAR(18) Unique identifier of currency No
Party_Pk CHAR(40) Unique identifier of the party No
Record_End_Date DATE End date of record No

Column(s) of "PARTY_LIMIT" Table

Name Data type Comment Is PK
Party_Limit_Pk CHAR(100) Unique identifier of the Party Yes
Limit
Mis_Date_Pk DATE Extraction date Yes
Limit_Start_Date_Pk DATE Start date of the limit No
Limit_Type_Cd_Pk CHAR(18) Limit type No
Limit_Amt DECIMAL(12,2) Total limit amount for the party No
which includes all arrangements
for the party
Limit_End_Date DATE Expiry date of the Limit No
Source_System VARCHAR2(20) Source of the extract No
Limit_Pk CHAR(80) Unique identifier of Limit No
Party_Pk CHAR(40) Unique identifier of the party No
Mis_Date_Pk DATE Extraction date No
Record_Status CHAR(18) No

Column(s) of "PARTY_NAME" Table

Name Data type Comment Is PK
Party_Name_Pk CHAR(60) Unique Identifier of the Party Yes
name
Record_Start_Date DATE Start date of the record Yes
Name_Start_Date DATE Effective start date of the name No
of the party
Party_Pk CHAR(40) Unique identifier of the party No
Previous_Name CHAR(90) Given name of the party No
Name_End_Date DATE Effective end date of the name No
of the party
Middle_Name CHAR(80) Middle name of the party No
Family_Name CHAR(80) surname or the family name like No
'Taylor' in the name 'Bob Taylor'
Prefix_Text VARCHAR2(10) The salutation like Mr, Ms, etc No
Suffix_Text VARCHAR2(10) Salutation like Jr. No
Short_Name VARCHAR2(30) Short name of party No
Name_Change_Reason CHAR(100) Reason of name change No
Source_System VARCHAR2(20) Source system identifier No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE End date of record No
Record_Status CHAR(18) Status of the record - live, No
historical, etc.

Column(s) of "PARTY_PARTY_RELATION" Table

Name Data type Comment Is PK

TEMENOS T24 User Guide Page 30 of 88

Data Warehouse

Column(s) of "PARTY_PARTY_RELATION" Table

Name Data type Comment Is PK
Party_Party_Relation_Pk CHAR(40) Unique identifier of the Yes
Party_Party_relation -
Concatenation of Party_pk,
Related_party_pk,
Party_relation_pk
Record_Start_Date DATE Start date of the record Yes
Relationship_Start_Date_Pk DATE Start date of the association No
between 2 parties
Party_Pk CHAR(40) Unique identifier of the party No
Related_Party_Pk VARCHAR2(15) Identifier of the related party No
Party_Relation_Pk CHAR(40) Unique identifier of the No
relationship
Relationship_End_Date DATE End date of the association No
between 2 parties
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Record_End_Date DATE end date of record No
Record_Status CHAR(18) No

Column(s) of "PARTY_RATING" Table

Name Data type Comment Is PK
Party_Pk CHAR(18) No
Record_Status CHAR(18) Yes
Party_Rating_Pk CHAR(40) Unique identifier of the Party Yes
rating
Record_Start_Date DATE Start date of the record Yes
Rating_Start_Date_Pk DATE Start date of the Party rating No
Rating_Pk CHAR(18) Rating type for example short No
term, long term, etc
Rating_Cd VARCHAR2(20) Rating code of the Party like No
"AAA"
Rating_End_Date DATE End date of the rating No
Rating_Review_Date DATE Next review date for the rating No
Rating_Status VARCHAR2(20) Status of the rating like "under No
watch", "to be withdrawn", etc
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Party_Pk CHAR(40) Unique identifier of the party No
Record_End_Date DATE end date of record No
Record_Status CHAR(18) No

Column(s) of "PARTY_RELATION" Table

Name Data type Comment Is PK
Party_Relation_Pk CHAR(40) Unique identifier of the Yes
relationship
Record_Start_Date DATE Start date of record Yes
Record_Status CHAR(18) Status of the record - live, No
historical, etc
Relation_Description CHAR(18) Description of the relationship No
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Record_End_Date DATE End date of record No

Column(s) of "PARTY_RESIDENCE" Table

Name Data type Comment Is PK
Party_Residence_Pk CHAR(60) Unique identifier of the table Yes
and concatenation of Party_pk
and Residence_status
Record_Start_Date DATE Start date of the record Yes
Party_Pk CHAR(40) Unique identifier of the party No
Residence_Status VARCHAR2(20) Status of the residence No
Reside_Type CHAR(40) Type of the residence No
Reside_Start_Date DATE Start date of the residence No

TEMENOS T24 User Guide Page 31 of 88

Data Warehouse

Column(s) of "PARTY_RESIDENCE" Table

Name Data type Comment Is PK
Residence_Value DECIMAL(25,5) Value of the residence No
Source_System VARCHAR2(20) Source System identifier No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE End date of record No
Record_Status CHAR(18) Status of the record - live or No
history

Column(s) of "PARTY_SEGMENT" Table

Name Data type Comment Is PK
Party_Segment_Pk CHAR(40) Unique identifier of the Party Yes
Segment
Record_Start_Date DATE Start date of the record Yes
Record_Status CHAR(18) Status of the record - live, No
historical, etc
Party_Segment_Description CHAR(80) Description of the Party No
Segment
Party_Segment_Short_Name CHAR(80) Short name of the Party No
Segment
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the Extract No
Record_End_Date DATE No

Column(s) of "PARTY_STATUS" Table

Name Data type Comment Is PK
Party_Status_Pk CHAR(40) Unique identifier of the party Yes
status
Record_Start_Date DATE Start date of record Yes
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.
Party_Status_Des CHAR(60) Description of the Party Status No
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE End date of record No

Column(s) of "PRODUCT" Table

Name Data type Comment Is PK
Product_Pk CHAR(40) Unique identifier of the product Yes
Record_Start_Date DATE Start date of the record Yes
Record_Status CHAR(18) Status of the record - live, No
historical, etc
Mis_Date_Pk DATE Extraction date No
Prod_Short_Name CHAR(40) short Name of the product No
Prod_Desc CHAR(50) Description of the Product No
Prod_Mnemonic CHAR(20) Mnemonic of the Product name No
Prod_Module_Ind CHAR(20) Module which the Product No
belongs to
Prod_Start_Date DATE Start date of the product No
New_Prod_Ind CHAR(1) Indicator whether the product No
has been recently launched
Prod_End_Date DATE Start date of the product No
Source_System CHAR(18) Source of the extract No
Record_End_Date DATE End date of the record No

Column(s) of "PROT_ASSET_RIGHT" Table

Name Data type Comment Is PK
Prot_Asset_Right_Pk CHAR(40) Unique identifier of the table Yes
Record_Start_Date DATE Start date of the record Yes
Protection_Asset_Group CHAR(20) Group of the Protection asset No
Percent_Cover DECIMAL(10,5) Percent of amount covered by No
the Protection Asset
Validity_Date DATE Date of validity of the No
protection asset

TEMENOS T24 User Guide Page 32 of 88

Data Warehouse

Column(s) of "PROT_ASSET_RIGHT" Table

Name Data type Comment Is PK
Review_Date_Fqu CHAR(40) Frequency of the review date No
Expiry_Date DATE Expiry date of the asset No
Party_Pk CHAR(40) Unique identifier of the Party No
Local_Ref CHAR(40) Local reference No
Record_Status CHAR(18) Status of the record - live, No
historical, etc
Os_Percent_Cover DECIMAL(10,5) Percent of outstanding covered No
by protection asset
Alloc_Work_Id CHAR(40) Allocated work identification No
Limit_Pk CHAR(80) Unique identifier of Limit No
Source_System VARCHAR2(20) Source system identifier No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE End date of record No
Mis_Date_Pk DATE Extraction date No

Column(s) of "PROT_ASSET_RIGHT_BRANCH" Table

Name Data type Comment Is PK
Prot_Asset_Right_Branch_Pk CHAR(40) Unique identifier of the table Yes
and is a concatenation of
Prot_asset_right_pk and
Branch_pk
Record_Start_Date DATE Start date of the record Yes
Branch_Pk CHAR(40) unique identifier of the branch No
Prot_Asset_Right_Pk CHAR(40) Unique identifier of the No
Prot_asset_right table
Limit_Reference CHAR(40) Reference for the Limit No
Party_Pk CHAR(40) Unique identifier of Party No
Percent_Allocation NUMBER(10,5) Percent of Protection asset No
allocated for the branch
Source_System VARCHAR2(20) Source system identifier No
Mis_Date_Pk DATE Extraction date No
Record_Start_Date DATE Start date of the record No
Record_Status CHAR(18) Status of the record - live, No
historical, etc

Column(s) of "PROT_ASSET_RIGHT_LIMIT" Table

Name Data type Comment Is PK
Prot_Asset_Right_Limit_Pk CHAR(60) unique identifier of the table and Yes
concatenation of Limit_pk and
Prot_asset_right_pk
Record_Start_Date DATE Start date of the record Yes
Limit_Pk CHAR(80) Unique identifier of Limit No
Prot_Asset_Right_Pk CHAR(40) Unique identifier of the table No
Prot_asset_right
Source_System VARCHAR2(20) source system identifier No
Record_End_Date DATE End date of the record No
Record_Status CHAR(18) Status of the record - live, No
historical, etc
Mis_Date_Pk DATE Extraction date No

Column(s) of "PROT_ASSET_RIGHT_PARTY" Table

Name Data type Comment Is PK
Prot_Asset_Right_Party_Pk CHAR(60) Unique identifier of the table Yes
and concatenation of
Prot_asset_right_pk and
Party_pk
Record_Start_Date DATE Start date of the record Yes
Party_Pk CHAR(40) Unique identifier of the party No
Prot_Asset_Right_Pk CHAR(40) Unique identifier of the table No
Source_System VARCHAR2(20) Source system identifier No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE end date of the record No
Record_Status CHAR(18) Status of the record - live, No

TEMENOS T24 User Guide Page 33 of 88

Data Warehouse

Column(s) of "PROT_ASSET_RIGHT_PARTY" Table

Name Data type Comment Is PK
historical, etc

Column(s) of "PROTECTION_ASSET" Table

Name Data type Comment Is PK
Protection_Asset_Pk CHAR(40) Identification of the Protection Yes
asset
Mis_Date_Pk DATE Extraction date Yes
Protection_Asset_Address_Id CHAR(40) The address or location of the No
asset
Protection_Asset_Des VARCHAR2(40) Description of the asset No
Protection_Asset_Maturity_Date DATE Maturity date of the asset which No
refers to the end of its economic
life
Prot_Asset_Bank_Id VARCHAR2(20) Unique identification of the No
asset in the host application of
the bank
Branch_Pk CHAR(40) unique identifier of the branch No
Country_Pk CHAR(18) Unique identifier for the country No
Source_System VARCHAR2(20) Source of the extract No
Prot_Asset_Value_Exe DECIMAL(20,6) Value of the protection asset as No
on date
Prot_Asset_Value_Max DECIMAL(20,6) Maximum value of Collateral No
Prot_Asset_Value_Third_Party DECIMAL(20,6) Third party protection asset No
value
Prot_Asset_Value_Gl DECIMAL(20,6) Value of the Protection asset in No
GL
Prot_Asset_Value_Cen_Bank DECIMAL(20,6) Collateral value as per Central No
Bank
Currency_Pk CHAR(18) Unique identifier of the currency No
Protection_Asset_Type_Pk CHAR(20) Type of protection asset No
Protection_Asset_Group CHAR(20) Grouping of the Protection No
Asset
Asset_Value_Start_Date DATE Start date of the Asset value No
Prot_Asset_Value DECIMAL(25,5) Value of the protection asset No
Record_Status CHAR(18) Staus of the record - live, No
historical, etc.

Column(s) of "PROTECTION_ASSET_GROUP" Table

Name Data type Comment Is PK
Protection_Asset_Group_Pk CHAR(20) Unique identifier of the Yes
Protection Asset Group
Record_Start_Date DATE Start date of the record Yes
Protection_Asset_Group CHAR(20) Protection asset group No
Protection_Asset_Group_Des CHAR(50) Protection asset group No
description
Protection_Asset_Group_Name CHAR(40) Protection asset group name No
Protection_Asset_Type_Pk CHAR(20) Unique identifier of the No
Protection_asset_type
Source_System VARCHAR2(20) Source system identifier No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE End date of the record No
Record_Status CHAR(18) Status of the record - live, No
historical, etc

Column(s) of "PROTECTION_ASSET_TYPE" Table

Name Data type Comment Is PK
Protection_Asset_Type_Pk CHAR(20) Unique identifier of the Yes
Protection_asset_type
Record_Start_Date DATE Start date of the record Yes
Record_Status CHAR(18) Status of the record - live, No
historical, etc
Protection_Asset_Type_Des CHAR(40) Protection asset type description No
Nominal_Value CHAR(40) Nominal value No

TEMENOS T24 User Guide Page 34 of 88

Data Warehouse

Column(s) of "PROTECTION_ASSET_TYPE" Table

Name Data type Comment Is PK
Execution_Value CHAR(40) Execution Value No
Third_Party_Value CHAR(40) Third Party Value No
Gen_Ledger_Value CHAR(40) General Ledger Value No
Central_Bank_Value CHAR(40) Central Bank Value No
Source_System VARCHAR2(20) Source system identifier No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE End date of the record No

Column(s) of "RATING" Table

Name Data type Comment Is PK
Rating_Pk CHAR(40) Unique identifier of the party Yes
Record_Start_Date DATE Start date of the record Yes
Rating_Des CHAR(60) Description of the rating agency No
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Rating_Agency_Id CHAR(40) Identifier of the rating agency No
Record_End_Date DATE End date of the record No
Record_Status CHAR(20) Status of the record - live, No
historical, etc

Column(s) of "RATING_AGENCY" Table

Name Data type Comment Is PK
Rating_Agency_Pk CHAR(40) Unique identifier of the party Yes
Record_Start_Date DATE Start date of the record Yes
Rating_Agency_Des CHAR(80) Description of the Rating No
agency
Source_System VARCHAR2(20) Source system identifier No
Mis_Date_Pk DATE Extraction date No
Rating_Agency_Name CHAR(80) Name of the Rating Agency No
Rating_Agency_Type CHAR(40) Type of rating agency - Internal No
or external
Record_End_Date DATE End date of record No
Record_Status CHAR(18) No

Column(s) of "REGION" Table

Name Data type Comment Is PK
Region_Pk CHAR(40) Unique identifier of the region Yes
Record_Start_Date DATE Start date of record Yes
Record_Status CHAR(18) Status of the record - live, No
historical, etc.
Region_Des CHAR(50) Region description No
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE End date of record No

Column(s) of "REGION_COUNTRY" Table

Name Data type Comment Is PK
Region_Country_Pk CHAR(60) Unique identifier - Yes
Concatenation of Country_pk,
Region_pk
Record_Start_Date DATE Start date of record Yes
Country_Pk CHAR(18) Unique identifier for the country No
Region_Pk CHAR(40) Unique identifier of the region No
Source_System VARCHAR2(20) Source of the extract No
Mis_Date_Pk DATE Extraction date No
Record_End_Date DATE End date of record No
Record_Start_Date DATE Start date of the record No
Record_Start_Date DATE Start date of record No
Record_Status CHAR(18) Status of the record - live or No
history

TEMENOS T24 User Guide Page 35 of 88

Data Warehouse

Column(s) of "SECTOR" Table

Name Data type Comment Is PK
Sector_Pk VARCHAR2(15) Unique identifier of the sector Yes
Record_Status CHAR(18) Status of the record - live, No
historical, etc.
Record_Start_Date DATE Yes
Sector_Description CHAR(50) Description of the sector No
Parent_Sector_Pk CHAR(18) Sector code which it reports to No
Sector_Short_Name VARCHAR(50) Short name of the sector No
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Record_End_Date DATE No

Column(s) of "TIME_PERIOD_TYPE" Table

Name Data type Comment Is PK
Time_Period_Type_Pk CHAR(18) A code for a time period such as Yes
once, daily, weekly or monthly.
Record_Start_Date DATE Start date of record Yes
Record_Status CHAR(18) Status of the record - live, No
historical, etc.
Time_Period_Desc VARCHAR2(1000) Description of the time period No
code
Mis_Date_Pk DATE Extraction date No
Source_System VARCHAR2(20) Source of the extract No
Record_End_Date DATE End date of record No

Philosophy and Standards for the Data Warehouse Data Model

Centralising all banking arrangements
In T24, each application stores data in its own separate table – so for accounts there is an ACCOUNT
table, for loans or deposits there is the LD.LOANS.AND.DEPOSITS table. This is appropriate for a
transaction processing system such as T24. However, it is not optimal for an analysis database.
In the data warehouse, there will be an ARRANGEMENT table, where the highest level of information
regarding all bank arrangements with clients will be stored.
Bank arrangements for phase 1 will include financial contracts and portfolio management.
Note that the Data Warehouse is not a transaction processing system, it is a Data Analysis system.
As a result, data in T24 relating to how a contract is to be processed will not be stored – only financial
data, relating to what is currently in the system.

Managing Balances
For any one arrangement, there can be several balances : for example, for a loan, there can be the
initial balance, the outstanding balance, the interest payable on that balance, the unpaid balance in
grace and the unpaid overdue balances. For limits, there can be initial amount, outstanding amount,
utilised amount.
For portfolios, we can have balances of shares or bonds. Instead of a financial amount, we would
store a nominal amount, with reference to the share this amount relates to.
Each balance reflects a specific point in time. As the data warehouse is engineered to enable
complete and accurate historic reporting, we will sustain all balances as they change across time.
Therefore, if a balance changes on a different day, the previous balance will not be overwritten but a
new balance record will be generated. If however a balance is changed twice in one day, the more
recent balance will overwrite the previous balance as it is only necessary after COB to store a single
balance snapshot per day.

TEMENOS T24 User Guide Page 36 of 88

Data Warehouse

Categorisation of Tables in Data warehouse

All the tables in the Data Warehouse have been categorised into two types namely
a) Dimension tables which hold relatively static data which change slowly over time like
Customer, Sector, Industry, Product, etc.
b) Fact tables which hold the dynamic data which change daily like Account balance, number of
transactions, average balance, etc.

Entity

Entity
Name Type Definition
AGMT Fact Highest level table for each contract. This
holds information relating to all sustained
contracts in the database. The table holds
information which would not normally be
expected to change through the life of the
contract - however, if the contract does
change, a new record would be generated,
with the new details and a new
ARRANGEMENTCODE - while the
ARRANGEMENTID would remain the
same.
AGMT_AGMT_RELATIONSHIP Dimension This entity identifies relationships between
arrangements and identifies the type of
relationship. An example would be the
arrangements are related for collateral
purpose or one arrangement replaced
another arrangement due to a refinance.
There is one instance for each arrangement-
arrangement relationship.
AGMT_BAL_BOOK_DATE Fact
AGMT_BAL_POST_VALUE_BOOKING Fact
AGMT_BALANCE Fact This file holds the various balances related
to the arrangement.
Examples of balances - outstanding balance,
provision balance, interest
payable/recievable balance, overdue
balance/in grace balance etc. Outstanding
balance is likely to be the principal one
used. Each balance is distinguished by
Balance Type, which itself holds
information about the balance itself.
There will also be a rate at which the
balance is gaining interest/depreciating in
value.
There may also be a repricing/transfer price
rate applicable to this balance. There is
likely to be a counterpart BALANCE record
to the ARRANGEMENT_BALANCE for
interest details.
This will hold all data relating to the
financial balances of contracts. For
example, there will be information holding
the amount and currency of balances, along
with information detailing the rate that the
balances are accruing interest (if
applicable), or possibly depreciating in
value, and the future date that these rates
may change, the date that this balance is
closed (if known), as well as reference to
the parent Arrangement contract.
Every arrangement has at least one balance -
generally for the outstanding amount.

TEMENOS T24 User Guide Page 37 of 88

Data Warehouse

Entity
Name Type Definition
However, there may be other balances such
as interest payable/receivable, payments
past due/in grace/non accrual etc.
A maximum of one record per balance type
per contract per day is sustained in the
system. This has to be stored in order to
enable historic comparisons and reporting of
amounts. Therefore, whenever there is a
new balance on a new day, a new record
should be generated to reflect this.
It is debateable as to whether there should
be an 'enddate' for the balance, if one
balance (but not the contract overall) is due
to be 'closed' at a particular time.

AGMT_BALANCE_POST_VALUE Fact
AGMT_BALANCE_VALUE Fact
AGMT_PARTY Dimension This associative entity defines the
relationships that this arrangement has with
parties. For example, relationships include
arrangement holder, beneficiary, trustee, the
car dealer that originated the account, the
business or individual that funded the
deposit money, or another customer that
referred this customer to the bank.
AGMT_PARTY_ROLE Dimension It stores the description of various
arrangement party role types.
AGMT_PROFIT Fact This entity tracks the changes to an
account's profitability over time. This entity
has the account profitability that is
calculated outside of this system. There is
one instance for each account, date and
profit type combination.
This entity can be derived from other
entities in the model that have costs and
revenue associated with it.
AGMT_RELATIONSHIP Dimension This minor entity describes the type of
relationship between two arrangements such
as collateral, transfer, etc.
BRANCH Dimension This stores the information of the branch
from where the arrangement originated. It is
a type of business unit
COUNTRY Dimension It stores the details about the country along
with the region.
COUNTRY_CURRENCY Dimension This stores the relationship of the country
with the currency
CURRENCY Dimension It stores details about the currency
CURRENCY_CURRENCY_MARKET Dimension This stores the relation between the
currency market and the particular currency.
CURRENCY_MARKET Dimension This stores the description of the various
currency markets for a currency.
CURRENCY_RATE Fact It stores the currency rates along with the
history
EMPLOYEE_DEPT Dimension It holds the department to which an
employee belongs.
EMPLOYEE_PARTY Dimension It stores the details about the employee of
the organisation
EMPLOYEE_RELATION Dimension It stores the emplyee relationship with the
employer
EXTERNAL_IDENTIFICATION Dimension It stores the details of external identification
of a party like passport, driving license,
SSN, etc.
GENDER Dimension It stores the gender description

GL_ENTRY Fact It stores all the GL entries over time

GL_REPORTING_BALANCE Fact The entity that indicates the actual account
balance amounts for defined GL accounts.

TEMENOS T24 User Guide Page 38 of 88

Data Warehouse

Entity
Name Type Definition
GLOBAL_PARTY Dimension This stores the unique identifier of Global
party and the description
INDUSTRY Dimension It describes the industry details of the party
INTEREST_BASIS This stores the various basis of interest
Dimension calculation like A/a , A/360. etc along with
the description
INTEREST_BENCHMARK Fact It stores the benchmark rate for a particular
benchmark like LIBOR over time.
INTEREST_BENCHMARK_DES Dimension This entity represents an interest index . An
example of indices are bank prime rate, 3-
month bank CD, LIBOR 6-month etc.
Interest rates charged to customers are
based on these indexes.
JOB_TITLE Dimension It stores the job title of the individual.
LIMIT Fact This table stores all the limit details of an
arrangement.
LIMIT_BRANCH Fact
LIMIT_COLLATERAL Fact This stores the mapping of collateral with
the limit.
LIMIT_COLLATERAL_PERIOD Fact

LIMIT_COLLATERAL_RIGHT Fact
LIMIT_COMMODITY Fact
LIMIT_COUNTRY Fact This stores the the limit for each country in
the overall limit
LIMIT_CURRENCY Fact
LIMIT_GRADE Fact This stores the the limit for each country in
the overall limit
LIMIT_MATURITY Fact

LIMIT_PRODUCT Fact This stores the relationship of the Limit with

various products.
MIS_DATE Dimension It stores the calendar time date, week,
month, quarter, half year and year
OCCUPATION Dimension It stores the occupation of the individual.

PARTY Dimension A party is any individual or organization

that is of interest to the Bank.
PARTY_ADDRESS Dimension It stores the mail address of the customer or
party
PARTY_EMPLOYMENT Dimension
PARTY_LIMIT Fact It stores the total limit for the Party
PARTY_NAME Dimension It stores the name of the Party over time.
PARTY_PARTY_RELATION Dimension It stores the relationship between 2 parties
with the role code
PARTY_RATING Dimension It stores the rating of the Party over time.

PARTY_RELATION Dimension Types of relationships that parties can have

to each other.
Examples of relationships :

For party :
Sibling
Cohabit
Parent

For companies :
Head office
Subsidiary
Supplier
Partner

General :

TEMENOS T24 User Guide Page 39 of 88

Data Warehouse

Entity
Name Type Definition
Trustee
POA

PARTY_RESIDENCE Dimension It stores the residence details of the party

PARTY_SEGMENT Dimension The Segmentation Concept is central to the
Data Warehouse overall design. The Party
Segment consists of a logical grouping of
parties, which can be used as a standardised
grouping elsewhere.
Examples of segments could be :
Young Married High Earner
Young Unmarried High Earner etc etc
Or
Medium Sized Services Industry Privately
Owned Company
Medium Sized Information Technology
(Software) Public Listed Company
For this reason, it is not possible to detail
the attributes at this point - as they may be
set by the client. However, the attributes for
this table are likely to be dimensions upon
which the system is likely to be reported.
The rules for setting segmentation will be
stored as part of the extraction tool, so when
a party is entered its segmentation is
identified, and this is attached to the DW
table.

PARTY_STATUS Dimension This stores the status of the Party whether a

defaulter , bankrupt, etc
PRODUCT Dimension It stores the details about the Product
offered by the bank
PROT_ASSET_RIGHT Dimension
PROT_ASSET_RIGHT_BRANCH Dimension
PROT_ASSET_RIGHT_LIMIT Dimension
PROT_ASSET_RIGHT_PARTY Dimension
PROTECTION_ASSET Fact This entity identifies all the known assets of
Parties. These also include equipment or
vehicles being leased by the financial
institution. There is one instance for each
asset. An asset could be any item that is of
value to the Party The term 'asset' is from
the customer's perspective.
PROTECTION_ASSET_GROUP Dimension
PROTECTION_ASSET_TYPE Dimension
RATING Dimension It stores the description of a Rating of
agency like S & Pwhich is a type of Party
for the Bank
RATING_AGENCY Dimension

REGION Dimension This stores the region details for the country
REGION_COUNTRY Dimension It stores the relation between a Country and
a particular region like Asia, Europe,
EMEA, etc.
SECTOR Dimension It stores the sector description of the
customer
TIME_PERIOD_TYPE Dimension It stores the description of time period type
like daily, weekly, monthly, etc.

TEMENOS T24 User Guide Page 40 of 88

Data Warehouse

TEMENOS T24 User Guide Page 41 of 88

Data Warehouse

Managing Static Change / Slowly Changing Dimensions

As part of the principle of complete historic reporting, the Data Warehouse Data Model will be
designed to handle the gradual changing of significant attributes over time, while keeping historic
details for accurate back-dated reporting.
For example, if a party changes from one residential address to another midway through the year, it is
important that all data relating to that party for the first half of the year uses data relating to their first
address, while all financial results from the second half of the year uses data from the second half.
To enable this, it is necessary to generate a new party record whenever a significant attribute changes
on that party’s profile. A start date for the new party record is established, with possibly also an end
date for the previous record. All arrangements relating to that party are also switched on that date to
refer to the new party record. In order to be able to maintain party continuity however, the same
identifier field is maintained between the two records.
This principle of maintaining historic settings can be applied to all tables holding measurable attributes.

The data model of the Party and its related tables are shown above.

Maintenance of Reference Data

Data such as currency rates, bond/share closure rates and even interest rates change daily. In order
to enable accurate back-valued valuations, calculations and comparisons, all reference data of this
type will be stored daily, so that for example we could work out the valuation of 10 Samsung shares on
1st January 2005 by referencing the closing rate for Samsung shares on that day, and the closing
valuation of Korean Wong on January 1st.
Reference tables to be maintained :
CURRENCY
INTEREST.KEY
Note : The only fields to be sustained in these tables would be those which change daily (@ID, rate)
Example : Currency
Field Name Value
@ID GBP-20060101
MID_RATE 1.85

TEMENOS T24 User Guide Page 42 of 88

Data Warehouse

Standardisation of Segmentation across tables

In order to group tables into equal levels of granularity, we use the concept of segmentation.
Examples of segmentation are:
• Party Segmentation: grouping parties into different ‘types’.
• Product Segmentation : Grouping products
For example, we could define parties into the following segmentation groups:
• Young Unmarried Low earner
• Young Unmarried High Earner
• Young Married Low Earner
• Young Married High Earner etc etc.
This segmentation is clearly based on 3 attributes (age bracket, marital status, income bracket). Once
the segments have been defined, they can be defined in all applicable tables: so the PL Balances
table, which may be consolidated by party segment, would use these segmentation rules. In addition,
the Party table would also use these rules to provide standard grouping for parties,
One important item to note is that segmentation should not cross tables – product segmentation
relates purely to data relevant to that product, party segmentation purely to details about the party.
An additional note, which may be for phase 2 but which would be extremely useful for regulatory
reporting, is the development of parallel segments. In this way, a party may fit into one type of
segment for one method of reporting (Young, Unmarried Low Earner) and an entirely different
segment for a different method of reporting (Individual, I.T Graduate, Inner London). Obviously, when
items are consolidated by segment there would need to be parallel balances tables to handle each
segment, or the consolidation would need to occur at a yet lower level with references to the segments
which are applicable.

Difference between segments & hierachies within static attributes

Hierarchies within static attributes are all entirely self contained: therefore, the hierarchy implied within
Account Officer does not need to refer to any other tables.
There is no variable logic within one record to determine which hierarchy it fits into (its ‘parent’) – a
Employee member is simply assigned to a manager, a company industry is assigned to a wider
industry group. Everything is manually specified and assigned.
Segments however are calculated as a result of a number of fields on a single table – for example, the
party segment can be calculated from groupings of sectors, industries etc – in fact, it is likely to be
high-level sectors, industries etc. The segments themselves can then have hierarchies, which would
be manually defined like hierarchies within static attributes.
Segments are likely to be dynamic and changeable – therefore, it is likely that there will need to be a
tool to recalculate and reapply segmentation to static data.

Updates & Calculations within the Data Warehouse

It is essential that data within the data warehouse and within T24 should reconcile. Furthermore, it
would be pointless to replicate calculations and processing done within T24 also within the data
warehouse. Therefore the overall approach should be to ensure that the Data Warehouse sustains
data, but does not calculate it itself.
For example, average balance amounts could be calculated from daily Arrangement balances over
time. However, this has already been calculated through T24 to leave the average balance amount.
Therefore, the T24 amount will be migrated, rather than the amount be calculated. Similarly for

TEMENOS T24 User Guide Page 43 of 88

Data Warehouse

collateral amounts, when based on T24 contracts: these will still be extracted from T24, even if they
could be derived from the DW data.
Of course, it is possible that some balances may be influenced by contracts outside of T24. In this
case, additional processing would be required – however, we are currently building a self-reliant T24
Data Warehouse, local configuration when combining with other systems is inevitable.

Profitability
Profitability calculations present some of the more complicated and important challenges to the data
model.
In T24, each Profit/Loss Posting has its own entry in the CATEG.ENTRY file. However, it is
anticipated that were these postings to be migrated across to the data warehouse, the potential
volume of transactions would cause prohibitive performance issues. Therefore, it is proposed that all
PL postings from the OLTP (Online Transaction Processor – in most cases, T24) are consolidated into
PL Balances files.
Consolidation can be based on any variables within the profitability sphere. However, it is anticipated
that the main sources of consolidation will be :
Party or Party Segment (at various levels of party segmentation)
Arrangement, or Arrangement Segmentation
Entry Type, or Entry Type Segmentation
Daily, or through Date Segmentation (periods).
The level of consolidation for these postings can be within the control of the client. It may be that
consolidation is placed at the party and arrangement level, totalling by individual PL type and stored
daily : this would produce the highest volume of balances, stored at the lowest level. This would be
appropriate for a bank with relatively low volumes of transactions.
However, a higher volume retail client may wish to total all PL entries by a higher level of party,
arrangement and pl segmentation and for these entries to be stored on a weekly or even monthly
basis. This would produce a lower volume of balances.
A further possibility is that the bank may choose to consolidate some entries at the lowest level, and
others at a higher level (e.g all high net worth clients would have pl entries consolidated by customer,
whereas low net worth clients would have entries consolidated by various entry levels).
Profitability will then be calculated by grouping balances, making adjustments from the pl adjustments
table and taking into account activites from the events file.

Programming Requirements
To ensure that the DW solution is platform-independent, and also to make maximum use of our
existing expertise (i.e. in T24 and Infobasic) wherever possible, the maximum amount of work has
been performed in T24, and the minimal amount within the data store. However, even with this in mind,
some calculations may be performed on the data warehouse.

Records with changing dimensions

When a new record for a customer / account officer is entered, for easy/efficient reporting the ‘old’
record will be updated with a ‘close date’. As with balances, any subsequent records will also include
the update to this record.

TEMENOS T24 User Guide Page 44 of 88

Data Warehouse

MIS reporting
Profitability Analysis
Phase 1 of DW also delivers Profitability Analysis, the first of a set of business intelligence applications
– others will follow in later stages.

DW will provide equivalent reporting functionality to the existing T24 Management Information module,
through reports generated from the external Data Store. The current Management Information module
provides information including income and expense, average balances, transaction counts, etcetera
about customers, products, departments from which Management may analyse profitability and
performance.

Elements within the MI module regarded as efficient will be reused, while those areas that require
improvement will be replaced with external best-practice software. Overall, the analytical potential of
the new environment will considerably exceed that possible today with the Management Information
module. The figure below illustrates the functionality currently delivered by MI, and how this
functionality will now be replicated through DW.

MI Element DW Equivalent

PL Entries / Balances In phase 1, profitability will be based on the use of individual

CATEG.ENTRY, files, which will be migrated to the external data
store. In phase 2, consolidations of balances along
party/product/department lines may be built. However, for phase 1,
the CATEG.ENTRY file is exported & all P&L is calculated from this
by the generation of cubes.

TEMENOS T24 User Guide Page 45 of 88

Data Warehouse

PL Reallocation Entries P&L Adjustments can be calculated online using features of the
ETL product, along with manual adjustments using
MI.AUTO.ENTRY entries – so MI.AUTO.ENTRY is generated as
before, and exported to the DW, for both Transfer Pricing entries
and PL Reallocations. However, this functionality will need to be
further enhanced to run online / through services rather than only
at the building of the MI cube as initially delivered.

Average Balances As specified in the profitability report, Average Balances are

calculated using Balance Movement of MI module.

MI Data Cubes T24 Functionality is not used for the generation of data cubes.
Data Cubes are generated using the OLAP reporting tool.

With data stored on an external data store, it will be possible to provide all of the reports currently
delivered by the MI module. In addition, performance issues related to the generation of MI Data
Cubes and Balance Movement records will be removed. The process will be more transparent to
Users, who will be able to take greater ownership of the MI process.

TEMENOS T24 User Guide Page 46 of 88

Data Warehouse

Setup
The Data Warehouse T24 Code and configured tables are supplied to T24 in the same way as all
other modules, through addition of the DW product code to the SPF through the standard installation
procedures. As with all T24 modules, once the functionality has been installed, all unauthorised
records relating to DW need to be authorised.

jBASE Release
As the DW Functionality makes use of new jBASE functionality to enable the automatic population of
alternative tables and databases, the minimal release upon which the installation of DW can be done
is 4.1.5.11.

T24 ETL Architecture

ETL means Extract, Transform, and Load. ETL is a process in data warehousing that involves
o Extracting data from outside sources,
o Transforming it to fit business needs (which can include quality levels), and ultimately
o Loading it into the end target, i.e. the data warehouse.
In the below sections we would see the architecture of T24 ETL.

Online Services
Automated Extraction of Table Data
The data to be extracted from T24 to the data warehouse is controlled via the DW.EXTRACT.PARAM
table.
The DW.EXTRACT.PARAM table specifies which T24 tables will have some or all of their data
extracted into the data warehouse

Selecting tables to populate the data warehouse

Once the module DW has been installed, the DW.EXTRACT.PARAM table must be populated to
specify each table which is ‘of interest’ to the data warehouse. At this stage we are only specifying
which T24 tables contain data which will be required by the data warehouse, we are not specifying
which data fields within the T24 table are required. The details of which fields are to be extracted, is
discussed in the section 2.4. below:
The layout of the DW.EXTRACT.PARAM table is as follows;
ƒ Field Name ƒ Type ƒ Description
Record @ID 35,A @ID of T24 Application which may be extracted from T24 to the
Data Warehouse. Must be a valid application
Description Description of table
Rules Routine This is a standard EB.API routine to which the whole of
extracted record, will be passed in VM/FM format along with the
@ID. The routine can decide, based on any field’s value, to
determine whether each record is of interest to Data
Warehouse.
If the routine decides not to proceed with the extract then it
needs to send a return value of 1 in the fourth argument.

TEMENOS T24 User Guide Page 47 of 88

Data Warehouse

( **** Phase 2 Enhancement****)

This field will be linked to standard T24 Rules mechanism to
determine whether each record is of interest to Data
Warehouse.

Shown below is a typical DW.EXTRACT.PARAM setup for the T24 application Customer with Rules
routine:

Rules Routine
This is a standard EB.API routine to which the whole of extracted record will be passed in VM/FM format along
with the @ID. The routine can decide based on any of the field’s value to determine whether each record is of
interest to Data Warehouse or not.
If the routine decides not to proceed with the extract then it needs to send a return value of 1 in the fourth
argument.

TEMENOS T24 User Guide Page 48 of 88

Data Warehouse

The field Rules.Routine is a non-mandatory field. If a routine is attached then above said processing will happen
else the record will be extracted from T24 unconditionally.

Shown below is a typical EB.API setup for a Rules routine:

TEMENOS T24 User Guide Page 49 of 88

Data Warehouse

Shown below is a sample Rules routine written in Info-Basic

TEMENOS T24 User Guide Page 50 of 88

Data Warehouse

XML Schema generation in T24

The process of XML schema generation of the T24 tables is enclosed in the XSD Schema Generation
User Guide. Please download this document also as it is essential to Data Warehouse.

T24 Transformation Tables

EB.DDL.SCHEMA
This is a T24 Table to produce the XML schema for a Data Warehouse table (for mapping purpose) from its DDL
(i.e. CREATE TABLE).
@ID is the name of the external Data Warehouse table. To start with, only “CREATE” type DDL is allowed and
cannot be created without a table name. The number of opening brackets should be the same as the number of
closing brackets. This is a ‘W’ type template. The DDL of Data Warehouse table needs to be copied to the field
DDL and on verification the XML schema would be generated by T24 and placed on the field SCHEMA. The field
TABLE.TYPE needs to hold information whether the table is a Dimension or Fact. If left NULL, then it is
understood as Dimension table.

The layout of the EB.DDL.SCHEMA table is as follows;

TEMENOS T24 User Guide Page 51 of 88

Data Warehouse

On verification, the XML schema is produced in the field Schema

We could write an Enquiry on this table and export the Schema’s for mapping purposes.

TEMENOS T24 User Guide Page 52 of 88

Data Warehouse

1..1 EB.TRANSFORM
This table holds the generic mapping XSL for mapping from T24 table to a DW table.
@ID syntax: <Valid T24 Table name>-<UPDATE/DELETE>. If the XSL contains the mapping logic for
the table update (input/edit) then the ID should be <Table name>-UPDATE. If it contains the mapping
logic for delete, then the ID should be <Table name>-DELETE. The field “Mapping.xsl” stores the
mapping XSL for Update/Delete. It is left to the user to input a valid and meaningful XSL.

T24 DW Loading Table

DW.CONNECT.PARAM
This table is used to hold database connection details. It is similar to a Parameter record to store the
database connection details.
@ID should be “SYSTEM”.

TEMENOS T24 User Guide Page 53 of 88

Data Warehouse

All the fields are mandatory in this table.

Values in the fields “PASSWORD” and “REPEAT.PASSWORD” should be the same; it does not
accept NULL as a valid password as it has the validation of minimum 5 characters and maximum 15
characters.
The connection parameters defined are read from this table and are embedded with SQL.

T24-DW mapping mechanism

The existing T24 data needs to be mapped in a relational format so that it can be stored in a relational
database which is currently Oracle. Mapping the T24 data is achieved by the following means:
• XML schema of T24 table (source table) to be mapped is taken from Standard Selection
• The destination table data model or DDL is produced in ERWIN and the corresponding XML
schema is produced using EB.DDL.SCHEMA in T24. It should be kept in mind that while
producing DDL, the data type and length needs be done after considering the data dictionary
in Standard Selection in T24.
• Thereafter both the schemas are inserted in Altova Mapforce as shown below and the
mapping is done manually using Mapforce features
• In case of multivalues, the mulitvalue fields are populated in another table with the Primary
key being a concatenation of the Parent table Primary Key in T24 and the first multivalue field.
Similarly a sub value fields are populated in another table with the Primary key being a
concatenation of the Parent table Primary Key in T24 , the first multivalue field and the first
field in the sub-value
• In case of concatenation a ‘*’ is used to differentiate the various concatenated fields.

TEMENOS T24 User Guide Page 54 of 88

Data Warehouse

• After physical mapping in Mapforce, the XSL or the stylesheet of the mapping is generated
using the XSLT tab in Mapforce.
• The stylesheet is subsequently integrated with other stylesheets, if required and is pasted in
EB.TRANSFORM of T24. This style sheet is referred by the T24 XML record to produce the
corresponding DW XML record which is subsequently turned to a SQL record using
DW.TRANSFORM

Creation of DW schema
The user needs to go to EB.DDL.SCHEMA and type the name of the table which one wants
to map in DW (data warehouse) and the press Edit tab.

The User needs to type the short description and description of the DW table schema and then copy
the corresponding DDL of the DW table into DDL and commit the deal. The user needs to verify the
deal subsequently.
The DW schema subsequently produced needs to be copied in notepad and stored in a file.

TEMENOS T24 User Guide Page 55 of 88

Data Warehouse

Mapping using ALTOVA

The user needs to install Altova Mapforce (professional 2008) in order to do this mapping. Once
Altova is installed and opened then the XML schemas of T24 table and the corresponding DW table/s
need to be inserted as shown in the figure below. The mapping is done by dragging the arrow from the
T24 attribute to the corresponding DW attribute. There are other Altova features which are used like
Concatenation, If clause, Boolean, etc.
The Altova mapforce user guide is enclosed for reference.

Obtain T24 table and DW table XML schema as shown below

TEMENOS T24 User Guide Page 56 of 88

Data Warehouse

Mapping the source and destination tables by dragging the cursor and using Altova features

TEMENOS T24 User Guide Page 57 of 88

Data Warehouse

Mapping T24 multivalues in Data Warehouse

TEMENOS T24 User Guide Page 58 of 88

Data Warehouse

The stylesheet is produced by pressing the XSLT tab in Altova as shown below:

TEMENOS T24 User Guide Page 59 of 88

Data Warehouse

Integrating the Stylesheets:

When a T24 table is mapped to several DW tables, we might be required to integrate multiple mapping
stylesheets (XSL) into one. The table EB.TRANSFORM can hold only a single integrated style sheet
(XSL)
Shown below an example of how to Integrate the stylesheets
Step 1: In the example, we see a mapping between the T24 table Customer to two Data Warehousing
tables PARTY and PARTY_RATING using Altova Mapforce.

TEMENOS T24 User Guide Page 60 of 88

Data Warehouse

Step 2: Click the Preview button on any one of the tables (i.e. based on the order of the DW tables in
which the INSERT/UPDATE needs happen)

Step 3: Now, view the XSL by clicking the XSLT tab at the bottom of the Mapping window (make sure
that the Preview button of the DW table is Pressed as shown)

Step 4: On the XSLT Tab, right-click and click Select All and then Copy to copy the XSL

TEMENOS T24 User Guide Page 61 of 88

Data Warehouse

Step 5: Paste the copied XSL in an Editor (notepad or preferably a XML editor)
• We could remove any unwanted comment or/and the XSD references.
• Make sure the Slash (/) before the element value “row” is removed.

TEMENOS T24 User Guide Page 62 of 88

Data Warehouse

Step 6: Repeat Step 2 – Step 3 for the other DW table PARTY_RATING.

• Make sure that we select and copy only a portion of the XSL within the tag <ROOT> and its
closing tag </ROOT>.
• We could do away with the namespace information.

TEMENOS T24 User Guide Page 63 of 88

Data Warehouse

Step 7: Integrating the XSL’s.

Paste the copied XSL of the PARTY_RATING mapping into the Editor (prepared in Step 5) at the end
of PARTY mapping such that the tag <ROOT> & </ROOT> remains as the parent/root tag for the
entire mapping XSL.

We could validate this XSL for correctness, if we are using a XML Editor.
The same could be repeated for mapping containing more two DW tables and a single Integrated XSL
could be produced.

TEMENOS T24 User Guide Page 64 of 88

Data Warehouse

Copy style sheet in T24

This stylesheet from Altova needs to be copied in EB.TRANSFORM (mapping XsL) in T24 after
naming it properly. The transaction needs to be committed and authorised subsequently.

Virtual fields in Standard Selection

The T24 Table schema produced while authorising the Standard Selection contains only the “D” data
fields in the application. In-case, we need few Lookup fields for mapping purposes, we can make use
of a new field Type called “V”-Virtual in Standard Selection. While the standard T24 schema (just with
the Data fields) will still be produced with the ID <T24 Table name>.xsd, the new schema including the
Lookup (Virtual fields) will be produced with the ID <T24 Table name>_EXTENDED.xsd in the file
F.SCHEMA. Then this new EXTENDED schema can be used in Altova Mapforce for mapping.
The newly introduced SYS.TYPE = V, SYS.FIELD.NO can accept any of the following:
ƒ A valid I/J Desc field name
ƒ A user defined sub routine and pass any hard-coded values or / and value of “I/J”
type field or @ID field of the table as argument.
ƒ A local reference field defined in the application.

TEMENOS T24 User Guide Page 65 of 88

Data Warehouse

I Descriptor field.

Subroutine

Subroutine

JDescriptor field.

Local Ref Field.

External Database Population

The population of external an external database is managed through the procedure of calling an
external .jar file, which will ‘push’ the predefined data to an external location.
Further to this, there are five .jar files needed to connect to an external database. These are :
• jaxen-1.1.1.jar
• junit-4.1.jar
• dom4j-1.6.1.jar
• t24Utils.200805.jar
• odbc14.jar
These can be downloaded from the secure Temenos ftp server, under the DW folder. Contact
Temenos distribution for the IP address and your login / password.

TEMENOS T24 User Guide Page 66 of 88

Data Warehouse

There is a pre-requisite of java version 1.4 for these jars.

All these jars need to be added to the CLASSPATH in order to be executed. It is suggested that you
create a directory in the .run directory named “jars” and that the .profile or remote.cmd is amended to
include these two items.
The appropriate jar should be downloaded to fit the type of RDBMS which is being populated.
Under UNIX:
export CLASSPATH=$CLASSPATH:$RELIB/t24Utils.200805.jar
export CLASSPATH=$CLASSPATH:$RELIB/dom4j-1.6.1.jar
export CLASSPATH=$CLASSPATH:$RELIB/jaxen-1.1.1.jar
export CLASSPATH=$CLASSPATH:$RELIB/odbc14.jar
export CLASSPATH=$CLASSPATH:$RELIB/junit-4.1.jar
Example

Under WINDOWS:

set CLASSPATH= %CLASSPATH%: %HOME%\jars\ t24Utils.200805.jar

set CLASSPATH = %CLASSPATH%: %HOME%\jars\ dom4j-1.6.1.jar
set CLASSPATH = %CLASSPATH%: %HOME%\jars\ jaxen-1.1.1.jar
set CLASSPATH = %CLASSPATH%: %HOME%\jars\ junit-4.1.jar

TEMENOS T24 User Guide Page 67 of 88

Data Warehouse

set CLASSPATH = %CLASSPATH%: %HOME%\jars\ odbc14.jar

Running the ETL process

The actual data ETL process will be performed using the standard T24 Services functionality.

TSA.SERVICE record
The TSA Service record DW.EXTRACT is released from the core. This service record gets released
either as <XYZ>/DW.EXTRACT or DW.EXTRACT depending upon whether the set-up is Multi-
company or not (where XYZ represents the company mnemonic). Before authorizing the record, the
SERVICE.CONTROL field needs to be changed as AUTO and it needs to be linked to a valid TSA
Workload profile. Also the corresponding F.BATCH record (DW.EXTRACT or <XYZ>/DW.EXTRACT)
should be authorised.

TSA.WORKLOAD.PROFILE
TSA.WORKLOAD.PROFILE record for this task must be created with as many agents as required,
and linked to the Service record DW.EXTRACT. For more details on setting up TSA services please
refer to the Technical User Guide.

DW API records
The T24 DW module has three extension points i.e. ETL.
• Firstly, the extract routine that is called once the data extract has been performed (E).
• The second is used to transform the contents to suitable for loading into the Data
Warehouse(T)
• Third, to determine the database connectivity parameters, and the actually load the data into
an external database(L)
In the above-said ETL, the Loading is implemented via Java API.

TEMENOS T24 User Guide Page 68 of 88

Data Warehouse

Close of Business Services.

DW Bulk loading
Enhancement has been done to enable the migration of bulk data to the external data store, as an
alternative to online updates triggered.

DW.BULK.EXTRACT
This is the table in which we specify the T24 table(s) from which records need to be extracted and
populated in an external data store.

Field Name Type Description

@ID 35,A Can be any value
XX. < T24 Table Any record from STANDARD.SELECTION – any
T24 Table
XX.XX It may be that only a selection of the T24 table
FIELD.SELECTION should be migrated (records after a certain date, for
example). Therefore, a sub value set of
FIELD.SELECTION, SELECTION.OPERAND, and
SELECTION.CRITERIA may be used to work out
which tables should be extracted. An alternative to
this sub value is the EXTRACTION.RULE which
would otherwise be used to determine if the record
should be extracted.
XX.XX FIELD.SELECTION and SELECTION.CRITERIA
SELECTION.OPERAND determines which records in the T24.TABLE should
be extracted in bulk load.
XX.XX FIELD.SELECTION and SELECTION.OPERAND
SELECTION.CRITERIA determines which records from T24.Table should be
extracted.
XX. > EXTRACT.REG As an alternative to the FIELD.SELECTION –
SELECTION.CRITERIA sub value set, a REG record
incorporated with RULE can be specified to
determine which records should be extracted
XX.COMPANY.CODE This field holds the ID of the Company record. This is to
specify from which company the data to be extracted for
non-INT files.
STATUS Defines the status of DW.BULK.EXTRACT record:
• 1) Can be PENDING, RUNNING or MIGRATED.
• 2) Record can't be edited when it is in RUNNING
status.
• 3) Record in PENDING and MIGRATED status
can be edited and re-used

TSA.SERVICE record
The TSA Service record DW.BULK.PROCESSING is released from the core. This service record gets
released either as <XYZ>/DW.BULK.PROCESSING or DW.BULK.PROCESSING depending upon

TEMENOS T24 User Guide Page 69 of 88

Data Warehouse

whether the set-up is Multi-company or not (where XYZ represents the company mnemonic). Before
authorizing the record, the SERVICE.CONTROL field needs to be changed as AUTO and it needs to
be linked to a valid TSA Workload profile. Also the corresponding F.BATCH record
(DW.BULK.PROCESSING or <XYZ>/ DW.BULK.PROCESSING) should be authorised.

TSA.WORKLOAD.PROFILE
TSA.WORKLOAD.PROFILE record for this task must be created with as many agents as required,
and linked to the Service record DW.BULK.PROCESSING. For more details on setting up TSA
services please refer to the Technical User Guide

Note: For DW Bulk Extract/Upload the Service <XYZ>/DW.EXTRACT should continue to run in
addition to <XYZ>/DW.BULK.PROCESSING.

Overview of Input and Processing

The selection fields, “Fld.Selection”, “Selection.Op.” and “Selection.Cr” are optional. (Figure1)
The “Extract.Reg” field is also optional and an alternative to the selection fields.
It accepts only a valid REG record as illustrated in Figures 2 and 3.
If both the selection fields and the REG are used, the selection fields serve as pre-select criteria.

Figure.1

TEMENOS T24 User Guide Page 70 of 88

Data Warehouse

Figure.2

Figure.3

TEMENOS T24 User Guide Page 71 of 88

Data Warehouse

DW.BULK.EXTRACT: After verification and running – MIGRATED Status:

Figure.4

SERVICES TO BE RUN: (Figures 6 and 7)

DW.BULK.PROCESSING:

Figure.5

TEMENOS T24 User Guide Page 72 of 88

Data Warehouse

DW.EXTRACT:

Figure.6
DW.BULK.EXTRACT: After the services DW.BULK.PROCESSING and DW.EXTRACT are
started;

Figure.7

TEMENOS T24 User Guide Page 73 of 88

Data Warehouse

Error Messages
ETL error logging facility
The existing T24 DW ETL system is enhanced to log the errors in the ETL stage. There
could be various errors happening in these three layers of Extraction, Transformation &
Loading and it becomes essential to log the errors as when it occurs. These error logs could
be manually analyzed/reconciled, errors corrected and ETL process could be re-tried. The
errors are logged with sufficient information like Date, Time, Error Stage, Routine name,
Application, Module, File Name, Record Id and the most important the Error message

EB.LOGGING.PARAMETER
This is the parameter file for the log. This parameter will be keyed in by Context.id and will contain two
fields called LOG.LEVEL & LOG.METHOD for DW.EXTRACT, DW.TRANSFORM and DW.LOAD.

TEMENOS T24 User Guide Page 74 of 88

Data Warehouse

LOG LEVEL
1. Info: Includes INFO (information about the ETL), ERROR and FATAL errors.
2. Error: Includes ERROR and FATAL errors.
3. Fatal: Includes only FATAL error.

LOG METHOD
1. Always: Logs all messages even when the ETL crashes via FATAL .ERROR.
2. Console: Prints the output in the service and does not log in EB.LOGGING.
3. Disk: All logs are written to EB.LOGGING under normal circumstances. Does not log when
the ETL crashes via the T24 subroutine FATAL.ERROR.

TEMENOS T24 User Guide Page 75 of 88

Data Warehouse

ENQUIRY DW.RECONCILE.PARAM
A new enquiry has been written on F.EB.LOGGING, which takes in the select criteria’s and produces
a report like Enquiry on the error log.

The output is split date-wise and contains the count of INFO, ERROR and FATAL logs.

TEMENOS T24 User Guide Page 76 of 88

Data Warehouse

TEMENOS T24 User Guide Page 77 of 88

Data Warehouse

Interfaces
Altova Mapforce Mapping Guide
Please visit the below link for using Altova Mapforce for mapping T24 table(s) to DW (Data
Warehouse) table(s).

www.altova.com/download/2006/MapForcePro.pdf

Using ERWIN to build the Data Model

Building a table
The user needs to click the entity icon to build a table with ERWIN in the logical mode as shown in the
figure below and name the table along with its definition by right clicking the table icon and selecting
‘Entity Properties’ . Thereafter the user needs to type the attributes for the table and define the data
type, primary key and definition of the attributes in the table after choosing the ‘Attributes’ .

TEMENOS T24 User Guide Page 78 of 88

Data Warehouse

Selecting Entity Properties after right clicking the icon

Definition of the Table

TEMENOS T24 User Guide Page 79 of 88

Data Warehouse

Selecting Attributes after right clicking the icon

Definition of the Party_pk attribute in the table after selecting ‘New’

TEMENOS T24 User Guide Page 80 of 88

Data Warehouse

Definition of the Primary key of the Party Table by clicking the ‘Primary key’

Definition of the attribute by clicking ‘Definition’ tab

TEMENOS T24 User Guide Page 81 of 88

Data Warehouse

Definition of the attribute by clicking ‘Definition’ tab

Identifying Relationship in Data Model

An identifying relationship is a relationship between two entities in which an instance of a child entity is
identified through its association with a parent entity, which means the child entity is dependent on the
parent entity for its identify and cannot exist without it. In an identifying relationship, one instance of
the parent entity is related to multiple instances of the child.
In IE notation, AllFusion ERwin DM (Data model) draws an identifying relationship line as a solid line
with crows feet.
1. In the logical model, click on the the identifying relationship icon AllFusion ERwin DM toolbox.
The cursor changes to the identifying relationship..
2. Click the parent entity, then click the child entity.

TEMENOS T24 User Guide Page 82 of 88

Data Warehouse

One can create an identifying relationship when you add a relationship using the Relationships dialog,
on the diagram window by clicking the Identifying relationship icon. AllFusion ERwin DM automatically
migrates primary key attributes from a parent entity to a child entity, so you do not need to enter any
foreign keys in the child.
Tip: You can click a relationship line to highlight it. Highlighting allows you to trace the path of the
relationship line from parent to child.

Non-Identifying Relationship in Data Model

A non-identifying relationship is a relationship between two entities in which an instance of the child
entity is not identified through its association with a parent entity, which means the child entity is not
dependent on the parent entity for its identify and can exist without it. In a non-identifying relationship,
one instance of the parent entity is related to multiple instances of the child.
With AllFusion ERwin DM, you can create the following types of non-identifying relationships:
a) Optional non-identifying relationship
b) Mandatory non-identifying relationship
In an optional non-identifying relationship, the attributes that are migrated into the non-key area of the
child entity are not required in the child entity. Therefore, nulls are allowed in the foreign key. More
You can set the default null option in the Model Properties dialog. If you assign a non-identifying
relationship, AllFusion ERwin DM automatically assigns either a mandatory or optional non-identifying
relationship based on your default null setting. However, you can change the null option in the
Relationships dialog to override the default setting.
AllFusion ERwin DM draws an optional non-identifying relationship differently depending on the
notation for your model:
IE notation draws a dashed line with a cross and a circle on the parent end. The child end indicates
the cardinality with:

TEMENOS T24 User Guide Page 83 of 88

Data Warehouse

a) crows feet with a cross and a circle, as shown below (Zero, one or more)
b) crows feet with a cross (One or more)
In a mandatory non-identifying relationship, the attributes that are migrated into the non-key area of
the child entity are required in the child entity. Therefore, the foreign key cannot be null.
One can set the default null option in the Model Properties dialog. If you assign a non-identifying
relationship, AllFusion ERwin DM automatically assigns either a mandatory or optional non-identifying
relationship based on your default null setting. However, you can change the null option in the
Relationships dialog to override the default setting.
AllFusion ERwin DM draws a mandatory non-identifying relationship differently depending on the
notation for your model:
IE notation draws a dashed line with a cross on the parent end. The child end indicates the
cardinality with:

a) crows feet with a cross and a circle, as shown below (Zero, one or more)
b ) crows feet with a cross (One or more)

To define the non-identifying relationship:

1. On the Model menu, choose Model Properties.
2. Click the Defaults tab.
3. In the Default Non-Identifying Relationship Null Option group box, choose one of the following:
i Click Not Null to make the default non-identifying relationship a mandatory
relationship.
ii Click Null to make the default non-identifying relationship an optional relationship.
4. Click OK.

TEMENOS T24 User Guide Page 84 of 88

Data Warehouse

One can change the null option in the Relationships dialog to override the default setting.

Generation of the DDL

The generation of the DDL involves the following steps:
a) Select the main subject area by clicking the subject area tab
b) Thereafter select the ‘Tools’ tab on the mail menu and subsequently select ‘Forward
Engineer’ and ‘Schema Generation’ and the following screen pops up.

c) Click ‘Preview’ tab for the generation of the DDL as shown below:

TEMENOS T24 User Guide Page 85 of 88

Data Warehouse

Refer to the ERWIN user guide for further details on using ERWIN for data modelling.

Creating the Data model in Database (Oracle)

Copy DDL from T24
The user needs to copy the DDL of the relevant table for the database from EB.DDL.SCHEMA as
shown below:

TEMENOS T24 User Guide Page 86 of 88

Data Warehouse

The user thereafter needs to paste this DDL in the database like Oracle as shown below and execute
it to create the relevant table in database.

TEMENOS T24 User Guide Page 87 of 88

Data Warehouse

Glossary
DW Data Warehouse/Data Warehousing
ETL Extract, Transform and Load
XSL Extensible Stylesheet Language
XSLT Extensible Stylesheet Language Transformations
XML Schema(XSD) XML Schema Definition
XML Extensible Markup Language
PL Profit and loss
MI Management Information
GL General Ledger
DDL Data Definition Language

TEMENOS T24 User Guide Page 88 of 88