Sunil S. Ranka's Weblog

Superior Data Analytics is the antidote to Business Failure

Archive for the ‘…’ Category

Oracle ACE Award

Posted by sranka on March 2, 2010

Dear All,

I have been confirmed with Prestigious Oracle ACE award. I want to take this moment to thank all friends, colleague, and family for all the support and feedback. More than me, you all deserve this award.

About Oracle ACE Program : Click Here .

My Profile in Oracle ACE Directory

Please continue your support.


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


Posted in ... | 5 Comments »

My NorCal-2010 OAUG Presentation

Posted by sranka on January 15, 2010

Hi All,

Finally done with my slide for NorCal-2010. I will be presenting on Jan 19th, 2010 at Santa Clara convention center, Santa Clara, CA between 2:30 – 3:30PM (PST) in Mission City Room 1. If you guys are around please attend.

Title: ‘How to enrich OBIEE UI using Custom JavaScripting’
The abstract for this presentation:

‘JavaScript is primarily used in the form of client-side JavaScript for the development of dynamic websites. OBIEE uses prepackaged JavaScript for rendering contents on the browser. During this presentation we will talk about how using simple javascript call in conjunction with OBIEE prepackaged JavaScript API, how we can get handle to the report XML structure. Using custom JavaScript parser how XML structure can be read and utilized. Some of the custom JavaScript usages are: What if analysis, Integration with Operational system, how to provide custom download feature on reports, custom messaging, replacing multiple go button with single go button etc.’

Hope to see some of you.

Hope this helps

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

Posted in ... | Leave a Comment »

OBIEE Answers (Ad-Hoc Reporting) Best Practices

Posted by sranka on January 7, 2010

Wishing you all a very Happy New Year 2010!!!! . I was waiting for some exciting topic to start with my 2010 blogging. Before holidays I had a deliverable of documenting best practices for Answers implementation (Ad-hoc reporting). Answers, a topic close to me. Since requirements keep changing as new ideas starts flowing in from the end users, OBIEE ANSWERS comes is key for success of any operational and tactical Business Intelligence project.

OBIEE ANSWERS Ad hoc reporting empowers power business users to create their own reports and perform in-depth analysis of real-time information right in their Web browsers. Sometimes, business users need reporting capabilities for one-time questions or want to build specialty reports without having to request that the IT department create a new report for them. Ad hoc reporting allows end users to get answers on demand and customize their queries so that their reports contain only the information needed.

Best Practices

  • Getting critical information from the right people at the right time, detail Business Requirements analysis is critical to the success of Ad hoc reporting.
  • Ad Hoc reporting user will have access to most granular level of data, hence data and Object level security is high priority item.
  • A single security model should be used for both canned and ad hoc reporting
  • Empower and educate key end users on content and OBIEE to develop their own reports in a managed Ad hoc report environment.
  • Don’t release all the detail measures in the first phase, have phased approach for releases.
  • A separate OBIEE sas and web servers are preferred for better performance and scalability.
  • Join Fact and Dimension using numeric key for better performance.
  • Don’t overload the user with too many Fact and Dimension, keep the Dimensional modeling simple with few facts and Dimension for ease of use.
  • Training for comprehensive understanding of the subject area that will be queried should be given to End Users.
  • Educate users on restricting number of rows displayed on browser to less than 25.For detail data use download to data option.
  • Measures and Dimension should be logically grouped for better user experience.
  • Performance and detail unitesting of each and every measure with all the dimensions.
  • Create and save variables and filters for returning the most relevant results.
  • Basic and advanced statistical calculations needs to be done at the repository level
  • Answers access should be restricted only to power users.
  • Keep track of Answers subject area using Usage Tracking for improvements and enhancements.
  • Keep an eye on the db so that users don’t use most of the resources bu using some naked queries.
  • In past we have seen that when users are trying to download huge data SAS server crashes, so please make sure you have enough storage space defined for — DATA_STORAGE_PATHS,SORT_MEMORY_SIZE,SORT_BUFFER_INCREMENT_SIZE,VIRTUAL_TABLE_PAGE_SIZE — variable in NQSConfig.INI confige

Hope This Helps


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

Posted in ..., OBIEE | Tagged: , , , , , | 1 Comment »

How to dynamically calculate TopN

Posted by sranka on November 25, 2009

Hi All

Finally I am sitting down to post an interesting thing I came across during my recent release. I would like to than Gopal Kethineni for this.

The requirement was very interesting and important :

Business wanted to calculate Top 10 opportunities/$ amounts by different slices/dices.

