Bi Publisher and nested tables and loops

One thing I have noticed while developing RTF templates is that it always seems to be in your favor to try to use tables to structure your data. Not only this, but using or not using tables, depending on your XSL logic, can actually greatly change the output of your BI publisher report.

The following post will illustrate a few things I went through while developing a report that had a strict heirarchy. Now, this report is based off of an Oracle Apps Bill of Lading, and combined a bit with a Sales Order Acknowledgement document, so it might not look familiar. The point is, there are a few steps in understanding how to get your report looking right.

1. Understand what data needs to be on the report and HOW it needs to look and be structured.
2. Become familiar with the data and how it is structured hierarchically or if at all.
3. Become intimately familiar with your query or tool (like Oracle Reports) that needs to generate
the XML that your BI Publisher template will merge with.
4. Review your XML that is merging with the template so you truly understand what data is at what
level in the hierarchy, and where it might repeat as a group.
5. Learn to re-structure your report or application that serves the XML so it fits your needs.
6. Finally, learn to use BI Publisher's templating ability (and tables) to produce the output in the right
format.

That being said, for our example we will look at a heirarchy as follows:
LPN (license plate number) is the master node or top-most level.
- Item Number and Item Description are just underneath LPN, and there might be multiple
entries PER LPN.
- Quantity, Lot Number, Heat Number, and Mechanical Number are all attributes of an item
number, and thus they can all appear multiple times PER item number.

The XML data might look like:

LPN- GDN0000124
Item Desc- 2.0000.PL.7050.T7451.ALUM.BARE.48.5000.144.5000
Item #- 600296
Heat Number- 398662
Mechanical Number- 339394
Lot Number- 027.500/R014.2506276-A00
Quantity- 77
Unit of Measure- LBS

Note that this is the easiest situation, 1 LPN, 1 'set' of item description and item number, and 1 'set' of the item's attributes underneath. We need to design the BI Publisher template for a repeating possibility though, like:

LPN 1
ITEM 1
ITEM Attributes 1
.
.
ITEM Attributes N
.
.
ITEM N
.
.
.
LPN N

So multiple possible LPNs each with multiple possible items, each with multiple possible attributes. And yes, the people that read this report want it grouped nicely so that each attribute belonging to each item belonging to each LPN is 'grouped' and structured hierarchically. For any experienced reporting developer or any data analysis professional, this isn't too bad. However mastering this in BI Publisher with MS Word as your RTF Template tool, you really should master nested tables. The following is a screenshot of how we can get this to work in a report. It is a simple shot of the most basic situation with only 1 LPN, 1 item and 1 description. note the nested tables in this output:


The next shot is a more complicated output using the SAME template, this one having multiple items and descriptions for 1 LPN:



Again, notice the structuring of the tables I purposely left so you can see how this is built from top to bottom. You need to nest your tables hierarchically to get this structure to work.
Finally, the template and the XSL and code:


A few things to note in this quick template, we are indeed using barcodes as you might have noticed. Our initial for-each tag (in aqua) is outside the main table and will repeat basically for every single occurence of an LPN. Then we have a table nested inside the LPN table. This next FE (for-each) is shown in the 2nd nested table and it will repeat for each occurrence of an item number and item description pair. Finally, we nested a 3rd table inside the item's table, and it holds the item's attributes. The 3rd and final FE (for-each) will repeat for each Quantity, Lot, Heat, and Mechanical ID attributes it finds for the current item. You'll then see the EFE (end for-each) that stops the repeating for each of the 'FE' sections.

We also have a XSL tag that says split-by-page-break:
This is a tag that needs to be experimented with and placed JUST BEFORE the right EFE tag to see what it does. If done right, it will page break at the end of each of the elements for the EFE it proceeds. In this case, the tag is just before the last EFE which is related to the initial FE which if for the repeating LPN. So it will page break for each LPN we encounter...and it does. It keeps all items and attributes together but only makes a new page when the LPN changes.

Hope this helps!

Oracle Warehouse Builder OWB 11g New Features Part 4!

This is a continuation from part 3. We will continue to explore some new features this time of OWB 11g. Last time we touched on the Siebel Connector and some general connectivity options.

First, you should know that there are a few new features in OWB 11g that were rolled out in the latest OWB 10g R2 patch version 10.2.0.3. As follows:

• Version support for type 2 SCDs.
• Set based Delete in mappings code generation.
• Availability of a merge optimization property for table operators.
• DML error logging to handle error tables.

So what are these and how are they 'new'?

Versioning of Type 2 SCDs (slowly changing dimensions)
Hierarchy versioning supports multiple hierarchies. When the non-leaf level (think of a tree or maybe a bush...the leaves are the furthest from the root or middle) of a dimension contains versioned attributes, the versioning of this non-leaf level results in the versioning of its corresponding child records if they have effective date and expiration date attributes and if you have enabled hierarchy versioning. In essence, what can happen to 'Dad' will happen to his kids too.

