Sunil S. Ranka's Weblog

Superior Data Analytics is the antidote to Business Failure

Archive for the ‘11g’ Category

Permissions for both HDFS and local fileSystem paths

Posted by sranka on July 18, 2014

Hi All,

Permission issues is one of the key error , while setting up Hadoop Cluster, while debugging some error found below table on http://hadoop.apache.org/ . It’s a good scorecard to keep handy.

 

Permissions for both HDFS and local fileSystem paths

The following table lists various paths on HDFS and local filesystems (on all nodes) and recommended permissions:

Filesystem Path User:Group Permissions
local dfs.namenode.name.dir hdfs:hadoop drwx——
local dfs.datanode.data.dir hdfs:hadoop drwx——
local $HADOOP_LOG_DIR hdfs:hadoop drwxrwxr-x
local $YARN_LOG_DIR yarn:hadoop drwxrwxr-x
local yarn.nodemanager.local-dirs yarn:hadoop drwxr-xr-x
local yarn.nodemanager.log-dirs yarn:hadoop drwxr-xr-x
local container-executor root:hadoop –Sr-s—
local conf/container-executor.cfg root:hadoop r——–
hdfs / hdfs:hadoop drwxr-xr-x
hdfs /tmp hdfs:hadoop drwxrwxrwxt
hdfs /user hdfs:hadoop drwxr-xr-x
hdfs yarn.nodemanager.remote-app-log-dir yarn:hadoop drwxrwxrwxt
hdfs mapreduce.jobhistory.intermediate-done-dir mapred:hadoop drwxrwxrwxt
hdfs mapreduce.jobhistory.done-dir mapred:hadoop drwxr-x—

Hope this helps

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

This table was taken directly from http://hadoop.apache.org/docs/r2.3.0/hadoop-project-dist/hadoop-common/SecureMode.html

 

Posted in 11g, Big Data | Tagged: , , , , , , , | 1 Comment »

Oracle Apache Hadoop Hive ODBC Driver For OBIEE

Posted by sranka on July 3, 2013

Hi All,

Last few months have been a crazy ride, wrapping up on new opportunities and getting upto speed on Hadoop took all the free time away . During my read on Hadoop , I always wondered why we dont have out of the box ODBC driver? I tried using out of the box ODBC driver given by HortonWorks and it worked up to certain extend but running using cloudera was a challenge. Finally to the sigh, with 11.1.1.7 Oracle has introduced Oracle Apache Hadoop Hive ODBC Driver For OBIEE.

So far extracting the data using Hadoop have never been a challenge, but using the data to make sense out of it has been a constant challenge.  With this integration, there would be some amount of relief with the existing investment people have made in OBIEE. Hive’s SQL dialect, called HiveQL doenst support the full SQL-92 specification, please refer 11.1.1.7 Metadata Repository Builder’s Guide for not supported features.

For details on where to start please refer 11.1.1.7 Metadata Repository Builder’s Guide.  For importing the metadata from Hive you will have to download ODBC driver from oracle support web, for details please refer DocID 1520733.1 .

In coming weeks I will be working more on Big Data approach and Solutions. 

Hope this helps

Sunil S Ranka

“Superior BI is the antidote to Business Failure

 

 

Posted in 11g, Me, OBIEE | Tagged: , , , , , , , , , , | Leave a Comment »

Oracle Intelligence Virtual Summit 2012 – Aug 15-16

Posted by sranka on August 9, 2012

Dear All,

On behalf of BIWA board and volunteers, I would like to extend a personal invitation for — Oracle BIWA Virtual Summit 2012 — between Aug 15th and Aug 16th. This is the first ever Virtual Summit dedicated on Oracle’s BI technology. The summit runs over two days with dedicated topics on Warehousing and Advanced Analytics Focus on day 1 and Business Intelligence Focus on day 2. Usually these kind of Summit cost hunderds or couple of thousands of $. But BIWA presents this to you all for FREE $0. We have arranged 5 session on each day , with option of attending your choice of session based on your preference.

Key Note Speakers

For more details and Registrations please visit link below ::

http://www.ioug.org/Events/OracleBIWAVirtualSummit2012Aug1516/tabid/342/Default.aspx

Looking forward to see most of you all. In case of any logistical issues, please feel free to contact me at sunil_ranka@yahoo.com.

Hope this helps.

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

Posted in 11g, OBIEE | Tagged: , , , , , , , | Leave a Comment »

OBIEE – 11.1.1.6 At a Glance

Posted by sranka on February 23, 2012

Hi All

Finally cat is out of the bag, the much awaited and anticipated OBIEE 11.1.1.6 is out and available for GA.

The new release adds more enhancements, additional certifications, and bug fixes among other features. This release delivers a BI platform specially designed to leverage:

  • Oracle Exalytics hardware’s large memory, processors, concurrency, and other hardware features and system configurations
  • Enhancements to Times-Ten for Exalytics for analytical processing at in-memory speeds
  • Dynamic user interface enhancements that complement large amounts of data to present business information in meaningful and compelling ways
  • A new BI Server Summary Advisor for Exalytics for aggregate generation and persistence
  • Essbase memory usage optimizations and concurrency improvements for Exalytics to deliver efficient distribution of processing
  • BI Publisher performance, lifecycle, workflow and report creation enhancements
  • New and enhanced Scorecard views and BI Mobile improvements
  • Numerous Security, Management/Diagnostic and Lifecycle enhancements
  • Certified BI and EPM Applications on Exalytics

Hope This helps

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

Posted in 11g, sunil s ranka | Tagged: , , , , | Leave a Comment »

Fusion Applications – Overview of Business Intelligence and Reporting components

Posted by sranka on February 8, 2012

Hi All,

While looking through Oracle Support came across an informative “Overview of Business Intelligence and Reporting components”

 

Fusion Applications includes a variety of Business Intelligence and Report functionality. Some of these are exposed to the end-user, and some exist as middleware layers.

This document briefly lists the components, their common acronyms and the functionality that they bring to Fusion Applications.

Please note that the column titled “Function” contrass whether the component is a tool (infrastructure), or content (an analysis or report definition which is delivered with Fusion Apps).

BI Component Function Description Where exposed in the Applications UI (example)
Oracle BI Enterprise Edition (OBIEE) Middleware Tools and infrastructure Oracle BI Enterprise Edition (Oracle BI EE) is a comprehensive set of enterprise business intelligence tools and infrastructure that includes a scalable and efficient query and analysis server, an ad-hoc query and analysis tool, interactive dashboards infrastructure, proactive intelligence and alerts functionality, and an enterprise reporting engine. Underlies most BI analyses and reports. N/A
Analysis Editor Ad-hoc analysis designer The “Analysis Editor” (or Answers) lets you explore and interact with information by visually presenting data in tables, graphs, pivot tables, and so on. You can include the views that you create in an analysis, for display in Fusion Apps pages or dashboards. A sub-component of OBIEE. Via Reporting Pane or Navigator: Tools
Essbase Middleware Tools and infrastructure Oracle Essbase is an online analytical processing (OLAP) server that provides an environment for deploying prepackaged applications or developing custom analytic and enterprise performance management applications. Example usage:  underlies Financial Reporting. Financials > GL
BI Composer Ad-hoc analysis designer BI Composer is a simple-to-use wizard that allows you to quickly and easily create, edit, or view analyses without the complexities of the Analysis Editor. A sub-component of OBIEE. Via Reporting Pane or Navigator: Tools
Oracle Transactional Business Intelligence (OTBI) Analyses Oracle Transactional Business Intelligence is a complete, prebuilt BI solution that deliver intuitive, role-based intelligence throughout an organization. It uses Oracle BI Enterprise Edition as it’s technology layer to provide an easy-to-use interface for business users to perform current state analysis of their business applications.
Pre-constructed queries and reports are executed real time against the transactional schema supported by a layer of view objects.
An OBIEE repository (.rpd) and Web Catalog are supplied.
CRM Sales  Opportunity Watch Real Time
Oracle Business Intelligence Applications (OBIA) Analyses Oracle Business Intelligence Applications are complete, prebuilt BI solutions that deliver intuitive, role-based intelligence throughout an organization.
Oracle Business Intelligence Applications analyses use a data warehouse. Oracle Business Intelligence Applications supplies the warehouse database schema and the logic that extracts data from the Oracle Fusion Applications transactional database and loads it to the warehouse. Oracle Fusion Applications end users interact with the information in the Oracle Business Analytics Warehouse via analyses exposed on Fusion Applications pages, also using Oracle BI Enterprise Edition components (such as Analysis Editor, BI Composer and Dashboards).
Datawarehouse Console (DAC) is used to control and orchestrate extract-transform-load (ETL).
Informatica PowerCenter ETL tool is a third-party ETL platform (available from eDelivery) that performs the detailed extract, transform, and load (ETL) operations for the data warehouse.
An OBIEE repository (.rpd), Web Catalog, DAC repository and Informatica respository are supplied.
Financials > Customer Profitability
Financial Reporting Reports Oracle Hyperion Financial Reporting (Financial Reporting) is a powerful tool for designing and presenting analytic data graphically. You can design traditional financial report formats such as cash management reports, profit and loss statements, and balance sheets. You can also design nontraditional formats for financial or analytic data that include text and graphics.
Use Financial Reporting to create and deliver financial reports to the General Ledger system. Reporting administrators use EPM Workspace with financial reports to create and modify reports, books, and batch definitions and to schedule batch jobs via the reporting scheduler user interface within EPM Workspace. Reporting users run and view reports using the General Ledger user interface; they can launch reports in an IFrame within the General Ledger browser window.
Financials – Financial and Statutory Reporting
BI Publisher (BIP) Middleware Tools and infrastructure WYSWIG Tool for creating pixel precision reports.
Oracle Business Intelligence Publisher is an enterprise reporting tool for authoring, managing, and delivering reports from multiple data sources in multiple formats through multiple channels. A sub-component of OBIEE.
Via Reporting Pane or Navigator:  Tools
BIP Reports Reports Pre-constructed pixel perfect formatted reports Fusion Financials contains over 100 pre-constructed BIP reports, for example. 1099 report
Oracle Real-Time Decisions (RTD) Middleware Tools and infrastructure Oracle Real-Time Decisions enables you to develop adaptive enterprise software solutions. These adaptive solutions continuously learn from business process transactions while they execute and optimize each transaction, in real time, by way of rules and predictive models. A sub-component of OBIEE. N/A
RTD Reports/Content/CRM Sales Predictor Analyses CRM Sales Prospector
Oracle Data Miner (ODM) Middleware Tools and infrastructure Oracle Data Mining automatically finds relationships hidden in data, by expanding SQL  it adds analytical verbs–such as predict, cluster, detect, and classify–also by embedding cutting-edge algorithms in the Oracle Database kernel. Underlies CRM Sales Predictor. N/A
ADF based Reporting Reports Reports and Graphs which use ADF objects to create the UI look and feel , where the data has been sourced from Oracle Business Intelligence. These use an ADF component called BI Logical SQL VO, which in essence is a special type of VO that executes a logical SQL statement against the BI server. These VO’s are referenced in the .rpd ( BI’s common semantic layer)

Note that there are some ADF based graphs and reports which do not use BI as a source for data.

 

 

 

 

 

 

  The above entire text has been taken from oracle support document [ID 1334765.1]

Hope this helps

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

Posted in 11g | Tagged: , , , , , , , , , , | Leave a Comment »

Default OBIEE 11G Ports

Posted by sranka on December 28, 2011

Hi All

Recently came across a situation, in which someone  Due to some security issue someone wanted to bypass OBIEE’s default ports. After looking at multiple places, I could put all information together. I thought it would be a great share.

Even out of the box installation lets you change the configuration, during installation.

Once we selected one installation type from either Enterprise Install or Software only install and click next. Then in the next step it will ask for port configuration. Check Specify Ports using Configuration file and browse Just specify the newly assign port configuration file (staticports.ini , see the format below).

