My Date doesn’t like me with Siebel !

 

Are you doing ok with your date in the Siebel reporting with BI Publisher ?

If you are coming from Siebel this might make you kind of confused because the BI Publisher user’s guide tells you it supports all sorts of the date formatting and date related calculations but somehow it doesn’t work when you try with the Siebel reports. If you are coming from BI Publisher reporting world you might have figured out why it doesn’t.

BI Publisher provides quite various of ways of the date formatting and date related calculations. You can find the detail from the BI Publisher’s Reports Designer’s Guide. Here is a section for the date formatting. And here is a section where you can find the date related functions that you can use to do the date related calculation.

However, in order for BI Publisher to do the date formatting or the calculation the date data (in the XML data) needs to be presented as something called ‘Canonical’  format, which is something like ‘2008-10-09T05:31:13.000-04:00’. The example contains Year, Month, Day, Time, and Time zone difference from GMT. It doesn’t need to contain the time and the time zone difference parts, but it needs to have the date part at least. And it needs to be in ‘YYYY-MM-DD’ or ‘YYYY-MM-DDTHH:MI:SS’ format.

And now, the date data generated by Siebel with the IO is in ‘MM/DD/YYYY HH:MI:SS’ format, so it is something like ‘10/09/2008 05:31:13’. And BI Publisher doesn’t recognize that this is a date data because it’s not presented in the ‘Canonical’ format!

Due to this fact, not only you can’t do the date formatting but also you can’t do the date related calculation. For example, you might want to print number of day between two presented dates like start date and end date. If the those two date data is presented in the ‘Canonical’ format then you can use something like ‘date_diff()’ function to get the duration counted between the two dates. But it doesn’t do anything or you get an error if you provide those two dates not in the ‘Canonical’ format.

There is a discussion in the Siebel reporting product development that this might be addressed in the next Siebel FixPack by having IO returning the date in the ‘Canonical’ format. But it’s not determined yet and guaranteed.

So what should we do now ? There are two possible and reasonable workarounds to overcome this issue. The first one is to use one of the Siebel’s extended function ‘totext()’ to convert the Siebel date format to the ‘Canonical’ date format before the BI Publisher’s date related function comes in. Here is an example of how to use to convert the date.

psfn:totext(ssCreated,"yyyy-MM-dd","MM/dd/yyyy")

The above example is giving the Siebel date data, telling what date format the presented date data in, and telling how to convert. So if the ‘ssCreated’ is ‘10/09/2008’ then it would return as ‘2008-10-09’, which is the ‘Canonical' date format. Once you get your date data in the ‘Canonical’ format then you can do the date formatting or use the date functions to meet your requirements.

The advantage of this option is easy and quick to implement and you don’t need any customization. The disadvantage of this option is that as you see it gets messy especially when you want to do a date related calculation in a complex business logic because you need to specify the date formats every time you use it.

The second is to develop a custom Java function to convert the date from, the Siebel’s date format to the ‘Canonical’ date format. As you might know BI Publisher supports a way for you to develop custom functions with Java and use them in the RTF template. The advantage of this option is clean and easy to use. The disadvantage is that you need to develop the Java code and deploy it to the server, which might require the system administrator’s help if you don’t have an access to the server instance.

So now, at least you can get your date data work in the Siebel reports! And I will discuss on how to develop the custom Java function for Siebel reports tomorrow. So again, stay tuned!

What to Do with these Siebel Extended Functions?

After you run the Actuate to BI Publisher conversion utility and open the generated RTF template file you would find something like below in the text forms, though it depends what you had in your old Actuate report.

<?value-of:concat(concat(ssActiveFirstName," "),ssActiveLastName)?> <?value-of:psfn:Format(psfn:CInt(TotalCommit1),"$#,##0")?> <?value-of:concat("Printed on: ",psfn:Format(psfn:currentdate(),"mm/dd/yyyy"))?>

And when you try to preview with your MS-Word with BI Publisher Add-in, you’ll get an error.

First of all, when you try to preview the report after loading a sample XML data by using the MS-Word add-in (Template Builder) you need to follow some steps that I discussed yesterday.

Second, the ‘value-of’ just doesn’t work. I don’t even know why they are there. But the good news is you can just get rid of them!

And now, assuming that your MS-Word Add-in has already been setup correctly and you have declared the ‘psfn’ name space in your RTF template, the next step is to figure out ‘what the hell are these functions doing?’

The conversion utility generated those codes with the Siebel’s extended functions doesn’t mean that you need to use them as they are. Most of the functions are covered and can be replaced by the BI Publisher’s existing functions so no need to use the Siebel’s. I would recommend to use the BI Publisher’s ones over the Siebel’s ones due to a long term maintenance advantage. However, some of the functions are not provided by BI Publisher, then you want to stick with the Siebel’s extended functions like the ones I recommended yesterday.

So What to Do with These Syntax?

Well, this really depends on each case but here is a list of the steps that you can take to reach to the answer.