Hierarchy versioning is not enabled by default for type 2 SCDs (slowly changing dimensions). When you create a type 2 SCD by using the Create Dimension Wizard, hierarchy versioning is disabled. You must use the Data Object Editor to enable hierarchy versioning.

So what is a Type 2 Slowly Changing Dimension?

In Type 2 Slowly Changing Dimensions, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key.

For example, say we have the following table:

Customer Key	Name	State
1001	Christina	Illinois

After Christina moved from Illinois to California, we add the new information as a new row into the table:

Customer Key	Name	State
1001	Christina	Illinois
1005	Christina	California

Advantages:

- This allows us to accurately keep all historical information.

Disadvantages:

• This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.
• This necessarily complicates the ETL process.

Usage:

• About 50% of the time.

When to use Type 2:

• Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

Set-based DELETE in Mapping Code Generation
Beginning in Warehouse Builder 10.2.0.3, you can design a mapping with the loading type set to DELETE and code generation set to SET BASED.

Merge Optimization for Table Operators
Beginning in Warehouse Builder 10.2.0.3, you can enable the Merge Optimization property for table operators. When set to true, this property optimizes the invocation or execution of expressions and transformations in the MERGE statement. There is QUITE a bit of information and tips and tricks out there on using the merge operator to the full extent.

DML Error Logging to Handle Error Tables
Prior to release 10.2.0.3, OWB already generated error-handling code for row-based mode.
Beginning in Warehouse Builder 10.2.0.3, DML error logging is available for set-based maps.

Thats is all for now, soon we'll move on to more new features and how OWB11g is easier to install and maintain.

Oracle Warehouse Builder OWB 11g New Features Part 3!

This is a continuation from part2. Here is part 3 of the new features and enhancements to Oracle Warehouse Builder - OWB 11g. It is in these posts that you can make the educated decision whether or not to pursue OWB 11g, or stay with OWB 10gR2. It is also here you can compare and contrast the differences between the 2 releases. Once again, props to Oracle for providing some of the info!

The new OWB 11g connectivity options....

• Now there is database support for database 11g to be both the source and target.
• Oracle Database 11g is supported as both an OWB source and an OWB target. Oracle Database 11g support is closely linked to the simplified install of OWB 11g as part of the Oracle Database 11g install.
• Now there is also Gateway 11g support
• The ability of OWB to connect transparently to non-Oracle systems continues with OWB 11g support for Gateway 11g.
• Also, the new optional Siebel Connector fo Siebel Analytics

OWB Connector to Siebel Analytics...
The Warehouse Builder Connector to Siebel enables you to connect to—and extract data and metadata from—Siebel applications. The connection to the Siebel applications uses database users that have the appropriate privileges set by the DBA. After you import metadata from Siebel applications, you can use Siebel objects in mappings, create process flows containing Siebel objects, and generate SQL code.

You can use this connector with non-Oracle databases after you establish a connection to those databases. The table above lists the functionality available in the Warehouse Builder Connector to Siebel.

Next time in part 4 we will discuss more new features and enhancements!

Microsoft Internet Explorer SUCKS!

I don't care what version you're using of IE. I hate every version now. I just wanted to let anyone reading my blog that you can view it the way it was meant to be seen with Mozilla Firefox.

If you use IE, or maybe any other browser than FireFox (Opera, Safari....haven't tested them) you might not see my blog the way it was meant to be rendered. Even my girlfriend religiously uses Firefox and it is NOT because I tell her to! And she isn't a engineering or technical person!

BI Publisher / XML Publisher CAN produce XML as an output format

In the above diagram, we see quite a few formats on the right that are available as output formats. PDF, RTF (mostly Microsoft Word), HTML, EDI (Electronic Data Interchange), EFT (Electronic Funds Transfer), and Microsoft Excel. I have implemented every output format thus far except XML. I figured, why implement BI Publisher to output XML if I am already getting XML for BI Publisher to start working with in the first place? Well sometimes you need to add a little or take a little away from the XML that BI Publisher is fed to start the whole XML output creation process.

You CAN have BI Publisher create XML as an output however not using a template that you may have used before, such as an RTF or PDF template. To transform incoming XML to another form of an output XML, you need to create an XSL stylesheet

From that point, you can upload it to the Apps XML Publisher Template Manager (as I have discussed in some other BI Publisher posts here) and it has to be registered as an XML-XSL template, not an RTF or other format. The concurrent manager will then be able to process this like any other BI Publisher template and give you the transformed format.

If you were wondering, we decided to go with BI Publisher and outputting the XML with BIP instead of Oracle Reports because our client is adopting BIP not only inside Apps, but as the standalone Business Intelligence reporting tool of choice, and they need bar codes and other nice things to be able to print out on Zebra Printers over their network. Zebra printers have a proprietary format and XML is something that will work for them.