18 Configuring the Oracle BI Repository

This section describes how to configure the Oracle BI Repository for use with Oracle BI Applications.

It contains the following topics:

Section 18.1, "How to Set Up Date-Specific Metrics"
Section 18.2, "How to Set Up Additional Time Series Metrics for Oracle Business Analytics Warehouse"
Section 18.3, "How to Set Up Additional Dimension Tables for Oracle Business Analytics Warehouse"
Section 18.4, "How to Set Up Product Category Hierarchies"
Section 18.5, "About the Ago Keys for Oracle Business Analytics Warehouse"
Section 18.6, "About Oracle BI Time Repository Variables"
Section 18.7, "About User Authentication"
Section 18.8, "About the Security or Visibility Configuration"
Section 18.9, "About the Group Variable"
Section 18.10, "About Configuring Usage Tracking for Oracle Business Analytics Warehouse"
Section 18.11, "About the Incremental Deployment of the Oracle BI Applications Repository"

Note:

When you open OracleBIAnalyticsApps.rpd in the Oracle BI Administration Tool, you need to enter the repository password. See Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users for more information.

18.1 How to Set Up Date-Specific Metrics

The time dimension in the Oracle BI Repository for Oracle Business Analytics Warehouse is a standard or canonical time dimension that links to the most important time role in each star schema. The Physical table alias used as a canonical time dimension is W_DAY_D_Common.

If a fact table contains a distinct set of metrics that must be reported by different dates, the metadata is organized so that each metric is reported by its causal date.

For example, the Invoice fact table has three metrics called Invoice Amount, Fulfill Amount, and Paid Amount, and each of these metrics need to be reported by the corresponding date—Invoice Date, Fulfill Date, and Payment Date. Additional dates in a fact table that a metric could be queried by are known as Secondary dates. These are available to the end users inside a detailed presentation folder. The detailed presentation folder is typically called the Details folder.

In Table 18-1 each of the metrics reflect the activity related to that event for the entire period, for example, Invoice Amount by Invoice Date, Fulfill Amount by Fulfill date, and Payment Amount by Payment Date.

Table 18-1 Invoice Fact Table Example

Date	Invoice Amount	Fulfill Amount	Payment Amount
January	4000	5000	4500

To implement date-specific metrics:

Using the Administration Tool, open OracleBIAnalyticsApps.rpd.

The OracleBIAnalyticsApps.rpd file is located at:
```
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_
obisn\repository
```
In the Physical layer, right-click Oracle Data Warehouse, and create a new physical alias for the fact table.
Create joins for the physical alias which are similar to the base fact table.

The Join to the date dimension is changed to use the date role in question.
Create a new logical table source in the logical fact table that maps the metrics for the physical fact alias.

The grain of the fact table is the same as the base fact table.

Note:

You need to map each metric to one logical table source at the Detail Level.

18.2 How to Set Up Additional Time Series Metrics for Oracle Business Analytics Warehouse

The Oracle BI Repository provides a framework to add AGO metrics. The Oracle Business Analytics Warehouse is preconfigured with pre-mapped AGO metrics; however, you can map other metrics by using the following procedure.

To set up additional time series metrics:

Using the Administration Tool, open OracleBIAnalyticsApps.rpd.

The OracleBIAnalyticsApps.rpd file is located at:
```
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_
obisn\repository
```
In the Physical layer, right-click the Oracle Data Warehouse object, and create a new Ago physical alias table.
In the Physical layer, create additional tables for each Ago alias required.

For example, Quarter Ago, Year Ago, and so on.

These aliases need to have the same joins as the base fact table, except for the date join, which you can change in the next step. Setting up this alias is easier to accomplish by copying the base table.
Change the join to the date dimension (W_DAY_D) to use the appropriate Ago Key.
Map the Ago metrics in the logical table using the new fact alias by creating a new logical table source under the fact table.
Set the content pane levels for the Ago logical table source, to specify the level of the source data.

These settings are the same as the base fact table.
Save and close the OracleBIAnalyticsApps.rpd file.

18.3 How to Set Up Additional Dimension Tables for Oracle Business Analytics Warehouse

The Oracle Business Analytics Warehouse is preconfigured to map dimension tables required for analysis. The Physical layer in the Oracle BI Repository provides several other dimension table keys that can be used for certain specific analysis. If you need to set up any of the additional dimension tables in the Physical layer, perform the following procedure.

To set up additional dimension tables:

Validate that the dimension table key is resolved appropriately for the data source that you are using.

Note:

Dimension tables do not apply to every source system.
Using the Administration Tool, open OracleBIAnalyticsApps.rpd.

