Feeds:
Posts
Comments

Archive for the ‘obiee design’ Category

OBIEE allows analysis based on physical query.

Example:

physical sql

I have tried to use results from this analysis in a filter inside another analysis.

filter

filter 1

error

With its good intentions Oracle OBIEE tries to build a single query with analysis in filter as sub-query. Since analysis used in sub-query is a physical query it unable to process syntax properly.

It is possible that I’m not doing this correctly.  Has anyone able to use physical sql in sub-query? If you have could you please post details?

I couldn’t find any useful information online.

I’m going assume worse and it doesn’t work that way I’m trying to use. If that’s the case there is a good case to be made for adding this feature down the road. It would be nice to have nested logical and physical query capability to solve tough requirements.

Read Full Post »

OBIEE has a fairly simple EDIT interface for allowing ad-hoc users to create and modify reports (analysis). Sometimes you will come across business use cases where using edit is simply too much for users. Business users deserve a simple interface for dynamically filtering a data set that is presented in dashboard. It has to be simple and easy to use for users with basic level of technical capabilities.

In this article we will discuss few options for building “Dynamic Where Conditions” to offer simple filtering capability at runtime.

Below is an example of dashboard that allows users to select Column, Operator and Value to filter results.

concept

As shown in below screenshot, Product & Region prompts are built using out-of-box functionality.

prompt_for_operator

Main Concept behind this example is the ability of OBIEE to support presentation variables in where conditions. We don’t need to use jQuery to achieve this functionality. However I’m using jQuery to make this little nicer and also have the capability to prevent invalid input values and build only valid WHERE conditions.

Details:

prompt details

Where clauses in Analysis:

where_conditions

jQuery Scripts:

Add below javaScript to Dashboard.

<head>
<style type=”text/css”>

.CVFormatCell{text-align:left;}
.PromptStepTitleCell{display:none;}
.promptButtonsCell{display:none;}
</style>
<script src=”http://code.jquery.com/jquery-latest.js”> </script>
<script type=”text/javascript”>
function setForm() {

var i=0;
$(‘form’).each(function() {
i++;

if (i == 2) {
var myForm = $(this);

$(myForm).find(“label”).each(function(){
if ( $(this).attr(“title”) == ” “) {
$(this).parent().parent().hide();
}
});

var j = 0;
$(“.promptEditBoxField”).each(function(){

j++;
if ( ( j >= 4) && ( j <= 6)) {
$(this).hide();
}
});
}

});

}
</script>

<script type=”text/javascript”>

var oparrtxt = [ “Equal To”, “Not Equal To”, “Less Than”, “Greater Than” ];
var oparrsym = [ ” = “, ” != “, ” < ” , ” > ” ];
var coldisplay = [‘Revenue’, ‘Fixed Cost’, ‘Variable Cost’, ‘Unit Price’, ‘Discount Amt’];
var colformula = [‘”Base Facts”.”1- Revenue”‘, ‘”Base Facts”.”11- Fixed Costs”‘, ‘”Base Facts”.”10- Variable Costs”‘, ‘”Simple Calculations”.”20  Actual Unit Price”‘, ‘”Base Facts”.”3- Discount Amount”‘]

function clickApply() {
var i=0;
$(‘form’).each(function() {
i++;

if (i == 2) {
var $forminputs = $(“.promptTextField, .promptEditBoxField”);
var where1 = “1=1”;
var where2 = “1=1”;
var where3 = “1=1″;

if ( ($forminputs.eq(4).val() == ‘–Select Value–‘) || ($forminputs.eq(6).val() == ”)) {
//alert(‘no value selected’)
}
else {
where1 = colformula[jQuery.inArray($forminputs.eq(4).val(), coldisplay)] + ‘ ‘ + oparrsym[jQuery.inArray($forminputs.eq(5).val(), oparrtxt)] + $forminputs.eq(6).val() ;
}

if ( ($forminputs.eq(7).val() == ‘–Select Value–‘) || ($forminputs.eq(9).val() == ”)) {
//alert(‘no value selected’)
}
else {
where2 = colformula[jQuery.inArray($forminputs.eq(7).val(), coldisplay)] + ‘ ‘ + oparrsym[jQuery.inArray($forminputs.eq(8).val(), oparrtxt)] + $forminputs.eq(9).val() ;
}

if ( ($forminputs.eq(10).val() == ‘–Select Value–‘) || ($forminputs.eq(12).val() == ”)) {
//alert(‘no value selected’)
}
else {
where2 = colformula[jQuery.inArray($forminputs.eq(10).val(), coldisplay)] + ‘ ‘ + oparrsym[jQuery.inArray($forminputs.eq(11).val(), oparrtxt)] + $forminputs.eq(12).val() ;
}

$forminputs.eq(13).val( where1);
$forminputs.eq(14).val( where2);
$forminputs.eq(15).val( where3);

$forminputs.eq(13).hide();
$forminputs.eq(14).hide();
$forminputs.eq(15).hide();

}
});

$(“#gobtn”).click();

}
</script>

<script type=”text/javascript”>
function clear11FilterDisplay() {
$(“.FilterCell”).each(function(){
if( ($(this).html() == ‘1 = 1’) || ($(this).html() == ‘1=1’)){
$(this).parent().html(‘ ‘);
}
});

}
</script>
<script type=”text/javascript”>

$(document).ready(function(){

setForm();
});
</script>
</head>

Custom Apply button:

Add below to Dashboard to create “Apply” button.

<input type=”button” id=”Apply” name=”Apply” value=”Apply” onClick=”clickApply()” class=”button” />

dashboard_setup

jQuery Scripts in Analysis:

Add below script to analysis to remove prompt labels and 1=1 conditions from displaying in filter view.

<script type=”text/javascript”>

setForm();
clear11FilterDisplay();
</script>

analysis

Result:

result

Conclusion:

This is just a simple example of what can be done using dynamic where conditions. I can see several practical applications for this technique to improve usability of BI applications.

Feel free to drop a comment on how you can leverage this concepts to improve your business.

Read Full Post »

In OBIEE, we can use couple of methods for giving users the option to choose a display of information from list of choices presented to them.

Option 1:  View Selector

Option 2:  Guided Navigation

There are several blogs on these topics. Here are couple of links I was able to quickly find.   View Selector             Guided Navigation

View Selector is an anlysis/report level feature. So it can be used for individual reports as well as for dashboards. You can insert a report with view selector view inside dashboard pages.

On the other hand Guided Navigation is a Dashboard level feature.

Below are few important distinctions between these features.

  • Because of its nature view selector can present different views of data that originates from a single query (single or union query).
  • Guided navigation can be used switch between different reports or different views from the same report. We have to submit dashboard pages to different pages. That means we making trips back & forth between presentation server and BI Server.

Above are the major fundamental differences between these two approaches.

Sometimes you will come across a dashboard requirement where you can use either of these approaches. Technically this situation arises only when all of the required views can come from same query.

So, if you have to make decision between view selector & guided navigation which one would you choose?

I’m using below rational for making my decision.

  • I would use view selector when all of these views are coming from the same granularity of data. This way there is no need to submit the page again to see the data.
  • I would use guided navigation when granularity changes between views and there is a performance impact because of it.

What would you do differently? Please share your thoughts.

Read Full Post »