BIEE and BI Publisher's integration (Best Practice tip)

BI Publisher can gather data from different data sources, but with regards of the integration with BIEE two in particular are of our interest:

1. SQL Type
2. OBIEE Answers, termed Oracle BI Answers
   

The first type, allows us to connect to any database using a JDBC connection. The second type, allows us to connect to BIEE server by means of BI Answers queries.

What is the difference? Let's see. If we take the route with OBIEE Answers then in BI Publisher we get immediate results without any additional work:

For the above data set we get the following XML output:


Problem with Long Tag Names in RTF Templates

But one of the drawbacks of this approach is that we get long tag names, like for example: <_sales_facts_._amount_sold_>. These long tag names are difficult to handle in BI Publisher for RTF template development. For example, if the formula used to create the OBIEE Answer is a long CASE statement, the tag contains this long statement. This drawback is a problem because of Microsoft Word’s properties limitation. Any situation of form field is limited to 276 chars. That is 138 chars max per tab: Status Bar and Help Key (F1) ) as confirmed below:



Workaround for the Problem with Long Tag Names in RTF Templates
But using the following workaround, we still can access BI Server and leverage all its content. Simply go to same BI Answers query ==> Advanced tab and take the “logical sql”:


Next, paste this logical sql into the data model in BI Publisher and voila, we get the same results but with the labels associated: saw_0, saw_1, … saw_5.

At this point some work is required to mapping the saw_X columns with the corresponding ones in the BI Answers query to make the column names meaningful in our report. Renaming the columns does not present any limitations to our goal of pixel perfect reporting.

Now, if we execute the report just defined then we can see that the XML elements of the output file are shorter and can be accommodated to our taste.