Friday Mar 23, 2012

Updating a database connection password using a script

An interesting customer requirement that I thought was worthy of sharing today. Thanks to James for the requirement and Bryan for the proposed solution and me for testing the solution and proving it works :0)

A customers implementation of Sarbanes Oxley requires them to change all database account passwords every 90 days. This is scripted leveraging shell scripts today for most of their environments. But how can they manage the BI Publisher connections?

Now, the customer is running 11g and therefore using weblogic on the middle tier, which is the first clue to Bryans proposed solution. To paraphrase and embellish Bryan's solution a little; why not use a JNDI connection from BIP to the database. Then employ the web logic scripting engine to make updates to the JNDI as needed? BIP is completely uninvolved and with a little 'timing' users will be completely unaware of the password updates i.e. change the password when reports are not being executed.

Perfect! James immediately tracked down the WLST script that could be used here, http://middlewaremagic.com/weblogic/?p=4261 (thanks Ravish)

Now it was just a case of testing the theory. Some steps:

  1. Create the JNDI connection in WLS
  2. Create the JNDI connection in BI Publisher pointing to the WLS connection
  3. Build new data models using or re-point data sources to use the JNDI connection.
  4. Create the WLST script to update the WLS JNDI password as needed.
  5. Test!

Some details.

  1. Creating the JNDI connection in web logic is pretty straightforward.
    1. Log into hte console and look for Data Sources under the Services section of the home page and click it
    2. Click New >> Generic Datasource
    3. Give the connection a name. For the JNDI name, prefix it with 'jdbc/' so I have 'jdbc/localdb' - this name is important you'll need it on the BIP side.
      Select your db type - this will influence the drivers and information needed on the next page. Being a company man, Im using an Oracle db.
      Click Next
    4. Select the driver of choice, theres lots I know, you can read about them I just chose 'Oracle's Driver (Thin) for Instance connections; Versions 9.0.1 and later'
      Click Next >> Next
    5. Fill out the db name (SID), server, port, username to connect and password >> Next
    6. Test the config to ensure you can connect. >> Next
    7. Now you need to deploy the connection to your BI server, select it and click Next.
      You're done with the JNDI config.
  2. Creating the JNDI connection on the Publisher side is covered here. Just remember to the connection name you created in WLS e.g. 'jdbc/localdb'
  3. Not gonna tell you how to do this, go read the user guide :0) Suffice to say, it works.
  4. This requires a little reading around the subject to understand the scripting engine and how to execute scripts. Nicely covered here. However a bit of googlin' and I found an even easier way of running the script.

    ${ServerHome}/common/bin/wlst.sh updatepwd.py

Where updatepwd.py is my script file, it can be in another directory. As part of the wlst.sh script your environment is set up for you so its very simple to execute.

The nitty gritty:

  1. Need to take Ravish's script above and create a file with a .py extension.
  2. Its going to need some modification, as he explains on the web page, to make it work in your environment. I played around with it for a while but kept running into errors.
    The script as is, tries to loop through all of your connections and modify the user and passwords for each. Not quite what we are looking for. Remember our requirement is to just update the password for a given connection.

    I also found another issue with the script. WLS 10.x does not allow updates to passwords using clear type ie un-encrypted text while the server is in production mode. Its a bit much to set it back to developer mode bounce it, change the passwords and then bounce and then change back to production and bounce again.
    After lots of messing about I finally came up with the following:

#############################################################################
#
#  Update password for JNDI connections
#
#############################################################################



print("*** Trying to Connect.... *****")
connect('weblogic','welcome1','t3://localhost:7001')
print("*** Connected *****")
edit()
startEdit()


print ("*** Encrypt the password ***")
en = encrypt('hr')
print "Encrypted pwd: ", en

print ("*** Changing pwd for LocalDB ***")
dsName = 'LocalDB'

print 'Changing Password for DataSource ', dsName
cd('/JDBCSystemResources/'+dsName+'/JDBCResource/'+dsName+'/JDBCDriverParams/'+dsName)
set('PasswordEncrypted',en)

save()
activate()


Its pretty simple and you can expand on it to loop through the data sources and change each as needed. I have hardcoded the password into the file but you can pass it as a parameter as needed using the properties file method. Im not going to get into the detail of that here but its covered with an example here.

Couple of points to note:
1. The change to the password requires a server bounce to get the changes picked up. You can add that to the shell script you will use to call the script above.
2. The script above needs to be run from the MW_HOME\user_projects\domains\bifoundation_domain directory to get the encryption libraries set correctly. My command to run the whole script was:

d:\oracle\bi_mw\wlserver_10.3\common\bin\wlst.cmd updatepwd.py

- where wlst.cmd is the scripting command line and updatepwd.py was my update password script above.

I have not quite spoon fed everything you need to make it a robust script but at least you know you can do it and you can work out the rest I think :0)

Wednesday Feb 29, 2012

Conditional Charting II

A follow up post on yesterdays efforts. After pinging a few colleagues Klaus came up with a much much neater approach that appeals to my sense of easy, straightforward and neat, when it comes to code anyway. A by product of the approach, it can handle, none, one or multiple conditonal bars.

So, no variables ... a big plus, some chart xml editing, not quite so good but the benefits far outweigh the costs. Its a case of digging into the chart code and maybe Klaus (who is in charge of the template builders) will one day, provide this conditionality via the chart dialogs ... pleeeease Klaus :0)

Heres the relevant snippet of the chart code:

<Graph seriesEffect="SE_AUTO_GRADIENT">
 <LegendArea visible="false" />
 <ExceptionalRisers>
  <xsl:for-each select=".//Row"  xmlns:xsl="
http://www.w3.org/1999/XSL/Transform">
  <xsl:if test="number(number(.//Value) div number(.//Target))&lt; number($pLim)">
   <ExceptionalRiser series="0" group="{position()-1}" fill border/> 
  </xsl:if>
  </xsl:for-each>
 </ExceptionalRisers>
 <LocalGridData>
...
</Graph> 

We essentially, build the multiple ExceptionalRiser entries in the graph code itself rather than try and build out a concatentated string variable. The string approach was my first thought but 1. XSL does not allow XML strings to be built dynamically and 2. the chart does not break if there are no conditional columns to be rendered.
Dealing with the code a line at a time
  <xsl:for-each select=".//Row"  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
Loop over the data as we did before
  <xsl:if test="number(number(.//Value) div number(.//Target))&lt; number($pLim)">
Look for percentages less than a specified value. I externalized this value into a variable (pLim) to make things easier to test, you can play with it in the template.
   <ExceptionalRiser series="0" group="{position()-1}" fill border/> 
If we find a row of data that meets the criteria we create an ExceptionalRiser entry and use the current record number (position()) minus 1 because out bars are numbered from zero.
Then we just close out the if, the for-each and we're done.
If the if statement does not return true at all i.e. no bars need to be colored; the chart just renders as normal, nice!

Klaus took his example a little further than mine. The one thing that my approach suffers from is that it requires the engine to loop over the data twice for the chart. Once to search for ExceptionalRisers and again to render the chart itself. Klaus took an aproach to create a variable to hold the chart data first and then  run the riser code and chart rendering against that. Check that out in the second template (ConditionalChart2-1.rtf) please note, that its an 11g template and will need the 11g plugin to see it running. Both available and with sample datasets here.

You can now conditionally color your bar charts, horizontal or vertical. I have tried the same with pie charts but no banana so I'm assuming its only going to work for bar charts. I have tested on 10.1.3.4.x and 11g releases. 

Wednesday Dec 07, 2011

Excel 2007 Warnings!

