Oracle Apache Hadoop Hive ODBC Driver For OBIEE

Hi All,

Last few months have been a crazy ride, wrapping up on new opportunities and getting upto speed on Hadoop took all the free time away . During my read on Hadoop , I always wondered why we dont have out of the box ODBC driver? I tried using out of the box ODBC driver given by HortonWorks and it worked up to certain extend but running using cloudera was a challenge. Finally to the sigh, with 11.1.1.7 Oracle has introduced Oracle Apache Hadoop Hive ODBC Driver For OBIEE.

So far extracting the data using Hadoop have never been a challenge, but using the data to make sense out of it has been a constant challenge.  With this integration, there would be some amount of relief with the existing investment people have made in OBIEE. Hive’s SQL dialect, called HiveQL doenst support the full SQL-92 specification, please refer 11.1.1.7 Metadata Repository Builder’s Guide for not supported features.

For details on where to start please refer 11.1.1.7 Metadata Repository Builder’s Guide.  For importing the metadata from Hive you will have to download ODBC driver from oracle support web, for details please refer DocID 1520733.1 .

In coming weeks I will be working more on Big Data approach and Solutions. 

Hope this helps

Sunil S Ranka

“Superior BI is the antidote to Business Failure

 

 

Oracle Intelligence Virtual Summit 2012 – Aug 15-16

Dear All,

On behalf of BIWA board and volunteers, I would like to extend a personal invitation for — Oracle BIWA Virtual Summit 2012 — between Aug 15th and Aug 16th. This is the first ever Virtual Summit dedicated on Oracle’s BI technology. The summit runs over two days with dedicated topics on Warehousing and Advanced Analytics Focus on day 1 and Business Intelligence Focus on day 2. Usually these kind of Summit cost hunderds or couple of thousands of $. But BIWA presents this to you all for FREE $0. We have arranged 5 session on each day , with option of attending your choice of session based on your preference.

Key Note Speakers

• Day 1 – Business Intelligence to Predictive Analytics by James Taylor
• Day 2 – See More, Act Faster by Balaji Yelamanchili from Oracle

For more details and Registrations please visit link below ::

http://www.ioug.org/Events/OracleBIWAVirtualSummit2012Aug1516/tabid/342/Default.aspx

Looking forward to see most of you all. In case of any logistical issues, please feel free to contact me at sunil_ranka@yahoo.com.

Hope this helps.

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

Default OBIEE 11G Ports

Hi All

Recently came across a situation, in which someone  Due to some security issue someone wanted to bypass OBIEE’s default ports. After looking at multiple places, I could put all information together. I thought it would be a great share.

Even out of the box installation lets you change the configuration, during installation.

Once we selected one installation type from either Enterprise Install or Software only install and click next. Then in the next step it will ask for port configuration. Check Specify Ports using Configuration file and browse Just specify the newly assign port configuration file (staticports.ini , see the format below).

Default Port Numbers :

WEBLOGIC

• Domain Port No = 7001
• Oracle WLS BIEE Managed Server Port No = 9704
• Oracle WLS BIEE Managed Server SSL Port No = 9804

OPMN

• Oracle Process Manager Local Port No = 6100
• Oracle Process Manager Remote Port No = 6200
• Oracle Process Manager Request Port No = 6003

BIFOUNDATION

• Oracle BI Server Port No = 9703
• Oracle BI Server Monitor Port No = 9701
• Oracle BI Presentation Services Port No = 9710
• Oracle BI Scheduler Port No = 9705
• Oracle BI Scheduler Monitor Port No = 9708
• Oracle BI Scheduler Script RPC Port No = 9707
• Oracle BI ClusterController Port No = 9706
• Oracle BI ClusterController Monitor Port No = 9700
• Oracle BI JavaHost Port No = 9810

Format of staticports.ini File

[WEBLOGIC]

#The Domain port no. This is the listen port of Weblogic Adminserver for the domain.
Domain Port No = 7001

#The "content" port for the BIEE apps. This is the Weblogic Managed Server port on which BIEE applications are deployed.
Oracle WLS BIEE Managed Server Port No = 9704

