OBIEE “Act As Proxy” Feature


Right ! I am back with a article of  OBIEE 10g  feature called “Proxy User” .Also known as “Act As Proxy” .

So here we will learn about the usage of  Web Catalog Privileges option as below :

Act As Proxy0

The background of this utility is ,using this feature One user can act a proxy user and can give a proxy on behalf of other user here called Target user ,such a way that Proxy user can edit ,manipulate the request created by Target user . Being a Administrator ,he has to assign the Proxy permission to the group of people who will act as Proxy User.Thus Administrator will be delegating the permissions .Which proxy user will access which Target user report could be determined by the mapping defined under back-end table. So whatever the Report and Dashboard the Target user have access would be visualized by the Proxy User .

Sometime Global Administrator user want to see the reports of the target users for debugging purpose.In this scenario he/she might be interested for acting as Proxy User and assign the Act As Proxy for him/her . This is a typical scenario where this feature is suitable.

Below specifies the Proxy feature in step by step approach :

1) For my case I have build a repository using the SCOTT schema ‘EMP’ and ‘DEPT’ table .Apart from that I have created a PROXY_TABLE to maintain a relationship between Proxy and Target user list as below.Added that table in my repository .

Act As Proxy1

So here ACCESS_TYPE is a column which takes 2 values — full or restricted. If full access is provided, then the Proxyuser list would have edit/read privileges of the Target user Dashboards/Pages/Reports (N.B :- Proxyuser himself should have those read/edit privileges). If restricted access is provided, then the Proxyuser would only have read privileges (provided the proxy user himself has those privileges).

2)  My RPD configuration as below :  I have also created the  6 users  in RPD  appeared above  among  which ‘proxyuser1’ , ‘proxyuser2’ , ‘proxyuser3’ have been added under user group ‘Proxy_User_Groups’ . This user group I  have to create in Web Catalog as well  so that  Act As Proxy can  only be given to that group .

Act As Proxy23) Now we have to create the list of Session Initialisation Block and Variable to fetch the target user list based on the logged in Proxy user .

Act As Proxy3

Hence I create Init Block ‘Set_RUNAS’ which will populate a Non-System Session variable RUNAS defined later in sqltemplate file and it is acting as a parameter to that custom Logon template(see point 7 XML code) file .Since we will pass this variable in next Init block hence lets populate it using a dummy SQL as :

SELECT ‘SOMETHING’ FROM DUAL

Act As Proxy4

4) Lets initialize a System Session Variable ‘PROXY’ using the query below and set the proper execution precedence.

SELECT TARGET_USER FROM PROXY_TABLE
WHERE TARGET_USER = ‘VALUEOF(NQ_SESSION.RUNAS)’
AND PROXY_USER = ‘:USER’

Act As Proxy5 5) Initialize Proxy Level variable as Non-System Session variable  to define the access type using below SQL :

SELECT ACCESS_TYPE FROM PROXY_TABLE
WHERE TARGET_USER=’VALUEOF(NQ_SESSION.RUNAS)’ AND PROXY_USER = ‘:USER’

Act As Proxy66) Now lets put a instanceconfig.xml entry below just before tag </ServerInstance>

<LogonParam>
<TemplateMessageName>ProxyLogonParamTemplate</TemplateMessageName>
<MaxValues>200</MaxValues>
</LogonParam>

This will ensure that a new message template can be called where template name is “ProxyLogonParamTemplate” and the Max Value specifies maximum how many user can be defined as “Act As” .

Act As Proxy7

7) We need to define the custom message template as below and save the file as “ProxyUser.xml’ under “..\OracleBI\web\msgdb\customMessages” .

This custom message template for the proxy functionality contains the SQL to get the list of target users that a proxy user can Act As. This list appears in the User box in the Act As dialog box.

Act As Proxy8

<?xml version=”1.0″ encoding=”utf-8″ ?>
<WebMessageTables xmlns:sawm=”com.siebel.analytics.web.messageSystem”>
<WebMessageTable system=”SecurityTemplates” table=”Messages” translate=”no”>
<WebMessage name=”ProxyLogonParamTemplate”>
<XML>
<logonParam name=”RUNAS“>
<getValues>EXECUTE PHYSICAL CONNECTION POOL oradeba.Proxy_Conn
select TARGET_USER from PROXY_TABLE where PROXY_USER=’@{USERID}'</getValues>
<verifyValue> EXECUTE PHYSICAL CONNECTION POOL oradeba.Proxy_Conn
select TARGET_USER from PROXY_TABLE where PROXY_USER=’@{USERID}’ and TARGET_USER=’@{VALUE}'</verifyValue>
<getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL oradeba.Proxy_Conn
select PROXY_USER, ACCESS_TYPE from PROXY_TABLE where TARGET_USER=’@{USERID}’
</getDelegateUsers>
</logonParam>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

