Data Architecture Guidelines

Any enterprise or business thrives on data. They create, share, and manage data to run their business.

The data architecture describes how the data is created, processed, stored, distributed and managed by a business and or its applications. In other words, it should define an end-to-end vision as how the data flows from source to target to users. A documented understanding of the enterprise data architecture is an essential pre-requisite to many common IS and business improvement initiatives.

The data architecture has many uses. It helps to get a handle on data as it is really used by the business, and it is a key artifact if one wants to develop and implement governance supporting a data strategy. It also helps to guide cross-system developments such as Enterprise Application Integration (EAI), common reporting, and data warehousing initiatives.

The data architecture is never complete, and hence care should be taken when developing the framework such that it is scalable and flexible.

Following are the key stages or phases of a good data architecture:

• Organize
• Move
• Store
• Access
• Present
• Organize

Ensure you identify the source for data collection. It is important to actually identify the source systems, breakdown the data into atomic level so that it can be used or integrated to make it meaningful. Consider reworking or reformatting the original data to the future state as required by business. This effort is time consuming depending upon the original data and the new requirement.

Develop a data model – conceptual, logical and physical that identifies existing and new entities, attributes, and relationships. Define metadata and data dictionary.

Move

Identify the method and technology to move data from source to the new target. This involves choosing a tool that will carry out Extraction, Transformation and Load. Develop business rules and a frame work to integrate data. The frame work needs to consider error handling as well. Develop process and methodology to ensure validity of data that is moved to the target.

Store

Identify a database platform that meets business and technology criteria. Create the database based on the physical model. Ensure the database is sized to accommodate the future growth. Pay special attention to performance – data load, retrieve and reporting. Develop a data retention and archival strategy. Develop process to capture data changes and audit the changes.
Develop policies for data management in each business area:

• What data is stored.
• Who is responsible for its collection and quality.
• Who controls it, and who administers it.
• How long it must be stored, and how it will be disposed of or archived afterwards.
• Who may have access to it, and how it should be disclosed to others outside the normal user groups.

Access

Identify the platform as to how the data is accessed – web (intranet, extranet), desktop etc. Develop a security model that identifies the users who would be accessing the data and their rights. Take into consideration of firewalls and other security softwares when data is accessed from external.
Develop a semantic layer that separates business users accessing data directly from the database and incorporate some of the reporting metrics and rules.

Present

Select a suitable presentation tool that satisfies the business needs and that meets the technology challenges. Define presentation layer metrics and layout. Develop a strategy to run the reports. When possible, schedule them to minimize the impact of network traffic and load on the database.

Integrating OBIEE and Google Maps and Drilling to Dashboards

Since we have started to integrate Google maps into an OBIEE application the users requested the ability to look at a Map of the US and see concentrations of points on a map. They wanted to see at a national level where incidents they were interested in were located. They also wanted the ability to zoom in and see exactly where each incident occurred. The solution we chose to meet the requirement was described in the last blog Integrating OBIEE and Google Maps with the MarkerClusterer API. This is another example of the same clustering map on our system but with more navigation enabled.

Once a user zooms into a level on the map that has single location icons they have the ability to click on the icon and see some basic information about the event represented.

The user can then decide if more information is needed. By clicking on the text in the popup the user is directed to a new dashboard showing the origin, destination, and location of an incident. The following dashboard has another report that plots three addresses on a Google map, and allows for further navigation to company information or a BI publisher report on the incident details.

The above shows you what the user can do with map interaction now let’s look at the code as to how it is done. We will start with some basics I mentioned in the last blog.

1. First we geocoded our addresses and stored latitudes and longitudes for each data point we wanted to map. If you do not geocode the addresses you really need to work with less points (less than 1000) and the results will take much longer. The code is based on having lat and longs.
2. Then we created a query that has the company facility name and internal company number (used for navigation), lat and long, and a piece of data called rank for displaying on the single points in the map. The RSUM(1) is used for displaying if more that 3000 rows could be displayed in the map.

Because this drill down is based on the clustering API the code reflects that API. Note it is not necessary to use the clustering API as the basic Google Maps API allows on click events.

The heavy lifting is done in a narrative view using JavaScript, the Google maps API, and the MarkerCluster API

1. Get a Google Maps API Key from: http://code.google.com/apis/maps/signup.html.  Google maps API uses a key generated based on the IP and name from the calling server, so be sure you are on your web server when obtaining this. Get a key based on your own OBIEE server address.
2. Create a narrative view and make sure you mark the contains HTML Markup checkbox

I will add all the code after we examine the parts of the code dedicated to the pop up box and link to a dashboard.

function initialize1(lat, long, cnt, ncident_num, incident_dt, incident_sev)

This initializes the values from the query using this from the narrative and basing the values from the order of column in the criteria tab.

initialize1('@2','@3','@4','@1','@5','@7')

point1 = new GLatLng(lat, long);
marker = createMarker(point1,incident_num,incident_dt,incident_sev);
markers.push(marker);

This code initializes the single data points to display on the map and stores the incident number, date, and severity on the data point to display in the popup.