As many of you have found out, everything is not as it seems with the Excel output that gets generated from an RTF template. It has non-Excel like limitations and the files are quite big. Those of you using Excel 2007 will have noticed another niggle. When you open an 'Excel' output you get a warning along the lines of:

The file you are trying to open, '*.XLS', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

This is down to BIP actually generating HTML and setting the mime type and extension to get Excel to open it. This is the cause of the limitations and the cause of the warnings. The limitations can be addressed by using an Excel template more on those in the user docs and elsewhere in this blog. Addressing the warning is going to require a modification to your users' registries.

Huge thanks to Abhishek Gupta for digging up a note from Oracle Support on it and thanks to whoever wrote the original. The note number is 1077728.1. I have uploaded Abhishek's snippet form the note here.

Update: Leslie has reminded me; for she is my Jimney Cricket to my Pinocchio, constantly prodding my conscious :0)
As an additional option in 11.1.1.5 Excel 2007 is a new output type from RTF that generates .xlsx.
Excel 2007 is a new output type available for reports. When selected, BI Publisher generates the output in Excel XML format (.xlsx). If you have Excel 2007 or later installed, this option provides the best preservation of layout and formatting.

As noted in the comments, the Excel templates generate binary xls output not xlsx format and therefore have xls limitations in their abilities.

Thursday Dec 01, 2011

Dynamic Content using Sub Templates

I have written about sub templates in the past on a few occasions; the principle behind them is pretty simple. If you have common report components that can be shared across reports; be they blocks of text like standard contract clauses or maybe some common calculation or function, drop them into a sub template and share the love. Develop once, use everywhere!
A colleague was recently tasked with conditionally bringing into a report output, paragraphs of static text based on some user preferences. That’s an ideal candidate for a sub template approach; drop all of the paragraphs in to an RTF subtemplate and then just conditionally pull them in based on some boolean expressions.
You might, quite naturally think about conditionally importing a series of sub templates rather than defining one, with all the content. However, XSL does not allow the conditional import of sub templates so you must take the single template approach. You can of course, import multiple sub templates if you have a lot of content to bring in but in most cases I would expect a single sub template will suffice.

BIP does need to know what those paragraphs need to be for each user whether that’s as a set of parameter values or a data element in the incoming data set. For this example I have used both approaches and they work all flavors of BIP. Implementation of the sub template onto the servers is going to be a little different but the main principle is the same. I have mercilessly ripped out a nice graphic from Leslie’s (doc writer extraordinaire) documentation.



This is for the 11g version that supports loading sub templates into the report catalog as objects.  They can then be referenced in your main template using the import statement:

<?import:xdoxsl:///subtemplatefolder/subtemplatename.xsb?>

The subtemplate folder is going to be from the /SharedFolders  or /My Folders root. For instance, I have a sub template ‘paragraphs’ loaded into a ‘test’ folder under  Shared Folders. The import statement in my main template is ‘<?import:xdoxsl:///Test/ParaSubTemplate.xsb?>’

Update from Leslie

For those of you testing using your own My Folder area. The syn tax is

<?import:xdoxsl:///~username/path to subtemplate.xsb?> where username is your user name. For example: <?import:xdoxsl:///~tdexter/Subtemplates/Template1.xsb?>

Recommend you move them into the shared folder area in production.

For 10g you will either need to drop them into an accessible directory and use the file URI or mount them into the web server directory structure and access them via an http URI. I normally mount them in a directory under the ‘xmlpserver’ directory e.g J2EE_HOME\applications\xmlpserver\xmlpserver\subtemplates, a template is then accessible via the URI ‘http://server:port/subtemplates/template.rtf’

Make sure you set the Allow External References property to true for the report so that the sub template can be accessed.



The actual content of the sub template is pretty straight forward. It’s a series of paragraphs bounded by the ‘template’ command e.g.

