Loading Data into Oracle BI Cloud Service using OTBI Analyses and SOAP

Introduction This post details a method of loading data that has been extracted from Oracle Transactional Business Intelligence (OTBI) using SOAP into the Oracle Business Intelligence Cloud Service (BICS). The OTBI instance may either be Cloud-Based or On-Premise. This method may also be used to load data from Oracle Business Intelligence Enterprise Edition (OBIEE). It […]

Extracting Data from Oracle Business Intelligence 12c Using the BI Publisher REST API

Introduction This post details a method of extracting data from an Oracle Business Intelligence Enterprise Edition (OBIEE) environment that is integrated with Oracle Business Intelligence Publisher (BIP) 12c. The environment may either be Cloud-Based or On-Premise. The method utilizes the BI Publisher REST API to extract data from a BIP report. It also uses BIP […]

Understanding the BI Web Catalog Patching Process

Introduction Following on from a recent blog article for trouble-shooting RPD Patching issues, which you can find here, this article will cover the BI Web Catalog Patching process. In addition to an overview of the process, it will provide a methodology to understand exactly what changes the Patching Process is endeavoring to make. This could be useful […]

Integrating ODI with OBIEE: How to invoke ODI scenarios & Load Plans from OBIEE

Introduction This article demonstrates how to configure Oracle Business Intelligence Enterprise Edition (OBIEE) to invoke Oracle Data Integrator (ODI) web services.  In a data warehouse environment where the ETL/ELT tool is ODI and the business intelligence tool is OBIEE, it is common to find the need for BI users to execute ODI scenarios and ODI […]

How to Recover the RPD Repository Password in Fusion Applications or OBIEE

Introduction The RPD Repository file contains the metadata to create reports.  This includes details of the physical data sources and connections, the business model mapping with joins, and the presentation layer that is presented to report writers.  The RPD file is encrypted with the Repository Password.  Without that password – the BI Administration tool can […]

Front-ending a SAML Service Provider with OHS

This is a follow-up to one of my previous posts titled Integrating OBIEE 11g into Weblogic’s SAML SSO, where I mention the following when configuring the Service Provider: “The Published Site URL field value is the base URL for federation services on the server. For SAML2, make sure the webcontext path is saml2. This is […]

Integrating OBIEE 11g into Weblogic’s SAML SSO

SAML is a way to convey identity information across systems. It is an industry-accepted standard and especially interesting when you need to propagate user information between different security domains, because it can overcome the HTTP cookie limitations in cross-domain scenarios (although there are ways to solve that with OAM today) and implement the concept of  transient federation (also known as virtual users), where the user base is not shared between partners.

I’ve recently came across a scenario requiring OBIEE 11g integration into SAML 2.0 SSO. The requirement was that OBIEE 11g should be able to drive its authorization decisions based on the SAML Subject as well as SAML Attribute Statements from the SAML Assertion generated by a home-built Identity Provider (IdP). This post examine what can be done (and how) in this scenario.

The exact products versions used in this demo are as follows:

  • Platform: Windows XP Server
  • Oracle Weblogic Server 10.3.5
  • Oracle Business Intelligence Enterprise Edition

