Oracle BI Foundation Suite 11.1.1.6 is now GA

Today Oracle announced the availability of Oracle Business Intelligence Foundation Suite 11.1.1.6.0 on Oracle Software Delivery Cloud ( eDelivery) and OTN.

The purpose of this blog is to provide the extracts from the announcement and also some helpful links to start with.

OTN Download Location

http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-165436.html

OBIEE 11.1.1.6.0 (Client Tools)

Microsoft Windows x86 32 bit /64 bit  

OBIEE 11.1.1.6.0 Available Binaries for Operating Systems (Server Tools)

Binaries are available for Windows – 32 bit /64 bit, Linux x86 32 bit / 64 bit, Sun Solaris 64 bit SPARC and X86 and AIX – 64 bit.

RCU – Repository Creation Utility – (Windows 32 bit / Linux 32 bit)

Documentation URL Location

http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/documentation/index.html

Certification Matrix now published for OBIEE 11.1.1.6.0

The certification matrix for all oracle fusion middleware anlong with OBIEE 11.1.1.6.0

http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html

High Level Features

·         80 + New Features and Enhancements with BI Foundation Suite 11.1.1.6

·         OBI and Essbase optimized for in-memory analytics with Exalytics.

·         Speed-of-thought interactive analysis, “go-less” prompts, and contextual actions

·         Increased Essbase parallelism for calculation, load, restructure and export

·         New visualizations, including the innovative Contribution Wheel

·         Scorecard mobile and interactivity improvements

·         BI Publisher end user, developer, and administrator enhancements

·         New browser, data source and platform certifications

Oracle Exalytics

With this release of OBIEE 11.1.1.6.0, all components of Oracle Exalytics are now available.

·    OBI Foundation Suite 11.1.1.6 is the required version for Exalytics  It includes Essbase 11.1.2.2.000 and Oracle BI 11.1.1.6

·    Oracle Exalytics In-Memory Machine X2-4 and Oracle Times Ten for Exalytics 11.1.2.2.2 are already GA

The Exalytics – an engineered system for in-memory decision support, online analytical processing, forecasting and planning opens number of possibilities to achieve ultimate BI solution.  I am planning to cover features/ new enhancements in details in future blogs entries going forward.

Installing Oracle Essbase Release 11.1.2.1.0 on Windows

In my previous blog entries I have written about the Times Ten in-memory database (IMDB) 11.2.2 and Oracle In-Memory Database Cache 11.2.2 and its integration with OBIEE.

Oracle Exalytics is a new beast in the market, which provides extreme performance of Business Intelligence and Enterprise Management applications. Oracle Exalytics runs the Oracle Business Intelligence Foundation along with Oracle Times Ten In-Memory Database for Exalytics. Both BI Foundation and Times Ten In-Memory Database for Exalytics have been specifically enhanced to work together and have been optimized to provide exclusive features on Oracle Exalytics hardware.

The Oracle Essbase – multidimensional OLAP Server is also one of the important constitute of Oracle’s journey of Exalytics. For Exalytics, Oracle Essbase has a number of optimizations for in-memory operation including improvements to overall storage layer performance, enhancements to parallel operations, enhanced MDX syntax/ MDX query engine.

This blog is attempt to document step by step installation of Essbase with enough details. I would like to explore the new exciting world of Exalytics in near future through successive blogs entries.

Steps to install the Oracle Essbase Release 11.1.2.1.0 are as follows

1.       Download the software from OTN

http://www.oracle.com/technetwork/middleware/essbase/downloads/index.html

For this blog entry I have downloaded and installing the 64 bit version on my laptop.

Essbase download includes

·         Oracle Essbase Server and Clients

·         Oracle Essbase Administration Services and Client

·         Oracle Provider Services

·         Oracle Essbase Integration Services and Client

·         Oracle Essbase Studio and Client

·         Oracle Essbase Spreadsheet Add-in

After downloading all required download files, it should have following files.

2.   Extract contents from the downloaded zip files

Extract all zip file in one folder D:/Install, it should have following folder structure. The ‘assemblies’ subdirectories contain the actual installation components.

3.       Preparation of Database

Database using a supported RDBMS (Oracle Database, Microsoft SQL Server, or IBM DB2) is must. EPM System supports 32-bit as well as 64-bit versions of all supported databases; however, the version of the database should match the operating system e.g. 64 bit operating system with 64 bit database version.

The database must be created using Unicode Transformation Format UTF-8 encoding (character set).  

Oracle SQL to check if the database parameter is UTF-8 or not is as below

select * from nls_database_parameters where parameter='NLS_CHARACTERSET'

Oracle database user ID should have following roles and privileges

CREATE SESSION

CREATE VIEW

RESOURCE

4.       Double click installTool.cmd in the root directory /install ( EPM System Installer Files Folder)

 Note: - On Windows machines, do not use the Administrator user to install and configure

Error – EPMINS-01001 error while installing Hyperion 11.1.2.1.0

Resolution – Control Panel -> administration tools ->system configuration

Click on the Tools tab, Select ‘Change UAC settings’

