Sunday Oct 09, 2011

BIP and Mapviewer Mash Up III

This is the third installment of the BIP and Mapviewer Mashup, for the previous entries:

BIP and Mapviewer Mash Up I

BIP and Mapviewer Mash Up II

BIP and Mapviewer Mash Up III

Its been a hectic couple of weeks which has included all things mapviewer and integration. I have finally got my piece de resistance in mapping integration working but more on that next time. Its very cool in a geeky BIP, kinda way; my wife was completely fascinated when I told her all about it over dinner last night. Either that, or she has become very adept at nodding and saying 'that's nice honeycakes' at appropriate times. I hope the former but suspect the latter :0(

The next part of this mashup series, I said we would parameterize the map call. That's actually pretty easy to do, just a bit more effort with the parameters and the concat functions.

1. Set up your parameters in the data model. These do not have to be tied to the query. We support 'template ' only parameters. they just all have to be defined in the datamodel. Note the name you give to the parameter(s)

2. In your RTF template you need to declare 'interest' in the parameters. Its the same as the CURRENT_SERVER_URL format.


3. In the fields where you are building the encoded request string you can use the concat function to drop the parameter values into the string. In this case we are changing th map title with a parameter called 'title'

 <xsl:param xdofo:ctx="begin" name="pMapRq">concat

Its not tough but boy is it a bit of a nightmare to manage and keep track of everything in that encoded URL.

As I mentioned in my last post its a good idea to maintain the parts of the URL as un-encoded text in the template surrounded by an 'if:1=2' if statement to keep it hidden at runtime.

Next post, we get to a much more robust, easier to manage and as I mentioned cooler solution ... enter the mapping servlet. It acts as a map request broker between the BIP template and the mapviewer server. The RTF template is not full of encoded URLs but just a simple URL call to the servlet that will call the mapviewer server and stream the image back to BIP for rendering. I have built in a couple of tricks but more on that next time.

Thursday Sep 29, 2011

BIP and Mapviewer Mash Up II

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 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:

<fo:external-graphic src="url({$mReq})"/>

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 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.


Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!


« April 2014