Default Port Numbers :

WEBLOGIC

  • Domain Port No = 7001
  • Oracle WLS BIEE Managed Server Port No = 9704
  • Oracle WLS BIEE Managed Server SSL Port No = 9804

OPMN

  • Oracle Process Manager Local Port No = 6100
  • Oracle Process Manager Remote Port No = 6200
  • Oracle Process Manager Request Port No = 6003

BIFOUNDATION

  • Oracle BI Server Port No = 9703
  • Oracle BI Server Monitor Port No = 9701
  • Oracle BI Presentation Services Port No = 9710
  • Oracle BI Scheduler Port No = 9705
  • Oracle BI Scheduler Monitor Port No = 9708
  • Oracle BI Scheduler Script RPC Port No = 9707
  • Oracle BI ClusterController Port No = 9706
  • Oracle BI ClusterController Monitor Port No = 9700
  • Oracle BI JavaHost Port No = 9810

Format of staticports.ini File


[WEBLOGIC]

#The Domain port no. This is the listen port of Weblogic Adminserver for the domain.
Domain Port No = 7001

#The "content" port for the BIEE apps. This is the Weblogic Managed Server port on which BIEE applications are deployed.
Oracle WLS BIEE Managed Server Port No = 9704

#The SSL port for the Weblogic Managed Server
Oracle WLS BIEE Managed Server SSL Port No = 9804

[OPMN]

#Process Manager Local port no
Oracle Process Manager Local Port No = 6700

#Process Manager Remote port no
Oracle Process Manager Remote Port No = 6701

#Process Manager Request port no
Oracle Process Manager Request Port No = 6702

[BIFOUNDATION]

#The listen port for OracleBIServer component
Oracle BI Server Port No = 9703

#The monitor port for OracleBIServer component
Oracle BI Server Monitor Port No = 9701

#The listen port for OracleBIPresentationServices component
Oracle BI Presentation Services Port No = 9710

#The listen port for OracleBIScheduler component
Oracle BI Scheduler Port No = 9705

#The monitor port for OracleBIScheduler component
Oracle BI Scheduler Monitor Port No = 9708

#The script RPC port for OracleBIScheduler component
Oracle BI Scheduler Script RPC Port No = 9707

#The listen port for OracleBIClusterController component
Oracle BI ClusterController Port No = 9706

#The monitor port for OracleBIClusterController component
Oracle BI ClusterController Monitor Port No = 9700

#The listen port for OracleBIJavaHost component
Oracle BI JavaHost Port No = 9810

Hope This helps.

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

Posted in 11g, OBIEE, sunil s ranka | Tagged: , , , , , | 2 Comments »

Analytics Cache Purge ODBC Extension Functions

Posted by sranka on November 4, 2011

Siebel Analytics version 7.7 provided three ODBC extension functions for purging query cache entries: one to purge by query, one to purge by physical table, and one to purge all cache entries in the entire query cache. The subsections below describe each of the three functions.

Even though its not document as part of 11g, but we have implemented at one of the client and it worked as is (Please use at your own risk)

Purge by query

The table below formally defines the ODBC extension function for purging a cache entry associated with a specified query:

Usage: Call SAPurgeCacheByQuery (?)

Input Arguments:

Argument Number Column Name Data Type Data Size Nullable
1 SQL Query SQL_C_CHAR 64 KB No

Output result set: None

SAPurgeCacheByQuery will purge a cache entry that exactly matches the logical query plan derived from a specified query. This function takes one, and only one, parameter representing query text.

For example, suppose you have a query cache entry generated from the following query that retrieves the names of all employees earning more than $100,000:

select lastname, firstname from employee where salary > 100000;

The call below programmatically purges the cache entry associated with this query:

Call SAPurgeCacheByQuery('select lastname, firstname from employee where salary > 100000' );

Purge by table

The table below formally defines the ODBC extension function for purging a cache entry associated with a physical table:

Usage: Call SAPurgeCacheByTable(?,?,?,?)

Input Arguments:

