OBIEE - How to Set a Temp Query Logging Level?

You might want to diagnose performance or data issues by setting a temporary log level for a query. You can enable query logging for a select statement by adding a prefix clause in the Advanced SQL Clauses section of the Advanced tab in Oracle BI Presentation Services.
For example, for the select statement:
SELECT a, b, c from dim1, fact 1;
You can specify the logging level of 5 in the Prefix field as follows:

Set Variable LOGLEVEL=5;

For this query, the logging level of 5 is used regardless of the value of the underlying LOGLEVEL variable.

OBIEE - What is Log File Rotation?

Log file rotation is the creation of new log files, when the file exceeds a specified threshold or date.

Take the MaximumFileSizeKb setting for the component log configuration file for the Oracle BI Scheduler as an example. Whenever a log file exceeds the size that is specified by this setting, then the existing Scheduler log file is renamed, and a new log file is created.

·                     nqscheduler.log — The latest log file.

·                     nqscheduler-<n>.log — The renamed previous log file.
where <n> = date and timestamp, 
for example nqscheduler-20100909-2135.log

Additionally, a log file date that is older than the MaximumLogAgeDay setting is deleted.

OBIEE - Function Shipping

OBIEE Function-Shipping:

Each database has its own set of built in features and limits. The BI server always makes use of these built-in database functions as it will be more efficient to process data on database. This process is aptly called Function Shipping. In case if the required function is not supported by the data source, then the BI server performs the operations by itself.

In the BI AdminTool double-click on the physical data source to take a look at the properties associated, in the Features tab you can see the inbuilt functions available in the data source. BI Server will decide what functions to ship and what not during run time based on the settings here.

The Default settings (Check Boxes) are getting loaded from a INI file bases on the data source and connection pool settings.

The INI file, contains the default settings for database features, is in <Oracle_Instance>\config\OracleBIServerComponent\coreapplication\DBFeatures.INI

If you must modify the DBFeatures.ini file, use extreme caution because setting features that the underlying databases do not support can cause errors and unexpected results to be returned.
Regards, 

- Akash

Outer Joins - Left, Right and Full - Practical Example

Outer Join:

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both). In this case left and right refer to the two sides of the JOIN keyword.

SQL> Select * from A;

emp_id emp_name dpt_id 1001 Saran 20 1002 Jason 20 1003 Brian 20 1004 Paul 10 1012 Mary 40 1007 Steve 30 1008 Jim 20 1009 Mark 20 1010 Yihong 20 1013 Melanie 30 1011 Greathouse 50 1014 Jie 50 1015 Raj 50

SQL> Select * from B;

dpt_id	dpt_name
10	Management
20	Development
30	Usability
40	HR
60	Sales

Left Outer Join:
This means that a left outer join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.
SQL> 
select a.EMP_ID, a.EMP_NAME, a.DPT_ID, b.Dpt_name from a 
LEFT OUTER JOIN b on a.DPT_ID = B.DPT_ID 
order by a.EMP_ID;

emp_id	emp_name	dpt_id	dpt_name
1001	Saran	20	Development
1002	Jason	20	Development
1003	Brian	20	Development
1004	Paul	10	Management
1007	Steve	30	Usability
1008	Jim	20	Development
1009	Mark	20	Development
1010	Yihong	20	Development
1011	Greathouse	50	Null
1012	Mary	40	HR
1013	Melanie	30	Usability
1014	Jie	50	Null
1015	Raj	50	Null

Right Outer Join:

This returns all the values from the right table and matched values from the left table or NULL in case of no matching join predicate.
SQL> 
select a.EMP_ID, a.EMP_NAME, a.DPT_ID, b.Dpt_name from a 
RIGHT OUTER JOIN b on a.DPT_ID = B.DPT_ID 
order by a.EMP_ID;

emp_id	emp_name	dpt_id	dpt_name
1001	Saran	20	Development
1002	Jason	20	Development
1003	Brian	20	Development
1004	Paul	10	Management
1007	Steve	30	Usability
1008	Jim	20	Development
1009	Mark	20	Development
1010	Yihong	20	Development
1012	Mary	40	HR
1013	Melanie	30	Usability
Null	Null	Null	Sales

Full Outer Join
a full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set.
SQL> 
select a.EMP_ID, a.EMP_NAME, a.DPT_ID, b.Dpt_name from a 
FULL OUTER JOIN b on a.DPT_ID = B.DPT_ID 
order by a.EMP_ID;

emp_id	emp_name	dpt_id	dpt_name
1001	Saran	20	Development
1002	Jason	20	Development
1003	Brian	20	Development
1004	Paul	10	Management
1007	Steve	30	Usability
1008	Jim	20	Development
1009	Mark	20	Development
1010	Yihong	20	Development
1011	Greathouse	50	Null
1012	Mary	40	HR
1013	Melanie	30	Usability
1014	Jie	50	Null
1015	Raj	50	Null
Null	Null	Null	Sales

Regards,
- Akash