[OBIEE 11g] Action Framework Integration With External Applications

 

Action Framework – Integration with External Applications

Action Framework in BI EE 11g opens up BI EE to external applications for extensive integration. 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. 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] Patching Repositories And Migration From Development To Production

 

Patching Repositories and Migration from Development to Production

In 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. 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] User Roles With OBIEE 11.1.1.6

 

User Roles with OBIEE 11.1.1.6

In choosing a tool like OBIEE companies are looking for more than a reporting solution. They want users to gain insight into their data and find anomalies and trends hidden in the transactional data. However, the tools available so far in OBIEE have really divided users into those that can learn and excel in Answers (Analysis), and those that are confined to dashboard consumption of data. In Fact Gartner finds that:

At most of my clients many users are tied to the availability of report developers or power users to get the data they need. Many times it is a new attribute or a different aggregation or sort that is required. With the new dashboard features in OBIEE 11.1.1.6 a whole new group of users can have the power of truly interactive reporting by planning for and incorporating content into dashboard for that purpose. This article will outline the methodologies that can be incorporated in dashboard design and the basic training it would take to get that next set of users comfortable with grabbing their own data thus enabling them to accomplish their jobs easier, and freeing up IT resources for more complicated tasks.

What Actions Are Available

 

In the properties section of each analysis is a tab that allows developers to set the availability of various report manipulation features. The list below and indicates what the features can be allowed for dashboard users to manipulate a single report.

Drill

Drilling has been with OBIEE since the beginning. For the end user it allows the ability to get to details quickly and to zoom to the results of the same report on fine grains of data. This has been setup up as the primary interaction on the column properties for the year column below. The change in 11.1.1.6 is the ability to drill by right clicking to the interaction menu.

 

Moving Columns

Let’s look at using a table view in a dashboard. For this example I created an analysis with a couple of hierarchies and many hidden columns. The use case is to provide a single report that can answer many questions about that subject area and is customizable by the end user.

Here is the base line report saved on a dashboard.   Let’s explore a few use case scenarios an end user may want handle without going to the Analysis tool.

Prompting and Sectioning:

This is a straight forward example that has been available since 11G came out but let’s walk through the use case. A user wants to be able to prompt on any dimension column in a report. Let’s say Brand for our example, which is a matter of dragging the desired column to the prompt drop space on the report or selecting move column à To Prompts in the right click menu. You will notice in this snapshot that the right click menu got much bigger and we will go through the list one at a time.

Moving this to a prompt condenses the report and allows the user to work with one brand at a time

Or with a section:

All these reconfigurations of data are truly useful to end users that need different views of data but are not comfortable utilizing the Analysis tool. The ability to turn off the feature is equally important for some reports. Dashboards created with many reports formatted a certain way or for public consumption may make sense to disallow this feature.

Sorting Columns

Sorting is another feature that came with 11g originally but now can be controlled in the report properties. Users can change the sort of a column on the fly showing numbers in a way that makes the most sense to the individual user.

 

Add and Remove Values

The add and remove values feature is set up in 2 ways. At the attribute level a user can remove or keep only the selected value or values. This is useful if a user only cares about certain regions or just does not care about certain values and want to clean up their view of data. 

The more powerful aspect of add and remove feature is at the column level. Here a user can enable some complex comparisons and analysis. For each dimesnion value the users can select to keep or remove based on any measures top X or bottom X. So for example say this users is only interested in years with the top 3 based on total damages. These kind of dashbaord based analysis can really enable a busienss end user with simple but powerful tools. As long as the measure is part of the criteria the user can manipulate the results on it.

The user can also filter the report based on comparing columns. Say the end user want to only see years where the number of fatalities is more than the number of hospitalizations, or a measure based on a fixed value. The possibilities of filtering and calling out important data are really endless with this feature. Like all these customization interactions, a user can save important ones as their dashboard customizations.

Groups

Groups allow users to create bins of data and group results into categories of their choosing. 

 

By control clicking the members you want to include into the group then right clicking to create the group use can easily add rows to the report representing custom groupings of data. In the below example I combined all the departments starting with T into the T depts group. I can then inspect groups that have been created to see the members assigned.

To further customize my view, I can remove the details behind my group by removing the departments that start with T rows. Notice the grand totals stay correct no matter how many groups have been created.

Calculated Items

Calculated Items allows for the addition of rows to the report with customized aggregations. For example, we can add a row for the average office. By control clicking all the office members and then right clicking and selecting add calculated item.

The results show a new row with the average for the measure in the table.

Subtotals and Running Sums

The ability to add totals, subtotals, and running sums to any report adds another layer of customization for the dashboard ad-hoc user.  In this example a report starts with not totals or subtotals and by right clicking the end user can customize a report that shows both at any level.

First Grand totals, like in the pivot table editor the user can choose to add them after or before the columns or rows, giving are dashboard only user the abilities to customize the report like a report developer.

Users can also include subtotals when 2 or more dimensions are on an axis. 

  

Finally the user can pivot the measures to be absolute or running sums.

Include and Exclude Columns

 

To truly enable ad-hoc analytics as a dashboard only user, the report builders should consider adding hidden columns that can be included or excluded depending on the end users preferences. Let’s look at an example with Sample App using a report based on Time, Product, Office, and 3 measures.

Traditionally to save real estate we may design the report with column selectors or view selectors, maybe variable prompts or other methods for controlling what attributes are available. But now we can design a report with excess columns and give the presentation choices to the end user directly. First let’s look at the measures; we can include others by right clicking in the measure section of the pivot table. 

And then we can move the column or the measure label just like the original version of 11g.

The same can be done for dimension columns in excluding or including them, then saving the customization in the dashboard.

With the powerful right click options available to dashboard end users, report developers can rethink the content of reports to truly expand the possibilities for end user ad-hoc report adoption without having to train on the analysis tool. This opens the door to a new class of user who is curious about the data but not ready to commit to ad-hoc development.