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

Migrating OBIEE Logical Models to use a Data Warehouse : Part 1

A couple of weeks ago I posted an article about a next-generation Oracle BI architecture, where I advocated building a first cut OBIEE system against your operational applications, producing some “quick win” reports, and then over time copying your operational data into a data warehouse and re-pointing the OBIEE semantic model to point to this instead. The idea behind this is to allow you to realize some immediate benefits from OBIEE and the semantic model, whilst providing a method by which you can eventually move your data into an Oracle data warehouse without disturbing the reports you created earlier. I’m presenting on this at the upcoming UKOUG BI & Performance Management Special Event next week in London, you can download the slides from here.

All well and good, but how well does this work in practice? Well, there’s no better way to find out than to give it a go, so what I’m going to do over the next three days is work through three stages of the type of project I’m suggesting:

1. Firstly, we’ll start off with three operational systems (Orders, CRM and HR), and create a single logical model that maps on top of them.
2. We’ll then create some reports against this data and save them for checking later.
3. Then, we’ll use OWB to extract this data from the source systems and create a data warehouse
4. We’ll then re-map the logical model I created earlier to point to this new data source
5. Then, we’ll check the same reports and see whether they still work ok.

Starting up SQL Developer and taking a look at the three application data sources, the Orders schema contains a number of tables in a “normalized” design. It’s got an orders table, an order details table, various product tables and so on.

The CRM schema is a bit simpler, and contains details on customer interactions, their reason and their outcome. The customers it refers to are in the Orders schema.

The HR Schema contains details of employees, appointments, jobs and departments, with details of employees being in the Orders schema to record salesperson activity.

So what we have here is a set of normalized tables that contains details on order activity, refunds, staff appointments and customer interactions. Out of these tables I therefore want to create a dimensional model, with the fact tables corresponding to the activities I just mentioned (orders, appointments, customer calls and so on) and the dimensions corresponding to the customer, product, employee and other lookup tables in the source schemas.

Starting off with a new repository then, the first thing I do is start creating this logical model. I’ll start off by modeling the facts first, then I’ll build out the dimensions, and only once that’s complete will I start thinking about mapping them to the source data.

I start off them by creating my first fact table, which will model the orders that I receive.

At this initial stage, I just create the logical columns within the fact table; the datatypes for each column will be defined later once I map source columns to them. Then, I repeat the process for the dimension tables, again just creating the column names and leaving the definition of the datatypes until later on. The point here is that you are modeling what the logical model should be, later on we’ll worry about mapping these objects to the source data.

Now, I create logical(complex) joins between the dimensions and fact table that I’ve just created.

Then, it’s a case of defining the dimensions for my logical model and setting the aggregation method for the measures, and the logical model is complete.

So I’m now at the point where I have a complete first cut of my logical model. At the moment, I’ve only got a single fact table, later on I’ll add some more, but for now my next step is to map in the source tables. To do this, I use the Import … From Database feature in Administrator and read in the source table metadata.

One of the most important things you have to do when importing source data into OBIEE is to make sure all the primary and foreign keys are registered in the physical model. I therefore go and create a physical database diagram for each schema, making sure all the tables are properly joined together – this for example is the Orders schema.

Then, I go and create joins between the different schemas, joining for example the tables in the HR schema to the Order schema via the Orders table, as employee data is references in orders via the Salesperson ID.

By repeating this across all of the source schemas, I make it possible for OBIEE to join together all the source data into a single, “virtual” source database.

Now it’s time to map the source data onto the logical model I created earlier. I start off by dragging the Order ID field from the Order Details physical table and dropping it on top of the Orders Fact logical table, which adds this physical table as a logical table source for the fact table and maps the Order ID logical column to the Order ID physical column.

I then repeat the process for the Order Line ID column, and the rest of the columns in this source table that have corresponding entries in the logical fact table.

The Order Details physical table only contains a part of the order information I want to put into my logical fact table – the most granular part that details the individual line items in the order. I also want to map across some data from the actual Orders table as this contains the order “header” information, such as the date of the order and the customer who made the order. To do this, I first map the existing fact table logical table source to this additional table, which I can do because the Orders and Order Details tables are joined together in the physical model.

Now I can just map in the additional columns from the Orders table to finish off this logical table’s mapping.

Once this step is complete, I’ve mapped in all the source columns for the logical fact table, with the fact table’s logical table source pointing to two source tables, the Orders and Order Details tables.

I then repeat the process for the logical dimension tables, editing the logical table sources as needed to provide access to additional lookup tables.

Once this is complete, I then edit the logical tables to add the primary keys, and then check the state of the logical model at this stage.

Everything is now complete except that I haven’t yet provided source table mappings for the time dimension. There’s a reason for this though, in that the source systems don’t have a “Times” table, all dates are held as Date datatypes in the various transaction tables and it’ll therefore be up to me to make a separate table out of them, together with derived fields such as Month, Day of Week and so on.

In my Orders fact table, time actually crops up a couple of times; there’s an Order Date and a Ship Date, and to be able to analyze these dates independently, I’ll need to create two time dimension tables, one for order data and one for ship date. I rename the Time dimension table I created earlier on to Order Date instead, and then map in the Order Date from the Orders table on to the Day ID logical column within it.

I then edit the logical table source for the Order Date logical table and use the Expression Editor to derive the various date components from the Order Date physical column.

We’re now ready to go. I create a simple Presentation model out of my Logical Model, start up Oracle BI Answers and run a couple of test reports.

The tabular report comes back OK. What about one that involves dates and a bit more analysis, say a pivot table report?

Not bad, it looks OK. So, tomorrow we’ll look at the next step – taking this operational data spread over three applications and copying it into a data warehouse, which we’ll do using Oracle Warehouse Builder 10gR2.