How do you reference a presentation variable in your filter:
Press the filter sign of the column
Press Add > Variable > Presentation
Enter the varaible name and default value
Till Next Time
Tuesday, March 17, 2009
OBIEE Google Maps Multiple Addresses
Based on a previous posting (http://obiee101.blogspot.com/2008/10/obiee-using-google-maps-q-style.html) I was asked if it's possible to add multiple addresses to a Google map. Actually this even simpler then having to create a separate map for each address.
Let's get our base table:
Now create a narrative view:
In the Prefix part put:
// Set the Google maps api key:
<script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAAtgh-ZliBar5ci3sjZR_oGRSEtwgT_n0GADCjO95K9FWMY2XE2RQZwN8F1TggjSu117aG70pYMI0GfQ type="text/javascript"></script><script type="text/javascript">
// Declarations
var map = null;
var geocoder = null;
var marker = null;function initialize() {
if (GBrowserIsCompatible()) {
// Create the map
map = new GMap2(document.getElementById("map_canvas"));
// Set the center without markers; 13 is the zoom level
map.setCenter(new GLatLng(37.4419, -122.1419), 13);
// Add the compass and zoom control
map.addControl(new GLargeMapControl());
// Add the Map type control
map.addControl(new GMapTypeControl());
// Get a new geocoder (needed to convert adresses to coordinates
geocoder = new GClientGeocoder();
// Get the ICON for the marker
icon0 = new GIcon();
icon0.image = "http://www.google.com/mapfiles/marker.png
icon0.shadow = "http://www.google.com/mapfiles/shadow50.png
icon0.iconSize = new GSize(20, 34);
icon0.shadowSize = new GSize(37, 34);
icon0.iconAnchor = new GPoint(9, 34);
icon0.infoWindowAnchor = new GPoint(9, 2);
icon0.infoShadowAnchor = new GPoint(18, 25);
// Get the Adresses
GetMapAdress ()
}
}function showAddress(address,comment) {
// Coverts adresses to coordinates and set the marker on the chart
if (geocoder)
{
geocoder.getLatLng
(
address,
function(point)
{
if (!point)
{
alert(address + " not found");
}
else
{
map.setCenter(point, 13);
var marker = createMarker(point,icon0,comment);
map.addOverlay(marker);
// Opens the last marker
marker.openInfoWindowHtml(comment);
}
}
);
}
}
function createMarker(point, icon, popuphtml)
// Creates the marker
{
var popuphtml = "<div id=\"popup\">" + popuphtml + "<\/div>";
var marker = new GMarker(point, icon);
GEvent.addListener(marker, "click", function() {marker.openInfoWindowHtml(popuphtml);});
return marker;
}function GetMapAdress ()
{
In the narrative part:
// From here it's build dynamicly in OBIEE
showAddress('@2', '@3');
In the Postfix part:
}
</script>
<body onload="initialize();return false" onunload="GUnload()">
<div id="map_canvas" style="width: 600px; height: 400px"></div>
</body>
</html>
No check if it works in the compound layout:
Till Next Time
Edit if you are using IE you migth want to look also here:
http://obiee101.blogspot.com/2009/07/obiee-google-maps-alternative-ending.html
Tuesday, March 10, 2009
OBIEE hidden column writeback
Sometimes you want to use a "hidden" column for a writeback procedure. Problem is if you use the conventional Hide properties of a column it's also not visible for the write back.
Workaround: use a CSS hide:
Enable the "Use Custom CSS Style " enter display:none. Both for the field and the header.
(Be aware the original value is still available in the source of the page!)
Till Next Time
OBIEE date prompt to day month year
A Obiee prompt from a calendar to a presentationvariable is cast as date. One way of extracting the day, month or year can be done based on the locate function:
day: substring( '@{Date1}{01/01/1900}' from (locate('/','@{Date1}{01/01/1900}',1) +1)for (locate('/','@{Date1}{01/01/1900}' ,4) - locate('/','@{Date1}{01/01/1900}',1)-1))
Monday, March 9, 2009
Forum Autorefresh
I'm a hugh fan of the OTN fora. Problem is that it doesn't have an auto-refresh. I've made a small script to load the page in a iFrame and reload it every 5 minutes:
<!-- OTN OBIEE With autorefresh -->
<!-- Check if it's a 5 minute part of an hour is reached --><script type="text/javascript">
var timer = setInterval("checkTime()", 1000 * 60 );function checkTime()
{
if((new Date().getMinutes() % 5) == 0)
self.location.reload(true);
}
</script><!-- Load the page as Iframe -->
<IFRAME SRC="http://forums.oracle.com/forums/forum.jspa?forumID=378 WIDTH=1200 HEIGHT=900 frameborder=0 SCROLLING="no">
If you can see this, your browser doesn't understand IFRAME. However, we'll still <A HREF="http://forums.oracle.com/forums/forum.jspa?forumID=378>link</A> you to the file.
</IFRAME>
Till Next Time
Friday, March 6, 2009
OBIEE identifying the hierarchy level
On the OTN forum I was asked how you can identify the hierarchy level which the user is currently on (Did he select Year, Quarter,Month etc?).
One way of doing this is "using" the aggregate awareness availability of a column. First you create a "dummy" aggregate view for each hierarchy:
- year_level: select distinct d_year, 4 AS DIM_LEVEL from d_date
- quarter_level: select distinct d_quarter, 3 AS DIM_LEVEL from d_date
- etc.
These tables you physically join to your dimension:
Update 2010/06/28:
Add Dummy joins to your other dimensions:
Set the expression to 1=1
In your fact table you add an extra column named dim_date_level:
Next add the sources to your fact table:
Add an inner join on your core fact table to your original dimension:
On each level source map to the column:
and set the aggregation content level:
set the aggregation level for the level column to MIN:
Test your Report:
Be sure to check all your dimension levels!
Till Next Time
BI Consultant 2008
Gisterenavond ben ik door de bezoekers van www.bidutch.nl uitgeroepen tot BI consultant 2008. Ik wil langs deze weg iedereen die op mij gestemd heeft bedanken!
"Resultaten behaald in het verleden bieden geen garanties voor de toekomst!" , maar maken de uitdaging wel groter!
Yesterday I was awarded "BI consultant 2008" by the visitors of www.bidutch.nl. I would like to thank everybody who voted for me!
"Results from the past are no warranty for the future", they only make the new challenge bigger.
Thursday, March 5, 2009
OBIEE Custom Authenticators
Within OBIEE you can build your own custom authenticators. In the directory {OracleBI}\server\SDK\CustomAuthenticatorSamples\ you can find a sample. But if you are just like me not up to speed C++ it takes some effort to get it working.
Step 1: get a C++ compiler. (I used the Microsoft Express edition)
Step 2: Locate the SampleDynFileAuth.vcproj file open in a text editor and remove all references to InheritedPropertySheets.
Step 3: Open the solution, check for errors.
Step 4: Compile the DLL
Step 5: Create a passord .txt file with the following structure:
name passwordhash "Groups" "atributes" "atributes"jim mij "Admin" "full name=Jim Smith" "title=Manager"
jim2 mij "Admin|qa" "full name=Jim Smith" "title=Manager"
(passwordhash is the password in reverse)
Step 6: Create a Custom Authenticator in your offline RPD:
Step 7: Add the User and Group variable to a init block
Step 8: reboot the bi-server and start testing!
Till Next Time
Tuesday, March 3, 2009
OBIEE NQSSetSessionValue
When playing around with session variables in the repository in the help context you wil find a reverence to NQSSetSessionValue:
But if your looking for how to use it........ (At least I couldn't find anything on the net or in the docs.). Anyhow: after logging an SR I was pointed in a direction to find a solution. The way to use this function is to issue an direct SQL request from a web page by ussing the ?IssueRawSQL?SQL (FI: http://vmobiee:9704/analytics/saw.dll?IssueRawSQL?SQL )
(you have be granted the right permission for this!)
Next you have to format the request like:
call NQSSetSessionValue('DataType VariableName=Value;')
FI: call NQSSetSessionValue('Float MYSESSIONVAR=11;')call NQSSetSessionValue('String MYSESSIONVAR=Test;')
The types allowed are: Integer, String, Float, Date, Time and DateTime.
Till Next Time
OBIEE Date, TimeStamp and ORACLE Indexes
Mark Rittman triggered meto investigate this: If you are Filtering against a date column in OBIEE you will see in the log that a timestamp conversion is added to your query:
select distinct T2826.DAYTIME as c1
from
DAYTIME2 T2826
where ( T2826.DAYTIME = TIMESTAMP '1968-03-14 00:00:00' )
order by c1
This timestamp conversion implies that a index on the DATE column is skipped, resulting in a full tablescan. How can we force the usage of an index? Here is an alternative:
1: Create a FUNCTIONAL INDEX on your date column Fi:
create index dt3 on daytime2(cast ("DAYTIME" as timestamp));
2: Create a view on your table casting the date column as TIMESTAMP:
create view v_daytime2
as
(select cast(daytime as timestamp) as daytime from daytime2)
Map this view to your RPD and you will, check the explain plan, see that Oracle uses your index.
If you have any better / other solutions please let me know!
Till Next Time
Monday, March 2, 2009
OBIEE Between Dates Prompt
One the questions you see regularly on the forums is how to create a 'between dates' dashboard prompt if the date column is not a part of a date dimension. If more important with the use of the calendar control.
Step 1 create a new prompt based on a date dimension:`
select 'Calendar' from the control dropdown box, if you do it later on it won't be available (undocumented feature ;-) ).
Step 2: Alter the column function:
CASE WHEN 1=0 THEN CALENDAR.D_DATE ELSE TIMESTAMP '1900-01-01 00:00:00' END
The CASE WHEN part guarantees validation of the column.
Step 3: Set the presentation variable and label
Step 4: repeat step1
Step 5: Alter the column function:
CASE WHEN 1=0 THEN CALENDAR.D_DATE ELSE TIMESTAMP '2999-12-31 00:00:00' END
This has to be different from the one used in step 2 since OBIEE uses the default outcome of the formula as internal name for the column.
Step 6: Set the presentation variable and label
Step 7: reference the variable in your report:
Step 8 : Create the dashboard:
Till Next Time
Subscribe to:
Posts (Atom)