C Troubleshooting

This appendix contains the following topics:

Sorting and Displaying of Null Values in Reports
Cancelling Jobs in Oracle Life Sciences Data Hub

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).