if (cnt == 1 )
{
var markerCluster = new MarkerClusterer(map1, markers);
}
}
function createMarker(point, popuphtml, date, severity)
{
var marker = new GMarker(point);
GEvent.addListener(marker, "click", function() {marker.openInfoWindowHtml("" + "Incident Number: " + popuphtml +"
" + "Incident Date: " + date+ "");});
return marker;
}

This code runs for each data point to show and displays the point, the popup box, and the on click event of navigating to the incident dashboard. The dashboard link is passing the prompts for the next dashboard. The popuphtml is a variable for incident number from the criteria.

Prefix

[−]

<html>
<head>
<script src="http://maps.google.com/maps?file=api&v=2.x&client=gme-dotdc&sensor=false" type="text/javascript"></script>
<script type="text/javascript">
var map1 = null;
var count = 1;
var point1 = null;
var marker;
var markers = [];
function initialize1(lat, long, cnt, incident_num,incident_dt,incident_sev)
{

if (count == 1)
{
if (GBrowserIsCompatible())
{
map1 = new GMap2(document.getElementById(1),{size:new GSize(900,600)});
map1.setCenter(new GLatLng(39.5, -98.35), 4);
map1.addControl(new GLargeMapControl());
map1.addControl(new GMapTypeControl());
markerClusterer = new MarkerClusterer(map1);
count = 0;
alert("Due to the large volume of Data this Report may take up to 5 Min to display the Map. Please do not close the browser.");
}
}
point1 = new GLatLng(lat, long);
marker = createMarker(point1,incident_num,incident_dt,incident_sev);
markers.push(marker);

if (cnt == 1 )
{
var markerCluster = new MarkerClusterer(map1, markers);
}
}
function createMarker(point, popuphtml, date, severity)
{
var marker = new GMarker(point);
GEvent.addListener(marker, "click", function() {marker.openInfoWindowHtml("<a href = http://hiptest.phmsa.dot.gov/analytics/saw.dll?Dashboard&PortalPath=/shared/Safety%20Maps/_portal/Safety%20Maps&Page=Incidents%20Navigation&Action=Navigate&col1=%22Incident%20-%20Form%205800%22.%22Incident%20Number%22&val1="+ popuphtml + "&col2=%22Incident%20Search%22.%22Incident%20Position%22&val2=" + severity + " target=\"_blank\">" + "Incident Number: " + popuphtml +"<br/> " + "Incident Date: " + date+ "</a>");});
return marker;
}


function MarkerClusterer(map, opt_markers, opt_opts) {
// private members
var clusters_ = [];
var map_ = map;
var maxZoom_ = null;
var me_ = this;
var gridSize_ = 60;
var sizes = [53, 56, 66, 78, 90];
var styles_ = [];
var leftMarkers_ = [];
var mcfn_ = null;

var i = 0;
for (i = 1; i <= 5; ++i) {
styles_.push({
'url': "http://gmaps-utility-library.googlecode.com/svn/trunk/markerclusterer/images/m" + i + ".png",
'height': sizes[i - 1],
'width': sizes[i - 1]
});
}

if (typeof opt_opts === "object" && opt_opts !== null) {
if (typeof opt_opts.gridSize === "number" && opt_opts.gridSize > 0) {
gridSize_ = opt_opts.gridSize;
}
if (typeof opt_opts.maxZoom === "number") {
maxZoom_ = opt_opts.maxZoom;
}
if (typeof opt_opts.styles === "object" && opt_opts.styles !== null && opt_opts.styles.length !== 0) {
styles_ = opt_opts.styles;
}
}


function addLeftMarkers_() {
if (leftMarkers_.length === 0) {
return;
}
var leftMarkers = [];
for (i = 0; i < leftMarkers_.length; ++i) {
me_.addMarker(leftMarkers_[i], true, null, null, true);
}
leftMarkers_ = leftMarkers;
}


this.getStyles_ = function () {
return styles_;
};


this.clearMarkers = function () {
for (var i = 0; i < clusters_.length; ++i) {
if (typeof clusters_[i] !== "undefined" && clusters_[i] !== null) {
clusters_[i].clearMarkers();
}
}
clusters_ = [];
leftMarkers_ = [];
GEvent.removeListener(mcfn_);
};

function isMarkerInViewport_(marker) {
return map_.getBounds().containsLatLng(marker.getLatLng());
}


function reAddMarkers_(markers) {
var len = markers.length;
var clusters = [];
for (var i = len - 1; i >= 0; --i) {
me_.addMarker(markers[i].marker, true, markers[i].isAdded, clusters, true);
}
addLeftMarkers_();
}


this.addMarker = function (marker, opt_isNodraw, opt_isAdded, opt_clusters, opt_isNoCheck) {
if (opt_isNoCheck !== true) {
if (!isMarkerInViewport_(marker)) {
leftMarkers_.push(marker);
return;
}
}

var isAdded = opt_isAdded;
var clusters = opt_clusters;
var pos = map_.fromLatLngToDivPixel(marker.getLatLng());

if (typeof isAdded !== "boolean") {
isAdded = false;
}
if (typeof clusters !== "object" || clusters === null) {
clusters = clusters_;
}

var length = clusters.length;
var cluster = null;
for (var i = length - 1; i >= 0; i--) {
cluster = clusters[i];
var center = cluster.getCenter();
if (center === null) {
continue;
}
center = map_.fromLatLngToDivPixel(center);

// Found a cluster which contains the marker.
if (pos.x >= center.x - gridSize_ && pos.x <= center.x + gridSize_ &&
pos.y >= center.y - gridSize_ && pos.y <= center.y + gridSize_) {
cluster.addMarker({
'isAdded': isAdded,
'marker': marker
});
if (!opt_isNodraw) {
cluster.redraw_();
}
return;
}
}

// No cluster contain the marker, create a new cluster.
cluster = new Cluster(this, map);
cluster.addMarker({
'isAdded': isAdded,
'marker': marker
});
if (!opt_isNodraw) {
cluster.redraw_();
}

// Add this cluster both in clusters provided and clusters_
clusters.push(cluster);
if (clusters !== clusters_) {
clusters_.push(cluster);
}
};


this.removeMarker = function (marker) {
for (var i = 0; i < clusters_.length; ++i) {
if (clusters_[i].remove(marker)) {
clusters_[i].redraw_();
return;
}
}
};


this.redraw_ = function () {
var clusters = this.getClustersInViewport_();
for (var i = 0; i < clusters.length; ++i) {
clusters[i].redraw_(true);
}
};


this.getClustersInViewport_ = function () {
var clusters = [];
var curBounds = map_.getBounds();
for (var i = 0; i < clusters_.length; i ++) {
if (clusters_[i].isInBounds(curBounds)) {
clusters.push(clusters_[i]);
}
}
return clusters;
};


this.getMaxZoom_ = function () {
return maxZoom_;
};


this.getMap_ = function () {
return map_;
};


this.getGridSize_ = function () {
return gridSize_;
};


this.getTotalMarkers = function () {
var result = 0;
for (var i = 0; i < clusters_.length; ++i) {
result += clusters_[i].getTotalMarkers();
}
return result;
};


this.getTotalClusters = function () {
return clusters_.length;
};


this.resetViewport = function () {
var clusters = this.getClustersInViewport_();
var tmpMarkers = [];
var removed = 0;

for (var i = 0; i < clusters.length; ++i) {
var cluster = clusters[i];
var oldZoom = cluster.getCurrentZoom();
if (oldZoom === null) {
continue;
}
var curZoom = map_.getZoom();
if (curZoom !== oldZoom) {

// If the cluster zoom level changed then destroy the cluster
// and collect its markers.
var mks = cluster.getMarkers();
for (var j = 0; j < mks.length; ++j) {
var newMarker = {
'isAdded': false,
'marker': mks[j].marker
};
tmpMarkers.push(newMarker);
}
cluster.clearMarkers();
removed++;
for (j = 0; j < clusters_.length; ++j) {
if (cluster === clusters_[j]) {
clusters_.splice(j, 1);
}
}
}
}

// Add the markers collected into marker cluster to reset
reAddMarkers_(tmpMarkers);
this.redraw_();
};


this.addMarkers = function (markers) {
for (var i = 0; i < markers.length; ++i) {
this.addMarker(markers[i], true);
}
this.redraw_();
};

// initialize
if (typeof opt_markers === "object" && opt_markers !== null) {
this.addMarkers(opt_markers);
}

// when map move end, regroup.
mcfn_ = GEvent.addListener(map_, "moveend", function () {
me_.resetViewport();
});
}


function Cluster(markerClusterer) {
var center_ = null;
var markers_ = [];
var markerClusterer_ = markerClusterer;
var map_ = markerClusterer.getMap_();
var clusterMarker_ = null;
var zoom_ = map_.getZoom();

this.getMarkers = function () {
return markers_;
};

this.isInBounds = function (bounds) {
if (center_ === null) {
return false;
}

if (!bounds) {
bounds = map_.getBounds();
}
var sw = map_.fromLatLngToDivPixel(bounds.getSouthWest());
var ne = map_.fromLatLngToDivPixel(bounds.getNorthEast());

var centerxy = map_.fromLatLngToDivPixel(center_);
var inViewport = true;
var gridSize = markerClusterer.getGridSize_();
if (zoom_ !== map_.getZoom()) {
var dl = map_.getZoom() - zoom_;
gridSize = Math.pow(2, dl) * gridSize;
}
if (ne.x !== sw.x && (centerxy.x + gridSize < sw.x || centerxy.x - gridSize > ne.x)) {
inViewport = false;
}
if (inViewport && (centerxy.y + gridSize < ne.y || centerxy.y - gridSize > sw.y)) {
inViewport = false;
}
return inViewport;
};

this.getCenter = function () {
return center_;
};


this.addMarker = function (marker) {
if (center_ === null) {
/*var pos = marker['marker'].getLatLng();
pos = map.fromLatLngToContainerPixel(pos);
pos.x = parseInt(pos.x - pos.x % (GRIDWIDTH * 2) + GRIDWIDTH);
pos.y = parseInt(pos.y - pos.y % (GRIDWIDTH * 2) + GRIDWIDTH);
center = map.fromContainerPixelToLatLng(pos);*/
center_ = marker.marker.getLatLng();
}
markers_.push(marker);
};


this.removeMarker = function (marker) {
for (var i = 0; i < markers_.length; ++i) {
if (marker === markers_[i].marker) {
if (markers_[i].isAdded) {
map_.removeOverlay(markers_[i].marker);
}
markers_.splice(i, 1);
return true;
}
}
return false;
};

this.getCurrentZoom = function () {
return zoom_;
};


this.redraw_ = function (isForce) {
if (!isForce && !this.isInBounds()) {
return;
}

// Set cluster zoom level.
zoom_ = map_.getZoom();
var i = 0;
var mz = markerClusterer.getMaxZoom_();
if (mz === null) {
mz = map_.getCurrentMapType().getMaximumResolution();
}
if (zoom_ >= mz || this.getTotalMarkers() === 1) {

// If current zoom level is beyond the max zoom level or the cluster
// have only one marker, the marker(s) in cluster will be showed on map.
for (i = 0; i < markers_.length; ++i) {
if (markers_[i].isAdded) {
if (markers_[i].marker.isHidden()) {
markers_[i].marker.show();
}
} else {
map_.addOverlay(markers_[i].marker);
markers_[i].isAdded = true;
}
}
if (clusterMarker_ !== null) {
clusterMarker_.hide();
}
} else {
// Else add a cluster marker on map to show the number of markers in
// this cluster.
for (i = 0; i < markers_.length; ++i) {
if (markers_[i].isAdded && (!markers_[i].marker.isHidden())) {
markers_[i].marker.hide();
}
}
if (clusterMarker_ === null) {
clusterMarker_ = new ClusterMarker_(center_, this.getTotalMarkers(), markerClusterer_.getStyles_(), markerClusterer_.getGridSize_());
map_.addOverlay(clusterMarker_);
} else {
if (clusterMarker_.isHidden()) {
clusterMarker_.show();
}
clusterMarker_.redraw(true);
}
}
};

this.clearMarkers = function () {
if (clusterMarker_ !== null) {
map_.removeOverlay(clusterMarker_);
}
for (var i = 0; i < markers_.length; ++i) {
if (markers_[i].isAdded) {
map_.removeOverlay(markers_[i].marker);
}
}
markers_ = [];
};

this.getTotalMarkers = function () {
return markers_.length;
};
}

function ClusterMarker_(latlng, count, styles, padding) {
var index = 0;
var dv = count;
while (dv !== 0) {
dv = parseInt(dv / 10, 10);
index ++;
}

if (styles.length < index) {
index = styles.length;
}
this.url_ = styles[index - 1].url;
this.height_ = styles[index - 1].height;
this.width_ = styles[index - 1].width;
this.textColor_ = styles[index - 1].opt_textColor;
this.anchor_ = styles[index - 1].opt_anchor;
this.latlng_ = latlng;
this.index_ = index;
this.styles_ = styles;
this.text_ = count;
this.padding_ = padding;
}

ClusterMarker_.prototype = new GOverlay();

ClusterMarker_.prototype.initialize = function (map) {
this.map_ = map;
var div = document.createElement("div");
var latlng = this.latlng_;
var pos = map.fromLatLngToDivPixel(latlng);
pos.x -= parseInt(this.width_ / 2, 10);
pos.y -= parseInt(this.height_ / 2, 10);
var mstyle = "";
if (document.all) {
mstyle = 'filter:progid:DXImageTransform.Microsoft.AlphaImageLoader(sizingMethod=scale,src="' + this.url_ + '");';
} else {
mstyle = "background:url(" + this.url_ + ");";
}
if (typeof this.anchor_ === "object") {
if (typeof this.anchor_[0] === "number" && this.anchor_[0] > 0 && this.anchor_[0] < this.height_) {
mstyle += 'height:' + (this.height_ - this.anchor_[0]) + 'px;padding-top:' + this.anchor_[0] + 'px;';
} else {
mstyle += 'height:' + this.height_ + 'px;line-height:' + this.height_ + 'px;';
}
if (typeof this.anchor_[1] === "number" && this.anchor_[1] > 0 && this.anchor_[1] < this.width_) {
mstyle += 'width:' + (this.width_ - this.anchor_[1]) + 'px;padding-left:' + this.anchor_[1] + 'px;';
} else {
mstyle += 'width:' + this.width_ + 'px;text-align:center;';
}
} else {
mstyle += 'height:' + this.height_ + 'px;line-height:' + this.height_ + 'px;';
mstyle += 'width:' + this.width_ + 'px;text-align:center;';
}
var txtColor = this.textColor_ ? this.textColor_ : 'black';

div.style.cssText = mstyle + 'cursor:pointer;top:' + pos.y + "px;left:" +
pos.x + "px;color:" + txtColor + ";position:absolute;font-size:11px;" +
'font-family:Arial,sans-serif;font-weight:bold';
div.innerHTML = this.text_;
map.getPane(G_MAP_MAP_PANE).appendChild(div);
var padding = this.padding_;
GEvent.addDomListener(div, "click", function () {
var pos = map.fromLatLngToDivPixel(latlng);
var sw = new GPoint(pos.x - padding, pos.y + padding);
sw = map.fromDivPixelToLatLng(sw);
var ne = new GPoint(pos.x + padding, pos.y - padding);
ne = map.fromDivPixelToLatLng(ne);
var zoom = map.getBoundsZoomLevel(new GLatLngBounds(sw, ne), map.getSize());
map.setCenter(latlng, zoom);
});
this.div_ = div;
};

ClusterMarker_.prototype.remove = function () {
this.div_.parentNode.removeChild(this.div_);
};

ClusterMarker_.prototype.copy = function () {
return new ClusterMarker_(this.latlng_, this.index_, this.text_, this.styles_, this.padding_);
};

ClusterMarker_.prototype.redraw = function (force) {
if (!force) {
return;
}
var pos = this.map_.fromLatLngToDivPixel(this.latlng_);
pos.x -= parseInt(this.width_ / 2, 10);
pos.y -= parseInt(this.height_ / 2, 10);
this.div_.style.top = pos.y + "px";
this.div_.style.left = pos.x + "px";
};

ClusterMarker_.prototype.hide = function () {
this.div_.style.display = "none";
};


ClusterMarker_.prototype.show = function () {
this.div_.style.display = "";
};

ClusterMarker_.prototype.isHidden = function () {
return this.div_.style.display === "none";
}
</script>
</head>
<body>

Narrative

[−]

<div id="1" > <script type="text/javascript"> initialize1('@10','@11','@12','@13','@14','@15') </script>
</div>

Postfix

[−]

</body>
</html>

And set the max rows to a number less than 5000.

The subsequent map is a little more straight forward, as it does not have the MapCluster API embedded.

This map has 3 different addresses describing the same event plotted with 3 different icons.

The Criteria for this map has 3 locations latitude and longitudes


Now for the map create a narrative report and use only 1 row to display

Prefix

[−]

<html>
<head>
<script src="http://maps.google.com/maps?file=api&v=2.x&client=gme-dotdc&sensor=false" type="text/javascript"></script>
<script type="text/javascript">
var map1 = null;
var newzoom;
var newcenter;

function initialize1(lat1, long1,lat2, long2, lat3, long3)
{

Note that the narrative criteria should map to this initialization block

if (GBrowserIsCompatible())
{
map1 = new GMap2(document.getElementById(1),{size:new GSize(900,600)});
map1.setCenter(new GLatLng(39.5, -98.35), 10);
map1.addControl(new GLargeMapControl());
map1.addControl(new GMapTypeControl());
}

var icon = new GIcon();
icon.image = "http://maps.google.com/mapfiles/arrow.png";
icon.shadow = "http://maps.google.com/mapfiles/arrowshadow.png";
icon.iconSize = new GSize(39, 34);
icon.shadowSize = new GSize(37, 34);
icon.iconAnchor = new GPoint(9, 34);
icon.infoWindowAnchor = new GPoint(9, 2);

Note: Creates the green arrow icon

var icona = new GIcon();
icona.image = "http://www.google.com/mapfiles/markerA.png";
icona.shadow = "http://www.google.com/mapfiles/shadow50.png";
icona.iconSize = new GSize(20, 34);
icona.shadowSize = new GSize(37, 34);
icona.iconAnchor = new GPoint(9, 34);
icona.infoWindowAnchor = new GPoint(9, 2);

Note: Creates the A tear drop icon

var iconz = new GIcon();
iconz.image = "http://www.google.com/mapfiles/markerZ.png";
iconz.shadow = "http://www.google.com/mapfiles/shadow50.png";
iconz.iconSize = new GSize(20, 34);
iconz.shadowSize = new GSize(37, 34);
iconz.iconAnchor = new GPoint(9, 34);
iconz.infoWindowAnchor = new GPoint(9, 2);

Note: Creates the Z tear drop icon

var bounds = new GLatLngBounds();

if (lat2 != 400)
{
point2 = new GLatLng(lat2, long2);
map1.addOverlay(new GMarker(point2, icona));
bounds.extend(point2);
newzoom = map1.getBoundsZoomLevel(bounds);
newcenter = bounds.getCenter();
map1.setCenter(newcenter,newzoom);
}

Plots point 2 using icon a

if (lat3 != 400)
{
point3 = new GLatLng(lat3, long3);
map1.addOverlay(new GMarker(point3, iconz));
bounds.extend(point3);
newzoom = map1.getBoundsZoomLevel(bounds);
newcenter = bounds.getCenter();
map1.setCenter(newcenter,newzoom);
}

Plots point 3 using icon z

if (lat1 != 400)
{
point1 = new GLatLng(lat1, long1);
map1.addOverlay(new GMarker(point1, icon));
bounds.extend(point1);
newzoom = map1.getBoundsZoomLevel(bounds);
newcenter = bounds.getCenter();
map1.setCenter(newcenter,newzoom);
}

Plots point 1 using icon

}
</script>
</head>
<body>

Narrative

[−]

<div id="1" > <script type="text/javascript"> initialize1('@10','@11','@12','@13','@14','@15') </script>
</div>

Postfix

[−]

</body>
</html>

OBIEE: Seeding the Dashboards for End Users and Purging on Nightly Basis

Many dashboards on most systems can be seeded with an iBot that runs overnight and caches the reports to the dashboards for quick access and less queries to the database. We created a series of iBots that are triggered by an initial iBot the purges the old Cache and starts the process of reseeding the data. First we created the iBot Purge Cache:

This iBot is run at 5:00am every night after the ETL completion by the Administrator user. The delivery content is the first report we needed to cache. The destination for the iBot is the Oracle BI Server Cache.

Notice in the Advanced section the javascript purgeSASCache.js, Below are the parameters that were set in the Advanced tab.

The javascript purgeSASCache.js should be placed in the folder OracleBI/Server/Scripts/Common
The below is the javascript file:

outStream.Close();

return output;

}

//////////////////////////////////////////////////////////

// Get WshShell object and run nqCmd. Capture the output

// so that we can handle erroneous conditions.

var wshShell = new ActiveXObject("WScript.Shell");

// Create a temp file to input the SQL statement.

var fso = new ActiveXObject("Scripting.FileSystemObject");

var tempFolder = fso.GetSpecialFolder(2);

var tempInFileName = fso.GetTempName();

var tempOutFileName = fso.GetTempName();

tempInFileName = tempFolder + "\\" + tempInFileName;

tempOutFileName = tempFolder + "\\" + tempOutFileName;

var tempInFile = fso.CreateTextFile(tempInFileName, true);

tempInFile.WriteLine(sqlStatement);

tempInFile.Close();

try

{

// execute

var dosCmd = nqCmd + " -d \"" + dsn + "\" -u \"" + user

+ "\" -p \"" + pswd + "\" -s \"" + tempInFileName + "\"" +

" -o \"" + tempOutFileName + "\"";

wshShell.Run(dosCmd, 0, true);

var output = GetOutput(fso, tempOutFileName);

// Remove the temp files

fso.DeleteFile(tempInFileName);

if (fso.FileExists(tempOutFileName)) {

fso.DeleteFile(tempOutFileName);

}

// Check the output for any errors

if (output.indexOf("Processed: 1 queries") == -1) {

ExitCode = -1;

Message = output;

}

else if (output.indexOf("Encountered") != -1) {

ExitCode = -2;

Message = output;

}

else {

ExitCode = 0;

}

} catch (e) {

if (fso.FileExists(tempInFileName)) {

fso.DeleteFile(tempInFileName);

}

if (fso.FileExists(tempOutFileName)) {

fso.DeleteFile(tempOutFileName);

}

throw e;

}

Integrating OBIEE and Google Maps with the MarkerClusterer API

Since we have started to integrate Google maps into an OBIEE application, the users have requested the ability to look at a Map of the US and see concentrations of points on a map. They wanted to see at a national level where the companies they were interested in were located. They also wanted the ability to zoom in and see exactly where each facility was located. The solution we chose to meet the requirement was the Google maps and the Marker Cluster API found here: Gmaps utility library

The way the code works as described by the creator: “MarkerClusterer collects markers into different clusters and displays the number of markers in each cluster with a label, creating new clusters as the map zoom level changes. The clustering algorithm is simple; for each new marker it sees, it either puts it inside a pre-existing cluster, or it creates a new cluster if the marker doesn’t lie within the bounds of any current cluster.”

So for our project we took a set of about 10,000 facility locations and attempted to map them but found the first limitation of JavaScript being that most clients will freeze if you plot over 6,000 or so points. So we gave the users the ability to filter the data on several criteria to limit the number of points in the map and placed a governor on the number of points by setting max rows to 3000. Below is the top zoom level view of the data set we started with:

You will notice that the default clustering algorithm has 3 colors representing clusters of more than 100 (Red), 10 to 99 (Yellow) and 2 to 9 (Blue). A single location will be represented with a tear drop icon. So now instead of seeing 3,000 separate points there are about 30 clusters on the map. The eye is quickly drawn to the northeast with its multiple red clusters. Now to meet the requirements a user can zoom in anywhere on the map and the clusters re-calculate.

So we have seen how the code works, below is the steps used to implement this on our project.

1. First we geocoded our addresses and stored latitudes and longitudes for each data point we wanted to map. If you do not geocode the addresses you really need to work with less points (less than 1000) and the results will take much longer.
2. Then we created a query that has the company facility name and internal company number (used for navigation), lat and long, and a piece of data called rank for displaying on the single points in the map. The RSUM(1) is used for displaying if more that 3000 rows could be displayed in the map.

The heavy lifting is done in a narrative view using JavaScript, the Google maps API, and the MarkerCluster API

1. Get a Google Maps API Key from: http://code.google.com/apis/maps/signup.html - Google maps API uses a key generated based on the IP and name from the calling server, so be sure you are on your web server when obtaining this. Get a key based on your own OBIEE server address.
2. Create a narrative view and make sure you mark the contains HTML Markup checkbox

In the Prefix box add the following code: (See the red text comments that explain parts of the code)

Prefix Text:

[−]

<html>
<head>
<script src="http://maps.google.com/maps?file=api&v=2.x&client=gme-dotdc&sensor=false" type="text/javascript"></script> //GOOGLE API
<script type="text/javascript">
var map1 = null;
var count = 1;
var point1 = null;
var marker;
var markers = [];
function initialize1(comp_facility, lat, long, row_wid, cnt, rank)
{

if (count == 1)
{
if (GBrowserIsCompatible())
{
map1 = new GMap2(document.getElementById(1),{size:new GSize(900,600)});
map1.setCenter(new GLatLng(39.5, -98.35), 4); //Center Map on US
map1.addControl(new GLargeMapControl());
map1.addControl(new GMapTypeControl());
markerClusterer = new MarkerClusterer(map1);
count = 0;
alert("Due to the large volume of Data this Report may take upto 5 Min to display the Map. Please do not close the browser."); //A Warning to users that the map generation may take time
}
}
point1 = new GLatLng(lat, long); // Creates the data point to display
// marker = new GMarker(point1);
marker = createMarker(point1, row_wid, comp_facility, rank); // Text to display on popup
markers.push(marker); // Type of marker for a single location

if (cnt == 1 )
{
var markerCluster = new MarkerClusterer(map1, markers);
}

}


function createMarker(point, int_num,cmp_facility, cmp_rank)
{

var marker = new GMarker(point);
GEvent.addListener(marker, "click", function() {marker.openInfoWindowHtml("<a href = http://www.yoururlhere.com/analytics/saw.dll?Dashboard&PortalPath=/shared/Safety%20Maps/_portal/Safety%20Maps&Page=Navigation&Action=Navigate&col1=Company.%22Internal%20Company%20Number%22&val1="+ int_num + " target=\"_blank\">" + cmp_facility +"<br/><center>" + "Company Rank: " + cmp_rank + "</center></a>");});
return marker; //On click event will take user to a OBIEE dashboard
}


function MarkerClusterer(map, opt_markers, opt_opts) {
// private members
var clusters_ = [];
var map_ = map;
var maxZoom_ = null;
var me_ = this;
var gridSize_ = 60;
var sizes = [53, 56, 66, 78, 90];
var styles_ = [];
var leftMarkers_ = [];
var mcfn_ = null;

var i = 0;
for (i = 1; i <= 5; ++i) {
styles_.push({
'url': "http://gmaps-utility-library.googlecode.com/svn/trunk/markerclusterer/images/m" + i + ".png", //location of the cluster graphics
'height': sizes[i - 1],
'width': sizes[i - 1]
});
} //code for generating the clusters

if (typeof opt_opts === "object" && opt_opts !== null) {
if (typeof opt_opts.gridSize === "number" && opt_opts.gridSize > 0) {
gridSize_ = opt_opts.gridSize;
}
if (typeof opt_opts.maxZoom === "number") {
maxZoom_ = opt_opts.maxZoom;
}
if (typeof opt_opts.styles === "object" && opt_opts.styles !== null && opt_opts.styles.length !== 0) {
styles_ = opt_opts.styles;
}
}


function addLeftMarkers_() {
if (leftMarkers_.length === 0) {
return;
}
var leftMarkers = [];
for (i = 0; i < leftMarkers_.length; ++i) {
me_.addMarker(leftMarkers_[i], true, null, null, true);
}
leftMarkers_ = leftMarkers;
}


this.getStyles_ = function () {
return styles_;
};


this.clearMarkers = function () {
for (var i = 0; i < clusters_.length; ++i) {
if (typeof clusters_[i] !== "undefined" && clusters_[i] !== null) {
clusters_[i].clearMarkers();
}
}
clusters_ = [];
leftMarkers_ = [];
GEvent.removeListener(mcfn_);
};

function isMarkerInViewport_(marker) {
return map_.getBounds().containsLatLng(marker.getLatLng());
}


function reAddMarkers_(markers) {
var len = markers.length;
var clusters = [];
for (var i = len - 1; i >= 0; --i) {
me_.addMarker(markers[i].marker, true, markers[i].isAdded, clusters, true);
}
addLeftMarkers_();
}


this.addMarker = function (marker, opt_isNodraw, opt_isAdded, opt_clusters, opt_isNoCheck) {
if (opt_isNoCheck !== true) {
if (!isMarkerInViewport_(marker)) {
leftMarkers_.push(marker);
return;
}
}

var isAdded = opt_isAdded;
var clusters = opt_clusters;
var pos = map_.fromLatLngToDivPixel(marker.getLatLng());

if (typeof isAdded !== "boolean") {
isAdded = false;
}
if (typeof clusters !== "object" || clusters === null) {
clusters = clusters_;
}

var length = clusters.length;
var cluster = null;
for (var i = length - 1; i >= 0; i--) {
cluster = clusters[i];
var center = cluster.getCenter();
if (center === null) {
continue;
}
center = map_.fromLatLngToDivPixel(center);

// Found a cluster which contains the marker.

if (pos.x >= center.x - gridSize_ && pos.x <= center.x + gridSize_ &&
pos.y >= center.y - gridSize_ && pos.y <= center.y + gridSize_) {
cluster.addMarker({
'isAdded': isAdded,
'marker': marker
});
if (!opt_isNodraw) {
cluster.redraw_();
}
return;
}
}

// No cluster contain the marker, create a new cluster.

cluster = new Cluster(this, map);
cluster.addMarker({
'isAdded': isAdded,
'marker': marker
});
if (!opt_isNodraw) {
cluster.redraw_();
}

// Add this cluster both in clusters provided and clusters_

clusters.push(cluster);
if (clusters !== clusters_) {
clusters_.push(cluster);
}
};

this.removeMarker = function (marker) {
for (var i = 0; i < clusters_.length; ++i) {
if (clusters_[i].remove(marker)) {
clusters_[i].redraw_();
return;
}
}
};

this.redraw_ = function () {
var clusters = this.getClustersInViewport_();
for (var i = 0; i < clusters.length; ++i) {
clusters[i].redraw_(true);
}
};

this.getClustersInViewport_ = function () {
var clusters = [];
var curBounds = map_.getBounds();
for (var i = 0; i < clusters_.length; i ++) {
if (clusters_[i].isInBounds(curBounds)) {
clusters.push(clusters_[i]);
}
}
return clusters;
};

this.getMaxZoom_ = function () {
return maxZoom_;
};

this.getMap_ = function () {
return map_;
};

this.getGridSize_ = function () {
return gridSize_;
};

this.getTotalMarkers = function () {
var result = 0;
for (var i = 0; i < clusters_.length; ++i) {
result += clusters_[i].getTotalMarkers();
}
return result;
};

this.getTotalClusters = function () {
return clusters_.length;
};

this.resetViewport = function () {
var clusters = this.getClustersInViewport_();
var tmpMarkers = [];
var removed = 0;

for (var i = 0; i < clusters.length; ++i) {
var cluster = clusters[i];
var oldZoom = cluster.getCurrentZoom();
if (oldZoom === null) {
continue;
}
var curZoom = map_.getZoom();
if (curZoom !== oldZoom) {

// If the cluster zoom level changed then destroy the cluster

// and collect its markers.

var mks = cluster.getMarkers();
for (var j = 0; j < mks.length; ++j) {
var newMarker = {
'isAdded': false,
'marker': mks[j].marker
};
tmpMarkers.push(newMarker);
}
cluster.clearMarkers();
removed++;
for (j = 0; j < clusters_.length; ++j) {
if (cluster === clusters_[j]) {
clusters_.splice(j, 1);
}
}
}
}

// Add the markers collected into marker cluster to reset

reAddMarkers_(tmpMarkers);
this.redraw_();
};


this.addMarkers = function (markers) {
for (var i = 0; i < markers.length; ++i) {
this.addMarker(markers[i], true);
}
this.redraw_();
};

// initialize

if (typeof opt_markers === "object" && opt_markers !== null) {
this.addMarkers(opt_markers);
}

// when map move end, regroup.

mcfn_ = GEvent.addListener(map_, "moveend", function () {
me_.resetViewport();
});
}


function Cluster(markerClusterer) {
var center_ = null;
var markers_ = [];
var markerClusterer_ = markerClusterer;
var map_ = markerClusterer.getMap_();
var clusterMarker_ = null;
var zoom_ = map_.getZoom();

this.getMarkers = function () {
return markers_;
};

this.isInBounds = function (bounds) {
if (center_ === null) {
return false;
}

if (!bounds) {
bounds = map_.getBounds();
}
var sw = map_.fromLatLngToDivPixel(bounds.getSouthWest());
var ne = map_.fromLatLngToDivPixel(bounds.getNorthEast());

var centerxy = map_.fromLatLngToDivPixel(center_);
var inViewport = true;
var gridSize = markerClusterer.getGridSize_();
if (zoom_ !== map_.getZoom()) {
var dl = map_.getZoom() - zoom_;
gridSize = Math.pow(2, dl) * gridSize;
}
if (ne.x !== sw.x && (centerxy.x + gridSize < sw.x || centerxy.x - gridSize > ne.x)) {
inViewport = false;
}
if (inViewport && (centerxy.y + gridSize < ne.y || centerxy.y - gridSize > sw.y)) {
inViewport = false;
}
return inViewport;
};

this.getCenter = function () {
return center_;
};

this.addMarker = function (marker) {
if (center_ === null) {
/*var pos = marker['marker'].getLatLng();
pos = map.fromLatLngToContainerPixel(pos);
pos.x = parseInt(pos.x - pos.x % (GRIDWIDTH * 2) + GRIDWIDTH);
pos.y = parseInt(pos.y - pos.y % (GRIDWIDTH * 2) + GRIDWIDTH);
center = map.fromContainerPixelToLatLng(pos);*/
center_ = marker.marker.getLatLng();
}
markers_.push(marker);
};

this.removeMarker = function (marker) {
for (var i = 0; i < markers_.length; ++i) {
if (marker === markers_[i].marker) {
if (markers_[i].isAdded) {
map_.removeOverlay(markers_[i].marker);
}
markers_.splice(i, 1);
return true;
}
}
return false;
};

this.getCurrentZoom = function () {
return zoom_;
};

this.redraw_ = function (isForce) {
if (!isForce && !this.isInBounds()) {
return;
}

// Set cluster zoom level.

zoom_ = map_.getZoom();
var i = 0;
var mz = markerClusterer.getMaxZoom_();
if (mz === null) {
mz = map_.getCurrentMapType().getMaximumResolution();
}
if (zoom_ >= mz || this.getTotalMarkers() === 1) {

// If current zoom level is beyond the max zoom level or the cluster

// have only one marker, the marker(s) in cluster will be showed on map.

for (i = 0; i < markers_.length; ++i) {
if (markers_[i].isAdded) {
if (markers_[i].marker.isHidden()) {
markers_[i].marker.show();
}
} else {
map_.addOverlay(markers_[i].marker);
markers_[i].isAdded = true;
}
}
if (clusterMarker_ !== null) {
clusterMarker_.hide();
}
} else {

// Else add a cluster marker on map to show the number of markers in

// this cluster.

for (i = 0; i < markers_.length; ++i) {
if (markers_[i].isAdded && (!markers_[i].marker.isHidden())) {
markers_[i].marker.hide();
}
}
if (clusterMarker_ === null) {
clusterMarker_ = new ClusterMarker_(center_, this.getTotalMarkers(), markerClusterer_.getStyles_(), markerClusterer_.getGridSize_());
map_.addOverlay(clusterMarker_);
} else {
if (clusterMarker_.isHidden()) {
clusterMarker_.show();
}
clusterMarker_.redraw(true);
}
}
};

this.clearMarkers = function () {
if (clusterMarker_ !== null) {
map_.removeOverlay(clusterMarker_);
}
for (var i = 0; i < markers_.length; ++i) {
if (markers_[i].isAdded) {
map_.removeOverlay(markers_[i].marker);
}
}
markers_ = [];
};

this.getTotalMarkers = function () {
return markers_.length;
};
}

function ClusterMarker_(latlng, count, styles, padding) {
var index = 0;
var dv = count;
while (dv !== 0) {
dv = parseInt(dv / 10, 10);
index ++;
}

if (styles.length < index) {
index = styles.length;
}
this.url_ = styles[index - 1].url;
this.height_ = styles[index - 1].height;
this.width_ = styles[index - 1].width;
this.textColor_ = styles[index - 1].opt_textColor;
this.anchor_ = styles[index - 1].opt_anchor;
this.latlng_ = latlng;
this.index_ = index;
this.styles_ = styles;
this.text_ = count;
this.padding_ = padding;
}

ClusterMarker_.prototype = new GOverlay();

ClusterMarker_.prototype.initialize = function (map) {
this.map_ = map;
var div = document.createElement("div");
var latlng = this.latlng_;
var pos = map.fromLatLngToDivPixel(latlng);
pos.x -= parseInt(this.width_ / 2, 10);
pos.y -= parseInt(this.height_ / 2, 10);
var mstyle = "";
if (document.all) {
mstyle = 'filter:progid:DXImageTransform.Microsoft.AlphaImageLoader(sizingMethod=scale,src="' + this.url_ + '");';
} else {
mstyle = "background:url(" + this.url_ + ");";
}
if (typeof this.anchor_ === "object") {
if (typeof this.anchor_[0] === "number" && this.anchor_[0] > 0 && this.anchor_[0] < this.height_) {
mstyle += 'height:' + (this.height_ - this.anchor_[0]) + 'px;padding-top:' + this.anchor_[0] + 'px;';
} else {
mstyle += 'height:' + this.height_ + 'px;line-height:' + this.height_ + 'px;';
}
if (typeof this.anchor_[1] === "number" && this.anchor_[1] > 0 && this.anchor_[1] < this.width_) {
mstyle += 'width:' + (this.width_ - this.anchor_[1]) + 'px;padding-left:' + this.anchor_[1] + 'px;';
} else {
mstyle += 'width:' + this.width_ + 'px;text-align:center;';
}
} else {
mstyle += 'height:' + this.height_ + 'px;line-height:' + this.height_ + 'px;';
mstyle += 'width:' + this.width_ + 'px;text-align:center;';
}
var txtColor = this.textColor_ ? this.textColor_ : 'black';

div.style.cssText = mstyle + 'cursor:pointer;top:' + pos.y + "px;left:" +
pos.x + "px;color:" + txtColor + ";position:absolute;font-size:11px;" +
'font-family:Arial,sans-serif;font-weight:bold';
div.innerHTML = this.text_;
map.getPane(G_MAP_MAP_PANE).appendChild(div);
var padding = this.padding_;
GEvent.addDomListener(div, "click", function () {
var pos = map.fromLatLngToDivPixel(latlng);
var sw = new GPoint(pos.x - padding, pos.y + padding);
sw = map.fromDivPixelToLatLng(sw);
var ne = new GPoint(pos.x + padding, pos.y - padding);
ne = map.fromDivPixelToLatLng(ne);
var zoom = map.getBoundsZoomLevel(new GLatLngBounds(sw, ne), map.getSize());
map.setCenter(latlng, zoom);
});
this.div_ = div;
};

ClusterMarker_.prototype.remove = function () {
this.div_.parentNode.removeChild(this.div_);
};

ClusterMarker_.prototype.copy = function () {
return new ClusterMarker_(this.latlng_, this.index_, this.text_, this.styles_, this.padding_);
};

ClusterMarker_.prototype.redraw = function (force) {
if (!force) {
return;
}
var pos = this.map_.fromLatLngToDivPixel(this.latlng_);
pos.x -= parseInt(this.width_ / 2, 10);
pos.y -= parseInt(this.height_ / 2, 10);
this.div_.style.top = pos.y + "px";
this.div_.style.left = pos.x + "px";
};

ClusterMarker_.prototype.hide = function () {
this.div_.style.display = "none";
};

ClusterMarker_.prototype.show = function () {
this.div_.style.display = "";
};

ClusterMarker_.prototype.isHidden = function () {
return this.div_.style.display === "none";
}
</script>
</head>
<body>

 Narrative text:

Narrative Text:

[−]

<div id="1" > <script type="text/javascript"> initialize1('@1','@3','@4','@5','@6','@2') </script>

</div>

Note the@ numbers are in the order of the function call in the prefix:

function initialize1(comp_facility, lat, long, row_wid, cnt, rank)

Finally in the PostFix add:

Postfix Text:

[−]

</body>

</html>

And set the max rows to a number less than 5000.

The next blog I will show how to use the single points on the map to drill down into another dashboard in OBIEE.

Extend OBIEE Charts – Part 1: Google Chart API

OBIEE Answers provides quite a number of useful charts but it’s always nice to have a few additional options, which is the reason for this entry.   A few of our clients have expressed an interest in seeing heat Maps, Spark Lines, Bullet Graphs, and several more custom graphs embedded within OBIEE.   For this entry, we’ll take a look at Google’s Chart API and show you how we’ve integrated Google Charts within OBIEE Answers.

The Google Chart API allows you to dynamically generate your graphs by simply passing the parameters to an URL and in turn, Google Chart will respond with a PNG-image of your chart. You can then embed this image in tag anywhere in a webpage. For more information on the Chart API see here.

In this illustration, we will create a heat map using US map and data from one of our OBIEE Answers request. Here is the table:

(first column contains US state abbreviations and the second is just a random number from 1 to 6)

Now insert a Narrative view and writing the following codes to its corresponding sections:

Prefix section:

<!--first show a dummy map-->
<img src="http://chart.apis.google.com/chart?cht=t&chm=usa&chs=440x220" id="GD_mapImg"/>

<script type="text/javascript">
  // setting up parameters
  var chartURL = "http://chart.apis.google.com/chart?cht=t&chtm=usa&chs=440x220&chds=1,6";
  var chartBGColor = "&chf=bg,s,EAF7FE";

  //specify the gradient: <default color>, <start of gradient>,.., <end of gradient>
  var chartPalette = "&chco=FFF5EB,FEE6CE,FDAE6B,F16913,D94801,A63603,7F2704";

  var chartDataLabels = new Array();
  var chartData = new Array();

Narrative Text:

  chartDataLabels.push('@1');
  chartData.push(@2);

Postfix Text:


  // putting it all together
  chartURL = chartURL + chartBGColor + chartPalette + "&chld=" + chartDataLabels.toString().replace(/,/g,'') + "&chd=t:" + chartData.toString();
  // now get the image from Google Chart
  document.getElementById('GD_mapImg').src = chartURL;
</script>

Check the box “Contains HTML Markup”. Remember this is required.

That’s it, you should see the map in the results section -

To customize the map, see here for more information


Limitations of Google Chart:
* Not interactive: it is just a static PNG image.
* Lack of Legend: especially no legend for Maps.
* Small chart size: largest Map is 440x220

Overall, this simple heat map works well and satisfies most of our current requirements. If you are looking for interactive and more eye-catching maps or charts in general, please be on the look-out for the second part of this series where we will show the same Narrative view approach with a slightly different API.  Spoiler alert: it is another great product from Google.

Utilizing EBS Responsibilities for OBIEE Authorization

As you may already know, the GROUP session variable in the OBI repository can be utilized to store group information brought in from external sources. The repository will assign users to local repository groups when the external and internal group names match.

For instance, if we had an EBS user with a responsibility of 'OBI Admin', we could read that information from EBS and store it in our local GROUP variable. We could then create a repository group with the same name, 'OBI Admin', and assign appropriate permissions to the group. The user would then automatically be assigned to the local 'OBI Admin' group and inherit all permissions within that group. The following diagram depicts this process flow:



The issue here is the GROUP variable is parsed in a certain manner and has specific requirements in order to work correctly. Namely, each group stored in the GROUP variable must reside in the same field and must be separated by a semi-colon. Therefore, row-wise initialization does not work for this process.

So, armed with the above information, we are able to create some custom SQL in the repository that will read all of the responsibilities from the EBS tables for the authenticated user and then roll them up into one field with each group name separated by a semi-colon.

Development Process:

1. Create a new Session Initializaton Block in the RPD. Name it something like 'getEBSGroups'.
2. Edit the Data Source for the initialization block to point to your EBS source.
3. Input the following SQL statement into the Default Initialization String block:





4. Create two Data Target Variables. The first is used to store the user name and the second is used to store the group information. You may call the first 'EBS_Username'. Make sure you name the second variable 'GROUP'.
5. Because you are authenticating against OID (LDAP), you should already have an initialization block for authentication purposes. You will need to ensure you edit your execution precedence to run the authentication initialization block prior to attempting to run this block.
6. Also ensure the user variable you utilized in your authentication initialization block is specified correctly in the following portion of the custom SQL:user_name=upper('valueof(NQ_SESSION.USER)')
7. Create repository groups that exactly match the names of the EBS responsibilities you are reading in and storing in the 'GROUP' variable.

Upon successful authentication into OBIEE, this new initialization block will kick off and query the EBS tables for the responsibilities of the authenticated user. It will store all of the user's EBS responsibilities in the GROUP variable. The GROUP variable will be parsed by the BI server and any responsibility in the variable that matches an existing repository group will result in the authenticated user being automatically assigned to that repository group!

Business Objects XI R2 and Google Maps

Have you ever felt the need for more visualization techniques than WebIntelligence delivers out of the box? One example is mapping and this is where Business Objects Masher comes in. By installing the Business Objects Masher server, you now have a way to access APIs, such as Google Maps or Flash, and easily incorporate them into a WebIntelligence document. The following diagram shows how mapping integrates into the Business Objects architecture.

The Masher tool comes with two components:

1) Mashup Editor - converts a WebIntelligence report into a Mashup report
2) Template Editor - contains the instructions for how WebIntelligence will convert to the Mashup report you choose

