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.

image

image

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:

image

Add it to the compound Layout:

image

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

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

image

image

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

User1:

image

User2:

image

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:

image

image

For the a category:

image

image

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

image

image `

Till Next Time

Thursday, August 6, 2009

OBIEE TopN versus the rest

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

image

image

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.

image

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:

image

Now add the Customer name to the extra Column:

image

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:

image

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

image

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:

image

image

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.

image

Select and copy it from notepad.

image

And paste it back in the RPD.

image

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

Till Next Time