Tuesday, March 16, 2010

How to Migrate BI Publisher Reports to Production?

migration

How can I migrate all the reports developed at a development instance to a testing or production instance with BI Publisher Server ?

This is very typical thing you as an administrator or reports developer would concern. The good news is, migrating BI Publisher reports from one instance to anther is pretty simple. All the reports are stored in a BI Publisher repository, which is typically on a file system or inside Oracle XML database. So, all you need to do is to copy the BI Publisher repository from the source system and paste it to the target system. Here is a diagram that shows the migration.

Diagram

Of course you need to have BI Publisher server installed and up-and-running at the target system before the migration. Either if your repository is on the file system or XML DB, this process would be pretty much the same. I’ll talk about the process assuming that the repository is on the file system in this post.

What is BI Publisher Repository?

So what is really the BI Publisher repository. It stores the following types of contents and information.

  • Reports
  • Server Configuration
  • Translation

BI Publisher Repository Screenshot

clip_image002

It not only stores the reports but also contains all the configuration related information. Let’s take a look at the detail for each. 

1. Reports

Under ‘Reports’ folder in the repository all the reports are organized in a folder based structure, which is in the same way that you see in the BI Publisher Enterprise server’s reports list UI page. Each report has its own folder, which contains one report definition file (.xdo) and report templates such as .rtf or .pdf.

1.1. Reports Definition

All the information for the BI Publisher reports are stored in the reports definition files which has a file extension as ‘.xdo’ It stores the information like sql query, report name, parameter name, list of values, template name, reports translation, bursting setting, report property, etc.

1.2. Reports Template

When you work on reports development most of the work is done within the report layout templates such as RTF Templates, PDF Templates, to design the reports layout and formatting. Once you complete the development you upload them to the BI Publisher Enterprise Server and register them as layout templates for each report. These uploaded report templates are also stored in the BI Publisher repository and resides along with the reports definition file (.xdo) in a same report folder.

2. Enterprise Server Configuration

All the BI Publisher server configuration related information is also stored in the BI Publisher repository. The configuration information includes the following.

  • Server Configuration
  • Security
  • Scheduler
  • Delivery
  • Data Source

These information is stored under Admin folder within the repository.

3. Translation

There are two types of the translation. One is a translation for the BI Publisher Server product UI. BI Publisher Server supports 28 languages translations (includes English) for its UI. All the translated strings are stored in a set of XLIFF files and each language has its own XLIFF file to store the translation. These translations are located under Admin/Translation.

And another is a translation for the reports. If you are a French and open a report you want to see all the text are written in French. If you are a German you expect the same way. BI Publisher separates the translation from the report so you don’t need to create multiple reports for each language. Instead, you can just have one report and multiple translation XML files (XLIFF format), which contains only the translatable strings. These translations are located along with each report.

Now you get an idea of the BI Publisher Repository. Let’s start the migration.

 

Migration Process Overview

Before you start the migration, again, ensure the BI Publisher Server exists at a target instance! The migration process has two parts.

  1. Migrate BI Publisher repository
  2. Perform Post-Migration Configuration

1 Migrate BI Publisher Repository

First, we need to obtain the repository from the source instance. Then locate it to the target BI Publisher repository.

1.1 Obtain BI Publisher Repository from a Source Instance

The screen below shows the root directory of the BI Publisher repository

clip_image002[4]

  1. Identify the BI Publisher Repository at the source instance
    You can find this information in a BI Publisher’s system configuration file, which can be found at %J2EE_HOME%/applications/xmlpserver/xmlpserver/WEB-INF/xmlp-server-config.xml (e.g. C:\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF)

<?xml version="1.0" encoding="UTF-8"?>

<xmlpConfig xmlns="http://xmlns.oracle.com/oxp/xmlp">

<resource>

<file path="C:\OracleBI\xmlp\XMLP"/>

</resource>