The Template Editor, as shown below, contains several definitions that need to be set for a new template.

Mashup Template Editor: Google Map

In the example of the Google Map, the Requirements needed (parameters passed by the WebIntelligence report) are an address, state, and zip code. These dimensions can be plotted on the X axis while the measures are plotted on the Y. Additional JavaScript coding can be done in the Masher functions of Init, Mash, and Dispose, to perform functions such as creating the markers with specific values, sending the Google API key, and adding in controls like Zoom, Traffic, and Search.

In the example below using the Mash function, these additional controls will be updated any time the WebIntelligence report is refreshed or if the filters are updated.

map.enableScrollWheelZoom();

map.enableContinuousZoom();

var trafficOptions = {incidents:true};

trafficInfo = new GTrafficOverlay(trafficOptions);

map.addOverlay(trafficInfo);

map.addControl(new TrafficControl());

map.addControl(new GLargeMapControl3D());

map.enableGoogleBar();

Additional Controls defined in Mash

After creating a WebIntelligence report with the dimensions and measures needed for the mapping template, you can convert the report to a Mashup report by using the Mashup Editor as seen below. Open the WebIntelligence report, right click on the table, choose “Turn to Table”, and pick the new Google Map format. Then format the Chart to define your parameters for the X and Y axis as defined in the template.

Mashup Editor

Format Chart
 

After saving your report in the Mashup Editor, you can log into WebIntelligence and view your report as you would any other. Our example map can be seen below:

Example Map