8 ) Now Restart BI server and Presentation server .

9) Using targetuser1 , targetuser2 I have developed two reports .So that when proxyuser1 logged in he/she would be able to Act as targetuser1 and can see the reports developed by targetuser1.Similarly targetuser2 report is also delegated to proxyuser2 .proxyuser1 could edit/change the targetuser1 reports but proxyuser2 would not be able to modify targetuser2 reports .

10)  Using Administrator login Goto Manage Privileges and under “Act As Proxy” set the Proxy user group .For my case, I have Granted the privileges to ” Proxy_User_Groups ” .

Act As Proxy9 11) I have logged in as proxyuser1 and landed to below reports which is proxyuser1’s own report not targetuser1 report. Now click on “Set As” under Settings link and you will get a small window open which will be populated using ‘targetuser1’ .As much as targetuser defined it will be get populated in this drop down list .

Act As Proxy10Act As Proxy11

12) Now clicked “ok” and landed to the report developed by ‘targetuser1’ in his/her own dashboard .As the ‘Edit Dashboard’ option is enabled hence you can do any modification here and save the report. Next time when ‘targetuser1’ will log into system he/she can easily find the changes done by someone ! here by its Proxy .

Act As Proxy1213) Now logout from ‘proxyuser1’ and logged in as ‘proxyuser2’ . Go to Settings and clicked on “Set As” .After that ‘targetuser2’ should be selected .Click “ok” .Now you can see ‘targetuser2’ dashboard reports .Also ‘Edit option’ is disabled since “proxyuser2” has “restricted” access level defined under PROXY_TABLE .

Act As Proxy1314) So thats all about OBIEE Proxy user feature ! So small but so robust … 🙂

OBIEE Clustering – Approach of Oracle BI Server Load balancing/Failover


Luckily I got a chance to get work on a BI Clustered Environment on behalf of a giant UK Customer .The situation demands a clustered shared repository and load balanced environment so that BI Server resource would be shared across multiple server and the consecutive request from loads of users will be distributed without being a lack of memory or a memory dump .

 As the application was so big that it could have to process million of request in a day hence the idea was to have a clustered shared BI server env . In addition to that there was multiple BI Presentation server was there .Since OBIEE still not have a active failover mechanism (until incorporated using DMZ network and deployed inside a failover Router) so we have gone for a passive failover mechanism where at any time one Presentation server will be used and absorb the request transmitted from Browser and another will be in hot standby mode and will be used in case of the Primary Presentation server stop response .

So lets make situation bit simple : We have “HostSAS1”  and “HostSAS2” act as two BI Clustered Analytics Server and “HostSAW1” and  “HostSAW2” act as two BI Web Server .At any time two SAW server can pass the request to any of the SAS server and depending on the load the BI Cluster controller module will redirect the request and process it by using any of the Analytics Server resource .

Clustering1

 

 

 

 

 

 

 

 

 

 

 

 

 

Hence “HostSAS1” and “HostSAS2” need only BI server components and Cluster controller installed and configured ,whereas “HostSAW1” and “HostSAW2” needs only Web server component installed .Since both SAS1 and SAS2 are only configured for single BI instance having single default DSN “AnalyticsWeb” hence to configure BI Clustering we need to configure those DSN’s in both server as below. Please note that we need to make atleast one server as Primary . Hence our case SAS1 has been made as primary .

Clustering2

 

 

 

 

 

Check the Clustered DSN option and put the Primary and Secondary Controller information (Either Host name or IP Address) in both servers .Make necessary changes on NQClusterConfig.ini file located under ..\OracleBI_Home\server\Config

 Clustering3

 

 

 

 

After that make the Oracle BI Server and BI Clustered Controller Service up and running and Click “Test Clustered Connect” option . This should throw a successful clustered connection message .That means the BI Cluster controller now actively participating and can handshaking with two BI Server services using Clustered ODBC connection port 9706 .

Pros and Cons :

BI Server Clustering is definitely a scalable solution by which we can tie multiple server together to handle the loads of inbound requests and thus perform a good load balancing and load sharing across them . This also enhance the BI server response and inturn the performance .