<?template:para1?>
…
…
<?end template?>
<?template:para2?>
…
…
<?end template?>
<?template:para3?>
…
…
<?end template?>
Now we have the dynamic content defined it’s a case of conditionally bringing it into the main template. For this example I have demonstrated two approaches; both rely on the required paragraph information to be in the main dataset:
1.    Using parameters to allow the user to select the appropriate paragraphs to be brought in. This means creating the parameters and ensuring that you have set the property on the data model to include the parameter values in the XML result set.

Once that’s done its just a simple case of using id statements to check if a given paragraph should be included:

<?if:.//PARA1='1'?><?call:para1?><?end if?>


This assumes my parameter is called PARA1 and that a ‘1’ means include it, it could easily be a ‘Y’ or ‘True’ value, you are just testing for it.


2.    Including a value in the data to define what paragraphs should be included. If you have stored what paragraphs should be included for a given entity i.e. customer, supplier, employee, etc. Then you can extract those values into the data set and test for them. For this demo I have a 5 character set of ‘1’s and ‘0’s to represent the paragraphs that need to be included e.g. 10110. I just use a substring command to find out if a particular paragraph needs to be included.

<?if:substring(.//PARAS,1,1)='1'?><?call:para1?><?end if?>


Where PARAS is the element holding the ‘1’s and ‘0’s string to be parsed.
You can of course use some other means of marking whether a paragraph needs to be included or not. It’s just a case of parsing out the values with a substring or similar command.
You should be able to generate dynamic content such as this:


 Notice that I have rendered the code so that I can see that the paragraphs are coming in i.e 10001 means we get paragraphs 1 and 5. I have built out demo files for the second method, available here.

Wednesday Oct 12, 2011

BIP and Mapviewer Mash Up IV

Dang folks, we're on part four already, things start to get really groovy now.
If you're jumping in at part four, here are the previous episodes:

The current approach to get the maps into the output using encoded concatenated URL strings works but if you need to make changes to the map request call it gets fiddly and annoying quite quickly and if you need to drop another map into another output you'll need to embed the code into the second RTF template. What if we could simplify all of this and create a 'map request broker' that sat between the template and the map server? Even better if that broker could handle any map request and support as many layout templates that I want. I have just the solution built and ready to roll.

Of course all three components might be running on the same physical server inside the same weblogic domain. Its a pretty simple concept and it makes life so much easier when embedding a map into an output. The servlet is called from the RTF template using a URL that is constructed on the fly; we can also pass parameters on the URL to influence the map. For my example those parameters include the mapviewer server and port (to make the template portable) the map title, what needs to be mapped and the map request file to be used. The servlet receives the request grabs the parameters off the URL. It then reads the map request files and substitutes the appropriate variable values and then makes the call to mapviewer requesting a map. Once it receives the map it streams the image back to the template for rendering by BI Publisher. No messy encoded URL's minimal concatenation in the template to create the servlet URL and I have fine control over the map request. Best of all any template can call the same servlet, we just need the base map request on the server.

The Breakdown

There are three components to consider, what's in the RTF template the servlet code and the map request. Lets look at the map request first.

<?xml version="1.0" encoding="UTF-8"?>
<map_request title="param1" 
 basemap="world_map" 
 datasource = "obiee_navteq_sample" 
 width="640" 
 height="480" 
 bgcolor="#a6cae0" 
 antialiase="false" 
 format="param2">
 <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, param3 
    from obiee_state 
    where iso_country_code='USA'</jdbc_query>
  </theme>
 </themes>
</map_request>

Its just a well formed XML file. This has loads going on and to find out more you're going to have to hit the books - the mapviewer documentation is here - check out the XML API section for some samples. I was playing with it last night and found that the 'size' attribute under the 'center' element controls the zoom level ... go figure. But combining that with the 'coordinates' value under geoFeature and some nifty LOVs in your BIP report and folks can move around a map and zoom in and out. More on that later.

 Testing your map requests is simple enough inside the mapviewer web console http://server:port/mapviewer >> Requests. Just copy, paste and submit.

The other features of note are my parameters highlighted in blue. Before I pass this request on to the mapviewer server I do a search and replace on the 3 parameters. You'll see that in the servlet code. Remember, this XML is not hard coded into the servlet, its a separate XML file in a 'resource' directory that is read by the servlet. Taking this approach I can re-use the servlet for multiple map requests.

Next, the servlet code, ready for my awesome java skills? Read on ...

package oracle.bipmapper;

//Read-write libs
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
//Handle the URL call
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
//Servlet libs
import javax.servlet.*;
import javax.servlet.http.*;


public class bipmap extends HttpServlet {
    private String CONTENT_TYPE = "";

    public void init(ServletConfig config) throws ServletException {
        super.init(config);
    }

    public void doGet(HttpServletRequest request,
                      HttpServletResponse response) throws ServletException,
                                                           IOException {

        doPost(request, response);
    }

    public void doPost(HttpServletRequest request,
                       HttpServletResponse response) throws ServletException,
                                                            IOException {

        ((1)) - Fetch the URL params. Check that we have values, if not, assign defaults.
        String measure1 =
            (request.getParameter("a1").toString() == "") ? "sqkm" :
            request.getParameter("a1");
        String server =
            (request.getParameter("serv").toString() == "") ? "75.101.156.237" :
            request.getParameter("serv");
        String port =
            (request.getParameter("port").toString() == "") ? "9704" :
            request.getParameter("port");
        String title =
            (request.getParameter("title").toString() == "") ? "Default Title" :
            request.getParameter("title");
        String format =
            (request.getParameter("format").toString() == "") ? "PNG_STREAM" :
            request.getParameter("format");
        String filename =
            (request.getParameter("file").toString() == "") ? "mapreq.xml" :
            request.getParameter("file") + ".xml";

        // Needed objects for the mapviewer call
        HttpURLConnection huc = null;
        URL mapAddress = null;
        ((2)) Set the content format based on the format parameter value
        // The else value is just text, thats for the SVG support
        if (format == "PNG_STREAM") {
            CONTENT_TYPE = "image/png";
        } else if (format == "JPG_STREAM") {
            CONTENT_TYPE = "image/jpg";
        } else {
            CONTENT_TYPE = "text/javascript";
        }

        response.setContentType(CONTENT_TYPE);

        ((3)) //Find the request file
        ServletContext context = request.getSession().getServletContext();
        String separator = System.getProperty("file.separator");
        final String TMPDIR =
            context.getAttribute("javax.servlet.context.tempdir").toString();
        // With WLS the tempdir drops you into the _WL_user/APP_NAME/RANDOM_DIR/public
        // we need the 'war' directory to find the resource directory
        String reqFile =
            TMPDIR.substring(0, TMPDIR.lastIndexOf(separator)) + "/war/resource/" +
            filename;

        ((4))// Construct the start of the URL map request
        String mapURL =
            "http://" + server + ":" + port + "/mapviewer/omserver?xml_request=";
        ((5)) // Load the XML request file
        String xmlReq = getMapReq(reqFile);
        ((6)) // Look for a replace the parameters in the XML request.
        xmlReq = xmlReq.replace("param1", title);
        xmlReq = xmlReq.replace("param2", format);
        xmlReq = xmlReq.replace("param3", measure1);

        InputStream is = null;

        ((7)) // Fetch the output stream from the mapviewer request
        try {
            mapAddress = new URL(mapURL + URLEncoder.encode(xmlReq));
            huc = (HttpURLConnection)mapAddress.openConnection();
            huc.setDoOutput(true);
            huc.setDoInput(true);
            huc.setUseCaches(false);
            huc.setRequestMethod("POST"); //Use HTTP POST method.
            is = huc.getInputStream();
            // Write the image stream back to the caller ie BIP in the template layer
            OutputStream out = response.getOutputStream();
            byte[] buf = new byte[1024];
            int len = 0;
            while ((len = is.read(buf)) >= 0) {
                out.write(buf, 0, len);
            }
            is.close();
            out.close();
        } catch (Exception e) {
            e.printStackTrace(System.err);
            System.exit(1);
        }
    }


    private static String getMapReq(String fname) throws java.io.IOException {
        ((5.5)) // Fetch the XML request file and load it into a string object
        byte[] buffer = new byte[(int)new File(fname).length()];
        BufferedInputStream f = null;
        try {
            f = new BufferedInputStream(new FileInputStream(fname));
            f.read(buffer);
        } finally {
            if (f != null)
                try {
                    f.close();
                } catch (IOException ignored) {
                }
        }
        return new String(buffer);
    }
}

I have highlighted the important bits:

(1) Standard stuff here, pulling the parameters off the URL and assigning them to string variables. For the un-initiated each entry is an inline if statement.

(2) Im checking the requested output so that I can set the appropriate content type. In my example Im supporting PNG, JPG and SVG. The SVG is handled in the else condition. More on that in another post.

(3) Here I fetch the request XML file. Its in a 'resource' directory in the deployment. This was a bit of a pain, I expected the 'javax.servlet.context.tempdir' to return he root of the web application but in weblogic you end up in a 'public' directory under the root. My resource directory is actually under the 'war' directory under the root so this piece of code:

ServletContext context = request.getSession().getServletContext();
String separator = System.getProperty("file.separator");
final String TMPDIR = context.getAttribute("javax.servlet.context.tempdir").toString();
String reqFile =
            TMPDIR.substring(0, TMPDIR.lastIndexOf(separator)) + "/war/resource/" +
            filename;

does some substringing and concatentation to build a path to the correct directory. Im sure there is an easier way but it works for now. Note the user of the 'separator' string so that the servlet can be deployed to windblows, unix or linux. I had a devil of a time catching that one. I developed on Linux but it would not run on windblows until I grabbed the appropriate separator ... grrr!

(4) Starting to construct the URL request for the XML map request

(5) Fetching the map request XML from the file in the resource directory

  (5.5) This is the function to read the map request file and load it int a string variable

(6) A simple replace for each of the parameters created in the map request. We could have gotten fancier here with an XML parser but its not a huge string and there are three replacements to do. Its not a slow process.

(7) Now we construct the full http request for the map. Note the encoding of the xml request string, thats a must. Once we get the result we just have the servlet write it back to the response object for BIP to render.

Nothing earth shattering, could it be better, sure, if you fail to pass enough parameters you get an ugly null pointer exception which could be handled better but just make sure you pass enough parameters :0)

