Playing with CAF(OBIEE Content Accelerator Framework)


I am right back after hectic day-to-day schedule ! Now I will try to explore a new OBIEE feature called Content Accelerator Framework (CAF) version 1 . It is a free utility available in OTN and yet not part of OBIEE 10g default installation .Possible reason could be , as this utility come in the mid way of transition between 10g and future version 11g .Hopefully Oracle will provide a polished automated CAF release bundled in 11g version to thrash out Legacy Web Replication method and will provide a full-proof intelligent migration tool.

What is CAF/The Objective and Purpose

It is a RPD and Catalog migration utility to replicate/clone and synchronize RPD objects and existing reports from Source environment to Target OBIEE environment. It also helps to create duplicate report and duplicate logical RPD constructs from one OBIEE environment to other even if source and target environment has nothing in common and only basic logical model at Target environment .This in-turn help to propagate the changes made on RPD /Catalog  from one environment to other .This could even done after selecting each and individual object.

Overcome from Legacy Web Replication

Webcat replication seems to be an old school method while CAF approaches to make a revolutionary change in Migration aspects .The tool is simply amazing with its wide variety of features . As we all know Webcat Replication is not a very robust method while doing the synchronization. There are lot of glitches while anybody try to adopt this .Not only this , the configuration and network based setup is very much important for doing a successful webcat replication which sometime seems to be a headache for a Configuration Manager .However it is very good in clustered based synchronization arena and not typically in migration .In CAF approach the migration task would be much more simpler and not so cumbersome to handle .As the entire Catalog based concept changes from legacy .webcat file after Oracle takeover the product ,it is predictable that Replication strategy need to change as well . Lets see what OBIEE11g comes with !!!

Why this Blog Thread

There are lots of so-called OBIEE Guru’s already blogged about CAF . However my intension is to blog it with nitty-gritty after deep diving so that readers could feel how it works across various Proof Of Concept approaches.

Installation Prerequisite

1) OBIEE 10.1.3.4 and later (not OBIEE 11g !)

2) JDK 1.6 or later (Note : Make sure the JDK version is correct and correct PATH entry is there as Environment variable parameter)

Installation and Configuration

Download CAF and extract it under below path .Note that don’t rename the Temporary subdirectory “C:\OracleBI\TEMP_CAFV1_Install” and the extracted content should be under the mentioned path and directory to avoid any issues.

Now make sure OBI Catalog manager is not running and all the process is killed i.e. “catalogmanager.exe, java.exe, javaw.exe” . Otherwise unwanted JAR error will be thrown.

Navigate to the directory ..\OracleBI\web\catalogmanager\plugins.

Create a backup of the file com.siebel.analytics.web.catalogmanager_1.0.0.jar and move the backup file outside of the OBIEE directory structure.
Delete the original com.siebel.analytics.web.catalogmanager_1.0.0.jar file from ..\OracleBI\web\catalogmanager\plugins.
Lets try to open Catalog Manager by selecting Start > All Programs > Oracle Business Intelligence > Catalog Manager. Catalog Manager should not open and you should receive an error message similar to the following:

Now run the batch file from the extracted directory and continue it by pressing “Y” .

Now try to open Catalog Manager once again and you can see two new option added as ‘Syncronize’ and ‘Clone’ . This indicates that CAF has been installed and configured successfully .

So lets discuss about how functionally it is working across several perspective in a step by step different kind of approaches.

Approach 1 – Cloning Catalog Object from Dev to Test Environment

CAF Cloner Framework

I have created a RPD called dev_rpd , duplicate it and rename it as test_rpd .The dev_rpd will be used as Source RPD and test_rpd as Target RPD .Both RPD’s having same SA and same metadata objects configured based on Default ‘EMP’ and ‘DEPT’ table  joins .A report and prompt is created in ‘dev_catalog’ .This will be used as source of target catalog ‘test_catalog’ .The Online RPD is dev_rpd and it has been copied to “\OracleBI\server\Repository\dev” to make an offline access to it.Another offline RPD is test_rpd and kept under “\OracleBI\server\Repository\test” .

Lets configure instanceconfig.xml to point “test_catalog” .Start the Presentation services .Initially it will be blank catalog .We will see how to synchronize dev_catalog items to online test_catalog now.

