OBIEE 11G Location of Configuration files

Configuration Files
Source: Oracle® Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition > Where are Configuration Files Located?



Oracle BI Server 
NQSConfig.INI
logconfig.xml
ORACLE_INSTANCE\config\OracleBIServerComponent\coreapplication_obisn
For example:
\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1
Note: Although DBFeatures.ini is also located in this directory, do not edit this file directly. See Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence  Enterprise Edition for information about how to edit features for a database.

Oracle BI Presentation Services
instanceconfig.xml  ORACLE_INSTANCE\config\OracleBIPresentationServicesComponent\coreapplication_obipsn
For example:
\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1
Note: Do not add elements to the instanceconfig.xml file unless you are overriding the stated default values. Override only those settings that are necessary for configuring the system to meet the needs of your organization.

Cluster Controller 
NQClusterConfig.INI
ccslogging.xml
ORACLE_INSTANCE\config\OracleBIApplication\coreapplication
For example:
\instances\instance1\config\OracleBIApplication\coreapplication

Oracle BI Scheduler
instanceconfig.xml
ccslogging.xml
(for Cluster Controller)
ORACLE_INSTANCE\config\OracleBISchedulerComponent\coreapplicati on_obischn
For example:
\instances\instance1\config\OracleBISchedulerComponent\coreapplication_obisch1

JavaHost 
config.xml
logging_config.xml
ORACLE_INSTANCE\config\OracleBIJavaHostComponent\coreapplication_obijhn
For example:
\instances\instance1\config\OracleBIJavaHostComponent\coreapplication_obijh1

Oracle BI Presentation Services Plug-in
bridgeconfig.properties
MW_HOME\user_projects\domains\domain_name\config\fmwconfig\biinstances\coreapplication
For example:
mw_home\user_projects\domains\bifoundation_domain\config\fmwconfig\biinstances\coreapplication

OBIEE 11G Auto Complete Prompts

Oracle BI EE provides auto-complete functionality for prompts, which, when enabled, suggests and highlights matching prompt values as the user types in the prompt selection field. Auto complete is only available for the Choice List as user input and when checked the “Enable User to Type Value” option.

Configuring Auto Complete at system level.
Open the instanceconfig.xml file for editing
Location: D:\OBIEE11g_HOME\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1
Take backup copy
Right click and click on Edit
Insert the below code between the    <ServerInstance>…….. </ServerInstance>
<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>
Save your changes and close the file
Restart Oracle Business Intelligence.
The auto complete functionality is enabled at the system level, but the dashboard designer or BI Reports developer can exclude the auto-complete functionality from dashboards, and user can turn auto-complete off by using the “My Account dialog”.
After implementing the auto complete functionality at system level, you can enable or disable at dashboard properties level

And by user level in my account settings.

The results in Prompts for auto complete are shown below

These are not case sensitive, you can type in any case (this is part of the above code)

Aggregate Functions OBIEE

Aggregate functions perform operations on multiple values to create summary results.

The aggregate functions cannot be used to form nested aggregation in expressions on logical columns that have a default aggregation rule defined in the Aggregation tab of the Logical Column dialog. To specify nested aggregation, you must define a column with a default aggregation rule and then request the aggregation of the column in a SQL statement.

Aggregate functions include:

·         AGGREGATE AT

·         AVG

·         AVGDISTINCT

·         BOTTOMN

·         COUNT

·         COUNTDISTINCT

·         COUNT(*)

·         FIRST

·         GROUPBYCOLUMN

·         GROUPBYLEVEL

·         LAST

·         MAX

·         MEDIAN

·         MIN

·         NTILE

·         PERCENTILE

·         RANK

·         STDDEV

·         STDDEV_POP

·         SUM

·         SUMDISTINCT

·         TOPN

AGGREGATE AT

This function aggregates columns based on the level or levels you specify. Using AGGREGATE AT guarantees that the aggregate for the measure always occurs at the levels specified after the keyword AT, regardless of the WHERE clause.

Syntax

AGGREGATE(expr AT level [, level1, levelN])

Where:

expr is any expression that references at least one measure column

level is the level at which you want to aggregate. You can optionally specify multiple levels.

You cannot specify a level from a dimension that contains levels that are being used as the measure level for the measure you specified in the first argument. For example, you cannot write the function as AGGREGATE(yearly_sales AT month) because "month" is from the same time dimension that is being used as the measure level for "yearly_sales."

Example

The following example shows the AGGREGATE AT function and example results:

SELECT month, year, AGGREGATE(sales AT Year)

FROM timeseriestestingWHERE year = 1994 AND month = 12

Result:

Month    Year    AGGREGATE AT year12       1994    7396Row count: 1

Because the AGGREGATE AT operator is always executed before the predicates, it always returns the correct total for the time level specified after the keyword AT.

AVG

This function calculates the average (mean) value of an expression in a result set. It must take a numeric expression as its argument.

Note that the denominator of AVG is the number of rows aggregated. For this reason, it is usually a mistake to use AVG(x) in a calculation in Oracle Business Intelligence. Instead, write the expression manually so that you can control both the numerator and denominator (x/y).

Syntax

AVG(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

AVGDISTINCT

This function calculates the average (mean) of all distinct values of an expression. It must take a numeric expression as its argument.

Syntax

AVG(DISTINCT numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

BOTTOMN

This function ranks the lowest n values of the expression argument from 1 to n, 1 corresponding to the lowest numeric value. The BOTTOMN function operates on the values returned in the result set. A request can contain only one BOTTOMN expression.

Syntax

BOTTOMN(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the bottom number of rankings displayed in the result set, 1 being the lowest rank.

COUNT

This function calculates the number of rows having a nonnull value for the expression. The expression is typically a column name, in which case the number of rows with nonnull values for that column is returned.

Syntax:

COUNT(expr)

Where:

expr is any expression.

COUNTDISTINCT

This function adds distinct processing to the COUNT function.

Syntax

COUNT(DISTINCT expr)

Where:

expr is any expression.

COUNT(*)

This function counts the number of rows.

Syntax

COUNT(*)

Example

For example, if a table named Facts contained 200,000,000 rows, the sample request would return the results shown:

SELECT COUNT(*) FROM Facts

Result:

200000000

FIRST

This function selects the first returned value of the expression argument. For example, the FIRST function can calculate the value of the first day of the year.

The FIRST function is limited to defining dimension-specific aggregation rules in a repository. You cannot use it in SQL statements.

The FIRST function operates at the most detailed level specified in your explicitly defined dimension. For example, if you have a time dimension defined with hierarchy levels day, month, and year, the FIRST function returns the first day in each level.

You should not use the FIRST function as the first dimension-specific aggregate rule. It might cause queries to bring back large numbers of rows for processing in the Oracle BI Server, causing poor performance.

When a measure is based on dimensions, and data is dense, the Oracle BI Server optimizes the SQL statements sent to the database to improve performance.

Note that you cannot nest PERIODROLLING, FIRST, and LAST functions.

Syntax

FIRST(expr)

Where:

expr is any expression that references at least one measure column.

Example

FIRST(sales)

GROUPBYCOLUMN

For use in setting up aggregate navigation. It specifies the logical columns that define the level of the aggregate data existing in a physical aggregate table.

For example, if an aggregate table contains data grouped by store and by month, specify the following syntax in the content filter (General tab of Logical Source dialog):

GROUPBYCOLUMN(STORE, MONTH)

The GROUPBYCOLUMN function is only for use in configuring a repository. You cannot use it to form SQL statements.

GROUPBYLEVEL

For use in setting up aggregate navigation. It specifies the dimension levels that define the level of the aggregate data existing in a physical aggregate table.

For example, if an aggregate table contains data at the store and month levels, and if you have defined dimensions (Geography and Customers) containing these levels, specify the following syntax in the content filter (General tab of Logical Source dialog):

GROUPBYLEVEL(GEOGRAPHY.STORE, CUSTOMERS.MONTH)

The GROUPBYLEVEL function is only for use in configuring a repository. You cannot use it to form SQL statements.

LAST

This function selects the last returned value of the expression. For example, the LAST function can calculate the value of the last day of the year.

The FIRST function is limited to defining dimension-specific aggregation rules in a repository. You cannot use it in SQL statements.

The LAST function operates at the most detailed level specified in your explicitly defined dimension. For example, if you have a time dimension defined with hierarchy levels day, month, and year, the LAST function returns the last day in each level.

You should not use the LAST function as the first dimension-specific aggregate rule. It might cause queries to bring back large numbers of rows for processing in the Oracle BI Server, causing poor performance.

When a measure is based on dimensions, and data is dense, the Oracle BI Server optimizes the SQL statements sent to the database to improve performance.

Note that you cannot nest PERIODROLLING, FIRST, and LAST functions.

Syntax

LAST(expr)

Where:

expr is any expression that references at least one measure column.

Example

LAST(sales)

MAX

This function calculates the maximum value (highest numeric value) of the rows satisfying the numeric expression argument.

Syntax

MAX(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The MAX function resets its values for each group in the query according to specific rules.

MEDIAN

This function calculates the median (middle) value of the rows satisfying the numeric expression argument. When there are an even number of rows, the median is the mean of the two middle rows. This function always returns a double.

Syntax

MEDIAN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The MEDIAN function resets its values for each group in the query according to specific rules.

MIN

This function calculates the minimum value (lowest numeric value) of the rows satisfying the numeric expression argument.

Syntax

MIN(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The MIN function resets its values for each group in the query according to specific rules.

NTILE

This function determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. In other words, the resulting sorted data set is broken into several tiles where there are roughly an equal number of values in each tile.

NTile with numTiles = 100 returns what is commonly called the "percentile" (with numbers ranging from 1 to 100, with 100 representing the high end of the sort). This value is different from the results of the Oracle BI PERCENTILE function, which conforms to what is called "percent rank" in SQL 92 and returns values from 0 to 1.

Syntax

NTILE(numExpr, numTiles)

Where:

numExpr is any expression that evaluates to a numeric value.

numTiles is a positive, nonnull integer that represents the number of tiles.

If the numExpr argument is not null, the function returns an integer that represents a rank within the requested range.

PERCENTILE

This function calculates a percent rank for each value satisfying the numeric expression argument. The percentile rank ranges are from 0 (1st percentile) to 1 (100th percentile), inclusive.

The percentile is calculated based on the values in the result set.

Syntax

PERCENTILE(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The PERCENTILE function resets its values for each group in the query according to specific rules.

RANK

This function calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

The rank is calculated based on the values in the result set.

Syntax

RANK(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The RANK function resets its values for each group in the query according to specific rules.

STDDEV

This function returns the standard deviation for a set of values. The return type is always a double. STDEV_SAMP is a synonym for STDDEV.

Syntax

STDDEV([ALL | DISTINCT] numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

If ALL is specified, the standard deviation is calculated for all data in the set.

If DISTINCT is specified, all duplicates are ignored in the calculation.

If nothing is specified (the default), all data is considered.

The STDDEV function resets its values for each group in the query according to specific rules.

STDDEV_POP

This function returns the standard deviation for a set of values using the computational formula for population variance and standard deviation.

Syntax

STDDEV_POP([ALL | DISTINCT] numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

If ALL is specified, the standard deviation is calculated for all data in the set.

If DISTINCT is specified, all duplicates are ignored in the calculation.

If nothing is specified (the default), all data is considered.

SUM

This function calculates the sum obtained by adding up all values satisfying the numeric expression argument.

Syntax

SUM(numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

The SUM function resets its values for each group in the query according to specific rules.

SUMDISTINCT

This function calculates the sum obtained by adding all of the distinct values satisfying the numeric expression argument.

Syntax

SUM(DISTINCT numExpr)

Where:

numExpr is any expression that evaluates to a numeric value.

TOPN

This function ranks the highest n values of the expression argument from 1 to n, 1 corresponding to the highest numeric value. The TOPN function operates on the values returned in the result set. A request can contain only one TOPN expression.

Syntax

TOPN(numExpr, integer)

Where:

numExpr is any expression that evaluates to a numeric value.

integer is any positive integer. Represents the top number of rankings displayed in the result set, 1 being the highest rank.

The TOPN function resets its values for each group in the query according to specific rules.

Get Age between two Days in OBIEE Report

For Getting Age between two dates we use SQL Query as :

datediff(d, startdate, enddate) as Age

However default date format in OBIEE is Timestamp. So you need timestamp function (Available under Calendar/Date Function Heading) to get required result.

Syntax:

TIMESTAMPDIFF(interval, timestamp1, timestamp2)

Where:
interval is the specified interval. Valid values are:

SQL_TSI_SECOND

SQL_TSI_MINUTE

SQL_TSI_HOUR

SQL_TSI_DAY

SQL_TSI_WEEK

SQL_TSI_MONTH

SQL_TSI_QUARTER

SQL_TSI_YEAR

timestamp1 and timestamp2 are any valid timestamps.

Example
For Number of Days between two days:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, “TO_DATE_COLUMN”)

For Number of Days till current date:
TimestampDiff(SQL_TSI_DAY, “FROM_DATE_COLUMN”, CURRENT_DATE)

*You can also use this function for getting age difference for other time dimension attributes as week, quarter, month or year. All you need to do is change first attribute i.e 'interval' & other attribute's accordingl