BI Publisher 11G Upgrade Part 2 ! – Things to Know

I just talked about the 11G Upgrade the other day, but looks there are some confusions around this topic, so here I am, trying to clear them up today.

Data Set with No Columns!

When your report’s data model is using SQL Query or BI Answers as the data source type then after the upgrade you would notice that your data sets don’t have any column in the Data Model Editor. So it would like this.

This is because such data sets in 10G don’t capture necessary information to populate such columns for the new 11G Data Model. For example, SQL Query data sets don’t have any data type information for each column nor any grouping or hierarchy information. We wanted to make the upgrade process as easy and quick as possible so when you do the upgrade with the Upgrade Assistant utility BI Publisher doesn’t require any database connection. That means, you can perform the upgrade at anywhere without requiring any database connection. However, the Upgrade Assistant has no idea about each column or element data.

However, if you uses Data Template with SQL Queries in 10G then after the upgrade your 11G data model will have all the columns and associated information. This is because, as you imagine, all the necessary information is already provided in the Data Template.

 

So you can keep running the reports using the data model without any modification after the upgrade. However, this type of data model is not really expandable. For example, you can’t add a calculated column from the UI and you can’t create a link with other data sets. Having said that, I would recommend that you take a look at such Data Models and decide if you want to keep them as they are. If you like to have those columns populated then simply, you can create a new data set copying the same SQL query from the original data set, then you can delete the old data set. One thing to note though, when you do create a new data set then make sure the generated XML structure and the element names match to the RTF Template using the data model. Instead of updating the RTF templates, which can be cumbersome, you can use the Structure Pane to update the XML element name.

 

 

11G Data Model is More Restrictive

Since we’ve moved completely to a Web UI based Data Model Editor instead of the creating/updating the Data Template with Text editors model, now there are some restrictions for the Data Model. Therefore, you might get a warning message when you try to save the data model with invalid configuration. There are two things you want to be aware for this validation thing. 

1. Case Sensitive

Let’s take a look at the following example, you see there is a aggregated element ‘DEPTSAL’, which is referencing ‘G_EMP.SALARY’ with ‘SUM()’ function.

 

With 10G Data Template you can reference to other elements without caring much about whether the letters are upper cases or lower cases. So if you write ‘G_EMP.salary’ it would still work with 10G. But not with 11G. That means it has to be ‘G_EMP.SALARY’.

2. Orphan Elements

Now let’s take a look at another example below.

As you see, the ‘FAVORITE_MOVIE’ element is referencing to ‘FAVORITE_MOVIE’ column but it doesn’t exist in the SQL query. With 10G, BIP just returned a null value for this. But not with 11G. This would give you an invalid warning unless you delete this column from the Data Model editor UI.

Therefore, it’s important to review the original data templates and correct them before the upgrade. If you didn’t correct them or didn’t catch them then you can open such data models with the Data Model Editor and remove such columns or recreate a new data set within the same data model.

Upgrade or Upload ?

I’ve got this question of ‘Can I upload the upgraded reports to 11G from the UI ?’, and the short answer is ‘No’. The Upgrade Assistant utility is meant to upgrade your 10G catalog to 11G converting each report to an appropriate format for 11G. So Yes, it does migrate the 10G reports to 11G, however, it is not a migration tool that you want to use to migrate one report after another in order to upload them later.

But there might be some circumstances where you want to upgrade only a set of the reports instead of all of them. If that’s the case, I would suggest you do the upgrade first with the Upgrade Assistant utility by pointing your 10G catalog as the source then pointing to an empty folder as a target, which will create a new 11G catalog with your upgraded reports. Then you can manually move a selected set of the reports by copying them over to another 11G catalog on the file system where the catalog is located. But not from UI.

 

How They Look Like in Catalog ?

I thought it would be helpful to show you how the reports look like in the Catalog for both 10G and 11G. The main thing is, as I talked in the previous post ‘10G Users! Let’s Upgrade to 11G!’, a single report in 10G will be split to two reporting objects in 11G. One is a Report (.xdo) and another is a Data Model (xdm). And here’s how they look like.

Report in 10G Catalog

 

Report in 11G Catalog

This is the sample report upgraded to 11G. Now there are two report objects, one is ‘Employee Salary Report.xdo’, which contains report definition, layout templates, etc, and another is ‘Employee Salary Report.xdm’, which contains only the data model (such as SQL query) information.

Report - XDO

Data Model - XDM

 

Hope this post have clarified some of the uncertainty around the upgrade, and your upgrade process will be as smooth as possible. But if you have hit any issue or question or anything, please feel free to contact me at Kanichiro.Nishida@oracle.com. Happy Upgrade!