Finally the RTF template, its a case of declaring parameters for the report parameters and then building the URL request to the servlet. You can see the fields in the graphic on the right. The only ones of note are:

  • The file parameter, allows you to point to any deployed request file
  • The mURL that constructs the URL. Its just a big long concat with the name|value pairs for the servlet parameters.

Then just use the external-graphic place holder as we have before to reference the mURL parameter. The curly braces {} get BIP to pre-process the value before processing the graphic.

Finally ...

Thats it, put the three pieces together et voila, you have a mapping solution that's more manageable and configurable. You can of course increase the number of parameterized values in the xml map request.

I have bundled up the JDeveloper (11.1.1.5) project as it is ... its only really got the servlet and XML request in it. You can just copy and paste into your own project - get the project here. I have also zipped the 11g BIP report with the RTF template (Mapviewer4.rtf) get that here. For 10g'ers just unzip the xdoz file to get at the RTF template.

So, we can now get maps into our BIP outputs, the quality is pretty good but it could be better. Having spent some time digging around in the mapviewer docs I spotted that they support SVG outputs ... more on that next time.

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.

<?param@begin:name;defaultvalue?>

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
("%3Cmap_request%20title%3D%22",$title,
"%20basemap%3D%22world_map%22%20datasource%20%3D%20%22
obiee_navteq_sample%22%20width%3D%22640%22%20height
%3D%22480%22%20bgcolor%3D%22%23a6cae0%22%20antialiase
%3D%22false%22%20format%3D%22JPG_STREAM%22%3E")
     </xsl:param>

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

<?param@begin:mReq;concat($mURL,$pXMLStr,$pMapRq,$pCenterOp,$pGeo,$pCentCl,$pLeg,$pTheme,$pMapRqCl)?>

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

Friday Aug 26, 2011

Bursting and Hairloss

After last week's post on small successes after much pain and misery. Still pulling your hair out over minor things? You should be so lucky to have some to pull. I lost mine years ago, I blame my kids. In fact here's one of my kids unconsciously rubbing it in on a recent'ish diving expedition. He's the one on the right, in case you were wondering who the wrinkled old man was on the left ... yes, that's me :0( I've heard of sucking your gut in for a photograph; I now need to remember to pull the skin tight on the back of my head too :0)

