BIP and Mapviewer Mash Up II
By Tim Dexter-Oracle on Sep 29, 2011
Quite some time ago now I wrote the first of what I thought were going to be at least a couple of articles on getting BIP to render maps via Oracle Mapviewer. It was a real HelloWorld example with no 'World' just the 'Hello' bit.
I like to think it was like Kevin Costner's 'Waterworld', a nice idea but poorly executed and a flop at the blog box office but it would have been a perfect map for the movie. I have to admit, I think Dennis Hopper was awesome as the bad dude.
Well, I recently needed to show something more than an area of blue ocean, we needed some land. As you'll see if you go back to the original post, I have dabbled, I know how to render a map view in OBIEE but I knew I needed to lean on our resident map meister David to take this forward. David lives and breathes maps and probably knows the Oracle GIS solutions better than the folks that wrote them.
After a conversation, I was on the right track with the original post, mapviewer has an XML API that is accessible via a URL. Its just a case of building the URL and calling mapviewer and getting the result rendered by BIP. One thing that David noted was to not use the XML data that the template had access to. You can create your set of name/value pairs from the XML and pass it on to the URL. But imagine doing that for even a map showing data across all 50 states of the US or countries across the EU or APAC. It's going to get large very very quickly. The XML API does support passing a query to the mapviewer server for it to execute fetch and format the data into a map. Yep, we're breaking one of Publisher's cardinal rules, going back to the db for more data but the benefits far out weigh the costs. Armed with this information I got going. We have been working with a customer that needs this functionality (Hi Wilson :-) so I had a sample XML request to play with:
<?xml version="1.0" standalone="yes"?> <map_request title="US Renal Disease Rates" basemap="world_map" datasource = "obiee_navteq_sample" width="640" height="480" bgcolor="#a6cae0" antialiase="false" format="PNG_STREAM"> <center size="45"> <geoFeature> <geometricProperty typeName="center"> <Point srsName="SDO:8307"> <coordinates>-96, 34</coordinates> </Point> </geometricProperty> </geoFeature> </center> <legend bgstyle="fill:#ffffff;stroke:#ff0000" profile="MEDIUM" position="SOUTH_WEST"> <column> <entry text="Number of Renal Disease Cases:" /> <entry style="V.POPULATION_COUNTY" tab="1" /> </column> </legend> <themes> <theme name="theme1" min_scale="5.0E7" max_scale="0.0"> <jdbc_query datasource="obiee_navteq_sample" jdbc_srid="8307" spatial_column="geometry" render_style="OBIEE_NAVTEQ:V.POPULATION_COUNTY"> SELECT geometry,sqkm from obiee_state where iso_country_code='USA'</jdbc_query> </theme> </themes> </map_request>
That's a hunk of XML to pass right, there is a lot going on in there. It basically sets the base map, size, center point, themes (or layers) to be added. The format is important, when you hop on over to the mapviewer doc you'll see that the format can take multiple values in our case we need 'XXX_STREAM' where XXX is the image format name e.g. PNG, JPG, etc. If you just request XXX then you get a URL string to the image on the server (that will come in handy in an upcoming post but not here) Finally the query that needs to be executed; this is the important bit for us; it needs to marry a map related column to some measure in this case 'sqkm'. Once the data set is returned, mapviewer can then 'map' the data. Our XML gets us this map to the left. Big prizes for the first one to spot whats wrong with the map? and why? Answers can be placed on the down tube of a shiny new 58cm Cervelo S5 and sent to the usual address.
For those of you that have gone back to part I of this post, you'll remember that we need to encode all of the XML before we can use it. Otherwise BIP gets all upset and reports a problem. When I started on this example I started to encode it all by hand, not a good plan and much shouting at the monitor ensued. Being lazee or smart, take your pick, I jumped on Google to look for some kind soul that had provided some web page that would do the encoding for me. Deepest thanks go out to the owner of http://meyerweb.com/eric/tools/dencoder/ you saved me from pulling out what hair I have left. For someone that has very little hair; I sure do talk about it on this blog a lot; maybe I need to talk to someone about that or get a toup?
Encoding nightmare solved but its going to be a big ugly piece of text to manage if I just encode the lot and assign it to a parameter. I decided to break it up a bit into sections and then use a concat function to bring it all back together.
The individual pieces make the string more manageable in terms of needing to make changes. I would recommend embedding the actual XML string into the template and noting which section belongs to which parameter and then surrounding it with an IF statement to hide it at runtime. The concat generated a big ol URL that I can test in a browser to ensure its going to return the map I want. You'll see in the template that you can test on the desktop too, as long as you have access to the mapviewer server.
Once you have the URL correct its just a case of dropping it into a form field:
At runtime the URL is resolved, called and the map returned to BIP for rendering.
For those of you on 11g (I have tested this on 220.127.116.11 BIP) you can download the complete report here. You just need to upload it and probably change the data connection on the data model. It relies on the obiee_navteq db user that is installed with the BIEE sample app.
For those of you on 10g, heres the template and some sample data to play with.
Next for this series, parameterizing the XML so that users can set various features at runtime. Imagine being able to ask for 'Renal disease cases by state' for one request and then 'Liver disease cases by country' for another using the same report/template.
Following that, after my somewhat 'hit it with a big hammer until it submits' (we're good at that in our house :) approach I have plans for something a bit more sophisticated.