How to Cancel running query in OBIEE

OBIEE support cancel on all (or all the major ones) sources. It calls the appropriate cancellation method for each product. For example, for DB2 it call SQLCancel(). For Oracle it call OCIBreak(). 

The only automated control OBIEE has for cancelling request queries is via the Admin tools' Permissions > Query Limits 'Max rows' and 'Max Time' options. Once either of these limits has been reached, OBIS will send a cancel call to the source database. A user may also explicitly cancel a running request as well. Whether or not the cancel is effective on the database is very much a timing issue. For example, if the cancel call is received while the request is in a fetch state, then most databases will ignore it, since it would typically take longer to back out the transaction and cancel, then to allow it to complete.

And, whether the cancel call is received by the database in time to effectively cancel the request depends on several factors as well, like the complexity of the request, and the speed of the OBIS server and / or the database server. For example, it does take time for OBIS to act on a request that has exceeded a repository-defined query limit. During that time, the database is still processing the request. Pending the factors mentioned earlier, the request may have passed the point where the database sees cancellation as a viable option."

Additionally, when a user executes a cancel command in a Presentation Server session a cancel request is sent to the BI Server, which then sends the appropriate cancel call to the source (OCIBreak for Oracle).

[nQSError: 14025] No fact table exists at the requested level of detail

One of the most common error message that is seen while creating reports is shown below.



The first thing we check in case of this error is whether we have given proper levels in the content tab. Sometimes levels were not properly given in rpd and correcting it solves the problem. But this might not be the case in all scenarios. One such scenario is explained below.

I am using oracle HR schema for the demo purpose.

Physical Diagram

BMM Diagram

Dim - Employee is sourced from the physical tables Fact_Employee & Dim_Department. This is done so as to bring the employee information and department information in a single logical table.

Fact - Employee is sourced from Fact_Employee.
Dim - Location is sourced from Dim_Location which contains the geographic information.

Now lets try creating some reports.

 The above mentioned criteria gives the desired result.

Now include the location information as well in the analysis.

Now when we click on the Results tab, we  are not getting the desired result, instead we receive an error message stating " No fact table exists at the requested level of detail".

We received this error message after including a column from the Dim - Location table. Now when the logical request is sent to BI server, BI server gets is unable to find join between Dim_Location and Fact_Employee. The join between Location and Employee happens via Department table. So we should include this information in design so that BI server is able to generate proper physical query for the incoming logical request.

Add Dim_Department as an LTS to the Dim - Location table.

Check in the changes in rpd and try creating the report again. After making the above mentioned change, BI server will now be able to understand how to create join between Dim - Location and Fact - Employee.

Include custom links in OBIEE Global Header

By default, the global header in Oracle BIEE contains menus and options that allow you to navigate easily among features. You might like to customize the global header and the Get Started section of the Home page to better meet the needs of users by disabling certain links or including your own links. Changes that you make to the Get Started section do not affect the Help menu in the global header. For custom links, you can specify various attributes, including the following:

·         The text for the link (either a static string or a message name to use for localization).

·         A URL to access.

·         Whether the page from the URL replaces the current page or opens in a new tab or window that you can name.

·         The relative ordering of links in the header.

Update the customlinks.xml file to specify customizations to the global header

1)      To include Gmail before Catalog link, insert the below mentioned code in the customlinks.xml (ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obipsn)

<link id="l5" name="Gmail" description="gmail" src="http://www.gmail.com" target="blank" iconLarge="common/gmail.png" >

   <locations>

      <location name="getstarted"> /

      <location name="header" insertBefore="catalog" />

   </locations>

</link>

Before custom links are visible on the Home page, you must edit the instanceconfig.xml file to include the CustomLinks element and the Enabled element within it (whose default is true).

2)      Open the instanceconfig.xml file for editing

3)      Search for the ServerInstance section, in which you must add the CustomLinks element.

4)      Include the elements and their ancestor elements as appropriate, as shown in the following example:

<ServerInstance>

   <CustomLinks>

      <Enabled>true</Enabled>

      <filePath>c:/mydir/mysubdir/customlinks.xml</filePath>

   </CustomLinks>

</ServerInstance>

5)      Save your changes and close the file.

6)      Restart Oracle Business Intelligence.

Scheduler tables in OBIEE 11g

Oracle BI Scheduler is a server that manages and schedules jobs. When a user creates and schedules an agent, Oracle BI Presentation Services gathers information about the agent such as its priority, the intended recipients, and the devices to which content should be delivered. Presentation Services packages this information and other characteristics into a job, then informs Oracle BI Scheduler when to execute the job.

Agents can run in parallel on different threads. The number of agents that can run in parallel depends on the size of the Scheduler thread pool (a configurable setting) and the number of threads used up by each agent. Queueing might occur if too many agents are triggered at the same time.

Oracle BI Scheduler uses a single back-end database to store pertinent information about a job, its instances, and its parameters.

The details about the scheduler tables are mentioned below :

S_NQ_JOB - This table is used by Scheduler to store information about scheduled jobs. That means when you create a new agent from OBIEE, an entry corresponding to it get created in S_NQ_JOB table. This table is stored in the BI_PLATFORM schema, therefore to access it; you should have access to BI_PLATFORM schema.

Some of the columns in the table are mentioned below 

Column Name	Description
JOB_ID	It is unique identifier for each agent
NAME	Name of Agent
NEXT_RUN_TIME_TS	Next scheduled runtime of agent
LAST_RUN_TIME_TS	Last runtime of the agent

S_NQ_INSTANCE – This table stores information about scheduled job instances. For a job in the S_NQ_JOB table, there will be multiple entries in the S_NQ_INSTANCE table based on the agent run.

Column Name	Description
JOB_ID	Identifier for the job populated from S_NQ_JOB
INSTANCE_ID	Unique identifier for each instance
STATUS	Shows the status of the agent 0 - Completed 1 - Running 2 - Failed 3 - Cancelled 5 - Timed out
BEGIN_TS	Start of the instance
END_TS	End of instance
EXIT_CODE	Number of e-mails sent by an Agent job, after the job is completed

Relation with  S_NQ_JOB

S_NQ_JOB.JOB_ID = S_NQ_INSTANCE.JOB_ID

S_NQ_ERR_MSG - This table stores error messages for Scheduler job instances that do not complete successfully. 

Column Name	Description
JOB_ID	Same as in S_NQ_JOB & S_NQ_INSTANCE
INSTANCE_ID	Same as S_NQ_INSTANCE
ERROR_MSG_TXT	Displays the error message in agent failure. This message is same as the message in Agent.log file.

Relation with  S_NQ_INSTANCE

S_NQ_INSTANCE.JOB_ID  =  S_NQ_ERR_MSG.JOB_ID

AND  S_NQ_INSTANCE.INSTANCE_ID  =  S_NQ_ERR_MSG.INSTANCE_ID

 

S_NQ_JOB_PARAM - This table holds information about Scheduler job parameters for scheduled jobs.

Relation with  S_NQ_JOB

S_NQ_JOB.JOB_ID = S_NQ_JOB_PARAM.JOB_ID