Experimenting with the new Oracle BI Baseline Validation Tool

The Oracle BI Baseline Validation Tool is a long overdue new tool introduced in 12c that allows BI administrators  to perform regression testing after a BI migration or a BI upgrade. The goal of this post is to document my experimentation with this new tool in Windows 7. As an example I’ve got an environment with OBIEE 11.1.1.7 installed and another equivalent environment with version 11.1.1.9. The BVT is run from the command with a configuration file and set of arguments. Following are the high-level steps for using the Oracle BI BVT:

1. Establish a baseline (set of dashboards, reports), in this example we compare all items  
2. Configure the configuration file to specify the location of the target catalog, Oracle
   BI Presentation Services login information, and the plug-ins to activate. A sample configuration testconfig.xml is provided out the box, one can create a new using the BVT utility
3. Run the Oracle BVT tool three times:
• First run to generate the baseline
• Second run to generate the same data from the patched environment
• Third run to compare results between the before and after web catalog

The BVT allows a fine grain level comparison allowing even the ability to set a maximum tolerance for floating point fluctuations between reports.

Oracle BI Validation Tool Installation

Requirements

• Java 1.6 and higher
• Windows: Windows 7 and higher, Windows Server 2012 and higher
• Linux: Oracle Linux U5 or Oracle Linux 7
• Mozilla Firefox or IE

It  is shipped with the Oracle Business Intelligence Developer Client Tool, after installation, the tool is located in

[Installation Folder]\bi\components\oracle.bi.bvt

Once the zip file is uncompressed

Configuration File

Looking at the sample configuration file, there are 2 sections

Deployment

Description of the target environment that the tests will run against, the deployment name can be updated in order to describe your specific environment, in our case we will define the following deployment names; “OBIEE11117base” to designate our baseline OBIEE 11.1.1.7 environment and ““OBIEE11119upgrade” to designate the upgraded environment. The deployment names will be fed to the BVT as command line arguments

Test Plug-ins

This section describes the different types of comparisons (5) that are available for the web catalog; each type of comparison is developed as a plug-in. Following are the plug-ns available out of the box:

• Catalog Plug-in Compares the metadata of objects in catalog folders,
  
• UI Plug-in [com.oracle.biee.bvt.plugin.ui] : Compares screen captures of individual analyses and dashboards.
  
• Reports Plug-in [com.oracle.biee.bvt.plugin.report]: Exports the results of the analyses to CSV and generates a comparison report.
  
• Logical Query Plug-in [com.oracle.biee.bvt.plugin.logicalquery] : Downloads and saves the logical SQL generated for analyses
  
• Dashboard Plug-in [com.oracle.biee.bvt.plugin.dashboard]: Exports the results of the analyses to XML and generates a comparison
  report.

The plug-ins can be enabled by setting the “enabled” tag to true.

For more details about the plug-ins, consult the official documentation: Comparing Oracle Business Intelligence Deployments Using the Oracle Business Intelligence Baseline
Validation Tool.

Let’s update the sample configuration file and capture the baseline for our OBIEE 11.1.1.7 web catalog. We enter the URL for the BI environment and the username/password. Note that you can chose to enter the password as a command line argument with the “-password” flag followed by the actual password; if no value is passed with the flag, you will be prompted to enter it.

We run the following command in order to capture our 11.1.1.7 baseline, the syntax to capture the configuration for our BI 11.1.1.7 is:

bin\obibvt.bat -config TestConfig.xml -deployment BI11117base

Where testconfig.xml is the name of the configuration file and BI111117base is the deployment name to run. As an example, we’ve enabled all the tests and run the BVT to capture our OBIEE 11.1.1.7 baseline. A folder named after the deployment name is created under the results folder, a progress status is displayed on the screen while the processing is underway.

Note that you can set timeout for loading each report, by default, the timeout to download a single report is 15000 milliseconds (15 seconds). Once the script is done executing, sub folders are created in the main results folder

We run the script again in order to capture the 11.1.1.9 environment, this will enable us to run comparisons between environments

bin\obibvt.bat -config TestConfig.xml -deployment BI11119

Once the script is done running, a second folder is created in the main Results folder