However there are couple of issues observed during the application/RPD syncronisation process during polling time window .One of the known problem is ,while you are doing online RPD editing from either Primary or Seconday server and save the RPD it would restart the BI server and copy the changed RPD and syncronised it across 2 servers .Thus ,eventually it make the consistency and integrity but if any user try to access report in between sometime it would throw errors as the BI server is restarting .

Moreover , Before restarting the BI Server during the cluster sync process the RPD usually copied into a separate location of Log .But after syncronisation it doesn’t delete the RPD from Log file location . So best practice is to remove that RPD from log after service down or making the services up in clustered environment .Otherwise if you try to put a fresh RPD in Primary server it could have been replaced and syncronised by previous RPD from log file and necessarily incur some inconsistent behavior and wrong results .Believe me this is my observation while working Online in Clustered env .

This is not a Cons rather a restriction which seems logically correct .What will about your Cache Files while you env is Clustered ? As we know Cache files must be build inside individual BI Server location as separate physical files and since that location is not shared across BI multiple servers .Hence to perform the Caching in Clustered environment, the physical location for storing and sharing cache entries need to be specified using parameter “GLOBAL_CACHE_STORAGE_PATH”  under NQSCONFIG.ini file .Also it is worth to monitoring nQCluster.log from “..\OracleBI_HOME\server\Log”, if Clustering is enabled .

N.B : – To operate clustering perfectly follow the Cluster and OBAS (Analytics Server) startup sequence judiciously i.e Primary OBAS up , Seconday OBAS up , Cluster Controller of Primary up and then Secondary Controller up .

RPD(Repoitory Directory) and Catalog File Name Syntax – nQSError


I was suddenly experimenting with the RPD file name syntax and came to know that some special character in .rpd file name will not be recognized by BI Server service and hence putting character like “$,#,%,<space>,-” inside the filename will throw below error in NQServer.log file and you will get below error message after Windows service startup . 

RPD-Filename

 

 

 

 

 

[47012] Syntax error in NQSConfig.ini file.

2009-10-02 17:01:26

     [nQSError: 26009] File D:\OracleBI\server\Config\NQSConfig.INI Near Line 33, Near <“>: new line in string.

However we can give special character in BI catalog file name and it loads by the Presentation server cool . So its somehow contradictory that certain syntax allowed in Catalog but not in Repository .See this :

RPD-Filename2

Online editing of Multiple RPD from Hetergeneous/Cross platform


One of our current assignment was to do the online development using one RPD from one platform say Windows and also need to do the testing of the different RPD hosted into different IBM AIX platform. Since AIX doesn’t support the GUI kind of stuff hence we have configured both the RPD in Windows env via multiple DSN configuration  . It’s pretty simple , easy and straightforward though need to cautious about some syntax .

As we know OBIEE installation do a default configuration of “AnalyticsWeb” System DSN (i.e Oracle BI Server Driver NQSODBC.dll)  which appeared in a box while developer try to open the RPD in *Online Mode* .

 

Multiple DSN1     Multiple DSN2

 

 However developer can simultaneously work with multiple RPD online where RPD’s even hosted under heterogenous platform .Only thing you need to know is that the IP address or Host name and the Port by which a SQL ODBC connection done between BI Presentation server and BI server(usually the default port is 9703 until customized and configured ) . So lets create a new System DSN .

Click Add on System DSN tab from Microsoft ODBC Datasource Administrator window . Select the driver as “Oracle BI Server” .Click Finish .

Then go  through the below steps . Here I have created a DSN called “Sample Scott” and let the other settings unchanged apart from Server “AIX_Server” .Before that make sure you able to ping that server from your local machine by running “ping AIX_Server” from command prompt .Otherwise you can put the AIX host IP address .Sometime you might need to map the hostname and IP address in “..\WINDOWS\system32\drivers\etc\hosts” file .

Multiple DSN3

 

 

 

 

 

If explicit login ID and password required to access Oracle BI Server from other environment (apart from  OS authentucation) enter the Login ID and password for the same and change the port if necessary .Since  I am not physically loading multiple RPD from my own BI server(physically 2 separate BI server running instance load that RPD into their memory) hence keep uncheck Default repository Catalog” .

     Multiple DSN4Multiple DSN5

 

 

 

 

 

Make sure the AIX server reachable and SAW  service working fine there once running the SAWPING utility from $ORACLEBIHOME\web\bin .

 >> sawping –v –s AIX_Server

It should return the response as : “Server alive and well” . This utility try to access Oracle BI Presentation Server services to lookup whether this could communicate with Oracle BI Server via 9703 port .