#The SSL port for the Weblogic Managed Server
Oracle WLS BIEE Managed Server SSL Port No = 9804

[OPMN]

#Process Manager Local port no
Oracle Process Manager Local Port No = 6700

#Process Manager Remote port no
Oracle Process Manager Remote Port No = 6701

#Process Manager Request port no
Oracle Process Manager Request Port No = 6702

[BIFOUNDATION]

#The listen port for OracleBIServer component
Oracle BI Server Port No = 9703

#The monitor port for OracleBIServer component
Oracle BI Server Monitor Port No = 9701

#The listen port for OracleBIPresentationServices component
Oracle BI Presentation Services Port No = 9710

#The listen port for OracleBIScheduler component
Oracle BI Scheduler Port No = 9705

#The monitor port for OracleBIScheduler component
Oracle BI Scheduler Monitor Port No = 9708

#The script RPC port for OracleBIScheduler component
Oracle BI Scheduler Script RPC Port No = 9707

#The listen port for OracleBIClusterController component
Oracle BI ClusterController Port No = 9706

#The monitor port for OracleBIClusterController component
Oracle BI ClusterController Monitor Port No = 9700

#The listen port for OracleBIJavaHost component
Oracle BI JavaHost Port No = 9810

Hope This helps.

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

OBIEE 11g : File is too large, error while uploading RPD more than 40MB

Hi All

Recently ran into an very interesting problem, where using EM when we trying uploading RPD of 40MB size, we repeatedly failed with UI Error message:”Warning: The file upload failed. The file could not be uploaded because it is too large..” .  Sai Kodthivada, from team, did some reasearch and found that there is harcoding of 40MB in File location:

D:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp\_WL_user\em\jmb4hf\war\WEB-INF\web.xml

<!-- Maximum memory per request (in bytes) -->
<context-param>
   <param-name>org.apache.myfaces.trinidad.UPLOAD_MAX_MEMORY</param-name>
<param-value>1024000</param-value>
</context-param>

<!-- Maximum disk space per request (in bytes) -->
<context-param>
<param-name>org.apache.myfaces.trinidad.UPLOAD_MAX_DISK_SPACE</param-name>
<param-value>50960000</param-value>
</context-param>

We tried modifying the file, but we were not successful, after filing an SR with oracle, support came with an alternative.
An alternative to using EM to upload an RPD may be found in Doc ID: “How To Deploy An OBIEE 11g RPD Using An MBean Via Enterprise Manager And A WLST Script (Doc ID 1319333.1)”.

Hope This helps.

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

Hope This helps.

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

Sample OBIEE 11g Usage Tracking Repository

Hi All

Some of you must have ran into the issue where OBIEE 11g Usage Tracking RPD Fails To Open In Administrator With [nQSError: 36015]. After working with oracle they helped us with sample usge tracking repository . Thanks to Steve Fitzgerald from Oracle , in anticipation of customers needing the file, he attached the RPDs to the following doc.

The Sample OBIEE 11g Usage Tracking RPD Fails To Open In Administrator With [nQSError: 36015] (Doc ID 1336567.1)

Hope this helps

Sunil S Ranka
“Superior BI is the antidote to Business Failure”

Unable To Create Session Variable Using Obiee 11.1.1.5 Admin Tool

Hi All

An interesting thing happen, when I was trying to choose a connection pool for creating a Session variable Init Block , connection pool Select button was disabled. This functionality worked in 11.1.1.3. Just to avoid any doubt I restarted admin tool, followed by machine restart, but end result didn’t change. After looking around, I remembered that in one of the metalink they talked about “Allow First Connection for init block”. I tried that option and it work . Below are the details ::

Issue :

Select button is disabled when trying to choose a connection pool for creating a Session variable Init Block

Solution:

Check “Allow First Connection for init block” to true

For setting “Allow First Connection for init block”  follow below steps :

1. In the Administration Tool, select Tools, then select Options to display the Options dialog.
2. On the General tab, select “Allow First Connection for init block. (this option is not a best practice and might cause performance issues.)

Hope This Helps

Sunil S Ranka
“Superior BI is the antidote to Business Failure”

 