Click launch

Slide the bar down to ‘’Never notify’’

Click ok

5.       Follow the instructions on the installer

Select a language

Welcome and Prerequisite Checks

Choose Destination

Installation Type

 

Product Selection  - Foundation Services and Essbase , uncheck Performance Management Architect and all web applications

           Confirmation - Installation

Progress 

Installation Complete

Configure – EPM oracle instance

Configure - Shared Services Database Configuration

Configure Common Settings - windows services, SSL and SMTP Email Server settings

Configure Database

Deploy to Application Server – Specify Web Logic Domain Information

Oracle Web Logic - Application to deploy

Essbase Studio – Server data file location

Configure Essbase Server

Setup Oracle BIEE and Publisher Logical Web Address - This is an optional step

Configure Web Server

Shared Service Password - This password would be used to login to Essbase Admin Server

Confirmation - configuration

Progress

Summary - configuration complete

6.       Verify  Installation

Make sure that all Hyperion services are up and running

To start Essbase Server – execute following batch file via command line or start up menu

                                                                                                                            

$EPM_HOME\user_projects\epmsystem1/bin/start.bat Essbase. Properties

Select Start, then Programs, the Oracle EPM System, then Foundation Services, the epmsystem1, and then EPM System Diagnostic

The Oracle EPM System diagnostic report is displaced as follows. In the first column, and PASSED or FAILED is displayed for each service. If any service displays a FAILED status, close the report and start the diagnostic again.

 

Upgrading OBIEE 10g to OBIEE 11g for Financial Services Profitability Analytics V5.1 – A Practical Example

Problem Description

The Oracle Financial Services Profitability Analytics v5.1 is BI Apps for Oracle Financial Services Analytical Applications Infrastructure v7.1/7.2, particularly for Profitability Analytics.  The current BI Apps solution is available with BI Foundation 10g.

This blog is an attempt to provide you a practical example of upgrading the OBIEE 10g Repository and Web Catalog to OBIEE 11g in general and Financial Services Profitability Analytics V5.1 in particular.

Pre-requisites

RCU – Created the Destination BIPLATFORM Schemas and OBIEE 11g installed and configured

Upgrade Assistant must be able to access following directories

A.      Directories that contain the 10g Oracle BI Repository Files

B.       The 10g Catalog and Catalog Deliveries directories

Make sure the target database entry is in tnsmanes.ora or odbc connection for relational/dsn based solution. As profitability analytics is essbase based reporting solution make sure that essbase server must be accessble.

The OBIEE 11g Administration Server and the Managed Server must remain running during the upgrade process.

Oracle Fusion Middleware Upgrade Assistant

The Oracle Fusion Middleware Upgrade Assistant is used for upgrading the repository and web catalog.

Start Upgrade Assistant

Change the directory to the ORACLE_HOME/bin directory (e.g. D:\Middleware\Oracle_BI1) of the Oracle Fusion Middleware installation.

On Windows operating system execute/click - ua.bat for UNIX execute. /ua

Welcome Screen

Specify Operation

Select operation - Upgrade Oracle BI RPD and Catalog

Specify Source Details

This is regarding Oracle 10g BI Repository and Catalog Details from existing 10g Installation/copy.

For Repository – Location/User Name/Password and password which will be used to secure upgraded 11g repository.

Note – For Multiple BI repositories run upgrade assistant once for each repository.

For Catalog – Catalog Directory / Deliveries Directory – by default it is located in the OracleBIData/web/catalog directory, if it does not exists any empty directory named deliveries can be given for upgrade.  I have created a empty directory and specified its path.

Specify Web logic Server Details

Examine - The components provided

Upgrade Summary- The screen shows the upgrade summary such as repository source, catalog directory etc.

Upgrading Components

Upgrading Success

Verification of Oracle BI EE Upgrade

1. Login http://localhost:7001/em  (Fusion Middleware Control) and verify that OBIEE Components are up and running.

2. Run the Upgrade Assistant again and select Verify Instance , put in details of weblogic server, and click on verify option.

The verfication screen - success

Error -

During upgrade I have encountered following error.

Log File – Checked log file at D:\Middleware\Oracle_BI1\upgrade\logs\us2012-02-15-12-06-10PM.log

[2012-02-15T15:10:21.119+00:00] [BIEE] [NOTIFICATION] [] [upgrade.BIEE] [tid: 14] [ecid: 0000JM1ZOpO8how70FyWMF1FEtyn000005,0] Upgrade catalog started

[2012-02-15T15:10:25.204+00:00] [BIEE] [ERROR] [] [upgrade.BIEE] [tid: 14] [ecid: 0000JM1ZOpO8how70FyWMF1FEtyn000005,0] Item (/shared/_ibots) already exists in catalog.

[2012-02-15T15:10:25.209+00:00] [BIEE] [ERROR] [] [upgrade.BIEE] [tid: 14] [ecid: 0000JM1ZOpO8how70FyWMF1FEtyn000005,0] Upgrading catalog failed.[[

Item (/shared/_ibots) already exists in catalog.

]]