We can now run the same script with the –compareresults flag in order to perform comparisons between the 11.1.1.7 and the 11.1.1.9 environments.

Now navigate to the Comparisons folders and examine the differences between 11.1.1.7 and 11.1.1.9; for each test there is an html file with the color coded test results, relevant details and hyperlinks to items of concern.

Web Catalog comparisons

Details are provided such as :

• number of of BI objects in the base environment
  
• number of BI objects in the target environment
  
• items only present in the base environment
  
• items only present in the target environment
  
• items that are different

Drill down links provide more details where there are exceptions

 

Dashboard comparisons

Differences between dashboard details are provided.

Logical Query comparisons

Differences in logical SQLs between both environments are documented.

Reports Comparisons:

An export to CSV of the results for each of the analyses is performed for each environment and a comparison report is performed.

UI comparisons

UI plugin compares screen captures pixel by pixel of individual analyses and dashboards and displays a comparison report side by side. A score is assigned to each comparison based on the match: a score of 1 indicates a perfect match while 0 indicates a complete mismatch. The threshold for a pass of fail score can be set in the configuration file, it is 0.95 by default. The side by side comparisons is very handy and help you see the discrepancies in look and feel.

Error

p.s: Note that we were only to have all tests run successfully using Firefox version 34.0; tests will fail while running with Internet 11.0 and Firefox 42.0 we got the following error in the log:

NS_ERROR_XPC_CI_RETURNED_FAILURE: Component returned failure code: 0x80570015 (NS_ERROR_XPC_CI_RETURNED_FAILURE)

Apparently this error is caused by the Selenium web driver in later version of Firefox, you can read more about it here.

It is worth noting that there is a flag in the configuration file that allows you (1) to set the default browser for running the tests and (2) another flag where you can specify the number of browser instances to run concurrently while performing the tests.

Conclusion

The goal of this post was to document a preliminary experiment with the new regression testing tool that shipped with obiee 12c; setting up the configuration and the running comparisons is fairly straightforward; the results are detailed and handy. There is no doubt that this tool will be useful for OBIEE regression testing. In conclusion, the Oracle BI development team is making big strides in addressing pain points that have made working with OBIEE and migrating and maintaining different Oracle BI environments a challenge. One shortcoming of the tool though is that BI-Publisher reports are not included in the comparisons, hopefully future releases of the BVT will include this type of comparisons.

OBIEE 12c new command line utilities

Oracle Business intelligence 12c introduced a new set of command line utilities that will make it easier for BI System administrator to manage the OBIEE service instance ; release 12c has shifted the process control operations that used to be performed  from the web applications such as Enterprise Manager Fusion Manager to command line scripting utilities. Operations such as uploading an RPD can no longer be accomplished from EM. The new command utilities are the following:

• lisconnectionpool
• updateconnectionpool
• listrpdvariables
• updaterpdvariables
• downloadrpd
• uploadrpd
• renameapproles
• deletapproles
• renameusers
• deleteusers

All these commands invoke under the covers a new REST API that enables to collect information and perform certain operations on your instance.

- Examples of information that can be collected  from the API

• health of BI System instance
• Service instance status
• list of services instances

- Examples of operations that can be performed

• create/delete new service instance
• Check BI service instance overall health, components health, dependencies health
• Export BAR file
• perform operations on the RPD such as  those we listed under the new commands (upload/download RPD etc.) , getrepositories (list of RPD from the BI Server),

To obtain an exhaustive list of services, one can use a REST client such as RESTClient or curl. The screen shot below shows an example using a RESTClient plugin for Firefox:

The REST API can be invoked from the following endpoints (PORT is the managed server port, 9502 by default)

http://[hostname]:[PORT]/bi-lcm/v1/s1 : for all listing service instances, creating a new service instance

http://[hostname]:[PORT]/bi-lcm/v1/s1/ssi : for information and operations on our single service instance (ssi)

• http://[hostname]:[PORT]/bi-lcm/v1/s1/ssi/bar : for exporting the service instance as a BAR file ( for more information about a BAR file see following post)
• http://[hostname]:[PORT]/bi-lcm/v1/s1/ssi/health: Check SI status and diagnose issues:
• /bi-lcm/v1/s1/ssi/health/status: overall health check

• /bi-lcm/v1/s1/ssi/health/checks: health check for SSI

• /bi-lcm/v1/s1/ssi/health/components: health check for internal components

• /bi-lcm/v1/s1/ssi/health/components: health check for dependencies
• 
• http://[hostname]:[PORT]/bi-lcm/v1/s1/ssi/rpd: Operations on the RPD for this SI
• /bi-lcm/v1/s1/ssi/download/downloadrpd:
• /bi-lcm/v1/s1/ssi/rpd/uploadrpd:
• /bi-lcm/v1/s1/ssi/rpd/listconnectionpool:
• /bi-lcm/v1/s1/ssi/rpd/updateconnectionpool:
• /bi-lcm/v1/s1/ssi/rpd/users/rename:
• /bi-lcm/v1/s1/ssi/rpd/users/delete:
• /bi-lcm/v1/s1/ssi/rpd/approles/delete
• /bi-lcm/v1/s1/ssi/rpd/approles/rename

Utilities

Coming back to the new commands listed in the documentation, there is a main script called data-model-cmd.sh to run that takes each of the new command line utility as an argument.

[ORACLE_HOME]/user_projects/domains/bi/bitools/bin/data-model-cmd.sh

downloadrpd

To get the correct syntax  for each of the new command line utilities, run the script data-model-cmd.sh with the –h flag; note that the flag needs to be lowercase; for example to show the correct syntax for the downloadrpd command, run the following:

./data-model-cmd.sh downloadrpd–h

Usage

Note that the port is 9502 on my default installation

you will be prompted to enter the RPD password, then a status message will be displayed.

 

Note that the logging level was set to FINEST in order to get more debug output, this is done by altering the logging.properties file located in:

[Oracle_Home]/bi/modules/oracle.bi.commandline.tools/scripts/

uploadrpd

Usage

./data-model-cmd.sh uploadrpd  -h
usage: downloadrpd -I <RPD filename> -W <RPD password> -U <cred user> [-P <cred password>] [-SI <service instance>] [-S <host>] [-N <port>] [-SSL] [-Y]
-SSL  - use a secure connection to the server

listconnectionpool

Usage

 ./data-model-cmd.sh listconnectionpool -U <cred user> [-P <cred password>] [-SI <service instance>] [-S <host>] [-N <port>] [-V <true/false>] [-O <outputFile.json>] [-SSL]