In past we had accomplished the above requirement by pre calculating the values. But the permutations and combinations were many. E.g Top 10 Opportunities By Geographical Hierarchy or By Product Hierarchy or by Time Hierarchy. If we had to pre calculate all of the above permutations and combinations than we had to write too many ETL and eventual ETL load would have delayed.

To make solution more scalable and dynamic we used POWER of OBIEE by twisting giving TOPN function. Following is the example .

For Every Level 4 Of Geographical Hierarchy, show all the Top 10 Customers based on the opportunity amount. Along with this we need to show all the Level 5 of the Geographical Hierarchy.

In the above example the challenge was to select Level 4,Level 5 , Customer Name, and Opportunity amount as part of the criteria but to calculate Top 10 customer only Level 4 Of Geographical Hierarchy needed to be considered as Level 5 was needed to be considered as only an attribute. To solve the above complex problem.Following expression was used as part of formula column.

TOPN(SUM("Fact - Customer"."Opportunity Amount" by "Dim - Geo Hrchy"."Level 4", "Dim - Customer"."Customer Name"),10 by "Dim - Geo Hrchy"."Level 4") 

We have saved more than several hours of ETL development/QA and system resources time. If I can count correctly, I would say we must have saved more than $100K , by implementing this solution.

Hope This helps,
Sunil S Ranka

Posted in ..., OBIEE | Tagged: , , , , , , , , | 2 Comments »

Oracle Open World 2009

Posted by sranka on November 7, 2009

Hi All,

Its been over few weeks since OOW is over, I know its kind of late, but better late than never. After attending OOW , was caught up with regular deliverable and time was running by. It 1:17 AM on Saturday morning and thought will update fellow readers on OOW-09. I attended OOW as a blogger and it was different experience. In past I had attended as an employee, but going as independent blogger was different ball game. Met with few famous name from blogging world. Business Intelligence , specially OBIEE was all over place in the conference. Even though the conference attendees turn out was less, but all the BI sessions were packed. Met with product managers for OBIEE, played with 11g OBIEE and in short I would say, 11g is a must. I will blog on what all things are part of 11g. Larry’s keynotes and shots at IBM were highlights. Surprise appearance and speech of  Arnold Schwarzenegger left every one astonished.

Last but not least the AEROSMIT show was fabulous, Steven Tyler, and Joe Perry duo were oustanding. Even though I am not a fan of ROCK, but it was worth stading in the front row with thousands of watt of music. Over all it was well worth.

Larry With Exadata

Larry With Exadata

Arnold Schwarzenegger

Arnold Schwarzenegger


Rare View -- All Oracle Think Tankers (SVPs)

Rare View -- All Oracle Think Tankers (SVPs)

Steve Tyler - Aerosmith

Steve Tyler - Aerosmith

Oracle - Aerosmith

Oracle - Aerosmith




Posted in ... | Tagged: , , , , , , | Leave a Comment »

On Teradata Queries are been fired twice. Once for Parsing and once for execution.

Posted by sranka on September 9, 2009

Hi All

Recently I been working with Enterprise Architect group to tune the performance of application on Tera Data platform. While working on varies issues, came across very interesting fact, a query which was taking only 15 sec on database , but was taking more that 20 sec to return the data onto the browser. While digging into the problem, Enterprise Architect found that query is been executing twice, once for parsing and next time the actual execution. This was very evident for all the request.We spoke with Oracle support and suggested following options ::

In windows:
1. Please check the setting on Teradata ODBC under Options, then check box for ‘Disable Parsing’.

This option is provided to the user to enable/disable parsing of SQL statements by the ODBC driver. If the user is using the Teradata Database specific SQL, then the SQL statements need not be parsed by the driver. If this option is checked, the driver does not parse the SQL statements.
Default: unchecked

2. Please ‘check’ on this option on Teradata ODBC driver and retest.


Options Configurable in the .odbc.ini File,
NoScan=[ Yes | No ]
Default = No

After trying above option, we could not get solution to the problem, after talking with Oracle Product management they suggested to set the system variable to


After setting the above system variable, queries were not executing twice.

What it does :

After setting the variable, OBIEE server engine doesn’t parses the query and send the exact the same query to database, hence only one query is been sent to database.

Where you can not use this solution :

One word of caution, if you are using any bind variables than you can not use this option.

Hope this helps

Sunil S Ranka

Posted in ..., OBIEE | Tagged: , , , , , | 1 Comment »

How to increase SAW (Presentation Server) logging

Posted by sranka on August 6, 2009

