A Troubleshooting

This appendix contains the following topics:

Deleting Control Table Entries
Sorting and Displaying of Null Values in Reports
Cancelling Jobs in Oracle Life Sciences Data Hub
Errors in Reports

Deleting Control Table Entries

The Control Table (W_CONTROL_S) contains a record for each ETL execution of a specific target table. The record stores a start and end timestamp. That is, source records were extracted only if their creation or modification timestamp was between the start and end timestamps specified in the Control Table record for a given ETL mapping execution.

There are occasions when it is desirable to be able to delete selected entries from the Control Table. This is useful when you want to:

Delete all the entries from the control table for full load.
Delete selected entries from the control table based on the different input options. This will help when new columns are added and data needs to be reloaded into a selected dimension or fact.

Accordingly, the Control Table population program has been enhanced to support deletion of entries from control table, while supporting its original function of populating the control table.

Navigate to OCDA_domain > OCDA_SOURCES_APP_AREA > OCDA_CONTROL_TABLE_WA.
Click OCDA_CONTROL_TABLE_POPULATE_PRG.
Submit the program with the following parameters:

Submission Details

Submission Type: Immediate

Submission Mode: Incremental

Force Execution: Yes

Submission Parameters

Following are input parameters for the control table populate program that are used to delete rows:
1. Delete_mode parameter has a list of values used for mode of deletion. Below are the values in the list and description of operation performed.
  
  None - Performs a normal operation on the control table, that is, populates the table.
  
  All - Deletes all the entries in the control table.
  
  ETL_RUN_ID - This requires a list of comma seperated ETL_RUN_ID in the input_values field. When submitted, it will delete entries corresponding to input_values.
  
  MASTER_JOB_ID - This requires a list of comma seperated Master_Job_Id in the input_values field. When submitted, it will delete entries corresponding to input_values.
  
  Program_Name - This requires a list of comma seperated Program_Names in the input_values field. When submitted, it will delete entries corresponding to input_values.
2. Input_values parameter takes a comma-separated list of values that need to be deleted.

Sorting and Displaying of Null Values in Reports

In order to understand results shown in OBIEE reports, it may be necessary to understand how null values are sorted and displayed in reports.

Oracle uses NULL as a pseudo-value for a table cell when there is no actual value. For example, if the number of documents awaiting completion for a site is unknown, the column containing that attribute of the site will be set to null in the database.

As null values can appear in among data, OBIEE has rules that determine how to display the null values. And as OBIEE supports sorting of data in a column, it has rules for how nulls should be sorted.

The following are the rules:

Oracle's sorting order cause a null value to be treated as greater than any non-null value.
In table views, OBIEE generally displays null values as empty cells.

The exception is when the request designer has specified that the user can navigate to a different request by clicking on a value in the column that contains null. In that case, in order to give the user something to click on, OBIEE displays the null value as a zero.

These rules can produce unexpected results. This following section describes how to interpret such unexpected results. It also describes actions you can take in creating OBIEE requests to override OBIEE's default rules.

The results of these rules are:

If the data in a column contain nulls and non-nulls, and the column is sorted, and navigation is not enabled from cells in the column, then (i) nulls will display as blank cells, and (ii) the blank cells will sort as larger than the largest non-null value.

If the data in a column contain nulls and non-nulls, and the column is sorted, and navigation is enabled from cells in the column, then (i) nulls will display as zeros, (ii) the cells representing nulls (but now displaying as zeros) will sort as larger than the largest non-null value. If there are actual zeros in the column as well, they will sort as smaller than the smallest positive value in the column. So, if you have both real zero values and null values, and cell-based navigation is enabled, and you sort the column, you will get two clumps of zeros - one representing the nulls, the other representing the actual zeros - separated by the non-negative actual values.

OBIEE does have a capability that can be used to make it easier to identify null values. In requests, you can use the IFNULL function to specify that NULL should be replaced by a large negative value that could not be a "real" value for the column. For instance, if "# Documents Outstanding" could be null in your data, and you want to include it in a request, you could change the functional definition of the column in the request from "# DocumentsOutstanding" to IFNULL("# Documents Outstanding", -99). This would cause nulls to sort and display as if their value was -99.

If you use IFNULL, it is important that you:

Choose a value that could not also be a legitimate value (this may vary from column to column, though it is preferable to use the same IFNULL replacement across all columns).
Communicate to your end users the meaning of the IFNULL values.

Cancelling Jobs in Oracle Life Sciences Data Hub

Cancelling jobs (Informatica programs) submitted in Oracle LSH does not automatically abort the workflow in Informatica PowerCenter. This needs to be done manually. To abort the workflow:

Identify the folder that contains the particular workflow. For more information, refer to Identifying the Folder Containing the Workflow.
Abort the workflow in Informatica PowerCenter. For more information, refer to Aborting a Workflow.

Identifying the Folder Containing the Workflow

Use the particular job's command log file in Oracle LSH (cmdlog.log), to identify the Informatica folder that contains the workflow. Perform the following steps in Oracle LSH to view the log file:

In the Job Execution section of My Home, click the Job ID hyperlink of the particular job you want to cancel.

The Job Execution Details screen is displayed. The Master Job Id field displays the system-generated unique ID of the job that calls this job (parent job).
Click Outputs, and then click the hyperlink in the View column.
Choose to save or open the command log file (cmdlog.log).

Errors in Reports

Error

CDA reports return any of the following errors:

Assertion failure
Invalid arithmetic operation on non numeric type

[nQSError: 22019] Function Median does not support non-numeric types

[nQSError: 22025] Function TimestampDiff is called with an incompatible type
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113]

Cause

The source of the problem may be the warehouse table W_ETL_RUN_S. This table should contain a row for each execution of the ETL that updates the warehouse. Many reports in CDA depend on the value of the dynamic Repository variable CURRENT_DAY. This Repository variable, in turn, depends on the maximum value of the column LOAD_DT in W_ETL_RUN_S. The value of CURRENT_DAY is updated every 5 minutes by an initialization block in the CDA Repository. If W_ETL_RUN_S does not contain any values for LOAD_DT, then CURRENT_DAY will be null, and the many reports that depend on it will fail with errors such as those shown above.

If the ETL for CDA is working correctly, a row should be added to W_ETL_RUN_S each time an ETL run completes successfully, and these errors will not occur. However, especially during initial setup and load, a partially complete ETL run will load some data, but not set the value of LOAD_DT. Also, test data is loaded directly into a warehouse, and the test data does not supply rows for W_ETL_RUN_S, the conditions will be set for these assertion failures.

Fix

To fix the above errors, perform the following steps:

In the warehouse, check that W_ETL_RUN_S has at least one row, and that the value of LOAD_DT is not null in the rows that are present.
If W_ETL_RUN_S already has values for LOAD_DT, it is not the cause of the errors. Skip the remaining steps in this list and look elsewhere for the cause of the errors.
If W_ETL_RUN_S has no values for LOAD_DT, correct it, if necessary, by re-executing the ETL in incremental mode.
Exit the CDA OBIEE Presentation Server web page.
Restart the CDA OBIEE Presentation Server and BI Server , and log in.
Wait 5 minutes, to ensure that CURRENT_DAY is updated from the corrected W_ETL_RUN_S.
View a dashboad page where the error had occurred. If the error was due to missing LOAD_DT values, the report should now show results or a (No Results) message, if there is no data for the report.