File outputFile.json content:
{
"Title":"List Connection Pools",
"Conn-Pool-Info":[
{
"uid":"<uid1>",
"connPool":"<conn pool name1>",
"parentName":"<parent name1>",
"user":"<username1>",
"password":"<password1>",
"dataSource":"<data Source value1>",
"appServerName":"<app server name1>"
},
{
"uid":"<uid2>",
"connPool":"<conn pool name2>",
"parentName":"<parent name2>",
"user":"<username2>",
"password":"<password2>",
"dataSource":"<data Source value2>",
"appServerName":"<app server name2>"
}
],

Output: JSON file containing the list of connection pools and details

updateconnectionpool

Usage

 ./data-model-cmd.sh updateconnectionpool -C <connPoolList.json> -U <cred user> [-P <cred password>] [-SI <service instance>] [-S <host>] [-N <port>] [-SSL]
File connPoolList.json content:
{
"Title":"List Connection Pools",
"Conn-Pool-Info":[
{
"uid":"<uid1>",
"connPool":"<conn pool name1>",
"parentName":"<parent name1>",
"user":"<username1>",
"password":"<password1>",
"dataSource":"<data Source value1>",
"appServerName":"<app server name1>"
},
{
"uid":"<uid2>",
"connPool":"<conn pool name2>",
"parentName":"<parent name2>",
"user":"<username2>",
"password":"<password2>",
"dataSource":"<data Source value2>",
"appServerName":"<app server name2>"
}
],
"Variables-In-Conn-Pool":[
{
"uid":"<uid1>",
"variable":"<VARNAME1>",
"value":"'<VALUE1>'"
},
{
"uid":"<uid2>",
"variable":"<VARNAME2>",
"value":"'<VALUE2>'"
}
]
}

Input file: JSON file containing the changes to be made to the connections pool (1 or many). Example is shown below

listrpdvariables

Usage

./data-model-cmd.sh listrpdvariables -U <cred_username> [-P <cred_password>] [-SI <service instance>] [-S <hostname>] [-N <port_number>] [-V <comma or new line separated FILE containing selected variables names>] [-O <outputFile.json>] [-SSL] [-H]

File outputFile.json content:
{
"Title":"List Rpd Variables",
"Rpd-Variables":[
{
"uid":"<uid1>",
"variable":"<VARNAME1>",
"value":"'<STRING_VALUE>'"
},
{
"uid":"<uid2>",
"variable":"<VARNAME2>",
"value":"<NUMBER_VALUE>"
},
{
"uid":"<uid3>",
"variable":"<VARNAME3>",
"value":"'<EXPRESSION>'"
},
{
"uid":"<uid4>",
"variable":"<VARNAME4>",
"value":"<FUNCTION>"
}
]
}

Output: JSON file containing the list of RPD variable

Rather than displaying all the RPD variables, you can create a CSV file containing the list of variables you’d like to output and feed it to the script with the –V flag.

updaterpdvariables

Usage

/data-model-cmd.sh updaterpdvariables -C <rpdVariablesList.json> -U <cred_username> [-P <cred_password>] [-SI <service instance>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

File rpdVariablesList.json content:
{
"Title":"List Rpd Variables",
"Rpd-Variables":[
{
"uid":"<uid1>",
"variable":"<VARNAME1>",
"value":"'<STRING_VALUE>'"
},
{
"uid":"<uid2>",
"variable":"<VARNAME2>",
"value":"<NUMBER_VALUE>"
},
{
"uid":"<uid3>",
"variable":"<VARNAME3>",
"value":"'<EXPRESSION>'"
},
{
"uid":"<uid4>",
"variable":"<VARNAME4>",
"value":"<FUNCTION>"
}
]
}

renameapproles

Usage

[oracle@demo bin]$ ./data-model-cmd.sh  renameapproles -T <approlenamelist.json> [-L <plugin list>] -U <cred user> [-P <cred password>] [-SI <service instance>] [-S <host>] [-N <port>] [-SSL]

File approlenamelist.json content:
{
"Title":"Target Application Roles",
"App-Roles":[
{ "oldname":"<current_approle1>", "newname":"<new_approle1>" },
{ "oldname":"<current_approle2>", "newname":"<new_approle2>" },
{ "oldname":"<current_approle3>", "newname":"<new_approle3>"}
]
}

deletapproles

Usage

renameusers

Usage

 ./data-model-cmd.sh -T <usernamelist.json> [-L <plugin list>] -U <cred user> [-P <cred password>] [-SI <service instance>] [-S <host>] [-N <port>] [-SSL]

File usernamelist.json content:
{
"Title":"Target Users",
"Users":[
{ "oldname":"<current_user1>", "newname":"<new_user1>" },
{ "oldname":"<current_user2>", "newname":"<new_user2>" },
{ "oldname":"<current_user3>", "newname":"<new_user3>" }
]
}

 

deleteusers

Usage

 ./data-model-cmd.sh  deleteusers -T <usernamelist.json> [-L <plugin list>] -U <cred user> [-P <cred password>] [-SI <service instance>] [-S <host>] [-N <port>] [-SSL]

File usernamelist.json content:
{
"Title":"Target Users",
"Users":[
{ "name":"<user1>" },
{ "name":"<user2>" },
{ "name":"<user3>" }
]
}

The purpose of this post was to experiment with the new command line tools and to list all the commands with the correct syntax for easy future reference. The new commands are part of the first version v1 of the BI Lifecycle Management REST API; there no doubt that more commands will be added to the API in future releases, this is a big step forward for automating operations and monitoring the BI instance without having to interact with the UI.

N.B.

I was unsuccessful at running the user and role management commands (last 4 command listed) : the error message is “Operation failed”, using curl to perform the same operation by invoking the REST API directly failed as well. As soon as we find resolve this issue, this post will be updated.

Migrating from OBIEE 11g to 12c: Sample App v406 as an example

The intent of this post is to document the migration steps from OBIEE 11g Linux based Sample Application v406 ( version 11.1.1.7.140415) to 12c and experiment with a new migration utility that was introduced in 12c.     The migration method as indicated in the documentation is “Out-of-place” where a new OBIEE 12c installation is performed  followed by a migration of relevant files to the new installation using the BI migration tool. This new tool generates a single cohesive artifact containing the RPD, web cat and security settings that can be imported into the BI 12c environment; “In-place” (OBIEE binaries upgraded) is no longer available. Note that only OBIEE release 11.1.1.7 or later can be migration to 12c. The migration process used to be a major pain point in the OBIEE lifecycle management.

Install OBIEE 12c

The installation steps were covered in a previous post; even though those steps were for Windows 7; they are  very similar to those in Linux. Note that Sample App v406 include JDK1.7; OBIEE 12c requires JDK1.8_051. You can download the the Java rpm from the Oracle website and install it by issuing the following command

then configure Java to use the latest JDK, details on updating Java are covered in this blog.

One thing to note is that the start|stop|status services take quite a long time to run; even longer in Linux than in Windows from my preliminary testing.According to Oracle Support the potential root cause may be the lack of “entropy” on the Linux operation system not this kind of  entropy, rather the following type of entropy.

Generate the Migration Tool

The tool creates a bundle OBIEE 11g archive which contains security configuration information, OBIEE RPD and web catalog. The migration process is a 2 steps process: first we create the bundle and then we import the bundle into the newly created 12c system. The BI migration tool is a jar file made available after OBIEE 12c installation; it is located in

[ORACLE_HOME]/user_projects/domains/bi/bitools/bin/migration-tool.sh

where bi is the domain name

We run the following command to generate the BI Migration Tool:

./migration-tool.sh package bi-migration-tool.jar

Note that when your run

./migration-tool.sh

at the command line, it will echo the following;

This may mislead you into thinking that the command expect arguments such as “out” etc… That is not the case! The correct syntax is indeed the one that takes “package” and the output jar file as an argument. The syntax above will be used after creating the jar file to generate the export bundle (next section).

• “package” tells the BI Migration Script to perform a packaging operation.
• “bi-migration-tool.jar” is the filename of the migration tool jar file where the output is written.

Here is the jar file that was created

Now that we’ve generated the bi-migration-tool.jar, we can create a bundle that contains an export of our metadata information from the 11g Oracle home.

Create the export bundle

The export bundle is created by running the following command at the shell prompt

java -jar bi-migration-tool.jar out <oracle 11g home> <domain home> <output export bundle path>

Where

• out tells the migration tool to perform and export operation
• <oracle 11g home> is Oracle BI home directory (Oracle_BI1)
• <domain home> is the BI11g domain directory which is with out of the box installation  user_projects/domains/bifoundation_domain directory
• <output export bundle path> is the export bundle where the output is written, note that the output file has to have .jar extension

• /app/oracle/biee/Oracle_BI1 is the OBIEE Sample Application v406 Oracle BI Home
• /app/oracle/biee/user_projects/domains/bifoundation_domain is OBIEE Sample Application  v406 Oracle BI Home
• /home/oracle/11gexport.jar is the export bundle

When it’s done and it is successful, the last message is “Migration action succeeded” .

The export bundle  has been created in /home/oracle/

Importing the Export Bundle

 

There are 2  approaches for importing the previously created export bundle:

1) Using the the BI 12 configuration assistant; this is done when you’re performing the BI 12c configuration for the first time. This is the method recommended by Oracle.

2) Using the BI Migration script to import the export bundle , this is for cases when the configuration has already done (domain and BI service instance have been created)

