Thursday Sep 13, 2012

Dynamic Bursting ... no really!

If any of you have seen me or my colleagues present BI Publisher to you then we have hopefully mentioned 'bursting.' You may have even seen a demo where we talk about being able to take a batch of data, say invoices. Then split them by some criteria, say customer id; format them with a template; generate the output and then deliver the documents to the recipients with a click. We and especially I, always say this can be completely dynamic! By this I mean, that you could store customer preferences in a database. What layout would each customer like; what output format they would like and how they would like the document delivered. We (I) talk a good talk, but typically don't do the walk in a demo. We hard code everything in the bursting query or bursting control file to get the concept across. But no more peeps! I have finally put together a dynamic bursting demo! Its been minutes in the making but its been tough to find those minutes! Read on ...

It's nothing amazing in terms of making the burst dynamic. I created a CUSTOMER_PREFS table with some simple UI in an APEX application so that I can maintain their requirements. In EBS you have descriptive flexfields that could do the same thing or probably even 'contact' fields to store most of the info.

Here's my table structure:

Name                           Type
------------------------------ -------- 
CUSTOMER_ID                    NUMBER(6)
TEMPLATE_TYPE                  VARCHAR2(20)
TEMPLATE_NAME                  VARCHAR2(120)
OUTPUT_FORMAT                  VARCHAR2(20)
DELIVERY_CHANNEL               VARCHAR2(50)
EMAIL                          VARCHAR2(255)
FAX                            VARCHAR2(20)
ATTACH                         VARCHAR2(20)
FILE_LOC                       VARCHAR2(255)

Simple enough right? Just need CUSTOMER_ID as the key for the bursting engine to join it to the customer data at burst time. I have not covered the full delivery options, just email, fax and file location. Remember, its a demo people :0) However the principal is exactly the same for each delivery type. They each have a set of attributes that need to be provided and you will need to handle that in your bursting query. On a side note, in EBS, you use a bursting control file, you can apply the same principals that I'm laying out here you just need to get the customer bursting info into the XML data stream so that you can refer to it in the control file using XPATH expressions.

Next, we need to look up what attributes or parameters are required for each delivery method. that can be found in the documentation here.

 Now we know the combinations of parameters and delivery methods we can construct the query using a series a decode statements:

select distinct cp.customer_id "KEY",
     cp.template_name TEMPLATE,
     cp.template_type TEMPLATE_FORMAT,
     'en-US' LOCALE,
     cp.output_format OUTPUT_FORMAT,
     'false' SAVE_FORMAT,
     cp.delivery_channel DEL_CHANNEL,
     decode(cp.delivery_channel,'FILE', cp.file_loc 
      , 'EMAIL', cp.email
      , 'FAX', cp.fax) PARAMETER1,
     decode(cp.delivery_channel,'FILE', c.cust_last_name||'_orders.pdf'
      ,'EMAIL','cc@BIPublisher.com'
      ,'FAX', 'faxserver.com') PARAMETER2,
     decode(cp.delivery_channel,'FILE',NULL
      ,'EMAIL','customer_service@bipublisher.com'
      ,'FAX', null) PARAMETER3,
     decode(cp.delivery_channel,'FILE',NULL
      ,'EMAIL','Your current orders'
      ,'FAX',NULL)    PARAMETER4,
     decode(cp.delivery_channel,'FILE',NULL
      ,'EMAIL','Please find attached a copy of your current orders with BI Publisher, Inc'
      ,'FAX',NULL)  PARAMETER5,
     decode(cp.delivery_channel,'FILE',NULL
      ,'EMAIL','false'
      ,'FAX',NULL) PARAMETER6,
     decode(cp.delivery_channel,'FILE',NULL
      ,'EMAIL','reply@bipublisher.com'
      ,'FAX',NULL) PARAMETER7
from cust_prefs cp, 
customers c,
orders_view ov
where cp.customer_id = c.customer_id
and cp.customer_id = ov.customer_id
order by  cp.customer_id

Pretty straightforward, just need to test, test, test, the query and ensure it's bringing back the correct data based on each customers preferences. Notice the NULL values for parameters that are not relevant for a given delivery channel.

You should end up with bursting control data that the bursting engine can use:

 Now, your users can run the burst and documents will be formatted, generated and delivered based on the customer prefs.

