BI Publisher: Converting Report6i reports - Pitfalls and Suggestions

Yes there are pitfalls! I have seen some questions in the bi publisher forum about converting all of a companies "existing report6i code" to BI Publisher. Don’t get me wrong the tool is a great, but like any tool it has some issues.

Here are some common elements as far as time is concerned for report development in general. These estimates are based my experience writing reports in the 11i e-business suite.

New Reports – From dev -->test -->production typically development time is about 40 hours.
Fixing an Existing report – If you’re lucky, 2 minutes but on average 5 about hours.
Converting/Copying an existing report 10-15 hours
Converting reports6i to BI Publisher
Small report 10 hours
Large report 20-40 hours, it can get ugly!

You may be asking yourself, what in the duce? How is that possible that converting a large report could take upto 40 hours? There are some bugs in the conversion tool. As example:

Reports6i:
Function get_item_no return char
Is
l_item_no varchar2(30);
Begin
Select item_no
Into l_item_no
From ic_item_mst
Where item_id = :item_id;

Return l_item_no;
End;

Conversion to PL/SQL:
Function get_item_no (item_id in number) return char
Is
l_item_no varchar2(30);
Begin
Select item_no
Into l_item_no
From ic_item_mst
Where item_id = item_id;

Return l_item_no;
End;

Data Template Issues
In the converted PL/SQL example you can see the problem in grey. Sometimes during the conversion the parameters end up having the same name to a joining column. Ouch, get_item_no will actually compile too (note: it really shouldn’t in "theory" since item_id is “technically” ambiguous). Now were left with a situation where that function will blow up 100% no matter where it’s called from. Some reports can have over 50+ functions. When this issue occurs you have to fix each query and narrow it down in the data template.

Format Template Issues
Another kind of nasty issue is caused by sloppy development in reports6i with srw.set_field(….). This "was" a way of changing the data on a field in the report format trigger. It’s a big no, no. Well, guess what, set_field never gets converted to the format template so we have to fix the issue manually. Something like this is really easy to slip through the cracks. Also, did I mention that all the logic in your format triggers to suppress fields or groups, change colors, fonts, etc doesn’t get converted as well? Now that will take some dev time!

Conclusion
The one thing to come away from this article is this: Don’t fix things that aren’t broken (it’s easy to say, hard to practice). It’s a lot of work to convert a large report and troubleshooting the conversion issues will cost you time and money. Also, expect to feel a majority of the pain in the format template conversion. Formatting is the number one challenge for any report!

When the conversion tool becomes a more mature product we can expect the conversion to be better and it “might” make sense to convert more of your organizations reports. The only justification for this would be either lower cost of support or a disappearing reports6i skill set in your organization/development community. With that in mind, if you don’t have a choice and you have to convert a report, the tool does a pretty good job and it’s better than nothing.

PL/SQL: Taking it to the next level.

Disclaimer: The PL/SQL Examples presented here are theory and will not compile. However there are plenty of resources from oracle on this subject.

Ever wonder how you could query a pl/sql table? Well there is a way to do this now, since 9i. You must define a function to be “pipelined.” This is one of the PL/SQL’s more powerful abilities, ironically, it’s rarely used, it’s relatively unknown in the PL/SQL Development community. If you talked to your top PL/SQL guru and said, "Hey man these pipelined functions could be really awesome for reporting." The response from your guru would be this, "How dare you feel my head with such vile and lonesome propaganda!"

Essentially, for report writers this opens up some doors that we haven’t ever thought about or wish we had when writing some really challenging reports. This really comes in handy when your working with tough data models. With pipelined functions there is no need for staging tables, long concatenated lexical’s or multiple functions.

While a function will only return one value, there is an exception to this rule which is a pipelined function which can returns n-values. The pseudo syntax for defining a function of that nature is below.


function get_valid_employee_table (p_dept in varchar2) RETURN emp_tbl_type PIPELINED
is
l_emp_rec emp_pkg.emp_rec;
begin
loop through some pl/sql table that you pre-populated in your before report trigger or the calling program
l_emp_rec := some_pl_sql_table;
if ..
--do something
pipe row(l_emp_rec);
elsif ..
--do something
pipe row(l_emp_rec);
else
--do something
pipe row(l_emp_rec);
end if;
end loop;
return;
end get_valid_employee_table;


Sample Call:

select *
from TABLE(emp_pkg.get_valid_employee_table(‘finance’) managers
where managers.employee_id in (select employee_id from employees)

The inside skinny: When, Where and Why

When:
1. You want to avoid a huge concatenated lexical variable in your where clause that will cause performance issues.

2. There is a need for staging table. Typically, with staging tables your already be storing the data in memory so why make the report transaction based. Note: Staging tables in reports are caused by a bad data model or unruly requirements….it can happen :-)

Where:
1. Code this in your pl/sql package.
2. Use it either for reporting (see when) or an ETL Process.

Why:
1. There is no need to create half a dozen functions, you can create a record and call one function and cast it as a table. Boom, you saved yourself some redundancy in your query and in PL/SQL
2. Writes to disk are eliminated, performance plus
3. No need to manage transactions in a report (ie commit/rollbacks into staging tables). Less complex report…
4. Complex calculations can be preformed in SQL natively
a. ad-hoc counts
b. group by
c. joins/outer joins to sql tables or even other parallel enable functions.
5. Technical this can be viewed as polymorphism. Since its code that is logical being represented as a table or view (yes can you put in a view) it can be queried n- possible ways with your limitation only being that of SQL and or PL/SQL. Think about it...


Send me a shout! Is there a mistake or would you like to comment about this article?