Select Page

Art of BI: OBIEE and Google Maps Integration

Author: Christian Screen | | July 14, 2009

Google Maps is very popular and when someone first mentioned to me that an integration between OBIEE and the Google Maps API was possible I was totally stoked. For a sales, marketing, supply chain and many other subject area reporting analytics this is awesome.  You get the picture.

Recently a colleague mentioned they were having problems getting the integration to work and I just had to chime in.  They had done several things right, such as getting the API key from google (one should always register it for http://localhost/ unless domain restrictions don’t otherwise allow for full blown testing using this URL) , they had built a simple report with city, state, and zip, and they downloaded a code snippet from another OBIEE blogger who has also been playing with the integration. That blogger had them off to a decent start but they immediately noticed problems with the logic. They were unable to view the map properly when editing, etc.  The other OBIEE Google map bloggers have probably never done any serious web development integration before and that’s where the problems start. I’ll seek to set you on the correct path in this blog series.  I’ll start you with a simple example of an Answers Google Map integration and the code that goes along with it.  So, if you want your report to look similar to the one below, without any glitches, then continue reading and follow along.

OBIEE / Google Maps – Single or Multiple Locations & GeoCoding

This solution works for either a single or multiple locations.  If you aren’t familiar with Geo-Coding the smartest thing to do is a Google™ search so that you know how this type of mapping actually works. In an enterprise solution you would want to Geo-Code all of your locations (i.e. stores, distribution centers, or any facility with an address) in advance. There are several ways of doing this and there are several companies that sell Software as a Service (SAaS). We like a few and we’ll mention those later. If you Geo-Code in advance you can simply store the latitude and longitude in the database possibly alongside your address information. When you specify lat/long to produce your map the performance is at least 3-times better than simply plotting an address. For simplicity sakes this example allows Google Maps to Geo-Code for us during our main operation.

Build a Simple Location Report

This is a high-level tutorial so we won’t discuss how to build a basic Answers report. Using your own dataset and OBIEE subject area model, create a report containing at a minimum a physical location (i.e.: city, state, zip). We put all of ours in separate columns for this example so that you can see how the OBIEE narrative section utilizes its effective iteration over our table recordset. Most locations have an address and hypothetically you would have that for most location.  We excluded it to keep the example simple. We even through in a sales measure column for additional affect you’ll see later on.

Where to Build the Map?

After your build your simple location report as above, view it in the results tab click the layouts drop-down and locate the “Narrative” option.
This is where we will be confronted with the property textboxes to begin coding for our Google Map view based on the table recordset from the simple location report.

The Narrative View

When you first navigate to this view all property textboxes will be blank. Use the code provided below to fill in the appropriate boxes. This must be accurate or you will have unexpected results. Ultimately we want your Narrative property boxes to look like the image below. I have highlighted what each of different narrative sections do as they correspond with the table view recordset from our simple location report. This is typical of the narrative section. It is basically a for-each loop.

Code For Prefix Property Section


{script src='http://maps.google.com/maps?file=api&v=2&sensor=false&key=[YOUR GOOGLE MAP API KEY HERE]' type='text/javascript'}{/script}

{script type='text/javascript'}
// Declarations
var map = null;
    var geocoder = null;
    var marker = null;

    window.onload = addCodeToFunction(window.onload, function() {
      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), 9);
        // 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, 9);
                          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"}{p}" + popuphtml + "{ /div}";         var marker = new GMarker(point, icon);         GEvent.addListener(marker, "click", function() {marker.openInfoWindowHtml(popuphtml);});         return marker;     } function addCodeToFunction(func, code){    if(func == undefined)       return code;    else{       return function(){          func();         code();       }    } } function GetMapAdress() { // From here it's build dynamicly in OBIEE showAddress('@2, @1, @3', '@2, @1 @3 has {span style="color:"}@4{/span} worth of product on order'); } window.onunload = GUnload(); {/script} {div id="map_canvas" style="width: 600px; height: 400px;"}{/div}

Additional Integration Notes

  • Ensure that the checkbox marked “Contains HTML Markup” is checked
  • View your Answers view in Compound Layout after saving the report
  • Don’t forget to add your own Google Maps API key to the script. This is really important.

Conclusion

This is more than a head-start for the integration. They key for the integration is in the use of JavaScript (Not Java and not Java Server Pages, don’t even get me started on people’s juxtaposition of the three). All previous bloggers to date try to use and tags in their code and that is what screws up their code. I’ll go into the how and why of this integration method in comments or in a part2 of this series if there is interest. I have a lot to share here but choose not to type aimlessly. Please comment with any questions and I’ll get back to you there.

Oracle BI Publisher (BIP) Tips: Functions, Calculations & More

Check out these BI Publisher tips including functions & calculations so you can understand more about the production and support of BI Publisher reports.

Sherry Milad | January 15, 2018

How to Index a Fact Table – A Best Practice

At the base of any good BI project is a solid data warehouse or data mart.

Christian Screen | March 16, 2010

Qlik vs. Tableau vs. Power BI: Which BI Tool Is Right for You?

Tableau, Power BI, and Qlik each have their benefits. What are they and how do you choose? Read this blog post for a quick analysis.

Tom Hoblitzell | June 6, 2019

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS