OBIEE 11G Patching Repositories and Migration from Development to Production

ne 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.

OBIEE 11G Incremental Web Catalog Migration – Patching Web Catalogs

esterday, we saw how to go about patching repositories so that the entire process of migration from development to production can be automated. That patching process was basically an extension of the 3 way merge process. In 10g, 3 way merging was supported in Web Catalogs as well. I had blogged about it here. But the biggest downside was, it had to be done very carefully as there were chances of losing a lot of objects. In 11g, web catalog incremental migration is supported with more granular control on how the 3 way merge happens.

The high level architecture of the patching process for web catalogs is given below

Creating the Patch:

Applying the Patch:

So the process more or less is the same as the Repository Patching process. To start with lets assume we have a webcatalog called SampleAppBaseline which will be our baseline web catalog that is already in production (SampleAppProd). Due to a change request, lets assume following are the changes that were made to the SampleAppBaseline webcatalog.

1. One report was deleted – A report called ChartZoom has been deleted
2. One report was modified – A report called Filters has been modified
3. One new report was created – A report called SampleSales has been created

The name of this new web catalog is SampleAppChanged. Web Catalog patching is done through the Catalog Manager command line options. In 10g this was not documented as shown here. In 11g, we have much more comprehensive set of options that makes scripting catalog manager related tasks a lot easier. So we start with first creating the diff file using the following command

./runcat.sh -cmd diff -baseline /home/oracle/SampleAppBaseline -latest /home/oracle/SampleAppChanged -outputFile /home/oracle/SampleAppDiff.txt -folder /shared -verbosity detail

Lets look at the diff file to see what the contents are.

As you see all the changes that were made to the web catalog have now come into this diff file. Remember we can now control whether we want to migrate the permissions as well from one web catalog to the other. Now the next step is to create a patch file out of this diff file by comparing the changes in the diff file with the production web catalog.

./runcat.sh -cmd createPatch -inputFile /home/oracle/SampleAppDiff.txt -outputFile /home/oracle/SampleAppPatch.txt -production /home/oracle/SampleAppProd

If we look at the patch file, it will look similar to the diff file. Basically this provides us with an opportunity to pick & choose the changes that we want to apply in to the production web catalog (through command line options)

By modifying the PRODUCTION webcatalog name in the patch file we can apply multiple patches simultaneously to different web catalogs (like UAT, PROD etc). To apply the patch we just need to fire the following command

./runcat.sh -cmd applyPatch -inputFile /home/oracle/SampleAppPatch.txt -outputFile /home/oracle/SampleAppPatched.txt

If we now open the production web catalog we should now be seeing all the changes applied automatically. Remember to test this out completely in development before finalizing the scripts in production. I have seen instances(on my test sand box) where the patch files seem to somehow not pick up the changes automatically (had to enforce it through timestamp differences).

OBIEE 11G Action Framework – Integration with External Applications

Action Framework in BI EE 11g opens up BI EE to external applications for extensive integration. Mark has covered this as part of his posting here. BI EE, even in 10g had a lot of integration options. But one biggest drawback had always been the lack of an ability to call external applications/scripts/processes right within the BI EE dashboard. Only way in 10g was to either make the call through a URL (like action links to Ebusiness Suite & Siebel) and browser based Java Scripts. In 11g, we now have a lot more options. There are basically 2 kinds of Actions. The first one is the Navigate action which basically enables the drill from BI EE to external applications like EPM, Ebusiness Suite, another report/dashboard in BI EE etc. The parameters can be passed from one application to the other easily.

The second one is the Invoke action that provides an ability to call external Java processes, Browser scripts, web services etc. The invoke action does not provide external navigation. They are primarily used for triggering processes that happen outside of BI EE like calling ETL processes, java processes etc

Both the Navigate and Invoke actions are part of the Action Framework. Listed below are some of the new features in Action Framework that enable integration with external applications.

1. Integration to external Applications through a URL – This feature existed even in 10g and was one of the most commonly used ways of integrating other applications with BI EE. The biggest problem with this in 10g was, we manually had to hand craft the URLs and the parameters. In 11g, this integration has been enhanced to alleviate 2 important issues that we have had with 10g

a. We can specify location aliases instead of hardcoded ip addresses (or hostnames). For example, if you are using Go URL to call another BI EE report, then we will have to enter the full URL including the hostname. When we migrate from environments (say dev to prod), then we will have to manually change the hostnames in every report that use Go URL. This is not needed anymore.

b. Its now much easier to specify the url parameters through the UI. We can pass parameters which can be either made visible or hidden. For example, if you want to pass username and passwords to the url but you would like them to be invisible for the person invoking it, then that is possible now.

2. Integration with Web Services – This is a complete new feature of BI EE 11g. In 10g, web services can be called only using Java Scripts or by calling an external JSP page. In 11g, we have this feature natively. So, effectively if you have say ODI ETL Web Services and you would like to kick start an ETL process from BI EE, it is easily possible now. In addition to making calls to external web services, BI EE also has a new type of Web Service called BI EE Web Services for SOA. This new web service basically is for integration with Oracle FMW products like BPEL etc. One of the big drawbacks in BI EE 10g web services was, every-time we had to call a report or an iBot, we had to establish a session with BI Server and then pass the session id to the individual web service methods. But in the BI EE Web Services for SOA, there is no need to establish a session to get access to the individual methods. I will cover this in detail in another blog post.

3. Integration using Java Methods – Another interesting integration that is part of 11g is the ability to call Java methods natively. In 10g, the only way to call java methods directly was to use delivers and then call the java methods through the Java Host. In 11g, this method of calling java methods (through Javahost) is not recommended. Instead the java classes will have be wrapped inside a EJB and then this EJB can be called from BI EE. This opens up a lot of integration opportunities like writeback to Essbase etc. I will cover this in a separate blog post.

4. Integration with EPM – BI EE 10.1.3.4 introduced the first steps of integration between BI EE and EPM. In BI EE 10g, we could embed BI Answers, Dashboards etc within an EPM workspace. Also, SSO capability was added between the EPM and BI Suites. But there was no native integration between the reporting tools like Hyperion Financial Reporting and BI EE. One had to use methods like the one i have used here and here. In 11g, this is not needed anymore. It is possible navigate to any EPM report (HFR & Web Analysis) and we can also pass parameters from BI EE to the target EPM report. I will blog about this in detail in another blog post.

BI EE 11g provides us with a lot of potential opportunities for integration that weren’t easily available in 10g. Next up is a blog post on how to call Java-EJB methods to add watermarks to BI EE PDF exports.

OBIEE 11G Parent-Child Hierarchies – Differing Aggregations

Mark has already explained the new Parent-Child hierarchy new feature in 11g here. Parent Child hierarchies are different from normal level based hierarchies not only in the nature of their structure but also in the way aggregations are done. For example, in a level based hierarchy, the most common type of aggregation that is done involves rolling up the numbers from the lower most level. But in the case of parent-child hierarchy, an aggregation by default enforces the roll-up across all descendant members. To understand this lets try to take this through an example.

Lets consider a sales department with the employee hierarchy shown below

Its a simple parent child employee hierarchy found in the oracle HR schema. Now the requirement for us is to model the Salary attribute of an employee in 3 ways

1. As an attribute – Show the salary of each employee as an attribute in the parent child hierarchy.
2. As a measure (All intermediary Roll-ups) – Roll-up the salary figures of all the employees who roll into the employee. For example, Scott King will have a Salary equal to sum of salaries of all the employees who report to him, including the ones who are his direct & in-direct reporting employees.
3. As a measure (Level-0 Roll-up) – Roll-up the salary figures from the lower most level till the top node For example, in the above figure, Scott King will have a Salary Roll-up figure of all the employees who are the lowermost level (not including the intermediary nodes – very similar to normal level based hierarchy roll-ups)

The above requirement is quite straightforward, but to achieve similar such aggregations in a normal level-based hierarchy, we will have introduce fragmentation & other techniques. But with parent-child hierarchy modeling now available in BI EE, all the above 3 requirements can quite easily be achieved.

