Migrating OBIEE Projects Between DEV and PROD Environments

One of the clients I’m working with this week wants to go through how OBIEE environments are migrated from one environment to another. They are working on a solution for their organization where a team of developers put together an initial first cut of the repository and web catalog, and then at various points from then onwards they will either completely refresh the production environment using what’s in development, or more usually they’ll take an element of what’s in development, say an individual subject area, set of fact and dimension tables or a set of dashboards or reports, and deploy those as a “patch” in to production.

When working with OBIEE, my understanding is that you can have one BI Server per physical/virtual server, which can connect to one or more repositories, though only one of them is the “default”. You also normally then set up one BI Presentation Server instance which connects through to the default repository for the corresponding BI Server. In reality this means that each OBIEE environment consists of one BI Server and one Presentation server on the same physical server (together usually with an installation of BI Publisher, configured to use BI Server security), or in some cases the BI Server and BI Presentation Server might be split onto different physical servers, and possibly further clustered on to more servers if the expected load is high.

You can, it should be noted, set up additional Presentation Servers on the same physical server, each of them connecting through to the same BI Server but with different “default” repositories, which gives you a way to create your DEV, PROD and other environments on the same physical server (if it’s a beefy Unix box, for example). However this is a fairly complex task (see this blog posting by Borkur) and it’s usually easier to have a simple pairing of one BI Server, one Presentation Server (plus the BI Publisher, Delivers etc servers) per environment with all of these contained on their own physical server. The rest of the migration steps in this posting therefore assume that you’ve got each environment set up in this straightforward way, with OBIEE already installed on each server and a single installation of BI Administrator that can connect to both of them.

When you think about migrating an OBIEE environment, there are four main things you’ll want to move across:

1. The BI Server repository, which contains the enterprise semantic layer, variables, security settings (including users and groups if you use RPD-based security) and connection pool settings through to the physical data sources. The contents of the repository are normally contained with a single RPD file contained in the %ORACLEBI/%server/Repository directory, with %ORACLEBI% being determined by the SAROOTDIR environment variable and the current RPD in use (only one can be online at any one time per installation of OBIEE) being determined by the %ORACLEBI%/server/config/NQSConfig.INI file.
2. The BI Presentation Server web catalog, which contains the reports, dashboards, prompts, ibots and so on, together with a separate set of users and roles (usually propagated from the BI Server repository) and their permissions on the web catalog objects. Unlike the BI Server repository, the web catalog is stored in a set of XML files, one per web catalog object organized into folders, plus an ATR file per folder that contains permissions on the objects within them. The files that make up the web catalog are held in the %ORACLEBIDATA%/web/catalog directory, with %ORACLEBIDATA% being determined by the SADATADIR environment variable.
3. Connections through to physical datasources, held on the server containing the BI Server, which when working in an Oracle environment are usually contained in a single TNSNAMES.ORA file. The connection pool settings in the BI Server repository references the connections in this file.
4. The BI Publisher Report Catalog, which contains the XDO files that define the BI Publisher reports. It is assumed that BI Publisher is configured to use BI Server security or LDAP security, therefore the users and groups in the BI Publisher OC4J container will not need to be migrated. This catalog is normally found in the %ORACLEBI%xmlp/XMLP/Reports directory.

Note that this list isn’t exhaustive – if you know of anything else that needs migrating (perhaps to do with Disconnected Analytics, perhaps the configuration files for the Presentation Server) let me know by adding a comment to this posting.

In a typical customer situation, the environment migration lifecycle looks something like this:

Step 1 : Initial Development (“First Cut”)

All development of the repository and web catalog initially happens in DEV. At some point, the initial work is complete and the DEV environment is ready to be copied into PROD (or TEST, or whatever)

Step 2 : First Deployment to Production