The OracleBIAnalyticsApps.rpd file is located at:
```
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_
obisn\repository
```
Add a dimension table alias in the Physical layer.
Join the dimension table alias to the fact table alias using the appropriate keys.
Save and close the OracleBIAnalyticsApps.rpd file.

18.4 How to Set Up Product Category Hierarchies

In Oracle Business Intelligence Applications, the following three hierarchies are supplied by default:

General Hierarchy
Purchasing Hierarchy
UNSPSC Hierarchy

You can optionally choose to configure additional hierarchies. The following procedure shows an example of how to do this, based on one of the purchasing categories configured by default.

Note that you can use steps similar to the ones in this procedure to add any type of hierarchy to the RPD.

To configure product category hierarchies in the RPD file:

Using the Administration Tool, open OracleBIAnalyticsApps.rpd.

The OracleBIAnalyticsApps.rpd file is located at:
```
ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_
obisn\repository
```
For the purchasing category example, create a link with the W_PRODUCT_D table's PROD_CATn_WID column.
Create the new dimension and dimension hierarchy in the Business Model and Mapping layer.
Add the new columns to the Presentation layer.

For information about configuring Product Category Hierarchies in DAC, see Section 3.2.1.1, "Configuration of Product Hierarchy (Except for GL, HR Modules)."

18.5 About the Ago Keys for Oracle Business Analytics Warehouse

The Ago Key fields are used to set up the time series metrics like Year Ago, Quarter Ago, and so on. The Ago Key fields represent metrics for a prior period, for example, Quarter Ago Revenue, Year Ago Revenue, and so on. Oracle Business Analytics Warehouse is preconfigured with a set of fields in the W_DAY_D table. These fields are:

MONTH_AGO_WID
QUARTER_AGO_WID
TRIMESTER_AGO_WID
WEEK_AGO_WID
YEAR_AGO_WID

These fields are used in joins to Oracle Business Analytics Warehouse fact tables to achieve the Ago metrics. The joins in Oracle Business Analytics Warehouse uses the Ago fields in the W_DAY_D table.

18.6 About Oracle BI Time Repository Variables

The Oracle BI Repository is preconfigured with variables that are used for both reporting and internal usage.

Table 18-2 lists some example Oracle BI repository date variables and their descriptions. For a full list of variables, in the Oracle BI Administration Tool, choose Manage, then Variables, to display the Variable Manager, and refer to the Description fields for a brief description.

Table 18-2 Oracle BI Repository Date Variables

Variable Name	Description
CAL_MONTH_YEAR_AGO	Returns the value of Previous Year Month in the YYYY/MM format.
CURRENT_BALANCE_DK_AP	Returns the value of the last date key for the available Accounts Payable balance. It is used in Accounts Payable Account Balance Computation.
CURRENT_BALANCE_DK_AR	Returns the value of the last date key for the available Accounts Receivables balance. It is used in Accounts Receivable Account Balance Computation.
CURRENT_BALANCE_DK_GL	Returns the value of the last date key for the available General Ledger balance. It is used in General Ledger Account Balance Computation.
CURRENT_DAY	Returns the value of Current Date in the MM/DD/YYYY format.
CURRENT_FSCL_MONTH	Returns the value of Current Fiscal Month in the YYYY/MM format.
CURRENT_FSCL_QUARTER	Returns the value of Current Quarter in the YYYY Q n format.
CURRENT_FSCL_WEEK	Returns the value of Current Fiscal Week in the YYYY Week nn format.
CURRENT_FSCL_YEAR	Returns the value of Current Fiscal Year in the FYYYYY format.
CURRENT_JULIAN_DAY_NUM	Returns the value of Current Julian Date Number.
CURRENT_MONTH	Returns the value of Current Month in the YYYY/MM format.
CURRENT_QTR	Returns the value of Current Quarter in YYYY Q n format.
CURRENT_WEEK	Returns the value of Current Week in the YYYY Week nn format.
CURRENT_YEAR	Returns the value of Current Year in the YYYY format.
FSCL_MONTH_YEAR_AGO	Returns the value of Previous Year Fiscal Month in YYYY/MM format.
FSCL_QTR_YEAR_AGO	Returns the value of Previous Year Quarter in YYYY Q n format.
NEXT_FSCL_MONTH	Returns the value of Next Fiscal Month in the YYYY / MM format.
NEXT_FSCL_QUARTER	Returns the value of Next Quarter in the YYYY Q n.
NEXT_FSCL_WEEK	Returns the value of Next Fiscal Week in the YYYY Weeknn format.
NEXT_FSCL_YEAR	Returns the value of Next Fiscal Year in the FYYYYY format.
NEXT_MONTH	Returns the value of Next Month in the YYYY / MM format.
NEXT_QUARTER	Returns the value of Next Quarter in the YYYY Q n.
NEXT_WEEK	Returns the value of Next Week in the YYYY Weeknn format.
NEXT_YEAR	Returns the value of Next Year in the YYYY format.
PREVIOUS_FSCL_MONTH	Returns the value of Previous Fiscal Month in the YYYY/MM format.
PREVIOUS_FSCL_QUARTER	Returns the value of Previous Quarter in the YYYY Q n format.
PREVIOUS_FSCL_WEEK	Returns the value of Previous Fiscal Week in the YYYY Weeknn format.
PREVIOUS_FSCL_YEAR	Returns the value of Previous Fiscal Year in the FYYYYY format.
PREVIOUS_MONTH	Returns the value of Previous Month in the YYYY/MM format.
PREVIOUS_QUARTER	Returns the value of Previous Quarter in the YYYY Q n.
PREVIOUS_WEEK	Returns the value of Previous Week in the YYYY Weeknn format.
PREVIOUS_YEAR	Returns the value of Previous Year in the YYYY format.
REF_JULIAN_DATE	Stores the start date of the Julian calendar and should not be changed.
REF_JULIAN_DATE_NUM	Stores the Julian number for the start of the Julian calendar and should not be changed.
TIME_OFFSET	Returns the difference between the current date and a given number of days value. It is primarily used for testing to simulate an earlier or later date. You could set the variable to the number of days you want the preceding date variables to be moved back.
YEAR_AGO_DAY	Returns the value of year ago date in the mm/dd/yyyy format.

18.7 About User Authentication

You need to configure the user authentication of the Oracle Business Analytics Warehouse, depending on your requirements. For more information about configuring user authentication, see Oracle Business Intelligence Applications Security Guide and Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition.

18.8 About the Security or Visibility Configuration

Oracle BI Applications includes a set of application roles that are used to control the visibility of Subjects Areas in the Presentation layer. For more information about application roles in Oracle BI Applications, see Oracle Business Intelligence Applications Security Guide.

Table 18-3 lists some example application roles. You can view a full list of application roles in the policy store, or open the RPD file in the Oracle BI Administration Tool in online mode and select Manage, then select Identity.

Table 18-3 Application Roles

Application Role	Description
Administrators	The Administrators application role has all rights and privileges. It cannot be removed.
Agent Scorecard User	This application role is able to view Agent Scorecard application content.
AP Analyst	This application role is able to view application content for Oracle Payables Analytics.
AP Manager	This application role is able to view high-level application content for Oracle Payables Analytics.
AR Analyst	This application role is able to view application content for Oracle Receivables Analytics.
AR Manager	This application role is able to view high-level application content for Oracle Receivables Analytics.
CFO	This application role is able to view most of the Oracle Financial Analytics application content.
Contact Center and Agent Performance Analyst	This application role is able to view Contact Center Telephony Analytics and Agent Performance application content.
Contact Center and Agent Performance User	This application role is able to view a subset of Contact Center Telephony Analytics and Agent Performance application content.
Contact Center Sales Analyst	This application role is able to view Contact Center Telephony Analytics and Order Management Analytics application content.
Contact Center Sales User	This application role is able to view a subset of Contact Center Telephony Analytics and Order Management Analytics application content.
Controller	This application role is able to view application content for Oracle General Ledger and Profitability Analytics and Siebel Profitability Analytics.
Customer Service Analyst	This application role is able to view Customer Service for Oracle Contact Center Telephony Analytics application content.
Customer Service User	This application role is able to view a subset of Customer Service for Oracle BI Contact Center Telephony Analytics application content.
Contact Center Telephony Analytics User	This application role is able to view Oracle BI Contact Center Telephony Analytics application content.
Financial Analyst	This application role is able to view Oracle Financial Analytics application content.
Human Resources Analyst	This application role is able to view Oracle HR Analytics application content.
Human Resources Vice President	This application role is able to view high-level application content for Oracle HR Analytics application.
Inventory Analyst	This application role is able to view application content for Oracle's Procurement and Spend Analytics Family of Products (Oracle Inventory Analytics, Oracle Procurement and Spend Analytics, Oracle Supplier Performance Analytics).
Inventory Manager	This application role is able to view high-level application content for Oracle's Procurement and Spend Analytics Family of Products (Oracle Inventory Analytics, Oracle Procurement and Spend Analytics, Oracle Supplier Performance Analytics).
Primary Owner-Based Security	Used for securing owner-based data elements that come from the transactional system.
Primary Position-Based Security	Used for securing position-based data elements that come from the transactional system.
Purchasing Buyer	This application role is able to view Oracle's Procurement and Spend Analytics Family of Products (Oracle Inventory Analytics, Oracle Procurement and Spend Analytics, Oracle Supplier Performance Analytics) content pertaining to purchasing.
Sales Executive Analytics	This application role is able to view high-level application content for the Order Management Analytics application.
Sales Manager	This application role is able to view most of the high-level application content for Oracle BI Contact Center Telephony Analytics application.
Sales Manager Analytics	This application role is able to view most of the high-level application content for Oracle BI Contact Center Telephony Analytics application.
Sales Operations Analytics	This application role is able to view operational application content for Oracle BI Contact Center Telephony Analytics application.
Sales Representative Analytics	This application role is able to view low-level application content for Oracle BI Contact Center Telephony Analytics application.
Sales Rev and Fulfill Analyst	This application role is able to view the content for Oracle BI Contact Center Telephony Analytics Revenue and Fulfillment application.
Sales Rev and Fulfill Exec	This application role is able to view the high-level application content for Oracle BI Contact Center Telephony Analytics Revenue and Fulfillment application.
Sales Rev and Fulfill Mgr	This application role is able to view most of the high-level application content for Oracle BI Contact Center Telephony Analytics Revenue and Fulfillment application.
Sales Rev and Fulfill Rep	This application role is able to view low-level application content for Order Management Analytics Revenue and Fulfillment application.
Sales Revenue Analyst	This application role is able to view the content for Oracle BI Contact Center Telephony Analytics Revenue application.
Sales Revenue Exec	This application role is able to view the high-level application content for Oracle BI Contact Center Telephony Analytics Revenue application.
Sales Revenue Mgr	This application role is able to view most of the high-level application content for Oracle BI Contact Center Telephony Analytics Revenue application.
Sales Revenue Rep	This application role is able to view low-level application content for Oracle BI Contact Center Telephony Analytics Revenue application.
Service Delivery and Costs Analyst	This application role is able to view Service Delivery and Costs for Oracle BI Contact Center Telephony Analytics application content.
Service Delivery and Costs User	This application role is able to view a subset of Service Delivery and Costs for Oracle BI Contact Center Telephony Analytics application content.
Supplier Performance Analyst	This application role is able to view Oracle's Procurement and Spend Analytics Family of Products (Oracle Inventory Analytics, Oracle Procurement and Spend Analytics, Oracle Supplier Performance Analytics) content pertaining to supplier performance.
Supplier Performance Manager	This application role is able to view high-level content for Oracle's Procurement and Spend Analytics Family of Products (Oracle Inventory Analytics, Oracle Procurement and Spend Analytics, Oracle Supplier Performance Analytics) content pertaining to supplier performance.
Supply Chain Executive	This application role is able to view Oracle's Procurement and Spend Analytics Family of Products (Oracle Inventory Analytics, Oracle Procurement and Spend Analytics, Oracle Supplier Performance Analytics) content.

18.9 About the Group Variable

The GROUP session variable in the RPD file retrieves the membership of a user in the application roles used for data and object security. Application role membership is managed in the policy store.

If you are using Oracle BI Applications with the Siebel CRM system, then you can leverage the Authorization session initialization block to populate the GROUP variable with the appropriate Siebel responsibilities, which associates users to what they are allowed to see in the Oracle BI Application by using their Siebel responsibility. For more information, see Oracle Business Intelligence Applications Security Guide. Figure 18-1 shows an example of an initialization block that associates a user to a Group membership.

Figure 18-1 Initialization Block - Authorization Dialog

This screenshot or diagram is described in surrounding text.

18.10 About Configuring Usage Tracking for Oracle Business Analytics Warehouse

Oracle Business Analytics Warehouse supports the accumulation of usage tracking statistics. For more information about usage tracking, see Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

18.11 About the Incremental Deployment of the Oracle BI Applications Repository

Oracle BI Applications consists of various application families, for example, Supplier Performance Analytics, Contact Center Telephony Analytics, General Ledger and Profitability Analytics, and so on. You can purchase these applications at different times. You can customize functionality and incrementally add new applications.

This section describes the procedure for deploying multiple applications. You can repeat the procedure to add applications incrementally.

Figure 18-2 shows a single Oracle BI Applications environment. During installation, you will be asked to specify the application module(s) you have licensed, and the installer will extract the metadata corresponding to this module into one repository file. You can then modify the Oracle BI Repository to suit your business needs.

Figure 18-2 Oracle Business Analytics Warehouse Environment

When you purchase another Oracle BI Applications application, you need to extract new metadata for all the modules that you have licensed. Use the merge utility in Oracle BI Administration Tool to perform a patch merge. For more information on merging repositories, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

The merged repository preserves your modifications from the original Oracle BI Repository and appends the information with the new Oracle BI Repository, as shown in Figure 18-3.

Figure 18-3 Merging with an Oracle BI Repository

You can repeat this merging procedure to add more Oracle BI applications to the Oracle BI Repository.