Tuesday May 08, 2012

Port 80 Goodness for Publisher

Do you ever tire of remembering what port BIP is running on when you enter the URL? Ever wish you could just type:

http://tdexter-lap/xmlpserver

and be done with it? Well its really not that hard to do. Its documented how to front the web logic server with a web server whether that be Microsoft's IIS or maybe Apache. Well with my success at getting BIP on to my Ubuntu laptop I thought I would give Apache a whirl being as I dont think IIS would run on my machine :)

I did start off with the docs but as usual Googling got me faster, more concise instructions with some help if things went awry. Of course, BIP has nothing to do with the configuration other than to tell Apache where the xmlpserver app is running and on what port. So its really just an exercise in installing Apache and then configuring it.

Rather than re-invent the wheel, check out Vikas' blog post here - http://vikashazrati.wordpress.com/2009/10/10/apache-weblogic-ubuntu/.  Yes, its specific to Ubuntu but can be applied to any flavor of Linux in this case. For you 'windowers' out there, this will get you on the road to non port typing bliss, http://docs.oracle.com/cd/E21764_01/web.1111/e14395/toc.htm.

For linux folks, just follow Vikas' excellent post and just replace the references to the 'medrec' application with 'xmlpserver' and change the port from 7011 to 7001. You can of course add the /console app to the list as well.

I have covered web logic server installs ie 11.x. If you re running earlier versions on OC4J the Oracle AS documentation is pretty good on how to get BIP on port 80.

In no time you'll have forgotten that BIP even runs on a specific port.

Monday May 07, 2012

Running BIP on Ubuntu!

I am a complete Ubuntu (Linux) convert ... my work laptop runs 64 bit Ubuntu and Im very happy with it. Yes I do still need windblows for a few things and for testing but I would say 80-90% of my time is spent in Ubuntu. Not so good is Oracle's support for Ubuntu, some of that I guess, is around Ubuntu being seen primarily for the desktop and not for servers and also Ubuntu's interesting at times, implementation of Linux.

Case in point, the recent release of the BI Publisher Trial Edition (see here) we state support for Linux but to be fair, we narrow that down to Oracle Linux Redhat and SLES. OK, but as its so lightweight it would be great to be able to fire it up on my laptop without the need for a winddoze VM. Trying the installer or more appropriately the 'configurer' it falls at the first hurdle complaining about the script.
Thats easily fixed, the pain point in question is the -s switch for the password entry for the admin user you create. Just open the configure.sh and remove the -s references (x2).

read -s -p "Enter a password for the Administrator user: " WLS_ADMIN_PWD
echo ""
read -s -p "Confirm password: " WLS_ADMIN_PWD2


Rerunning and it falls over a little further into the configuration.
At this point I want to thank Juergen in Germany for digging into the sub scripts and figuring out what to do. I have to admit, I had put the investigation on to the back burner while I have been traveling. Juergen found the problem in the ../server/wls/configure.sh file. It checks for the MW_HOME and JAVA_HOME environment variables and even if you have set them it still complains. The easiest fix is to just comment out the checks for the variables.

# Users must set $MW_HOME variable
if [[ -z "$MW_HOME" || ! -d $MW_HOME ||  ! "$(ls -A $MW_HOME)" ]]; then
  echo "ERROR: You must set MW_HOME and it must point to a directory".
  echo "       where an installation of WebLogic exists. Ensure you point"
  echo "       this variable to the extract location of the zip distribution."
  exit 1;
fi

# Users must set $JAVA_HOME variable if [[ -z $JAVA_HOME || ! -d "${JAVA_HOME}/bin" ]]; then   echo "ERROR: You must set JAVA_HOME and point it to a valid location"   echo "       of where your JDK has been installed"   exit 1; fi

Just comment out the red text with a # at the beginning of the line.

Now when you run the main configureBIP script it completes and successfully starts the WLS server and consequently BIP. Its real fast to start and fast to run and best of all I have another reason I can avoid windblows!



Yes I have boring windows colors but what the heck, its not windows!


Wednesday May 02, 2012

BI Publisher has hit the gym

As Pieter reminded me this morning a sleek, slim, new installer is available for BIP 11g. The Oracle BI Publisher 11g Trial Edition has been released on OTN. In comparison to the fatty monster that is, OBIEE Suite, tipping the scales at more than 7Gb; BIP Trial Ed is a svelte, muscular beauty a tad under 600 Mb. A fully functional, free server provided for evaluation purposes. It is not intended for production deployments thou. For that you have to go start the BIEE download and go for a cup of joe.

The installer includes the Derby database and BIP is configured out of the box with a bunch of new demos and samples so just download, unpack, run the config script and you're ready to roll. You can of course hook it up to your own data sources and get building. Check the links below.