</xmlpConfig>

  1. Go to the BI Publisher Repository folder
    e.g. C:\OracleBI\xmlp\XMLP
  2. Copy all the folders except ‘DemoFiles’ under the above folder. (If you want to migrate a set of demo XML data files you can copy the ‘DemoFiles’ folder as well.
  3. Save the folders onto your local machine or check them into your source control system

1.2 Locate BI Publisher Repository Content to a Target Instance

  1. Login to the target instance machine
  2. Go to the BI Publisher Repository directory.
  3. Copy the source repository folders, which are the ones you have copied at the above steps, to this folder.

clip_image004

1.3 Validate the Repository Migration

  1. Restart your target J2EE server, such as Oracle OC4J server.
  2. Login to the Target BI Publisher Enterprise Server

clip_image006

Once you have logged in, you should see all the directories and reports migrated from the source instance under Reports tab in the new target BI Publisher Enterprise Server.

clip_image010

Post-Migration Task at Target Instance

After the BI Publisher Repository has been migrated successfully you want to perform a post-migration task to review a target specific information. Often that the development application instance doesn’t share the same information such as data source, delivery system, scheduling information, etc with the target instance.

For example, when you have a project where you develop financial reports out of a financial database, usually you would have one financial database for development, one for Testing, and one for Production. And you would expect that each of the BI Publisher Enterprise Server instance would talk to each financial database but not share the same database among multiple BI Publisher servers. This is just an example for a data source but it can be applied to other settings such as follows.

  • Data Source
  • Security
  • Scheduler
  • Delivery

When you have completed the migration process all the above settings are just the copies from the source system so you want to review the settings and update them appropriately so that they can be specific to a target system.

When you do this review and need to update any of the information, it is strongly recommended that you do it through the BI Publisher Enterprise Server Admin UI, not through the manual file modification. Just to make sure you don’t accidentally broken the system.

13 comments:

  1. Hi Kanichiro,
    Thanks for the nice post.
    How do you migrate the data to a SQL Server Database?
    Can we migrate the same XML repository?

    Chandra

    ReplyDelete
  2. how do i migrate pre built templates from EBS DATA SOURCE to bi publisher

    ReplyDelete
  3. Could you please tell me how to migrate reports from BI Publisher 10g to 11g.

    ReplyDelete
  4. writing a 10g to 11g migration post, will post it soon, stay tuned! you might want to follow me @bipublisher on twitter for the update!

    ReplyDelete
  5. here is the post I recently posted on the official BIP blog....

    http://blogs.oracle.com/xmlpublisher/2011/03/10g_users_lets_upgrade_to_bi_p.html

    ReplyDelete
  6. Hi Kanichiro, thanks for this very insightful post.

    In 11g, how do we migrate the security (users and roles that are assigned to each reports or folders) from the development server if we're using the Fusion Middleware security (and not the BI Publisher security)? Can we do that, or we need to reapply the security in the production server?

    And how can we migrate all the report jobs (scheduler) that we have set up in the development server? Can we do that, or we need to recreate the report jobs (scheduler) in the production server?

    ReplyDelete
  7. With the latest 11g - 11.1.1.5 if you are Administrator user then when you download or export with the BIP Catalog Utility the reports are exported with 'security.xml' file that contains the permission information.

    Our understanding is that there should be different setting for the Schedule between Dev/QA/Prod so there is not straight forward way to migrate the schedule jobs. Let me know if you have a business case here, then I can bring it up to our development group.

    ReplyDelete
  8. Hi Kanichiro, thanks for the reply. I checked the BIP Catalog Utility and saw that it's using .sh file. My environment is Windows Server. Can it work under Windows?

    As for the scheduler, we have requirements of creating about 500 reports. We need to configure and define the scheduler in the development to test whether or not all report jobs running correctly. And when we move to testing environment, we need to do the same. And in production environment too. Recreating 500 scheduler jobs surely are very tedious work, don't you think? If there is a simpler and easier way to do that, it would really cut down the time needed.

    ReplyDelete
  9. Hi Stewart,

    With the current release, there is only sh file and no .cmd file for Windows. However, when you look inside the .sh file you would notice that what it does is simply setting the classpath and calling a java API 'oracle.xdo.tools.catalog.command.CommandRunner' and pass the inputs. So you can run the Java API directly and pass the same input values that you would pass to the .sh command.

    ReplyDelete
  10. As to how to migrate the scheduled jobs, you might want to use the BIP Web Service to retrieve the schedule information and create the same jobs programmatically.

    ReplyDelete
  11. Also, the catalog utility is documented here. http://download.oracle.com/docs/cd/E21764_01/bi.1111/e13880/T526682T572260.htm

    ReplyDelete
  12. Kanichiro, I see that there is a security.xml file under the $BIPRepositoryHome$/Admin/Security/ folder. Does simply copying that security.xml file to new environment works (after creating all the application roles in the new environment, of course)?

    Inside the file, there is an entry like this:





    Does the roleGUID matter? Because the roleGUID for each application role in different environment will be different. Greatly appreciate your advice. Thanks!

    ReplyDelete
  13. Hi, Kanichiro.
    We have our development and testing environment as diferente folders inside the same catalog in the same BI publisher 11.1.1.5 installation. Each report in each environment has its own data model with its corrsponding JDBCConnection. I mean report A1 in deveolpment has A1DataModel whiche refers A1JDBCConnection.
    When we need to move ie 10 reports from dev to testing the dev team exports those 10 reports as xdmz/xdoz pairs.
    We need to reimport them one by one to the testing folder and adjust in each DataModel to A2JDBCConnection and the imported report to use the imported datamodel.

    According to your experience is there an automatic way to import all the reports togheter and change the requiered references (JDBC Data Sources and the DataModels to use) in the destination folder?
    All the info is stored in the BI catalog.

    regards
    Martin Canias.

    ReplyDelete