Oracle^® Business Intelligence Applications Installation and Configuration Guide > Using Oracle Business Analytics Warehouse Exception Reports With Siebel CRM Sources >

Understanding Oracle Business Analytics Warehouse Exceptions

Exception reports are defined for the following components:

• List of Values. Identifies gaps and overlaps for certain LOV types.
• Cost Lists. Identifies products for which the cost lists have not been defined. Identifies the cost lists which define costs for a product in a certain currency for overlapping periods of time.
• Exchange Rates. Currency Exchange rates that do not change over a period of time. If exchange rates are not defined for more than 30-day intervals, then they are flagged as an exception.
• Hierarchies. Entities that have circular references are flagged as exceptions. The Oracle Business Analytics Warehouse supports 10 levels of hierarchies. If there are entities that have more than 10 levels of hierarchies defined, they are flagged as exceptions.

List of Values Exceptions

List of Values include High and Low values that can be used as bucket values in categories for effective analysis. If these values are not contiguous (such as gaps or overlaps in defined values), the ETL process cannot accurately categorize the values.

An example of List of Values exceptions is shown in Table 88.

Notice that, in the example for Type MY_TYPE, there are overlaps and gaps between records. There is an overlap of ranges between the first and second row. There is a gap between second and third row, and between third and fourth rows.

The following LOV types are analyzed for List of Value Exceptions:

• ACCNT_REVENUE_SIZE
• ACCNT_EMP_SIZE
• LEAD_AGE_DAYS
• OPTY_REVENUE_SIZE
• OPTY_UNIT_SIZE
• ACCNT_REVENUE
• QUOTE_AGE_DAYS
• ACCNT_REVN_GROWTH
• APPROVAL_AUTH_SIZE
• SR_CHART_AGE
• ASSET_COST_CATEGORY

Cost List Exceptions

Cost Lists for specified products and currency should not have overlapping time periods. If multiple cost lists are defined for a product and currency during a given time period, then the cost for the product may not be computed correctly in the Oracle Business Analytics Warehouse.

An example of Cost List exceptions is shown in Table 89.

In the example, Cost List 1 and 2 have definitions of cost overlapping over 06-01-2000 to 12-31-2000.

Products Without a Cost List

During the ETL process, the costs of the products are calculated based on the Cost List table. If the cost lists are not defined correctly, the cost of the products cannot be calculated correctly in the Oracle Business Analytics Warehouse. This exception mapping queries the product table and looks for a minimum of one cost list to be defined. The products with no cost list definition are flagged as exceptions.

Exchange Rate Exceptions

The Oracle Business Analytics Warehouse supports transactions in many different currencies. Oracle Business Intelligence converts all currencies in the Oracle Business Analytics Warehouse to a single currency for analysis purposes. The ETL Base Exchange Currency parameter in System Preferences, indicates the currency to which all the financial amounts will be converted. The Exchange rates are derived from the Exchange Rate tables in the transactional database. If the currency exchange rates do not change for a period of 30 days, then Oracle Business Intelligence flags it as an exception.

If there are time period gaps in the exchange rate data, the ETL process defaults to the most recent recorded exchange rate. If the actual exchange rate is significantly more or less favorable than what is recorded in the database, the outdated exchange rate distorts the true value of currency amounts in the Oracle Business Analytics Warehouse.

NOTE:  Exchange rates are derived from records that are of type "Daily" in the transactional database. If any other types have been defined, they are not handled without some customization.

Invalid Hierarchy Exceptions

Accounts, divisions, products, and opportunities can all have hierarchical relationships. These entities are denormalized within the Oracle Business Analytics Warehouse database to a fixed number of levels. Oracle Business Intelligence supports up to ten hierarchies levels in the transactional database. If the depth of hierarchies extends beyond this number, results become inconsistent or incomplete.

Circular Hierarchy Exceptions

Circular Hierarchies arise when the parent-child relationship has circular references. See Table 90 for an example.

Oracle Business Intelligence flags exceptions for two levels. Circular references over two hierarchies are not flagged. See Table 91 for an example.

These produce infinite levels of hierarchies. The same records will be captured under the Invalid Hierarchy exceptions as their hierarchy depths will increase beyond 10 levels.