http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index.html

BI Publisher Trial Edition 11g for Windows 64 bit (587 MB)

BI Publisher Trial Edition 11g for Windows 32 bit (582 MB)

Windows installers include Java SE 6 Runtime Environment

BI Publisher Trial Edition 11g for Linux 32 and 64 bit
(533 MB)

Linux installer requires Java SE 6 Runtime Environment (download)

Install Guide | Quick Start Guide | Getting Started Tutorial

BIG NOTICE ... right here, read this!

BI Publisher Trial Edition is not supported for production deployments. 
You can save any reports developed on the Trial Edition and upload to your production environment.

Tuesday May 01, 2012

Fusion Middleware Innovation Awards 2012

Is your organization using Oracle BI  to deliver unique business value? If so, submit a nomination today for the Oracle Fusion Middleware Innovation Awards. These awards honor customers for their cutting-edge solutions using Oracle Fusion Middleware. Winners are selected based on the uniqueness of their business case, business benefits, level of impact relative to the size of the organization, complexity and magnitude of implementation, and the originality of architecture. The awards will be presented during Oracle OpenWorld 2012 (September 30-October 4) in San Francisco.

Thats the official blurb out of the way, I know you are out there.. I have seen and heard about some very cool uses of Oracle BI. I'm not limiting things to BI Publisher here, nor even just Oracle BI but any use of Oracle middleware. Maybe you have integrated BIP into your ERP solution, or are allowing users to run reports via web center or retrieve BIP documents from a content management system. Maybe, you have built a kick ass billing system that integrates with BIP for invoice generation and OBIEE to do the ad-hoc reporting on payments, debtors, etc.

Anything qualifies, just get yourself over to the Awards site and fill out an application. You have until July 17th 2012 to get your solution into the spotlight. Good luck!

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)

Monday Mar 19, 2012

Advanced Charts Part I

Yeeeep! Another series looms ... this one could stretch out a bit and more options become available. Ever needed to generate something similar to these?





Beyond what BIP can provide today but there are a few options; one from Oracle and R, now out in the wild and another out there from JFreeChart, open source and therefore almost free. Of course Google is ever present and they have been extending their chart support. I blogged the How for Google charts a while back here. Different ways to integrate but they can all help to close the charting gap for you.

Wednesday Feb 22, 2012

11.1.1.6 is out ...

I know, I'm a day or two late ... blame my travel schedule. Get 11.1.1.6 here.

Some highlights

  • Improved Create Report Flow - really neat UI to walk you through the report creation process. Old pros dont worry you can circumvent it.
  • Enhancements to Parameter Display Options
    • Support for radio button and check box display of values
    • New options for placement of parameters in the report viewer
    • New report viewer toolbar button to show or hide parameters
    • Search added to menus
    • Option to remove the Apply button
  • Support for HTML Formatting in Data - out of the box support to automatically format your XHTML data inside your BIP output
  • Support for PDF/A and PDF/X Output
  • Edit Scheduled Jobs - what it says on the tin
  • Event-Driven Schedules - ohhhh yeah! This is a great step for creating triggers to automatically execute a BIP job. Looking forward to playing with this.

 For more details check out the New Features sections in the 11.1.1.6 documents here.

Tuesday Feb 14, 2012

Debugging BIP ... its getting easier

Long time friend of BIP and bippers everywhere, Ike Wiggins, has worked on and shared another nugget of BIP gold. An easier way to set up debugging on your RTF templates while previewing output using the MSWord plugin. No more deploying your template to test and debug; do it from the comfort of an armchair with a glass of your favorite tipple ... alright, it's not that easy, but a lot easier.

Head on over to Ike's little ol place on the net and find out the 'how' -  http://bipublisher.blogspot.com/2012/02/bi-publisher-xdotop-msword-debugging.html.  It's worth spending some time over there having a dig around, there's gold in dem der posts I tell ya!

Saturday Jan 28, 2012

Oracle Policy Automation and BI Publisher

Yep, found another integration with BIP. I get asked about Oracle Policy Automation quite a lot; I do not profess to know much; enough to be dangerous I guess. However, I was not aware that they had been working with BIP to help automate the generation of documents from the system.

For the uninitiated, here's the blurb care of :

Oracle Policy Modeling is a Windows desktop application for transforming legislation and policy documents into executable business rules. Rules are written in Word and Excel documents using phrases in languages such as English, Chinese and French. These rule documents can be shared amongst business and information technology stakeholders, and commentary can be added into the documents without affecting the structure of the rules themselves. Other features of Oracle Policy Modeling include integrated test case execution and debugging capabilities, and the definition of interviews for interactive rule-based assessments. Interview screen order and branching logic can be defined using visual flow diagrams.

