Bye Bye 2012 !!! Welcome 2013

Hi All

It just seems like yesterday, but its again time of the year where we need to recap what took place last year. 2012 was a year of “World coming to an End“, Thank to God, IT directors didn’t think the same and didnot spent all the dollars. 2012 Year started on high note with 11G picking up its momentum.  But at point client took their own time to decide on upgrades. With bitten badly of older releases of OBIEE , the foul taste still didn’t leave customers too happy. 11.1.1.5 was a solid answer to many questions and .6 followed the foot steps.

Year 2012 saw an increase trend in business looking for more agility and flexibility in BI solutions, oracle’s acquisition of  ”Endeca” could be a potential solution. Next few releases we would see endeca and OBIEE getting a focal convergence.  Personally 2012 was a satisfactory year, changing roles from delivery to more strategic was one of the move I personally enjoyed. Few successful pre-sales engagements and client visit to Dubai were few memorable events.

2012 OOW, was a year of Oracle Cloud. A year back cloud was a box, but in 2012, the cloud was the only thing. Larry’s keynote was much awaited, right from db to application, everything was in cloud.2012 OOW, was a year of repeat content, but saw some improvement in software releases. Year after year presentations seemed the same. One thing which I personally liked was demo of Endeca’s integration with EBS.  This integration has tremendous potential, but time ahead would prove the facts. Hands on exposure to endeca, Exalytics was satisfactory. This year distinction at OOW was stress on Big Data. It was more on how to use Big Data technologies and oracle db.  Over all OOW 2012 was a time well spent affair.

Oracle Product Offerings at OOW-2012

Treasure Island Party

Pearl Jam at OOW 2012

Personally it was a satisfactory year, along with professional career, had a great joy of bundle with tiny 10 toes, a baby boy (Saarth). With Saarth’s arrival, now we have a complete family (Rinku – Wife, Samvi – Daughter, Saarth – Son ). Samvi was waiting for Saarth for a long time.

Along with this, I had my 1st media print on future of BI http://issuu.com/itnext/docs/it_next-vol-03-issue-10-november_-2012/49 ( Article starts from Page 47).

In all 2012 was a satisfactory year, waiting for 2013. Thank You for all your continue support and looking forward for the same in 2013.

Wishing Every one a Happy and Prosperous New Year, Be Safe!!!!!!

Thanks

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

Huge Temp FIles (nQS*.TMP) files are generated by the BI Server

Hi All

Hope you all had great Thanks Giving break, I was away from action as I was in the man made wonders of Dubai attending a client proposal. In short Dubai is a wonderland and would take some time off to write about the same. At one of the recent client visit, ran into a very basic peculiar issue, thought would share with you all.

It was 10G to 11G upgrade and after running 11G server for sometime, huge temp files (nQS*.TMP) were getting created. Due to this client file system was getting filled up and at times they had to restart the server. After spending few hours came across some suggestions from Oracle support, hence thought it would be worth while sharing with you all.

The Temp files have the following 3 main functions functions

• Perform data manipulations required by column request functions and/or formulae that cannot be pushed down to the source database(s).
• Perform data sorts to combine and/or aggregate return sets.
• Prepare the final result set for OBIPS processing.

Possible Solutions

• Use aggregated tables instead of detail tables so that less number of rows are pulled resulting in smaller dataset to be processed
• Reducing the column width will reduce the tmp file size.
• If possible Avoid unnecessary processing like Sort, Formatting , front end calculation
• Use data base ORDER BYs to reduce the load
• Configure multiple file location for temp file storage (‘WORK_DIRECTORY_PATHS’ param in NQSConfig.ini)

For more details please refer to oracle document ID 857571.1 and 863864.1.

Hope This Helps

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

OBIEE 11.1.1.6 Bug Fixes

Hi All

As 11.1.1.6 has released, people are interested in what is stored in BI antique. Below is the link which lists all the possible list of bug fixes.

https://docs.google.com/document/d/1ckfLg1RUo6SE_xm7F7JYKIasIOB9-aDmNJ7E176qQWo/edit

Hope This helps

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

Fusion Applications – Overview of Business Intelligence and Reporting components

Hi All,

While looking through Oracle Support came across an informative “Overview of Business Intelligence and Reporting components”

 

Fusion Applications includes a variety of Business Intelligence and Report functionality. Some of these are exposed to the end-user, and some exist as middleware layers.

This document briefly lists the components, their common acronyms and the functionality that they bring to Fusion Applications.

Please note that the column titled “Function” contrass whether the component is a tool (infrastructure), or content (an analysis or report definition which is delivered with Fusion Apps).

BI Component	Function	Description	Where exposed in the Applications UI (example)
Oracle BI Enterprise Edition (OBIEE)	Middleware Tools and infrastructure	Oracle BI Enterprise Edition (Oracle BI EE) is a comprehensive set of enterprise business intelligence tools and infrastructure that includes a scalable and efficient query and analysis server, an ad-hoc query and analysis tool, interactive dashboards infrastructure, proactive intelligence and alerts functionality, and an enterprise reporting engine. Underlies most BI analyses and reports.	N/A
Analysis Editor	Ad-hoc analysis designer	The “Analysis Editor” (or Answers) lets you explore and interact with information by visually presenting data in tables, graphs, pivot tables, and so on. You can include the views that you create in an analysis, for display in Fusion Apps pages or dashboards. A sub-component of OBIEE.	Via Reporting Pane or Navigator: Tools
Essbase	Middleware Tools and infrastructure	Oracle Essbase is an online analytical processing (OLAP) server that provides an environment for deploying prepackaged applications or developing custom analytic and enterprise performance management applications. Example usage:  underlies Financial Reporting.	Financials > GL
BI Composer	Ad-hoc analysis designer	BI Composer is a simple-to-use wizard that allows you to quickly and easily create, edit, or view analyses without the complexities of the Analysis Editor. A sub-component of OBIEE.	Via Reporting Pane or Navigator: Tools
Oracle Transactional Business Intelligence (OTBI)	Analyses	Oracle Transactional Business Intelligence is a complete, prebuilt BI solution that deliver intuitive, role-based intelligence throughout an organization. It uses Oracle BI Enterprise Edition as it’s technology layer to provide an easy-to-use interface for business users to perform current state analysis of their business applications. Pre-constructed queries and reports are executed real time against the transactional schema supported by a layer of view objects. An OBIEE repository (.rpd) and Web Catalog are supplied.	CRM Sales  Opportunity Watch Real Time
Oracle Business Intelligence Applications (OBIA)	Analyses	Oracle Business Intelligence Applications are complete, prebuilt BI solutions that deliver intuitive, role-based intelligence throughout an organization. Oracle Business Intelligence Applications analyses use a data warehouse. Oracle Business Intelligence Applications supplies the warehouse database schema and the logic that extracts data from the Oracle Fusion Applications transactional database and loads it to the warehouse. Oracle Fusion Applications end users interact with the information in the Oracle Business Analytics Warehouse via analyses exposed on Fusion Applications pages, also using Oracle BI Enterprise Edition components (such as Analysis Editor, BI Composer and Dashboards). Datawarehouse Console (DAC) is used to control and orchestrate extract-transform-load (ETL). Informatica PowerCenter ETL tool is a third-party ETL platform (available from eDelivery) that performs the detailed extract, transform, and load (ETL) operations for the data warehouse. An OBIEE repository (.rpd), Web Catalog, DAC repository and Informatica respository are supplied.	Financials > Customer Profitability
Financial Reporting	Reports	Oracle Hyperion Financial Reporting (Financial Reporting) is a powerful tool for designing and presenting analytic data graphically. You can design traditional financial report formats such as cash management reports, profit and loss statements, and balance sheets. You can also design nontraditional formats for financial or analytic data that include text and graphics. Use Financial Reporting to create and deliver financial reports to the General Ledger system. Reporting administrators use EPM Workspace with financial reports to create and modify reports, books, and batch definitions and to schedule batch jobs via the reporting scheduler user interface within EPM Workspace. Reporting users run and view reports using the General Ledger user interface; they can launch reports in an IFrame within the General Ledger browser window.	Financials – Financial and Statutory Reporting
BI Publisher (BIP)	Middleware Tools and infrastructure	WYSWIG Tool for creating pixel precision reports. Oracle Business Intelligence Publisher is an enterprise reporting tool for authoring, managing, and delivering reports from multiple data sources in multiple formats through multiple channels. A sub-component of OBIEE.	Via Reporting Pane or Navigator:  Tools
BIP Reports	Reports	Pre-constructed pixel perfect formatted reports	Fusion Financials contains over 100 pre-constructed BIP reports, for example. 1099 report
Oracle Real-Time Decisions (RTD)	Middleware Tools and infrastructure	Oracle Real-Time Decisions enables you to develop adaptive enterprise software solutions. These adaptive solutions continuously learn from business process transactions while they execute and optimize each transaction, in real time, by way of rules and predictive models. A sub-component of OBIEE.	N/A
RTD Reports/Content/CRM Sales Predictor	Analyses		CRM Sales Prospector
Oracle Data Miner (ODM)	Middleware Tools and infrastructure	Oracle Data Mining automatically finds relationships hidden in data, by expanding SQL  it adds analytical verbs–such as predict, cluster, detect, and classify–also by embedding cutting-edge algorithms in the Oracle Database kernel. Underlies CRM Sales Predictor.	N/A
ADF based Reporting	Reports	Reports and Graphs which use ADF objects to create the UI look and feel , where the data has been sourced from Oracle Business Intelligence. These use an ADF component called BI Logical SQL VO, which in essence is a special type of VO that executes a logical SQL statement against the BI server. These VO’s are referenced in the .rpd ( BI’s common semantic layer) Note that there are some ADF based graphs and reports which do not use BI as a source for data.

 

 

 

 

 

 

  The above entire text has been taken from oracle support document [ID 1334765.1]

Hope this helps

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

People who all influenced me in Year 2011

Dear All,

I was sitting around and thinking about few people , whom I met during the course of 2011, some of them left a mark and impression, hence thought of mentioning them in words.

Personally it was satisfactory year,

• The project I implemented,won “Fusion Middleware Innovation Awards”
• Implemented 11G with BW-SAP as datasource (odd, on which oracle also wouldn’t bet)

Oracle Fusion Middleware Innovation Award

• Met some of the people whom I had always admired likes of murali subramanian and Balaji Yelamanchili from Oracle, Ming Tsai of infosys to mention few.

Murali Subramanian Larry Ellison Of Oracle, India

During my day at Oracle, Murali was talk of town, he was first one to venture into India and grew Oracle’s IDC (India Development Center) from no where to little over 10,000 people in couple of years. I would attribute most of Oracle’s outsourcing success to Murali. 2 hours I spent with him were one of the most cherished moment of OOW and 2011. A truly amazing and down to earth personality.

Balaji Yelamanchili From Oracle

I had opportunity to meet Balaji during collaborate-11 at florida, on behalf of BIWA board, I had invited him as a featured guest. His candid personality was simply astonishing, in spite of being at the that level his eagerness to learn BI was commendable. His customer focused attitude helped one of our key customer to go live against all odds. I heard him again at OOW-11, his transformation from EMC, Documentum to Oracle BI is simply amazing. In all, Oracle BI is in safe hands.

Ming Tsai From Infosys, Managing Director & Chief Client Officer

I had heard Ming on couple of occasions (OOW-10 and OOW-11), his unique style of presentation had made an impact, even though during OOW-10, kris gopalakrishnan’s presentation was dry, Ming made it interesting, he did justice once again to S.D. Shibulal’s OOW-11 presentation. His stage presence and charisma is some thing adorable. I run into Ming Tsai, during OOW’s Treasure Island party. He was nice and kind enough to spend some time,talk and most importantly he helped setting up camera for nice shot. In short he is well rounded .