Open the Catalog Manager and open dev_catalog offline and Click on Clone .That does mean I want only Emp report to be cloned in target blank web catalog i.e. test_catalog .

It will pop-up content accelerator wizard .If the Target Catalog is not online it will throw below error :

Otherwise move to the next screen to Select Source and Target offline RPD location and Online Target Catalog URL prefix (BI service plugin ‘analytics’ should be excluded)

Now choose the Target Subject area where you want the items to be migrated .

Click Next .This will parse the Emp request/report XML and extract the columns from it to display at the left-pane.Whereas at the Right pane it selects the Presentation tables from Target RPD .

Below I have clicked EMPNO from left pane and double clicked to the EMPNO from EMP table at right pane to get the final Mapped Values .As you can see after hovering the mouse you can see the Data Type of the target mapped column.This datatype will be extracted and identified from Target RPD automatically .

Any incorrect mapping will throw below error :

Do all the mapping as below :

Lets change the target request name from default “/shared/Cloned” to make it in sync with source catalog .Check the “Create Dashboard” option to make a new dashboard “emp_dashboard” at target catalog .(You can skip Dashboard creation option,in that  case only Answers request will be Cloned at Target) .Also please note that we have same named dashboard “emp_dashboard” at “dev_catalog” and same report emp added under that dashboard which we want to cloned in same name at Target catalog “test_catalog” .

You can edit the target request name also .Finally click on Finish. This results below successful cloning log .

Now lets login to Online ‘test_catalog’ and I can see a new dashboard created having the same report.Also it is available in Answers .

So the Cloning has been succeeded from Source Development environment to Target Test environment.

You can notice 2 things : Target RPD test_rpd has been renamed to test_rpd_backup before change and target catalog report description has been changed i.e. marked red in above image .How pretty and careful CAF is !!!

To be Continued ……….

Updated as of 17-Feb-2011 … I am not resuming this thread anymore as new OBIEE 11g comes with enhanced migration capability without CAF …

Presentation Catalog/Services Caching


As we know there are two kind of Caching is available in OBIEE .One of them is most popular and familiar i.e. BI Server Caching and other is BI Web Server caching . Presentation Catalog Caching is rarely used and it rarely take any attraction in performance . However a most significant use of it will be, bypassing the default report level Caching in real-time BI application i.e typical Transactional Application where the back-end data changed in a moment and need to be reflected in the Analytics report (though BI is not built for OLTP query)  .In such scenario it would be meaningful for developers to force bypass the web query caching and let analytics hit the database once again to fetch updated data , if any .This could be manually done by pressing Refresh button at the bottom left of Browser window .

However if there are multiple dashboards and say user1 wants dashboard1 data updated on real-time after back-end ETL populates, then rather suggesting user to click on Refresh button each time is not the best option as fickle minded user1 could see stale data and can raise a hue and cry unnecessarily on old data .That could be easily avoided using Bypass Web Server Cache feature in Advanced tab of the corresponding reports .

Bypass NQQuery Cache

This feature would not only giving the user  flexibilities to customize the set of reports where the real-time data reflection needed but also avoiding a global change in cache config params under instanceconfig.xml which will be applicable for entire platform and possibly that could not be the right choice at right time !

If you are running multiple instances of Oracle BI Presentation Services (either through replication or clustering) you need to add the following entries in the Oracle BI Presentation Services configuration file (instanceconfig.xml) to manage when the Presentation Catalog cache is to be updated from disk .
<Catalog>
<AccountIndexRefreshSecs>120</AccountIndexRefreshSecs>
<AccountCacheTimeoutSecs>180</AccountCacheTimeoutSecs>
<PrivilegeCacheTimeoutSecs>180</PrivilegeCacheTimeoutSecs>
<CacheTimeoutSecs>120</CacheTimeoutSecs>
<CacheCleanupSecs>600</CacheCleanupSecs>
</Catalog>

Pivot table – Conditional Coloring and Tooltips


Once I was working for a giant UK based client where we had a requirement to replace the existing .Net system . As everybody know that OBIEE is not a programming language rather a tool and there must have certain limitations as this could not be customized like a language .This is pretty hard to make a realization to them as they mostly focus on business needs rather thinking from technical perspective . Which is true indeed ! So sometime we say this is not possible very upfront and outright and sometime we need to further dig down on any alternative solution or workarounds . Sometime it is successful and sometime not . So this is all about the background of this thread and how myself succeed to help there business achievement .

