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!
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
« March 2015
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
31
    
       
Today