Thursday, 23 February 2012

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

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

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.

Sunday, 19 February 2012

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


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.
 

Thursday, 16 February 2012

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.

Sunday, 12 February 2012

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.