Monday 30 April 2012

OBIEE "Stop BI Services" prompting for user/password


Under Windows the "Stop BI Services" script prompts for the weblogic adminstrator user-id & password. This can be turned off by editing the StartStopServices.xml file in the directory:

$OBIEE_HOME\instances\instance1\bifoundation\OracleBIApplication\coreapplication\

Locate the  <condition property="should.get.credentials"> tag:

        <condition property="should.get.credentials">
            <or>
                <isset property="should.create.credentials"/>
                <isset property="is.stop.all"/>
            </or>
        </condition>

and change the second isset property to "should.create.credentials":

        <condition property="should.get.credentials">
            <or>
                <isset property="should.create.credentials"/>
                <isset property="should.create.credentials"/>
            </or>
        </condition>




Sunday 29 April 2012

Linux OBIEE start/stop script


Here is a script for starting and stopping the OBIEE 11g services under Linux.

This is generally available from other blogs but I've improved it by adding an option just to restart the OPMN services (rather than the entire weblogic stack) and also got it to create the boot.properties file to prevent prompting for the weblogic admin userid & password.

The settings at the top of the file need to be amended to suit the installation.

The file name should be something like obiee_start_stop.sh.

Update: I've enhanced it further to firstly overcome the occassional problem where the presentation services fails to start, which I suspect it because it's starting too soon, the solution being to start the five opmn services manually one at a time. Secondly I've added additional options to just stop, start and restart the opmn services.


#!/bin/bash
#
# File:    /etc/init.d/obiee
# Purpose: Start and stop Oracle Business Intelligence 11g components.
#
# chkconfig: 2345 99 10
# description: Manage OBIEE service.
#
# These values must be adapted to your environment.

ORACLE_OWNR=user                      # Local Unix user running OBIEE
ORACLE_FMW=/u01/app/obiee            # Deployment root directory
                                  
BIEE_USER=weblogic                    # BIEE administrator name
BIEE_PASSWD=Password1                  # BIEE administrator password              
BIEE_DOMAIN=bifoundation_domain        # Domain name
BIEE_INSTANCE=instance1                 # Instance name
BIEE_SERVER=bi_server1                  # Server name
BIEE_MANAGER_URL=linux.local.com:7001    # Admin server URL (hostname:port)   

#create the security file boot.properties

rm -f $ORACLE_FMW/user_projects/domains/$BIEE_DOMAIN/servers/AdminServer/security/boot.properties
echo  username=$BIEE_USER >$ORACLE_FMW/user_projects/domains/$BIEE_DOMAIN/servers/AdminServer/security/boot.properties
echo  password=$BIEE_PASSWD >>$ORACLE_FMW/user_projects/domains/$BIEE_DOMAIN/servers/AdminServer/security/boot.properties

# These should require no change.

WL_PATH=$ORACLE_FMW/wlserver_10.3/server/bin
BIEE_PATH=$ORACLE_FMW/user_projects/domains/$BIEE_DOMAIN/bin
ORACLE_INSTANCE=$ORACLE_FMW/instances/$BIEE_INSTANCE

export ORACLE_INSTANCE


NOW=$(date +"%Y%m%d-%H%M")
START_LOG=/u01/app/obiee/logs/obiee-start-$NOW.log
STOP_LOG=/u01/app/obiee/logs/obiee-stop
-$NOW.log
SUBSYS=obiee

start() {
    echo "********************************************************************************"
    echo "Starting Admin Server on $(date)"
    echo "********************************************************************************"
    $BIEE_PATH/startWebLogic.sh &
    wait_for "Server started in RUNNING mode"
   
    echo "********************************************************************************"
    echo "Starting Node Manager on $(date)"
    echo "********************************************************************************"
    $WL_PATH/startNodeManager.sh &
    wait_for "socket listener started on port"

    echo "********************************************************************************"
    echo "Starting Managed Server $BIEE_SERVER on $(date)"
    echo "********************************************************************************"
    $BIEE_PATH/startManagedWebLogic.sh $BIEE_SERVER http://$BIEE_MANAGER_URL &
    wait_for "Server started in RUNNING mode"

    echo "********************************************************************************"
    echo "Starting BI components on $(date)"
    echo "********************************************************************************"
    $ORACLE_INSTANCE/bin/opmnctl start
    $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obijh1
    $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obiccs1
    $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obis1
    $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obisch1
    $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obips1
    $ORACLE_INSTANCE/bin/opmnctl status


    echo "********************************************************************************"
    echo "OBIEE start sequence completed on $(date)"
    echo "********************************************************************************"
}

stop() {
    echo "********************************************************************************"
    echo "Stopping BI components on $(date)"
    echo "********************************************************************************"
    $ORACLE_INSTANCE/bin/opmnctl stopall

    echo "********************************************************************************"
    echo "Stopping Managed Server $BIEE_SERVER on $(date)"
    echo "********************************************************************************"
    $BIEE_PATH/stopManagedWebLogic.sh $BIEE_SERVER t3://$BIEE_MANAGER_URL $BIEE_USER $BIEE_PASSWD
    echo "********************************************************************************"
    echo "Stopping Node Manager on $(date)"
    echo "********************************************************************************"
    pkill -TERM -u $ORACLE_OWNR -f "weblogic\\.NodeManager"
   
    echo "********************************************************************************"
    echo "Stopping Admin Server on $(date)"
    echo "********************************************************************************"
    $BIEE_PATH/stopWebLogic.sh
    echo "********************************************************************************"
    echo "OBIEE stop sequence completed on $(date)"
    echo "********************************************************************************"
}

wait_for() {
    res=0
    while [[ ! $res -gt 0 ]]
    do
        res=$(tail -5 "$START_LOG" | fgrep -c "$1")
        sleep 5
    done
}

case "$1" in
    start)
        echo "********************************************************************************"
        echo "Starting Oracle Business Intelligence on $(date)"
        echo "Logs are sent to $START_LOG"
        echo "********************************************************************************"
        start &> $START_LOG &
        touch /u01/app/obiee/logs/$SUBSYS
    ;;
    stop)
        echo "********************************************************************************"
        echo "Stopping Oracle Business Intelligence on $(date)"
        echo "Logs are sent to $STOP_LOG"
        echo "********************************************************************************"
        stop &> $STOP_LOG
        rm -f /u01/app/obiee/logs/$SUBSYS
    ;;
    status)
        echo "********************************************************************************"
        echo "Oracle BIEE components status...."
        echo "********************************************************************************"
        $ORACLE_INSTANCE/bin/opmnctl status
    ;;
    stopopmn)
        echo "********************************************************************************"
        echo "Oracle BIEE components status...."
        echo "********************************************************************************"
        $ORACLE_INSTANCE/bin/opmnctl stopall
    ;;
    startopmn)
        echo "********************************************************************************"
        echo "Oracle BIEE components status...."
        echo "********************************************************************************"
        $ORACLE_INSTANCE/bin/opmnctl start
        $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obijh1
        $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obiccs1
        $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obis1
        $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obisch1
        $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obips1
        $ORACLE_INSTANCE/bin/opmnctl status
    ;;    
    restartopmn)
        echo "********************************************************************************"
        echo "Oracle BIEE components status...."
        echo "********************************************************************************"
        $ORACLE_INSTANCE/bin/opmnctl stopall
        $ORACLE_INSTANCE/bin/opmnctl start
        $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obijh1
        $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obiccs1
        $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obis1
        $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obisch1
        $ORACLE_INSTANCE/bin/opmnctl startproc ias-component=coreapplication_obips1
        $ORACLE_INSTANCE/bin/opmnctl status
    ;;
    restart)
        $0 stop
        $0 start
    ;;
    *)
        echo "Usage: $(basename $0) start|stop|restart|stopopmn|startopmn|restartopmn|status"
        exit 1
esac


exit 0

Running OBIEE under SELinux


When installing OBIEE 11g on a Linux server where SELinux is enforced there are a number of library files which need to be registered with SELinux to gain permission to run.

Generally it's easier to install OBIEE with SELinux disabled, then re-enable it afterwards the run the following commands to give OBIEE permission to run.

to disable SELinux edit the file /etc/sysconfig/selinux and change the line

set selinux='enforcing'
to
set selinux='disabled'

save and then reboot the server. Reverse this change to re-enable SELinux after the installation.

Run the following commands in a terminal window under the root user-id to allow OBIEE to run under SELinux:

(you'll need to change the OBIEE install directory  '/u01/app/obiee' to your install directory)

chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libons.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnemagent.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmndisco.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnreports.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnwc.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnovd.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnbi.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnoid.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmncustom.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnohs.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/liblogloader.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libmodapi.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnbam.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnip.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnopenssl.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnoptic.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnoraclessl.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnreports.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnwireless.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libagtsh.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libclntsh.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libclsra11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libcorejava.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libdms2.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libdmsapp.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libexpat.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libhasgen11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libheteroxa11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libiau.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libldapjclnt11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnjni11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnjssl11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnldapj11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmadbg.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmadm.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmalk.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmarl.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmasf.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmastk.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmcfhc.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmcfsga.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefdms.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefojmx.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefos.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefpfa.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefport.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefsp.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefsql.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefsqlt.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefud.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefut.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefvr.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefw.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmemso.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmeoci.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmevq.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmevsp.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnnz11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnque11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libntcpaio11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libocci.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libocijdbc11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libocr11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libocrb11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libocrutl11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libons.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libOsUtils.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libowm2.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libskgxp11.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libskgxpd.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libskgxpg.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libskgxpr.so'
chcon -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libxdb.so'



semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libons.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnemagent.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmndisco.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnreports.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnwc.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnovd.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnbi.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnoid.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmncustom.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnohs.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/liblogloader.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libmodapi.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnbam.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnip.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnopenssl.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnoptic.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnoraclessl.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnreports.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/opmn/lib/libopmnwireless.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libagtsh.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libclntsh.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libclsra11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libcorejava.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libdms2.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libdmsapp.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libexpat.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libhasgen11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libheteroxa11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libiau.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libldapjclnt11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnjni11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnjssl11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnldapj11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmadbg.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmadm.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmalk.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmarl.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmasf.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmastk.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmcfhc.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmcfsga.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefdms.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefojmx.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefos.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefpfa.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefport.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefsp.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefsql.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefsqlt.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefud.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefut.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefvr.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmefw.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmemso.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmeoci.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmevq.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnmevsp.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnnz11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libnque11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libntcpaio11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libocci.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libocijdbc11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libocr11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libocrb11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libocrutl11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libons.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libOsUtils.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libowm2.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libskgxp11.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libskgxpd.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libskgxpg.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libskgxpr.so'
semanage fcontext -a -t textrel_shlib_t '/u01/app/obiee/Oracle_BI1/lib/libxdb.so'


Monday 16 April 2012

Creating a dashboard prompt for Essbase UDAs



In my last Blog I looked at how to import Essbase UDAs into OBIEE and how to use them to filter reports.  The problem with the basic approach to using UDAs is that the filters need to be hard coded into a report (or a saved filter) - you can’t treat them as a dimension and let the user interact with them when they view the report. 

If you simply try to create a dashboard prompt you merely end up with multiple prompts - one for each UDA - with values of 0 and 1, not overly friendly for your users!

However with some creative use of filters you can expand their functionality and here I’m going to show how to create a dashboard prompt to determine which UDA the user wants to filter a report by.

Rather than creating a simple prompt on the UDA column we are going to create a Variable prompt, get it to set a presentation variable and then create a function on the filter to use that variable.

So let’s start by creating a new dashboard prompt.  Create a new prompt as usual.

Use the + icon to create a new Variable prompt:



Enter a name for the presentation variable, a label for the prompt and then select ‘Choice List’ as the User input option.


From the Choice List Values drop down select ‘Custom Values’ then use the + icon to manually add the values. I usually add an ‘All’ option as the default, so once all the values have been entered, click on the options icon and select  ‘Custom Specific Value’ as the Default Selection and click the + icon to select the ‘All’ option as the default:


 Click ok and save the prompt.

If you now add this to a dashboard page you should see the drop down list as entered with the All option as the default:



Now we need to add filters to the report to use the prompt.

I’ve created a simple report which uses just three fields, the Sales Channel dimension against which we are going to use the UDAs plus time and the data measure:



Which without any filters obviously shows all Countries.



We now need to add filters to the report. However because each UDA is a separate column, we need to add one filter per column. In this case we have 5 ‘Area’ UDA columns, so we need 5 filters.
Start by adding a new filter for  the first UDA Column


Which brings up the New Filter dialog window:



Now we can’t simply add this column as it stands as the filter – we need to reference the presentation variable, plus we also need to allow for that fact that Area A can be selected either by itself or implicitly when the ‘All Areas’ option is selected.

Also remember from my first blog on using UDAs that the UDA Columns contain just two values, 1 or 0. 

We therefore need to replace the column formula with a new formula that:

 a) Checks the presentation variable
 b) Allows for selecting the individual UDA or the All opton
 c) returns a 1 or 0 for OBIEE to perform the filter with.

The following double case statement will do the job:

case when "Sales Channel"."Country - Area A" = 1 and
(case
when '@{VAR_SALES_AREA}{All Areas}' = 'All Areas' then 1
when '@{VAR_SALES_AREA}{All Areas}' = 'Area A' then 1
else 0
end) = 1
then 1 else 0 end

This formula basically says return a value of 1 when the UDA column of ‘Country – Area A’ is 1 AND the presentation value is either ‘All Areas’ or ‘Area A’, otherwise return a value of 0. 

This is the link between the UDA column and the text value we placed in the dashboard prompt – they do not necessarily need to match, you could take the opportunity to give the UDA columns a more meaningful name to the users, for example rather than ‘Area A’, the prompt could have ‘Northern Europe’. In this case the formula would be:

case when "Sales Channel"."Country - Area A" = 1 and
(case
when '@{VAR_SALES_AREA}{All Areas}' = 'All Areas' then 1
when '@{VAR_SALES_AREA}{All Areas}' = 'Northern Europe' then 1
else 0
end) = 1
then 1 else 0 end


Note that I’ve used 'All Areas' as the default value in the formula: @{VAR_SALES_AREA}{All Areas}. This isn’t strictly necessary, but does mean the report will run ok when editing the analysis as the presentation variable won’t have been set.

We enter this into the column formula screen:


Click ok to save.

If you get the following message
Invalid Alias Format : Table_name.Column_name required. OK (Ignore Error)

Do as it says and click the 'OK (Ignore Error)' link.

Back in the New Filter screen we just need to complete the filter by entering 1 in the value field:


This filter is now complete.

So now we need to repeat this and create a filter for each of the UDA columns. Each time replacing the two references to ‘Area A’ with the appropriate UDA code.

Once all filters have been created you then need to change them from AND filters to OR: (you will never get any results with all five UDA columns filtered with AND!)


If you're creating a report with other filters then you will need to ensure the groupings are correct – that these UDA filters are grouped together separately from other filters or groups of filters.

Now just save the report and place it on the dashboard. The default will of course show all areas:


And changing the prompt will filter the report as required:



The final step I usually take is to save the filter itself – that way it can easily be added to any report without the hassle of adding all of the individual filters.  Note also that the dimension associated with the UDAs (Sales Channel in this case) does not need to appear in the report – like any other filter it will be applied to the data even if the dimensions are not specifically shown in the report.







Sunday 15 April 2012

Using Essbase UDAs in OBIEE11g



Essbase UDA’s (User Defined Attributes) can now be imported into OBIEE and used to filter data in reports. Here I’ll show an example of how to do this and what can be done with it.

Here we have an Essbase cube. The Sales Channel dimension has a geographic hierarchy, but in this case the company has a sales area structure which is not based on geography, so we want to be able to filter reports based on sales area.  In the Essbase outline we have the sales areas (A to E) defined as UDAs against each country:


The Essbase cube definition is imported in the OBIEE repository via the Administration tool using the usual File -> Import metadata option:



When selecting the cube to Import, make sure the ‘Import UDAs’ option is ticked (is usually is by default)


Now, although the Import UDAs option was ticked, the UDAs do not appear automatically in the physical layer. Expanding the data source down the Sales Channel hierarchy does not reveal them:



To bring them into the physical layer, right click on Sales Channel (the dimension which have the UDAs) and select  ‘Create columns for UDA’ -> ‘All UDAs’:


You don’t have to bring in all UDAs if they are not needed – you may have other UDAs in the Essbase cube that you don’t need for OBIEE reporting. Instead you can repeatedly select ‘create columns for UDA’ for each required UDA.

In this example I’ve selected All UDAs. The Sales Channel hierarchy now includes columns for each individual UDA.


An important point on how UDAs are handled by OBIEE is that they are each individual columns against the dimension. OBIEE does not associate them with each other. In this example there is no concept of ‘Sales Areas’  - as far as OBIEE is concerned there are five columns against which you can filter the dimension. You can therefore bring in as many UDAs as you need irrespective of whether they are logically associated or not – you could for example have Local Currency, Area Manager or any other type of UDA and they would all appear as a long list of additional columns against the dimension.
 
Once the physical layer has been arranged, copy the entire cube into the business model and presentation layers as usual. No further changes are necessary, although one thing to be aware of is that the UDAs will be placed against all levels of the dimension, not just those to which the UDA’s have been assigned.  So you end up with the presentation layer looking like this:


This is understandable as OBIEE doesn’t know that in this case the UDAs only apply to a specific level ( in this case Gen 4), so you can manually tidy things up a bit by removing the UDAs from the levels that aren’t required. Usually you’d rename the levels as well to make them meaningful to the users:

Finally save the RPD and check in the changes.

In OBIEE the UDA fields now appear under the Sales Channel dimension when creating a new Analysis:


And can be selected for filtering the report. Before we do that, lets look at what these columns are. You can select them for inclusion in the report. Choosing all of them along with Country and the main data measure:


Displays the following:

So you can see that the UDA fields simply contain a 1 or 0 value, indicating whether that dimension value has each UDA in Essbase.  Refering back to the outline screenshot above UK, France and Germany had UDA Area A, so they have a 1 in the "Country – Area A" column, the other countries have a 0. The pattern is repeated across the countries that have each of the 5 Area UDAs. If any country had been assigned more than one Area UDA in Essbase then it would have a 1 in all appropriate columns. Similarly if a country didn't have any of the selected UDAs it would have 0 in all columns.

Filtering the report then becomes the simple matter of adding a filter to the report on the required column where the value is 1 or 0 depending on whether you wish to include or exclude countries with that UDA. The downside is that you do need to add a filter for each UDA value, you can’t simply say “where UDA value = Area A or Area B”.

You therefore add a UDA column as a filter in the same way as any other column:


So the combined filters of:

Have this effect on the above report:

Only those countries with a 1 for Area A or Area D are included. Note that the filter needs to have the OR operator – the default AND operator would produce no records in this case as there are no countries within Area A and Area D. 

Of course in reality you wouldn’t include the UDA columns, so the report would appear  as:



The dimension which the UDA columns are associated with does not need to appear in the report. The following pivot table has no filters and no reference to the Sales Channel dimension:


Adding the Area A = 1 filter does limit the data in the report:



So, in conclusion, importing and using UDAs from Essbase is quite straight forward , however their use is pretty much limited to filtering on the data, you can’t treat a set of UDAs as a dimension. 

It is however quite a powerful feature, especially when you have many sets of UDAs against multiple dimensions in your Essbase cube – it greatly increases the reporting power on that data within OBIEE.

Next time I’ll show how with a bit of advanced filtering a dashboard prompt of UDA values can be created to allow users to filter on the fly.







Wednesday 4 April 2012

Setting default number formats in OBIEE 11g


You can change the system wide default data format for basic numbers.

I’m not sure if there is a standard default setting, I’ve seen 0 decimal places with commas, 1 decimal place without commas recently. Either way there is an xml file in the catalog which controls this and you can change it.

The file is called _datatype_formats and is stored in the catalog directory (e.g. C:\app\obiee\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite) under \root\system\metadata

If you edit this file (take a backup first!), it comes as a single line set of xml tags, a bit of re-formatting displays the tags as:

<?xml version="1.0" encoding="utf-8"?>
<sawsavedformat:metadata xmlns:sawsavedformat="com.siebel.analytics.web/savedformat/v1.1">
<sawsavedformat:datatypeSavedFormats>
<sawsavedformat:datatypeFormat xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" datatype="GDT:real">
<saw:displayFormat>
<saw:formatSpec suppress="repeat" wrapText="true" visibility="visible" hAlign="right" vAlign="top" imagePlacement="right" interaction="action">
<saw:dataFormat minDigits="2" maxDigits="2" commas="false" negativeType="minus" xsi:type="saw:number"/>
</saw:formatSpec>
</saw:displayFormat>
<saw:tableHeading>
<saw:displayFormat>
<saw:formatSpec/>
</saw:displayFormat>
</saw:tableHeading>
<saw:columnHeading>
<saw:displayFormat>
<saw:formatSpec/>
</saw:displayFormat>
</saw:columnHeading>
</sawsavedformat:datatypeFormat>
</sawsavedformat:datatypeSavedFormats>
</sawsavedformat:metadata>

If you look at the <saw:dataFormat tag, 10 lines from the top there are some parameters:

minDigits, MaxDigits, commas and negativeType

minDigits and MaxDigits control the number of decimal places. Not sure what the difference between the two are, but it seems to prefer them both set to the same number, i.e. 2 if you want 2 decimal places.

Commas controls whether the thousands separater comma is displayed, valid values are true or false

negativeType controls how negative numbers are displayed. Valid values are:
minus                   black numbers with the minus sign
redMinus            red numbers with the minus sign
paren                   black numbers inside parentheses
redParen             red numbers inside parentheses

Once you saved the file just restart presentation services to see the effect.
Here’s a simple example using the sample sales subject area:

With default settings of 0 decimal places and commas = true




After changing the file to show 2 decimal places and commas = false



There’s also a tag in this filed called formatSpec with some of the standard formatting settings, I’ll have a play with these sometime soon.