Select Page

Art of BI: OBIEE and Google Maps Integration

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




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 EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
code

Recover a Table from an RMAN Backup in an Oracle 12c

This blog post will is to show a table restore for one table in a container database

Megan Elphingstone | February 2, 2017

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