Important Path Directories in OBIEE 11g

Wondering about the configuration files in OBIEE 11g.?? Here's some of the main Configuration/Log file locations.

OBIEE Configuration file Path :-

Catalog Path:
C:\Middleware\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog

Repository Path:
C:\Middleware\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository

Instanceconfig.xml:
C:\Middleware\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1

NQSConfig.INI:
C:\Middleware\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1

Start-up / Stop Issues :-

You have to check the below logs if you have any start-up issues for any of your OBIEE components.
The first step is to check the status of all components using,

C:\Middleware\instances\instance1\bin>opmnctl status -l

If any of the component status is down, go to the corresponding log file for more information.
To individually start components for an example to start the presentation services you would use the below command,

opmnctl startproc ias-component=coreapplication_obips1

Similarly to start other components replace the coreapplication_obips1 with correct names.

OBIEE System Component Log Locations :-

Presentation Services:
OBIEE_HOME\instances\instance1\diagnostics\logs\OracleBIPresentationServicesComponent\coreapplication_obips1

sawlogo.log - Fro any issues related to Presentation Services start/stop

BI Server Component:
OBIEE_HOME\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1

nqquery.log - For any issues related OBIEE Analysis & RPD issues
nqserver.log - For any issues related to Server Component start/stop

BI Scheduler Component:
OBIEE_HOME\instances\instance1\diagnostics\logs\OracleBISchedulerComponent\coreapplication_obisch1

nqscheduler.log - For any issues related to Scheduler Component start/stop

BI Cluster Component:
OBIEE_HOME\instances\instance1\diagnostics\logs\OracleBIClusterControllerComponent\coreapplication_obiccs1

nqcluster.log - For any issues related to Cluster Component start/stop

Java host Component:
OBIEE_HOME\instances\instance1\diagnostics\logs\OracleBIJavaHostComponent\coreapplication_obijh1

jh.log - For any issues related to Java Host Component start/stop

Weblogic Server Log Locations :-

BI Managed Server:
C:\Middleware\user_projects\domains\bifoundation_domain\servers\bi_server1\logs

bi_server1.out - For any issues related to bi_server1

Admin Server:
OBIEE_HOME\user_projects\domains\bifoundation_domain\servers\AdminServer\logs

AdminServer.log - For any issues related to AdminServer

Authenticating Users Using an External Database Table

You can choose to maintain lists of users and their passwords in an external database rather than in the repository. An external database table consisting of user login information has been provided so that you can import this information into the repository and use it to authenticate users during login.

The table contains a list of users, their logins and passwords, and the group they belong to. Optionally, the table can also contain the logging level for each user, Web interface information, and the names of specific database catalogs or schema to use for each user when querying data. After this information has successfully been imported, you need to create an initialization block that retrieves this data.

Create an sql file to create the oracle database table for external authentication as shown below:

CREATE TABLE "SECURITYTABLE" 

   (

"SALESREP" VARCHAR2(24 BYTE), 

"USERNAME" VARCHAR2(32 BYTE), 

"PWD" VARCHAR2(16 BYTE), 

"GRP" VARCHAR2(24 BYTE)

   );

Insert following values for sample.

Note:
Each user listed in this table is a member of the Sales Rep group, except for Ellen Abel, who belongs to the Sales Admin group.
Using a table of users in an external database allows you to maintain a single list of users for multiple repositories. Although the groups must be entered into the Administration Tool manually, it is easier to set up 100 groups than it is to set up 50,000 separate users.

Import the SECURITYTABLE table in your repository.

Create an initialization block. This initialization block will populate session variables with data values returned by querying the database each time a user logs in.

• Select Manage > Variables.
• In the left pane, select Session > Initialization Blocks.
• In the right pane, right-click and select New Initialization Block.
• In the Name field, enter Security.

In the Default Initialization string field, create an initialization string that populates group, username, password, and log level variables. You define the variables in a later step. 

The values returned by the database are assigned to the variables you define. These variables are used to authenticate a user during login. Enter the following into the Initialization string field:

select GRP, SALESREP, USERNAME, 2 from SECURITYTABLE where USERNAME = ':USER' and PWD = ':PASSWORD';

Create the variables. Because you requested data for four variables in the string, you need to define each variable. The variables are Group, DisplayName, User, and LogLevel.

Click Edit Data Target.

• Click New.
• Enter Group in the Name field.
• Click OK.
• Click Yes to acknowledge that this variable has a special purpose.

Repeat the process for the remaining three variables. The order of the variables is important. You must list variables in the same order as the columns in the SQL statement.

Ensure that, your screen resembles the following screenshot.

Check Global Consistency for any errors.

Save the repository.

Upload it on server and restart BI Services.

Open BI Analysis and check whether you are able to log in using any of the user from your external database table "SECURITYTABLE".

E.g: User Name: EAbel

       Password: ea

Happy blogging.. :-)

Sagar Tippe.

Setting LOGLEVEL from Answers in OBIEE

Run an analysis?? Want to see the SQL / Query Log generated by OBIEE server??

Here you can get trick to temporarily enable the logging level. Then you can be able to see the query log.

Normally, we check query in NQQuery.log file or from Answers also we can check the query directly.

If it’s directly from Answers (go to Administration > Manage Sessions.

If we are unable to see the log file or if we find any error message saying that, No Log found while trying to watch the query in log.

Here, the first thing you need to check is LOG LEVEL. When creating rpd, by default log level takes 0 values for Administrator user.

Using Administration Tool,

• Manage > Identity
• Double-click  the User for which you would like to set the Log Level.
• Set it to 2 or more level accordingly, such that, the query will be appeared.

You can get the detail of available Log Levels in OBIEE at the below link,
http://obieenow.blogspot.in/2014/02/different-logging-levels-in-obiee-11g.html

Using Answers, (This is temporarily, for that current session only)

• Stop your BI Server service
• Delete the content of NQQuery.log completely, save file.
• Start BI Server services.
• Open BI Analysis and run any simple report.
• Go to advanced tab.
• Within Advanced SQL Clauses, Scroll down to see the option: Prefix
• Here write SET VARIABLE LOGLEVEL=2; (semi-colon at the end is must)

• Now click on Results tab to re – run the report.
• Now you go Settings > Administration > Manage Sessions
• Click on View Log to view the query. Now you are able to see the query and according to the LOGLEVEL you have set.
• Go back to Advanced tab, observe the Logical query.
• Before SQL, you find the text you written in Prefix field.

• By mentioning the semi colon, BI Server executes these statements one after another.

Oracle BI Mobile App Designer is now Available. (Trial Edition)

You can try out the new era in BI Development from the below link.

http://www.oracle.com/technetwork/middleware/bi-foundation/bi-mobile-ad-trial-download-2020939.html?ssSourceSiteId=ocomen

Is this is what you were looking for..!! Then try it out. ;-)

Source: https://blogs.oracle.com

Thanks,
Sagar Tippe.

Opaque View In OBIEE

Deploying Opaque View Objects:

In offline mode, the Deploy Views utility is available when importing from data sources with ODBC and DB2 CLI data sources. Oracle Native (client) drivers are also supported in the offline mode for deploying views. In online mode, view deployment is available for supported data sources using Import through server.

Using the Create View SELECT Statement:

The SQL statement for deploying opaque views in the Physical layer of the repository is available for supported data sources. To determine which of your data sources support opaque views, contact your system administrator or consult your data source documentation.

Only repository variables can be used in the definition. An error is generated if a session variable is used in the view definition.

Syntax: CREATE VIEW view_name AS select_statement;

Where:

select_statement is the user-entered SQL in the opaque view object. If SQL is invalid, the create view statement fails during view deployment.

view_name is one of the two following formats: schema.viewname, or viewname. The connection pool settings determine if the schema name is added.

For opaque view objects, the right-click menu contains the Deploy View(s) option. When you select Deploy View(s), the Create View SQL statement executes and attempts to create the deployed view objects. The following list describes the ways you can initiate view deployment and the results of each method:

Right-click a single opaque view object. When you select Deploy View(s), the Create View SQL statement executes and attempts to create a deployed view for the object.

Right-click a physical schema or physical catalog or any of the objects. If any opaque view object exists in the schema or catalog, the right-click menu contains the Deploy View(s) option. When you select Deploy View(s), the Create View SQL statements for all qualifying objects execute and attempt to create deployed views for the qualifying objects contained in the selected schema or catalog.

Undeploying a Deployed View:

Running the Undeploy Views utility against a deployed view deletes the views and converts the table back to an opaque view with its original SELECT statement.

To undeploy a deployed view:

• In the Physical layer of the Administration Tool, right-click a physical database, catalog, schema, or table.
• If a deployed view exists that is related to the selected object, the right-click menu contains the Undeploy View(s) option.
• Select Undeploy View(s).
• A list of views to be undeployed appears.
• If you do not want to undeploy one or more of the views, clear the appropriate rows.
• In the View Deployment - Undeploy View(s) dialog, click OK to remove the views.
• A message appears if the undeployment was successful.
• In the View Deployment Messages dialog, you can search for undeployed views using Find and Find Again, or you can copy the contents.
• When you are finished, click OK.

When to Delete Opaque Views or Deployed Views

Use the following guidelines to remove opaque or deployed view objects in the repository:

• Removing an undeployed opaque view in the repository. If the opaque view has not been deployed, you can delete it from the repository.
• Removing a deployed view. When you deploy an opaque view, a view table is created physically in both the data source and the repository. Therefore, you must undeploy the view before deleting it. You use the Undeploy Views utility in the Administration Tool. This removes the opaque view from the back-end data source, changes the Table Type from None to Select, and restores the SELECT statement of the object in the Physical layer of repository.

Note:

• All the database cannot run View Deployment. because in the XLS or in any other non relational databases cannot have the features called 'CREATE_VIEW_SUPPORTED'. This features of schema we will get from features tab when we are right clicking on the   physical layer schema folder and selecting the properties.
• It is possible to select multiple views and deploy them simultaneously.

[nQSError: 38107] Not all four columns are defined for closure table

Problem: [nQSError: 38107] Not all four columns are defined for closure table

In OBIEE 11g, while dealing with Parent-Child Hierarchy, you can get the error saying,
[nQSError: 38107] Not all four columns are defined for closure table 'D51 Sales Rep Parent Child'. (HY000)

The BI Consistency Check will be fine. But when you are going to test your results in Analysis, the error report would through this error.
[nQSError: 38107] Not all four columns are defined for closure table ....

The root cause of this error is Parent-Child setting in hierarchy can't be set up properly while modifying repository in online mode.

Solution: 

• To fix this issue, make your all BI Services down.
• Open your repository in offline mode.
• Set the Parent-Child settings properly.
• Save and take your repository online.
• Test your result now.

Happy BI. ;-)