However, I take comfort in the fact that:

1. He's mine
2. Both of his grandfathers and 3 of his great grandfathers are/were follicly challenged.
So he and his brother will be bald as coots by the time they hit their 20s ... ha, join the club boys!

The reason for my hair pulling? Getting bursting working in 11g. Now, Leslie, 'Super Documenter' to the rescue. It's there in black and white, just RTFM Tim! My words, not hers!

My bursting query in 10g for a simple demo starts:

select  d.department_name KEY,
...
...

But this throws an error in 11g along the lines of:

Syntax error: Encountered "KEY" at line 2, column 24

Simple fix and it is documented in the 11g user docs.

select  d.department_name as "KEY",
...
...

Not sure if this is a BIP or database choke, I'm suspecting the db. Anyhoo, change the select statement and my bursting is working again!

Friday Aug 19, 2011

BI Publisher and WebDAV ... done!

A suitable sub title for this post might be, 'Tim, taking a tiny step forward after several days of misery is now a happy camper.' Isn't it amazing in the world of software and development how:

1. What looks to be the simplest thing in the world can trip you up, kick you in the googlies while you're down and generally be a bit of a meany and 

2. Once you have beaten said bully into submission you feel so glad; you stand at your desk and smile a very self congratulatory smile (even cheer, I did) that no one else around you understands. Especially your dog, who is sitting faithfully at your feet and interprets this exuberant display as the signal that he is going to get to go outside and play ball. Sorry, Bandit, I got a blog post to write dude! But I digress ...