So now when I trying to open the RPD into Online mode it will give me two option as below .The “AnalyticsWeb” DSN instance will open the RPD from my local machine but “Sample_Scott” open RPD from “AIX_Server” .(Note that DSN name “Sample Scott” changed to “Sample_Scott” here )

Multiple DSN6

 

 

 

 

 

 

Trying to access “Sample_Scott” with proper password will throw the below error though my BI server instance running on AIX machine .

 Multiple DSN7

 

 

 

 

 

Can you identify what is the problem behind this ? The problem is BI server try to find a DSN called “Sample_Scott” whereas the actual DSN name is “Sample Scott” . Now lets change the DSN name to “Sample_Scott” and here you go ! there is no more problem . So in brief BI server doesn’t support the DSN having space between them . This is al about the syntactical caution we need to consider .

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 .

Migration from Siebel Analytics to OBIEE


The below text outlines the self-defined process and approach why and how we need to migrate Siebel Analytics to OBIEE . There might be some gaps in the entire definition of the process as the entire view is of mine and I did not face any typical issue while doing the migration , though there are couple of known bugs flying around in OTN discussion forum and in Metalink3 (Oracle Support) . So definitely there must be something !

If there are any specific points(pros/cons) somebody could like to add I will be happy to make the alteration !!!

Introduction

Oracle Business Intelligence (BI) is a portfolio of technology and applications that provides the industry’s first integrated, end-to-end Enterprise Performance Management System, including category-leading performance management applications, MIS applications, BI applications, BI foundation and tools, and data warehousing. Oracle Business Intelligence Enterprise Edition consists of components that were formerly available from Siebel Systems as Siebel Business Analytics Platform, with a number of significant enhancements.

Background

 As the  “Oracle First” procurement strategy has been defined by some big companies after a close tie up with Oracle hence it force them to decide,move and built the reporting platform on newly release BI component by Oracle called Oracle Business Intelligence(OBI) .This require migration of existing reporting platform from Siebel Analytics to OBI enterprise edition .

Why to Migrate

There are ample reasons to leverage the benefits of new OBIEE framework after doing migration of the existing platform. Some of the benefits have been described below:

User Interface Integration

 1)    OBIEE is an emerging, popular, enhanced BI system to provide the intelligent interface for both front and back office reporting

2)    Oracle BI Enterprise Edition is improved to support the business and technology requirements of application integration, not only from a user and data perspective, but also in the light of business process integration: moving from reactive looking-back-to-what-happened reporting to a pro-active, guiding, alerting intelligent business requirement

3)    Technically speaking lots of new feature with the enhancement of existing functionalities have been introduced for better reporting

4)     For the benefit of end use business communities lots of flexibility that can be the reason to adopt this

5)    Because of the 100% web-based and standards based architecture (SOAP) of the OBIEE platform it is very easy to integrate the OBI Presentation Services with a web based application like Siebel CRM or EBS .

Security Integration

 1)    Besides the critical requirement of single sign on and authentication, an important and time saving characteristic of a BI Application is the integration with the base application security and visibility rules. Typically large organizations have the requirement to secure company information and transactional data based on not only the user’s role or responsibility (Sales Rep or Sales Manager) in the organization but also on his or her position (CEO vs. CFO ). Based on the user’s responsibility, he should have access to certain analytical content, i.e. dashboards, reports, alerts, subscriptions etc.; based on the user’s position; he should have access to certain areas of the database, e.g. the data belonging to division A or B, team 1 or 2

2)    Oracle BI EE fully supports this role and position based security rules using the technique of row-wise initialized session variables and initialization blocks. Upon login and after authentication, queries are being executed against the database in a certain order to retrieve the security information which is stored in session variables like GROUP or POSITION. These variables are then used to control access to objects in the OBIEE repository or web catalog but also to build business model filters to restrict access to critical records. This integration becomes critical in a volatile organization where user change role and/or position frequently

Data Integration

       The pre-built Business Analytics Warehouse (BAW) is one single but modular data warehouse model to support one or a combination of the source systems mentioned. The BAW is compliant with the Ralph Kimball dimensional modelling methodology and supports slowly changing dimensions, aggregate tables, hierarchy tables and many others.

        The pre-built OBIEE repository and web catalog are aligned with the Business Analytics Warehouse and contain the domain specific and end user facing dashboards, reports and KPI’s , metrics, drill down paths, guided navigation, alerts, action links, traffic light alerts, etc .

