How-to: Hadoop Integration with OBIEE 11g

The newest release of Oracle Business Intelligence 11.1.1.7 shows Oracle's continued efforts in trying to integrate its Oracle Business Intelligence Platform with big data technologies such as Hadoop and Hive. Specifically, I'm talking about OBIEE 11g's ability to integrate with a Hadoop Data source.

What is Hadoop?

Hadoop is a framework that enables data to be distributed amongst many servers (nodes), commonly referred to as a 'distributed file system'. The data is not stored in a single database, rather it is spread across multiple clusters.

How does Hadoop process data stored in multiple nodes?

Hadoop uses a programming model called 'MapReduce' for parallel processing across multiple nodes. At a high level this is comprised of two steps:

1. Map step
1. The map step takes the data, divides it into smaller sets of data and distributes the result to worker nodes
2. Reduce step
1. The reduce step collects the data from all of the worker nodes and aggregates it into a single 'output'

What is Hive?

MapReduce functions are generally written in Java and generally require someone with deep knowledge in both Hadoop and MapReduce. The guys over at facebook created a technology called 'Hive' which is a data warehouse infrastructure that sits on top of Hadoop. More simply, Hive does the 'heavy lifting' of creating the MapReduce functions. In order to query a Hadoop distributed file system, instead of having to write MapReduce code, you generate sql-style code in a hive language called 'HQL'

Why does this matter in the Oracle Business Intelligence / Analytics space?

The analytics space is experiencing a shift in both technology and function. Traditional BI projects required a 'data warehouse' to store data in a series of star schemas (denormalized models) for quick query generation and data retrieval. The development and support of the data warehouse is achieved through a team of ETL developers whose main focus is to create the mappings that perform the data transformation from the source to the target.
Unless the functional requirements are clearly understood during this phase, value is usually lost in the data transformation and the potential to eliminate relevant data is certainly possible.

Using OBIEE 11g's Hadoop integration via a Hive ODBC, OBIEE can directly query distributed file systems via Hive. What does this mean? The potential now exists to eliminate or reduce the need for ETL as we now have the ability to directly query gigantic file systems.

The saving grace to ETL developers is that a need still exists for someone to create the HQL functions that populate the 'tables' that OBIEE uses. Ultimately, it could be a change in how ETL is developed.

How do you integrate OBIEE 11g with Hadoop?

Step 1: Download the Hive ODBC Drivers from http://support.oracle.com

You can reference Oracle Note 'Using Oracle Hadoop ODBC Driver with BI Administration Tool [ID 1520733.1]'

Step 2: Create a Hive ODBC Connection via the ODBC Data Source Administrator

Similiar to how you create an ODBC connection to edit the repository online, you're going to create an ODBC connection but this time specify the driver as 'Oracle Apache Hadoop Hive WP Driver'

Once you've created the ODBC Data Source Connection, you can configure the Driver set up under the 'General' tab:



Step 3: Configure Database Connection

Moving into the repository, you're going to create a new database connection like you would for any data source in the physical layer. Note that you need to specify the database type as 'Apache Hadoop' (this is important!).

Step 4: Create Connection Pool