In other words, a cool piece of technology, but tell us Tim how is it used? Well, it has been publicly deployed within several government web-sites. Australia's Department of Immigration uses it for visitors to check their eligibility for visas. The UK uses it for their Employment Status Indicator assessment tool, the UK governments online portal for businesses also uses OPA for over 60 interactive tools while the United States uses the software for guidance on tax law. The last one might be pertinent for me this weekend as W-2s have arrived and I have a night of misery trying to get a refund.

How does BIP fit in? Pictures are worth a thousands words and Fiona from the development team down under has recorded a great YouTube session explaining the integration and how to take advantage of it. I know I'm becoming more American cos I found it a little hard to place her accent at first, not to be rude Fiona but was it Brit, Aussie or maybe New Zealand? I plumped for Aussie, 10 points to me ... phew!

Direct link to the OPA YouTube channel - some other great vids on use cases and features.

You can find the OPA Homepage here lots of info on the tool and its possible applications.

Friday Dec 23, 2011

Siebel Tips and Tricks

Good tidings for the season to all of you. Its been erratic I know but we have stumbled along this year ...

I have been working with Eric, a Siebel sales consultant whiz on the BIP integration. He shared some really useful tips with a Siebel-BIP customer yesterday which I thought were well worth sharing.

Date Formatting

Firstly, some help with formatting dates when you are using a Siebel integration objects to generate the data. Siebel IOs do not generate the XSD date format that BIP needs to let you format dates to your hearts content.  Attached are a couple files that provide some examples on how to manipulate dates to get the desired formats. Some are easy to get working and some need a little effort.

MSWord Plugin Testing

Siebel development have shipped  some Siebel-BIP functions that are deployed to the server. However, to be able to test them locally on your desktop you need to get the java libraries into your classpath prior to starting MSWord. Here is the code Eric uses for starting Word with the Siebel classes in a batch file.  It assumes that the jar files are available locally on the client machine at C:\811DQSSIA\Client\CLASSES.  This will make the classes used by some Siebel report templates available to test directly in Word.  I have two versions, one for Word 2003 and one for Word 2007.

Word 2003:

echo %1

set _JAVA_OPTIONS=-Xbootclasspath/a:C:\811DQSSIA\Client\CLASSES\SiebelXMLP.jar;C:\811DQSSIA\Client\CLASSES\XMLP.jar;C:\811DQSSIA\Client\CLASSES\Siebel.jar;C:\811DQSSIA\Client\CLASSES\XSLFunctions.jar;C:\811DQSSIA\Client\CLASSES\SiebelCustomXMLP.jar;C:\811DQSSIA\Client\CLASSES\SiebelCustomXMLP_SIA.jar;

"C:\Program Files\Microsoft Office\Office12\Winword.exe" %1

Word 2007:

echo %1

set _JAVA_OPTIONS=-Xbootclasspath/a:C:\811DQSSIA\Client\CLASSES\SiebelXMLP.jar;C:\811DQSSIA\Client\CLASSES\XMLP.jar;C:\811DQSSIA\Client\CLASSES\Siebel.jar;C:\811DQSSIA\Client\CLASSES\XSLFunctions.jar;C:\811DQSSIA\Client\CLASSES\SiebelCustomXMLP.jar;C:\811DQSSIA\Client\CLASSES\SiebelCustomXMLP_SIA.jar;

"C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft Office\Microsoft Office Word 2007.lnk" %1

 Happy Holidays!

Tuesday Dec 06, 2011

11.1.1.5 December 2011 Update Available

Latest and greatest from the boys and girls at the coal face for December. Bug fixes and some interesting enhancements that I hope to dig into to here soon.

