Log files for Oracle Business Intelligence (OBIEE) 11g version.

Using Enterprise Manager

You can check the different log files in the Oracle Business Intelligence (OBIEE) 11g version.
The right way to check them is using the Enterprise Manager (EM) Console page but you can also review the files directly on the hard disk.

a) EM Console. Login to the URL

http://server.domain:7001/em

and navigate to:

Farm_bifoundation_domain-Business Intelligence-coreapplications-Diagnostics-Log Messages

These are all the available files:

Presentation Services Log
Server Log
Scheduler Log
JavaHost Log
Cluster Controller Log
Action Services Log
Security Services Log
Administrator Services Log

************************************************************************

To check the files directly, you can use the following:

1) Admin Server logs.

AdminServer-diagnostic.log

Directory:
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/AdminServer/logs
or
$DOMAIN_HOME/servers/AdminServer/logs

2) Managed Server log
bi_server1-diagnostic.log

Directory:
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/bi_server1/logs
or
$DOMAIN_HOME/servers/bi_server1/logs/

3) Node Manager log files

WL_HOME/common/nodemanager

Example:

D:\OBIEE11G\wlserver_10.3\common\nodemanager
4) BI Componets logs file are under:
$MIDDLEWARE_HOME/instances/instance1/diagnostics
5) OPMN: Oracle Process Manager and Notification Server

All files under the directory:

$MIDDLEWARE_HOME/instances/instanceX/diagnostics/logs/OPMN/opmn
or
$ORACLE_INSTANCE/diagnostics/logs/OPMN/opmn


6) Enterprise Manager Log
emoms.trc

Directory:
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/sysman/log
or
$DOMAIN_HOME/servers/sysman/log

Auto Complete Functionality in Prompts

Available now in OBIEE 11.1.1.6 is the ability for prompts to suggest and highlight matching values as the user types in a value for the prompt.

 At the moment this functionality is limited to Choice lists only, and even then only when you allow the "Enable User to Type Value" checkbox. This you'll find in the prompt properties.

So how do we turn this functionality on, as it doesn't happen out the box.

Step 1.

Edit the instanceconfig.xml file in the \instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1 directory. Between the <serverinstance></serverinstance> tags add the following. 

<prompts>
<maxdropdownvalues>256</maxdropdownvalues>
<autoapplydashboardpromptvalues>true</autoapplydashboardpromptvalues>
<autosearchpromptdialogbox>true</autosearchpromptdialogbox>
<autocompletepromptdropdowns>
<supportautocomplete>true</supportautocomplete>
<caseinsensitive>true</caseinsensitive>
<matchinglevel>MatchAll</matchinglevel>
<resultslimit>50</resultslimit>
</autocompletepromptdropdowns>
</prompts>

This will allow autocomplete and ignore case, returning a list of 50 (at most) matches. 

Step 2.

Restart the BI component either through Enterprise Manager or opmnctl. 

Step 3. 

Now create a prompt ensuring that your settings are similar to those below.

 When you come to test the prompt, enter two or more letters and the dropdown below the prompt should fill. Note that the pattern of letters you enter can occur anywhere in the entries in the list returned, even in the middle of a word.

Updating a userid in the Web Catalog with runcat.cmd

Have you ever run into this error with OBIEE 11g?

