OBIEE Forcing OC4J_BI shutdown

Somtimes your OC4J is really stuck, and if you to give a shutdown without resestting your server try:
"{JDK HOME here}\bin\java.exe" -jar {ORACLE_BI home here}\oc4j_bi\j2ee\home\admin.jar ormi://localhost:23791 {OC4JADMIN password here} -shutdown force

Till Next Time

OBIEE Conditional X-axis graph

One of our customer had a unique request. If the selected period becomes longer then a predifinend period, the x-axis should switch from item 1 to item 2 without user intervention! Here is how we did it:

First create the conditonal x-axis: (Sales sample)

CASE WHEN (TIMESTAMPDIFF(SQL_TSI_DAY, MIN("D0 Time"."T00 Calendar Date"), MAX("D0 Time"."T00 Calendar Date")) > 20 ) THEN "D1 Customer"."C2 Cust Status" ELSE "D2 Market"."M02 Area" END

Next alter the Y value so that it will follow the X - axis:

sum("F1 Revenue"."1-01 Revenue (Sum All)" by CASE WHEN
(TIMESTAMPDIFF(SQL_TSI_DAY, MIN("D0 Time"."T00 Calendar Date"), MAX("D0 Time"."T00 Calendar Date")) > 20 ) THEN "D1 Customer"."C2 Cust Status"
ELSE "D2 Market"."M02 Area" END )

Add everthing to a dashboard:

15 days:





25 days:

Till Next Time

OBIEE scripting user into the repository

On the OTN forum today there was a question about how to script a new user into the repository: http://forums.oracle.com/forums/thread.jspa?threadID=697646&tstart=0

The only way I know is using the nQUDMLExec.exe

First create a txt file with your new users:

DECLARE USER "NewUser" AS "NewUser" UPGRADE ID 1 FULL NAME {New User} PASSWORD
'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54A286E822D97C35C7AD5C43AD4F2A09EAC4D07C3A079829F'
PERIODICITY 90 HAS ROLES ( "Administrators", "XMLP_ADMIN" ) DESCRIPTION
{password = welcome1} PRIVILEGES ( READ);

The password can only be entered encrypted, this string is for "welcome1".

Next you have to "merge" the txt file into the reopsitory:

E:\OracleBI\server\Bin>nQUDMLExec.exe -U Administrator -P Administrator -I E:\temp\NewUser.txt -B E:\temp\paint.rpd -O e:\temp\newrep.rpd

Check the new repository!

Till next Time

OBIEE Change the password

Found this one on the blog of Jose Troya (http://oracleintelligence.blogspot.com/2008/08/obiee-enable-users-to-change-passwords.html) It's in Spanish so I translated it into English.

If you don't a security model like LDAP in place, all the users and there passwords are stored in the repository. Wenn you want your users to be able to change there own password from there browers here is how you do it:

Step 1: Create a new entry in your customMessage.xml:

Be sure it's in your customMessage folder. You can cut and paste this message from the controlmessages.xml file found in the ..\OracleBI\web\msgdb\messages.
Restart the Oracle BI Server, Web server, and WWW services in that order.

Till Next Time

OBIEE Conditional Hyperlink

A customer wanted a conditional hyperlink.

If the turnover was greater then 100000 it should show a hyperlink else only the number

Here is how we did it:

CASE WHEN ("F1 Revenue"."1-01 Revenue (Sum All)"> 100000 )THEN '{a href=http://www.google.com }'CAST("F1 Revenue"."1-01 Revenue (Sum All)" as VARCHAR(10)) '{/a}' else CAST( "F1 Revenue"."1-01 Revenue (Sum All)" as VARCHAR(10) )END

Don't forget to set the column data type settings:

Till Next Time

OBIEE Back One Page

Found an intresting one on the OTN forum today:

http://forums.oracle.com/forums/thread.jspa?threadID=683746&tstart=0

You can make your own "Go Back One Page" button by adding a textpart to your dashboard section:

{input type="button" value="Go Back One Page" onclick="history.back();"}

For a clear button have look here:

http://obiee101.blogspot.com/2008/08/obiee-making-clear-button.html

Till Next Time

OBIEE Identifying the server.

If you are working in a "Development Street" like environment, (separate Development, Testing, Acceptance and Production machines) , most of the time the data on these machines will not be the same. In most organizations the refresh rate for the production system is higher then on the other systems. On most development and test system you usually only have a subset of the production data. Problems can arise when during the development process you create “hard copies” of your reports (paper/pdf etc). These tend to start roaming trough the organization, and when they accidently land on a managers desk he/she might misinterpret the data.

Solution 1 would be to hardcode the “source name” on the report or in a repository variable. But… report and repositories are transported back and forth between the different “Development Street” parts, so there is no SOx compliant guarantee that the report is correctly identified.

Solution 2 is to dynamically put the server name and IP-address on the report. Problem is that OBIEE doesn’t have build in variables holding these values.
As long as your repository connects to at least one Oracle database, then you can use this method, using the Oracle sys_context function

In your repository make two new initiation blocks called:

INIT_ENV_TERMINAL

INIT_ENV_IP_ADDRESS

For the data source use:
SELECT sys_context('USERENV', 'TERMINAL') FROM dual

And
SELECT sys_context('USERENV', 'IP_ADDRESS') FROM dual

Put the data in repository variables named TERMINAL and IP_ADRESS.

In your reports you can call these variables using:

VALUEOF(TERMINAL) and VALUEOF(IP_ADDRESS)

Till Next Time

This article was original written for the Ciber Knowledge Blog:
http://knowledge.ciber.nl/weblog/?p=127

OBIEE Hidding dashboard sections

When you make a dashboard you don't want to show the same reports to each (group of) user (s). To accomplish this there are two strategies: Create a unique dashboard for each group, which can be very labor and maintenance intensive.

Or you can make parts of the dashboard visible based on the group the user belong to.

First we have to make a report that will return at least a row if a user belongs to a specific group and no rows if the user isn't a member.

The valueof(NQ_SESSION.GROUP) returns a string with all the groups the user belongs to. Using the the POSITION function we can determine if a certain group is in the string:

POSITION('Administrators' in valueof (NQ_SESSION.GROUP) )

If the value is greater then 0 then it's in the string.

Now we add this formula to the filter:

Next goto the guided navigation dashboard section you want to toggle:

Select Reference Source Request, the selection report and how it should react.

The section is now only visible if a user is a member of the Administrators group.

Till Next Time

This article was original written for the Ciber Knowledge Blog:

http://knowledge.ciber.nl/weblog/?p=126

OBIEE Stragg function on 10G..

Up to 9i you had to use Tom Kyte's Stragg function to get the children of the level to a single string. From 10GR1 you can use the collect function.

I wrote an entry on my ORACLE101 blog (http://oracle101.blogspot.com/2008/08/oracle-collect-function.html) which can be used in the same way you use the STRAGG function in OBIEE (see: http://obiee101.blogspot.com/2008/08/obiee-children-of-level-converting-rows.html)

Till next time

OBIEE Where is the ODBC Data Source Administrator?

Stumbled today over an OBIEE sandbox machine without an ODBC Data Source Administrator configured:


We had to dig deep in our collective brain where to find it: ..\windows\system32\odbcad32.exe
In the registry you have give to have full control over the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC


Till Next Time

OBIEE Configuration Tags

Hi,

I did an extraction of the "sercret" file with all the OBIEE configration tags:
OBIEE10340CONFIGURATIONTAGS.pdf

You can find the file at ..\OracleBI\systemsmanagement\resources\
DON'T EDIT THIS FILE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Till Next Time

OBIEE error saving security account

My colleague Rene Kuipers encountered this error while trying to copy reports using the catalog manager over a network share.

This was caused by the fact that the catalog manager didn't have full control over the catalog folder (read,write,create and delete).
Till Next Time

OBIEE Children of the level / Converting rows to strings

1 Preface
Consider the following table:


But what you really want is this:

Or even better sorted alphabetically

This document describes how you can achieve this in OBIEE against an Oracle DB

2 Stragg function
This solution is based on Tom Kite’s original String Aggregation function found here:
http://www.sqlsnippets.com/en/topic-11591.html

To implement this function in OBIEE your first have to bring the function to the database. Either in the data-schema or in your custom OBIEE function schema. If you do the later be sure that the OBIEE function schema has direct select rights granted on the data-schema tables and views. Grant an execute on the STRAGG function to public. It’s also very handy to create a public SYNONYM for the STRAGG function. (CREATE PUBLIC SYNONYM STRAGG FOR SCHEMA_NAME.STRAGG;)

2.1 The STRAGG scripts

2.1.1 The STRAGG object

create or replace type stragg_type as object
(
string varchar2(4000),

static function ODCIAggregateInitialize
( sctx in out stragg_type )
return number ,

member function ODCIAggregateIterate
( self in
out stragg_type ,
value in varchar2
) return number ,

member
function ODCIAggregateTerminate
( self in stragg_type,
returnvalue out
varchar2,
flags in number
) return number ,

member function
ODCIAggregateMerge
( self in out stragg_type,
ctx2 in stragg_type
)
return number
);
/

2.1.2 The STRAGG type body

create or replace type body stragg_type
is

static
function ODCIAggregateInitialize
( sctx in out stragg_type )
return
number
is
begin

sctx := stragg_type( null ) ;

return
ODCIConst.Success ;

end;

member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number
is
begin

self.string := self.string ',' value ;

return
ODCIConst.Success;

end;

member function ODCIAggregateTerminate
( self in stragg_type ,
returnvalue out varchar2 ,
flags in number
) return number
is
begin

returnValue := ltrim( self.string,
',' );

return ODCIConst.Success;

end;

member function
ODCIAggregateMerge
( self in out stragg_type ,
ctx2 in stragg_type
)
return number
is
begin

self.string := self.string
ctx2.string;

return ODCIConst.Success;

end;

end;
/

2.1.3 The STRAGG function

create or replace function stragg
( input varchar2 )
return varchar2
deterministic
parallel_enable
aggregate using stragg_type
;
/

3 OBIEE Usage
These function work both from the repository as directly from the reports.
3.1 Unsorted

EVALUATE_AGGR( 'STRAGG(%1)' as varchar(200), EMP.ENAME)

3.2 Sorted




EVALUATE( 'STRAGG(%1) OVER ( PARTITION BY (%2) ORDER BY (%1) ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) ' as varchar(200), EMP.ENAME,DEPT.DEPTNO)

3.3 Distinct
EVALUATE_AGGR( 'STRAGG( DISTINCT %1)' as varchar(200), EMP.ENAME)

Till Next Time

This article was original written for the Ciber knowledge Blog:
http://knowledge.ciber.nl/weblog/?p=125

OBIEE Difference between two rows

Found this one on the OTN Forum:

http://forums.oracle.com/forums/thread.jspa?threadID=692479&tstart=30

Handy when your database doesn't have a Lag or Lead function

Till Next Time

OBIEE Configuring Configuring the scheduler on Windows2003/Oracle

As with many configurations on OBIEE, it's in the documentation only distributed on many PDF's.
Here is how install the OBIEE scheduler on Windows2003 and an Oracle DB.
Basically you have to go trough 5 steps:
1 Create the DB User and Tables.
2 Check the instanceconfig.xml
3 Add the Scheduler Administrator to the credential store
4 Configure the Job Manager
5 Run A test

1 CREATE THE DB USER AND TABLES.
The table scripts can be found in ..\OracleBI\server\Schema\SAJOBS.Oracle.sql, but you have create the user yourself. Or you can use this script:
/*****************************************************************************/
/* Setup OBIEE scheduler on ORACLE */
/* READ THE SCRIPT BEFORE YOU RUN IT !!!!!!!!!! */
/* Run the script as SYSTEM */
/*****************************************************************************/
/*****************************************************************************/
/* Create the S_NQ_SCHED user */
/* You can change the password NOT the user name */
/*****************************************************************************/
DROP USER S_NQ_SCHED;
CREATE USER S_NQ_SCHED IDENTIFIED BY "S_NQ_SCHED_PASSWORD"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PROFILE DEFAULT
QUOTA UNLIMITED ON "USERS";
GRANT "CONNECT" TO S_NQ_SCHED;
GRANT "RESOURCE" TO S_NQ_SCHED;
GRANT CREATE SESSION TO S_NQ_SCHED;
GRANT CREATE TABLE TO S_NQ_SCHED;
GRANT CREATE VIEW TO S_NQ_SCHED;
ALTER USER S_NQ_SCHED DEFAULT ROLE NONE;

2 Check the instanceconfig.xml
In ..\OracleBIData\web\config you will find the instanceConfig.xml

Check if the entry between Alerts Tags has a ScheduleServer entry. If you run it on an other port then 9705 add it in the form ServerName:PortNumber (MyServer:1234).

3 Add the Scheduler Administrator to the credential store
Open a command line box:
cryptotools credstore -add -infile e:/OracleBIData/web/config/credentialstore.xml
Credential Alias: admin

Username: SchedulerAdmin

Password: SchedulerAdmin

Do you want to encrypt the password? y/n (y): Y

Passphrase for encryption: secret

Do you want to write the passphrase to the xml? y/n (n): Y

File "OracleBIData_HOME/web/config/credentialstore.xml" exists. Do you want to overwrite it? y/n (y): Y

4 Configure the Job Manager
From the file menu select Configuration Options

On the Database tab enter the connection pool date. Always use the native call interface if possible!.

On the General tab enter the administrator name and credentials
Start the scheduler service

You can find the log in: ..\OracleBI\server\Log\

5 Run A test
Create a simple report and press Save And Schedule

Click on the destinations tab an select Oracle BI Server Cache

Save the IBot.
Open a connection with the jobmanager:

Enter the login credentials

Press the refresh button and have a look at the entry:

This article was original written for the Ciber knowledge Blog: http://knowledge.ciber.nl/weblog/?p=124

Till Next Time