Search for patch 13366758: UPDATE FOR BI PUBLISHER ENTERPRISE 11.1.1.5.0 DECEMBER 2011 on Oracle Support. Make sure you check out the readme for patch installation instructions.

 9102877 - QA: QUERY BUILDER GENERATES INCORRECT SQL FOR MANY DBS
 10402890 - SETTINGS FROM "DELIVERY CONFIGURATION" PAGE NOT BEING HONORED WHEN REPORTS RUN
 11725468 - ER: RTE HTML SUPPORT IN DATA XML
 11828672 - QA: INCORRECT INACTIVE WARNING MESSAGE
 11835845 - 11G SQL DATA SET CREATION ORA-01756 ERROR BECAUSE COLON IS READ AS BIND VARIABLE
 11850902 - SHARE REPORT LINK "NO PARAMETERS" AND "DOCUMENT ONLY" RENDER INCORRECTLY
 11870449 - BI PUBLISHER XDOXSLT:SDIV() FUNCTION RESULTS NOT CORRECT
 12312539 - QA: REPORT VIEWER SCREEN FREEZES FOR A FLEX REPORT WHEN SESSION EXPIRES
 12376386 - REMOVETEMPLATEFORREPORT API DOESN'T REMOVE TEMPLATE
 12405904 - CANNOT DEPLOY BIP WHEN DEPLOYMENT PATH INCLUDES SPACE CHARACTERS
 12406034 - CANNOT USE JAVA DB - DERBY - FOR SCHEDULER DATABASE
 12588342 - DELETEOBJECT RANDOM FAILURE
 12595931 - WHEN PRINTING CHARTS TO PDF, NUMBERS BECOME ARABIC NUMBERS
 12615821 - MISSING MEMBERS ON THE XML GENERATED BY BIP
 12634030 - RE-PACKAGING MLR#11893189 TO XDOPARSER11G.JAR
 12646493 - ORA-22902 CURSOR EXPRESSION NOT ALLOWED
 12666066 - ER: EXCEL 2007 SUPPORT IN REST API
 12705244 - JOINED MDX DATA SETS DO NOT PRODUCE CORRECT DATA - DATA MISSING
 12726163 - NUMBER OF SESSIONS INCREASES AFTER RUNNING SCHEDULED JOBS
 12737670 - GROUP BY FOR MDX DATA SET MISSING IN 11.1.1.5 - WAS PRESENT IN 11.1.1.3
 12741858 - BI ASNWERS ANALYSIS IMPLEMENTING CASE WHEN STATEMENTS CAN NOT BE CONSUMED IN BIP
 12750192 - ER: GENERATE NATIVE COMBINATION DUAL Y GRAPH IN PPTX OUTPUT FROM RTF AND XPT
 12766932 - VERTICAL DUAL Y COMBO CHART MISSING SETTINGS TO ASSIGN VALUES TO AXIS
 12775400 - DBFW:GENERATING IN XLXS OUTPUT IS THROWING AN ERROR
 12822514 - SQL WITH CLAUSE - DATASET DOESN'T WORK (AND DIAGNOSTIC ISN'T HELPFUL)
 12828845 - ISSUE WITH XDO_USER_NAME PARAMETER ON BIP 11.1.1.5.0
 12835431 - INTRANSIT VALUATION REPORT QUERY GIVING ERROR IN EDITOR
 12847306 - BI PUBLISHER DATA SET - WON'T SAVE
 12872831 - EXCEL TEMPLATES GENERATING INCORRECT RESULTS FROM NESTED GROUPS
 12879801 - ALLOW SAVING DATA MODEL WITH SPECIAL CHARACTER
 12945367 - NOT ABLE TO EDIT DATA MODEL WHEN LINKS EXIT AMONG DATA SETS
 12945475 - IE: NOT ABLE TO EDIT DATA MODEL WITH MANY DATA SET AND LINKS
 12956901 - IN SQL SERVER CLOB IN DATA MODEL UI MARKED AS STRING DATA TYPE
 12956915 - INCORRECT DATA WHEN PROCESSING OF CLOB COLUMNS FOR SQLSERVER
 12956934 - DATA MODEL SHOULD RESPECT CASE SENSITIVITY FOR COLUMN AND TABLE NAMES
 12972559 - XML DATA FILE TRUNCATED
 12972852 - DYNAMIC IMAGES BROKEN FOR FO/PDF
 12989467 - NEED TO CONVERT DOCUMENT FORMAT "EXCEL" TO "EXCEL2000"
 13005659 - PSR:PERF:BIP:POOR PERF ACCESSING CATALOG FOLDER IN BIP 11G WITH SECURITY ENABLED 
 13007341 - DISPLAY ISSUES IN PARAMETER TAB OF OBI PUBLISHER 11G
 13095050 - STRESS: OUTOFMEMORY ERRORS WITH VIEW REPORT STRESS
 13100021 - BI PUBLISHER: QUERY BUILDER RESULTS RETURN COLUMN TYPE (APPEARS) INSTEAD VALUE
 13100220 - HOW TO USE 11.1.1.5 WITH THE IDENTITY ASSERTER
 13250009 - FOR METADATA LOGICAL SQL BIND WITH DUMMY VALUES INSTEAD OF NULL
 13321449 - RESOURCELIMITEXCEPTION DURING ANY OPS THAT USE JNDI DATA SOURCE
 13346737 - MULTIPLE VALUES IN A DASHBOARD PROMPT DOESN'T WORK WITH BI PUBLISHER INTEGRATED
 13351305 - QUERY BUILDER DOES NOT BUILD CORRECT SYNTAX FOR SQL SERVER
 13421264 - ADDING PARAMETER TO SQL BI SERVER DATA SET CORRUPTS DATA MODEL

 

Enjoy!