Met few more smart people , The Real OBIEE 11G Heros, please read post http://sranka.wordpress.com/2011/06/23/453/

Thanks You All for reading following.

Hoping and wishing you all a happy and prosperous new year. Be safe and enjoy.

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

What a Year it was !!! 2011.. BI .. BI and More BI ..

Dear All,

Keeping on with the tradition of a yearly round-up this is the time of the year, where I take a moment and revisit, what happen in whole year.

Started 2011, on a low note with doing something different, preferably in the newer technology, 11G was the preferred choice, but buggy 11.1.1.3  release had put many customer on hold. But it was April, where I got an opportunity to work on 11G, that to also an unique project where OBIEE needed to be integrated with BW SAP. During the course met few smart and dedicated people.

2011, what a year, personally and professionally very satisfactory, ranging from learning technology, meeting smart people, meeting people who could influence me, winning awards, the list is end less, I wish this year had more than 12 months.

Oracle BI and 2011

This year 2011, specially has been a year of many excitement for an BI professional innovations, announcements, adoptions, budgets, you name it. Every thing was here.

Oracle announced 11G towards the end of 2010, but 2011 was the one where it picked up like a wild fire.

2011 was the year, Oracle competing with hardware vendors, Oracle’s Exadata did fairy well. Having worked on Teradata and some experience on Exadata, Oracle is fairing well on Exadata,  I dont have statistics, but performance wise I always felt Exadata was better than Teradata.. Oracle’s Exalytics seems to be by far answer to in memory analysis, especially target towards Essbase cubes. Over all 11.1.1.5 was a satisfactory release. Oracle delivered on the promise and they were on the dot and target.

In 2012, Oracle’s investment in mobile, seems to be promising and with 11.1.1.6 it will become more prominent, some of the Oracle Mobile demos at OOW were impressive. Oracle has no choice, but they are catching up and they are catching up faster than any one. Only player who I admire for mobility is Microstrategy, but with Oracle owning ERPs at most of the prominent places, OBIEE becomes default choice of the ecosystem.

( Oracle Open World )OOW-11

As usual OOW was great fun this year, thanks to technological innovations, content was not stale, with Exalytics, OBIEE 11.1.1.6, Essbase there was enough to be busy,confused and picky for 4 days.. Larry made it very interesting with pick on SalesForce.com, last year it was IBM and this year it was “marc benioff’s” turn.

It was interesting to listen in to Larry and Marc on “What is Cloud ?” , for a moment we all felt that we all are in the midst of history where two giants are redefining the “CLOUD”, which we all thought is already a mature concept.

As usual Treasure Island party was a blast, “sting” was on the top of the world. Warmth of his singing, made all of us forget the chilliness of San Francisco Bay. It was great crowd, great fun.

Mark Hurd Interviewing with Bloomberg

Oracle Open World Showcase

Treasure Island Party

Sting At Oracle Open World

Personal

Personally it was satisfactory year,

• The project I implemented,won “Fusion Middleware Innovation Awards”
• Implemented 11G with BW-SAP as datasource (odd, on which oracle also wouldn’t bet)

Oracle Fusion Middleware Innovation Award

In all, satisfactory year, looking forward for the new year.

Wishing Every one a Happy and Prosperous New Year, Be Safe!!!!!!

Thanks

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”

Analytics Cache Purge ODBC Extension Functions

Siebel Analytics version 7.7 provided three ODBC extension functions for purging query cache entries: one to purge by query, one to purge by physical table, and one to purge all cache entries in the entire query cache. The subsections below describe each of the three functions.

Even though its not document as part of 11g, but we have implemented at one of the client and it worked as is (Please use at your own risk)

Purge by query

The table below formally defines the ODBC extension function for purging a cache entry associated with a specified query:

Usage: Call SAPurgeCacheByQuery (?)

Input Arguments:

Argument Number	Column Name	Data Type	Data Size	Nullable
1	SQL Query	SQL_C_CHAR	64 KB	No

Output result set: None

SAPurgeCacheByQuery will purge a cache entry that exactly matches the logical query plan derived from a specified query. This function takes one, and only one, parameter representing query text.

For example, suppose you have a query cache entry generated from the following query that retrieves the names of all employees earning more than $100,000:

select lastname, firstname from employee where salary > 100000;

The call below programmatically purges the cache entry associated with this query:

Call SAPurgeCacheByQuery('select lastname, firstname from employee where salary > 100000' );

Purge by table

The table below formally defines the ODBC extension function for purging a cache entry associated with a physical table:

Usage: Call SAPurgeCacheByTable(?,?,?,?)

Input Arguments:

Argument Number	Column Name	Data Type	Data Size	Nullable
1	Database Name	SQL_C_CHAR	128 KB	Yes
2	Catalog Name	SQL_C_CHAR	128 KB	Yes
3	Schema Name	SQL_C_CHAR	128 KB	Yes
4	Table Name	SQL_C_CHAR	128 KB	Yes

Output result set: None

SAPurgeCacheByTable will purge all cache entries associated with a specified physical table name (fully qualified) for the repository to which the client has connected. This function takes up to four parameters representing the four components of a fully qualified physical table name:  database, catalog, schema and table name proper.

For example, suppose that you wish to purge the cache entries associated with a table having the following fully qualified name in the physical layer of the Siebel Analytics repository:

DBName.CatName.SchName.TabName

The call below programmatically purges the cache entries associated with this physical table:

Call SAPurgeCacheByTable( 'DBName', 'CatName', 'SchName', 'TabName' );

Purge All

The table below formally defines the ODBC extension function for purging all entries in the entire query cache:

Usage: Call SAPurgeAllCache()

Input Arguments: None

Output result set: None

The call below illustrates the function’s usage:

Call SAPurgeAllCache();

Invoking ODBC Extension Functions

You can call these functions using the nqcmd.exe command-line executable.
The syntax of the call will be as follows:

nqcmd -d "Analytics Web" -uadministrator -psadmin -spurge.txt

where purge.txt contains the call (for example, call SAPurgeAllCache()).

Below is sample file I wrote, which will take all the txt files and run against the weblogic server. Doing this you can seed multiple file using one batch file.

@echo off
setLocal EnableDelayedExpansion
cd %1
FOR /F "TOKENS=1* DELIMS= " %%A IN ('DATE/T') DO SET CDATE=%%B
FOR /F "TOKENS=1,2 eol=/ DELIMS=/ " %%A IN ('DATE/T') DO SET mm=%%B
FOR /F "TOKENS=1,2 DELIMS=/ eol=/" %%A IN ('echo %CDATE%') DO SET dd=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('echo %CDATE%') DO SET yyyy=%%B
SET date1=%mm%%dd%%yyyy%_%time:~0,2%%time:~3,2%%time:~6,2%%
SET date2=%mm%%dd%%yyyy%_%time:~1,2%%time:~3,2%%time:~6,2%%
Set TDate=%time:~0,2%%time:~3,2%%time:~6,2%%
SET date3=%mm%%dd%%yyyy%

for /f "tokens=* delims= " %%a in ('dir/b *.txt') do (
set str=%%a
"D:\OBIEE11g\Oracle_BI1\bifoundation\server\bin\nqcmd.exe" -d coreapplication_OHXXX -u <username> -p <passwd> -s !str! -o !str!.out.!date3!

)

Hope This helps.

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

(Part of the text has been taken from Oracle Tech Note)

OBIEE 10g Configuration Tags

HI All

Below is the link for OBIEE10G tags. Thanks to OBIEE101 and LaGrange.

http://docs.google.com/fileview?id=0B8vnN_oQ0v04MTYwNzI1ODktYmZkNy00MzJlLTkwNGUtYmU3ZjgwMDc3OTQ4&hl=en

 

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”