We shall use the second approach given that we’ve already configured our BI 12c environment.

The same script migration-tool.sh script will be used with a different flag “in”

migration-tool.sh in <export bundle> <service instance name>

Where:

• in tells the migration tool to perform an import operation
• <export bundle> is the previously created export bundle
• <service instance name>  is the BI service instance. (ssi)

According to the Oracle documentation, “<service instance name> specifies the name of the service instance, which is service1”.  We observed that the script failed when the argument “service1” is included; the error message is following:

Configuration error: MSI not supported. Service instance must be: ssi

After a while, you’re prompted to enter the RPD password.

One can monitor all the configuration changes by looking at the log file.

If the migration is successful; Migration action succeeded is displayed.

Next steps after migration

Once the migration is done, the documentation recommends making updates to the repository in order to avoid authentication issues; this is applicable when migrating from OBIEE 11.1.1.7 to 12.2.1. Note that OBIEE Sample App v406 is version 11.1.1.7.140415 therefore this step is not applicable; however I will go through the steps in order to get accustomed with the new command line tools such as  downloadrpd.

Open the OBIEE repository in offline mode:

Navigate to [OBIEE DOMAIN HOME]/bitools/bin

and run the command below:

./data-model-cmd.sh downloadrpd –u uname –p pwd –o downloaded.rpd –w rpd_password