If you're interested in the example, I have used the sample OE schema data for the base report. The report files and CUST_PREFS table are zipped up here. The zip contains the data model (.xdmz), the report and templates (.xdoz) and the sql scripts to create and load data to the CUST_PREFS table.  Once you load the report into the catalog, you'll need to create the OE data connection and point the data model at it. You'll probably need to re-point the report to the data model too.

Happy Bursting!

Tuesday Sep 11, 2012

Hey, Wheres your field?

I have finally been getting back to playing with Publisher this past week and actually building demos!
I came across a nice feature in the online template builder. I had been tweaking the data model of an already built report including an existing online template.

I finished my data model changes and re-opened the online template to make changes and got this:

Nice! It recognized that some of the fields were now missing from the data model and was asking me to map new fields into the template. Once I had mapped them it then opened the template for editing. Very neat!

Wednesday Sep 05, 2012

Trigger Happy

Its been a while, I know, we’ll say no more OK? I’ll just write …

In the latest BIP 11.1.1.6 release and if I’m really honest; the release before this (we'll call it dot 5 for brevity.) The boys and gals in the engine room have been real busy enhancing BIP with some new functionality. Those of you that use the scheduling engine in OBIEE may already know and use the ‘conditional scheduling’ feature. This allows you to be more intelligent about what reports get run and sent to folks on a scheduled basis. You create a ‘trigger’ analysis (answer) that is executed at schedule time prior to the main report. When the schedule rolls around, the trigger is run, if it returns rows, then the main report is run and delivered. If there are no rows returned, then the main report is not run.
Useful right? Your users are not bombarded with 20 reports in their inbox every week that they need to wade throu. They get a handful that they know they need to look at. If you ensure you use conditional formatting in the report then they can find the anomalous data in the reports very quickly and move on to the rest of their day more quickly. You could even think of OBIEE as a virtual team member, scouring the data on your behalf 24/7 and letting you know when its found an issue.

BI Publisher, wanting the team t-shirt and the khaki pants, has followed suit. You can now set up ‘triggers’ for it to execute before it runs the main report. Just like its big brother, if the scheduled report trigger returns rows of data; it then executes the main report. Otherwise, the report is skipped until the next schedule time rolls around. Sound familiar?

BIP differs a little, in that you only need to construct a query to act as the trigger rather than a complete report. Let assume we have a monthly wage by department report on a schedule. We only want to send the report to managers if their departmental wages reach and/or exceed a certain amount.

The toughest part about this is coming up with the SQL to test the business rule you want to implement. For my example, its not that tough:

select d.department_name, sum(e.salary) as wage_total
from employees e,
departments d
where d.department_id = e.department_id
group by d.department_name
having sum(e.salary) > 230000

We're looking for departments where the wage cost is greater than 230,000 Dexter Dollars! With a bit of messing I found out you can parametrize the query. Users can then set a value at schedule time if they need to.

To create the trigger is straightforward enough.

You can create multiple triggers for users to select at schedule time. Notice I also used a parameter in the query, :wamount. Note the matching parameter in the tree on the left. You also dont need to return multiple columns, one is fine, the key is if there are rows returned. You can build the rest of your report as usual.

At scheduling time the Schedule tab has a bit more on it. If your users want to set the trigger, they check the Use Trigger box. The page will then pop fields to pick the appropriate trigger they want to use, even a trigger on another data model if needed.


Note it will also ask for the parameter value associated with the trigger.

At this point you should note that the data model does not make a distinction between trigger and data model (extract) parameters. So users will see the parameters on the General and Schedule tabs. If per chance you do need to just have a trigger parameters. You can just hide them from the report using the Parameters popup in the report designer, just un-check the 'Show' box

I have tested the opposite case where you do not want main report parameters seen in the trigger section. BIP handles that for you!

Once the report hits its allotted schedule time, the trigger is executed. Based on the results the report will either run or be 'skipped.'


Now, you have a smarter scheduler that will only deliver reports when folks need to see them and take action on the contents.

More official info here for developers and here for users.

Tuesday Sep 04, 2012

14540059 - UPDATE FOR BI PUBLISHER ENTERPRISE 11.1.1.6.0 AUGUST

Its been a while, I know :( I have posts in the pipe just gotta smoke em out!

The latest update for BIP 11.1.1.6 was released last week. A bunch of defects have been addressed as you can see below.

 13473493 - XMLP TRANSLATION ISSUE OF MILLION (ENG) TO MILLIONES (SPANISH)
 13521951 - BIP UPGRADE FROM 10G TO 11.1.1.5.0 IS NOT SUCCESSFULL FOR TIAA-CREF
 12542914 - ACC: REPORT VIEWER STRUCTURE HAS ERRORS - NO IFRAME AND NO LANG ATTRIBUTE
 13562801 - XML TAG DISPLAY SHOULD DEFAULT TO 'FOLLOW THE DATA
 13568043 - BIP QUERY FAILING VALIDATION DUE TO 'COALESCE' KEYWORD
 13592901 - THE REPORT IS THROWING AN SQL ERROR THAT REFERENCES CHECKING FOR NULL VALUES
 13836696 - BI PUBLISHER REPORT NOT GENERATED WHEN A TEXT FIELD START WITH "E.<SPACE>"
 13879206 - DM MIGRATION ISSUES
 13888939 - DM: LOV SEARCH CAUSING DB CONNECTION LEAK
 13904225 - XSLX ERROR DUE TO URL LINK AND USE OF LIST
 13930795 - RTF TEMPLATE GIVING DIFFERENT RESULTS IN DIFFERENT
 13942064 - XDOEXCEPTION THROWN WHEN RUNNING PEOPLESOFT TEMPLATES AND XML FILE
 13981523 - BI PUBLISHER ON 64-BIT WINDOWS CAN'T CONNECT TO MS ANALYSIS SERVICES CUBE
 14039229 - BIP 11.1.1.5.0 REPORTS ARE NOT WORKING ON BIP 11.1.1.6.0
 14055793 - BIP 11.1.1.6.0: DATE TYPE INPUT PARAMTER IS NOT DISPLAYING THE CORRECT VALUE USI
 14059851 - UNABLE TO GRANT PRIVILEGES TO ROLE: DOMAIN USERS; THE ROLE DOES NOT EXIST
 14109967 - LARGE OUTPUT CAUSES OUT OF MEMORY DUE TO LEFT OVER DEBUG CODE
 14163973 - ISSUES USING DATA MODEL EDITOR IN BIP 11.1.1.6
 14167915 - ORG.XML.SAX.SAXEXCEPTION: DATE FORMAT CANNOT BE NULL
 14240045 - EDITING SCHEDULED REPORTS DOES NOT REFLECT VALID VALUES FOR UPGRADED SCHEDULES
 14304427 - SEARCH DIALOG NOT BINDING PARAMETER VALUE - INVALID PARAMETER BINDING(S).
 14338158 - PASSWORD FIELD SHOULD NOT BE DISPLAYED FOR FMW SECURITY MODEL
 14393825 - OBIEE11G: LARGE NUMBER OF OBIPS SESSIONS CREATED WHEN USING SSO AND BI PUB
 14558377 - CONT. BUG 14240045:EDITING SCHEDULES IN BI PUBLISHER IS DEFAULTING TO 'ALL'

This patch is just for BI Publisher standalone installs. For those of you using BIP within the wider BIEE suite there is the 11.1.1.6.2 BP1 patchset. More details on that here.

Friday May 18, 2012

Secrets Revealed to Advanced Charting

We get a lot of emails and questions here at Publisher Tower concerning charts and how to do X. I write about some of the solutions here if I think they could be useful to a wider audience but its tough to document everything for everyone's specific features.

The chart dialog in the template builder gets you so far but there are cases where you are going to have to get into the code to make things work the way you want them to. I have documented a bunch which I have pulled together as links below. But if you do venture into the chart code, where do you start?

I have re-documented the location of the chart DTD document recently as it disappeared from OTN and having bugged a few people about it, its still not there, c'est la vie. But those of you with the Template Builder for Word (TB) have your own copy you can refer to. Just dig into your TB install directory and look for the dvt-jclient.jar (11g) or bipres.jar (10g) files open them with a zip utility a dig down through the directories to oracle\dss\graph\.
There you will find the fabled and rare, graph.dtd ... this is the golden fleece of the BIP charting world. In it, you will find secrets beyond your imagination, treasures beyond compare ...  OK, its not that exciting but there is a lot of charting info to be gleaned. There is not much in the way of comments but you can at least look up features and then see what attributes they will need to achieve your needs.

Just remember, Word has almost unlimited undo's, just get stuck in a try stuff out you are not going to break anything!

Some blogged chart solutions via google.

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)

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