Bookshelf Home | Contents | Index | Search | PDF

Siebel Data Warehouse Installation and Administration Guide > Setting System Preferences and Using Exception Reports > Using the Exception Report and Diagnostic Views >

Executing Exception Reports

---

Before loading the Siebel Data Warehouse for the first time and for the subsequent refreshes, you should plan to spend time cleansing your Siebel 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 Siebel 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 Siebel transactional database, repeat these actions until the exception report is empty:

• In Informatica Workflow Manager, run the Exception_Reports batch.
• In the Siebel user interface, 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 Siebel transactional database, and changes to the Siebel transactional database directly.

The ETL Exception Reports list contains one record for each exception flagged in the Siebel transactional database. The ETL Exception Explanation form, located below the ETL Exception Reports list, describes the selected exception, its effect on the Siebel Data 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 5, to visually compare how the list of values data extracted from the Siebel transactional database coordinates with the values loaded into the Siebel Data 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 5.  List of Values List

The top List of Values list shows values from the Siebel 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 Siebel transactional database data directly in this view, but the Siebel Data Warehouse list is read-only.

NOTE:  The List of Values is extracted into the Siebel Data Warehouse where the language is the same as the ETL Default Language set in the System Preferences, or whose translate flag is set to "N," or those that are active.

Using the Exchange Rates View

Use the Exchange Rates view to diagnose currency translation issues in the Siebel Data 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 Siebel Data Warehouse values for active currencies and their exchange rates. The bottom Exchange Rates (Data Warehouse) list shows the values loaded into the Siebel Data 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 Siebel transactional database.
• The exception reports flag any exchange rates to the ETL Base Exchange Currency that have not been refined within a specified period (30 days).

Using the Cost List View

Use the Cost List view to display the cost lists from the Siebel transactional database from the point of view of the product, and as a read-only view of the values to be loaded into the Siebel Data 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 Siebel Data Warehouse.

• The exception reports flag products that do not appear in the Cost List list or have Cost List time gaps and overlaps.
• The Siebel Data 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, and 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 System Preferences (Application Administration > System Preferences) 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 System Preferences are crucial for the building of Day Dimension, flattening of Exchange Rates, Cost Lists, and KPI (Key Performance Indicator fact) calculations.
• The System Preferences — 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 Siebel 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 System Preferences.

Bookshelf Home | Contents | Index | Search | PDF