Category: Business Intelligence (BI) Development

‘No fact table exists at the requested level’ error in OBIEE 11g

If you receive an error like this when you attempt to run a report, this post might help you.  This is an error that may occur after upgrading from OBIEE 10g to 11g.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 14025] No fact table exists at the requested level of detail:

Check each column used in your report and each column used in the filter definition to ensure that they are referencing the correct table.

A likely cause of this error is that one or more of your select columns or filter columns is referencing a column in a different subject area that the one being used in your report (different from the one you intended).

OBIEE 10g was more forgiving than 11g – and maybe fortunately, so that errors like these do not go unnoticed/undetected.

Using Custom Images in OBIEE 10g & 11g

In both OBIEE 10g and 11g, custom images need to be placed in 2 folders before use within reports/dashboards.

 In OBIEE 10g, images need to be placed in:

(1)     $BIHOMEoc4j_bij2eehomeapplicationsanalyticsanalyticsress_oracle10images

Windows directory path example:
C:OracleBIoc4j_bij2eehomeapplicationsanalyticsanalyticsress_oracle10images

Linux directory path example:
/usr/local/OracleBI/oc4j_bi/j2ee/home/applications/analytics/analytics/res/s_oracle10/images

-and-

(2)    $BI_HOMEwebappress_oracle10images

Windows directory path example:
C:OracleBIwebappress_oracle10images

Linux directory path example:
/usr/local/OracleBI/web/app/res/s_oracle10/images

 

In OBIEE 11g, images need to be placed in:

(1)    $OBIEE_FMW_HOMEuser_projectsdomainsbifoundation_domainserversbi_server1tmp_WL_useranalytics_11.1.17dezjlwarress_blafpimages

Windows directory path example:
C:obiee11guser_projectsdomainsbifoundation_domainserversAdminServertmp_WL_useranalytics_11.1.1silp1vwarress_blafpimages

Linux directory path example:
/u01/product/middleware/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res/s_blafp/images

-and-

(2)    $OBIEE_FMW_HOMEOracle_BIbifoundationwebappress_blafpimages

Windows directory path example:
C:obiee11gOracle_BI1bifoundationwebappress_blafpimages

Linux directory path example:
/u01/product/middleware/Oracle_BI1/bifoundation/web/app/res/s_blafp/images

Note: you may also create or use directories at the same level or below the images directories under the s_oracle10 (in 10g) and s_blafp (in 11g) directories, for example, if you wanted to put all your custom images into a separate directory.

Then in Answers / Analysis, you would reference the image as shown below.

Edit the Column Properties:
ImagesDirectories-ColumnProperties

Then, in the Style tab, within the Cell area, click on the Image object…

ImagesDirectories-ClickImage

Then in the Graphics dialog, select “Custom Image” and enter  fmap:images/your_custom_image.jpg.

Note: If your images are in a directory other than the images directory, you need to supply that path, but still starting below s_oracle10 or s_blafp directories.

And set the Image Placement property. Images Left (shows the Image to the left of the column data), Images Right (displays the image to the right of the column data), Images Only (displays the image only – no column data)
ImagesDirectories-ImageNameAndPlacement

If new images were placed into the directories after the last BI Server / Presentation Server restart, you will need to restart the BI Server and Presentation Server for the images to be displayed.

EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set

If you use the Oracle EVALUATE function in OBIEE, then you will need to set a parameter in the NQSConfig.INI file in OBIEE 11g.

If you get this error below, then you are using the EVALUATE function somewhere inside OBIEE (in one of your analyses/requests) and the parameter needs to be set appropriately in the NQSConfig.INI file.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)

This is what you need to do to correct the problem… 

Edit the NQSConfig.INI file, located at: OBIEE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn

This is what you will see by default in the config file…
————-
# EVALUATE_SUPPORT_LEVEL:
# 1: evaluate is supported for users with manageRepositories permssion
# 2: evaluate is supported for any user.
# other: evaluate is not supported if the value is anything else.
EVALUATE_SUPPORT_LEVEL = 0; 
————-

Change the parameter as follows: EVALUATE_SUPPORT_LEVEL = 2;

Save and restart the BI Server.  Your requests should work fine now.

OBIEE 10g: Unable to sort table although “Enable column sorting in dashboards” is checked