The initial creation of the PROD BI Server repository is done through copying the DEV RPD file in to the PROD environment. Copying across the entire RPD file will bring across all subject areas, physical models, presentation models, security settings, users and groups (if RPD security is used), variables and so on. If your OBIEE server does not have a TNSNAMES.ORA file on it to connect to the source database, you will need to copy this across from the DEV server as well. You can either copy this repository “interactively” by using the filesystem explorer application and cut and paste, or you can script the copy using a batch or shell script. If you need to make changes to the repository “en-route” to the PROD environment, you can instead of copying the repository “whole”, export it into a UDML text file using the using thenQUDMLGen.exe utility within the %ORACLEBI/server/bin directory, alter any settings that you need to change using a perl script for example, and then import the amended text file into a new, blank PROD repository using the nQUDMLExec.exeutility. See this posting on this blog on UDML and repository migration and merging, and this posting by Venkat on automating changes to connection pool settings using UDML during a migration.

The corresponding Web Catalog is migrated by copying the contents of the %ORACLEBIDATA%/web/catalog directory to the corresponding directory on the production server. This directory contains top-level folders for Delivers and Answers/Dashboard contents, like this:

With each top-level folder then containing sub-folders and then individual file pairs for each web catalog item, like this:

The BI Publisher report catalog is migrated by copying the contents of the %ORACLEBI%/xmlp/XMLP/Reports directory and the %ORACLEBI%/xmlp/XMLP/users directories over to the production BI Publisher environment.

In all cases, the RPD file, the contents of the Web Catalog files, any UDML files and BI Publisher report files can be stored in a version control system to create versioned releases of your project metadata.

3. Subsequent Full Refreshes of Production