Friday Nov 18, 2011

UPDATE FOR BI PUBLISHER ENTERPRISE 10.1.3.4.2 NOVEMBER 2011

It's Friday, that means its patch release time. Why do we do this to ourselves, 'we'll release on Friday!' It might 11.59 on Friday but by golly we'll have released on Friday. I can remember a release of BIP years ago that for some reason we went for 12/31 as a release date ... were we mad? I seem to remember we made it but talk about ridiculous pressure!

The latest 10g rollup is out in the wild and available from Oracle support. A bug fixing rollup but worth getting to and know that support will want you to get to it and re-test before going forward on an SR. One simple but very useful fix or enhancement:

[Cause of the bug]
@ ==================
@ Customer reports that despite the clock being shown, end users are clicking
@ on the View button repeatedly as the initial generation is taking some time.  
@ If the button were to be grayed out then  this would prevent the users
@ requesting the report more than  once.  Repeated requests are causing a
@ system overload and as this is their Production  instance this is extremely
@ important to the customer.
@ .
@ [The Fix]
@ =========
@ Added the logic to disable the button after the user clicks on the "view"
@ button and re-enable it when the report is loaded.

I told a group of customers once that they have a headache and we have a non-steroidal anti-inflammatory drug, alright, I actually said 'aspirin'. This little gem of a fix helps relieve another little headache that our aspirin was causing. The patch number for all this BIP pain killing is 13399232, enjoy!

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.

Tuesday Sep 27, 2011

BI Publisher sessions for Oracle Open World 2011

OOW

Are you coming to Oracle Open World 2011?

No?  There's still time to register!  The conference starts Sunday, Oct 2nd

Yes?  Great!  This year, in addition to the awesome sessions and demo pod, there are several hands on labs where you can get your hands on the product.

For a list of all the sessions that will highlight BI Publisher, please check out the Focus On Business Intelligence Publisher handout.

Be sure to add these sessions using the Schedule Builder, so you can be assured a spot. 


BI Publisher Demo Pods:
Oracle Business Intelligence Publisher    Moscone South, Right – SR-287
PeopleSoft PeopleTools     Moscone West – W-074

Oracle DEMOgrounds Schedule:

MONDAY, OCTOBER 3:     9:45 am – 5:30 pm
TUESDAY, OCTOBER 4:    9:45 am – 6:00 pm
WEDNESDAY, OCTOBER 5:    9:00 am – 4:00 pm



Sessions and Hands On Labs:

Everyone


Sun 2-Oct 1:00 pm – 1:45 pm

28541: Upgrading Your Oracle BIEE Software? Leverage 11g New Features
Moscone West 2011

Mon 3-Oct 3:30 pm – 4:30 pm    32020: Building Reports and Data Models in Oracle Business Intelligence Publisher 11g

(HANDS ON LAB)    Marriott Marquis - Salon 5/6

Mon 3-Oct 5:00 pm – 6:00 pm    14982: Create All Your Reports More Easily and Quickly with Oracle Business Intelligence Publisher   

Moscone West 3024

Mon 3-Oct 5:00 pm – 6:00 pm    12426: Oracle Business Intelligence Publisher: The Reporting Platform for Oracle Applications  

Moscone West 2022

Wed 5-Oct 1:15 pm – 2:15 pm    32020: Building Reports and Data Models in Oracle Business Intelligence Publisher 11g 

(HANDS ON LAB)    Marriott Marquis - Salon 5/6

Wed 5-Oct 5:00 pm  - 6:00 pm    16762: Become a Reporting Superstar with Oracle Business Intelligence Publisher Best Practices  

Moscone West 2002/2004

Thr 6-Oct 1:30 pm – 2:30 pm    15010: Oracle Business Intelligence Publisher: Anatomy of a Template

Moscone West -  2000



JD Edwards Enterprise

Tue 4-Oct 10:15 am  - 11:15 am    8009: Operational Reporting with Oracle Business Intelligence Publisher and JD Edwards: Success Story     Moscone West 3008

Wed 5-Oct 1:15 pm – 2:15 pm    15308: Simplify Your JD Edwards Reporting with Oracle Business Intelligence Publisher   

St. Francis - St. Francis Suite East

Thr 6-Oct 10:30 am -11:30 am    21406: Empower JD Edwards Users with Oracle Business Intelligence Publisher for Ad Hoc Reporting

(HANDS ON LAB)
Marriott Marquis - Nob Hill AB

Thr 6-Oct 12:00 pm -1:00 pm    21406: Empower JD Edwards Users with Oracle Business Intelligence Publisher for Ad Hoc Reporting

(HANDS ON LAB)   
Marriott Marquis - Nob Hill AB


Primavera