I discovered an interesting scenario recently. Although “Enable column sorting in dashboards” was checked (turned on), I was unable to sort on a particular table.  The mouse icon changed when I moused-over the column headings which gave the impression that it would sort, but it simply did not work.

Turns out there was an invalid “view” above the Table object in the Compound Layout. This happens when an object is removed from an analysis/request and its placeholder is not removed from the Compound Layout. See the screenshot below – notice the empty placeholder above the table with the message “The view that was in this position the the Compound Layout is no longer available. …”.

To fix the problem, simply remove/delete that invalid view from the compound layout and you will now be able to sort the table.

The difference between using logical columns and physical columns in derived columns calculations

When you define a calculated measure column using a logical column, OBIEE performs the aggregation for the columns used in the calculation first, before applying the calculation.

So for example, if a calculated measure (Total Revenue) is derived from the product of two LOGICAL columns (Unit Price x Number of Items), then OBIEE will create SQL such that the Unit Price and Number of Items columns are aggregated first, and then the multiplication.

However, if the calculated measure is derived from the product of two PHYSICAL columns, then the multiplication will be executed first, and then the aggregation after.

This can make a difference in some cases, so know what you want to accomplish and then carefully choose the appropriate column type for your derived column.

OBIEE 10g and OBIEE 11g directory structure comparisons

I got this chart from one of Oracle’s documents, and thought it would be handy to post for easy lookup and it might come in handy for someone searching for information on the directory parallels between OBIEE 10g and OBIEE 11g.

OBIEE 11g Key Directories and the corresponding 10g directories

Directory or Files 10g & 11g Locations
AdminTool.sh equalizerpds.sh/equalizerpds.exe JobManager.sh MigrateEUL.sh NQClient.sh nqcmd.sh/nqcmd.exe 10g: BI_ORACLE_HOME/server/Bin

 

11g: BI_ORACLE_HOME/bifoundation/server/bin

DBFeatures.INI NQSConfig.INI 10g: BI_ORACLE_HOME/server/Config

 

11g: ORACLE_INSTANCE/config/OracleBIServerComponent
/coreapplication_obisn

NQClusterConfig.INI 10g: BI_ORACLE_HOME/server/Config

 

11g: ORACLE_INSTANCE/config/OracleBIApplication
/coreapplication

NQQuery.log NQSAdminTool.log NQServer.log 10g: BI_ORACLE_HOME/server/Log

 

11g: ORACLE_INSTANCE/diagnostics/logs
/OracleBIServerComponent/coreapplication_obis1

Oracle BI Server repository directory:

  • SampleAppLite.rpd
  • paint.rpd
10g: BI_ORACLE_HOME/server/Repository

 

11g: ORACLE_INSTANCE/bifoundation
/OracleBIServerComponent/coreapplication_obisn/repository

Samples:

  • order.xml
  • Product.xml
  • samplesales.udml
10g: BI_ORACLE_HOME/server/Sample/samplesales

 

11g: ORACLE_INSTANCE/bifoundation/OracleBIServerComponent
/coreapplication_obisn/sample/SampleAppFiles

Usage Tracking:

  • SQL_Server_Time
  • UsageTracking.rpd
  • UsageTracking.zip
10g: BI_ORACLE_HOME/server/Sample/usagetracking

 

11g: ORACLE_INSTANCE/bifoundation/OracleBIServerComponent
/coreapplication_obisn/sample/usagetracking

Other Schemas (for example):

  • Oracle_alter_nq_acct.sql
  • SAACCT.DB2.sql
  • SAACCT.MSSQL.sql
  • SAACCT.Oracle.sql

Note: Use the Repository Creation Utility to install the Oracle BI Schema

10g: BI_ORACLE_HOME/server/Sample/Schema

 

11g: ORACLE_INSTANCE/bifoundation/OracleBIServerComponent
/coreapplication_obisn/schema

  • credentialstore.xml
  • instanceconfig.xml (for Presentation Services)
  • userpref_currencies.xml
10g: OracleBIData/web/config

 

11g: ORACLE_INSTANCE/config
/OracleBIPresentationServicesComponent/coreapplication_obipsn

  • JavaHost
  • sawlog0.log
10g: OracleBIData/web/log

 

11g: ORACLE_INSTANCE/diagnostics/logs
/OracleBIPresentationServicesComponent/coreapplication_obipsn

catalogmanager.exe 10g: BI_ORACLE_HOMEwebcatalogmanager

 

11g: ORACLE_INSTANCEbifoundation
OracleBIPresentationServicesComponentcoreapplication_obipsn

catalogmanagerruncat.cmd

instanceconfig.xml (for Oracle BI Scheduler) 10g: OracleBIDatawebconfig

 

11g: ORACLE_INSTANCEconfigOracleBISchedulerComponent
coreapplication_obischn

  • odbc.ini
  • user.sh
10g: BI_ORACLE_HOME/setup

 

11g: ORACLE_INSTANCE/bifoundation/OracleBIApplication
/coreapplication/setup

Troubleshooting OBIEE 11g

A couple tips on how to resolve some OBIEE 11g errors. When you receive an error such as …

“Supplementary information regarding operation: PROCESS:instance1:coreapplication_obisch1;FAILED_TO_START;
Operation Failed: start; OracleInstance: instance1; Component: coreapplication_obisch1; msg: 0 of 1 processes
started.”

or this …

“Error 500–Internal Server Error
From RFC 2068 Hypertext Transfer Protocol — HTTP/1.1:
10.5.1 500 Internal Server Error
The server encountered an unexpected condition which prevented it from fulfilling the request.”

1. First check to make sure your database instance and Listener are up and running.
You can do this through Enterprise Manager by connecting to the server/port.
https://hostname:1158/em/

If they are not running, then start them.
On Windows, you may need to make sure that the following Services are running:
. OracleServiceORCL
. OracleOraDb11g_home1ClrAgent
. OracleOraDb11g_home1TNSListener

2. Then check the status of other OBIEE components.
You can do this through the OBIEE Enterprise Manager.
http://hostname:7001/em/ then navigating to Business Intelligence -> coreapplication -> Availability tab.

or by running the opmnctl command as below
[Drive][FMW_HOME_DIR]instancesinstance1bin>opmnctl status
for example C:obiee11ginstancesinstance1bin> opmnctl status

In the example below, the BI Scheduler (coreapplication_obisch1) and the BI Presentation Server (coreapplication_obips1) are down.

Processes in Instance: instance1
———————————+——————–+———+———
ias-component | process-type | pid | status
———————————+——————–+———+———
coreapplication_obiccs1 | OracleBIClusterCo~ | 10136 | Alive
coreapplication_obisch1 | OracleBIScheduler~ | N/A | Down
coreapplication_obijh1 | OracleBIJavaHostC~ | 8396 | Alive
coreapplication_obips1 | OracleBIPresentat~ | N/A | Down
coreapplication_obis1 | OracleBIServerCom~ | 6736 | Alive

You can start the individual components using Enterprise Manager interface, or you may Stop BI Services and then Start BI Services from the Windows Start button
(Start -> All Programs -> Oracle Business Intelligence -> Stop/Start BI Services).

Changing the name of a Subject Area in OBIEE 10g (in preparation for upgrade to 11g)

This article shares a few steps for renaming a “Subject Area” in OBIEE 10g.  OBIEE 10g allowed leading spaces in Subject Area names. You may have done this to alter the sort order of your Subject Areas without using leading numbers. This would result in only a Warning message when you checked the repository’s consistency.  However, leading spaces in Subject Area names is not allowed in OBIEE 11g, and will result in an Error.

So, if you are upgrading to OBIEE 11g then you will need to change the Subject Area name(s) to remove leading spaces or you may need to change Subject Area name(s) for other reasons.

Here are a few steps to consider but I recommend that you perform these steps for one subject area first, confirm all is well, then repeat steps for all subject areas.

  1. Stop the Presentation Server.
  2. In the Oracle BI Administration Tool, manually rename the Subject Area(s) in the Presentation Layer (and remove the leading spaces if that was the reason for the rename).
  3. Log into Catalog Manager in online mode, and navigate to Tools –> XML Search and Replace
  4. Carefully set the text you would like to replace with the new text – but make sure the text string is unique enough so that you do not inadvertently changed text that you don’t want to change.  Repeat for each name change that is required.
  5. You may need to manually open your prompts in Answers to change the Subject Area names in the custom SQLs used to generate defaults and drop down lists.
  6. Restart the Presentation Server and test your changes.