OBIEE Grand Total Issue with UNION Report (Combine Reports)

Hi All

In 10g we had a bug where variance Grand Total didn’t work. John Minkjan covered it in great details at his OBIEE 101 Blog .

While using 11g for the similar variance on a report, it was nice to see the results showing up correctly. But we hit a road block when we were using a variance calculation on UNION report, YES!!!!! the variance calculation, still doesnt work when you are using combine. Oracle has an existing bug, and I have created another bug#12546303 – INCORRECT GRAND TOTAL ISSUE ON UNION REPORT.

In short if your varainace column is not working, don’t split your hair, it could be possibly a bug

More Details on Metalink:

NOTE: 941888.1,540371.1

BUG:5610580 – UNION QUERY NOT CREATING GRAND TOTAL % CALCS CORRECTLY
BUG:6655852 – PIVOT TABLE GIVES INCORRECT ANSWER IN TOTAL IN UNION QUERY
BUG:8285205 – INCORRECT PERCENTAGE VALUE IN PIVOT TABLE
BUG:8580420 – DOES NOT CALCULATE THE PIVOT TABLE VALUES CORRECTLY.
Bug 8662690: SERVER COMPLEX AGGREGATE RETURNS FIRST ROW ON THE COMBINED QUERY’S GRAND

Hope This Helps

Sunil S Ranka
“Superior BI is the antidote to Business Failure”

11.1.1.5.0 Release

The most awaited release of OBIEE is publicly available 11.1.1.5.0 :

OBIEE 11.1.1.5.0 and OBI Apps 7.9.6.3 download link:

http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/biee-111150-393613.html

OBIEE 11.1.1.5.0 documentation link:

http://download.oracle.com/docs/cd/E21764_01/bi.htm

OBI Apps 7.9.6.3 documentation link:

http://download.oracle.com/docs/cd/E20490_01/index.htm

Hope This Helps

Sunil S Ranka
“Superior BI is the antidote to Business Failure”

Fight of co-existance :: Application Role (OBIEE 11g) and Groups (OBIEE 10g)

Hi All

Past few I have been implementing OBIEE 11g with BW SAP backend/data source. The journey wasn’t easy and challenges are still not over. But during 10g upgrade, I came across an interesting situation where I had to design security for 11g OBIEE but support 10g security from upgrade in the same rpd and webcatalogue

As we all are aware 10g requires GROUPS (data/object level security) and WEBGROUPS (Webcatalogue level object security) for security. Due to backward compatibility GROUPS and WEBGROUPS are present in 11g as well.

Interesting Observation :

When you set ROLES and GROUPS session variable, ROLES takes precedence over GROUPS and only ROLES values are present, when you click on My Account –> Roles and Group. This could be potential oracle bug, but in my case we reverted back to 10g security where we are continuing to use GROUPS and WEBGROUPS variable.

Hope This Helps

Sunil S Ranka
“Superior BI is the antidote to Business Failure”

OBIEE 11g Security – Default BI/Application Roles

Following are the default Application Roles:

• The BIAdministrator role grants administrative permissions necessary to configure and manage the Oracle Business Intelligence installation. Any member of the BIAdministrators group is explicitly granted this role and implicitly granted the BIAuthor and BIConsumer roles.

• The BIAuthor role grants permissions necessary to create and edit content for other users to use, or to consume. Any member of the BIAuthors group is explicitly granted this role and implicitly granted the BIConsumer role.

• The BIConsumer role grants permissions necessary to use, or to consume, content created by other users. By default, every Oracle Business Intelligence authenticated user is part of the BIConsumers group and does not need to be explicitly added to the group or role

Authenticated Role
Every user who successfully logs in to Oracle Business Intelligence becomes a member of the authenticated role, and it is a replacement for the 10g Everyone PresentationCatalog group. The authenticated role is not stored in the obi application stripe and is not searchable in the Oracle Business Intelligence policy store. However, the authenticated role is displayed in the administrative interface for the policy store, is available in Application Role lists, and can be added as a member of another Application Role.

Hope This Helps

Sunil S Ranka
“Superior BI is the antidote to Business Failure”