Oracle® Fusion Middleware Reference Guide for Oracle Business Intelligence Applications 11g Release 1 (11.1.1) Part Number E16816-05 |
|
|
PDF · Mobi · ePub |
This chapter describes how to configure the Oracle BI Repository for use with Oracle BI Applications.
This chapter contains the following topics:
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.5, "About the Period Ago Keys for Oracle Business Analytics Warehouse"
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"
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
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
In the Physical layer, right-click on 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.
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
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
In the Physical layer, right-click on Oracle Data Warehouse, and create a new Period Ago physical alias table.
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.
Change the join to the date dimension (W_DAY_D) to use the appropriate Period Ago Key.
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.
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.
Save and close the OracleBIAnalyticsApps.rpd file.
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
Validate that the dimension table key is resolved appropriately for the data source that you are using.
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
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.
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:
Using Oracle BI Administration Tool, open OracleBIAnalyticsApps.rpd.
Create a link with the W_PRODUCT_D table's PROD_CATn_WID column.
Create the new Logical Layer Dimension and Logical Layer Dimension Hierarchy.
Add the new columns to the Presentation Layer.
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.
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: Repository variables with _OTBI and _OBIA should not be directly used. They are only used to switch between Oracle Business Intelligence Applications and Oracle Transactional Business Intelligence 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. |
Oracle Business Analytics Warehouse supports the accumulation of usage tracking statistics. For more information on the Usage Tracking application, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition (Oracle Fusion Applications Edition) .
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.
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 Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition (Oracle Fusion Applications 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 the figure below.
You can repeat this merging procedure to add more Oracle BI applications to the Oracle BI Repository.