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. Your report should look very close to this:
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
// 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');
Code For PostFix Property Section
} 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.