To increase the SAW/OBIPS logging level you need to make some changes to logconfig.xml.

  • Open logconfig.xml file located in D:\OracleBIData\web\config folder
  • Add the following lines in the <Filters> node of logconfig.xml:
 <FilterRecord writerClassGroup="Cout" path = "saw" information="100" warning="100" error="100" security="100"/>
 <FilterRecord writerClassGroup="File" path = "saw" information="100" warning="100" error="100" security="100"/>
 <FilterRecord writerClassGroup="File" path = "saw.httpserver.request" information="100" warning="100" error="100" security="100"/>
 <FilterRecord writerClassGroup="File" path = "saw.httpserver.response" information="100" warning="100" error="100" security="100"/>
 <FilterRecord writerClassGroup="File" path = "saw.mktgsqlsubsystem.joblog" information="100" warning="100" error="100" security="100"/>
  • You will also need to increase the number of log files being produced. This is also controlled in logconfig.xml.
  • In the <Writers> node, modify Writer implementation:
<Writer implementation="FileLogWriter" name="Global File Logger" writerClassId="1"
dir="{%SADATADIR%}/web/log" filePrefix="sawlog" maxFileSizeKb="10000" filesN="100" />

Set the filesN parameter to 100.

  • Logging at this level produces a lot of data. You need to make sure you have an adequate amount of free space on the hard drive to accommodate the additional logs.
  • For changes in logconfig.xml to take affect, presentation services (OBIPS) needs to be restarted.

Hope This Helps

Sunil S Ranka

The above details are been taken from Oracle Metalink3 Website (Document 740257.1). Document 740257.1 can be found at:

Posted in ..., OBIEE | Tagged: , , , , , | Leave a Comment »

People I Work With

Posted by sranka on July 15, 2009

Hi All,

Always I wanted to mention about the people I work with. Due to my team’s continue support, I could post many thing on blog. Following are people I have been working with for over 2-3 years. Not in specif order.

  • Alan Rodrigues, the six pack guy,  an avid body building guy and great OBIEE guy
  • Kaushik Regulapati, one of the cool guy I ever worked,  one of the finest OBIEE resource.
  • Srikanth Rukmanagari, can’t go wrong with this guy, a rock solid trust able OBIEE resource.
  • Aditya Masabathula, guy who made What-If Analysis work using Java Script.
  • Sachin Churi, a guy who can even find a bug in, an awesome database and IT Analyst
  • Jagadesh Rachakonda, I can bet he thinks faster than IBM-Super computer.

Thanks you all for making so many thing happen.

Sunil S Ranka

Posted in ... | 1 Comment »

Way To Solve Multiselect Prompt Bug

Posted by sranka on April 6, 2009

Hi All,

Thanks for all the positive response . Its been while since I posted any thing. Been busy with preparing for my collaboration-09 presenation. Yes!!! I am presenting at OAUG’s collaboration-09 conference schedule in the 1st week of May in Florida. For people who are interested, topic would be — Need for web 2.0 and OBIEE integration –. Coming back to this post.

Faced some weird and unusual problem with Multiselect.  The problem was so severe that if you don’t have proper security implemented on RPD side, than any one can see any data. hmmmmmm.. sounds scary!! yes it is scary so here is what happens.  In the following image you get to see all the name of the countries from the selected continent.

Results when clicked on ... from the base page

Results when clicked on ... from the base page

The actual fun begins when you hit the Go button,  and irrespective of which continent has been selected all the countries in the world will get displayed. see the image attached

Results shown when clicked on the Go button on the result screen

Results shown when clicked on the Go button on the result screen

What is the issue :

After investigating found out that whenever GO button is clicked, the constraint value has not been passed and all the values from the dimensions, are been retrieved.


To solve the issue we need to modify two javascript files residing in the folder res/b_mozilla/prompts/ :

  • globalfilterprompt.js changes to — GFPDoMultiSelect — function
  • gfpmultiselect.js changes to — — function

Changes :

Following is the modified — — function, all the text in the bold are the changes :: = function()
this.sWhere = “”;

var obj1 = window.parent.document.getElementById(“customMSPromptdiv”);