Argument Number Column Name Data Type Data Size Nullable
1 Database Name SQL_C_CHAR 128 KB Yes
2 Catalog Name SQL_C_CHAR 128 KB Yes
3 Schema Name SQL_C_CHAR 128 KB Yes
4 Table Name SQL_C_CHAR 128 KB Yes

Output result set: None

SAPurgeCacheByTable will purge all cache entries associated with a specified physical table name (fully qualified) for the repository to which the client has connected. This function takes up to four parameters representing the four components of a fully qualified physical table name:  database, catalog, schema and table name proper.

For example, suppose that you wish to purge the cache entries associated with a table having the following fully qualified name in the physical layer of the Siebel Analytics repository:

DBName.CatName.SchName.TabName

The call below programmatically purges the cache entries associated with this physical table:

Call SAPurgeCacheByTable( 'DBName', 'CatName', 'SchName', 'TabName' );

Purge All

The table below formally defines the ODBC extension function for purging all entries in the entire query cache:

Usage: Call SAPurgeAllCache()

Input Arguments: None

Output result set: None

The call below illustrates the function’s usage:

Call SAPurgeAllCache();

Invoking ODBC Extension Functions

You can call these functions using the nqcmd.exe command-line executable.
The syntax of the call will be as follows:

nqcmd -d "Analytics Web" -uadministrator -psadmin -spurge.txt

where purge.txt contains the call (for example, call SAPurgeAllCache()).

Below is sample file I wrote, which will take all the txt files and run against the weblogic server. Doing this you can seed multiple file using one batch file.

@echo off
setLocal EnableDelayedExpansion
cd %1
FOR /F "TOKENS=1* DELIMS= " %%A IN ('DATE/T') DO SET CDATE=%%B
FOR /F "TOKENS=1,2 eol=/ DELIMS=/ " %%A IN ('DATE/T') DO SET mm=%%B
FOR /F "TOKENS=1,2 DELIMS=/ eol=/" %%A IN ('echo %CDATE%') DO SET dd=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('echo %CDATE%') DO SET yyyy=%%B
SET date1=%mm%%dd%%yyyy%_%time:~0,2%%time:~3,2%%time:~6,2%%
SET date2=%mm%%dd%%yyyy%_%time:~1,2%%time:~3,2%%time:~6,2%%
Set TDate=%time:~0,2%%time:~3,2%%time:~6,2%%
SET date3=%mm%%dd%%yyyy%

for /f "tokens=* delims= " %%a in ('dir/b *.txt') do (
set str=%%a
"D:\OBIEE11g\Oracle_BI1\bifoundation\server\bin\nqcmd.exe" -d coreapplication_OHXXX -u <username> -p <passwd> -s !str! -o !str!.out.!date3!

)

Hope This helps.

Sunil S Ranka

“Superior BI is the antidote to Business Failure”

(Part of the text has been taken from Oracle Tech Note)

Posted in 11g | Tagged: , , , , | Leave a Comment »

Sample OBIEE 11g Usage Tracking Repository

Posted by sranka on September 27, 2011

Hi All

Some of you must have ran into the issue where OBIEE 11g Usage Tracking RPD Fails To Open In Administrator With [nQSError: 36015]. After working with oracle they helped us with sample usge tracking repository . Thanks to Steve Fitzgerald from Oracle , in anticipation of customers needing the file, he attached the RPDs to the following doc.

The Sample OBIEE 11g Usage Tracking RPD Fails To Open In Administrator With [nQSError: 36015] (Doc ID 1336567.1)

Hope this helps

Sunil S Ranka
“Superior BI is the antidote to Business Failure”

Posted in 11g, OBIEE, sunil s ranka | Tagged: , , , , , , , | 1 Comment »

Love and Hate Relationship :: OBIEE and BW-SAP Integration

Posted by sranka on July 8, 2011

Hi All

 

