This chapter gives you an overview of the Oracle Financial Services reporting architecture.
This chapter covers the following topics:
Reports are necessary to analyze business profitability and risk management. Whether you want to perform analysis on past activities or forecast future activities, you need efficient and logical access to your data. In addition, you may need to support broad distribution, remote users, or your own custom reports. The Oracle Financial Services Applications (OFSA) reporting solution provides you with the tools to meet these needs.
The reporting solution contains a Discoverer-based architecture that presents processed data stored in Enterprise Performance Foundation (EPF) tables, as well as the seeded reports for specific reporting activity. This chapter covers:
Features of the OFSA reporting architecture.
Data available in the architecture and required transformation.
Relationship between EPF Data Entities and its presentation in the architecture.
Most of the OFSA data is stored in the Enterprise Performance Foundation (EPF) tables. The EPF tables store process related metadata such as dimensions, hierarchies, rules, as well as the majority of the processed fact data. This data is required for OFSA reporting and thus the EPF tables must be accessible by the reporting solution.
To satisfy various reporting needs, Discoverer is the tool to provide reporting capabilities for the OFSA applications. When you use Discoverer for reporting, all reporting data needs to be represented in one or more business areas and reports are built upon them.
The OFSA reporting architecture consists of two core business areas, EPF – Core Business Area and FTP - Core Business Area, and related programs to convert some EPF data into Discoverer required format. The core EPF business area contains all necessary joins between EPF dimension members, hierarchies and fact tables to support the following activities:
A generic and scalable platform to support out-of-the-box reports provided by individual OFSA applications.
A foundation on which individual OFSA application can build upon to create their own business areas.
A platform to facilitate development of custom reports.
The seeded EPF business area can be extended to support custom changes in the EPF data model.
The core EPF business area includes the following entities from EPF. Some of these entities need to go through programmatic transformation before they can be used in Discoverer for reporting.
Definition information, such as dimension name and description, and all non-personal members are available in the business area. This information does not require transformation.
Note: For list of EPF dimensions included in the business area, see Core EPF Business Area and its Components.
All non-personal hierarchy structures from key OFSA dimensions are available in the business area.
Note: For the list of EPF dimensions whose hierarchies are represented, see Core EPF Business Area and its Components.
Run the Enterprise Performance Foundation Discoverer Hierarchy Transformation Engine program before you use the structures in Discoverer. This program will transform the parent-child relationships of each hierarchy into Discoverer compatible format. The transformed data supports up to twenty levels in each hierarchy.
Note: This program is available from the Enterprise Performance Foundation Administrator responsibility and the FTP Supervisor responsibility.
The EPF object catalog, that stores information such as processing rule information, and all version information for each object, are available in the business area. This information does not require transformation.
FEM data tables used by the OFSA applications are available in the business area. This information does not require transformation.
Note: For the list of EPF data tables represented, see Core EPF Business Area and its Components.
Moreover, additional calculated items, custom views linking multiple data tables, and various PL/SQL functions are available in the business area to facilitate reporting activities.
The following physical data model represents the relationship between fact tables, dimension hierarchy tables, dimension member tables, and value set mapping views using these tables and views:
FEM_BALANCES
FEM_NAT_ACCTS_HIER
FEM_NAT_ACCTS_B
FEM_DIS_LDGR_DIM_VS_MAP
FEM_BALANCES Fact Table Relationships
The EPF data entities are related as follows:
FEM_BALANCES table is joined with FEM_NAT_ACCTS_HIER table using NATURAL_ACCOUNT_ID and CHILD_ID columns.
FEM_NAT_ACCTS_HIER table has a recursive joining structure using CHILD_ID and PARENT_ID columns. Both PARENT_ID and CHILD_ID columns are joined with the NATURAL_ACCOUNT_ID column in the FEM_NAT_ACCTS_HIER table.
The FEM_DIS_LDGR_DIM_VS_MAP view contains mapping information between ledger ID and dimension value set IDs.
The FEM_BALANCES table and FEM_DIS_LDGR_DIM_VS_MAP view are joined through LEDGER_ID.
FEM_DIS_LDGR_DIM_VS_MAP view NATURAL_ACCOUNT_VS_ID column joins in FEM_NAT_ACCTS_B table, VALUSET_ID column.
In an Oracle Discoverer business area, each database object is represented as a folder. The figure below illustrates folder relationships in the business area based on the physical data model described in the FEM_BALANCES fact table relationships figure.
FEM Balances Folder Relationship
The EPF Balances folder is based on the FEM_BALANCES table, the EPF Natural Accounts Dimension Hierarchy folder is based on the FEM_DIS_NAT_ACCTS_HIER_VL view, and the Dimension Value Set Map folder is based on the FEM_DIS_LDGR_DIM_VS_MAP view.
The FEM_DIS_NAT_ACCTS_HIER_VL view is a hierarchy transformation view based on the FEM_NAT_ACCTS_HIER table. LEVEL1 to LEVEL20 columns in FEM_DIS_NAT_ACCTS_HIER_VL view represent hierarchy levels 1 through 20.
The EPF Balances and the EPF Natural Accounts Dimension Hierarchy folders are joined with the Natural Account ID item from the EPF Balances folder and the Level20 ID item from the EPF Natural Accounts Dimension Hierarchy folder.
The Dimension Value Set Map folder contains mapping information between ledger ID and dimension value set IDs. The mapping information is used to join the fact folder (EPF Balances) and the dimension hierarchy folder (EPF Natural Accounts Dimension Hierarchy folder) using value set IDs. The mapping information is also used to join fact folders and dimension member folders. Since the Discoverer Workbook does not allow pulling data if joins exist on all three folders (fact folder, dimension hierarchy folder, and the mapping folder), a workbook condition is used. The workbook condition uses a worksheet that is based on a Dimension Value Set Map folder to create the additional joins.
This folder relationship represents all facts tables with dimension hierarchy tables and dimension member table relationships in the business area.
If you add new columns to seeded fact tables or add new tables, you must manually add items for the new columns or create folders for the new tables using Discoverer Administration Edition. See:
How to add items to an item class, Oracle Business Intelligence Discoverer Administration Guide.
What are folders?, Oracle Business Intelligence Discoverer Administration Guide.
How to create simple folders from the database, Oracle Business Intelligence Discoverer Administration Guide.
How to create complex folders, Oracle Business Intelligence Discoverer Administration Guide.
How to create custom folders, Oracle Business Intelligence Discoverer Administration Guide.
The item names for the fact table folders are based on the seeded FEM configuration. If you change these names in FEM, you must manually make the changes in Discoverer Administrator.