Art of BI: OBIEE and Google Maps Integration

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

 
Code For Narrative Property Section

Code For PostFix Property Section

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.

Contact Us
Christian Screen
Christian is an innovator in analytics and data warehousing design, best practices, and delivery. With more than fifteenyears of decision support and data warehousing with key experiences at Office Depot HQ, Sierra-Cedar, and Capgemini, he oversees the Oracle Analytics Practice which includes the technical development and delivery of Oracle BI collaboration software, data warehouse solutions, Oracle BI/EPM projects, and packaged analytics solutions at Datavail.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

41 thoughts on “Art of BI: OBIEE and Google Maps Integration”
  1. hi,tnks for your even more detailed example.
    i have already use google map with oracle bi.
    it is really bussiness helpful,to add links in one db column(a simple answer let's say about details of the store) and pass it through showAddress(..,….) function .
    I face a lot of problems with Internet Explore(all versions) , it displays map in the preview window in narative view but not in the dashboard.Any ideas??
    In firefox 3 and chrome loads ok.
    keep the good work.

  2. Hi,tnks for the reply….
    i was working a lot and i forgot to mention that your code works perfect and in Internet Explorer…
    i beleive that Explorer has problem with <span> which i did not use…
    Tnks one more time…
    keep the good work

  3. Hi there,

    Have you come across any OBIEE implementation where OBIEE report will be refreshed based on the navigation in Google maps?
    Like reading parameters from Maps back to OBIEE reports ?? Are there any API's that can give back data from google maps?

    Best regards,
    Srini

  4. Hello
    I am trying to use a map of google in my request, but I have a problem:

    If I put this line in the prefix: Function GetMapAdress ()
    shows me the icon for the first city of my table

    Now if I put the line in narrative me there turns out to be alone the icon of the last city of the table

    For that this passing this??

    Forgive the Englishman

    1. Nora,

      I would have to see the dataset that you working with. However, I can tell you that the way the narrative section works, regardless of Google Maps or not is that:
      1.) The prefix and postfix are static and should be the begining and end of your code respectively
      2.) The narrative section basically acts like a foreach loop. So, for each record that exists in your recordset the narrative section will be iterated or executed. That is to say, if you have a recordset with 5 records the code in the narrative section will be run 5 times.

      Let me know if that helps.

      1. Thank you, already I work. Tape-worm evil located one {.
        Other one consults, the map me works to level of cities, but not of sectors (citadels). Notice that the names that appear in Google Maps do not coincide with the registered ones with my database.
        Can it indicate me if this sera the disadvantage??

  5. Hello

    I am not able to see a map based on the report address and it is only 1 record for testing purposes.
    Any advice will be highly appreciated.

    Thanks!
    Ashwin

    1. Ok I got the google map but only on the Dashboard Preview area! Wondering again but for another thing this time.

      Thanks!

    2. Ashwin,

      You will not be able to see the map in the display section when editing the narrative view. Also, attempt to hard-code your address in the narrative property section.
      For example, showAddress('123 Main St., Lynchburg, VA 24501', 'Testing');

      If the hard-coding works then your problem will be with the address you are inputing from your test recordset.

    1. Jin,

      I have not actually tested this with IE6; only IE7+.

      Test your code in IE7 to see if it works there. If you scan the web you will see that a lot of web developer condemn IE6 as the devil simply because Microsoft did such a shisty job with it. It has huge JavaScript and CSS incompatability issues that every decent web developer has had to work around at some point or another.

      Most companies mandate an IE version for use company wide. Most were quick to upgrade to IE7 and away from IE6. I suggest you do the same and not waste your time trying to tame the dinosaur that is IE6.

      -Christian

  6. Hi,
    Neat step wise description of the topic. Very helpful indeed!

    In the similar lines I am trying to integrate google BioHeatMaps into my narrative view. At the moment I am just trying to display the exact same chart that is described in the example, instead of any dynamic data from my reports, just to see if it works.

    http://informatics.systemsbiology.net/visualizati

    Since I am not using any data from my reports I tried various methods of including the entire code in either the prefix section or the postfix section , in both cases I don't get any output in the Display results section when I check the contains HTML markup.

    I believe there is no no need for any api key here ? when I run that exact same code in a browser as html , it just works fine (renders the heatmap).

    Appreciate any tips/insights or suggestions into how I can fix this.

    Thank you!

    1. Hello Christian,
      Appreciate if you could give me some pointers to integrate the BioHeatMaps with OBIEE narrative view.

      Thanks!

  7. Hello all

    I'm pretty new to Siebel and also google maps api. I was playing arround with the code you posted on the blog and it's working fine 🙂
    The thing is I'd like to do something a bit more complex: paint a different icon for each different site I got. I created a new icon and I'd like to use it as following: depending on the name of the city I'd lke to mark. For example: I'd like Madrid to have a different Icon to Barcelona… Is this possible? Any suggested approach?

  8. Hi All,

    need some help! i keep getting an error saying the website needs a different google maps api key. I have generated a key for my presentation sever http://pserver:9704 several times but it still fails. Any ideas? I am using firefox 3.5

    Thanks

    1. Jason,

      If you are stating that you have registered a key for the google maps API for the url, http://pserver:9704 this will of course create a problem. Make sure that when you register a key that you register it for your top level domain (TLD) name or your machine name only. For example, http://pserver or http://localhost.

      Please also note that if you are testing your OBIEE Google Map integration on a test server (ex: PSERVER) locally (by remoting in or whatever), then the Google API Key created for http://pserver will work when you access the URL as you are on that server, ex: http://pserver:9704/analytics/…, but if you are then trying to reference Presentation Services via the web and pointing to PSERVER's DNS (ex: http://obiee.mycompany.com:9704), you will of course need a different Google Maps API key for the domain http://obiee.mycompany.com per my example

      I hope this helps. Let me know.

      Cheers,
      C

  9. Hi Christian,
    Thanks a lot for your prompt reply. Greatly appreciate it. I created another key for http://pserver and OBIEE stopped complaining about the invalid key. However now in the Narrative section I have the Display Results checked but i see no results. It keeps spinning "Retrieving View… Please wait"

    I have three column in my requests. City, State & Zip
    what i am i doing wrong.

    Thanks for all your help.
    J.

    1. Jason,

      In regards to your new issue, I believe that I described this symptom in my blog post.

      Basically, OBIEE is retarted here. If you scan through the HTML source that makes up the narrative page, it is not correctly developed to enable dynamic HTML such as the Google mapping, javascript, etc. to run. The Google mapping will never show up in the Display Results section (when the HTML checkbox is checked) because of this. To see your test results you need to hit the show in dashboard button or save the results and view in your report/dashboard.

      Sounds like you are good to go now.

      Cheers,
      Christian

      1. Hi Christian,

        I am trying to implement the same functionality, and I can see the map if I add the code to a text item and clicked on the "Inlcude HTML markup", but when I add it to the narrative view, the map never get rendered. So, I have confirmed that my key works and that there are no proxy issues, but I still cannot see the map.

        Any help will be greatly apprecitated

  10. All,

    I've developed a small data mart representing information about my runs (time, distance, location, etc.). I have OBIEE set up as my BI Environment, and would like to integrate Google Maps into a few reports.

    I have all latitudes and logitudes captured in a table at (3 second incriments – about every 20ft.)…

    Can someone please give me a few pointers on how to call the lat / longs directly (rather than converting to address)?

    Thanks,

    Jonathan

  11. Just a quick tid bit…on line 63 there is an escape sequence on the div anchor tag that is not needed.

    Current: var popuphtml = "<div id="popup">" + popuphtml + "</div>";
    Change to: var popuphtml = "<div id="popup">" + popuphtml + "</div>";

    Hope this helps.

  12. All,

    I have semi-successfully integrated google maps to display lat/lon coordinates for a particular workout…Each coordinate has a unique LOCATION_ID, and I have filtered my report to return every 10 records (MOD).

    My issues is that, OBIEE seems to drop several records when plotting spatially. For example, if I have 50 records in my table (and 50 unique coordinates) then OBIEE will only plot 20 on the map.

    If I refresh my browser, clear the presentation cache, and re-run the request, OBIEE will generate a map with a different 20 coordinates.

    Can anyone help to successfully plot all points?

    Thanks

  13. Great article.
    I have the same problem as Jonathan where only some records are plotted and others are simply not there.
    Is there any solution to this?

    Also can any one comment on the security this affords to 'client sensitive' data.
    Does the 'address' data get transmitted along with the 'Total Ordered Amount' over the internet/to be stored or accessed by google?
    Thanks,
    Anisha

  14. Hallo, please help me with following issue:

    I am using to display in map directly GPS coordinates
    showAddress(‘@1, @2’);
    where @1, @2 stands for GPS Lat. and GPS Long.
    @1, @2 are in form with decimal comma, e.g. 49,2345 and google map work only with

    decimal point 49.2345.
    Is there any way how to replace point instead of comma?
    I have tried JAVA function replace

    var Lat_a;
    var Lat_b;
    Lat_a = ‘@1’.replace( ‘,’ , ‘.’ );
    Lat_b = ‘@2’.replace( ‘,’ , ‘.’ );
    showAddress(‘Lat_a, Lat_b’);

    but this substitution is not working. Maybe some error in Java code???

    Please help.
    Thank in advance,
    rgs,
    Michal

  15. This looked great, so I gave it a try, and unfortunately the Narrative view just sticks on 'Retrieving view … please wait'. I never get any results back. Tried the whole thing three separate times now. I've built the exact same four columns as in the example (State, City, Zip, $$), copied/pasted the code into the appropriate sections of the Narrative, and pasted in my API key. The little 'Retrieving view' clock just keeps spinning. Any thoughts?

    1. Hi,

      I created report exactly same way as demonstrated with four columns State, city, postal code and amount. after that created narrative view with code and replaced with my Google Maps API Key.

      I clicked on Dashboard preview but nothing appears, even after publishing report on dashboard nothing appears just a blank page. I know you said you need to see results in compound view, in compound view I see results but no map.

      Can you please help me.

      Dan

  16. don’t you hate it when you are lost? it really is a drag having to ask people to get directions… that’s why i need to say that handheld gps is a life saver….

  17. Hello,
    Thanks for the great post. I have incorporated your code as a narrative in the report, but I am unable to render a map. It renders successfully when I take the same code and add it to text item and check the "includes HTML markup" check box, which tell me that my google API key is good and that there are no proxy issues. In addition, I enabled some alerts in the script, but when implemented as a narrative, I do not even get those alerts. So it is as if the code does not get executed at all.

    Any help will be greatly appreciated

    Moez

  18. Hi Christian,

    Your blog really helped a lot. Need a small help. In our report, we have different locations, where we have to display different countries such as India, Australia, Netherlands, US etc.

    But being geographically apart, the graph doesnt showup every data point at one shot.

    Hence,I’d like to know is there a way to show all of these countries nearby with your code? Apologies, am new to this geocoding, though I could understand this to some extent as part of my googling

    Regards,
    Vinag

  19. Hi Christian,

    Nice post, I’m usign google maps in BI dashboards but showing this dashboards with web center using Impersonate connection to BI. When do that the maps doesn’t show, Do you know why?

    thanks in advance.

    Aldo

  20. Hi Christian,

    Nice post, I’m usign google maps in BI dashboards but showing this dashboards with web center using Impersonate connection to BI. When do that the maps doesn’t show, Do you know why?

    thanks in advance.

    Aldo