Oracle Business Intelligence OBIEE 101: August 2009

Friday, August 21, 2009

OBIEE Bing Maps

I showed how to integrate Google Maps into OBIEE. Doing the same with Microsoft Bing maps is slightly different. There service is completely asynchronous, which meant a slightly different approach for the narrative view.

Let’s start with the basics. Get your addresses and a extra column for the array counter. This is just a RCOUNT – 1.

Switch to a narrative view. In the prefix part put:

<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<script src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.2"></script>
<script>
var map = null;
var count = 0;
var adr = new Array();
var cmt = new Array();
var Center = new VELatLong(52, 6);
var ZoomLevel = 7;
function GetMap()
{

in the narrative put:

adr[@1]="@3";cmt[0]="@2";

In the postfix part put:

map = new VEMap('myMap'); map.LoadMap();
map.SetCenterAndZoom(Center, ZoomLevel);
FindLocation(adr[0]);

}

function FindLocation(searchstr)
{
if (searchstr != '')
{
map.Find(null, searchstr, null, null, null, null, false, null, null, false, AddPin);
}
}

function AddPin(layer, resultsArray, places, hasMore, veErrorMessage)

{
if(places.length > 0)
{
var shape = new VEShape( VEShapeType.Pushpin,places[0].LatLong);
shape.SetTitle(places[0].Name);
//alert(comments[count]);
shape.SetDescription(cmt[count]);
map.AddShape(shape);
map.SetCenterAndZoom(Center, ZoomLevel);
}
count++;

if(count<adr.length)
FindLocation(adr[count]);
}
</script>

</head> <script src="http://code.jquery.com/jquery-latest.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready( function(){GetMap();return false;
}

);

</script>
<body> <div id='myMap' style="position:relative; width:800px; height:600px;"></div> </body>

Don’t forget to set Contains HTML:

Add it to the compound Layout:

I personally find this webservice slower then the one from Google, but maybe some better coding could fix that. If you have any ideas, please let me know.

Till Next Time

Wednesday, August 19, 2009

OBIEE Config Tags

Updated the Config Tags file and made (hopefully) better readable: OBIEE 10341 Config Tags

Till Next Time

Monday, August 17, 2009

OBIEE Prompt based on groupmembership

Somebody on OTN claimed that you can’t make prompts available based on the security setting of the user (See: http://forums.oracle.com/forums/thread.jspa?threadID=943712). Here is a workaround based on guided navigation:

First create a report which filters results based on the group (noprompt) membership:

SELECT TIMES.CALENDAR_YEAR saw_0 FROM BM_SALES WHERE LOCATE( 'noprompt',VALUEOF(NQ_SESSION.GROUP)) > 0 ORDER BY saw_0

Based on being a member of nogroup the prompt will be available:

User1:

User2:

Till Next Time

Saturday, August 15, 2009

OBIEE Google Maps linebreak in annotations

I was asked how to get from

to this:

The problem is that the presentation server converts the HTML br symbol to htmltext (& g t) so the google maps api doesn't regonise it.

This can be bypassed by using the javascript replace command:

comment.replace(/, /g," ,<> "));

(without the spaces)

the code to create the marker now would look like this:

var marker = createMarker(point,icon0,comment.replace(/, /g," <> "));

Till Next Time

OBIEE Prompts setting a default value to “force” a selection

Sometimes you want to “force” the user to make a selection to prevent heavy traffic between the DB and the BI-server. On trick is to default the prompts to non existing values. For instance for years use 9999:

For the a category:

By default the dashboard will return no rows. It a good custom to set the default “no rows” text:

Till Next Time

Thursday, August 6, 2009

OBIEE TopN versus the rest

Getting a TopN in OBIEE is pretty simple using the rank function:

But how can we compare this to the rest?

Let’s add an extra column which gives us the rank number if it’s between 1 and 10, else 99.

case when RANK("F1 Revenue"."1-01 Revenue (Sum All)") <= 10 then RANK("F1 Revenue"."1-01 Revenue (Sum All)") else 99 end

Remove the original filter and check the results:

Now add the Customer name to the extra Column:

case when RANK("F1 Revenue"."1-01 Revenue (Sum All)") <= 10 then Cast( RANK("F1 Revenue"."1-01 Revenue (Sum All)") as char)|| ' - '||"D1 Customer"."C1 Cust Name" else '99 - The Rest' end

Check the results:

Now the big trick, switch to pivot table and arrange the columns like this:

Till Next Time

Monday, August 3, 2009

OBIEE www obiforums com (nice idea)

To bad it isn't moderated, so it's mostly spam...

http://www.obiforums.com/

Till Next Time

Sunday, August 2, 2009

OBIEE UDML and Notepad

Sometimes you are using a trick for such a long time, you automatically suspect the whole world to know it. As you might know the internal communication protocol for the BI-server is UDML. A simple way of getting the UDML for a column is doing a CTRL-C of the column and pasting it into a text editor like notepad:

How can an RPD developer make use of this feature? Well the trick also works the other way around. If you need to make a lot of variations of column (FI: if you need to make TimeSeries variations for all you measures) simple do some clever Find and Replace (CTRL-H) in the notepad copy.

Select and copy it from notepad.

And paste it back in the RPD.

This can save you literally hours of tedious clicking in the RPD.

Till Next Time