By the way, if you are interested, that's Bandit on the left, quite mad of course. Digby, the bear chasing jail bird, center and the fluff puff, fatty cakes on the right, is Hazel. What you can not see is my wife dangling a juicy red steak out of picture to get all three of them to pay attention for just an 1/8 of a second. It's a dogs life in the Dexter household I can tell you!

To the meat of this post and the reason for my misery followed by elation, setting up BIP to send documents to Oracle Universal Content Manager (UCM) using webdav in particular. I should state at this point that here is some documentation on how to set up 'webdav' however its generic. I know Leslie (doc person) will be the first to admit, its probably not enough. I also know that the meat of this post will make it into the official documentation. I shared my success with Leslie this afternoon, she at least understood my happiness and did not want to go out and play ball. At least I don't think she did?
Update:
Having read this back, I would like to publicly apologise to Leslie for intimating that she enjoys going outside and playing ball and any connotations that may have given rise to in your head dear reader!

Of course, it's actually quite simple to set up and my happiness is tinged slightly with a feeling of stupidity. I'm sure even my 10 year nephew could have worked it out in minutes, maybe my brain is finally giving up on me. I take comfort in the fact that the setup, in my  humble opinion lacks a field. Looking at the setup page, I know its small:

There are very few fields to fill:

  • Server Name
  • Host
  • Port
  • Username
  • Password
  • Authentication Type - this needs to be set to 'Basic' for UCM, so I am assured.

