What Hour is it?

You can extract time elements from dates in OBIEE at both the repository and answers level.

Just use something along the lines of :

CAST ( EXTRACT( HOUR FROM "CentralCal"."date") AS INTEGER )

OBIEE 11.1.1.6 - Changing how the analytics editor starts

In the latest release of OBIEE you can specify how you want your reports editor to start up.

You change the way the editor starts up from the (I find it so annoying) results tab, where the report attempts to run. Useful but not always helpful.

You can now ask to start on the criteria tab, very useful especially if you are navigating to a report with a long running query.

OBIEE - Using LocateN

So I guess your pulling your hair out about now, LocateN doesn't seem to work.

Quick tip:

Replace the LocateN with Locate, that's right remove the N (but keep the syntax). The Locate function appears to be overloaded to allow this functionality and the LocateN (which doesn't work) seems to be a leftover hanging around.

Locate(' ','one two three')

finds the first space ' ' in the string 'one two three', and here's the hidden part, starting from position 0 (zero).

LocateN(' ','one two three',5)

finds the first space ' ' in the string 'one two three' from position 5.

Usage Tracking in OBIEE 11.1.1.6

Unlike previous versions of OBIEE, usage tracking is now configured and maintained, not through the NQSCONFIG.INI file, but through enterprise manager.

So get busy fire up your enterprise manager http://......:7001:/em

and on the left hand side expand the Weblogic Domain
and once that has opened, the bifoundation_domain.

You should see two entries under bifoundation_domain (AdminServer and bi_cluster), right click on AdminServer and select System Mbean Browser.

The right hand side of the screen should now fill.

We are looking for the following mbean
Application Defined Mbeans

• oracle.biee.admin
• Domain: bifoundation_domain
• BIDomain.BIInstance.ServerConfiguration

Opening this up we see some properties we can edit. Remember to change any settings we need to take out an edit lock and then release it one done.

OBIEE - Database (Evaluate) Function

Database Functions (EVALUATE)

Users and administrators can create requests by directly calling database functions from either Oracle BI Answers, or by using a logical column (in the logical table source) within the metadata repository. Key uses for these functions include the ability to pass through expressions to get advanced calculations, as well as the ability to access custom written functions or procedures on the underlying database.

Support for database functions does not currently extend across all multidimensional sources. Also, you cannot use these functions with XML data sources.

By default, support for the EVALUATE family of database functions is disabled. You must change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI to enable support for the EVALUATE* functions. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

Functions include:

    EVALUATE
    EVALUATE_ANALYTIC
    EVALUATE_AGGR
    EVALUATE_PREDICATE

EVALUATE

This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for scalar calculations, and is useful when you want to use a specialized database function that is not supported by the Oracle BI Server, but that is understood by the underlying data source.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

The ability to use EVALUATE is disabled by default. To enable support for this function, change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

Syntax
EVALUATE('db_function(%1...%N)' [AS data_type] [, column1, columnN])Where:
db_function is any valid database function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns.

Examples

This example shows an embedded database function.

SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees

Examples Using EVALUATE_AGGREGATE and EVALUATE to Leverage Unique Essbase Functions

The following examples use the EVALUATE_AGGREGATE and EVALUATE functions. Note that expressions are applied to columns in the logical table source that refers to the physical cube.Use EVALUATE_AGGREGATE to implement custom aggregations. For example, you may want to compare overall regional profit to profits for the top three products in the region. You can define a new measure to represent the profits for top three products resulting in the Logical SQL statement:

SELECT Region, Profit, EVALUATE_AGGREGATE('SUM(TopCount(%1.members, 3, %2), %3)',Products, Profit, Profit) Top_3_prod_Profit FROM SampleBasic
The Oracle BI Server generates the following expression for the custom aggregation:

member [Measures].[MS1] AS 'SUM(Topcount([Product].Generations(6).members,3,[Measures].[Profit]),[Measures].[Profit])'
Use the EVALUATE function on projected dimensions to implement scalar functions that are computed post-aggregation. EVALUATE may change the grain of the query, if its definition makes explicit references to dimensions (or attributes) that are not in the query.

For example, if you would like to see the Profits for the top five products ranked by Sales sold in a Region, after creating the applicable measure, the resulting Logical SQL statement is as follows

SELECT Region, EVALUATE('TopCount(%1.members, 5, %2)' as VARCHAR(20), Products, Sales), Profits FROM SampleBasic

The Oracle BI Server generates the following expression to retrieve the top five products:

set [Evaluate0] as '{Topcount([Product].Generations(6).members,5,[Measures].[Sales]) }'

EVALUATE_ANALYTIC

This function passes the specified database analytic function with optional referenced columns as parameters to the back-end data source for evaluation.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

The ability to use EVALUATE_ANALYTIC is disabled by default. To enable support for this function, change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

Syntax
EVALUATE_ANALYTIC('db_function(%1...%N)' [AS data_type] [, column1, columnN])
Where:

db_function is any valid database analytic function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific analytic function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns.

Examples

This example shows an embedded database analytic function.

EVALUATE_ANALYTIC('dense_rank() over(order by %1 )' AS INT,sales.revenue)

If the preceding example needs to return a double, then an explicit cast should be added, as follows:

CAST(EVALUATE_ANALYTIC('Rank(%1.dimension.currentmember, %2.members)',"Foodmart93"."Time"."Month" as Double)

EVALUATE_AGGR

This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for aggregate functions with a GROUP BY clause.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

The ability to use EVALUATE_AGGR is disabled by default. To enable support for this function, change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

Syntax
EVALUATE_AGGR('db_agg_function(%1...%N)' [AS data_type] [, column1, columnN)
Where:

db_agg_function is any valid aggregate database function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns.

Example


EVALUATE_AGGR('REGR_SLOPE(%1, %2)', sales.quantity, market.marketkey)

EVALUATE_PREDICATE

This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for functions with a return type of Boolean.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Note that EVALUATE_PREDICATE is not supported for use with Essbase data sources.

The ability to use EVALUATE_PREDICATE is disabled by default. To enable support for this function, change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

Syntax
EVALUATE_PREDICATE('db_function(%1...%N)', [, column1, columnN)
Where:

db_function is any valid database function with a return type of Boolean that is understood by the underlying data source.

column1 through columnN is an optional, comma-delimited list of columns.

If you want to model a database function for comparison purposes, you should not use EVALUATE_PREDICATE. Instead, use EVALUATE and put the comparison outside the function. For example, do not use EVALUATE_PREDICATE as follows:

EVALUATE_PREDICATE('dense_rank() over (order by 1% ) < 5', sales.revenue)

Instead, use EVALUATE, as follows:

EVALUATE('dense_rank() over (order by 1% ) ', sales.revenue) < 5

Example

SELECT year, Sales AS DOUBLE,CAST(EVALUATE('OLAP_EXPRESSION(%1,''LAG(units_cube_sales, 1, time, time LEVELREL time_levelrel)'')', OLAP_CALC) AS DOUBLE)  FROM "Global".Time, "Global"."Facts - sales" WHERE EVALUATE_PREDICATE('OLAP_CONDITION (%1, ''LIMIT time KEEP ''''1'''', ''''2'''', ''''3'''', ''''4'''' '')=1', OLAP_CALC)  ORDER BY year;

View the original article here.

Configuring and Installing the Network Loopback Adapter on Windows Server 2008

OBIEE requires a looback adapter if the server uses a dynamically assigned IP Address.

Installing the loopback is fairly straightforward.
Start -> Computer -> Right-click, Properties -> Device Manager -> Computer Name, Right-click, Add Legacy Hardware ->

The Wizard appears ... click Next >.
On the selection screen "What do you want the wizard to do?" Select Install the hardware that I manually select from a list (Advanced) then click Next >
From the list, scroll down to and select the Network Adapters then click Next >
Scroll down the Manufacturer list (left) and select Microsoft then on the network adapter list, select Microsoft Loopback Adapter (right) and click Next >
The wizard will now confirm to install the virtual hardware. Clicking Next > will start the installation process.
  
Finally click the Finish button.

While the control panel is open. its a good time to configure the new (virtual) network adapter. We can configure the new Network just like a physical network. Go to Network and Sharing Center on the control panel
On the Network and Sharing Center, click Manage network connections.
Find the Network connection with labels as Microsoft Loopback Adapter, right click, then choose properties.

On my virtual network, I will be using IPv4 only for now. Choose Internet Protocol Version 4 (TCP/IPv4), then click properties.
I’m using a static network, and I will use the 10.10.10.10/ 24 block. Using this series, I dont want to conflict the existing physical network. My subnet mask is 255.255.255.0 and the DNS server (127.0.0.1). Click Ok. Then close the wizard.