OBIEE 11g (since delegates authentication, authorization and credential management to OPSS, which means users, groups, policies and credentials are all externalized. One typical OPSS deployment mode is using an LDAP server as the general security store for all artifacts. Remember though, at the time of this writing, OID is the only one supported LDAP server for policies.

A quick look at OBIEE 11g overall architecture is necessary to understand what comes up right next. I wouldn’t dare going into the details of OBIEE architecture on this post, so I recommend you take a look at the product official documentation. A good starting point is here.

For our purposes here, the important aspect to notice is that OBIEE has some components that run on Weblogic and some components that do NOT run on Weblogic. The core BI services are provided by the BI Server and BI Presentation Services. These are C-based standalone applications indeed.
Within Weblogic, the BI Presentation Services Plugin interfaces with the standalone BI Presentation Services system component, providing the web experience for end users.

The picture below (borrowed from the product documentation) describes the OBIEE architecture.


Of particular interest for our topic is the Security Services component.

In SSO mode, the BI Server components gets the authenticated userid from Weblogic and calls back the Security Services to construct an object (containing user principals, like userid and groups) that is returned back to BI server, who in turn uses it to drive authorization decisions on its internal objects. This alone allows OBIEE to consume a SAML Subject for the purpose of authorization decisions. So far, so good.

But what about using SAML Attribute Statements to drive authorization decisions within BI server? That would require SAML Attribute Statements to be persisted into OBIEE identity store once the SAML authentication request hits the server, so that Security Services could construct the necessary object when called back by BI Server. But SAML Attribute Statements are not persisted in the identity store.

Such characteristic essentially moots SAML Attribute Statements in this type of scenario. And that makes the usage of virtual users along with attribute processing irrelevant in a SAML Identity Asserter which also dismisses the need of a SAML Authenticator.

I am not saying this is something impossible to be done. It would require some custom work.

Long story short, in, out-of-the-box, OBIEE can consume the Subject out of a SAML Assertion, and that userid has to exist in the configured identity store for OBIEE.

When a SAML assertion is received by the Weblogic server hosting the target application, Weblogic’s SAML Identity Asserter parses the assertion and interacts with the ACS (Assertion Consumer Service) to validate it. If everything is ok, the identified user in the SAML assertion is passed down to the next authentication provider, who effectively authenticates the user and instantiates a Java Subject in the container. Make sure one of your authentication providers following SAML2IdentityAsserter is able to authenticate the user.


In my demo, the SAML assertion is produced by another Weblogic server working as the IdP (Identity Provider), via a SAML 2 Credential Mapper. A credential mapper essentially maps an existing java Subject to some credential.

From a configuration perspective, there are three main things do be done on OBIEE Weblogic server side: i) configuration of a SAML 2.0 Identity Asserter along with enabling SAML 2.0 Service Provider, ii) change of Analytics’ web.xml and weblogic.xml deployment descriptors and subsequent redeployment and iii) configuration of OBIEE for SSO in Enterprise Manager. For completion purposes, I also show how to configure a Weblogic server to be the Identity Provider.

If you already know how to configure Weblogic Federation Services and what to expect when OBIEE managed servers are in a cluster, you can safely skip the next section as well as Configuring the Identity Provider section. There’s really nothing special particular do OBIEE in them.

If you’re configuring both the SP and IdP in a single host (for testing purposes), make sure to refer to each of them using different names. For example, in my setup, the SP is sp.us.oracle.com and the idp is idp.us.oracle.com.

1. Configuring the Service Provider

Reference documentation is here. Here’s a tour and some notes over my configuration:

1) Add a SAML 2 Identity Asserter to the set of authentication providers and restart the Admin server. This is necessary for enabling configuring the server as a Service Provider (SAML 2.0 Service Provider tab in Federation Services)

2) Configure Weblogic Federation Services by filling in SAML 2.0 General tab for the Service Provider server. In the case of OBIEE, this is bi_server1. The information entered here is given to partners through a metadata file and is used as a means to establish trust across servers.


Couple of important notes here:

  • The Entity ID field value determines the name of this SAML server to its partners. If you have a cluster, make sure every member of the cluster is assigned the same value. An IdP will use this value to identify the Audience to which the SAML message is addressed. As any server in the cluster should be able to process the message. By the way, when configuring a cluster of SAML servers, make sure their configuration is exactly the same. Notice you can use Weblogic’s recording capabilities to save your actions into a script and replay that in the other servers using wlst.
  • The Published Site URL field value is the base URL for federation services on the server. For SAML2, make sure the webcontext path is saml2. This is going to be used in the metadata file as the prefix to build the ACS (Assertion Consumer Service) endpoint necessary for allowing an Identity Provider to properly communicate with this Service Provider. For example, an Identity Provider would be provided the following ACS endpoint is the above Published Site URL is specified:


  • When OBIEE managed servers are in a cluster, there’s typically an HTTP load balancer in front of the servers. In such case, make sure the Published Site URL refers to the name and port of the load balancer, because they are the ones the IdP needs to know. And also, make sure the load balancer is configured for session affinity. Reason being the SP ACS is a stateless application, that upon creating a session, redirects the browser to the requested URL (/analytics). If that redirection hits a different server in the cluster, there’ll be no session there and the user will be sent back to the IdP who will ask for credentials again.
  • Also, when Weblogic SAML services are in a cluster, you must use RDBMS as Weblogic server security store to avoid synchronization issues of SAML data across servers. Such requirement is for SAML data only, it has nothing to do with application session management, which is configured on a per application basis. And RDBMS should be set as the security store from the ground up, when the domain is first created. Trying to change the security store from embedded LDAP to RDBMS is not recommended.

3) Enable BI managed server as a  SAML2.0 Service Provider.


Important Note:

  • Default URL is invoked whenever a requester hit this Service Provider without an specific target service URL.

4) Export the SP configuration to a metadata file. Notice that any subsequent changes to your SP configuration will require a new export. Click Publish Metadata button in SAML 2.0 General tab and save it to a file. Examine the file contents. Notice the presence of an X509 certificate, to be used by the partner to encrypt data targeted to this Service Provider. Yes, the certificate is obtained from the Identity keystore configured for the server.

