OBIEE 12c File Locations

If you wonder, where are RPD, Catalog and all the other locations in OBIEE 12c. Here is the path to few files:

Live RPD Path:
[ORACLE_BI_HOME]/user_projects/domains/biee/bidata/service_instances/ssi/metadata/datamodel/customizations

Catalog Path:
[ORACLE_BI_HOME]/user_projects/domains/biee/bidata/service_instances/ssi/metadata/content

To Start, Stop or Start and Deploy RPD BI Sevices [start.sh, stop.sh, status.sh, datamodel.sh]:
[ORACLE_BI_HOME]/user_projects/domains/biee/bitools/bin

To Start/ Stop Weblogic Server, NodeManager 
[startNodeManager.sh, stopNodeManager.sh, stopWeblogic.sh, startWeblogic.sh]:
[ORACLE_BI_HOME]/user_projects/domains/biee/bin

InstanceConfig.xml Path:
[ORACLE_BI_HOME]/user_projects/domains/biee/config/fmwconfig/biconfig/OBIPS

NQSConfig.INI Path:
[ORACLE_BI_HOME]/user_projects/domains/biee/config/fmwconfig/biconfig/OBIS

ssl Path:
[ORACLE_BI_HOME]/user_projects/domains/biee/config/fmwconfig/biconfig/core/ssl

ODBC.ini Path:
[ORACLE_BI_HOME]/user_projects/domains/biee/config/fmwconfig/bienv/core

obis.properties Path:
[ORACLE_BI_HOME]/user_projects/domains/biee/config/fmwconfig/bienv/OBIS

Catalog Manager:
[ORACLE_BI_HOME]/bi/bifoundation/web/catalogmanager

Cheers!
Bose

OBIEE 12c with MySQL Community Edition Connectivity

This post brings the workaround on How to connect OBIEE 12c Environment with MySQL Server Community Edition.
This is Tested on OBIEE 12.2.1.3.0

Assumption :
OBIEE 12c comes with DataDirect Connections and support only for Enterprise Edition. But our requirement is to connect Community Edition as our DW is developed in there.

MySQL Community Edition ( i.e. FREE edition )
Server OS is Redhat 7 64-bit

Import tables in OBIEE Admin Tool
Download latest ODBC Driver and Install, set up DSN,  , Create connection pool, Import metadata, Define business and presentation layers.

Problem:   
Data Direct Drivers are shipped to connect OBIEE to MySQL.  Unfortunately these do not work as you get a message "[nQSError: 16001] ODBC error state: S1000 code: 0 message: [DataDirect][ODBC MySQL Wire Protocol driver]Connections to MySQL Community Servers are not supported.  Please contact MySQL to obtain a MySQL Enterprise or Commercial version.."

Solution:
Step1:
Use MySQL native ODBC instead of DataDirect ODBC.

You can get these from
http://dev.mysql.com/downloads/connector/odbc/#downloads

This is an rpm download and install, you need root privileges to do this.  DO NOT DOWNLOAD THE ANSI driver as it does not work.  
We have to download for Redhat 64bit Server:
mysql-connector-odbc-setup-8.0.13-1.el7.x86_64.rpm

Step2:
Update the configuration in ODBC.ini

Go to the following directory and update the ODBC.ini file like below:

ORACLE_BI_HOME/domains/biee/config/fmwconfig/bienv/core

Note: We can use either OBDC Driver (libmyodbc8w) or ANSI Driver(libmyodbc8a).

[MySQLDevTest]
Driver         = /usr/lib64/libmyodbc8a.so
Description  = Connector/ODBC 8.0 ANSI Driver DSN
SERVER       = mysql.dbhostname.com
PORT          = 3306
USER          = username
Password     = password
Database     = dbname
OPTION       = 3

Step3:  

Go to the following directory and update the obis.properties file for LD_LIBRARY_PATH to point OBDC Driver.

ORACLE_BI_HOME/domains/biee/config/fmwconfig/bienv/OBIS

e.g. 

"LD_LIBRARY_PATH" value="/usr/lib64:$ORACLE_HOME/common/ODBC/Merant/6.0SP1/lib$:$ORACLE_HOME/bifoundation/server/bin$:$ORACLE_HOME/bifoundation/web/bin$:$ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin$:$ORACLE_HOME/bifoundation/odbc/lib$:$ORACLE_INSTANCE$:$ORACLE_HOME/lib"

Import Points to Note:

Connection Pool Name, ODBC.ini configuration in OBDC Data Sources name and MYSQL configuration name has to be same.

E.g: MySQLConnTest

Cheers!

Bose

Starting and Stopping OBIEE 12c Services in Linux

Go to the following directory:

/obiee/Oracle/Middleware/Oracle_Home/user_projects/domains/biee/bitools/bin

Run the below commands to start, stop or to know the status of entire services.

To Stop All the Services

[root@obiee bin] ./stop.sh

To Know the  Status of All Services: 

[root@obiee bin] ./status.sh

To Start all BI Services

[root@obiee bin] ./start.sh

To particularly start or stop one or few services use below commands

Syntax:

./start.sh -i service_name1, service_name2

e.g: [root@obiee bin] ./start.sh -i obis1

./stop.sh -i servicename1, servicename2

[root@obiee bin] ./stop.sh -i obis1

In Windows, instead of .sh use .cmd. for Example. start.cmd

Cheers!

Bose

Setting Java Home in Linux

Edit the .bashrc or .bash_profile file and add the below line:

[$] vi .bashrc

export JAVA_HOME="Enter_the_Java_Home_Directory"

e.g: export JAVA_HOME=/app/JDK/jdk1.8

Here is the complete steps:

https://access.redhat.com/documentation/en-US/JBoss_Communications_Platform/5.0/html/Platform_Installation_Guide/sect-Configuring_Java.html

After updating it, just source the file so it would be effective immediately and you could verify with the below command

[$] source .bashrc

[$] echo $JAVA_HOME

Cheers!
Bose

Starting and Stopping Oracle Listener in UNIX

Below oracle link is having enough thorough information handling Oracle Listener services in LINUX/UNIX

https://docs.oracle.com/cd/B28359_01/server.111/b32009/strt_stp.htm#UNXAR173

UsageTracking in OBIEE 12c

UsageTracking:

The Oracle BI Server supports the accumulation of usage tracking statistics that can be used in a variety of ways such as database optimization, aggregation strategies, or billing users or departments based on the resources that they consume. The BI Server tracks usage at the detailed query level.

When you enable usage tracking, statistics for every query are inserted into a database table or are written to a usage tracking log file. If you use direct insertion, then the BI Server directly inserts the usage tracking data into a relational database table. It is recommended that you use direct insertion to write statistics to a database table.

When the BI Server starts, it validates the column names in the metadata against the list of valid columns in the usage tracking table. The following events occur:

To set up the usage tracking statistics database:

If we have the OBIEE in place already then we have RCU schmeas ready and Installed in our database which has all the tables required for Usagetracking

The RCU-created table names for usage tracking are S_NQ_ACCT, S_NQ_DB_ACCT, and S_NQ_INITBLOCK.

 Setting up direct insertion usage tracking:

1. On the Oracle BI Server computer, open the NQSConfig.INI file in a text editor. You can find this file at:

   BI_DOMAIN/config/fmwconfig/biconfig/OBIS

   Make a backup copy of the file before editing.

2. In the [USAGE_TRACKING] section, update the following parameters:

• Set ENABLE to YES.

• Set DIRECT_INSERT to YES.

• Set PHYSICAL_TABLE_NAME to the name of the fully-qualified database table for collecting query statistic information, as it appears in the Physical layer of the Oracle BI repository. For example:

  PHYSICAL_TABLE_NAME = "My_DB"."DEV_BIPLATFORM"."S_NQ_ACCT";
  

• Set CONNECTION_POOL to the name of the fully-qualified connection pool for the query statistics database, as it appears in the Physical layer of the Oracle BI repository. For example:

  CONNECTION_POOL = "My_DB"."Usage Connection Pool";
  

• Set INIT_BLOCK_TABLE_NAME to the name of the fully-qualified database table for inserting records that correspond to the initialization block statistics, as it appears in the Physical layer of the Oracle BI repository. For example:

  INIT_BLOCK_TABLE_NAME = "My_DB"."DEV.BIPLATFORM"."S_NQ_INITBLOCK;
  

• Set INIT_BLOCK_CONNECTION_POOL to the name of the fully-qualified connection pool for the table for inserting records that correspond to the initialization block statistics, as it appears in the Physical layer of the Oracle BI repository. For example:

  INIT_BLOCK_CONNECTION_POOL = "My_DB"."Usage Connection Pool";

3. Save and close the file.

4. Restart the Oracle BI Server.

Note:

A sample usage tracking repository model is provided with the Oracle Business Intelligence installation at: ORACLE_HOME/bi/bifoundation/samples/usage_tracking.bar.

This path applies to 12c versions, but does not apply to earlier versions.

To use the sample usage tracking repository, you modify the connection pool to point to your database, then merge the usage tracking repository with your existing repository.

Refer here: https://docs.oracle.com/middleware/1221/biee/BIESG/usage_track.htm#BIESG189

Cheers!

Bose

Adding Environment Variable in bash_profile in Linux

In a oracle BI environment, I had the following variables but I had to create it every time when I login into putty.
So How can we create an environment variable so It will be always there in server even after my session ends and I can access it all the time.

ORACLE_BI_HOME
ORACLE_BI_DOMAIN_HOME
LD_LIBRARY_PATH

Here is the solution:

open a file and save all your environment variables in it and add that file in bash_profile file.

Here I have opened eraclebi_env.sh file to have all my variables added the path.

Now open your bash_profile and the environment file path to this profile file like mentioned in the below sceenshot.

 

Now run env or echo $env_name to check the variables.

Downloading and Uploading RPD in OBIEE 12c

Here is steps on how to download and upload RPD in OBIEE 12c

Upload RPD:

**********************************************************************

Step1: 
Go to the following path
/obiee/Oracle/Middleware/Oracle_Home/user_projects/domains/biee/bitools/bin

[root@obiee bin]$ cd <$DOMAIN_HOME>/bi/bitools/bin

Step2: 
Enter the following commands in shell to upload the RPD:

Syntax:
./datamodel.sh uploadrpd -I /home/bose/Sample.rpd -W RPDPasword -SI ssi -U weblogic -P WeblogicPassword

Example:
[bose@oracle bin]$ ./datamodel.sh uploadrpd -I  /home/pcs_user/Sample.rpd -SI ssi -U weblogic -P Admin123  -W Admin123

Service Instance: ssi
Operation successful.
RPD upload completed successfully.

Download RPD:

*****************************************************

Step1: 
Go to the following path
cd $ORACLE_BI_DOMAIN_HOME/bi/bitools/bin

[root@obiee bin]$ cd $DOMAIN_HOME/bi/bitools/bin

Step2: 
Enter the following commands in shell to download the RPD

Syntax:
./datamodel.sh downloadrpd -O  /home/bose/Sample1.rpd -W RPDPassword  -SI ssi  -U weblogic -P WeblogicPassword

Example:
[bose@oracle bin]$ ./datamodel.sh downloadrpd -O  /home/bose/Sample.rpd  -SI ssi -U weblogic -P Admin123 -W Admin123

Operation successful.
RPD download completed successfully.

If  Your OBIEE is installed in Windows Server or using window shell script, Use datamodel.cmd instead of datamodel.sh

Syntax:

I - Input Directory

O - Output Directory

P - Admin Password

W - Repository Password

U - Admin User Name

SI - Service Instance

Cheers!

Bose

OBIEE 12c (12.2.1.4.0) is Awailable for Download

OBIEE 12c (12.2.1.4.0) Download Link:

https://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/default-4441820.html

OBIEE 12c (12.2.1.3.0) Download link:

https://www.oracle.com/technetwork/middleware/bi/downloads/default-3852322.html

OBIEE 12c Tutorials:

If would like to learn on creating BI Dashboards and RPD, then the following link will help us to learn.

https://www.oracle.com/technetwork/middleware/bi-enterprise-edition/tutorials/index.html

Cheers!

Bose