Supporting Technology

 Without going into detail the most important characteristics of the Oracle BI EE Platform to support successful BI Applications are:

 1)    The caching technology which enables a significant performance improvement for standard dashboards and reports

2)    The open repository ODBC API which enables other enterprise ODBC compliant tools to access the OBIEE repository presentation layer and therefore the pre-built KPI’s, dimensional attributes, preserving the security business rules

3)    The ability to join multiple different data sources and data source types in the OBIEE repository

4)    The multilingual capabilities of the OBIEE platform that gives the ability to use it across wide enterprise

5)    Ability to use multiple RPD hosted on a single server and distributed across multiple presentation services in parallel which offload a large subject area

Scope

Scope of this document is to orchestrate for key steps while migrating existing Siebel Analytics platform to OBI .However this is very High level plan and lots of dependency and hidden low level strategy need to gradually define. This document is not intended to define the low level features, enhancement, flexibility that has been introduced in latest OBIEE version

Step by Step Activities

                     There are couple of activities that need to taken care of considering the roadmap of migration from existing Siebel Analytics Platform to Oracle BI EE platform.

Environment

1) Acquiring the latest OBIEE licensed version for client from Oracle . Confirm the version to be downloaded from eDelivery metalink provided URL
2) Removing existing Siebel Analytics installation across all development and test servers and carry out the fresh installation of OBIEE
3) Do the same installation at Performance test environment to check the Single-Sign on features
4) Finally install that into production servers by Oracle Architect and configure the related key business components

RPD Changes

1) Take the latest production copy of RPD at Dev Environment and do the version up- gradation.
2) Do the sanity and consistencies check across all Business model and rectify any repository consistency error, metadata warning etc.
3) Migrate it across test environment and Single Sign-On Performance test environment.

Webcat Changes

1) Take the latest webcat copy for the respective RPD at Dev Environment and do the version up-gradation.
2) Do a sanity check and sort out all the invalid objects and there references, broken links etc after running sawmigrate utility .This need to be taken care of recursively and in reactive approach.
3) Go through all the reports, prompts and links to see everything working properly.
4) Revisit Subject Area, Dashboard, Page, Shared folder, Objects level permissions.
5) Housekeeping for invalid, redundant dashboards/reports/objects.
6) Change the ‘Help Information’ HTML paths aligning with new OBIEE installed folder structures.
7) Migrate it across test environment and Single Sign-On Performance test environment.

Environment Configurations

1) Provides a choice to deploy the Presentation Services and Presentation Services Plug-in either standalone Oracle Containers for J2EE (OC4J) or in Microsoft IIS. If J2EE will be decided to be the container then IIS need to be removed from all environments and OC4J will be act as HTTP Web server to communicate with Oracle BI Web Client .However existing IIS can act HTTP Web server but this is subject to POC to remove the whole set prior to install the OBIEE copy afresh.
2) Couple of benefits are there using J2EE as Web Container rather IIS which is out of scope of the documentation.
3)  Configure the HTTP Webserver using the existing dev/test environment URL and the respective port. Configuring this into existing URL reusing the port is subject to POC and might be altered.
4) Configure the same into Production like SSO enabled environment e.g. Performance test environment.
5) Check the SSO functionality for authentication and authorisation. Also verify the same for any data and object level of security.
6) Deploy the RPD-Webcat at Production environment having basic functionality in place.
7) Any URL Port change for J2EE HTTP Server configuration need to reflect to Order Gateway URL, BI Arena URL and need communicate across users.
8) Alternatively IIS Web server can be redirected to new HTTP Server configured URL which is of no impact to anywhere. This is subject to POC again.
9) Revisit production server hardware configurations to see the capabilities.

Additional Required Configurations

1) Change the Skin file path default configuration parameters as per the new windows folder structure of OBIEE deployment.
2) Move the customized CSS, JavaScript’s, images into the Skin directory path.
3) Make the respective changes on OBIEE Analytics and Presentation Server configuration files.
4) Take the Oracle best practice approach to define the performance parameters as per the current Production hardware environment setup and Production DB Configuration parameters.
5) Configure the clustered environment setup for BI Server at production for replication.
6) Define the strategy for failover/ load balance presentation services.

Additional Optional Configurations

  Below additional components is not available in default setup and can be installed and configured depending on the requirement to leverage the other business benefits and experiencing the new version features at maximum.

1) Install & Configure OBIEE Scheduler’s for Delivers .Acquire the relay service and configure the details
2) Install & Configure OBIEE Publisher and the BI Publisher desktop plug-in
3) Install & Configure OBIEE Disconnected client
4) Install & Configure OBIEE Open Intelligence ODBC interface
5) Install & Configure OBIEE Briefing book reader
6) Configure the Caching Strategy.

Dynamic switch DB or Schema on OBIEE User Login


We have a requirement to switch between the DB’s based on the user login. We have two identical/similar databases with different connection string and different DB user/Schema name and password corresponding to that schema . But based on the users who logged in the report, the connection should switch. Also one more objective we need to fulfill here is that the password of the database schema that will be stored should be encrypted so that nobody can tamper this and obviously due to reason of security violation and it should be decrypted by the OBIEE before connect to that database schema .

To cater this reqr. I have followed the below steps :

1) Create a table “DB_DETAILS” in any db with the following details:

Column Name Data Type 
=======================
LOGIN_NAME VARCHAR2 (20 Byte)  
DBNAME  VARCHAR2 (30 Byte)  
DBLOGIN  VARCHAR2 (30 Byte)  
DBPASSWORD VARCHAR2 (256 Byte)  
DBPASSWORD_ENCRYPT VARCHAR2 (256 Byte) 

2) Here I use a package called : ENCRYPTION to encrypt and decrypt the password in DB_DETAILS table

CREATE OR REPLACE PACKAGE encryption AS

  FUNCTION encrypt(input_string IN VARCHAR2) RETURN VARCHAR2;
  FUNCTION decrypt(input_string IN VARCHAR2) RETURN VARCHAR2;

END;
/

 CREATE OR REPLACE PACKAGE BODY         encryption AS

   — Declare the values for the key.
   — The package will be wrapped to hide the encryption key.
   
   key_string          VARCHAR2(1000)  := ‘12345678’;
   encrypted_string    VARCHAR2(2048);
   decrypted_string    VARCHAR2(2048);

   — FUNCTION ENCRYPT encrypts encrypted string
      FUNCTION encrypt(input_string IN VARCHAR2) RETURN VARCHAR2 IS

   BEGIN
      encrypted_string    := null;
      dbms_obfuscation_toolkit.DESEncrypt(input_string => input_string,
                                            key_string => key_string,
                                             encrypted_string => encrypted_string );
       RETURN  encrypted_string;
   END;

   — FUNCTION DECRYPT decrypts encrypted string
      FUNCTION decrypt(input_string IN VARCHAR2) RETURN VARCHAR2 IS
   BEGIN

      decrypted_string    := null;
      encrypted_string    := null;
      dbms_obfuscation_toolkit.DESDecrypt( input_string    => input_string,
                                            key_string       =>key_string,
                                            decrypted_string => decrypted_string );

      RETURN  decrypted_string;
   END;

END;
/

2) Add the ‘DB_DETAILS’ table against ‘Proxy_Conn’ .

User Switch on DB1

 

 

 

3) Add the relevant data against ‘DB_DETAILS’ table .

User Switch on DB2

 

 

 

4) Make sure you have two database configured properly with above credentials .Update the DBPASSWORD_ENCRYPT field by running . Note that the DBPASSWORD column would have no use for this POC . This is for reference only .

UPDATE  DB_DETAILS SET  DBPASSWORD_ENCRYPT = ENCRYPTION. Encrypt(DBPASSWORD)  ;
COMMIT;

5) Create the below Session Var Initialization Block . Here I am decrypting the password and assign it to a variable called ‘dbpassword’ . User Switch on DB3

 

 

 

 

 

6) Now for the connection pool for which we need dynamic switching as per the logged in users, set the below properties in connection pool of that database in physical layer:
Data Source Name: VALUEOF(NQ_SESSION.dbname)
User Name: VALUEOF(NQ_SESSION.dbuser)
Password:VALUEOF(NQ_SESSION.dbpassword)

User Switch on DB5 

 

 

 

 

 

7) Create the Subject Area ‘EMP_DEPT_DB’ depending on the database ‘ORBIT’, Connection pool ‘Conn_Pool’ . Assign the Physical join , Logical join properly in the conventional method.

8) Now create two users in repository as ‘deba’ and ‘rosy’ . According to that setup when you entered thorough ‘rosy ‘ you should be able to see data for ‘EMP’ and ‘DEPT’ table from database : ‘ORBIT’ while user ‘deba’ will be able to see the data from database ‘OREIPR’.Note that in both cases only one Subject area has been used but the data differs depending on logged in Users from different databases.

User Switch on DB6

User Switch on DB7