FYI: Troubleshooting Security with the Oracle BI Security Diagnostics Helper

The hundreds of BI Security notes on Oracle Support, the countless blogs dedicated to OBIEE 11g security configuration, the myriad of configuration scenarios, test conditions, and troubleshooting steps documented in Oracle's Fusion Middleware Guide for Oracle Business Intelligence - what does all of this mean? The configuration of security within the OBIEE 11g platform is easier said than done.

With the release of OBIEE 11g, Oracle has made efforts to mitigate (or at least rule out) the potential risks associated with security configuration in their new weblogic-centric model. This is achieved through a little known helper utility called the 'Oracle BI Security Diagnostics' tool. This tool gives developers the ability to:

• Diagnose security configuration issues within OBIEE
• Test BISystemUser configuration settings
• Test authentication of users

This tool is included with OBIEE 11.1.1.6.4 and higher but the ear file still needs to be deployed to AdminServer.

Step 1: Install the Oracle BI Security Diagnostics helper via WLST

In your unix terminal, execute the following command:

MW_HOME/ORACLE_HOME/common/bin/wlst.sh addDiagnosticsCodeGrant.py t3://<WebLogic_host_name>:<WebLogic_port_number>

e.g.

mw_home/Oracle_BI1/common/bin/wlst.sh addDiagnosticsCodeGrant.py t3://localhost:7001

You're going to be taken through a series of terminal interactions starting with the initalization of WLST:

Followed by log-in credentials to connect to WLST:

 

And finally a success message "Added code grants to bidiagnostics" which really just means the EAR file is ready for deployment in AdminServer.

Step 2: Deploy bidiagnostics.ear to AdminServer

In Admin Console (default 7001/console), navigate to Deployments -> Control tab and look for a deployment called 'bidiagnostics (11.1.1)' . Check the corresponding box and click 'Install':

The deployment is a straight forward process, and after the installation is complete just make sure you set the bidiagnostic application to 'Active'.

Step 3: Access the BI Diagnostic Helper

 Once the installation is complete, you can navigate to the application by accessing the following url:

http://mycomputer:7001/bidiagnostics/security/diagnostics.jsp

You'll be taken to a very simple UI that will give you the opportunity to perform basic security diagnostics and (hopefully) resolve any OBIEE 11g security issues you may encounter!

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: Data Visualization with External Javascript Libraries (D3)

One of the great features of Oracle's Business Intellgience 11g foundation is the ability to integrate external applications via an API call or through the use of javascript libraries. In a previous article I discussed how to utilize javascript functions using OBIEE 11g's native UserScripts.js. Today we're going to expand on this functionality by integrating third party data visualization scripts. One popular javascript library used for data manipulation is 'Data-Driven Documents' . This open source scripting library gives users the ability to manipulate data using methods not available in OBIEE 11g.  Kevin McGinley first wrote about this in 2012 and the guys over at Rittman Mead recently posted an overview of D3 / OBIEE integration.  Below we're going to cover all the steps required to implement a D3 visualization technique.

Before we get started, you can view all of the D3 visualization methods at their github. In the example below we're going to use airline data to and D3's Calendar View to visualize average flight delays. You will need OBIEE 11.1.1.6.2 or higher (this example uses OBIEE 11.1.1.7.0) and IE 9+.

 

Step 0: Create an Answers Report

This report should contain a year dimension, a date dimension and an aggregate fact column. In the airline example I've selected 'Date', 'Year' and 'Average Departure Delay'. Take note of the column order as you will have to reference the column number in a narrative.

Step 1: Download the D3 Javascript Library from github

This is going to download a 'd3-master.zip' file that contains all of the javascript libraries needed for integration. You will unzip all of these files into OBIEE 11g's analytics ear deployment under Weblogic's Domain Home  located at :

 user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\b_mozilla\common

Step 2:  Create css file for Calendar Formatting

The Calendar view's javascript code is basically one script, with one function and one css file. These 'chunks of code' are all stored in the index.html using the example located on github, but in order for this view to play nice with OBIEE 11g, we're going to need to dissect components of the code into isolated narratives and css files. The first step is to take the css code:

#chart {

  font: 10px sans-serif;

  shape-rendering: crispEdges;

}

.day {

  fill: #fff;

  stroke: #ccc;

}

.month {

  fill: none;

  stroke: #000;

  stroke-width: 2px;

}

and save it to its own css file (calendar.css) located at:

user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\b_mozilla\common\d3\examples\calendar\calendar.css (you will need to create the directory as this doesn't exist)

Step 3: Create an Answers Narrative to Execute the Javascript Library

Now that we've laid the groundwork for calling the D3 library, the next step is to integrate the Calendar View code into an Answers narrative.

First create the script headers and link type to call the javascript library. This code will be stored in the pre-fix of the narrative:

<script type="text/javascript" src="/analytics/res/b_mozilla/common/d3/d3.js"></script>

<link type="text/css" rel="stylesheet" href="/analytics/res/b_mozilla/common/d3/lib/colorbrewer/colorbrewer.css"/>

<link type="text/css" rel="stylesheet" href="/analytics/res/b_mozilla/common/d3/examples/calendar/calendar.css"/>

Next we're going to take the calendar view code and copy the entire code block from the start of the width variable delcaration to the end of the call to the selectAll function. Your code should look similar to:

<script type="text/javascript" src="/analytics/res/b_mozilla/common/d3/d3.js"></script>

    <link type="text/css" rel="stylesheet" href="/analytics/res/b_mozilla/common/d3/lib/colorbrewer/colorbrewer.css"/>

    <link type="text/css" rel="stylesheet" href="/analytics/res/b_mozilla/common/d3/examples/calendar/calendar.css"/>

    <div id="my_chart"></div>

    <script type="text/javascript">

var margin = {top: 19, right: 20, bottom: 20, left: 19},

    width = 720- margin.right - margin.left, // width

    height = 136 - margin.top - margin.bottom, // height

    cellSize = 12; // cell size

var day = d3.time.format("%w"),

    week = d3.time.format("%U"),

    percent = d3.format(".1%"),

    format = d3.time.format("%Y-%m-%d");

var color = d3.scale.quantize()

    .domain([5,30])

    .range(d3.range(9));

var svg = d3.select("#my_chart").selectAll("svg")

    .data(d3.range(year_range1, year_range2))

  .enter().append("svg")

    .attr("width", width + margin.right + margin.left)

    .attr("height", height + margin.top + margin.bottom)

    .attr("class", "RdYlGn")

  .append("g")

    .attr("transform", "translate(" + (margin.left + (width - cellSize * 53) / 2) + "," + (margin.top + (height - cellSize * 7) / 2) + ")");

svg.append("text")

    .attr("transform", "translate(-6," + cellSize * 3.5 + ")rotate(-90)")

    .attr("text-anchor", "middle")

    .text(String);

var rect = svg.selectAll("rect.day")

    .data(function(d) { return d3.time.days(new Date(d, 0, 1), new Date(d + 1, 0, 1)); })

  .enter().append("rect")

    .attr("class", "day")

    .attr("width", cellSize)

    .attr("height", cellSize)

    .attr("x", function(d) { return week(d) * cellSize; })

    .attr("y", function(d) { return day(d) * cellSize; })

    .datum(format);

rect.append("title")

    .text(function(d) { return d; });

svg.selectAll("path.month")

    .data(function(d) { return d3.time.months(new Date(d, 0, 1), new Date(d + 1, 0, 1)); })

  .enter().append("path")

    .attr("class", "month")

    .attr("d", monthPath);

    var csv =[];

Notes About this Code

Although this code does most of the heavily lifting and can be left unmodified, there are specific lines that can be changed and updated dynamically via the use of presentation variables.

Color Thresholds:

The color variable specifies the thresholds for red/yellow/green. In this case I deem the min and max ranges of an airline delay to be between 5 minutes and 30 minutes:

var color = d3.scale.quantize()

    .domain([5,30])

Chart Size Adjustment:

By modifying the code for the margin variable:

var margin = {top: 19, right: 20, bottom: 20, left: 19},

    width = 720- margin.right - margin.left, // width

    height = 136 - margin.top - margin.bottom, // height

    cellSize = 12; // cell size

  The height/width/cell size can be adjustable by changing the hardcoded values to presentation variables such as:

• @{Width}
• @{Height}
• @{CellSize}

Date Formatting:

The 'day' variable responsible for date formatting:

var day = d3.time.format("%w"),

    week = d3.time.format("%U"),

    percent = d3.format(".1%"),

    format = d3.time.format("%Y-%m-%d");

Requires that the format of the date be specified.  The Calendar View script by default uses a 'YYYY-MM-DD' format. If your OBIEE data is a MM-YY-DD format or has a timestamp, you will need to modify the column data format to the following:

Modifying the Date Range:

The Calendar View code by default hard codes a date range of 1990 to 2011. You will most likely need to modify these values for your data set create a presentation variable that allows the users to change the date range dynamically:

var svg = d3.select("body").selectAll("svg")
    .data(d3.range(1990, 2011))

Could be modified to:

var svg = d3.select("#my_chart").selectAll("svg")
    .data(d3.range(year_range1, year_range2))

In the upcoming steps I will show how these variables can be called.

 Step 4: Populate the Narrative and Post-Fix

In the narrative you will need to specify the Date and Metric you want to pass to the javascript function using the corresponding column number (see step 0 if you forgot!)

The Post-Fix should contain the remainder of the Calendar View code. This can remain unmodified:

var data = d3.nest()
    .key(function(d) { return d.Date; })
    .rollup(function(d) { return d[0].Metric; })
    .map(csv);
  rect.filter(function(d) { return d in data; })
      .attr("class", function(d) { return "day q" + color(data[d]) + "-9"; })
    .select("title")
      .text(function(d) { return d + ": " + (data[d]); });
function monthPath(t0) {
  var t1 = new Date(t0.getFullYear(), t0.getMonth() + 1, 0),
      d0 = +day(t0), w0 = +week(t0),
      d1 = +day(t1), w1 = +week(t1);
  return "M" + (w0 + 1) * cellSize + "," + d0 * cellSize
      + "H" + w0 * cellSize + "V" + 7 * cellSize
      + "H" + w1 * cellSize + "V" + (d1 + 1) * cellSize
      + "H" + (w1 + 1) * cellSize + "V" + 0
      + "H" + (w0 + 1) * cellSize + "Z";
}
</script>

Your narrative should be similar to:

Step 5: Create a Second Narrative for the Date Range

This narrative is optional, but assuming you want to give the user the ability to modify the date range, you would take the variables you referenced in the 'Modifying the Date Range' section (in my case year_range1 and year_range2)  and set both of them equal to two presentation variables like below:

Step 6: View Narratives in Answers

Adding both narratives to a single view, your end result should look similar to:

This guide barely scratches the surface of D3-OBIEE integration but serves as a great example of how 3rd party APIs and javascript libraries can be integrated into OBIEE 11g. I encourage all BI Architects to look through the entire D3 library and see how D3 can be integrated into their current engagement.

 

keywords: OBIEE 11g, Data-Driven Documents, OBIEE 11.1.1.7.0, UserScripts.js, Answers, javascript

FYI: Error while running Upgrade Assistant OBIEE 11g - UPGAST-00055

When migrating our legacy OBIEE 10g webcat and RPD to Oracle's new 11g platform, we often don't have the privilege of using a GUI based operating system for assembly & system test as most production systems are Solaris or Red Hat.

We can all agree that a unix distribution is more powerful & flexible, yet leaves much to be desired when attempting to resolve configuration issue in a 'friendly UI environment'. This is certainly no exception when attempting to use Oracle's Upgrade Assistant Command Line Interface .

Many users have encountered error " UPGAST-00055: error reading the Universal Installer Inventory. The inventory pointer oraInst.loc is either not readable or doest not exist". A quick search of Oracle's Fusion Middleware Error Message Reference Guide makes me believe that Oracle's writers are playing a cruel joke in their attempt to 'help':

Action: "see the secondary error message?" You've got to be kidding me!!

Oracle's 'help' aside, the upgrade assistant is looking for an oraInst.loc file which contains 2 things:

1. The location of your installation directory
2. The install group of your user

But the upgrade assistant cant find your oraInst.loc file, why?
UA, by default, searches for the oraInst.loc file in your /var/opt/oracle folder. But many times, the file won't be located there - for a number of reasons , including:

• custom installation directories
• co-installation of multiple oracle products
• de-installation and subsequent re-installation attempts

But you're not out of luck. Most likely, your oraInst.loc file will be in your $MW_HOME/oracle_common folder:

You can then use the UA CLI paramater -invPtrLoc to specify your custom oraInst.loc path.:

./ua MT -BIEEE -webcatdir /export/obiee/11g/Oracle_BI1/bin/webcatalog -invPtrLoc /export/obiee/11g/oracle_common/oraInst.loc -webCatDeliversDir /delivers -wlsPort 7001 -wlsUser weblogic
will generate the following:

followed by a series of postupgrade tasks that you can view at 'tail -f $ORACLE_HOME/upgrade/logs/postupgrade.txt'
and ultimately a 'completed successfully' message:

But what if you can't find your oraInst.loc file? Don't worry! Oracle has a template you can use located at:

• /$ORACLE_HOME//bifoundation/provision/scripts/bidomain/inst

1. Rename it to oraInst.loc
2. Copy it to your $ORACLE_HOME folder
3. Populate the inventory_loc line with the path to your oraInventory folder
4. Populate the inst_group with the group name of the account you're using to install & upgrade OBIEE
5. Run the upgrade assistant as specified above

Problem solved!

keywords : obiee 11g upgrade, upgrade assistant,  obiee administration, obiee 11g administration, weblogic obiee

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

FYI: ORACLE_HOME Environment Variable and OBIEE 11g RPD error ORA-12154

Consider the scenario where you've been asked to install a fresh 11g stack on a server. A normal OBIEE 11g stack includes:

• 11g database
• Repository via RCU
• 11g weblogic domain
• Admin Server
• Managed Server scaled out n times for each ManagedServer instance
• OBIEE 11g (managed within the weblogic domain)
• 11g presentation server
• 11g client tools

Due to financial constraints, you've been asked to install the 11g database, 11g weblogic domain and 11g presentation server on a single server. This in its self is not an issue, and is common practice, especially for proof of concepts.

In a standard 11g database install, you'll set your $ORACLE_HOME variable to ..\.\

product\11.2.0\dbhome_1\

This is where you'll store your TNSNames.ora and SQLNet.ora files. These files are critical because they determine the database addresses needed for establishing a collection. Your default tnsnames.ora file will most likely include your localhost.  See below for an example:

Now you've installed the database, created the RCU and you're ready to install & configure Fusion Middleware (formally 10g's Enterprise Manager).

But wait! Fusion Middleware's Configuration Guide says create an ORACLE_HOME environment variable and set it to OBIEE 11g's database path:  app\11g\mw_home

See table below:

 

OBIEE 11g ORACLE_HOME	app\11g\MIDDLEWARE_HOME
Oracle 11g database ORACLE_HOME	app\CookJR\product\11.2.0\dbhome_1

What do you do?

Keep your ORACLE_HOME environment variable set to your DB path and make sure any datasources you add as a physical layer of the RPD are added to the ORACLE_HOME db path and not the 11g ORACLE_HOME

This is critical because during the configuration of your OBIEE system within 11g you'll be required to set the ORACLE_HOME path twice:

1. When you configure your Oracle Identity Store to utilize an external database
2. When you update your tnsnames.ora file to contain the physical data sources of your connection pool within the RPD

If you fail to adhere to this policy, you will encounter the following error when you create a connection pool in your physical layer:

ORA-12154: TNS:could not resolve the connect identifier specified at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000)

This is actually a known Oracle Bug (Search OTN ID 129637.01) and if you encounter this error , relax the fix is quick:

STEP 1: Edit registry under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\<Oracle_Home> to make sure NLS_LANG key is set to a valid characterset for the Oracle Client used as the DSN.

STEP 2: Edit the file %MiddleWare_Home%\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\bi-init.cmd to set %ORACLE_HOME%\bin as first entry in the PATH.
e.g.

set PATH=%OBIEE_HOME%\bin;%OBIEE_HOME%\bifoundation\server\bin;%OBIEE_HOME%\bifoundation\web\bin;C:\Oracle\BIEE_11g\jre\bin;%windir%;%windir%\system32;%PATH%

STEP 3 Please edit the file %MiddleWare_Home%\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\user.cmd to set TNS_ADMIN to your Oracle client tnsnames.ora home directory path (in case exists) or to your OBIEE tnsadmin %OBIEE_Client_Home%\Oracle_BI1\network\admin.
e.g.

set TNS_ADMIN=C:\oracle\product\11.2.0\client_1\network\admin

Oracle Technical Network says you can't test this in online mode, but it is possible by creating a new connection pool and then attempting to 'Import Metadata'. No reboot is required. 

If you pass Step 1:

you have successfully connected to your physical data source using the tnsnames.ora file located in your

app\CookJR\product\11.2.0\dbhome_1 folder. Congrats!

keywords : obiee 11g upgrade, obiee ORACLE_HOME variable, obiee 11g guide, obiee 11g installation, ORA-12154

How-to: Drill through & Guided Navigation 101 w/ OBIEE 10g Answers

OBIEE offers 2 types of drilling for slicing & dicing:

1) Drill down- Using a hierarchy to navigate to a lower level of granularity for a specific report or data set

2) Drill through/drill across  - Using Answers to navigate from report 1 to report 2 in order to view additional fields while saving the filters used in report 1.

Consider the requirement of navigating from a summary report which displays Revenue by Quarter to a detailed report which breaks down revenue by month and includes additional metrics such as Booked Amount and Unit Price.

If the requirement only needed revenue by Month, then we could configure a hierarchy to drill from Quarter to Month. But since the user needs to see additional metrics in the detailed report, we must utilize the drill through feature to accommodate this requirement.

Step 1:  Create Summary Report with specified prompts and metrics.

In the above example I used SampleSales OOTB RPD to create a report that has slices Revenue by Quarter and created a prompt to filter the report by Area.

Make sure you specify M02 Area has prompted in your summary report:

 Step 2: Create Detailed report 1 with dimensions from report 1 set to filters as 'Prompted'

If you want both 'T03 Per Name Qtr' and 'M04 Region' values to be applied to your detail report, then you must set 'T03 Per Name Qtr' and 'M04 Region' to 'is Prompted' in your detail report.

Step 3: In Summary Report 1, enable Guided Navigation to to Detailed Report 1

For your KPI (in this example 0-01 Revenue (Sum All)) , click Column Properties - > Column Format Tab ->  Value Interaction Drop Down Menu - > Select Navigation then find your detailed report 1

Step 4: Test by Navigating from Summary Report 1 to Detail Report 1

I recommend adding the 'Filter' has a section to your detailed report to confirm that the values are getting passed. 

Report 1:   Filters ; M02 Area = 'Area 0', T03 Per Name Qtr = '2007 Q1', M04 Region = 'South' for first row where 1-01 Revenue (Sum All) = 27,391

Report 2:

keywords: obiee answers, drill down, drill through, drill down report, obiee guided naviation

How to: Complete OBIEE 10g installation Part 2 and 3 - Oracle Analytics Server & Presentation Services

In our last how-to post we covered the first step of installing OBIEE 10g on linux , Configuration of JDK for Oracle Business Intelligence

This post will focus on steps 2 and 3 below:


1) Configuration of JDK for Oracle Business Intelligence  - COMPLETE
2) Set up of the Oracle Business Intelligence Server
3) Set up of the Oracle Business Intelligence Presentation Services
4) Set up of the Oracle Business Intelligence Client Tool set

In Step 1 we:

1) configured JDK for our redhat linux box
2) created applicable usernames and groups
3) granted directory permissions to specific folders
4) created environmental variables needed for OBIEE 10g installation.


Do not continue unless you have completed the above steps, as they are pre-requisites for Steps 2 and 3.


Step 1 : Modification of bash shell script to include Oracle Environmental Variables


The OBIEE 10g installation is going to look for the following variables in your bash profile:

1) $JAVA_HOME
2) $PATH  - modified to include your OBI Set up folder

We created the $JAVA_HOME variable in step 1, so let's go ahead and modify our bash shell script to include the OBI Set up folder for the $PATH variable

For user account OBI, run the following command in terminal:

vi ~/.bash_profile

modify the bash shell start up script to include 

# OBI Setup Script
PATH=$PATH:/usr/local/OracleBI/setup
export PATH

Also, set the file descriptor limit to 10240 

# File Descriptor Limit
ulimit -n 10240 

Here is a VI review guide incase your VI skills are a little rusty: http://www.washington.edu/computing/unix/vi.html 


Step 2: Create installation folders & grant required access

Oracle Analytics Server uses /usr/local/setup/OracleBI and /usr/local/setup/OracleBIData for installation and execution. We need to create those folders and grant our user obi read write access:

Log into your root/super user account or have the sys admin execute the following commands:
 

mkdir -p /usr/local/OracleBIchown -R obi:oinstall /usr/local/OracleBIchmod -R 775 /usr/local/OracleBImkdir -p /usr/local/OracleBIDatachown -R obi:oinstall /usr/local/OracleBIDatachmod -R 775 /usr/local/OracleBIData

Step 3: Confirm /dev/random and /dev/urandom are available

Oracle BI  Presentation Services requires pseudo random number generation devices. Confirm your system has urandom and random installed:

In terminal, run the following command:

[cookjohn@local]/usr/local/OracleBI/setup% cd /dev
[cookjohn@local]/dev% ls *random

You should see the following output:

random  urandom

Step 4: Download Oracle BI Suite EE 10.1.3

Navigate to http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/business-intelligence-10g-165415.html 

and download x86 (Red Hat Linux / Oracle Enterprise Linux)

Then extract the file to your /tmp folder. You should have 2 folders:

drwxrwxr-x  3    59934 oinstall       4096 May 24 10:40 Server
drwxrwxr-x  5    59934 oinstall       4096 May 24 10:41 Server_Ancillary


Using your root/super user account, execute (or have the sys admin) the following commands:

chown -R obi:oinstall /tmp/Server
chmod -R 775 /tmp/Server
chown -R obi:oinstall /tmp/Server_Ancillary
chmod -R 775 /tmp/Server_Ancillary

Step 5: Run final compatibility check with UnixChk.sh

Oracle created a script which will determine if you've successfully completed all of the pre-work needed for installation.
In terminal, run the following command:

./UnixChk.sh -b /usr/local/OracleBI 

You should get the following output: 

SUCCESS!! - This machine is configured for Oracle BI EE 10.1.3.3.2 

Step 6: Proceed with installation

Congratulations, you've completed all of the required pre-work needed to install OBIEE! Let's get started! Navigate to your /usr/local/OracleBI/setup folder and run the following command:

./setup.sh -console

You will be asked to identify your java directory, installation type and installation folder.

Here are the details:

• Installation Location: /usr/local/OracleBI
• Data Location: /usr/local/OracleBIData
• Installation Type: Basic
• JDK Location: /usr/java/jdk1.5.0_22
  
  
   

 FYI: Basic vs Advanced Installation Primer:

Basic installation uses the J2EE Application Server rather than the Oracle Application Server. If you want to utilize single sign on, select Advanced.

In terminal, your summary should be as follows:

Oracle Business Intelligence 10.1.3.4.1 will be installed in the following
location:

/usr/local/OracleBI

with the following features:

Oracle Business Intelligence JDBC Driver
Oracle Business Intelligence Systems Management
Oracle Business Intelligence Server
Oracle Business Intelligence Cluster Controller
Oracle Business Intelligence Scheduler
Oracle Business Intelligence Client
Oracle Business Intelligence Presentation Services
Oracle Business Intelligence Presentation Services Plug-in and BI Office
Oracle Business Intelligence Publisher

for a total size:

 2626.9 MB

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 


|-----------|-----------|-----------|------------|
0%         25%         50%         75%        100%
||||||||||||||||||||||||||||||||||||||||||||||||||||

Installing Oracle Business Intelligence 10.1.3.4.1. Please wait...
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

Creating uninstaller...

Please wait...

-------------------------------------------------------------------------------
The InstallShield Wizard has successfully installed Oracle Business
Intelligence 10.1.3.4.1.

Step 7: Start Services

Now that you've installed OBIEE Server & Presentation services, you need to activate it by running the following scripts from your /usr/local/OracleBI/setup folder:

./oc4j -start # starts Oracle Application Server
./run-sa.sh start # starts BI server
./run-saw.sh start # starts presentation server
./run-ccs.sh start  # Oracle BI Cluster Controller

To shutdown the server, you need to run the commands in the opposite order:

./run-ccs.sh stop
./run-saw.sh stop
./run-sa.sh stop
./oc4j -shutdown -port 23791 -password oc4jadmin 

Step 8: Log into Answers

Navigate to your web server's root directory and append :9704/analytics/saw.dll?Dashboard to it. For example, if your web server is http://www.example.com , your url would be : http://www.example.com:9704/analytics/saw.dll?Dashboard

Default user/pass is : Administrator/Administrator

You can access Oracle Enterprise Manager 10g by appending :9704/em/ to your web server's root directory

In Summary, we :

1) Configured JDK for OBIEE

2) Created the appropriate installation folders

3) Created required environmental variables
4) Confirmed the system had appropriate random number generation scripts

5) Modified the file descriptor limit

6) Validated our configuration using Oracle's UnixChk script

7) Installed OBIEE 10G Server & Presentation Services using the basic installation method (Java instead of Oracle's Application Server)

8) Determined the process for starting and stopped services
9) Validated installation by navigation to the default Answers & Oracle Enterprise Manager 10g URLs.

Excellent job if you've made it this far. If you've been paying attention, you'll notice that only server side components have been installed. We still need to install the Client tools which allow us to modify the repository and remotely connect to the linux box. Stay tuned!

keywords : obiee 10g installation, obiee 10g linux, obiee 10g server installation process, obiee 10g configuration, obiee install linux