Following is the employees parent child table that stores all the attributes of an employee

We start off with modeling this as a normal dimension table. As shown in Mark’s blog entry, we then create the Parent-Child table that stores all the relationships within the employee hierarchy (if you look at the script used in creating this, it will be containing a Connect By statement that will connect a root hierarchical node to every member that is part of its hierarchy).

Once this is created, we start with including the Salary as an attribute within the Employee dimension itself

This will serve our first requirement i.e show salary as an attribute of every employee. Now, to model the 2nd requirement i.e. to model salary as a measure and then make it to rollup from all the intermediary hierarchical members, we start with defining an alias on the main Employee table. Then join this table to the custom parent child employee table. The join structure of all the 3 tables is given below

We would then be using the salary column from the new employee alias table in our logical fact table as shown below

Lets now look at the report now and see what BI EE generates.

As you see, BI EE has generated the correct results with the roll-up of intermediary members as well. This is something that is quite difficult to model in BI EE 10g. If we look at the query generated (just the key one that generates the measure as BI EE now generates lots of intermediary sub-queries to support contextual drilling) you would notice that the default Parent-Child behavior is to achieve a roll-up across a hierarchy

To achieve the 3rd requirement i.e. to model a level based hierarchy kind of rollups from level-0, we create another alias in the physical layer and then model it the same way as the 2nd requirement. In the fact logical table, we then create a new measure that will map to the new physical alias as shown below

To ensure that we roll-up only from level-0 members, we then apply a filter in the fact logical table source as shown below

Lets now take a look at what BI EE generates

As you see, BI EE now generates the roll-ups only from level-0 members as against every descendant. We can have even more granular roll-ups based on custom defined attributes.

OBIEE 11G BI Server Upgrade Considerations

One of the important points to consider when moving from BI EE 10g to 11g is in understanding the BI Server behavior changes. Though there are quite a lot of changes in the underlying code for performance improvement etc, introduction of new features in 11g can potentially alter your generated queries in 11g. In this blog entry i will basically cover 2 main important changes that have been introduced in 11g. There are more actually, but that is something we will try to cover in our training days event.

1. Level Based Measures at Detail Level:

One significant change in 11g is the handling of level based measures at the detail level. For example, lets consider a measure AMOUNT_SOLD that is assigned to the detail day level of the date dimension.

In 10g, whenever we bring this column for reporting, we would basically see something as shown below i.e. the level will always be enforced.

In the case of 11g, there are 2 types of behavior

a. If the report contains normal attribute columns and when the query is at a grain higher than the measure grain (say the report is at the Year level), then normal aggregation will be applied and the level will not be enforced like in 10g. An example screenshot is given below

b. If the report contains hierarchical columns, then if the query is at higher grain than the measure, the measure values will become null. The values will appear at that grain or below. An example screenshot is given below

2. Priority Groups & Order of LTS:

In 11g, there is a new concept called Priority Groups. In 10g, the LTS that determines the final query is dependent on the ordering of the LTS in the dimension logical table(in most cases – but there are cases where we cannot be always be sure to guarantee this behavior). But in 11g, this is determined by the priority order set in each and every LTS.

For example, lets consider a case where there are 2 aliases for the same dimension CHANNELS as shown below

In the business model and mapping layer, lets assume that both the LTS are mapped to all the columns. Both the LTS are at the same detail level. Now, lets assign a priority group of 0 for the Channel Alias – B LTS and a priority group of 1 for the Channel Alias – A LTS. Also the SALES LTS of the Fact table will also be assigned to the priority group 0. In 11g, the LTS with the least number is considered as the one that has to be chosen for querying (highest priority)

Now, lets create a report containing columns from Channels dimension and the Sales fact.

If you look at the query of the report, you will notice that Channels Alias – B will be chosen as it is the one having the highest priority.

Lets now go back to the repository and reassign the highest the priority to Channels Alias – A as shown below

For the same report, if you look at the query now, you will notice that Channels Alias – A will be chosen instead of the B alias.