1. Understand what it’s trying to do
2. See if there is any BI Publisher’s native function that can be used to provide the same functionality
3. If yes to the #2 question then replace and test
4. If no to the #2 question then see if it works without any modification
5. If no to the #4 question then come up with a logic by using BI Publisher’s native function or developing a new custom Java function

1. Understand What It’s Trying to Do

You can guess what it’s trying to do just by looking at the generated code. But it’s always better to see the original logic in the Actuate report definition. And it’s even better to review the original business requirement. Often that the requirement has been changed over the years and some of the logic might not be relevant anymore. If that’s the case you definitely don’t want to spend any of your time on something that is not meeting today’s requirements.

2. See if there is any BI Publisher’s Native Function

Once you understand the requirement and what the syntax is trying to do then review the BI Publisher’s function list to see if any of the functions can replace it without compromising the requirement.

3. Replace It and Test It

No much to talk about here, but go ahead replacing it and make sure the result is meeting the expectation.

4. Leave It As It Is and Test It

If you can’t find any BI Publisher’s function that can replace it then try to use it and see if it works. If it works then great, but I had some functions that didn’t work (e.g. CnvDate), and later I was told that they were not even supported! So it’s important to setup your MS-Word and test it on your local environment before uploading it to the Siebel server.

5. Come up with a New Way

If you can’t find any BI Publisher’s function that can replace and the Siebel’s extended function doesn’t work then you need to come up with a way to make it work to meet the requirements. With BI Publisher’s flexible development framework usually you should be able to find a way easily to achieve this by combining the existing functions or XSL/XPath functions and creating a logic.

As a long term solution though, if you see this function or logic can be used in many other reports, then you might want to think about developing your own Java custom function. BI Publisher supports such custom function being developed by Java and being used in the RTF template. I will cover this later this week.

Now let’s see what we can do with those in the above example.

What Can We Do With the Examples

Example 1:

Here is the first one.

<?value-of:concat(concat(ssActiveFirstName," "),ssActiveLastName)?>

This one is simply trying to print First Name and Last Name in this order and have one space between so the result would be something like below.

Barak Obama

As most of you know this is where it becomes so easy to develop and design with BI Publisher. What you need to do is just this.

<?ssActiveFirstName?> <?ssActiveLastName?>

You can either use the two text forms (the gray square box ones) or just type the above, and adjust the space between in the MS-Word.

Example 2:

Here is the second one.

<?value-of:psfn:Format(psfn:CInt(TotalCommit),"$#,##0")?>

This one is trying to do the number formatting in this ‘$9,990’ format. And CInt is trying to ensure that the data is always passed as number. You can simply replace this by get rid of all but the ‘TotalCommit’ data element like below.

<?TotalCommit?>

Then use the BI Publisher’s property UI where you can specify your desired number format.

Example 3

And here is the last one.

<?value-of:concat("Printed on: ",psfn:Format(psfn:currentdate(),"mm/dd/yyyy"))?>

This is trying to print the current date as a ‘Printed’ date so that the users know when this report is generated. There are actually many ways to print the ‘Current date’ with BI Publisher. And here are some examples.

<?xdofx:sysdate()?> <?xdoxslt:current_date($_XDOLOCALE, $_XDOTIMEZONE)?> <?xdoxslt:sysdate_as_xsdformat()?>

The date formatting itself is a whole another topic and I leave the detail to another post later. But you can use one of the above to return the current date and use the BI Publisher’s property UI, the one pop up when you double click the gray box, and specify your desired date format. And again, you don’t need the ‘concat’ function to concatenate the two strings. You can simply type the ‘Pritned on: ‘ and have the text form box next which includes one of the above syntax.

Conclusion

The chances are you will have more and various different requirements and can have the RTF templates generated with more various and can be funky syntax. Hope this post help you to get started and give you a starting point and make your reports development more enjoyable.

Siebel’s Extended Function for RTF Template

Has anyone noticed some funky functions after you converted the BAS (Actuate) files to BI Publisher’s RTF template ? These functions look confusing especially for some of us coming from pure BI Publisher side. And even worse, they are most likely throwing errors at you when you try to preview with the MS-Word Add-in Template Builder!

So what are these? Well, these functions are there to help you to provide easier way to achieve some of the Siebel specific requirements. However, there are many functions that now we can just use BI Publisher’s out-of-box functions instead. And some functions just simply don’t work due to some defects with the conversion utility.

So it’s important to identify what functions we can use, when to use them, and how to use them.

What Functions Can We Use ?

There are about 30 or so of the Siebel extended functions in total. But most of them are actually either not really useful or duplicate of already existing BI Publisher’s functions. I couldn’t get an approval to share the list with public so I can’t list them here. But again, most of them are not really useful so I’ll list only those that are useful for the Siebel reports development at the next section.

When to Use Them ?

Among all the Siebel’s extended functions here is a list of the functions that I personally find useful.

