Wednesday, July 27, 2011

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

The Stonehenge in UK

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.

image

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.

image

 

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.

image 

 

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.

bip_data_template_case 

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.

bip_data_template_orphant

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

image

 

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

image

Data Model - XDM

image

 

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!

8 comments:

  1. I am a BI Publisher Newbie.
    I was wondering if it is possible to create a report with a calculation for the basis of this, I want use the outcome of other reports.

    In example:
    Report A total: 1.000.000
    Report B total: 2.5

    New report: use a function/calculation to use the total outcome of report A and B.

    If Yes, how can I apply this calculation?
    Thanks in advance.
    Regards,
    Vivian

    ReplyDelete
  2. Not sure how you would be able to achieve this at the report level, but I would do with Data Model where I would create two data sets, each of which contains a sql query to get such total values or use Global Level function to get such total level for each data set. This way, you can create another function in either Data Model or Report for those two values.

    ReplyDelete
  3. Hello!
    I'm working on OBIEE migration from 10g to 11g version, including BI Publisher reports. Now, we are trying to stress out our old servers in tests to compare them with Oracle Exalytics.
    I've been messing with logs and parameters in WebLogic and cannot find an elegant way to see how much time is needed to preview a report. We need some period durations broken down to various components of OBIEE suite for each report. I just cannot find how to do this.
    We've got some rendering time from MBeans in Enterprise Manager + query times from NQQuery log etc. Little bits from everywhere across the system.
    I need to point out that we use very complex report generation and every component of OBIEE is in use. Down from physical connection to cubes, there are some SQL statements that handle data in Data Model and Presentation Layers in Repository, then some aggregate calculations are being done in Answers and finally, the most complex procedures are calculated inside Publisher reports (of course, Publisher uses Answers as Data Model source).

    Do you have any way or advice how to setup logging so that we can get total time of report being generated from the time user opened the report on dashboard until it was displayed. Also, we would like to see, how much time of that report was handled by BI publisher, how much by Answers and SQL queries, how much time was wasted waiting for Essbase to respond etc.

    Kind regards!
    Marko

    ReplyDelete
  4. Hi Marko,

    There are two things you can do. You can use BI Publisher's Auditing capability, which I've written on this blog before, to see how much time it takes to generate data and how much time to render report itself. Also, you can use BI EE usage tracking, which should be written on the BIEE administrator's guide, to see how much time to generate answers/dashboard.

    ReplyDelete
  5. Thanks for quick response. We have already tried to setup audit inside Enterprise Manager for Publisher and it is working but we're not pleased with breakdown of results in time duration.
    The next step was to setup usage tracking as we have in OBI version 10 nut it's delayed because we could not get our SYSDBA for a couple of hours to make the changes on database. Hopefully that 11g has more measures for usage tracking because version 10 was not enough for our needs to see specific report being generated.

    I've read your other post about BI Publisher Auditing and monitoring and it looks very good. That seems a good way to solve at least half of our issues. I'll give it a try on Monday and report back with feedback in several days.

    Thanks again!
    Marko

    ReplyDelete
  6. Hi Marko,

    I'm in a middle of writing a new blog post about an update for Auditing setup with the latest release, 11.1.1.6.2. Hopefully, it will be published today!

    Also, let me know what are the information you need for the auditing/monitoring but not there. I'm asking for specifically for BI Publisher.

    ReplyDelete
  7. Hello again!

    It took a little bit longer, but we've succeeded to setup usage tracking but haven't got around to see if it can deliver any useful log data.
    We've used rendering and data process times from MBeans Browser for our Publisher reports and that will be enough for now. It seems that there isn't any mechanism inside OBIEE that would satisfy our need and that is that we wish to see how much time was spent on each particular resource inside OBIEE, beginning with Dashboard -> Publisher -> Answers and down to SQL queries to DB.

    At this moment, this data from MBeans will be enough. Please, maybe you already know this - how can statistics inside MBeans Browser be resetted? To clear any data so I could start from clear state, I need to restart both managed and weblogic server and that isn't very convenient.

    Regards!
    Marko

    ReplyDelete
  8. Hi Marko,

    Have you tried to load the data into database and view the data from there ? That would be much easier to see the data.

    Here is the setup info:
    http://bipconsulting.blogspot.com/2012/04/bi-publisher-auditing-and-monitoring.html

    and more info here:
    http://bipconsulting.blogspot.com/2011/05/11115-new-feature-more-data-to-audit.html

    ReplyDelete