5) Get the IdP metadata file and import it as a Web SSO partner in the Management tab of SAML 2
Identity Asserter configuration. You should end up with something like this:


Click the partner name and edit the configuration:


Important Notes:

  • Notice that Virtual User and Process Attributes are not checked. They only make sense if you can use a SAML Authenticator.
  • Redirect URIs are the set of URIs that when called trigger the SAML SSO mechanism. It is a must that they are also secured by the container, otherwise Weblogic won’t trigger the authentication process. Specifically, you must make sure that those mentioned applications use CLIENT-CERT as the auth-method in theirs web.xml.

2. Changing Analytics’ web.xml for security constraint and adding weblogic.xml for role assignments

As previously mentioned, Weblogic server must be able to trigger container authentication upon an unauthenticated request for a URI specified in the set of Redirect URIs defined in the Service Provider partner configuration. Analytics application already ships with CLIENT-CERT auth-method, which is what we want. However, it does not define any security constraints about which users should be allowed access to the application.

OOTB, OBIEE defines three groups: BIAdministrators, BIAuthors and BIConsumers, who are respectively assigned to three application roles in OPSS policy store: BiAdministrator, BIAuthor and BIConsumer. Each of these roles are obviously assigned distinct permissions across the system. Look at ${BI_DOMAIN}/config/fmwconfig/system-jazn-data.xml file in a default OBIEE installation for such definitions.

We need to grant those 3 groups access to the Analytics application in the standard JavaEE way, i.e., by adding a security-constraint to any resource under the analytics web context in web.xml. Additionally, we must map the role name in the security-constraint to those 3 groups in weblogic.xml.

You’ll need to “unjar” the analytics application, make the changes, “rejar” it and redeploy the application. You find the original ear file at ${BI_HOME}/bifoundation/jee/analytics.ear

In web.xml (add <security-constraints> and <security-role> elements):


    <web-resource-name>BI Analytics</web-resource-name>

weblogic.xml (it has to be created in the same folder as web.xml):

<?xml version = ‘1.0’ encoding = ‘windows-1252’?>
<weblogic-web-app xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”
         xsi:schemaLocation=“http://www.bea.com/ns/weblogic/weblogic-web-app http://www.bea.com/ns/weblogic/weblogic-web-app/1.0/weblogic-web-app.xsd”

Notice the <principal-name>s. They refer to the groups that OBIEE expect to find in the configured identity store. They are groups, not application roles. If you decide to change those names, make sure they match the application role assignments in the policy store.

If you want any existing user accessing the analytics application,  you can map allowedGroups role-name (defined in web.xml) to the users group (instead of BIAdministrators, BIAuthors and BIConsumers) in weblogic.xml. In Weblogic, any authenticated user is automatically assigned to the users group.

3. Configuring OBIEE for SSO in Enterprise Manager

There’s still one more configuration for SSO in OBIEE. In Enterprise Manager, enable SSO and set the provider to “Generic SSO” for OBIEE’s coreapplication, as shown:


The screen is reachable via clicking the “coreapplication” link on the left and then selecting “Security” from the “Business Intelligence Instance” drop down menu on the right.

4. Configuring the Identity Provider

Configuration of the IdP is straightforward, very similar to SP’s. Here, we create a SAML2CredentialMapper, enable the server as an IdP and import the SP metadata to the credential mapper configuration.

1) Add a SAML2CredentialMapper to the list of Credential Providers and restart the Admin server.


2) Fill in IdP’s General Info tab.


3) Enable the server as an IdP.


4) Get the metadata file exported from the SP and import it as an IdP partner in the SAML2CredentialMapper configuration.


Hope this can be useful to someone out there. Lots to digest, I agree. 🙂

OBIEE 10g SSO Integration with OAM 11g

In this post I share the necessary steps in order to integrate OBIEE 10g into OAM 11g Single Sign On with the little caveat that OBIEE Analytics application is deployed in Weblogic server.

OBIEE 10g has two installation modes: basic and advanced. For SSO integration, you must pick adavanced mode. And Oracle Application Server version or later is required.

OBIEE 10g deployment guide states that it can be implemented with any SSO solution that uses cookies, http header variables or JavaEE container server variables. That’s true indeed, and most of the configuration is actually performed on the OBIEE side.

OBIEE 10g implements SSO through the concept of impersonation. It retrieves the end user identity through one of the mechanisms mentioned above and uses an impersonator user to establish a session to the OBIEE server on behalf of the end user.

