6 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 6.1, "How to Set Up Date-Specific Metrics"

• Section 6.2, "How to Set Up Additional Time Series Metrics for Oracle Business Analytics Warehouse"

• Section 6.3, "How to Set Up Additional Dimension Tables for Oracle Business Analytics Warehouse"

• Section 6.4, "How to Set Up Product Category Hierarchies"

• Section 6.5, "About the Period Ago Keys for Oracle Business Analytics Warehouse"

• Section 6.6, "About Oracle BI Time Repository Variables"

• Section 6.7, "About Configuring Usage Tracking for Oracle Business Analytics Warehouse"

• Section 6.8, "About the Incremental Deployment of the Oracle BI Applications Repository"

6.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 needs to 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 6-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 6-1 Invoice Fact Table Example

Date	Invoice Amount	Fulfill Amount	Payment Amount
January	4000	5000	4500

To implement date-specific metrics

1. Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.

2. In the Physical layer, right-click on Oracle Data Warehouse, and create a new physical alias for the fact table.

3. 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.

4. 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.

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

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

To set up additional time series metrics

1. Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.

2. In the Physical layer, right-click on Oracle Data Warehouse, and create a new Period Ago physical alias table.

3. In the Physical layer, create additional tables for each Period 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.

4. Change the join to the date dimension (W_DAY_D) to use the appropriate Period Ago Key.

5. Map the Period Ago metrics in the logical table using the new fact alias by creating a new logical table source under the fact table.

6. Set the content pane levels for the period ago logical table source, to specify the level of the source data.

   These settings are the same as the base fact table.

7. Save and close the OracleBIAnalyticsApps.rpd file.

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

Oracle Business Analytics Warehouse is pre-configured 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 dimensions tables to the Physical layer, perform the following procedure.

To set up additional dimension tables

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

2. Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.

3. Add a dimension table alias in the Physical layer.

4. Join the dimension table alias to the fact table alias using the appropriate keys.

5. Save and close the OracleBIAnalyticsApps.rpd file.

6.4 How to Set Up Product Category Hierarchies

In Oracle Business Intelligence Applications, the following three hierarchies are supplied out-of-the-box:

• General Hierarchy

• Purchasing Hierarchy

• UNSPSC Hierarchy

To customize Product Category Hierarchies:

1. Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.

2. Create a link with the W_PRODUCT_D table's PROD_CATn_WID column.

   
   

3. Create the new Logical Layer Dimension and Logical Layer Dimension Hierarchy.

   
   

4. Add the new columns to the Presentation Layer.

   
   

6.5 About the Period Ago Keys for Oracle Business Analytics Warehouse

The Period Ago Key fields are used to set up the time series metrics like Year Ago, Quarter Ago, and so on. The Period 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 pre-configured 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 period ago metrics. The joins in Oracle Business Analytics Warehouse uses the Period Ago fields in the W_DAY_D table.

6.6 About Oracle BI Time Repository Variables

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

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

Note: RPD variables with _OTBI and _OBIA should not be directly used. They are only used to switch between OBIA and OTBI sourcing.

Table 6-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.

6.7 About Configuring Usage Tracking for Oracle Business Analytics Warehouse

Oracle Business Analytics Warehouse supports the accumulation of usage tracking statistics. For more information on the Usage Tracking application, see the Oracle Business Intelligence Server Administration Guide.

6.8 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.

The figure below 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 6-1 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 three-way merge of the original repository, the modified repository, and the combined repository. For more information on merging repositories, see Oracle Business Intelligence Server Administration Guide.

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 the figure below.

Figure 6-2 Merging with an Oracle BI Repository

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