Recently I got chance onto work on BW-SAP and OBIEE 11g integration. I would say one of its kinds in world. You will get lot more examples and samples on different datasources but BW-SAP is a rare one. Working on a multidimensional data source on its own is a paradigm shift. Oracle and SAP are known for their love-hate relationship. At Openworld you will see a big SAP booth, where as in sapphire now , Oracle will show-off exadata capabilities. One of the things that surprised me was to build SAP cube, the core data set resides in Oracle relational database. I am not sure if this is a norm, but at the current installation this is the architecture. As mentioned, earlier it’s a paradigm shift to work from relational to multi dimensional. Till OBIEE 11.1.1.5, BW SAP was never a certified released, but they had a patch on 10.1.1.3.4 which using XMLA, we could connect to BW-SAP. In OBIEE 11.1.1.3 we could continue to use XMLA approach but as its not certified, you will not get any luck with Oracle-support. In 11.1.1.5,  BW-SAP is officially certified. What it means is that its been tested, if you look at the all the issues resolved there is only one known bug was resolved as part of 11.1.1.5. Apart from all the available features in 11.1.1.3, the key differentiator is the support for SAP BW and the Admin Tool natively understands hierarchies, dimensions, etc., and imports them directly.

 

It’s recommended that you go with the BAPI (native) adapter.  Expectation is that you will find better performance because BW handles things more efficiently within its internal processes with BAPI than with XMLA, even if it is the same MDX being sent via either protocol.

 

As per my understanding, if you don’t re-import, the old version (11.1.1.3) of XMLA would still work to some degree, but you would soon run into issues.  If it is working in 11.1.1.3, it would likely still be working in 11.1.1.5, but it’s strongly recommend that you re-import in 11.1.1.5 to take advantage of the improvements made.

 

As mentioned earlier, it’s a paradigm shift below are some of my observations:

 

  • SAP BW as a datasource is not supported with OBIEE 11.1.1.3
  • In multidimensional once you import the cubes/BEX query, it’s a black hole, there is not much you could do it on physical layer like alias renaming etc.
  • Re-importing of BEX/Multi provider could possibly change the column ids, hence resulting recreation of BMM, Presentation layer followed by re-creation of reports
  • Presentation variables can not be used on the key columns, as they generate in correct MDX
  • Filters On Non Key Columns are NOT added to MDX Query see note :1288595.1 on Oracle Support
  • After importing BW-SAP cubes or BEX, all the non integer columns get converted to varchar data types
  • Due to non-availability of date data type, date functions like ago, today can not be used.
  • In some of the cases OBIEE creates sub-optimal queries.

 

There are many more observation as list is long, if in case any one of you want to discuss further in detail, please feel free to contact me.

Hope This Helps

Sunil S Ranka
“Superior BI is the antidote to Business Failure”

 

Posted in 11g | Tagged: , , , , | Leave a Comment »

Real Obiee 11G Hero

Posted by sranka on June 23, 2011

At one of the current client, we had rough ride with pre as well as Post installation of OBIEE 11.1.1.3 and 11.1.1.5. Due to the complexity of newer architecture, we ran into several infrastructure and application problems. Oracle has done a great job in building the product, but embarking on journey of early adopter was not easy, after multiple support call, it was decided that Oracle would send their GEMS on site to do miracle. We had lower expectation looking at the magnitude of infrastructure issues, but after working with Matt Abrams (Seems like an ex Army, a tough guy with head strong and focused personnel), Olivier Bennardo (Guys in Leather Jacket) and Danny Edwin (Stripe Shirts) and Ahmed Shahzad Awan (Not in picture) and Steve Fitzgerald (Not in picture, Still I am wondering how much a person can do multi Tasking) things worked like charm. It almost felt that they has some Magic Wand and things just worked, I am just kidding. With immense knowledge of the subject and internal resources they could solve most of the issues in merely, less than two and half days. Yes!!!!! just two and half days, that’s why I call them — Real OBIEE 11g Real Hero — . I am hoping and wishing that you are your team get rewarded for all your hard work.

Olivier and Danny Thanks for all the help and Sub Way sandwich 🙂

20110622-104739.jpg

Posted in 11g | Tagged: , , , , , , | 1 Comment »

 
%d bloggers like this: