Oracle BI Administration Tool
This section discusses the:
Repository file
Physical layer
Business Model and Mapping layer
Presentation layer
Repository File
Prebuilt metadata content is maintained in the metadata repository file named EPM91_Master.rpd. The repository contains the Physical, Business Model and Mapping, and Presentation layers that are discussed in the following sections. The Oracle BI Administration Tool is the user interface into the layers in the repository, as shown in this example:
Image: Oracle BI Administration Tool
This example illustrates the fields and controls on the Oracle BI Administration Tool. You can find definitions for the fields and controls later on this page.
Consistency Check
The Oracle Business Intelligence Server Administration Guide discusses consistency checks that you must perform on repository metadata before you make the repository available for queries.
Physical Layer
A Physical table is an object in the Physical layer of the Oracle BI Administration Tool that corresponds to an object in a Physical database. The Physical layer folder stores the shortcuts (references) to physical tables. Physical tables are typically imported from a database or another data source, and they provide the metadata necessary for the Oracle BI Server to access the tables. The Physical layer represents the physical structure of the data sources to which the Oracle BI Server submits queries.
A physical object in OBIEE can also be based on an SQL query.
Connection Pools
In Fusion Campus Solutions Intelligence application, the EPM warehouses provide the data source for the Physical layer. A Physical layer can have multiple data sources. Each data source must have at least one corresponding connection pool, which contains data source information that the system uses to connect to a data source, the number of connections allowed, timeout information, and other connectivity-related administrative details. Connection pools allow multiple concurrent data source requests (queries) to share a single database connection, reducing the overhead of connecting to a database.
The Fusion Campus Solutions Intelligence application delivers two connection pools—MDW (multidimensional warehouse) Connection Pool and MDW Init Block Connection Pool. For performance reasons, the system uses the MDW InitBlock connection pool exclusively for initialization blocks. The dedicated connection pool contains these default properties:
|
Property |
Value |
|---|---|
|
Name |
MDW InitBlock Connection Pool |
|
Call Interface |
Default (OCI 8i/9i) |
|
Data Source Name |
<customer specific> |
|
Shared Logon |
Yes |
|
User Name |
<customer specific> |
|
Password |
<customer specific> |
|
Enable Connection Pooling |
Yes |
|
Parameters Supported |
Yes |
See Oracle Business Intelligence Server Administration Guide, "Creating and Administering the Physical Layer in an Oracle BI Repository," Setting Up Connection Pools.
Initialization Blocks
The system uses initialization blocks to initialize dynamic repository variables, system session variables, and nonsystem session variables.
|
Name |
Query |
Variable Name |
Type |
|---|---|---|---|
|
Admission Application PIA page |
SELECT A.URI || B.URL FROM PS_SRC_CONFIG A, PS_SRC_COMPONENT B WHERE A.SRC_SYS_ID = B.SRC_SYS_ID AND A.SRC_SYS_ID = 'HCM' AND B.COMPONENT_ID= 'ADM_APPL_PROG_MNT' |
ADM_APPL_PROG_MNT_PG |
Dynamic Repository |
|
Job Summary PIA page |
SELECT A.URI || B.URL FROM PS_SRC_CONFIG A, PS_SRC_COMPONENT B WHERE A.SRC_SYS_ID = B.SRC_SYS_ID AND A.SRC_SYS_ID = 'HCM' AND B.COMPONENT_ID= 'JOB_SUMMARY' |
JOB_SUMMARY_PG |
Dynamic Repository |
|
Journal Entries PIA page |
SELECT A.URI || B.URL FROM PS_SRC_CONFIG A, PS_SRC_COMPONENT B WHERE A.SRC_SYS_ID = B.SRC_SYS_ID AND A.SRC_SYS_ID = 'FSCM' AND B.COMPONENT_ID= 'JOURNAL_ENTRY' |
JOURNAL_ENTRY_PG |
Dynamic Repository |
|
Overdue Scheduled Payments PIA page |
SELECT A.URI||B.URL FROM PS_SRC_CONFIG A, PS_SRC_COMPONENT B WHERE A.SRC_SYS_ID= B.SRC_SYS_ID AND A.SRC_SYS_ID='FSCM' AND B.COMPONENT_ID= 'VNDR_PAYINQ_OVRDUE' |
VNDR_PAYINQ_OVRDUE |
Dynamic Repository |
|
None, defaulted to 2006 |
CURRENT_YEAR |
Static Repository |
|
|
None |
CURRENT_YEAR - 1 |
ONE_YEAR_PRIOR |
Static Repository |
|
None |
CURRENT_YEAR - 2 |
TWO_YEAR_PRIOR |
Static Repository |
|
None |
CURRENT_YEAR - 3 |
THREE_YEAR_PRIOR |
Static Repository |
|
None |
CURRENT_YEAR - 4 |
FOUR_YEAR_PRIOR |
Static Repository |
|
None |
CURRENT_YEAR - 5 |
FIVE_YEAR_PRIOR |
Static Repository |
Global Dimensions
Global dimensions are used by more than one mart within a functional warehouse, and across functional warehouses, to provide you with a consistent view of the data. The Physical layer stores global dimension tables in the Global Dimensions folder. Examples of global dimensions are calendar, time, and business unit.
System Table
The fact table named ZZZ is a logical table that is used for system purposes.
Business Model and Mapping Layer
The Business Model and Mapping layer represents the logical structure of the information in the repository. The physical schemas are simplified and reorganized based on the users' view of the data. The business models contain logical columns arranged in logical tables (logical dimension tables and logical fact tables), logical joins, and dimensional hierarchy definitions. This layer also contains the mappings from the logical columns to the source data in the Physical layer.
The Business Model and Mapping layer appears in the middle pane of the Oracle BI Administration Tool.
Generally, each logical display folder in this layer represents a business area. Each folder has a shortcut (reference) to all of the logical dimension and fact tables that are joined together in a star schema. For example, CSW – Admissions and Recruiting – Admission Application is the name of a logical display folder. It contains the logical fact table named Fact Admission Application, and related logical dimension tables. The logical display folder should contain all of the dimensions and facts that are required for the given star schema.
Image: CSW – Admissions and Recruiting – Admission Application star schema
The following graphic represents the star schema for the CSW – Admissions and Recruiting – Admission Application logical folder:
Dimensional Hierarchies
Some of the results that appear in the Fusion Campus Solutions Intelligence application represent hierarchical data structures. A hierarchy is a set of parent-child relationships between certain attributes within a dimension. The hierarchy attributes, called levels, roll up from child to parent. For example, months can roll up to years. Therefore, if an aggregate table exists at the month level, that table can be used to answer questions at the year level by summing all of the month-level data for a year.
The dimensional hierarchies in the Business Model and Mapping layer are either inherited from the dimensional hierarchies that exist in PeopleSoft source system data structures, or created specifically to support the delivered dashboard functionality. The Fusion Campus Solutions Intelligence application delivers metadata with these prebuilt hierarchies:
|
EPM Warehouse |
Hierarchical Dimension |
Table |
|---|---|---|
|
Campus Solutions |
Institution Academic Plan Academic Program Academic Organization Term Day |
D_INSTITUTION D_ACAD_PLAN D_ACAD_PROG H_ACAD_ORG D_TERM D_DAY |
|
Financial Management Solutions |
Account Department Product Day Business Unit |
H_ACCOUNT H_DEPT D_PRODUCT D_DAY D_BUSINESS_UNIT |
|
Human Capital Management |
Department Geography Day |
H_DEPT H_GEO D_DAY |
|
Supply Chain Management |
Commodity Supplier Business Unit Day |
D_COMMODITY D_SUPPLIER D_BUSINESS_UNIT D_DAY |
Degenerate Dimensions
A fact table may contain columns that cannot be aggregated, such as application number, application program number, and student career number that act as dimension attributes. In the Business Model and Mapping layer, these columns reside in a separate logical dimension folder named Dimension <Fact table name >. The logical fact folder (named Fact <Fact table name >) and the logical dimension folder (named Dimension <Fact table name >) both use the same physical source table.
For example, the F_ADM_APPL fact table in the Physical layer contains these columns that cannot be aggregated
Application Number
Application Program Number
Student Career Number
In this example, the F_ADM_APPL fact table is represented by two logical tables in the Business Model and Mapping layer: the Fact Admission Application table and the Dimension Admission Application table. Dimension Admission Application is the degenerate dimension. The Dimension Admission Application table contains the degenerate dimensions from the F_ADM_APPL fact table.
The Presentation table that hosts the degenerate attributes is called Document Details.
Presentation Layer
The Presentation layer simplifies the business model and makes it easy for users to understand and query. It exposes only the data that is meaningful to the users, and organizes the data in a way that aligns with the way that users think about the data. The Presentation layer represents subject areas. This layer provides a way to present a customized view of a business model, known as Presentation catalog, to different sets of users. The Presentation layer appears in the left pane of the Oracle BI Administration Tool and contains a group of subject areas that represent information about your institution or groups of users within your institution.
The subject area appears in the workspace when you click the Answers link from any location in the OBIEE application. Subject area names correspond to the types of information that they contain. For example, the metadata for the CSW subject area maps to data in the Campus Solutions Warehouse.
Campus Solutions
The delivered OBIEE metadata provides insight into information in these Campus Solutions Warehouse data marts and subject areas:
|
Campus Solutions Warehouse Data Mart |
Subject Area |
|---|---|
|
Admissions and Recruiting |
Admission Application Admission Application Status Admission Funnel Application Evaluation Student Recruiting Application Test Scores External Academic Summary External Test Scores Prospects to Applicants to Enrollees Recruiting Student Responses |
|
Campus Community |
Campus Events Event Meetings Organization Check List Organization Communications Person Check List Person Communications Service Indicators |
|
Student Financial Services |
Award Disbursement Award Snapshot Bill Summary Credit History Payment and Charges Cross Reference Payment Summary Student Financials Accounting Line Student Financials Payment Details Student Financials Transactions Student Financials Transactions Details |
|
Student Records |
Academic Plan Summary Academic Program Detail Class Class Enrollment Class Instructor Class Meeting Pattern Enrollment Requests Institution Summary Student Degrees Term Enrollment |
See Understanding Fusion Campus Solutions Intelligence Dashboards and Reports.
Financial Management Solutions
The delivered OBIEE metadata provides insight into information in these FMS Warehouse data marts and subject areas:
|
FMS Warehouse Data Mart |
Subject Area |
|---|---|
|
Enterprise Service Automation |
Contract Amendments Contract Distributions Contract Forecast Current Contract Forecast Periodic Contract Renewals Contract Revenue Recognition Contract Transactions Current Projects Employee Forecast Expense Distributions Expense Report Approvals Grants Management Award Grants Management Project Transaction Grants Management Proposal Grants Management Proposal and Award Summary Periodic Projects Project Changes Project Current Activities Project Deliverables Project Issues Project Periodic Activities Project Transactions Resource Assignments Resource Rates Resource Time Reports Service Orders |
|
General Ledger and Profitability |
Aggregated Balance Journal Entries Period Balances Profitability Analysis Commitment Control Activity Log Commitment Control Activity Log Fund Source Commitment Control Journal Commitment Control Budget Association Commitment Control Budget Balances Commitment Control Detailed Ledger Commitment Control Encumbrance Commitment Control Exception Commitment Control Fund Source Allocation Commitment Control Fund Source Received Commitment Control Overrides Commitment Control Transaction Log |
|
Global Consolidations |
Consolidation Ledger Flows |
|
Payables |
Account Entries Aging Process Ledger Voucher Match Exceptions Vouchers and Payments |
|
Receivables |
Account Entries Aging Process Credit Limit Customer and Supplier Netting Customer Ledger Days Sales Outstanding Items and Receipts |
See Understanding Fusion Campus Solutions Intelligence Dashboards and Reports.
Human Capital Management
The delivered OBIEE metadata provides insight into information in these HCM Warehouse data marts and subject areas:
|
HCM Warehouse Data Mart |
Subject Area |
|---|---|
|
Compensation |
Absence Absence Accrual Absence Event Absence Request Benefit Enrollment Global Payroll Leave Accrual Payroll Deduction Payroll Deduction Balance Payroll Earnings Payroll Earnings Balance Payroll Other Earnings |
|
Learning |
Accomplishment Activity Cost Completion Current Accomplishment Current Competency Employee Appraisal Employee Review Enrollment Learning Objective Learning Resource Person Competency Program Registration Training Training Course Session Expense |
|
Recruiting |
Recruitment Expenses Recruitment Tracking |
|
Workforce |
Benchmark Survey Disciplinary Action Employee Job Grievance Injury Illness Time And Labor Workforce Movement Activity Workforce Profile |
Supply Chain Management
The delivered OBIEE metadata provides insight into information in these SCM Warehouse data marts and subject areas:
|
SCM Warehouse Data Mart |
Subject Area |
|---|---|
|
Spend |
Voucher Voucher Line Voucher Distribution Line |
|
Fulfillment and Billing |
Billing Booking Billing and Backlog Bookings Kit Order To Cash Cycle Time Return Material Authorization Received Sales Order Sales Order Line Sales Order Line Ship |
|
Inventory |
History Sum Ledger Inventory Cycle Count Inventory Ledger Inventory Transaction Physical Inventory |
|
Manufacturing |
Work Center Master Work Center Resource Unit Work Order Master Work Order Part List Work Order Production Cost Work Order Routing Work Order Time Transaction |
|
Procurement |
Matching Analysis Procurement Cycle Time Analysis Purchase Order Disposition Purchase Order Distribution Purchase Order Line Purchase Order Line Multiple Accounts Purchase Order Receipt and Voucher Purchase Order Receipt and Voucher Multiple Accounts Purchase Order Received Shipment Requisition Analysis Requisition Line Return to Vendor Return to Vendor Distribution |
|
Supply Chain Planning |
Inventory Inventory Transfer Production Production Capacity Purchasing Sales Actual Sales Forecast |