Simple right? Thats what I thought too. In fact it is simple, it's knowing what you need to put into said fields to get BIP to talk to UCM is the key.

I have been working with some customers who are using UCM 11.1.1.4 so Im going to base my instructions on 11.1.1.4 but the basics are going to be the same for whatever version you are using. Firstly, you need to know the webdav URI for the server. This is what tripped me up for the longest time. Scouring the intertubes I got to a small post from Oracle's own Kevin Smith stating:

The format for the WebDAV URL has changed in 11g. It is now

   http://server:16200/_dav/cs/idcplg/webdav/

If you are using OHS in front of UCM it will be

   http://server/_dav/cs/idcplg/webdav/

and you will have to add _dav location to themod_wl_ohs.conf file

   <Location /_dav>
   .
   .
   .
   </Location>

This moved me forward a little. I had the server name and the port and that all important, webdav string for my 11g server, '_dav/cs/idcplg/webdav/' For those of you wondering what OHS is, its Oracle HTTP Server, yeah I had to go look that up too! After conversing with Kevin and Kyle (another great blogger) I found that the 16200 port was a bit of a red herring. If you use the default install of UCM, its actually running on port 7001. With that nugget I could move further forward. Now just take a look at the delivery page for a webdav channel at runtime:

Again a bit small, sorry, the fields this time are:

  • Web Folder Server
  • Remote Directory
  • Remote Filename
  • Username
  • Password

Well, looking at my UCM instance and the folders I have, you could reasonably expect to be able to use 'WebCenterSpace-Root/bipublisher' as the remote folder field value, right? Wrong! you also need the '_dav/cs/idcplg/webdav/' string too aaand a leading and trailing slash. So to get my content to by bipublisher folder I need:

 /_dav/cs/idcplg/webdav/WebCenterSpace-Root/bipublisher/

as my remote folder entry. If you're wondering, yes, I am working on some WebCenter-BIP integration at the moment too.

So, yes, your users are going to need to remember to put that 'prefix' string in every time. Hence my observation that we need another field in the server config page. Once you understand how BIP puts the URL string together it all becomes clear:

http://+server+:+port+/remote_folder/+remote_filename in my case
http://owcvm03:7001/ /_dav/cs/idcplg/webdav/WebCenterSpace-Root/bipublisher/1.pdf

UCM is not alone, Sharepoint and other webdav servers also have their own 'prefix' strings. I'll be logging that enhancement on Monday! For Sharepoint customers, some brave soul has already worked out what you need to get BIP posting docs - http://obieegurus.blogspot.com/2009/07/bi-publisher-sharepoint-integration.html

Now I had the correct remote folder string, all was well with the world. I can push content in, as long as the folder was either public or accessible by the user you specify in the username/password fields. Which gets me back to the set up page, whats the username/password there for? That's something Im still playing with and will update this post with results as I find out. But you got the basics, right?

For the file name, of course, you do not necessarily want to be fixed. I have tested all of the dynamic naming support documented here and it all works. The millisecond option that looks like an uppercase 'I' is actually a lower case 'L', go figure.

This solution has been tested with 11.1.1.5 and 10.1.3.4.1.

An Update: As Barb asked in the comments, can this be applied to EBS? Not out of the box but you can use the delivery APIs to send the document via webdav via virtual printer channel attached to the concurrent request. More on a series of virtual channel entries start here.

Dropping docs into any document repository is all well and good. I had a warm fuzzy glow for all of 30seconds when I got this working. However, whats the use of a document in a repository without any meta data to describe it? BIP can not do that right now, at least not out of the box. That particular nut is being cracked and I'll post once I have more info.
For now, I'm off to play with the dogs, Bandit, bring that ball back!


Friday Nov 12, 2010

The Unsung 11g Hero

[Read More]
About

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!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today