Within the Apache Hadoop database connection you just created in step 3, create a data source with a call interface as type 'ODBC 2.0' or 'ODBC 3.5'. The data source call interface should not be 'Apache Hadoop' (you've already specified the database as type as Apache Hadoop!). If you specify the data source call interface as 'Apache Hadoop' you will receive the following error:

Your connection pool should be similar to the following:

You should now be able to import your tables and columns just like any other connection pool. The BI Server will generate normal SQL statements as if it were querying a traditional Oracle database, but the Hive ODBC driver in turn converts that to HQL which is used to execute mapreduce functions to query the Hadoop distributed file system across multiple nodes.



 

keywords: hadoop, obiee 11g, hive, mapreduce, HQL

How-to: Automatic Repository Deployment and Promotion Process OBIEE 11g

A typical deployment process an OBIEE 11g repository will follow in most production environments resembles the following:

The development zone represents a series of developer machines modifying a repository either by:

• Multi User Directory Environment Configuration (MUDE)
• Local development machines where each developer migrates their changes to a centralized OBIEE 11g dev/unit test box via a patch-merge process

We're going to focus on the 'Production Deployment Path' that takes the repository from the Dev/Unit test machine and migrates it through the deployment path from Assembly Test through Production.

This production path is critical because it's at this point where the repository leaves the 'safe haven' of the developer environment and goes through various stages of testing, usually performed by another team. Each testing team will have their own BI Server and database that the repository must connect to for testing.

Usually, the repository remains the same through all environments except for:

• Connection Pools
• Environment specific server variables

We're going to perform the assembly test to production deployment process in a completely automated fashion by:

• Generating an XUDML file that connections connection pool information
• Generating a new system test repository by applying the System test XUDML to the assembly test repository
• Using WLST to upload the RPD to the specif iced environment

 

 Step 1: Generate the XUDML file for the assembly, system, staging and production environments

We're going to create an eXtensible Universal Database Markup Language (XUDML for short) that contains connection pools specific for each environment. This file is generated by biserverxmlgen and is basically the repository exported to XML. The way to accomplish this in OBIEE 10g was using UDML which has seen been deprecated and is not supported by Oracle - see Oracle Note 1068266.1.

Step 1.1 - Set Variables via bi-init.sh

. /export/obiee/11g/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh

Note the space between the '.' and the '/' . This is required for the i-init.sh script to propagate through all folders

Step 1.2 - Generate XUDML file

Navigate to export/obiee/11g/Oracle_BI1/bifoundation/server/bin/ and run:

biserverxmlgen -R C:\testconnpool\base.rpd -P Admin123 -O c:\testconnpool\test.xml -8

• Replace base.rpd with your source RPD - i.e. if you want to generate connection pool information for assembly test, base.rpd should represent your assembly test repository.
• -O generates the output XML file
• -8 represents the UTF-8 formatting for the XML file
• -P represents the password of the base repository

If fail to set your session variables will you encounter the following error:

"libnqsclusterapi64.so: open failed: No Such file or directory"

If you are successful, your output should be as follows:

Step 1.3 Remove inapplicable entries

For connection pool migrations, your script should only include:

<?xml version="1.0" encoding="UTF-8" ?>

<Repository xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DECLARE>
<Connection Pool ......>
</ConnectionPool>
</DECLARE>
</Repository>

You will only need to re-generate this file if you change your connection pool information. This XUDML file will be used to update connection pools of your target environment.

Step 2: Apply XUDML file to base repository 

Let's say you have an assembly test repository and a system test XUDML file. The biserverxmlexec.sh script will take your assembly test repository, system test XUDML file and generate a 'system test repository' using the following command located in export/obiee/11g/Oracle_BI1/bifoundation/server/bin/

biserverxmlexec -I input_file_pathname [-B base_repository_pathname] [-P password]
-O output_repository_pathname

Where:

• input_file_pathname is the name and location of the XML input file you want to execute base_repository_pathname is the existing repository file you want to modify using the XML input file (optional). Do not specify this argument if you want to generate a new repository file from the XML input file.password is the repository password.
• If you specified a base repository, enter the repository password for the base repository. If you did not specify a base repository, enter the password you want to use for the new repository.
• The password argument is optional. If you do not provide a password argument, you are prompted to enter a password when you run the command. To minimize the risk of security breaches, Oracle recommends that you do not provide a password argument either on the command line or in scripts. Note that the password argument is supported for backward compatibility only, and will be removed in a future release.
• output_repository_pathname is the name and location of the RPD output file you want to generate

Example:

biserverxmlexec -I testxudml.txt -B rp1.rpd -O rp2.rpd
Give password: my_rpd_password

You now have a system test repository that you can upload to your applicable environment.

Step 3: Upload Repository to BI Server via WLST

Many web sites show how to upload the repository via the FMW Enterprise Manager, but that is generally alot slower and not as efficient as scripting it.

The uploadRPD.py script below performs five tasks:

• Connects to WLST
• Locks the System
• Uploads the RPD
• Commits Changes
• Restarts BI Services

Copy the code below and save it as a python script (.py)

connect('user','pass','server')


user = ''
password = ''
host = ''
port = ''
rpdpath = '/path/path2/repository.rpd'
rpdPassword = ''

# Be sure we are in the root
cd("..\..")

print(host + ": Connecting to Domain ...")
try:
domainCustom()
except:
print(host + ": Already in domainCustom")

print(host + ": Go to biee admin domain")
cd("oracle.biee.admin")



# go to the server configuration
print(host + ": Go to BIDomain.BIInstance.ServerConfiguration MBean")

cd ('oracle.biee.admin:type=BIDomain,group=Service')
biinstances = get('BIInstances')
biinstance = biinstances[0]


# Lock the System
print(host + ": Calling lock ...")
cd("..")
cd("oracle.biee.admin:type=BIDomain,group=Service")
objs = jarray.array([], java.lang.Object)
strs = jarray.array([], java.lang.String)
try:
invoke("lock", objs, strs)
except:
print(host + ": System already locked")

cd("..")

# Upload the RPD
cd (biinstance.toString())
print(host + ": Uploading RPD")
biserver = get('ServerConfiguration')
cd('..')
cd(biserver.toString())
ls()
argtypes = jarray.array(['java.lang.String','java.lang.String'],java.lang.String)
argvalues = jarray.array([rpdpath,rpdPassword],java.lang.Object)

invoke('uploadRepository',argvalues,argtypes)

# Commit the system
print(host + ": Commiting Changes")


cd('..')
cd('oracle.biee.admin:type=BIDomain,group=Service')
objs = jarray.array([],java.lang.Object)
strs = jarray.array([],java.lang.String)
invoke('commit',objs,strs)


# Restart the system
print(host + ": Restarting OBIEE processes")

cd("..\..")
cd("oracle.biee.admin")
cd("oracle.biee.admin:type=BIDomain.BIInstance,biInstance=coreapplication,group=Service")

print(host + ": Stopping the BI instance")
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke("stop", params, signs)

BIServiceStatus = get("ServiceStatus")
print(host + ": BI ServiceStatus " + BIServiceStatus)

print(host + ": Starting the BI instance")
params = jarray.array([], java.lang.Object)
signs = jarray.array([], java.lang.String)
invoke("start", params, signs)

BIServerStatus = get("ServiceStatus")
print(host + ": BI ServerStatus " + BIServerStatus)

The aforementioned code works on scaled out (clustered) environments since there is only one active admin server. The code will connect to the active admin server located in your first node, and WLST will propagate changes to each node. You can validate this by navigating to the local repository folder of each node.


To run the script, load wlst located at :

 /export/obiee/11g/oracle_common/common/bin/wlst.sh

and perform the execfile command as follows:

execfile(‘/path/path1/path2/uploadRPD.py’)

In conclusion, the entire repository deployment process can be executed by the following two scripts:

• biserverxmlexec (provided by Oracle)
• uploadRpd.py (see above)

Reference: Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition

keywords: Repository, RPD deployment, repository deployment automation, obiee 11g, scripted rpd deployment, wlst

FYI: User able to log-in even with Invalid (or no) password in OBIEE 11g

With the release of Oracle Business Intelligence (OBIEE) 11g, the 'old' (read: OBIEE 10g) way of authenticating users has been deprecated, mainly - authenticating a user's credentials via the repository which i've outlined in this post . At its core, 10g authentication was accomplished using init blocks that populated the :USERS and :GROUPS session variables in the repository.

OBIEE 11g, on the other hand, authenticates via authenticators in weblogic such as Oracle Internet Directory. This guide actually covers OID authentication in OBIEE 11g .

The OBIEE 10g method for authentication still exists in 11g, and unfortunately it is still possible to configure 11g init blocks so that the query does not check the password of the user.
For example:

SELECT USER_ID FROM USERS WHERE USER_ID = ':USER'

would just check the user id and not the password was correct but not check the password. In a scenario where such an INIT block exists and is set to act as an authentication block, this can lead to users being able to log in with any (or no) password. It can also lead to some apparently odd/inconsistent behaviour. Consider the scenario where Users A and B both exist in OID which is set as the primary identity store. But User B also exists in a database which is referenced by an INIT block as described above. Both try to login using the wrong password. User A will simply fail. However, while User B will fail Authentication against OID, because the BI Server knows there is an Authentication INIT block set, it will then attempt to run that for each of them and in the case of User B, because their username is in the USER_ID column of the USERS table, they will be allowed in as the INIT block query apparently succeeds, even though it does not in fact correctly check the user’s password.

There is no 'fix' for this other than to force username validation for init blocks that use the :USER block or completely avoid using the :USER session variable.

Oracle has acknowledged this in security document 1359798.1

keywords: OBIEE 11g security, data level security, initialization blocks,  USER session variables, weblogic authenticators, 

FYI: OBIEE 10g to 11g Upgrade Process Validation Plan and bug 1467168.1 (Multiple Pie Charts Displayed for a Pivot Table)

So you've just completed your 10g repository and web catalog upgrade to Oracle's new 11g platform, congratulations! Per Oracle's Fusion Middleware Upgrade Guide for OBIEE 11g you're now ready to to perform, in my opinion, the most difficult task of the upgrade process : the validation of the upgraded environment (as pictured below):

A comprehensive OBIEE validation plan should include the following:

1. A thorough comparison of functionality of existing reports in the web catalog
2. Confirmation of customizations in the repository, including but not limited to:
1. level based measures
2. physical/bmm star schema
3. complex joins
4. derived measures
5. hierarchies
6. variables (session, static, dynamic)
3. Security - the upgrade to 11g will require you to make security changes to your 10g model, so customization is required, but you'll want to confirm the following none the less:
1. object level security in the web catalog
2. data level security in the repository
1. note* that any filters applied to your Groups in the 10g rpd will not be present in the 11g RPD as groups have been removed from the metadata layer completely

Security Manager 10g:

Security (Identity) Manager 11g:

Now as you complete step 1 of your validation plan ( A thorough comparison of functionality of existing reports in the web catalog)

You notice that all of your 10g pivot table pie charts are all being generated incorrectly! Rather than 1 pie with x slices, your 11g pivot table pie charts are x pies each with one slice.

10g pivot table pie chart:

11g pivot table pie chart:

Before you begin the process of estimating hours to manually re-configure every pie chart in your system, WAIT!

This is actually a known 11.1.1.6.0  bug [ID 1467168.1] which can be fixed using Oracle's oPatch software and Patch 14003822 via Oracle Support

The steps on how to apply this patch, or even utilizing oPatch in general, are hazy at best so below is a step by step outline on using oPatch w/ Patch 14003822 :

Step 1: Identify your oPatch directory

Normally located in your $ORACLE_HOME/opatch directory. After identifying your directory, add the oPatch path to your system PATH variable as you'll be running the 'opatch' application from command line.

Step 2:  Confirm core oPatch files exist 

By running the following via command line:

  - opatch lsinventory -jre $ORACLE_HOME/jdk/jre
Your output should be familiar to the screen below:

Step 3:  Install  Patch 14003822

Navigate to the directory that contains your unzipped Patch folder 14003822 and perform the following command:

   - opatch apply -jre $ORACLE_HOME/jdk/jre
Your screen should be similar to:

A successful patch will result in the following message:

Step 4: Validate Changes

BEFORE:



AFTER:

Original 10g chart for reference:





keywords: obiee 10g to 11g upgrade, 1467168.1, obiee 11g pie charts, obiee 11g upgrade, obiee 11g roadmap

How to: OBIEE 11g LDAP Authorization using database tables & stored procedures (advanced)

In our last post, we focused on the first step of OBIEE security configuration - 'authentication'. Authentication is the process of OBIEE validating your login credentials against a pre-defined source, such as LDAP, an external database, or even the RPD. This blog will focus on the second  step of OBIEE security configuration  - 'authorization'.

Authorization is 2 fold.

First: It can be used to control access to dashboards and reports, this is known as object level security.

Second: It can be used to control the viability of data on a report, this is known as data level security.

This how to will focus on the authorization using an external database table as the source.

Step 1:

Create a connection pool (to be used for your authorization) init block:

Best practice is to store the data source name (your database) and user name (your schema) in static repository variables such as OLAP_DSN and OLAP_AUTH_USER

Step 2:

In Variable Manager, create a new initialization block:

Step 3:

Configure the initialization block to point to the connection pool you created by clicking 'Edit Data Source' -> Specify the data source type as 'Database' -> Browse to the connection pool

Step 4:

You'll notice that once you specify the data source type as 'database' and configure the connection pool to point to the authorization connection pool you created earlier, you'll now see a box called 'Default initialization string'. This is where you put the SQL code to return the groups for a specific user.

In the above example, we are using:

 SELECT getSecurityGroup(UPPER(':USER')) FROM DUAL                                        )

