Wednesday, March 12, 2008

BI Publisher: BIPublisherIDE

BI Publisher Integrated Development Environment

If you ever wondered how the BI Publisher API's worked then look no further. This is the ultimate blog on how the API's and there is a free working code base you can download and use for development.

There are four caveats:

1. This code is free but cannot be copyrighted, unless your oracle (but you need to discuss terms first with me)

2. I don't think I can distribute xdocore.jar file, you will need to download either in the latest patchset (ebs) or standalone version.

For 11i environment, I would suggest downloading patch: 5472959. If you put the j5472959_xdo.zip file in c:\JDeveloper\jdev\mywork\BIPublisherIDE\BIPublisherIDE\lib
the project should work.

3. If you improve the project you should share the information here in this blogpost.

4. If you download the project please post a "positive comment." Kidding...but it would nice!

History

Long ago, I was doing a tremedous amount of BI Publisher development inside the EBS and Oracle Portal. One of the problems I was having is that BI Publisher development can be time consuming when you continually have to reload your format template, data template into the database and re-iteratively submit concurrent requests.

I was also faced with other issues as well, what if I wanted to test eText and Bursting locally? There was no option to do this any product. The only way to test eText is start->programs- your bip installation-> run template viewer


How It Works: Architecture


The BIPublisherIDE is setup for a developer by a developer (it's just like the hair club for men, "I'm not only a member but I'm also a client.")

There is a config class, api class and utility class.

config->api->utility

1. The config class contains methods and creates connections to databases.

2. The api's class extends the config class and inherits the methods and has the ability to override the methods. This class contains all of the bi publisher core api's.

3. The utility class extends the api class, put simply the utility class is a wrapper. It sets the foundation for which api's to call based on what is set in the config file (xml).

The design is intentional and sets a precedence for doing report development. As developers we iteratively run unit tests till we receive the desired result. One of the most cumbersome problems we face is that there is a lot of "non-value" added steps in report development process. This is why report development to a certain degree needs to be configurable and run locally.

Other benefits of the design is that fact that it's not only a development tool, it can be deployed in other environments. For instance you can deploy the BIPublisherIDE jar file in a concurrent program or other web based environment like portal, websphere, etc (this might be helpful for apex too).

How to run the IDE (BIPublisherIDE.pdf)

Download the IDE (BIPublisherIDE.zip)

Next Article to Come: How to display a BIP Report using the OA Framework (i.e: like the PO)

25 comments:

Anonymous said...

I happened upon this but when I try to run it in Jdev 10G (10.3~) I am getting a java.lang.error - Any ideas?

Ike Wiggins said...

It's the JRE. Make sure the project settings are pointing at the right directory.

Erik Ykema said...

Hi, the links are dead. Are the files still available somewhere?

Ike Wiggins said...

Hi erik, can you be more specific as to what links are dead?

Also, if you downloaded the BIPublisherIDE yesterday I would suggest re-downloading it, I made some changes to fix a bug I introduced.

Gareth said...

Hi Ike,

Finally had a chance to look at this very briefly. Scenario was I had an eText template and data xml file and wanted to test output generation. So I:
1. Installed the zip into R12 RUP6 jdev.
2. Put xdocore.jar in path from standalone BIP install
3. Configured xmlTemplateConfig.xml
4. Put my eText RTF and xml data as output_file_name.xml in the directory specified in xmlTemplateConfig.xml
5. Commented out the "if" statement block by "generate the xml file"
6. Ran XMLPublisherUtility successfully to generate the output file.

Would be good to have a "skip data generation" param in xmlTemplateConfig.xml.

Other than that, excellent work!

Regards,
Gareth

Ike Wiggins said...

Hi Gareth!

Thanks for kicking the tires so to speak. Nice work getting everything up and running.....

I think if you don't populate the DATA_TEMPLATE_NAME field with a value it should handle your scenario. Either way I'm glad you got it to work.

Thats the nice thing about being able to see the code, you can change it as you see fit.

Their is another GUI version I have developed that I'm waiting for OTN to publish. It has a performance problem when displaying the log file and a couple of little bugs I need to fix.

Ask Tim D. about it.

Bin:

http://www.mediafire.com/file/detjohwunz1/BIPublisherToolBox.zip

Src:

http://www.mediafire.com/file/nz2ohzy2g2z/BIPublisherToolBoxSrc.zip

Gareth said...

Hi Ike,

Would be good to get XSL-FO option into the bipide.

In the meantime I just threw in an XSL option:

1. Don't generate XSL from RTF when XSL template type (1 line change in XMLPublisherUtility.java):
Change
/**@generate the xsl file */
if (getStatus().equalsIgnoreCase("SUCCESS") && getFormatTemplateName() != null && !getTemplateType().equalsIgnoreCase("ETEXT") && !getTemplateType().equalsIgnoreCase("TRANSFORM_EXCEL") && !getTemplateType().equalsIgnoreCase("EXCEL_TO_XSL") && !getTemplateType().equalsIgnoreCase("BURST"))
To:
/**@generate the xsl file */
if (getStatus().equalsIgnoreCase("SUCCESS") && getFormatTemplateName() != null && !getTemplateType().equalsIgnoreCase("ETEXT") && !getTemplateType().equalsIgnoreCase("TRANSFORM_EXCEL") && !getTemplateType().equalsIgnoreCase("EXCEL_TO_XSL") && !getTemplateType().equalsIgnoreCase("BURST")&& !getTemplateType().equalsIgnoreCase("XSL"))

2. Change your xmlTemplateConfig.xml to:
< TEMPLATE_TYPE > XSL < /TEMPLATE_TYPE >

3. Leave your format_template_name in xmlTemplateConfig.xml as XXX.rtf as bipide appends with .xsl
< FORMAT_TEMPLATE_NAME > t.rtf < /FORMAT_TEMPLATE_NAME >

4. In the filesystem name your xsl file in as < format_template_name >.xsl ie. in my example XXX.rtf.xsl

Regards,
Gareth

Ike Wiggins said...

Gareth,
Thanks for the suggestion, will keep that in mind for future releases of the BIPublisherToolBox. Keep me posted on any other area's of improvement. Also, if you would like to collaborate on the GUI version of the BIPublisherToolBox (formally known as the BIPublisherIDE) let me know.

Ike Wiggins

Anonymous said...

Hi, stumbeld on this and I would like to try this. Can you supply the xmlTemplateConfig.xml and xmlpublisher utility files? They do not seem to be in the zip file. Is there any way to install this tool? The installation steps are a bit unclear as to what to do.
Keep up the good work

Ike Wiggins said...

The xmlTemplateConfig.xml is provided. It should be under the /src/bipublisher directory.

The BIPublisherIDE is java app. It's setup for JDeveloper10g. If your uncomfortable with java there is a gui option that's also available.

http://www.mediafire.com/file/detjohwunz1/BIPublisherToolBox.zip

Unknown said...

Hi Ike,

Thanks for the posting. I have a requirement to generate a Bi Publisher report in realtime without going to concurrent manager. I don't know Java so where should i start with. Should i go ahead and start using your IDE but the only issue is support if something fails. Other option is loading the Bi Publisher jar files in database and start using them.

Which route do you suggest. Can you give me the list of jar files I need to load into the database.

Ike Wiggins said...

I would start off with that and then look using OAF to display the report.

Look at this post. Unfortunately, the images disappeared.
http://bipublisher.blogspot.com/2008/03/bi-publisher-bip-in-oa-framework-part-1.html

When your done with that look at this one:

http://bipublisher.blogspot.com/2008/03/bi-publisher-bip-in-oa-framework-part.html

sateesh said...

Hi Ike,

This is Sateesh. This blog is really nice. Very nice articles on BI Publisher are being published regularly.

I am facing one issue while using the application "BIPublisherToolBox".

Please find the steps that I have followed:

1) I extracted the zip folder. Opened the bin folder and started the “BIPublisherToolBox” directly as I am already having JRE version 6.

2) The application opened successfully and I have added a new data source by giving the appropriate information.

3) Clicked on “New” button and entered the values for Template_type, Format Template, Data Template, Output Format, Output directory, Output file, Upload lob code, Upload short name, Paramater1 (deptno) and finally Parameter Value 1 (10). Remaining all fields left empty.

4) Later I clicked on “Upload Data Template” button and then “Upload Format Template” button.

5) Then I clicked on “Execute” button. The pdf file is generated successfully as expected under “D:\Value_additions\BI_Publisher_IDE\BIPublisherToolBox\corporate_examples\”

Issue:
--------
But Later I changed the Parameter 1 value from 10 to 20. Even then the application is taking the deptno value as 10 only.
The parameter value is not getting updated in the query. Please have a look at the log generated.

Exiting validateProperties()...
Entering initAppsContext()...
Registering Oracle JDBC driver...
Creating Oracle JDBC connection...
USE_APPS_CONTEXT is set to false. AppsContext won't be initialized...
Exiting initAppsContext()...
Entering resolveProperties()...
Resolving OWNER_ID. Calling OAHelper.getOwnerId()...
[102309_085805455][][ERROR] start of dataEngine
- - - - - - - - - - - - - - -
[102309_085805565][][STATEMENT] Inside properties Parser...
[102309_085805565][][STATEMENT] !!! Warning include_parameters=true Invalid property/value combination
[102309_085805565][][STATEMENT] include_null_Element=false
[102309_085805565][][STATEMENT] include_rowsettag=false
- - - - - - - - - - - - - - -
[102309_085805565][][STATEMENT] Inside parameterParser...
[102309_085805565][][STATEMENT] Parameter:NAME Default value: Vision
[102309_085805565][][STATEMENT] Inside dataQueryParser...
[102309_085805565][][STATEMENT] Inside dataStructureParser...
[102309_085805580][][STATEMENT] Group ...report
[102309_085805580][][STATEMENT] Group ...DEPT
[102309_085805580][][STATEMENT] Group ...EMPLOYEE
[102309_085805580][][STATEMENT] Template parsing completed....
- - - - - - - - - - - - - - -
[102309_085805596][][STATEMENT] Writing Data ...
[102309_085805690][][STATEMENT] Sql Query :Q_DEPT: SELECT DNAME, DEPTNO
FROM dept
ORDER BY dname
[102309_085805752][][STATEMENT] Sql Query :Q_EMPLOYEE: SELECT DEPTNO AS DEPARTTNO,EMPNO, ENAME, JOB, SAL
FROM emp
WHERE deptno = :deptno
ORDER BY ename
[102309_085805752][][STATEMENT] 1: deptno:10
[102309_085805768][][EVENT] Data Generation Completed...
[102309_085805768][][EVENT] Total Data Generation Time 0.0 seconds
[102309_085805783][][ERROR] end of dataEngine
- - - - - - - - - - - - - - -

What to do in order the generate the outputs by giving the different parameter values each time I execute the report.


Please help me in resolving this issue.

Thanks in advance.
Sateesh.

Ike Wiggins said...

Hi Satesh,

That's an old version of the ToolBox. Try this one. The performance will be much faster.

http://web.me.com/beth.wiggins/ike/bipublisher/BIPublisherToolBox.zip

See if the issue still exists. If it does I'll need to get some more info. When you update the parameter field you need to tab out of it and save.

sateesh said...

Hi Ike,

Thanks for your reply.

I have downloaded the new BIPublisherToolBox.zip from the link that you have mentioned in your last comment.

But this time when I try to upload the data template/ format template, it is giving the following error.

------ - --- ---- -----
Entering initAppsContext()...
Registering Oracle JDBC driver...
Creating Oracle JDBC connection...
USE_APPS_CONTEXT is set to false. AppsContext won't be initialized...
Exiting initAppsContext()...
Entering resolveProperties()...
Resolving OWNER_ID. Calling OAHelper.getOwnerId()...
Exception in thread "AWT-EventQueue-0" java.lang.NoClassDefFoundError: oracle/jbo/domain/BaseLobDomain
at oracle.apps.xdo.oa.util.XDOLoader.resolveProperties(XDOLoader.java:824)
at oracle.apps.xdo.oa.util.XDOLoader.init(XDOLoader.java:455)
at oracle.apps.xdo.oa.util.XDOLoader. (XDOLoader.java:421)
Caused by: java.lang.ClassNotFoundException: oracle.jbo.domain.BaseLobDomain
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source) ------ - --- ---- -----

As per my understanding, application searches for the required class under the \lib\ folder right?
Please tell me where to get the missing class/ jar file. If you have, please provide me the link.

I have one more doubt. While entering the details for creating new template configuration row, by tooltip message I understood that the value for the field “Upload Lob Code” should be present in xdo_lobs table. Is this right? What needs to be exactly entered in the “Upload Lob Code” and “Upload Short Name” fields?

By clicking on Upload Data Template/ Upload Format Template button, are the corresponding template details stored in the database tables? In that case in which tables will those details are stored?

Note: without uploading the data template/ format template, I clicked on Execute button. The application is running successfully and generated the pdf file.
So, I think some where it is storing the old values and using the same each time when I run the application.

Please help in resolving this issue.

Regards,
Sateesh.

Ike Wiggins said...


I have one more doubt. While entering the details for creating new template configuration row, by tooltip message I understood that the value for the field “Upload Lob Code” should be present in xdo_lobs table. Is this right?
yes, this is an optional feature of the toolbox.


What needs to be exactly entered in the “Upload Lob Code” and “Upload Short Name” fields?
You need to find the short name and application name in xdo lobs to use this feature of the toolbox.


By clicking on Upload Data Template/ Upload Format Template button, are the corresponding template details stored in the database tables?
No, only the data template and format template are stored.

In that case in which tables will those details are stored? XDO_LOBS


Note: without uploading the data template/ format template, I clicked on Execute button. The application is running successfully and generated the pdf file.
Yep, that is the way it is suppose to work. You do not have to have the format template or data template in the database.


So, I think some where it is storing the old values and using the same each time when I run the application.
Nope, the application does not store parameters in the database

Question:
When you updated the parameter field did you to tab out of it and save?

Ike Wiggins said...

Sateesh,

I finally narrowed this down to user error. If you want to make the data_template parameterized you will need to do that on your own.

The data template is designed to select all of the depts. If you want to change that go an ahead.

I just tested it and it works. Here are the steps.

Add the parameter in the data template.

<parameter name="P_DEPTNO" dataType = "number"/>

Change the query:

SELECT DNAME, DEPTNO
FROM dept
WHERE DEPTNO = :P_DEPTNO
ORDER BY dname

Update the toolbox row:

Add the parameter 1 name: P_DEPTNO
Add the parameter 1 value: 20

It works.

Ike

sateesh said...

Hi Ike,

Thanks for your suggestion.

I have followed the steps that you have suggested and changed the query.
I have added the parameter in the data template

parameter name="deptno" dataType = "number"

Changed the query which is under “Q_DEPT"

SELECT DNAME, DEPTNO
FROM dept
WHERE DEPTNO = :deptno
ORDER BY dname

The query under "Q_EMPLOYEE" is
SELECT DEPTNO AS DEPARTTNO,EMPNO, ENAME, JOB, SAL
FROM emp
WHERE deptno = :deptno
ORDER BY ename
----- ----- ---
group name="DEPT" source="Q_DEPT”
element name="DEPT_NAME" deptNo="DEPTNO" value="DNAME"
group name="EMPLOYEE" source="Q_EMPLOYEE"
element name="EMPNO" value="EMPNO"
element name="ENAME" value="ENAME"
element name="JOB" value="JOB"
element name="SAL" value="SAL"
--- ------ ------ ----


This time the parameter value is getting changed when I update it from the application as expected. Thanks.

But the new problem now is, Say for example for the dept 20 if there are 6 rows in emp table. Only the first row results are being displayed in the output.

Do observe the data template XML code provided above and suggest any changes so that when I give parameter value for the dept as 20,
I should get the all the 6 rows results in the output.

Thanks and Regards,
Sateesh.

sateesh said...

Hi Ike,

The problem is resolved.

For the problem to be resolved, I have modified the "dept_employee.rtf" file a little bit.

Now I am able to generate the output in different formats and with different parameters each time I run.

Great application which helps in BI publisher reports development/ testing made very faster.

Thank you very much for your support!

Thanks a lot.

Regards,
Sateesh.

Ike Wiggins said...

Thanks Sateesh. Let me know if you have any other questions or need some help.

sateesh said...

Hi Ike,

It would be great if you provide the .jws file, .jpr file and the related sources in the appropriate folder structure as you provided for BI Publisher IDE. So that I can deploy the same in jdeveloper and modify the sources if there are any enhancements I wish to add in future.

I have generated the report outputs for the template type "RTF". But still there are two more template types in the list which are "BURST" and "ETEXT".

Could you please provide the steps to follow to implement the Bursting and Etext.

Thanks for understanding.

Regards,
Sateesh.

Ike Wiggins said...

An OTN article ill get published sometime this week that will have more instructions on how to run the program. I'll post it in the comments and write up an post about it.

If your looking to learn more about bursting and etext I would suggest that you read oracle's bip documentation. It's an excellent resource. Also, Kevin Woodrow has a nice blog on etext. http://kwoodrow.blogspot.com/

As for the source, you can modify but your on your own....Well technically your on your own either way since this isn't supported by oracle.

http://web.me.com/beth.wiggins/ike/bipublisher/BIPublisherToolBoxSrc.zip

sateesh said...

Hi Ike,

Thank you very much for providing the sources.

Please share the link when you publish an OTN article.

Thanks a lot for your help!

Regards,
Sateesh.

Anonymous said...

Hi there,

Thanks for sharing the link - but unfortunately it seems to be not working? Does anybody here at bipublisher.blogspot.com have a mirror or another source?


Cheers,
William

Ike Wiggins said...

It's been replaced by the toolbox. Click on the downloads link at the top of the page.