The GUID (########################) for user ABC already exists in the catalog with username XYX. If this user was renamed in the backend, please use the catalog user management tools to rename this user in the catalog. If the username was not renamed, the catalog has to be cleaned of this GUID.[[
File:securityimpl.cpp
Line:610

This error occurs when an LDAP user is renamed, typically preventing the user from logging into the application. Oracle provides

documentation for updating the GUID, but there is little if any documentation to update a userid. Here are the steps to updating a userid in the OBIEE 11g Web Catalog.

Steps:

Log into the Oracle BI host.
Navigate to the Catalog Manager path:
<MiddlewareHome>\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager

Create a file called boot.txt in the same directory
Update the file with credentials able to use catalog manager
login=userid
pwd=password

Replace userid and password with those appropriate for your system (eg. weblogic....)

Save the file and close

Execute the runcat.cmd utility to update the userid
runcat.cmd -cmd renameAccounts -old OLDUSERID -new NEWUSERID -online http://IPAddress:PORT/analytics/saw.dll -credentials boot.txt

No output indicates that the user has been updated. Test by impersonating the user and logging into OBIEE.  You should now be able to login successfully under the new userid.

forums https://forums.oracle.com/forums/thread.jspa?threadID=2360823

OBIEE RPD Repository Report

Hidden away in a dusty corner of the OBIEE Admin tool is the Repository report.

This is pretty useful for a knowledge transfer. Click on tools -> utilities and then select "Repository Report".

This will give you a csv file detailing the information from presentation layer through the BMM and then the physical layer.

Columns are

Subject Area,
Presentation Table,
Presentation Column,
Description - Presentation Column,
Business Model,
Derived logical table,
Derived logical column,
Description - Derived Logical Column,
Expression,
Logical Table,
Logical Column,
Description - Logical Column,
Logical Table Source,
Expression,
Initialization Block,
Variable,
Database,
Physical Catalog,
Physical Schema,
Physical Table,
Alias,
Physical Column,
Description - Physical Column

I am currently experimenting with exporting this data and including it in management dashboards, tying it in with the catalog and usage tracking.

Where you derive a Fact is Important

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 Date Expressions

1)  First Day of the Previous Year
-------------------------------------------
TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 


From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.


2) First Day of the Current Year
----------------------------------
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 


This calculation returns the first day of the year by deducting one less than the total number of days in the year.


3) First Day of the Next Year
---------------------------------------
TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 


From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the next year.


4) First Day of the Previous Month
-----------------------------------
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))


From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month.


5) First Day of the Current Month
--------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.


6) First Day of the Next Month
-------------------------------------
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 


From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.


7) First Day of Current Quarter
---------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.


8) Last Day of the Previous Month
--------------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 


From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.


9) Last Day of Current Month
---------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 


From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.


10) Last Day of the Next Month
-----------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 


From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.


11) Last Day of Previous Year
------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.


12) Last Day of Current Year
------------------------------------------
TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 


From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.


13) Last Day of the Next Year
----------------------------------------
TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 


From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.


14) Last Day of Current Quarter
--------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 


Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.


15) Number of days between First Day of Year and Last Day of Current Month
------------------------------------------------------------------------------------------------------------------
 TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))) 


For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn’t want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month.

Using VALUEOF in OBIEE

VALUEOF

Use the VALUEOF function to reference the value of a repository variable. Repository variables are defined using the Administration Tool. You can use the VALUEOF function both in Expression Builder in the Administration Tool, and when you edit the SQL statements for an analysis from the Advanced tab of the Analysis editor in Answers.

Syntax

Variables should be used as arguments of the VALUEOF function. Refer to static repository variables by name. Note that variable names are case sensitive. For example, to use the value of a static repository variables namedprime_begin and prime_end:

CASE WHEN "Hour" >= VALUEOF("prime_begin")AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END

You must refer to a dynamic repository variable by its fully qualified name. If you are using a dynamic repository variable, the names of the initialization block and the repository variable must be enclosed in double quotes ( " ), separated by a period, and contained within parentheses. For example, to use the value of a dynamic repository variable named REGION contained in an initialization block named Region Security, use the following syntax:

SalesSubjectArea.Customer.Region = VALUEOF("Region Security"."REGION")

The names of session variables must be preceded by NQ_SESSION, separated by a period, and contained within parentheses, including the NQ_SESSION portion. If the variable name contains a space, enclose the name in double quotes ( " ). For example, to use the value of a session variable named REGION, use the following syntax in Expression Builder or a filter:

"SalesSubjectArea"."Customer"."Region" = VALUEOF(NQ_SESSION.REGION)

Although using initialization block names with session variables (just as with other repository variables) may work, you should use NQ_SESSION. NQ_SESSION acts like a wildcard that matches all initialization block names. This lets you change the structure of the initialization blocks in a localized manner without impacting requests.



Reference : otn.oracle.com