This statement says 'run the getSecurityGroup' stored procedure from the OLAP_DSN connection we specified in our connection pool and pass getSecurityGroup the parameter 'USER' which is a session variable populated in the Authentication process. 

getSecurityGroup stored procedure code is basically:

     Select Group From wc_auth_table_d

      where upper(ldap_id)=upper( name_in);

A username is passed in, and the group that user belongs to is returned to OBIEE

The wc_auth_table_d needs to have atleast the following columns:

Name      Null     Type             
--------- -------- -----------------
LDAP_ID   NOT NULL VARCHAR2(50 CHAR)

GROUP NOT NULL VARCHAR2(50 CHAR)

LOGLEVEL  NOT NULL VARCHAR2(20 CHAR)

 

 Step 5:

The above SQL statement returns 'GROUP' for a specified user, which now needs to be saved to a session variable.

Save the result to a variable target called 'GROUP' by clicking Edit Data Target and adding a new session variable called 'GROUP'.

Step 6:

Authentication needs to be ran prior to this Authorization block so you must set the execution precedence to run 'Authentication' first. Click 'Edit Authentication Precedence' and add your authentication block.

  

Step 7:

Your Authorization init block should be similar to the image below: 

Now if you log into your answers environment, your access will be limited to the groups 

 in wc_auth_table_d. If you pass authentication but but fail authorization (i.e. your username does not appear in the auth table), you will see the following error:

How to: Edit Repository (RPD) Online OBIEE 10g/11g

For a fairly simple task, there is relatively little information on OTN and the OBIEE Admin guide on

How to enable online repository editing:

This how to assumes you're setting up online editing on a windows server that already has, at the minimum, AdminTool installed.  Online repository editing relies on the ODBC protocol to connect to your linux-hosted repository.

-----
 On your windows client, navigate to the Open Database Connectivity (ODBC) screen and select the 'System DSN' tab.

 Click Add and Select 'Oracle BI Server' as the data source

Type in the appropriate name and description. Under the 'server' drop down menu, type in your linux-hosted repository server

Specify the username, password and port #  on the screen below. OBIEE uses default port 9703. Consult your nqsconfig.ini file for details.

Hit Next, ODBC will automatically test your configuration settings. Once complete, click finish and then navigate to AdminTool.exe

Click 'open online' (the blue folder) as seen below:

You should now see the ODBC connection you created as a selectable repository option

keywords: meta data repository, rpd, edit rpd online, rpd online, obiee rpd, obiee rpd admin tool

How to: OBIEE 11g LDAP Authentication in Repository

Recent challenge at a client site :

How to configure OBIEE 11g to authenticate using LDAP.

There are 2 main steps to configure OBIEE 11g to authenticate users via LDAP:

1) Create LDAP Server connection:

Manage -> Security
Within Security Manager : Action -> New -> LDAP Server

At the minimum, you will need to identify your company's LDAP server, version, port # and base DN. Base DN identifies the starting point of your authentication search. For example, if you want to search all entries under the o=test.com subtree of the directory, o=test.com is the base DN

If you leave Bind DN and Bind password blank , anonymous binding is assumed. Note that LDAP v2 does not support anonymous binding.

Hit the test connection button to confirm your settings are correct:

2) Create a LDAP initialization block that points to the LDAP server you just created:

Manage -> Variables
Within Variable Manager: Action -> New -> Session -> Initialization Block

 Specify LDAP as your data source type, click browse and select the LDAP server you just created (as seen below)

Hit OK, then click 'Edit Data Target'. You will have to create at a minimum: 2 session variables - "USER" and "DISPLAYNAME" with the corresponding LDAP variables.

If LDAP successfully authenticates, the USER and DISPLAYNAME variable be passed data which you can use for data-level authorization.

Click the 'New' button below, and create the 2 variables as specified in the image.

Row-wise initialization will allow you to create session variables dynamically at the initialization of the session. For Basic LDAP authorization, this can remain unchecked

Hit OK to navigate back to the main screen 'Session Variable Initialization Block'. On the bottom left hand corner you will see a button labeled test:

Click 'Test' and type in a valid LDAP username & password:

If successful, your session variables will return valid values:

If Invalid, you will get the following values:

Now restart OBIEE presentation services , and your user should be able to authenticate via Answers.

You will still need to be create an Authorization policy for this user, by assigning them to applicable groups. You can find that covered in this guide  - OBIEE 11g LDAP Authorization using an external database as the source

In Summary:

1) Create LDAP Connection

2) Create Init Block

3) Create LDAP USER and DISPLAYNAME session variables

4) Associate session variables with LDAP Init Block.