Oracle® Financial Services Asset Liability Management Analytics

250 Pages·2008·7.7 MB·English

Oracle® Financial Services Asset Liability
Management Analytics
User Guide
Release 6
Part No. E35679-01
August 2014
Oracle Financial Services Asset Liability Management Analytics User Guide, Release 6
Part No. E35679-01
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Primary Author:     Aneesh Kurian, Anuradha Muralidharan
Contributor:     Chris Spofford, Brad Bruckschen, Syed Sajjad Hussain
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of
their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are
used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron,
the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro
Devices. UNIX is a registered trademark of The Open Group.
This software and related documentation are provided under a license agreement containing restrictions on
use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your
license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,
transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse
engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is
prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If
you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on
behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software,
any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are
"commercial computer software" pursuant to the applicable Federal Acquisition Regulation and
agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation
of the programs, including any operating system, integrated software, any programs installed on the
hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the
programs. No other rights are granted to the U.S. Government.
This software or hardware is developed for general use in a variety of information management applications.
It is not developed or intended for use in any inherently dangerous applications, including applications that
may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you
shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its
safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this
software or hardware in dangerous applications.
This software or hardware and documentation may provide access to or information on content, products,
and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly
disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle
Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your
access to or use of third-party content, products, or services.
Contents
Send Us Your Comments
Preface
1 Introduction to ALM BI
FeaturesandComponentsofALMBI...................................................................................... 1-1
2 Overview of the ALM BI Process Flow
StepsinALMBIProcessFlow..................................................................................................2-1
EndtoEndALMBIDataMovementProcess...........................................................................2-2
3 Dimension Population
OverviewofDimensionPopulation.........................................................................................3-1
HierarchyFlattening..................................................................................................................3-2
OverviewofHierarchyFlatteningProcess.......................................................................... 3-2
PrerequisitesandTroubleshooting......................................................................................3-3
TablesUsedbytheHierarchyFlatteningProcess................................................................3-5
ExecutingtheHierarchyFlatteningProcess.........................................................................3-5
CheckingtheExecutionStatus.............................................................................................3-8
DimensionTablePopulation....................................................................................................3-8
OverviewofSCDProcess.................................................................................................... 3-9
Prerequisites...................................................................................................................... 3-11
TablesUsedbytheSCDComponent.................................................................................3-12
ExecutingtheSCDComponent......................................................................................... 3-17
CheckingtheExecutionStatus...........................................................................................3-19
ListofDimensionTables....................................................................................................3-20
    iii
DIM_RUNPopulation............................................................................................................ 3-23
OverviewofDIM_RUNProcess........................................................................................3-24
Prerequisites...................................................................................................................... 3-24
TableUsedtoPopulateRUNdetails..................................................................................3-25
ExecutingtheDIM_RUNProcess......................................................................................3-25
CheckingtheExecutionStatus...........................................................................................3-26
TimeDimensionPopulation...................................................................................................3-26
OverviewofTimeDimensionPopulation.........................................................................3-27
Prerequisites...................................................................................................................... 3-27
HowdoesALMBIUseDIMDates....................................................................................3-28
TableUsedtoPopulatetheTimeDimension.....................................................................3-29
ExecutingtheTimeDimensionPopulationProcess...........................................................3-29
CheckingtheExecutionStatus...........................................................................................3-31
4 ALM Results Transformation
OverviewoftheALMResultsTransformationProcess...........................................................4-1
Prerequisites..............................................................................................................................4-2
TablesPopulatedbyALMResultsTransformation.................................................................4-2
ExecutingtheALMResultsTransformationProcess.............................................................4-12
CheckingtheExecutionStatus................................................................................................4-14
SupportofMultipleHierarchies.............................................................................................4-15
5 Account Summary Population
OverviewofAccountSummaryTables....................................................................................5-1
OverviewofAccountSummaryPopulation.............................................................................5-2
Prerequisites..............................................................................................................................5-2
TablesUsedbytheAccountSummaryPopulationT2TProcess.............................................5-3
ExecutingtheAccountSummaryPopulationT2T...................................................................5-8
CheckingtheExecutionStatus..................................................................................................5-9
6 Fact Ledger Population
OverviewofFactLedgerPopulationTransformation..............................................................6-1
Prerequisites..............................................................................................................................6-2
TablesUsedbytheFactLedgerPopulationTransformation...................................................6-2
ExecutingtheFactLedgerPopulationTransformation............................................................6-4
CheckingtheExecutionStatus..................................................................................................6-6
7 Financial Services Authority (FSA) Reports
OverviewofFSAReports..........................................................................................................7-1
iv
PrerequisitesforFSAReporting...............................................................................................7-2
ProcedureforFSAReporting....................................................................................................7-2
HowtoPopulateDIM_REG_FSA_PRODUCTS......................................................................7-3
8 Overview of ALM BI Dashboards and Reports
ALMBIApplication..................................................................................................................8-1
AdvantagesofALMBI..............................................................................................................8-2
AccessingtheStandardReportsandDashboards....................................................................8-2
GettingSeededReportstoShowResults.................................................................................8-2
ALMBIListofDashboards...................................................................................................... 8-3
ListofALMSeededReports.....................................................................................................8-3
ALMReportDetails.................................................................................................................. 8-9
LiquidityRiskManagementReports..................................................................................... 8-64
OverviewofLiquidityRiskManagementReports............................................................ 8-64
LRMReportDetails........................................................................................................... 8-65
LiquidityRiskManagementDashboardPrompts............................................................. 8-84
FundsTransferPricingReports..............................................................................................8-85
A Creating a Custom Report
StepsforCreatingaCustomReport.........................................................................................A-1
B How to Change the Product Dimension in ALM BI
ChangingtheProductDimension-Overview.........................................................................B-1
StepstoPointtoaDifferentProductDimensioninALMBI...................................................B-2
C Update Hierarchy System ID through Batch Execution
Overview...................................................................................................................................C-1
ExecutingtheUpdateHierarchySystemID............................................................................ C-1
CheckingtheExecutionStatus.................................................................................................C-3
D Simplified Batch Execution
StepsforSimplifiedBatchExecution.......................................................................................D-1
E How to Define a Batch
BatchDefinition........................................................................................................................E-1
F Troubleshooting
SolutionsforTroubleshootingtheProblems...........................................................................F-1
    v