[2012-02-15T15:10:29.686+00:00] [Framework] [ERROR] [UPGAST-00138] [upgrade.Framework] [tid: 14] [ecid: 0000JM1ZOpO8how70FyWMF1FEtyn000005,0] upgrade exception occurred

[2012-02-15T15:10:29.686+00:00] [Framework] [ERROR] [] [upgrade.Framework] [tid: 14] [ecid: 0000JM1ZOpO8how70FyWMF1FEtyn000005,0] Cause: An unexpected upgrade exception has occurred. Action: See the secondary error message for additional details.

Cause of Error

In the catalog in _ibot folder there were 3 common empty folders

Resolution

Reviewed the web catalog, specifically the _iBot folder and remove three empty folders, rerun upgrade.

Data Densification in an OBIEE 10/11g

In last blog I have tried to explain various modeling options by which we can implement the Outer Join can be implemented for OBIEE 11G. The Dashboard option is only applicable to single dimension while the option of adding measure to preserve dimension values, is suitable for one or more dimension.

The steps involved were

·   Add separate logical table source for each preservation measure to trigger fact-based partitioning outer joins; each LTS maps just that preservation column

·    Set Level-based at the All level except for dimensions to be preserved

·    Apply the hidden column format as the system-wide default for these preservation measures

This modeling method of preserving each dimension becomes more complicated if number of preservation columns is high and it is also cumbersome to generate reports by using individual measures for each preserve column ( year) or combination of preserve columns ( e.g. year- office).

Is there any way by which we can achieve Data Densification for OBIEE for all dimensions and dimension members? The solution/model should also offer an ease in generating report with or without data densification within the same subject area.

This is an attempt to discuss the data densification in oracle database in general and OBIEE 10/11g in particular.

Data in a relational data warehouse is usually sparse. Densities of 0.1-2% are very common. No data exists in the fact table if there is no value exists for a given combination of dimension values. However, the analyst may want to view the data in a dense form, which rows for all combination of dimension values displayed even when no fact data exists for them. For example, if a product did not sell during a particular time period, users may still want to see the product for that time period with zero sales value next to it.

Data densification is the process of converting spare data into dense form. For Oracle database the portioned join operator provides an easy, elegant and efficient option e.g.

SELECT V2.pid, V2.tid, SUM(SALES) OVER

(PARTITION BY V2.PID, V2.YEAR ORDER BY V2.TID) YTD sales

FROM SALES PARTITION BY(PID) V1 RIGHT OUTER JOIN

TIMES V2 ON (V1.TID=V2.TID)

Data Densification - OBIEE 11g

The approach for data densification which works across multiple dimensions for OBIEE 11g is as follows. For demonstration purpose, I have used following reporting schema.

Fact –Billed Revenue

Dimensions –Time Day Grain, Time Month Grain, Time Quarter Grain, Customers, Offices, Product Customer

The idea is to add a dummy fact table with single column, and then create joins between the dummy fact table and the all dimension tables using 1=1 as the join criteria. This should force the BI Server to do an outer stitch join between original query (from Billed Revenue) and the Cartesian query from dummy fact table.

Modeling Physical Layer

Create a new physical table in the physical layer called ‘DUMMY’, with a single column called ‘DUMMY’ with a SQL statement source ‘SELECT 1 AS DUMMY FROM DUAL’;

Create joins between ‘DUMMY’ table and the all the dimension tables using 1=1 as the join criteria. The Office Dimension is joined with the condition 1=1 with the fact table as shown below.

The physical diagram after setting up all joins between the dimensions and ‘DUMMY’ fact table should be as same as shown below.

Modeling Logical Layer

In the logical layer drag the dummy column from the dummy table as a new fact measure, called ‘Show All Rows’.

Modeling Presentation Layer

In the Presentation Layer drag the ‘Show All Rows’ column, it is just drag and drop.

Note – After completing the development, don’t forget to check consistency of the repository.

OBIEE Analysis (Answer/Report)

Login, load repository metadata and choose the subject area for new analyses. The first step is to apply hidden column format as the system-wide default for ‘Show All Rows’ column.

The reports with ‘Show All Rows’ column and standard report without it are generated as below.

The  Logical SQL generated by OBIEE Server for combined report is as below.

SET VARIABLE QUERY_SRC_CD='Report',PREFERRED_CURRENCY='USD';SELECT

   0 s_0,

   "A - Sample Sales"."Time"."T05 Per Name Year" s_1,

   "A - Sample Sales"."Base Facts"."1- Revenue" s_2,

   "A - Sample Sales"."Base Facts"."Show All Rows" s_3

FROM "A - Sample Sales"

ORDER BY 1, 2 ASC NULLS LAST

The physical SQL generated by OBIEE Server  - BI Server does an outer stitch join between original query (from Billed Revenue) and the Cartesian query from dummy fact table.

By adding/removing ‘Show All Rows’ column in report Analyst can easily switch report to and from standard report to all rows (outer join) report.  

The outer join for time and office dimension is illustrated below.