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>