If production ever needs to be fully refreshed with what is in development, then the above process can be repeated. Note however that ordinarily, any reports or customizations that happened in the production environment (including users’ custom dashboards) will be overwritten using this process; these can however be preserved if the sub-folders below the %ORACLEBIDATA%/web/catalog/web_catalog_name/root/users`are saved before being overwritten by the DEV web catalog files, and then copied back after the migration takes place.

4. Incremental Refreshes of Individual Subject Areas, Tables, Columns & Corresponding Presentation Areas

Incremental migrations of elements of the BI Server repository can either be done interactively or through scripting.

Interactive migrations are carried out using the Copy / Paste feature in BI Administrator, and can be used to copy across any element from subject area down to individual column from one repository to another. To do this, open two copies of BI Administrator with one having the DEV repository loaded offline and one having the production repository loaded offline. Use the Copy and Paste feature of the tool to copy elements from one repository to another, like this:

Check the repository consistency afterwards, and if all is OK then open the production repository online to start using the migrated objects.

Automated (scripted) migrations are carried out using the nQUDMLGen.exe and nQUDMLExec.exe utlitiies, which can export and import repository elements from one repository to another (again this posting on this blog for examples of these utilities in use.) Automated migration in this manner is usually preferred in real, production situations as it removes the chance of operator error when migrating from one environment to another. If the physical tables to which logical tables map are the same in both the DEV and PROD environments (which they ideally should be) then logical tables should preserve their mappings when migrated from one environment to another. Scripted migrations are also the only way of migrating non-table and dimension settings from one environment to another, such as variable settings, user and filter settings etc). You can also take the text files created using these utilities and store them in a version control system to create versioned “patches” to your OBIEE metadata.

5. Incremental Refreshes of Individual Reports, Dashboards, Alerts, Prompts etc from the Web Catalog

Like repository migrations, Web Catalog migrations can be done either interactively or through manually copying filesystem objects, potentially in an automated fashion using batch files.

Interactive migrations are carried out in a similar way to repository migrations, but this time using two copies of the Catalog Manager application open, one connected to the DEV web catalog and one connected to the PROD web catalog. Like the BI Server repository, the web catalog can be opened offline by navigating to the %ORACLEBIDATA%/web/catalog/catalog_namedirectory (be careful that you open the correct directory, otherwise the Catalog Manager application will helpfully create a new web catalog for you rather than point out that it cannot find one at this location), or online by connecting to the BI Presentation Services URL. Once connected, you can cut and paste objects between the two catalogs as shown in this blog posting here.

Automated (scripted) web catalog item migrations can be accomplished by copying the web catalog element (a file of the same name as the report, dashboard etc with no file extension) and it’s corresponding ATR file (the same name with ATR at the end, as shown in the screenshot above) from the DEV web catalog to the PROD one using filesystem copies.

These copies can be batched up and automated using .BAT or unix shell scripts in order to copy across multiple reports and other elements, note the comment on this by Adrian Ward at the end of this blog post. Again it’s worth noting that automated migration in this way is usually preferred by customers as it removes the chance of someone inadvertently or deliberately changing a report during migration, or needing “live” access to the production environment. Note also that the two Web Catalogs need to have the same user and group settings for permissions to migrate correctly across both environments.

Miscellaneous Object Migrations

On any mature OBIEE system, there are no doubt other elements of each environment you would also wish to migrate. Some of these might include dashboard themes (XSL and other files), TNSNAMES.ORA files and/or ODBC DSNs, any clustering files and settings and BI Server users and groups if you are using RPD security (this would need, I believe, to be done using UDML scripting, see this other posting by Venkat). If anyone reading this knows of any others then add these as comments to the postings.

Migration Example : Complete Environment Refreshes whilst Preserving User Reports and Dashboards

So that’s the theory of how you go about migrating from one OBIEE environment to another. For this particular customer though, we can take a slightly simplified approach and release updates to the production environment by just copying across what’s in development, as long as we have a method to preserve any reports or dashboards that users create in the production environment and restore them after the migration takes place. This is a bit of a simpler process than using nqUDMLGen.exe and nqUDMLExec.exe as we won’t be migrating across individual parts of the repository or individual reports or dashboards. In addition, this customer uses the same database instance for their DEV and PROD OBIEE installations, so we can use the same TNSNAMES.ORA contents for all environments.

To put in place a routine for this migration, we will need to carry out the following steps, organized in to two parts; the first part (steps 1-5) will be concerned with gathering up all the repository and report metadata from the DEV environment and copy it to a staging area, with the second part (steps 6-9) copying this metadata across to the PROD area, after first taking a copy of any user-generated reports and dashboards.

1. Somewhere in a staging area, we create a directory for the metadata to be migrated. We call this OBI_RELEASE_x, with x being the release number.
2. Within this directory, we create four subdirectories
   1. RPD
   2. WEBCAT
   3. WEBCAT_USERS
   4. TNSNAMES
3. We then create two Windows or Unix batch/script files, with three input parameters
   1. REPOSITORY_NAME (to hold the name of the repository to be migrated)
   2. ORACLEBI (to hold the root directory of the Oracle BI Server installation)
   3. ORACLEBIDATA (to hold the root directory of the Oracle BI Presentation Server installation)
4. For this customer’s installation, they are not using BI Publisher or Delivers, so we will leave this out of the migration at this stage.
5. We then create a first script that copies the DEV environment metadata into the OBIEE_RELEASE_x directory ready for migration. This script performs the following steps:
   1. copy the %REPOSITORY_NAME%.RPD file from %ORACLEBI%/server/repository/ into /RPD directory.
   2. Copy the %ORACLEBIDATA%/web/catalog/%REPOSITORY_NAME% folder to /WEBCAT
   3. Copy the TNSNAMES.ORA file used on the server to /TNSNAMES
6. We then create a second script, which is used to copy the metadata from the OBIEE_RELEASE_x directory to the PROD server. It carries out the following steps:
   1. Shut down BI Server so that you can overwrite the repository file.
   2. Copy the /RPD directory to %ORACLEBI%/server/repository directory
   3. Copy the %ORACLEBIDATA%/web/catalog/%REPOSITORY_NAME%/root/users to /WEBCAT_USERS
   4. Copy the /WEBCAT directory to %ORACLEBIDATA/web/catalog/
   5. Copy the /WEBCAT_USERS directory to %ORACLEBIDATA%/web/catalog/%REPOSITORY_NAME%/root/users
   6. Copy the /TNSNAMES directory to the database client /network/admin directory
   7. Restart the BI Server
7. Once these scripts have run, to test that migration has happened correctly we perform the following steps:
   1. Go in to BI Administrator, check you can connect, check you can do row counts, check global consistency. Check that all users you expect are there
   2. Log in to dashboard, check that all reports and all users’ reports and dashboards are present.

I’ve tested this approach out on my DEV and TEST environment and it migrates all data, reports and dashboards correctly.

As I mentioned previously, what I haven’t tested it with is BI Publisher and Delivers, but the principal should still be the same. As usual, any comments or suggestions are most welcome.

i