Tue 4-Oct 10:15 am  - 11:15 am    9774: Primavera Application Users, Welcome to Oracle Business Intelligence Publisher

Moscone West 2016

Thr 6-Oct 9:00 am - 10:00 am    29803: Using Oracle Business Intelligence Publisher with Primavera Contract Management

(HANDS ON LAB)   
Marriott Marquis - Nob Hill CD


E-Business Suite


Sun 2-Oct 11:45 am –  1:00 pm    28214: Business Intelligence (BI) Publisher OAUG SIG  

Moscone West 3006


Siebel

Tue 4-Oct 5:00 pm – 6:00 pm    18234: Building More-Insightful Reports with Siebel Reporting Solutions
Moscone West 2010


PeopleSoft


Thr 6-Oct 10:30 am – 11:30 am    14002:  PeopleTools Developer Series: Mastering PeopleSoft's Reporting Tools  

Moscone West 2024

Monday Sep 19, 2011

11g September Rollup

The latest rollup patch for BIP 11.1.1.5 is available, 12954375. For your delight and delectation and to bulk out this post, I have reproduced the readme file below.

This patch contains fixes of following bugs for Oracle Business Intelligence Publisher 11.1.1.5.0.

 10402890 - SETTINGS FROM "DELIVERY CONFIGURATION" PAGE NOT BEING HONORED WHEN REPORTS RUN 
 11725468 - ER: RTE HTML SUPPORT IN DATA XML
 11828672 - QA: INCORRECT INACTIVE WARNING MESSAGE
 11835845 - 11G SQL DATA SET CREATION ORA-01756 ERROR BECAUSE COLON IS READ AS BIND VARIABLE
 12376386 - REMOVETEMPLATEFORREPORT API DOESN'T REMOVE TEMPLATE
 12405904 - CANNOT DEPLOY BIP WHEN DEPLOYMENT PATH INCLUDES SPACE CHARACTERS
 12406034 - CANNOT USE JAVA DB - DERBY - FOR SCHEDULER DATABASE
 12595931 - WHEN PRINTING CHARTS TO PDF, NUMBERS BECOME ARABIC NUMBERS
 12615821 - MISSING MEMBERS ON THE XML GENERATED BY BIP
 12634030 - RE-PACKAGING MLR#11893189 TO XDOPARSER11G.JAR
 12666066 - ER: EXCEL 2007 SUPPORT IN REST API
 12705244 - JOINED MDX DATA SETS DO NOT PRODUCE CORRECT DATA - DATA MISSING
 12726163 - NUMBER OF SESSIONS INCREASES AFTER RUNNING SCHEDULED JOBS
 12737670 - GROUP BY FOR MDX DATA SET MISSING IN 11.1.1.5 - WAS PRESENT IN 11.1.1.3
 12750192 - ER: GENERATE NATIVE COMBINATION DUAL Y GRAPH IN PPTX OUTPUT FROM RTF AND XPT
 12766932 - VERTICAL DUAL Y COMBO CHART MISSING SETTINGS TO ASSIGN VALUES TO AXIS
 12791282 - WS PROXY WIZARD CANNOT GENERATE PROXY WHEN SERVICE NAME HAS SPECIAL CHARACTERS
 12828845 - ISSUE WITH XDO_USER_NAME PARAMETER ON BIP 11.1.1.5.0
 12835431 - INTRANSIT VALUATION REPORT QUERY GIVING ERROR IN EDITOR
 12847306 - BI PUBLISHER DATA SET - WON'T SAVE
 12872831 - EXCEL TEMPLATES GENERATING INCORRECT RESULTS FROM NESTED GROUPS
 12879801 - ALLOW SAVING DATA MODEL WITH SPECIAL CHARACTER
 12945367 - NOT ABLE TO EDIT DATA MODEL WHEN LINKS EXIT AMONG DATA SETS
 12945475 - IE: NOT ABLE TO EDIT DATA MODEL WITH MANY DATA SET AND LINKS 
 12956901 - IN SQL SERVER CLOB IN DATA MODEL UI MARKED AS STRING DATA TYPE
 12956915 - INCORRECT DATA WHEN PROCESSING OF CLOB COLUMNS FOR SQLSERVER
 12956934 - DATA MODEL SHOULD RESPECT CASE SENSITIVITY FOR COLUMN AND TABLE NAMES
 12972852 - DYNAMIC IMAGES BROKEN FOR FO/PDF
 12989467 - NEED TO CONVERT DOCUMENT FORMAT "EXCEL" TO "EXCEL2000"

----------------------------- INSTALLATION FOR ENTERPRISE -----------------------------

1. Login to BI Domain Weblogic Administration Console.

2. Find the location of the "bipublisher".

3. Stop the bipublisher application.