This post by no means intends to discuss OBIEE architecture or go into the details of OAM 11g. As a matter of fact, the latter is extensively discussed in this blog by my colleagues in the Oracle Access Manager Academy series. Reading strongly recommended. Fantastic material.

And what you’re about to follow has been implemented in a Windows XP box for demonstration purposes.

The exact product versions used were:

Oracle Business Intelligence Enterprise Edition
Oracle Identity and Access Management
Oracle Access Manager WebGates
Oracle Identity Management
Oracle WebTier Utilities
Oracle Weblogic Server 10.3.4
Oracle Containers For Java (OC4J)

Fasten your seat belts! Here we go.

1 – Install OBIEE 10g

When you install OBIEE 10g, you get a set of standalone component processes, some admin UIs and a front-end web application running on top of OC4J.

For the purposes of this post, we’re interested in the BI Server, BI Presentation Services and the BI Presentation Services Plug-in components. The BI Server is a standalone process that maintains the BI data model and connects to data stores. BI Presentation Services is another standalone process that present information worked by BI Server to clients via ODBC. BI Presentation Services Plug-in allows web clients to interact with BI Presentation Services. In JavaEE application servers, it is a servlet component delivered via the analytics.war web application.

Once OBIEE is installed, find the analytics.war file under $BI_HOME/web folder.

Shut down OC4J in case it’s running. We don’t need it.

2 – Deploy the analytics.war application in WebLogic

Simply use Weblogic console to deploy the analytics.war application. There really is nothing special here. Click click click and you should get the analytics application up and running in Weblogic.

3 – Install Oracle HTTP Server (OHS)

OHS front-ends Weblogic server. A mod weblogic routing rule will forward requests to the analytics application running in Weblogic.

4 – Create routing rule in OHS mod weblogic for /analytics URL

This step can also be accomplished via Enterprise Manager.
Open mod_wl_ohs.conf located under your OHS instance home config folder and type in the following:

   1: <IfModule weblogic_module>
   2:  WebLogicHost <!--weblogic-server-running-analytics-application-->
   3:  WebLogicPort <!--weblogic-port-->
   4:  Debug ON
   5:  WLLogFile /tmp/weblogic.log
   6: </IfModule>
   8: <Location /analytics>
   9:  SetHandler weblogic-handler
  10: </Location>


Make sure to replace the values in between <!– –>

The OHS instance home config folder is typically located at $ORACLE_HOME/instances/<instance-name>/config/OHS/ohs1

Restart OHS.

Checkpoint 1: at this point we should be able to submit requests to OHS and have them directed to Weblogic.

5 – Install OAM 11g

Nothing special here. Just follow OAM 11g install guide. It is a good idea to create one Weblogic domain along with one managed server for the OAM server.

6 – Install OAM 11g WebGate in OHS

The WebGate checks whether the executing user is authenticated before letting it access the analytics application.

7 – Register the WebGate in OAM Console

Simply follow OAM Administration Guide Instructions.

Here’s my WebGate definition:


On registration, by default, you get an application domain and Authentication and Authorization policies automatically configured for the patterns / and /…/*. You don’t need those policies. Remove them and add the /analytics/…/* as a protected resource to the set of Authentication and Authorization policies.


And make sure you copy the generated ObAccessConfig.xml and cwallet.sso from the OAM’s $DOMAIN_HOME/output/<agent-name> to the WebGate’s instance config folder, which is typically located at OHS’ $ORACLE_HOME/instances/<instance-name>/config/OHS/ohs1/webgate/config.

<agent-name> is the name you gave to your WebGate when you registered it in the OAM Console.

Restart both OAM access server and the WebGate.

Checkpoint 2: At this point we should be able to have the WebGate intercepting calls to /analytics URL running in WebLogic and asking for credentials. Upon entering them, the user would be re-challenged by BI login screen.

8 – Create Impersonator user for the BI Server

Connect to the BI Administrator tool and select Manage –> Security.

Select User, right click on the panel’s right side, select New User… and type in the user name. For this exercise, I am calling it Impersonator. Make sure it is a member of the Administrators group.

9 – Add Impersonator user to BI Presentation Services credential store (credentialstore.xml)

Navigate to BI’s home web/bin folder and type:

> cryptotools credstore–add–infile <OracleBIData>/web/config/credentialstore.xml


You are prompted for some information. Make sure the Credential Alias is impersonation (literally). Username and password should obviously match those you just provided in the previous step. Encrypt the password and give it a passphrase.

10 – Configure instanceconfig.xml

instanceconfig.xml is also located at <OracleBIData>/web/config.

In my case, <OracleBIData> is C:\OracleBIData.

In order to allow BI Presentation Services connecting to BI Server using the Impersonator user, add the following snippet as a child of <ServerInstance> element:



Make sure to enter the passphrase you chose previously.

In order to allow BI Presentation Services consuming the end user identity authenticated by OAM, add the following as a child of <ServerInstance> element as well:

<SSO enabled="true">

Here we’re instructing BI Presentation Services to use the OAM_REMOTE_USER http header value as the “impersonatee” user. OMA_REMOTE_USER is always put in the HTTP header by OAM upon successful authentication. BI will simply trust that. Dangerous? Oh yes.

Don NOT go to production without implementing a trusting mechanism between Weblogic and OHS. Weblogic should only accept requests from OHS. And the solutions to the rescue are 2-way SSL or some firewalling protecting Weblogic. Don’t let anyone sending requests directly to Weblogic!

Restart BI Server and BI Presentation Services processes.

Checkpoint 3: At this point SSO should work for /analytics. After getting challenged by OAM on accessing /analytics/saw.dll?Dashboard, you should be let in without any further authentication challenge by BI.

Notice that we still have two user repositories. OAM is looking at the Weblogic embedded LDAP server while BI is looking at its internal repository. That assumes the user is defined in both identity stores.

OBIEE 10g has the option of importing users and groups to its internal repository from external systems. That’s certainly an option, but it involves synchronization, which I am not a great fan of. Import and synchronization are available in the BI Administration tool.

If you seek a single identity store, keep reading.

11 – Define a new OID identity store in OAM

This step assumes OID has been previously installed. In this exercise, OID version is the one packaged in Oracle Identity Management
The application policy domain created when we registered our WebGate uses Weblogic embedded LDAP server as the identity store by default.

We need to change it, by pointing it to an external LDAP server. OID being the choice here.

This is done in OAM console. On the System Configuration tab, expand the Data Sources node and select User Identity Stores. Click the New button on the tool bar. Here’s my definition:


Then associate this identity store to the authentication scheme that is associated with the authentication policy protecting the /analytics/…/* pattern. This is done under Authentication Modules node on the System Configuration tab:


LDAP is the authentication module defined for the authentication scheme protecting our /analytics/…/* pattern.

Restart OAM server.

12 – Create an LDAP server in BI Server (the same OID identity store above)

Using BI Administration tool, define the LDAP server. Go to Manage –> Security –> New… –> LDAP Server


Click the Advanced tab and inform uid as the User name attribute type. uid is the attribute that univocally identifies the user in OID.


13 – Create a USER session variable in BI Server.

* Defining a USER session variable tells BI Server to authenticate users in an external repository. But in case of conflicting usernames, users defined in the BI repository takes precedence.

Using the BI Administration tool, go to Manage –> Variables. On the left side panel, under Session, select System. Right click on the right side and pick New USER…

14 – Create an LDAP Initialization Block for authenticating users in OID.

Initialization blocks are the means by which external repository data is communicated to BI server.

Again, using BI Administration tool, go to Manage –> Variables. Click Session. Right click on the right side and pick New Initialization Block… Give it a name, like Authentication Block.

Under Data Source, click Edit Data Source… button, pick LDAP as the type, click Browse button and pick the LDAP server you’ve defined previously.

Under Variable Target, pick the USER variable you’ve created. Inform uid as the LDAP Variable value.

You should end up with something like this:


Restart BI Server.

Checkpoint 4: at this point, you should be able to login with a user defined in OID and access the BI analytics application in SSO mode, but you’ll notice that the privileges within BI analytics look wrong.

15 – Implementing authorizations for BI using groups defined in an external LDAP server.

Unfortunately, OBIEE 10g does not retrieve group memberships directly from LDAP. But it is possible to implement it indirectly, by creating a virtual table in the Oracle database populated with LDAP user/group information (that can be done with DBMS_LDAP package).

Another option would be writing a SQL query directly against OID tables, but that’s too invasive and could break at any time due to changes in the OID schema, which is private.

Once you populate a table using DBMS_LDAP package, you can query it via a second Initialization Block and retrieve the group names for a given user, populating the GROUP session variable. This block should refer the Authentication initialization block we’ve defined earlier as a predecessor so that the USER variable is properly initialized with the authenticated user.

I am not done with this part yet. As time permits, I will come back with the virtual table definition as well as the initialization block.
But I guess there’s already plenty to do in case you want to try this out. Let me know about your experiences.

For more details…

Refer to product documentation.

OBIEE 10g Documentation Library (Deployment Guide has most of the information presented here).

OAM 11g Administration Guide