Function Name	Description	Example
DivIntZero(int div, int divisor)	Method returns a 0 if the divisor is a zero.	<?psfn:DivIntZero(10, 0)?>
now()	Method to return the current time in the native time format.	<?psfn:now()?>
phoneFormat(java.lang.String inp)	Method to returns a String, which is the usual way to display the phone or fax number i.e. (XXX)AAA-BBBB	<?psfn:phoneFormat(ssPhone)?>
totext(java.lang.String d,java.lang.String f,java.lang.String e)	Method to return the date "d" the format "f" of the input date, which is in format " e ". format yyyy-MM-dd.	<?psfn:totext(ssCreated,"yyyy-MM-dd","MM/dd/yyyy")?>
Val (java.lang.String input)	Method to Returns the numeric value of a string expression.	<?psfn:Val(ssAccountName)?>

Now, you might have the following two questions.

• Ok, those look useful and I want to use them. But is there any pre-configuration or pre-requisite steps ?
• I see bunch of functions in my RTF template that was generated by the conversion utility, what should I do with them ?

So let’s talk about what pre-requisite steps required to use the Siebel extended functions. Then later I’ll discuss about what to do with the automatically generated functions in the RTF template.

What are the Pre-Requisite Steps to Use Siebel Extended Functions ?

There are three steps to ensure in order for you to use the Siebel extended functions.

• Configuration at Siebel Server
• Configuration at BI Publisher Enterprise Server
• Configuration at Client (BI Publisher MS-Word Add-in)
• Declare the Siebel Extended Function in each RTF template

Configuration at BI Publisher Enterprise Server

There are a couple of JAR files that come with the Siebel server installation. And you need to make these JAR files available to your BI Publisher server instance so that at the runtime the BI Publisher Server can use the JAR files to execute the Siebel’s extended functions. So make sure you have those JAR files, which are listed below, under the BI Publisher’s Server’s Java library folder. (e.g. OracleAS_HOME/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/lib)

• XSLFunctions.JAR
• SiebelCustomXMLP.JAR
• SiebelCustomXMLP_SIA.JAR

Then you need to enable ‘External File Reference’ from the BI Publisher Server UI.

1. Log in to the Oracle BI Publisher Server with administrator privileges.
2. Click the Admin tab, and then select Runtime Configuration and Properties.
3. Change the default value for the Disable External Reference attribute to FALSE, click Apply, and then verify the changes were made.

The detail configuration for BI Publisher Enterprise Server is documented here.

Configuration at Siebel Server Side - Adding an Explicit Reference to JAR Files for the Oracle BI Publisher Server

This requires for the scheduling functionality to work with the functions. You can find the detail setup from here.

Configuration at BI Publisher client (MS-Word Add-in Template Builder) side

If you want to use the Siebel’s extended functions in your RTF template and preview with the MS-Word Template Builder you will get an error without the steps I’m going to talk about here. There are two things to do. You need to configure your Template Builder environment, which requires only for the first time, and need to declare the Siebel function name space in the RTF template for these functions to work.

Create a Batch File to Launch MS-Word Associating with the Siebel’s JAR files

Basically, you need to add a set of Siebel jar files to ‘_JAVA_OPTIONS’ variable and call MS-Word for these functions to work in your Template Builder instance. Here is an example script that you can use to launch MS-Word with the variable setting. 

echo %1 set _JAVA_OPTIONS=-Xbootclasspath/a:C:\81DQSSIA\client\classes\SiebelXMLP.jar;C:\81DQSSIA\client\classes\XMLP.jar;C:\81DQSSIA\client\classes\siebel.jar;C:\81DQSSIA\client\classes\XSLFunctions.jar;C:\81DQSSIA\client\classes\SiebelCustomXMLP.jar;C:\81DQSSIA\client\classes\SiebelCustomXMLP_SIA.jar "C:\Program Files\microsoft office\Office\Winword.exe" %1

Note that the above ‘set _JAVA_OPTIONS’ line must be a single line, no return key. You can find the detail of this configuration here from the configuration guide.

Once you have created the above script as a .bat file then you can double click this .bat file to start MS-Word and open your RTF template. In order for this configuration to work you need to shutdown all the MS-Word documents first and start MS-Word by clicking this bat file.

Declare Siebel’s Extended Function Name Space in RTF Template

Before you start using the extended function, first you need to declare a name space called ‘psfn’ as below in your RTF template.

<?namespace:psfn=http://www.oracle.com/XSL/Transform/java/com.siebel.xmlpublisher.reports.XSLFunctions?>

You need to declare this in every single RTF template file where you want to use the extended functions.

Now you’re ready to start using the Siebel’s extended functions!

How to Use Siebel Extended Functions?

You can use the Siebel’s extended functions as the same way you use other BI Publisher’s functions. Here is some example.

<?psfn:DivIntZero(10, 0)?> <?psfn:now()?> <?psfn:phoneFormat(ssPhone)?> <?psfn:totext(‘ssCreated’,"yyyy-MM-dd","MM/dd/yyyy")?>

You just need to start with ‘psfn:’ name space before the function. And you can preview the report output with a sample data that you have downloaded from the Siebel server UI (Web Client).

When you preview it you should be able to see the values from your sample XML data being calculated or formatted by the functions.

Troubleshooting:

1. I’m getting an error with ‘Namespace prefix 'psfn' used but not declared.’

Caused by: oracle.xdo.parser.v2.XPathException: Namespace prefix 'psfn' used but not declared.

As mentioned above you need to make sure that you have declared the Siebel’s extended function’s namespace in the RTF template. 2. I’m seeing an error with ‘method not found’ when trying to preview

2. I’m getting an error with ‘method not found’ when trying to preview.

If you are still seeing an error saying ‘method not found’ then you should make sure that you close all the MS-Word document completely then double click the custom MS-Word launch program (.bat) and open the RTF template file.

Conclusion

Once your RTF template is ready now you can upload it to the server and run it from the Web client. But you want to make sure that the server side configuration, which was mentioned above, has been done properly for these functions to work.

Hopefully now you understand how to use the Siebel’s extended functions within both the runtime and the development sides.

I still haven’t answered to one of the questions I raised above.

‘I see bunch of functions in my RTF template that was generated by the conversion utility, what should I do with them ?’

The Actuate to BI Publisher Conversion utility generate the RTF templates with many of the Siebel’s extended functions. So I will discuss about what to do with them tomorrow. Stay tuned!

What’s the Latest with Siebel Actuate Reports Conversion Utility?

 

I have talked about the Actuate to BI Publisher reports conversion for Siebel by using the Actuate Migration Utility at this blog before.

• Actuate to BI Publisher Reports Conversion for Siebel – Part 1
• Actuate to BI Publisher Reports Conversion for Siebel – Part 2

However, that was more than a couple of months ago therefore we’ve got some updates with the latest Actuate to BI Publisher reports conversion utility. Now the tool can generate Integration Objects (IO) based on the Siebel reporting object, which is quite useful because most of the existing custom reports use the reporting objects to generate the reporting data in the old Actuate based reporting framework.

So let me go through the reports conversion at very high level. The detail can be found in a PDF document that comes with the utility called, ‘Converting Reports from Actuate to Oracle BI Publisher’.

Where you can download?

You can download the Actuate to BI Publisher Reports Conversion Utility from Oracle’s support web site (a.k.a Metalink).

Once you are at the support web site you can type ‘8968224’ as a Patch ID to search the conversion utility. There are different versions for each version of the Siebel release.

How to Start?

With this tool you can do two things. One is to convert the reporting objects to the Integration Objects (IO). Another is to convert the Actuate BAS files to BI Publisher RTF template files.

The utility is a Java program so you can technically run it on either Windows or Unix/Linux environment. However, there is a command line script provided to assist you to run the utility easier. And the command line script is only for Windows. So I’d recommend using this command line script on Windows unless you have other special requirements that you can’t.

And since the utility is a Java program, you need to have a JDK or JRE setup on your machine before you run this tool. The JDK/JRE version should be 1.5 or later.

How to Use to Generate IO?

Run the Utility Script

First of all, you need to ensure that you have an ODBC connection to the local Siebel Tools database and that it works fine.

You can use ‘run_IOC’ command script to generate the IO. Here is a sample command line to call the utility’s script to generate an IO

run_IOC –r <Siebel Tools report name> -d <ODBC Datasource Name> -u <DB username> -p <DB password> -l <log file path> -ll <log level> -n <IO name>

The Report Name (-r) and ODBC Data Source Name (-d) are mandatory while other parameters are optional.

Parameter	Note
-r	Report Name. You can find this name as ‘Access Base DB Name’
-d	ODBC Data Source name
-u	username for Siebel user ‘SIEBEL’. This is case sensitive.
-p	password for Siebel user. This is case sensitive.
-l	log file path
-ll	log level. The option can be: SEVERE, WARNING, CONFIG, INFO, FINE, FINER, FINEST, ALL, or OFF
-n	If you want to override the generated IO name then you can use this option. But it’s better to not use this option so that the IO will be generated with a name starting with ‘BIP_’, which is required to be shown up in the IO list at the report template page.

And here is an example:

run_IOC -r SALES -d SSD -l C:\Conversion\Logs\Sales.txt -ll all

Import IO

Once the Integration Object(s) have been created in Siebel Tools, you can archive the Integration Object(s) into an archive .sif file, delete the Integration Object(s) and then re-import the archive .sif file back into Siebel Tools. This step is necessary to ensure the new repository objects have a unique ROW_ID. This will require the SRF to be compiled.

 

How to Use to Generate RTF Template?

Generate BAS File

First, you need Actuate BAS file to convert to the RTF Template. The Actuate report definition is stored in a ROD file so you need to generate the BAS from the ROD if you don’t have yet. You can generate the BAS files with Actuate eReport Designer. Here is the basic steps.

1. Open one of your ROD files in the Actuate eReport Designer
2. Click on Report-Build or press Shift+F8

This will generate BAS file under the same directory where the original ROD (xxx.rod) file is located.

Convert from BAS to BI Publisher RTF Template

Once you got the BAS file now you can call the script called ‘run’ to convert the BAS file to BI Publisher RTF template file. Here is the command line syntax.

