Oracle BI EE 11g – Patching Repositories and Migration from Development to Production

One of the biggest problems that we have had with BI EE 10g was there was no automated way of making repository deployments in Linux environments. Some of us internally in Rittman Mead have adopted UDML to script the migration from development to production. But the problem with UDML is that it cannot not generate the encrypted passwords (primarily for connection pools) automatically. So, the encrypted passwords for production environment had to be stored externally, and every-time there was a need to do a repository migration, these encrypted passwords had to be retrofitted back to the development repository UDML. In some cases, UDML cannot be used, as the development and production repositories might be completely different in terms of security etc. In such cases, the recommended way of merging changes in 10g was to use the repository merge. But again to do a merge, one had to have an external windows machine and then do the merging which was a major issue in many automated environments.

In 11g, BI EE supports a concept called as repository patching. Mark has covered this already here. What this allows us is to do apply incremental changes to the repository in development directly to production through an automated process. This is based on the concept of Merge that was there in 10g. 11g basically extends this concept. This blog entry basically expands on what Mark has already explained before. There are 2 parts to patching. They are

1. Creating the Patch – This is done by creating an incremental XML patch file that will contain just the changes done in development. The incremental XML patch file is created using the Compare option. 2. Applying the Patch – This is done by applying the incremental XML file on the production repository.

Initially after any new BI EE install, the repository that is created in development will be tested and then will be moved over to production with changes to the connection pool settings (primarily passwords) done through BI EE Administrator. Unfortunately, this step mandates the use of a Windows machine for Linux deployments. This is still the same in 11g as well. This is highlighted in the sample flow diagram given below

Once a production RPD is deployed for the first time, end users might want more changes to be done in the repository. For example, some of the common changes that might be needed are

1. Renaming of columns 2. Adding new columns 3. Adding new subject areas 4. Changing Security 5. Bug Fixes

These changes will typically be done on the development RPD. The concept of patching basically extracts the incremental changes that were done to the development RPD and then apply them to the production RPD. This is depicted as shown below

Generating Patch File:

Applying Patch:

Lets try to understand this through an example. Given below is a screenshot of a sample repository that will be moved to production.

Lets name the above repository as DevRPD. Now during the first time migration to production lets assume the following changes are made.

1. All connection pool user names changed to point to the Prod database usernames 2. All connection pool passwords changed to point to the Prod database passwords

Lets name this repository as ProdRPD. After this repository has been migrated to production, let’s assume that we are getting more enhancement requests. Following are the changes requested

1. A new subject area called SH – Aggregate. 2. Rename some columns. For example, rename CHANNEL_CLASS column to something more user friendly like “Channel Class” in the SH subject area.

After the above changes have been done using the DevRPD, this new repository will be saved as DevRPDv2. Lets assume that the developer while making the changes inadvertently deleted the column AMOUNT_SOLD. The admin notices this and he recreates the column AMOUNT_SOLD in the repository. A screenshot of the DevRPDv2 is given below.

Now, we basically have 3 repositories. DevRPD - The development baseline repository for the first release. ProdRPD – The baseline repository that is in production. DevRPDv2 – The repository containing all the changes requested by end users.

To migrate the changes done in DevRPDv2 repository, we first start with opening up the DevRPDv2 using the Admin Tool. Then we click on File->Compare option and then choose the Dev Baseline Repository as shown below

As you see we now have the changes done to the baseline repository (within the DevRPDv2) listed. If you notice carefully, we will have the AMOUNT_SOLD column listed twice as Deleted & Created.

This happens because when we delete an object, the upgrade id associated with the object is lost. The entire process of merge happens through the upgrade ids and not the names. Now you can imagine what will happen when we try to merge a lot of changes. We cannot have control on what developers do and hence we might see even more of these in the actual scenario. To reduce this and to treat the columns with matching names as the same, we have to equalize the repositories. So before every merge, it is always recommended to do an equalization that will ensure that the matching names have the same ids.

We can equalize non-matching objects (different names) as well one by one. In this case, I will use the automatic equalization that will apply the matching name-matching id principle. This would show all the objects that have been equalized as shown below, in our case just the AMOUNT_SOLD

If you now look at the compare screen, you will notice that the AMOUNT_SOLD column will no more be there

So lets create a patch now without making any more changes by clicking on “Create Patch”.

This will create an XML file containing all the changes.

Now, to apply patch there are 2 ways. One is through the Admin tool Merge process and the other is by using a command line utility called patchrpd. Lets go through the process of patching the repository through the Admin tool.

To do this we start with opening up the ProdRPD in offline mode. Then click on Merge & choose the Patch Merging option. Choose the DevRPD as the Original Repository and the XML file generated above as the patch file. Enter the appropriate usernames and passwords.

By clicking next, we trigger the patching process. The patching process basically recreates the intermediary DevRPDv2 internally and then performs a 3-way merge. If we look at the repository now, you should see all the changes in the target repository ProdRPD – Patched.

The same patching can be done through command line utility patchrpd. To do the same process that we did through the Admin tool above, the command would be

/u01/app/Middleware/Oracle_BI1/bifoundation/server/bin/patchrpd -P Administrator -C /home/oracle/ProdRPD.rpd -W Administrator -G /home/oracle/DevRPD.rpd -I /home/oracle/DevtoProdPatch1.xml -O ProdNew.rpd -S /u01/app/Middleware/Oracle_BI1/bifoundation/server/bin/xudml1.xsd

One main difference between the command line utility and the Admin tool based patching is, if there is any conflict resolution, the patch utility will just display some warnings and come out without creating the RPD. But in the admin tool we have the opportunity to resolve the conflicts and then proceed with the patching.

Next up is a methodology to do Web Catalog Patching. BI EE 11g offers similar patching capability for applying incremental changes to web catalogs.