/* sranka — added for MultiSelect Data Persistance*/
if(obj1.value == ”){
this.sWhere = ” ( 1 = 1 ) ” ;
this.sWhere =  obj1.value;
this.sWhere = ” ( 1 = 1 ) ” ;

if( == ”)
if (this.searchForm.Constraint.value != “”)
var sValue = null;
switch (this.searchForm.Match.value)
case “beginsWith”:
sValue = GFPMultiSelect.SQLEscape(this.searchForm.Constraint.value) + “%”;
case “endsWith”:
sValue = “%” + GFPMultiSelect.SQLEscape(this.searchForm.Constraint.value);
case “contains”:
sValue = “%” + GFPMultiSelect.SQLEscape(this.searchForm.Constraint.value) + “%”;
case “like”:
sValue = this.searchForm.Constraint.value;

if (sValue != null)
this.sWhere = this.sWhere + ” AND ” + this.vColumns[0] + ” LIKE ‘” + sValue + “‘”;

else if( == ”)
var tA = null;
var tB = null;

case ‘date’:
tA = tDTP.parse(this.searchForm.BetweenA.value, 2 | 8 | 16 | 32);
tB = tDTP.parse(this.searchForm.BetweenB.value, 2 | 8 | 16 | 32);
case ‘time’:
tA = tDTP.parse(this.searchForm.BetweenA.value, 4);
tB = tDTP.parse(this.searchForm.BetweenB.value, 4);
case ‘timeStamp’:
// convert
tA = tDTP.parse(this.searchForm.BetweenA.value, 1 | 2 | 4 | 8 | 16 | 32);
tB = tDTP.parse(this.searchForm.BetweenB.value, 1 | 2 | 4 | 16 | 32);
//convert to data timezone
if (this.nDisplayToDataTZOffset)
if (tA != null)
if (tB != null)

if(this.searchForm.BetweenA.value.length > 0 && !tA)
alert(kmsgWBInvalidInput + “\”” + this.searchForm.BetweenA.value + “\””);
return false;

if(this.searchForm.BetweenB.value.length > 0 && !tB)
alert(kmsgWBInvalidInput + “\”” + this.searchForm.BetweenB.value + “\””);
return false;

if(tA != null)
var sA = DateTimeParser.buildDateTimeClause(‘>’, this.vColumns[0], GFPMultiSelect.primaryType, tA);

if(sA != null)
this.sWhere = sA;

if(tB != null)
var sA = DateTimeParser.buildDateTimeClause(‘<‘, this.vColumns[0], GFPMultiSelect.primaryType, tB);

if(sA != null)
this.sWhere = this.sWhere.length == 0 ? sA : (this.sWhere + ” AND ” + this.sWhere + ‘ AND ‘ + sA);


else if( == ”)
var tA = this.searchForm.BetweenNA.value;
var tB = this.searchForm.BetweenNB.value;

if(tA.length > 0)
tA = parseFloat(tA, 10);
this.sWhere = this.sWhere + ” AND ” + this.vColumns[0] + “>=” + tA;

if(tB.length > 0)
tB = parseFloat(tB, 10);
var clause = this.vColumns[0] + “<=” + tB;
this.sWhere = this.sWhere + ” AND ” + this.sWhere.length == 0 ? clause : this.sWhere + ” AND ” + clause;

this.choicesDiv.innerHTML = “”;
this.totalSpan.innerHTML = “0”;
this.totalSpan.setAttribute(“total”, 0); = “none”;

VTDisplayValues(this.choicesDiv, this.vColumns, this.subjectArea, ‘kmsgGFPMultiSelectSearchValueTableRow’, this.sWhere, this.timeZone, this.timeZoneOffset,
‘scroll’, this.sId, null, null, null, null, null, “idGFPMultiSelect” + this.sId);

Following is the modified — GFPDoMultiSelect– function, all the text in the bold are the changes ::

function GFPDoMultiSelect(tEvent, sTextAreaID, sColumn, sSubjectArea, sWhere, sID, sCategory, sPrimaryType, sDisplay, sSQL, sDataTimeZoneOffset, sDisplayTimeZone)

/* sranka — Added this code as patch for persisting values for MultiSelect */
var parentElem = window.parent.document.getElementById(sTextAreaID);
//alert(‘GFPDoMultiSelect GlobalFilterPrompt.js :: ‘ +sTextAreaID);
var newdiv = window.parent.document.createElement(‘input’);
var divIdName = ‘customMSPromptdiv’;

var browserName=navigator.appName;

if(browserName == ‘Netscape’){

var tMultiSelect = new GFPMultiSelect();
var tDialog = new XUIDialog(“idGFPMultiSelect”+sID, tMultiSelect, null);, -1, -1, null, null, null, null, true);
tMultiSelect.initialize(sTextAreaID, sColumn, sSubjectArea, sWhere, sID, sCategory, sPrimaryType, sDisplay, sSQL, sDataTimeZoneOffset, sDisplayTimeZone);

return false;

I hope this will help solving some of the issue. I know that the code I have provided is not well formatted, please ping me or leave me a message for any questions or difficulties.

Hope this helps


Sunil S Ranka

Posted in ..., OBIEE | Tagged: , , , , , | 7 Comments »

%d bloggers like this: