OBIEE 11g Start/Stop Services - boot.properties

When starting/stopping the Managed Server or Admin Server (WebLogic),
./startManagedWebLogic.sh bi_server1 http://hostname:7001
./startWebLogic.sh
the user is prompted to enter username and password

Instead, you can enable auto login using a boot identity file. A boot identity file contains
user credentials for starting and stopping an instance of WebLogic  Server. An Administration
Server can refer to this file for user credentials instead of prompting you to provide them.
Because the credentials are encrypted, using a boot identity file is more secure than storing
unencrypted credentials in a startup or shutdown script. If there is no boot identity file when
you start a server, the server instance prompts you to enter a username and password. The boot
identity file can be different for each server instance in the domain

A) To configure the boot.properties file for the Managed Server, perform the following steps:

1. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/bi_server1/security and edit the boot.properties file
$ vi boot.properties


2. Modify the file to reflect the following and save it:

username=admin_username
password=admin_password

3. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/bin
./startManagedWebLogic.sh bi_server1 http://hostname:7001/console

B) To configure the boot.properties file for the AdminServer (WebLogic), perform the following steps:

1. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/AdminServers/security and edit the boot.properties file.
$ vi boot.properties

2. Modify the file to reflect the following and save it:
username=admin_username
password=admin_password

3. Browse to
$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/bin
./startWebLogic.sh

Note that the boot.properties file is identified and you are not prompted for a username and
password. The server is in the running mode. Nagivate to boot.properties and notice that the content of the file is now encrypted.

OBIEE log files and where to find them

OBIEE Logs are generated by the following components

1) BI Presentation Server
2) BI Server
3) BI Javahost
4) BI Scheduler


1) Presentation Server log (helpful to for presentation server login issues and Answer/dashboard related issues) and it can be located at ORACLEBIDATA/web/log/sawlog0.log

You can increase the logging on this server to get a detailed logging to troubleshoot the SSO and integration issues by modifying the logconfig.xml file

2) BI Server Logs:

- NQServer.log can be located from the path ORACLEBI/server/log/NQServer.log.
helpful with BI Server Start up issues and Subject areas loaded and the datasources connectedfrom within the RPD.
- NQQurey.log can be located from ORACLEBI/server/log/NSQuery.log
contains the BI Server query operations. Traces user actions.It can also identify if the query is hit from the cache or results obtained are straight from the database and also the actual time it took to execute the query.

It is possible to increase the logging for the NQquery.log via the RPD Security, or via the initialization blocks or by setting the LOGLEVEL Variable from the Answers 'Advanced' tab.The session variable LOGLEVEL overrides a user's logging level. For example, if the Oracle BI Administrator has a logging level set at 2 and the LOGLEVEL is set at 1 in the repository initialization block then the Oracle BI Administrator's logging level will be set at 1.


3) BI Javahost logs - for identifying the issues with the charts and graphs generated by java from answers/dashboard reports; diagnosing the issues with EPM and OBIEE integration with custom authenticator. Located at: ORACLEBIDATA/web/log/javahost/jhost0.log

4) BI Scheduler.log is useful for diagnosing the issues with delivers/scheduler.
To increase the logging on Scheduler, Set Debug flag to true in the Scheduler Job Manager configuration window.
- located at OracleBI/Server/log directory.

(source: Metalink Note 1069199.1)

How to create WebLogic / OC4J / All as Windows Services

Steps to Start WebLogic as a Windows Service:


1. Go to $%WEBLOGIC_DIR%\wlserver_10.3\server\bin where WEBLOGIC_DIR is the home directory of weblogic installation.

2. Create a file called createSvc.cmd with following values:
(please make sure you mention the right domain name, path and admin server instance name)

    echo off
    SETLOCAL
    set DOMAIN_NAME=base_domain
    set USERDOMAIN_HOME=C:\app\product\10.3.3\mt_1\user_projects\domains\base_domain
    set SERVER_NAME=AdminServer
    set PRODUCTION_MODE=true (or False if you are in DEV mode)
    set JAVA_VENDOR=Sun
    set JAVA_HOME=C:\Java\jdk1.6
    set MEM_ARGS=-Xms256m -Xmx512m
    call "C:\app\product\10.3.3\mt_1\wlserver_10.3\server\bin\installSvc.cmd"
    ENDLOCAL

3. create a backup of InstallSvc.cmd (because we are going to alter the default service name to be something more meaningful: Oracle WebLogic )

4. Edit InstallSvc.cmd
- search for 'beasvc'
- replace
%WL_HOME%\server\bin\beasvc" -install -svcname:"beasvc %DOMAIN_NAME%_%SERVER_NAME%"

with the meaningful chosen service name:

%WL_HOME%\server\bin\beasvc" -install -svcname:"Oracle WebLogic %DOMAIN_NAME%_%SERVER_NAME%"

5. Execute createSvc.cmd script from same folder C:\app\product\10.3.3\mt_1\wlserver_10.3\server\bin\

- This should create service with name of "Oracle WebLogic  base_Domain_Adminserver" in the registry under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services

6.You can now start and stop the service from the Services control panel window.


NOTE: if you want to delete the win service created, in the command line, type:
> sc delete < SERVICE name>


(reference: http://www.sadikhov.com/forum/index.php?showtopic=118351)
More on this: http://download.oracle.com/docs/cd/E14571_01/web.1111/e13708/winservice.htm




OC4J as a Windows Service
Download  JavaService.exe from http://javaservice.objectweb.org/ 
Pre Requisite: JDK 1.5 plus should be present on the machine.
Instructions

1. cmd prompt and cd to the folder where the javaservice.exe resides

2. javaservice -install "Oracle BI: OC4J Service" "C:\Program Files\Java\jdk1.6.0_11\jre\bin\client\jvm.dll" -XX:MaxPermSize=128m -Xmx512m "-Djava.class.path=C:\app\oracle\product\10.1.3\bi_1\oraclebi\oc4j_bi\j2ee\home\oc4j.jar" -start oracle.oc4j.loader.boot.BootStrap -description "Oracle BI Oc4J Service"

3. now you can net start the service

NOTE: step 2) needs to be configured according to your system settings (i.e.: java jdk path, oracle BI path)

 ALL
http://obieetalk.com/obiee-11g-auto-start-all-windows-services 

OBIEE 11g

Finally, OBIEE 11g is out!!

Product download:
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-165436.html

Upgrade from 10.3.4.1:
http://download.oracle.com/docs/cd/E14571_01/bi.1111/e16452/summary.htm#BGBHEFCF

Documentation available at:
http://download.oracle.com/docs/cd/E14571_01/bi.htm

ENJOY!

Oracle DB Geocoding with Google Earth

I have found this great article on this blog.

UTILS

11g - Change Name from BI to custom

http://108obiee.blogspot.com/2009/03/how-to-change-date-format-mask-in-date.html

 http://bipconsulting.blogspot.com/ 

http://sranka.wordpress.com/2008/06/06/enabling-sso-authentication-for-obiee/ 

Get Day/Month/Year from a Calendar Date

Roll-Over functionality

Allkind of Get Date functions in OBIEE

How to Build an Academic Year Time Dimension

Working as a Business Intelligence consultant is exposing me to new challenges every day. Finding the needle in the stack of hay or coming up with an innovative solution can be sometimes a very difficult task. As such, I have decided to start my own OBIEE blog, where I will write about my own found solutions or about useful articles that helped me do my daily job.

I will start with a common task, that of building a calendar dimension.
I have found the following interesting entry on John Minkjan's OBI blog, containing tips on what should the time table contain. The full thread can be found here.

Below I will provide the sql query that I have used for building an Academic Year time dimension. The requirement was:
- populate the calendar with data starting with a specific date (representing the date when the data warehouse started being populated).
- the week-start-day was considered to be a Sunday
- the academic start year is 1 April of each calendar year.
The bellow is specific to these requirements:

TABLE AY_CALENDAR
  -- day level
  time_id                           date,
  day_name                       varchar2(20),
  day_number_in_week    number,
  day_number_in_month   number,
  day_number_in_year      number,
  working_day_flag           varchar2(1),
  --week level
  week_number                 number,
  week_number_pay         varchar(3),
  week_start_date            date,
  week_end_date             date,
  -- month level
  month_name                 varchar(20),
  month_number             number,
  month_no_of_days      number,
  -- quarter level
  quarter_number          number,
  beg_of_quarter           date,
  end_of_quarter          date,
  -- year level
  year_number               number,
  year_number_of_days   number,
  year_start_date          date,
  year_end_date            date



INSERT INTO ay_calendar
  ( time_id,
    day_name,
    day_number_in_week,
    day_number_in_month,
    day_number_in_year,
    working_day_flag,
    week_number,
    week_start_date,
    week_end_date,
    month_number,
    month_no_of_days,
    --end_of_cal_month,
    month_name,
    --days_in_cal_quarter,
    beg_of_quarter,
    end_of_quarter,
    quarter_number,
    year_number,
    year_number_of_days,
    year_start_date,
    year_end_date )
SELECT
  TRUNC( sd ) time_id,
  TO_CHAR( sd, 'fmDay' ) day_name,
  TO_CHAR( sd + 1, 'D' ) day_number_in_week,
  TO_CHAR( sd, 'DD' ) day_number_in_month,
  TO_CHAR( sd - add_months(TRUNC( add_months(sd,-3), 'YEAR' ),3) + 1, '000' ) day_number_in_year,
  CASE WHEN TO_CHAR( sd, 'fmDay' ) IN ('Sunday', 'Saturday') THEN 'N' ELSE 'Y' END working_day_flag,                                
  trunc((sd - NEXT_DAY(add_months(TRUNC(add_months(sd,-3),'YEAR' ),3)-7,'SUNDAY'))/7) + 1 week_number,
  NEXT_DAY( sd - 7, 'SUNDAY' )   week_start_date,   
  NEXT_DAY( sd - 1, 'SATURDAY' ) week_end_date,   
  TO_CHAR( add_months(sd,-3), 'MM' ) month_number,
  TO_CHAR( LAST_DAY( sd ), 'DD' ) month_no_of_days,
  TO_CHAR( sd, 'FMMonth' ) month_name,
  TRUNC( sd, 'Q' ) beg_of_quarter,
  ( CASE
      WHEN TO_CHAR( sd, 'Q' ) = 1 THEN
        TO_DATE( '03/31/' || TO_CHAR( sd, 'YYYY' ), 'MM/DD/YYYY' )
      WHEN TO_CHAR( sd, 'Q' ) = 2 THEN
        TO_DATE( '06/30/' || TO_CHAR( sd, 'YYYY' ), 'MM/DD/YYYY' )
      WHEN TO_CHAR( sd, 'Q' ) = 3 THEN
        TO_DATE( '09/30/' || TO_CHAR( sd, 'YYYY' ), 'MM/DD/YYYY' )
      WHEN TO_CHAR( sd, 'Q' ) = 4 THEN
        TO_DATE( '12/31/' || TO_CHAR( sd, 'YYYY' ), 'MM/DD/YYYY' )
    END ) end_of_quarter,
  TO_CHAR( add_months(sd,-3), 'Q' ) quarter_number,
  TO_CHAR( add_months(sd,-3), 'YYYY' ) year_number,
  ( add_months(TO_DATE( '12/31/' || TO_CHAR( add_months(sd,-3), 'YYYY' ), 'MM/DD/YYYY' ),3)
    - add_months(TRUNC( add_months(sd,-3), 'YEAR' ),3) ) + 1 year_number_of_days,
  add_months(TRUNC( add_months(sd,-3), 'YEAR' ),3) year_start_date,
  add_months(TO_DATE( '12/31/' || TO_CHAR( add_months(sd,-3), 'YYYY' ), 'MM/DD/YYYY' ),3) year_end_date
FROM (
    SELECT
      TO_DATE( '05/24/2008', 'MM/DD/YYYY' ) + rownum sd
    FROM dual
      CONNECT BY level <= 3650
);

I hope this helps and gives some hints on how to tackle creating and populating the time dimension at database level.
All the best until next time!