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

Executing Oracle Business Analytics Warehouse Exception Reports

Before loading the Oracle Business Analytics Warehouse for the first time and for the subsequent refreshes, you should plan to spend time cleansing your transactional database data using the exception reports. The process is iterative, and requires coordination with other team members who have responsibility for data in the transactional database, such as the Siebel database administrator. After the initial cleansing, you should generate the exception reports on a scheduled basis to maintain the integrity of your data.

NOTE:  Rerunning the exception reports overwrites all data contained in this view.

To cleanse the transactional database, repeat these actions until the exception report is empty:

• In the DAC, run the Exception Reports execution plan.
• In Oracle's Siebel application that you are using, navigate to Analytics Administration > Exception Reports.

  NOTE:  In Siebel Financial Services, this screen is called DataMart Administration.

• For every line in the ETL Exception Reports list, fix the cause of the problem. For information on fixing problems, see Cleansing Data.

Cleansing Data

Use a combination of the Exception Reports and the Diagnostic views to assess changes that need to be made external to the transactional database, and changes to the transactional database directly.

The ETL Exception Reports list contains one record for each exception flagged in the transactional database. The ETL Exception Explanation form, located below the ETL Exception Reports list, describes the selected exception, its effect on the Oracle Business Analytics Warehouse building process, and offers suggestions for repairing the data.

To fix an exception

1. Select an exception record.
2. Read and understand the text in the ETL Exception Explanation form.
3. Click the report link.

   The object's data appears. (For example, if the object is an account, then the Account form appears. If the object is a cost list, then the Cost List list appears.)

4. Repair the problem, using the text in the ETL Exception Explanation form as a guide.
5. Return to the ETL Exception Reports list and place a check mark in the Fixed column to indicate to others that this exception has now been fixed.

Using the List of Values View

Use the List of Values view, shown in Figure 33, to visually compare how the list of values data extracted from the transactional database coordinates with the values loaded into the Oracle Business Analytics Warehouse. The ETL process removes duplicates and overlaps and fills data gaps. Values are extended to span the List of Values (LOV) minimum and maximum values. Duplicates, Range Gaps, and Overlaps are flagged by the exception reports.

Figure 33. List of Values List

The top List of Values list shows values from the transactional database and the bottom List of Values (Data Warehouse) list shows the data that is to be used in ETL process. You can edit the transactional database data directly in this view, but the Oracle Business Analytics Warehouse list is read-only.

NOTE:  The List of Values is extracted into the Oracle Business Analytics Warehouse where the language is the same as the ETL Default Language set in the DAC Source System Parameters, or whose translate flag is set to "N," or those that are active. For more information about setting DAC Source System Parameters, see Configuring Email Recipients in the DAC Client).

Using the Exchange Rates View

Use the Exchange Rates view to diagnose currency translation issues in the Oracle Business Analytics Warehouse. The ETL process removes duplicates, fills gaps, and removes overlaps. The ETL process computes exchange rates based on commutative and associative properties, such as product and reverse rates.

The top Exchange Rates list shows currencies, the middle Exchange Rates list shows the Oracle Business Analytics Warehouse values for active currencies and their exchange rates, and the bottom Exchange Rates (Data Warehouse) list shows the values loaded into the Oracle Business Analytics Warehouse for the selected currency in the upper Exchange Rates list to the ETL Base Exchange Currency. The Exchange Rates (Data Warehouse) list is read-only.

• The Active Currencies predefined query restricts the list to the active currencies in the transactional database.
• The exception reports flag any exchange rates to the ETL Base Exchange Currency that have not been defined within a specified period (30 days) in the DAC Source System Parameters.

Using the Cost List View

Use the Cost List view to display the cost lists from the transactional database from the point of view of the product, and a read-only view of the values to be loaded into the Oracle Business Analytics Warehouse. The ETL process removes duplicates, overlaps, and fills gaps.

The Cost List list (top) shows products, and the Cost List Line Items list (middle) shows the cost lists associated with the selected product. The Cost Lists (Data Warehouse) list (bottom) shows the data as it is transformed for the Oracle Business Analytics Warehouse.

• The exception reports flag products that do not appear in the Cost List list or have Cost List time gaps and overlaps.
• The Oracle Business Analytics Warehouse contains only one Cost List for a product and a currency at a time.

Using the ETL History View

After all of the exceptions are corrected, the building of the data warehouse can be initiated. This view lists the history of the ETL processes and their statuses. When each ETL batch starts, the name of the process along with the timestamp is set, the status is set to STARTED. When the batch completes, its status is updated to COMPLETED.

Additional Exceptions

The above mentioned exceptions are not an exhaustive list of all possible exceptions. Other exceptions are:

• The Analysis start and end date in the DAC Source System Parameters must span the entire period of time during which the transactions have occurred. For example, you may want to choose an early and late date range to cover the entire time period you are analyzing. These dates in the DAC Source System Parameters are crucial for the building of Day Dimension, flattening of Exchange Rates, Cost Lists, and KPI (Key Performance Indicator fact) calculations.
• The DAC Source System Parameters — ETL Date Format, ETL Analysis Start, ETL Analysis End parameters, and the List of Values — ETL_UNSPEC_DATE must be defined in the same data format. If one is changed, the others must be changed accordingly.
• List of Values must be defined appropriately. If there is no appropriate entry in List of Values, the strings that depend on List of Values in the transactional database will not be translated.
• There must be exchange rates defined for the currencies your organization deals with. If the appropriate exchange values are not found, the ETL process uses the ETL Unknown Exchange Rate defined in the DAC Source System Parameters.