G Liquidity Gap Report Division between ALM and LRM
LiquidityGapReportDivisionbetweenALMandLRM.......................................................G-1
H How to Populate Common Account Summary Data Directly from Instrument
Tables
CommonAccountSummaryDataPopulationfromInstrumentTables.................................H-1
vi
Send Us Your Comments
Oracle Financial Services Asset Liability Management Analytics User Guide, Release 6
Part No. E35679-01
Oracle welcomes customers' comments and suggestions on the quality and usefulness of this document.
Your feedback is important, and helps us to best meet your needs as a user of our products. For example:
• Are the implementation steps correct and complete?
• Did you understand the context of the procedures?
• Did you find any errors in the information?
• Does the structure of the information help you with your tasks?
• Do you need different information or graphics? If so, where, and in what format?
• Are the examples correct? Do you need more examples?
If you find any errors or have any other suggestions for improvement, then please tell us your name, the
name of the company who has licensed our products, the title and part number of the documentation and
the chapter, section, and page number (if available).
Send your comments to us using the electronic mail address: [email protected]
Please give your name, address, electronic mail address, and telephone number (optional).
If you need assistance with Oracle software, then please contact your support representative or Oracle
Support Services.
If you require training or instruction in using Oracle software, then please contact your Oracle local office
and inquire about our Oracle University offerings. A list of Oracle offices is available on our Web site at
www.oracle.com.
    vii

Preface
Intended Audience
Welcome to Release 6 of the Oracle Financial Services Asset Liability Management Analytics
User Guide.
The Oracle Financial Services Asset Liability Management Analytics User Guide
provides information needed to understand the underlying structure, prerequisites,
processing requirements, and use of Oracle Financial Services Asset Liability
Management Analytics.
The Oracle Financial Services Oracle Financial Services Asset Liability Management
Analytics User Guide provides useful guidance and assistance to:
• Technical end users supporting Business Intelligence applications
• Functional Business Intelligence end users
See Related Information Sources on page xi for more Oracle product information.
Documentation Accessibility
For information about Oracle's commitment to accessibility, visit the Oracle
Accessibility Program website at
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For
information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.
    ix
Structure
1  Introduction to ALM BI
This chapter provides a general description of the Asset Liability Management
Analytics application.
2  Overview of the ALM BI Process Flow
This chapter provides an overview of the end to end process flow.
3  Dimension Population
This chapter describes the steps required to setup and execute the SCD process.
4  ALM Results Transformation
This chapter describes the steps required to setup and execute the ALM Result
transformation process.
ALM Results Transformation is the process of pushing the ALM Results from the ALM
Processing area to the ALM BI Mart. It transforms both Deterministic and Stochastic
ALM results as well as other related information such as forecast rates and detailed cash
flows, based on the type of process and processing options selected.
5  Account Summary Population
This chapter describes how and when to execute the data movement processes needed
to populate account level data in the reporting mart.
Account Summary tables are account level BI tables that are used to consolidate
information from the various product specific tables used in both the Staging Area and
Operational Processing areas. The Account Summary tables in the ALM BI data model
are loaded from both the Staging Area tables and operational Instrument Tables using
the Table to Table (T2T) component of OFSAAI framework.
6  Fact Ledger Population
Fact Ledger population involves populating the FCT_LEDGER_STAT table from the
LEDGER_STAT table.
7  Financial Services Authority (FSA) Reports
8  Overview of ALM BI Dashboards and Reports
This chapter describes the seeded reports and dashboards.
A  Creating a Custom Report
This section details how to create a custom report using OBIEE and ALM BI.
B  How to Change the Product Dimension in ALM BI
This section describes how to change the Product dimension. The seeded product
dimension is PRODUCT. You can change any product dimensions other than the
seeded dimension, as follows:
C  Update Hierarchy System ID through Batch Execution
D  Simplified Batch Execution
This section describes how to setup and execute a simplified batch required for running
ALM BI processes.
E  How to Define a Batch
x