run –i <bas filename> -o <output filename> -l <log filename>

Note: The option -i is a mandatory while -o and -l are optional. If you don’t specify then it will generate the RTF template and the log file to the location where you specify for the bas file location.

And here is the example:

run -i SALES.bas -o SALES.rtf -l Sales.log

The above conversion script also supports a batch conversion where you can convert multiple BAS files to BI Publisher RTF Template files. You can specify a folder that contains multiple BAS files instead of a file name. Here is the example.

run -i D:\Conversion\BAS -o D:\Conversion\RTF -l D:\Conversion\Log

Modify and Upload RTF Template

Now you get the RTF template file generated and you can start reviewing it and making necessary changes. Based on my experience it will take some effort to do this manual modification depends on how many scripts are in the original reports and how well the original reports have been kept maintained. Also the conversion utility sometimes generate some functions that don’t work. I will discuss about these manual modification stuff in another post.

Once the manual adjustments are done then you need to register this RTF template to the Siebel server with the Web client and associate it with a View where you want to run this report from.

I will talk about Siebel’s custom extended function next week, so stay tuned!

Can We Have BI Publisher Integration for Our Siebel Version without Major Upgrade?

Some of our customers were asking if they needed to upgrade to Siebel R8.1.1.x version from the older release such as 8.0.x, 7.8.x, 7.7.x, to get the new Siebel Reporting with BI Publisher. The short answers is No. You can stay with your older release.

Having worked with many customers projects, I know how tough and can be a long process to upgrade the whole system especially with this type of CRM application. However, the Actuate support through Oracle is expiring so immediate migration to BI Publisher is required.

So now, we have released the BI Publisher integration as part of the Siebel Fix Pack for the older release of Siebel application to provide the same reporting functionality with BI Publisher that is provided in the Siebel R8.1.1.x.

Now you can start enjoying all the functionalities that come with the BI Publisher integration by just applying the Fix Pack listed below.

Here is the list of the Fix Packs that contains the BI Publisher integration.

Release	Release Date	Functionality
8.0.0.8 Fix Pack	30th September, 2009	Same as 8.1.1.1 Fix Pack functionality
7.8.2.14 Fix Pack	16th October, 2009	Same as 8.1.1.1 Fix Pack functionality
7.7.2.13 Fix Pack	23rd November, 2009	Same as 8.1.1.1 Fix Pack functionality

The latest Siebel Reporting with BI Publisher is 8.1.1.1, which added some new features like, Parameterized reporting, Scheduling. You should be able to find the Fix Pack from the metalink.

Again, the Actuate support through the Oracle support contract is expiring so I’d strongly recommend to upgrade to the version above by applying the corresponding Fix Pack so that you can start migrating the existing Actuate based reports to BI Publisher based reports

All the out-of-box reports have already migrated to BI Publisher based reports and available within the Siebel application. So you need to do the migration only for the reports you have customized or developed from scratch.

I have talked about how to migrate the existing reports at the following two posts if you are interested in.

• Actuate to BI Publisher Reports Conversion for Siebel – Part 1
• Actuate to BI Publisher Reports Conversion for Siebel – Part 2

If you are interested in discussing how to start planning for the migration, best practice approach, outsourcing service, etc, we have many experience and resources in our Oracle BI Publisher consulting group to assist. Please feel free to contact me at kanichiro.nishida@oracle.com.

BI Publisher Logging & Debugging - Part 4

There are more topics I would like to talk about on the BI Publisher logging & debugging. Especially there is now a new feature that allows you to enable the 'Report Generation Logging’ option at each report template level. So instead of enabling the logging feature at the server level you can quickly and easily enable the logging feature only for the report you are working without asking for your system administrator’s help!

Also, sometimes you might wonder what exactly the property values are coming to the RTF template. So I also will talk about this in this post. So let’s start with the ‘Report Level Logging Feature’!

Report Generation Logging Option at Report Level

This is a new feature that has been introduced recently with either October or November patch set release. This gives you an option to set if you want to enable the logging and what level of information at the report template level.

I have talked about how to enable the reports generation logging before and that is a setting at the BI Publisher server level. So if you have enabled the feature then all the reports start generating the log when they run.

While enabling the logging feature at the server level is useful when you want to debug all the reports at a testing environment, it might not be ideal when you want to debug only a particular report without impacting other reports’ performance. In order to turn on or off the logging feature at the server level you need to restart the server, which again might not be ideal when you need to find and ask the system administrator and wait for the next restarting schedule.

With this report level setting you can simply enable the report logging feature only for the reports you’re interested in and no need to wait for the server restart. 

How to Set the Logging at Report Level?

You can simply add a BI Publisher command called ‘xdo-debug-level’ in your RTF template. Here is an example.

<?xdo-debug-level: debug_level?>

The ‘debug-level’ should be replaced by one of the following options.

• STATEMENT - This log write all the reporting running process information.
• PROCEDURE - This will log only the procedure level information. This is required to measure the time consumed per each process.
• ERROR - This will log only the error related information.
• OFF - This will not log any information.

Example:

<?xdo-debug-level:"STATEMENT"?>

The debug_level value is not case sensitive so it can be either upper or lower case. For example, <?xdo-debug-level:"STATEMENT"?> or <?xdo-debug-level:"statement"?>.

The debug log directory will be generated in one of the following locations:

• <system-temp-dir>/xdodebug
• <java.io.tmpdir>/xdodebug (if system-temp-dir is not defined

Again, this setting can be done at the template level, this mean you can either modify the existing RTF template by adding the above command or create a new template which contains only the above command if you’re interested only in debugging the data model or parameter values generation part.

How about XSL Template?

If you are using the XSL Template instead, then you can add ‘<!--xdo-debug-level="statement"-->’ in the first 500 character of the template file.

How to See What Property Values are Passed in RTF Template?

Sometimes you wonder what exactly the values are passed to your RTF template. For example, when you set a custom variable at the server level and try to get the value to do a further operation in the template, it might work or not work the way you wanted. And you want to see if the value is really set and being passed to your template.

There is a very useful command to address this concern. You can simply type the following command in your template.

<?xdoxslt:getXDOProperties($_XDOCTX)?>

And you can upload the template and run from the BI Publisher Server UI. You should get something like below in your report output. 

xslt.CURRENT_SERVER_SCHEME='http' html-image-dir=C:\OracleBI\oc4j_bi\j2ee\home\applications\bip\xmlpserver\xdo/tmp/ fo-external-link-target=_blank html-css-dir=C:\OracleBI\oc4j_bi\j2ee\home\applications\bip\xmlpserver\xdo/tmp/ system-temp-dir=C:\OracleBI\oc4j_bi\j2ee\home\applications\bip\xmlpserver\xdo/tmp/ xslt.XDO_USER_ROLES='XMLP_TEMPLATE_DESIGNER,XMLP_ANALYZER_EXCEL,XMLP_ADMIN,XMLP_ANALYZER_ONLINE,XMLP_DEVELOPER,XMLP_SCHEDULER' xslt.CURRENT_SERVER_PORT='9704' xdk-secure-io-mode=true xslt.CURRENT_SERVER_URL='http://knishida-lap.us.oracle.com:9704/bip/' fo-external-link-base-url='http://knishida-lap.us.oracle.com:9704/bip/POC/' xslt.XDO_USER_NAME='administrator' xslt.HOSTPORTPATH='xdo.us.oracle.com/temp' xslt._XDOCHARTTYPE='image/png' xslt.CURRENT_SERVER_CONTEXT_PATH='bip' xslt._XDOOUTPUTFORMAT='text/html' xslt.HTTPSERVER='knishida-us.oracle.com:9704' html-css-base-uri=http://knishida-lap.us.oracle.com:9704/bip/xdo/tmp xslt._FURL='http://knishida-lap.us.oracle.com:9704/bip/POC/' xslt._xdo_user='administrator' xslt._xt='New Template 1' xslt._XDOHTTPSERVER='knishida-us.oracle.com:9704' html-image-base-uri=http://knishida-lap.us.oracle.com:9704/bip/xdo/tmp/ xslt.CURRENT_SERVER_NAME='knishida-lap.us.oracle.com' xslt._xf='html' xslt._XDOLOCALE='en-US' xdodebug.LogDir=C:\OracleBI\oc4j_bi\debug

All the above variables are available for you to use in your RTF template. And you can see the values if they are the ones you were expecting or not.

BI Publisher Logging & Debugging – Part 3

Yesterday, I have discussed how to enable and use the BI Publisher’s reporting level logging feature. Today, I would like to talk about how you can enable and use the BI Publisher Server level log information.

At default setting BI Publisher logs only error information in a system output. You can enable BI Publisher to generate more information in the system output by setting its debug level to ‘Debug’. The default is set to ‘Exception’.

Once you have set the BI Publisher Enterprise Server instance to be debug mode then it will start generating more information about the system level process in a system log file. If your BI Publisher is running on the OC4J server then you can find the log information in the OC4J console window.

Configuration for BI Publisher Server Level Log

Here are the steps to change your BI Publisher Enterprise Server to be in a debug mode:

1. Login to BIP as Administrator
2. Select Admin -> System Maintenance -> Server Configuration
3. Change Debug Level to ‘Debug’ under General Properties
   
   
   
4. Click on the ‘Apply’ button and restart the server.

Now you should start seeing more information than when the ‘Debug Level’ is set to ‘Exception’ in the application log file.

Where Can I Find the Log with OC4J ?

When you are running BI Publisher Enterprise Server on OC4J it generates system logging information to the OC4J console window. However, the problem is that the log information keeps being printed in the console but it doesn’t get logged into any log file on a file system. So unless you’re sitting in front of the server machine and be able to access to the console window, it’s not really useful. As an alternative, you can spool the console log information into a text file by updating the OC4J startup command file. Here is an example.

<original> "%JAVA_HOME%\bin\java" %JVMARGS% -jar "%OC4J_JAR%" %CMDARGS% => <updated> "%JAVA_HOME%\bin\java" %JVMARGS% -jar "%OC4J_JAR%" %CMDARGS% > oc4j.log

 

Are There Any Other Log Files ?

If you are running BI Publisher Enterprise Server on a OC4J server the OC4J server itself also generates various log files at runtime. Among all the files the most help file would be the application.log file, which includes system level warning and error messages. For example, when some functionalities of the BI Publisher Enterprise Server are not working or the server was shutdown unintentionally you might want to check this file first. The log file is located under the BI Publisher Server application deployment folder, but you can view the same log file from Oracle Enterprise Manager’s UI window as well. The below is a list of steps on how you can open the file within the Enterprise Manger.

Steps to access application.log file:

1. Access to Oracle Enterprise Manager and login

   e.g. http://knishida-lap.us.oracle.com:9704/em

2. Click ‘Log’ link located at the bottom

   

3. Select Components -> OC4J -> home -> Application xmlpserver

   

4. Click ‘View’ icon

   

You can view the log in the EM window, or you can click the log file link to download the full content of the log file.

OC4J server also generates other system level log files. Here is a list of the log files that are generated at runtime and can be found at the specified location.

OC4J log files:

• OC4J components using Java logging : /log/oc4j/log.xml
• Application Server Control Console : /log/ascontrol-application.log
• Applications deployed into OC4J  : /application-deployments/app_name/application.log
• Global (default) application  : /log/global-application.log
• Default Web site access logging  : /log/default-web-access.log
• OC4J server  : /log/server.log
• JMS  : /log/jms.log
• RMI  : /log/rmi.log

You can find more information about the OC4J log file in ‘OC4J Administrator’s Guide’.

Conclusion

BI Publisher offers a wide range of logging capability that is useful for you to perform a debugging and analysis when you encounter an error or issue. It is very critical to understand what type of information is available, when to use them, and how to analyze them to help addressing your problems. Now you have all the information, so have fun debugging and tuning!

BI Publisher Logging & Debugging - Part 2

This is a follow up from the yesterday’s post. Now you know what type of logging options are available for you to debug and further analyze your BI Publisher reporting.

Today, I’d like to focus on the first debugging option, ‘Logging for Reports Generation’ and discuss how to enable your BI Publisher instance to start generating such logging information.

You can enable the logging feature by preparing a appropriate BI Publisher debug configuration file. This is a manual work and not be able to set from its administration UI. Once you have configured it requires the server to restart.

Configuration for Enabling Report Generation Log

Here is the steps to follow to enable the ‘Logging for Reports Generation’.

Steps to Enable Report Generation Debug:

1. Create a directory where you want the log files to be stored.
   e.g. D:\BI_Debug
   
2. Identify the JDK home, which is used by your J2EE Server, such as OC4J, on which BI Publisher Enterprise Server is running.

If you are not sure about where is your JDK home is, here is an example of how you can find the JDK home. When you have installed BI Publisher as part of BI EE suite and with embedded OC4J server then you can find the JDK home in the OC4J launch program. The launch program, oc4j.cmd, can be found at

<ORACLE_BI_HOME>\oc4j_bin\bin\oc4j.cmd

e.g. C:\OracleBI\oc4j_bi\bin\oc4j.cmd

When you open the file find ‘JAVA_HOME’ entry in the file, which shows the JDK_HOME location.

Example:

:::::::::: START CONFIGURATION SECTION :::::::::::::::::: ::::::::::::::::::::::::::::::::::::::::::::::::::::::::: set ORACLE_HOME=C:\OracleBI\oc4j_bi set JAVA_HOME=C:\Program Files\Java\jdk1.5.0_16 set J2EE_HOME=%ORACLE_HOME%\j2ee\home

Once you find the JDK home location, follow the next steps.

1. Move to <JDK_Home>\jre\lib
2. Create a text file called: xdodebug.cfg
3. Add the following 2 lines in this file:
   

   LogLevel=STATEMENT LogDir=<BI_Debug_Log_Directory>

   Example:

   LogLevel=STATEMENT LogDir=D:\BI_Debug

4. Save the file at the <JDK_Home>\jre\lib
5. Restart your J2EE Application Server such as OC4J server.
   This will enable the Java runtime that is used by BI Publisher Enterprise Server to identify the configuration file xdodebug.cfg

With the above configuration BI Publisher Enterprise Server will start generating ‘various files’ under the specified log folder. I’ll expain what are those ‘various files’ below.

BI Publisher Reports Generation Log File and More

Once you enabled the report generation ‘Debug’ mode as described above BI Publisher Server now starts generating the following output files under the folder that you have specified in the xdodebug.cfg file.

• xdo.log
• XML Data file
• Template file(RTF, etc)
• XSL file
• XSL-FO file
• Formatted FO file (with Data)
• Final Output file

Among all the above files the file you would mostly need and appreciate is ‘xdo.log’ file, which logs all the reports generation processing related information including a data generation and a report output generation. You can also find all the temporary files, which are generated during the report generation process such as XML data file, XSL file, and FO file, and the final output file under the same log folder. I’ll explain each of the files in the following sections.

What Can Each File Do for Me?

Main Log File - xdo.log

This is a main log file that logs all the reports generation processes related information. While various things you can find from this file there are a couple points that would be very valuable for you to review.

Fist, you can find SQL queries and parameter values that are used to retrieve data from a database. Second, you can find report layout generation related information, which tells you errors/exceptions with your RTF templates if any. Third, you can find a total time spent for each of the report generation processes, such as retrieving data from data source and generating a XML file, converting from RTF template to XSL-FO, merging XSL-FO with the XML data to generate FO and generating a final report output.

The below is a list of information you can find in the log file.

Data Generation Layer

• SQL Queries for Parameters
• SQL Queries for Report Data
• Parameter Name, Default Value
• Assigned values for Parameters

Report Layout Generation Layer

• Processing Information by RTF Processer: Converting from RTF template to XSL-FO style sheet
• Warning about RTF Template
  (e.g. “Old RTF version detected, nested table disabled”)
• Processing Information by FO Processer: Matching with the data and generating a final report output

Summary Section

• Total time for Data Generation
• Total time for converting RTF to XSL-FO
• Total time for merging XSL-FO with Data to generate FO
• Total time for generating a final output such as PDF, HTML, etc

 

XSL-FO(xdo_…rtf_out.xsl)

This is a XSL-FO style sheet, which is converted from a RTF template file by BI Publisher’s internal RTF processer. You can analyze this style sheet file to see how your RTF template is converted and how each item in the RTF template has been translated into XSL/XPATH/FO languages.

XML Data File(xdo_…fo_data.xml):

This is a XML data file that is generated by BI Publisher based on the sql queries used for the report. You can verify this file to see if the data is retrieved from databases and presented in the XML file as you expected. Also you can use this file to preview the report output with BI Publisher Template Builder (MS Word Add-in) for your report output review.

RTF Template(xdo_….rtf_template.rtf):

This is the RTF template file that is registered with this report and used for this report generation. It should be as same as the one you have uploaded and the one you can download from the report edit window in BI Publisher Enterprise Server UI. You can use this RTF template with a combination of the above XML data file to preview the report output using BI Publisher Template Builder (MS Word Add-in) for further analysis.

Output File (xdo_…out):

This is a final output file that is generated by BI Publisher. While the file extension is always ‘.out’ the file content format depends on the report output type that is chosen at the runtime.

For example if the output was selected as HTML then the output file format is HTML. You can change the file extension to be ‘.html’ and open in the browser to see the report output. If the output was selected as PDF, the output file contains PDF binary data and you can open the file after you change the file extension to be ‘.pdf’.

Conclusion

Now you have much better understanding behind the BI Publisher’s reporting process and great tools to analyze your report generation process and the performance.

I will discuss on how to enable the ‘BI Publisher Server Level System Log’ and how to use it tomorrow, so stay tuned!

BI Publisher Logging & Debugging – Part 1

After you develop your BI Publisher reports next you want to do is to test your reports to see if they work appropriately, if they perform fast enough to meet the end user’s expectation, if they scale when more users access to the reports, etc.

And when you test your reports you need more information than what you see from the BI Publisher Server’s UI. This is where the BI Publisher’s logging and debugging feature comes in.

My team has been spending many times on troubleshooting and performance tuning to assist our clients to deliver the BI Publisher reports to their production and go live. And always first thing we do is to collect all the possible logging information and analyze what is happening at the reports generation time.

In order to do so, we first need to know what kind of logging feature is available with BI Publisher Server and how we can use it to help us perform the troubleshooting and performance tuning.

So, I’m going to cover this subject for the next few days.

What is Available?

• Logging for Reports Generation
• Server Level System Log

There are two types of BI Publisher logging frameworks. One is to log about its report generation process. Another is to log about its server (or system) level process related information including error, warning and exceptions.

Logging for Reports Generation

The report generation process log provides you information about how each report is generated including a data generation process and a report layout generation process. When BI Publisher generates a report output, first it retrieves a data from a specified data source. And then it applies a report layout to the data and converts it to a final report output. If your report’s layout template is RTF template, which is very common with BI Publisher reports development, then BI Publisher converts the RTF template to XSL-FO style sheet before it applies the style sheet to the data. Therefore when you want to debug or analyze the report generation process it is very important to separate each of the above mentioned layer so that you can narrow the point of the error or bottleneck of the issue and will be able to have better understanding of what is going on in side the report generation process. Anyway, you can find the following processes related information in the report generation process log.

• SQL query submission process to retrieve a data
• Conversion process from RTF template to XSL-FO File
• Data merging process with XSL-FO
• Conversion process from XSL-FO to final outputs such as PDF.

Each process shows the detail of the transactions and is tracked with time stamp in the log file. You can also find a total processing times for both data generation and report layout generation as summary.

Logging for Server

The system level log provides you an information about server level process such as scheduler, delivery, database connection, application server level integration, security etc.

I will talk about how you can enable the above logging capability and how you can use to address your problems tomorrow, so stay tuned!