| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
 Previous |
 Next |
| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
Previous |
Next |
This chapter describes how to configure the Oracle BI Repository for the Oracle Business Analytics Warehouse.
It contains the following topics:
Section 15.2, "Configuring the Oracle BI Repository Connections"
Section 15.4, "Setting Up Additional Time Series Metrics for Oracle Business Analytics Warehouse"
Section 15.5, "Setting Up Additional Dimension Tables for Oracle Business Analytics Warehouse"
Section 15.9, "About the Security or Visibility Configuration"
Section 15.6, "About the Period Ago Keys for Oracle Business Analytics Warehouse"
Section 15.11, "About Configuring Usage Tracking for Oracle Business Analytics Warehouse"
Section 15.12, "About the Incremental Deployment of the Oracle BI Applications Repository"
Before you can enable Siebel version 6.3 and version 7.5 applications, you must modify the Oracle BI Applications Repository.
To modify the Oracle BI Applications Repository for Siebel version 6.3 and version 7.5 applications
Open the file RepositoryDifferences75_63.xls in the \OracleBI\dwrep folder.
Using the Oracle BI Administration Tool, publish the information in the following tabs:
ITBlocks
LogicalCols
FinCols
Oracle BI Applications Repository uses four databases defined in the Physical layer, as follows:
Oracle Data Warehouse
Oracle EBS OLTP
PeopleSoft OLTP
Siebel OLTP
You need to configure the Oracle BI repository variables and connection pools to connect to your databases, as follows:
For information about configuring connection pools, see Section 15.2.2, "How to Configure Oracle BI Applications Repository Connections".
For information about configuring repository variables, see Section 15.2.3, "How to Configure Oracle BI Applications Repository Variables".
The Oracle Business Analytics Warehouse physical database has two predefined connection pools:
Oracle Business Analytics Warehouse Connection Pool. The Oracle Business Analytics Warehouse Connection Pool is the main connection pool in the Oracle BI Applications Repository. You need to configure this connection pool to connect to your physical data warehouse. The connection is used by the session initialization blocks. You can use this connection pool to set up a dynamic data source name.
Oracle Business Analytics Warehouse Repository Initblocks Connection Pool. Yo u need to configure the Oracle Business Analytics Warehouse Repository Initblocks Connection Pool to connect to the your physical data warehouse. The connection is used by the repository level initialization blocks. Repository level initialization blocks cannot be configured to use the dynamic data source name.
You can also set up dynamic data source names, which allow an Administrator to set one instance of Analytics server to connect to different data warehouses depending on the user. For more information about how to set up dynamic data source names, see Section 15.2.1.1, "How to Configure Dynamic Data Source Names".
You also need to configure the following Static variables:
OLAP_DSN. The value of the OLAP_DSN static variable is set to the data source name for the warehouse database.
OLAP_USER. The value of the OLAP_USER static variable is set to the database user name for the warehouse database.
OLAPTBO. The value of the OLAPTBO static variable is set to the database table owner for the warehouse database.
The PeopleSoft OLTP, Siebel OLTP, and Oracle EBS OLTP databases have two predefined connection pools each. The actual databases in the RPD will depend on the modules licensed by the customer. The connection pools and their functions within each database are listed below.
Oracle EBS OLTP:
Oracle EBS OLTP DBAuth Connection Pool. The Oracle EBS OLTP DBAuth Connection Pool is used if database authentication is required.
Oracle EBS OLTP Connection Pool. The Oracle EBS OLTP Connection Pool is used to connect to the Oracle EBS OLTP system.
You also need to configure the following Static variables:
ORA_EBS_OLTP_DSN. The value of the ORA_EBS_OLTP_DSN static variable is set to the data source name for the Oracle EBS OLTP database.
ORA_EBS_OLTP_USER. The value of the ORA_EBS_OLTP_USER static variable is set to the database user name for the Oracle EBS OLTP database.
Siebel OLTP
Siebel OLTP DBAuth Connection Pool. The Siebel OLTP DBAuth Connection Pool is used if database authentication is required.
Siebel OLTP Connection Pool. The Siebel OLTP Connection Pool is used to connect to the Siebel OLTP system.
You also need to configure the following Static variables:
OLTP_DSN. The value of the OLTP_DSN static variable is set to the data source name for the Siebel OLTP database.
OLTP_USER. The value of the OLTP_USER static variable is set to the database user name for the Siebel OLTP database.
PeopleSoft OLTP
PeopleSoft OLTP DBAuth Connection Pool. The PeopleSoft OLTP DBAuth Connection Pool is used if database authentication is required.
PeopleSoft OLTP Connection Pool. The PeopleSoft OLTP Connection Pool is used to connect to the PeopleSoft OLTP system.
You also need to configure the following Static variables:
OLTP_DSN. The value of the OLTP_DSN static variable is set to the data source name for the PeopleSoft OLTP database.
OLTP_USER. The value of the OLTP_USER static variable is set to the database user name for the PeopleSoft OLTP database.
This sections explains how to create and configure dynamic data source names.
Dynamic data source names allow the Administrator to set one instance of Analytics server to connect to different data warehouses depending on the user. For this you need to have your user authentication based on an external system (like LDAP), and add the following to your repository:
Create new session variables: Session_OLAP_DSN and Session_OLAP_USER
Create a Session Init Block which uses "Oracle Business Analytics Warehouse Repository Initblocks Connection Pool" to populate these session variables based on the user login.
Add this Initialization Block to the Execution Precedence list of the Authorization Initialization block.
Modify the values of Data Source Name and User Name fields in "Oracle Data Warehouse Connection Pool" to be VALUEOF(Session_OLAP_DSN) and VALUEOF(Session_OLAP_USER) respectively.
For the Password field, you should have the user password the same as the user name.
Update the field password with the same value as of User Name.
The section explains how to configure the Oracle BI connection physical databases and connection pools to connect to your database by doing the following:
Configuring the Oracle Data Warehouse, Oracle EBS OLTP, PeopleSoft OLTP, and Siebel OLTP physical databases.
Configuring the Oracle BI Applications Repository connection pools.
To configure the Oracle Data Warehouse, Oracle EBS OLTP, PeopleSoft OLTP, or Siebel OLTP physical databases
Using the Oracle BI Administration Tool, open the OracleBIAnalyticsApps.rpd file in the $SAHome\OracleBI\Server\Repository folder.
In the Physical pane, double-click the Oracle Data Warehouse object.
In the Database list, click your database type.
Save the repository.
Click Yes to Check Global Consistency.
Repeat steps 1-5 for the Oracle EBS OLTP, PeopleSoft OLTP, and Siebel OLTP data warehouse objects
Click OK when the Warnings are displayed.
To configure the Oracle BI Applications Repository connection pools
Using the Oracle BI Administration Tool, open the OracleBIAnalyticsApps.rpd file in the $SAHome\OracleBI\Server\Repository folder.
In the Physical pane:
Double-click the Oracle Data Warehouse Connection Pool within the Oracle Data Warehouse.
Type the database source name in the Data source name box.
Type your database User ID in the User name box.
Type your password in the Password box.
Repeat Steps a. to d. above for the other connection pools listed above.
Save the repository.
Click Yes to Check Global Consistency.
Click OK when the Warnings are displayed.
The section explains how to configure the Oracle BI repository variables.
To configure the Oracle BI Applications Repository variables
Using the Oracle BI Administration Tool, open the OracleBIAnalyticsApps.rpd file in the $SAHome\OracleBI\Server\Repository folder.
On the Manage menu, click Variables.
In the Variables Manager window, click Static.
Edit the OLAP_DSN, OLAP_USER, OLAPTBO, OLTP_DSN, OLTP_USER, ORA_EBS_OLTP_DSN, ORA_EBS_OLTP_USER variables, and close the Variables Manager window.
On the Manage menu, click Security
In the Security Manager window, click Users, and then:
Double-click on Administrator user, and type a new password.
Double-click on SADMIN, and type a new password.
Save and close the Security Manager.
Save the repository.
Click Yes to Check Global Consistency.
Click OK when the Warnings are displayed.
The time dimension in the Oracle BI repository for Oracle Business Analytics Warehouse is a standard or canonical time dimension that links to the 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 15-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 15-1 Invoice Fact Table Example
| Date | Invoice Amount | Fulfill Amount | Payment Amount |
|---|---|---|---|
|
January |
4000 |
5000 |
4500 |
To implement date specific metrics
Open the OracleBIAnalyticsApps.rpd with the Oracle BI Administration Tool.
The OracleBIAnalyticsApps.rpd file is located in the \OracleBI\server\Repository folder.
Right-click on Oracle Business Analytics Warehouse in the Physical layer, 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 repository for Oracle Business Analytics Warehouse is preconfigured with pre-mapped period ago metrics, however you can map other metrics by using the following procedure.
To set up additional time series metrics
Open the OracleBIAnalyticsApps.rpd with the Oracle BI Administration Tool.
Right-click on Oracle Business Analytics Warehouse in the Physical layer, and create a new Period Ago physical alias table.
Create additional tables in the Physical Layer 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 preconfigured to map dimension tables required for analysis. The physical layer in the Oracle BI repository provides several other dimensional 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.
|
Note: Dimension tables do not apply to every source system. |
Open the OracleBIAnalyticsApps.rpd with the Oracle BI Administration Tool.
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.
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 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 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 preconfigured with variables that are used for both reporting and internal usage.
Table 15-2 lists the Oracle BI repository date variables and their descriptions.
Table 15-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. |
You need to configure the user authentication of the Oracle Business Analytics Warehouse repository depending on your requirements. The Oracle Business Analytics Warehouse supports various authentication modes, for example, Repository authentication, Database authentication, and LDAP.
For more information about configuring user authentication, see Oracle Business Intelligence Server Administration Guide.
The Oracle Business Analytics Warehouse repository is preconfigured with a set of user groups. These groups control the visibility of catalogs in the presentation layer.
For more information on adding a user to repository user group, see Oracle Business Intelligence Server Administration Guide.
Table 15-3 lists the groups in the Oracle Business Analytics Warehouse repository.
Table 15-3 Repository User Groups
| Repository User Group | Description |
|---|---|
|
Administrators |
The Administrators user group has all rights and privileges. It cannot be removed. |
|
Agent Scorecard User |
This user group is able to view Agent Scorecard application content. |
|
AP Analyst |
This user group is able to view application content for Oracle Payables Analytics. |
|
AP Manager |
This user group is able to view high-level application content for Oracle Payables Analytics. |
|
AR Analyst |
This user group is able to view application content for Oracle Receivables Analytics. |
|
AR Manager |
This user group is able to view high-level application content for Oracle Receivables Analytics. |
|
CFO |
This user group is able to view most of the Oracle Financial Analytics application content. |
|
Contact Center and Agent Performance Analyst |
This user group is able to view Contact Center Telephony Analytics and Agent Performance application content. |
|
Contact Center and Agent Performance User |
This user group is able to view a subset of Contact Center Telephony Analytics and Agent Performance application content. |
|
Contact Center Sales Analyst |
This user group is able to view Contact Center Telephony Analytics and Order Management Analytics application content. |
|
Contact Center Sales User |
This user group is able to view a subset of Contact Center Telephony Analytics and Order Management Analytics application content. |
|
Controller |
This user group is able to view application content for Oracle General Ledger and Profitability Analytics and Siebel Profitability Analytics. |
|
Customer Service Analyst |
This user group is able to view Customer Service for Oracle Contact Center Telephony Analytics application content. |
|
Customer Service User |
This user group is able to view a subset of Customer Service for Oracle BI Contact Center Telephony Analytics application content. |
|
Contact Center Telephony Analytics User |
This user group is able to view Oracle BI Contact Center Telephony Analytics application content. |
|
Financial Analyst |
This user group is able to view Oracle Financial Analytics application content. |
|
Human Resources Analyst |
This user group is able to view Oracle HR Analytics application content. |
|
Human Resources Vice President |
This user group is able to view high-level application content for Oracle HR Analytics application. |
|
Inventory Analyst |
This user group is able to view application content for Oracle's Supply Chain Analytics family of products (Oracle Inventory Analytics, Oracle Procurement and Spend Analytics, Oracle Supplier Performance Analytics). |
|
Inventory Manager |
This user group is able to view high-level application content for Oracle's Supply Chain 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 user group is able to view Oracle's Supply Chain Analytics family of products (Oracle Inventory Analytics, Oracle Procurement and Spend Analytics, Oracle Supplier Performance Analytics) content pertaining to purchasing. |
|
Sales Executive Analytics |
This user group is able to view high-level application content for the Order Management Analytics application. |
|
Sales Manager |
This user group is able to view most of the high-level application content for Oracle BI Contact Center Telephony Analytics application. |
|
Sales Manager Analytics |
This user group is able to view most of the high-level application content for Oracle BI Contact Center Telephony Analytics application. |
|
Sales Operations Analytics |
This user group is able to view operational application content for Oracle BI Contact Center Telephony Analytics application. |
|
Sales Representative Analytics |
This user group is able to view low-level application content for Oracle BI Contact Center Telephony Analytics application. |
|
Sales Rev and Fulfill Analyst |
This user group is able to view the content for Oracle BI Contact Center Telephony Analytics Revenue and Fulfillment application. |
|
Sales Rev and Fulfill Exec |
This user group 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 user group 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 user group is able to view low-level application content for Order Management Analytics Revenue and Fulfillment application. |
|
Sales Revenue Analyst |
This user group is able to view the content for Oracle BI Contact Center Telephony Analytics Revenue application. |
|
Sales Revenue Exec |
This user group is able to view the high-level application content for Oracle BI Contact Center Telephony Analytics Revenue application. |
|
Sales Revenue Mgr |
This user group is able to view most of the high-level application content for Oracle BI Contact Center Telephony Analytics Revenue application. |
|
Sales Revenue Rep |
This user group is able to view low-level application content for Oracle BI Contact Center Telephony Analytics Revenue application. |
|
Service Delivery and Costs Analyst |
This user group is able to view Service Delivery and Costs for Oracle BI Contact Center Telephony Analytics application content. |
|
Service Delivery and Costs User |
This user group is able to view a subset of Service Delivery and Costs for Oracle BI Contact Center Telephony Analytics application content. |
|
Supplier Performance Analyst |
This user group is able to view Oracle's Supply Chain 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 user group is able to view high-level content for Oracle's Supply Chain 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 user group is able to view Oracle's Supply Chain Analytics family of products (Oracle Inventory Analytics, Oracle Procurement and Spend Analytics, Oracle Supplier Performance Analytics) content. |
The Group variable determines the membership of a user in the various security groups. You need to associate users to the appropriate groups defined in the OracleBIAnalyticsApps.rpd for the security filters to take effect.
If you are using the Oracle BI Applications with the Siebel CRM system, then you can leverage the Authorization session init 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 about configuring the Group variable, see Oracle Business Intelligence Presentation Services Administration Guide. The screen shot below shows an example of an initialization block that associates a user to a Group membership.
Oracle Business Analytics Warehouse supports the accumulation of usage tracking statistics. The Oracle BI repository for Oracle Business Analytics Warehouse is preconfigured with a connection pool to enable the population of the Usage Tracking log table.
You need to configure this connection pool to connect to the S_NQ_ACCT table. For more information the Usage Tracking application administering Usage Tracking, see the Oracle Business Intelligence Server Administration Guide.
Oracle Business Analytics Warehouse consist of various families of Oracle BI Applications, for example, Supplier Performance Analytics, Contact Center Telephony Analytics, General Ledger & Profitability Analytics, and so on. You can purchase these applications at different times. You can customize functionality and incrementally add new application or 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 Business Analytics Warehouse application environment. During installation, you will be asked to specify the application module(s) you have licensed, and the installer will extract the metadata project(s) corresponding to this module(s) into one repository file. You can then modify the Oracle BI repository to suit your business needs.
When you purchase another Oracle Business Analytics Warehouse application, you need to extract a new application repository, containing the metadata for all the modules that you have licensed. Use the Administration merge utility 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.
You can repeat this merging procedure to add more Oracle Business Analytics Warehouse applications to the Oracle BI repository