The requirement was something like there would be multiple measure which need to be displayed against dimensions in pivot table . “Measure 1”  is a target achievement measure which need to be shown either Green or Red status based on target value set (target value is not hard-coded and its dynamic and driven by a backend threshold value ). For other measures at the end month there might be some order which is confirmed , some unconfirmed and some yet to be build . Now once user hover mouse to the colored cell (of those status represented in Legend) it should show the information in tooltips .A valid information in this scenario could be specifying  the dynamically changed date time window and mention the type of the period i.e building , confirmed or unconfirmed .

Lets have a look on the pivot report :

Conditional Coloring and Tooltip

Nice huh ?? I have actually googling around to get a readymade solution and unfortunately it doesn’t help . Finally after building this I even get surprised about nothing is technically impossible !

Nows lets see the how the measure and its status entirely driven dynamically in RPD  .Only change done in webcat is treating the data format as HTML .

 Conditional Coloring and Tooltip2

Conditional Coloring and Tooltip3

 

 

However you can put same calculation in Catalog itself in column formula to achieve this . But for this let BI server understand that you want “Server Complex Aggregate” to represent the conditional column measure .

Conditional Coloring and Tooltip4

 

 

 

 

 

The commentary is another interesting stuff where based on status of each month order Admin user want to add/update there comments which navigated to a target where Writeback has been implemented . Commentary code is like below :

 

Conditional Coloring and Tooltip5

Report Title Navigation – OBIEE


Till release 10.1.3.4 , as I know , there is no facility/feature to navigate from  Report title to any Dashboard . But we had a customer requirement to get that feature so when you click on “Top 10 Sales Overview” report title it will move you to “Actual Sales” Dashboard page . There is no OOB facility to cater this requirement as the Title doesn’t accept any HTML code inside it . You could push this reqr back and could come up with an alternative solution to provide a link just below that report which will finally navigated to the target dashboard .

However this could be achieved if we do a little bit tweak with the report views . So I have found a workaround which is definitely behave like you are clicking on title and navigating to dashboard . Requirement is to show the navigation like below :

Title Navigation1

 

 

 

 

 

 

 

So as you see I have moved my subtitle info to Title view and pick up Narrative view , match the color and template like default OBIEE title template and achieve my business .Internally the Title region will be driven by Narrative view rather actual Title view .Here is the Narrative view code to fulfill the objective .

Title Navigation2

Multiple RPD & Multiple Presentation service instance on single BI Server


The objective of the thread is to understand the process of configuring multiple RPD and Presentation service in same BI server instance .Once that will be understood it can be further extended in scalable solution to integrate the approach on multiple server based instance as per business demands .The OBIEE suite documentation mentioned this functionality could be achieved but never mentioned the detail steps how to achieve this .

The initial bit could be found at below URL of Mark Rittman though not in detail :

http://www.rittmanmead.com/2007/09/11/managing-multiple-presentation-services-on-the-same-unix-box/

My goal in this thread is to clearly define the entire process however multiple Presentation Services instances on a machine is “NOT SUPPORTED BY ORACLE” .

The effective use of this approach will support multiple RPD editing online and also modifying multiple BI catalog online through two different instance .Also this would facilitate the running of failover instance in real life production environment .Apart from that customer could also ask for hosting two different (dev and test) instance(environments) in single host . 

Now lets assume that the two RPD i.e deba.rpd & test.rpd and there respective two catalogs both working properly while loaded individually and working independently .

Configuring the NQSCONFIG

Lets configure the NQSCONFIG.ini file as below :

[ REPOSITORY ] 
RPD1  =       deba.rpd , DEFAULT; 
RPD2    =     test.rpd ;

Configuring the DSN

Create 2 Oracle BI Server System DSN i.e DSN1 to point to default repository deba.rpd and DSN2 to point to test.rpd . Since we are configuring two RPD in parallel to single BI server instance hence  for both DSN we point to server Local with default Port 9703 . The 9703 port is ODBC communication port between BI Server and Presentation Services .

Multiple RPD and Presentation Instance-1Multiple RPD and Presentation Instance-2

 

 

 

 

 

N.B : whatever repository will be put under change Default repository section will be override by the DEFAULT settings in NQSCONFIG.ini .You can tick “Connect to Oracle BI Server to obtain default settings” ,so that RPD connection will be established as per the definition in .ini file after BI server restart .

Configuring the Catalog and instanceconfig.xml

For both deba.rpd and test.rpd we have catalog file under ../$OracleBIDataHome/web/catalog/” as “deba” and “test” having different sets of reports in each of them to distinguish properly.So lets make catalog “deba” online after making the below changes in instanceconfig.xml .

<WebConfig>
<ServerInstance>
   <DSN>DSN1</DSN>
   <CatalogPath>D:/OracleBIData/web/catalog/deba</CatalogPath>

Restart the services and log into catalog for “deba” and you can verify that the query running on deba.rpd and your dashboard display the reports relevant for catalog “deba” .Verify that from NQServer.log file .

Edit the xml and make the similar type of change to point to DSN2 and catalog “test” .It should work as well pointing to repo “test” .

Now till this point single BI server hosted two repository and you since the two repo available online hence you can easily make the online modification just by selecting the DSN from Admintool in online mode .

Deploying a new Presentation Service instance

As we know that there must be something different to distinguish two Web server instance . The typical URL is : http://localhost:9704/analytics where “analytics” is the context root and deployable J2EE module .My aim to create another context root by deploying a new instance of the analytics application which can be done by the Enterprise Manager as (http://localhost:9704/em). Typical default userid is “oc4jadmin” .

Lets follow the steps below to deploy the module “analytics.ear” from ..\$OracleBIHome\oc4j_bi\j2ee\home\applications .

Multiple RPD and Presentation Instance-3Multiple RPD and Presentation Instance-4

 

 

 

 

 

Multiple RPD and Presentation Instance-5

 Multiple RPD and Presentation Instance-6

 

 

 

 

Duplicating instanceconfig.xml for Presentation services

The second presentation service should use its own catalog i.e “test” and connect to the repo “test.rpd” while first presentation service would use “deba” .Hence there must be two separate xml web config file to point to different catalog .Duplicate the instanceconfig.xml file and create two xml as instanceconfig_deba.xml , instanceconfig_test.xml .

instanceconfig_test.xml

<WebConfig>   
<ServerInstance>
<Listener port=”9712″/>
<DSN>DSN2</DSN> 
<CatalogPath>../$ORACLEBIDATAHOME/web/catalog/test</CatalogPath> 

instanceconfig_deba.xml

<WebConfig>   
<ServerInstance>
<DSN>DSN1</DSN> 
<CatalogPath>../$ORACLEBIDATAHOME/web/catalog/deba</CatalogPath> 

Replace $ORACLEBIDATAHOME as per your env settings .

Configuring Presentation service communication channel for OC4J

Context root “analytics” can be communicated by OC4J using the default settings as per web.xml under “..\OracleBIHOME\oc4j_bi\j2ee\home\applications\analytics\analytics\WEB-INF” via default port 9710  .

But to communicate with context root “analytics_deba” I need to change the web.xml file under “..\OracleBIHOME\oc4j_bi\j2ee\home\applications\analytics_deba\analytics\WEB-INF” via default port 9712  .

Search for oracle.bi.presentation.sawserver.port and change the param-value into 9712.

Multiple RPD and Presentation Instance-7

 

 

 

 

 

 

After creating the new configuration files we have to re-start the BI Server , BI Presentation services. Please restart the OC4J instance also .

Running two presentation service parallelly

To start the new presentation services I create two batch file with below target shortcuts .

sawserver.exe -c $ORACLEBIDATAHOME\web\config\instanceconfig_test.xml

sawserver.exe -c $ORACLEBIDATAHOME\web\config\instanceconfig_deba.xml

First stop all service and then start BI and OC4J service and after that invoke the above two scripts .So the presentation services will be started and you can login and type the following URL  in your web browser:
http://server_name:9704/analytics
http://server_name:9704/analytics_deba

So presentation service application/context-root  “analytics” will refer to one repository file “deba.rpd”  and “analytics_deba” will refer to “test.rpd” but you now have the ability to create different reports, dashboards etc via different presentation services using single BI server env/resource .

N.B : You can define multiple catalog path in one instanceconfig.xml but only one will be active at a time and that will be the one which defined first .Hence the idea is to create multiple instanceconfig to access multiple catalog .