Where:

• downloadrpd is a new command line introduced in 12c, it will downloads the OBIEE repository
• uname is username
• pwd is the password
• -o is the output RPD
• -w is the repository password

To get help on the expected argument, run the following command line

./data-model-cmd.sh downloadrpd –h

We run the command, if successful the message RPD download completed successfully is displayed.

One thing to note is that instead of service1 the OBIEE 12c instance is named ssi , the Oracle documentation does not include the –SI flag, however without it you’ll run into an error message:

Operation failed. Service Instance key “service1” does not exist, probably because service1 is added implicitly as a command line argument when omitted.

 

Note that OBIEE 12c is more command line centric for deployment, it is no longer possible to upload the OBIEE repository from Web Logic enterprise manager.

We then open the OBIEE 12c repository using the Oracle BI Client Tool which unfortunately still runs only in Windows.

The DYNAMIC_OLAP_LOGIN is not present in the list of variables.

Top upload the “updated” repository, we use a new command line utility uploadrp that will load the OBIEE repository ONLINE!!!

No need (no longer possible) to log into Weblogic enterprise manager, navigate to the correct domain, lock the configuration, upload, apply, restart etc…

./data-model-cmd.sh uploadrpd –u uname –p pwd –i downloaded.rpd –w rpd_password –SI ssi

here again, note that the flag –SI is missing from the documentation.

 

If upload is successful, you get the following

Now the moment of truth; log into the new OBIEE 12c application and…success, we can clearly see the list of dashboards from the v406 Sample App.

As a sanity check, let’s display the 2.10 Vanilla Charts dashboard to confirm that the analyses are running correctly.

Sure enough, the charts/analyses are rendering correctly!

Post Migration steps

There are Post Migration steps to be done after migration not covered here, for more details, refer to  the Oracle documentation, the high level steps are listed below:

• Oracle BIEE

  • Migrating Catalog Groups
  • Migrating Configuration of Oracle BI EE
  • Configuring the Database to Use DataDirect Drivers
  • Configuring Usage Tracking
  • Configuring the SQL Server
  • Adding Roles and Permissions
  • Configuring MySQL for Oracle BI
  • Checking the BI JavaHost Configuration
  • Enabling Clusters
  • Enabling Oracle Hardware Acceleration and Compatibility Mode
  • Setting Compatibility Framework for Oracle BI Server

• BI-Publisher

  • Migrating the BI Publisher Configuration
  • Migrating Scheduler Jobs and Job History
  • Removing the BISystemUser Policy from the JMSResource Security Configuration
  • Configuring Passwords for Oracle BI Publisher Data Sources

• Essbase

The documentation makes reference to a new tool : the Oracle BI Baseline Validation Tool which enables you to identify differences during life cycle operations such as migration, it doesn’t look like the new tool is available for download yet.

In this blog post, we went over the migration steps from OBIEE 11g to OBIEE 12c using the OBIEE Sample Application v406 (version 11.1.1.7.14); there are several new command line utilities and tools introduced in 12c that will simplify the OBIEE lifecycle management; however there are still several steps that will have to be implemented manually.

Epilogue: (to quote the new BI 12c config): It turns out that the Oracle BI Validation Tool is bundled with the BI12c client download; it is located in [BI Client Install Directory]\bi\components\oracle.bi.bvt