4. Open a terminal and go to the directory.

5. Back up the existing xmlpserver.ear to xmlpserver.ear.org.

6. Copy new xmlpserver.ear from this patch.

7. In Admin Console, select "Lock & Edit".

8. Select the application "bipublisher" and "Update".

9. Leave all options same and proceed to Finish.

10. Select "Activate Changes".

11. Start the bipublisher application.

------------------------- MDX Query Limitation -------------------------

Joins of two MDX queries on a dimension that uses a MDX function in the Select Clause that requires the stipulation of the same dimension, such as Intersect, MemberRange, Union are not supported. Example

SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)) ON Axis(1)
FROM Demo.Basic
 
It should be written using a With Set member:
 
with set [UnionMarket] as '{Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)}'
SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(([UnionMarket]) ON Axis(1)
FROM Demo.Basic
This MDX set is recreated for the Join clause to:
 
with set [UnionMarket] as '{Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)}'
SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(Filter([UnionMarket], [Market].CurrentMember.Member_Name = "Boston")) ON Axis(1)
FROM Demo.Basic
Plus you have the new XHTML formatter included in the patch too, more on that here.

 

Tuesday Sep 13, 2011

Web Services Datasources over SSL

Great question on accessing a web service as a data source over SSL today. BIP without any configuration does not like em. Oracle of knowledge Shinji of the development team put together a couple of links and I wanted to get them out on to the intertubes and share them.

Question:

My customer is creating BIP reports that use a web service/wsdl data source.  It all works fine without SSL.  They are a financial services company and internally they are required to use ssl when retrieving data from the web service.  Trying to config to the WSDL to use SSL.  Having an issue returning data from the source due to the ssl encryption.  Errors from BIP indicate the certificate is missing, but we're not sure where it is supposed to be located.

Answer:

Basically, you need to enable SSL in JVM level so that java applications run on the JVM can use SSL.
This is a generic configuration of Java and not specific to BI Publisher.

It is also documented in BIP documents.



Monday Sep 12, 2011

HTML in XML Support

I luckily still get to see some internal emails from the development team. I saw one today making mention of an upcoming 11.1.1.5 roll up patch set that is coming. This piqued my interest as I thought, was there one for August? Indeed there was or is, check out patch 12831433. Digging into the patch read me I found a nugget of gold that many folks have been looking for ... out of the box HTML >> FO formatting.

I have written about this subject elsewhere in the blog and you can find em using the search box. This is new, this is out of the box support to convert you stored XHTML to the required format for Publisher's underlying language, XSL-FO. What do I mean by that? Being able to take

<B>This is bold text</B>
and convert to its XSLFO equivalent

<fo:inline font-weight="bold">This is bold text</fo:inline>

There are some restrictions to what's available right now but its a big step forward. Here's the details.

This patch supports HTML embedded in XML. The following new layout command is added to retain HTML format from data in the final output.
<?html2fo: xpath?>
The HTML code you want converted of course needs to be within the report's xml data so that the template processor can work on it. Further, the html2fo command needs the HTML to be inside a CDATA section in the report data. The term CDATA is used to describe or store text data that should not be parsed by the XML parser. For example the < and > characters denote the opening and closing characters for an XML element tag. In our case we want them to denote the opening and closing of HTML commands. Without the CDATA section the XML parser would attempt to parse and process the HTML commands resulting in unexpected results to say the least. 
In the example below the HTML code is embedded in the field RTECODE.
<?xml version="1.0" encoding="UTF-8"?>
<RTECODE>
<![CDATA[
<font style="font-style: italic; font-weight: bold;" size="3"><a
href="http://www.oracle.com">oracle</a></font> <br/>
<font size="6"><a href="www.oracle.com">www.oracle.com</a></font><br/><br/>
]]>
</RTECODE> 

The next question is how do you get the CDATA sections wrapped around your HTML code?  Your HTML does not need to be stored into the database with the CDATA sections already attached. Well, you can do it in your SQL query but you need to use a very specific way to achieve it:

select '<![CDATA' || '['|| RTECODE || ']' || ']>' as "RTECODE"
from table x

You'll be thinking, hey, I can get rid of a set or two of those concatenation pipes, don't do it! Please use the example above and stick to it. If you do remove some of the || then the CDATA section will not be preserved correctly by the extraction engine.

Sample usage in rtf template (assume the data.xml is as above).

<?html2fo:RTECODE?> 

Supported html formats:

  • Paragraph Font style ( bold, italic, plain, underline, subscript, superscript and strikes-through)
  • Font size
  • Font family
  • Background color
  • Foreground color
  • Paragraph alignment (center, left, right and justify)
  • Paragraph indent
  • URL link
  • List
  • Bullet List
  • Number List

Non-Supported formats:

  • Nested List (List with Indent)
  • Some HTML tags/attributes which manually inserted like Table, Image, etc... in stead of HTML editor
Pretty cool, if you are on 11.1.1.5 and not up to the August patch set, give it a try!


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!


Thursday May 12, 2011

BI Publisher 11g - 11.1.1.5 is here!

New Oracle BI Publisher 11g Release 11.1.1.5 is released! Here is a list of the new features.

clip_image002

Much Faster Interactive Reporting Experience

Using BI Publisher to interact with your data is snappy. Generating interactive report output uses less memory and is faster than ever. BI Publisher’s highly optimized, three layer caching mechanism provides a highly responsive, interactive reporting experience.




New Interactive Component - List

The List component is a nifty way to filter data in an interactive report. When you click values in the list, BI Publisher automatically filters the data in the report. In the Layout Editor you can set the orientation of the List to be horizontal or vertical and over 30 other options to make it visually appealing for your consumer needs. Lists are another great way to interact with your report to gain more insight. See a quick demo

clip_image004





clip_image007

Both Native and Web Apps for iPhone & iPad

More and more business tasks are performed on mobile devices like iPhone and iPad, so why not browse reports and interact with your data on your mobile device with BI Publisher? Simply access your BI Publisher reports with Safari or other browsers on your iPad or iPhone, or install the Oracle BI Mobile App for iPhone and iPad to enjoy the same rich, fast interactive reporting experience as on your desktop or laptop. See sample pictures.



Enterprise Reporting Platform for Oracle - E-Business Suite

BI Publisher now supports Oracle E-Business Suite’s data level security based on user responsibility and allows you to switch responsibilities on the fly. You can use BI Publisher 11.1.1.5 to view, manage, and deliver reports against E-Business Suite data and join and aggregate with data from other data sources. Utilities are available to help you convert from Oracle Reports and upgrade to BI Publisher 11g. Start taking advantage of many of the great BI Publisher 11g new features with your E-Business Suite data today.

clip_image008




clip_image010

New Data Source – Local Excel File

Often times you need data in Excel to supplement other data or you need to visualize it quickly. BI Publisher 11.1.1.3 added Excel files as a data source so you could create a report against Excel data located on the server. BI Publisher 11.1.1.5 goes one step further. Now you can upload Excel spreadsheets from your local machine directly to a Data Model. Create reports from the uploaded Excel spreadsheet or join it and aggregate it with other data sources. Reporting with local Excel files is a great way to empower your users to visualize and share data easily and still maintain a centralized report environment. See a quick demo





New Template Type - Excel Template

Some users just love Excel, so we added Excel as a report layout design option. In addition to the existing template options like BI Publisher Templates, RTF, PDF, Flash, and others, you can now use Excel to design your report the way you want, using custom functions and Excel Macros to split data across multiple sheets, calculate values dynamically, then share with your colleagues.

clip_image012











clip_image014

Auditing and Monitoring

Auditing is not just about compliance, it’s a way to improve customer service by understanding what your users like to do, when and how they access and view reports. BI Publisher 11.1.1.3 included a framework to help IT Administrators collect data required for auditing. BI Publisher 11.1.1.5 enhances the audit and performance information captured and introduces a method to store the data into a database. Now you can use BI Publisher to visualize, analyze and report on your auditing and performance information. See a quick demo.



New Output Format Type – Native Excel 2007 Output

Now you can download your report as Excel 2007 format and open it with the latest Excel on both Windows and Mac with high fidelity formatting and layout. You can use repeating groups in your report to automatically group data in separate worksheets. With this format option the downloaded Excel file is 70% smaller compared to other Excel output types, which makes it easier to share your report in Excel with your colleagues.

clip_image015



clip_image017

Reports Development Life Cycle Management

Developing reports often involves multiple phases and processes before going live. Reports need to be moved to a testing environment after the development and then to the production environment. You can use the new BI Publisher Catalog Utility to move your reports either one at a time or in batch from one environment to another all without shutting down servers so users can continue to generate the mission critical documents and reports they need.

Also, we have many more enhancement requests implemented in this release and fixed bugs from the previous release. Try download from here and start BI Publisher 11g today!

Want to learn more about BI Publisher?

clip_image019

Follow us @bipublisher for our latest news, inside scoop, or tweet us your feedback!

clip_image021

Connect with other BI Publisher professionals!

clip_image023

Find How-to and solutions over 500 articles!

clip_image025

Subscribe our channel and check our introduction and How-to videos!

clip_image027

Ask any question you have and get answered at our Discussion Forum!

Thursday Mar 10, 2011

Dynamic Data Connections

[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
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
21
22
23
24
25
26
27
28
29
30
31
      
Today