Lets Learn OBIEE & Oracle Fusion together................: May 2013

Don’t not only copy the click but be sure you understand what is happening!
Download and install the extended sales sample RPD and CAT. Dissect every report and dashboard to understand how the reports work.

http://download.oracle.com/technology/products/bi/files/oracle_bi_sample_analysis_setup_files.zip

Have a good look around at the OTN forums. Most questions have been asked before.

OBIEE forum: http://forums.oracle.com/forums/forum.jspa?forumID=378
OBISE forum: http://forums.oracle.com/forums/forum.jspa?forumID=404

Read the works of Stephen Few on how to design a good dashboard from the visual standpoint:

Learn to create reports and dashboard which people need, which is often completely different of the reports and dashboard they intentionally asked for…

http://www.orm.net/

If your start an 11g project brush-up your weblogic skills:

http://www.oracle.com/technetwork/middleware/fusion-middleware/documentation/index.html

Configure your OTN account so that you “watch” certain users. Looking around at the forum you soon learn which users give the most valuable answers.
Start following the OBIEE related blogs. At this moment there are about 10 your really need to watch. (Check my blog list).
Must read material:

http://greatobi.wordpress.com/2010/01/07/can-you-just-turn-answers-on/ {Jeff McQuigg}
http://greatobi.wordpress.com/2009/08/24/using-report-specs-for-requirements/ {Jeff McQuigg}
http://exoticexpedition.wordpress.com/2010/01/15/6-guiding-principles-of-obiee-dashboard-design/ {Deepanshu Mehta}
http://it.toolbox.com/blogs/eye-on-obi/understanding-the-oracle-bi-server-see-the-light-31891 {Gerard Braat}
http://www.biconsultinggroup.com/obiee-tips-and-tricks/ {BICG}
http://greatobi.wordpress.com/2011/07/06/prototyping-with-obi/ {Jeff McQuigg}

If you need more practices check out the Oracle Learning Library:

http://apex.oracle.com/pls/apex/f?p=OTNCR:1:0

Till Next Time

Saturday, May 18, 2013

The Three Most Common Mistakes Made in Oracle BI Application projects

For a variety of reasons, completing a successful Oracle BI Applications project is not as straightforward as one might think considering that the BI Applications are touted as a pre-built, end-to-end solution out of the box.

Based on our experience with either implementing new Oracle BI Applications projects or following on to failed projects, there are three common mistakes made that can determine the success or failure of the project:

1. Failing to follow the installation and configuration guides

This may seem difficult to believe but there are many cases where projects have been implemented without following the specific instructions in the installation guide and/or the configuration guide.

The installation guide is critical for setting up the infrastructure for Informatica, DAC, and the OBIEE platform.

Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users

Some of the more common steps missed include:

Setting up the SSE_ROLE for the target data warehouse user
Configuring the proper Code Page settings for data movement between source and target
Failing to review and apply the supplied Oracle database parameter settings in the parameter template file (for example the one for Oracle 11g named init11g.ora)
Not setting PowerCenter Integration Services custom properties - specifically the overrideMpltVarWithMapVar parameter which enables Informatica to evaluate parameters within mapplets.

The configuration guide provides instructions on how to set up both common Oracle BI Application areas and dimensions as well as functional area configuration steps.

Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users

Under common dimensions, it is critical that the exchange rate and calendar configuration is followed correctly and relates to the specific source system environment that will be used.

For the functional areas, there are a series of configuration files that must be reviewed and edited to conform to the source system. For example, the Human Resources functional area requires that the
band dimension files for Person Age, Job Requisition Age, Performance Ratings, and Period of Service are configured before the data is loaded.

Also domain CSV files for Ethnic Group, Sex, Employment, and other HR attributes should be reviewed prior to the data load, For HR Analytics, the most critical domain file that must be configured is the one that populates the Workforce Event dimension. This file (named domainValues_Wrkfc_EventType_psft.csv for Peoplesoft implementations) maps each employee assignment event from the PS_JOB table to a standard set of values for Hires, Terminations, and other job related activities. This file should be reviewed with knowledgeable HR subject matter experts to properly categorize each Action/Reason Code combination into the standard event types.

2. Developing dashboards without continuous user involvement

The four words that strike fear into the heart of any Oracle BI applications consultant are "I have a spreadsheet".    In many implementations, dashboard development requirements are taken directly from one or more existing spreadsheets that are passed among various business organizations.   This approach more often than not leads to a disappointed user base when a final dashboard is delivered because OBIEE, while quite powerful, cannot always replicate the form and function that is easily built into a spreadsheet.

A far better approach is to take the existing spreadsheets and work through a fit-gap analysis to understand the business requirements and metrics that drive the spreadsheet. After that is completed, the OBIA data model should be modified to reflect those requirements before any actual dashboard and analysis configuration is started.    Once the data model is ready and available with either actual or test data, workshops should be scheduled with users to demonstrate the capabilities of OBIEE on top of that data model.   Rather than duplicating spreadsheets, focus on the data model and the flow of an analysis. Many spreadsheets have thousands of rows that are filtered by the user and then pivoted to create other summary analyses.

Start with a top down approach on the dashboards, focusing on:

dashboard prompts to filter reports automatically
drilling and navigation
conditional highlighting
ranking reports to identify outliers and top performers
charts that visually display trends
multiple view types of the same data using view selectors
column selectors
filters and calculations driven by presentation variables

The key is to get users to think about interactive analysis instead of data dumps and scrolling through long table format reports.

It is important to push back on users when they ask for features that are not easily achieved in the OBIEE tool or require significant modification to the data model just to meet a very specific reporting requirement. Balancing the development and maintenance of any OBIEE code with what can be occasionally excessively specific user report requirements should be considered before heading down a path that can lead to project delays.

Involve users throughout the development process to get their input and feedback. With the rapid development capabilities of Answers, it is very easy to modify the layout of dashboards and analyses on the fly to get buy-in from the users.

3. Implementing the RPD without modification

The delivered metadata repository (RPD) that comes with the Oracle Business Intelligence Applications should not be considered a final product.    On every OBIA project, one of the first tasks that should be performed is an RPD Review with the business users to develop a list of customizations that will make the Presentation Layer of the RPD a more effective representation of the business.    Performing this process early on will greatly reduce the development time later on when reports are developed.   It also is very helpful in improving the user adoption experience if they are new users to the OBIEE Answers tool.

The three R's of the RPD review process are: Rename, Remove, and Reorder.

Rename any presentation column or table to reflect the business definition. It is far easy to rename a column than to get user's to convert their known business vocabulary to match that of OBIA. For example, rename the Out of the Box Employee Organization table and columns to be Department.

Remove any presentation columns and tables that are not required for analysis. This includes any columns that may be exposed in the Presentation Layer but are not populated by the ETL for the particular source system for the implementation.   Work under the assumption that any column exposed in the Presentation Layer must be populated by ETL, unit tested for accuracy, and useful for creating analyses.    Simplicity yields project success.

Reorder presentation tables and columns to be more effective for users.   Put most frequently used columns at the top of presentation tables.    Put dimension tables at the top and facts at the bottom of the subject area.    Group similar metrics together either by purpose or by time series.    Make good use of presentation table foldering to minimize the number of attributes and metrics displayed.

Conclusion:

There are no guarantees of success when implement a BI application. But there are certainly ways to increase the possibility of attaining the ultimate goal: satisfied users with a useful business analysis tool delivered on time and on budget. It can be done.

Understanding the types of tables in Oracle Business Intelligence Data Warehouse

The Oracle Business Analytics Warehouse (OBAW) is based on the dimensional modeling technique where fact tables are joined to multiple related dimension tables to form a "star schema". While at the most basic level the star schema can be represented by dimensions and facts, the process of building those tables in the Oracle Business Analytics Warehouse requires several more table types.

The standard OBAW ETL (Extract, Transform, Load) process includes the following tables:

Dimension Staging (_DS) contains data loaded from a source application database.

Dimension Target   (_D) contains the transformed dimension data for the final star schema model.

Aggregate tables (_A) sontains pre-summarized data at a higher grain than the base fact table. Aggregate tables are used to improve query performance on front end tools.

Fact Staging tables (_FS) contains staged metric data prior to ETL transformation.   Foreign key values for the dimensions have not yet been translated to WID (warehouse ID) key values.

Fact tables (_F) contains the metrics to be analyzed along with foreign key WID (warehouse id) values pointing to the associated dimensions.

Delete tables (_DEL) contains the primary keys of rows that were physically deleted from the source application. These rows are either flagged or deleted from the data warehouse during the ETL process.

Dimension Hierarchy tables (_DH) contains dimension hierarchical structure information. These are flattened hierarchies with multiple columns representing each level of the hierarchy, typically a code and name pair at each level and allow for rolling up data at various summary group levels.

Staging tables for Dimension (_DS) contains dimension hierarchy information that has not been processed by final ETL transformations.

Internal tables (_G, _GS, _S) Internal tables are referenced by ETL mappings for data transformation purposes as wells as controlling ETL execution.

Mini dimension tables (_MD) Include combinations of the most queried attributes of
their parent dimensions. These smaller "combo" tables are then joined to the fact tables in order to improve query performance.

Persisted staging tables (_PS) contains a static copy of source table data with additional calculated columns in order to improve ETL processing.    These tables are useful when there isn't a clear incremental extract date available to handle changes that affect multiple records. These tables are never truncated except during a full load.

Pre-staging temporary table (_TMP) Source-specific tables used as part of the ETL processes to make the source data match the standard staging table structure. These tables contain intermediate results that are created as part of the conforming process

Sunday, May 5, 2013

Best Practice BI Admin tool

Best Practice at presentation layer .
1.       Catalogue to map to one business layer and one BMM layer only .
2.       Use Parent folder and sub folder to group fact and similar dimension together .
3.       Avoid use of alias while adding new column in Presentation layer .
4.       The column should be shorter alphabetically .
5.       Get customer sign off about the column before building reports
6.       Make proper use of permission in the PPL .
7.       Don’t use “ in column .
8.       PPL column name should not have same name as table name
9.       Limit number of object in folder to 10 t0 12 .
10 Use object description
11-Remove the primary key column and other column

Best Practice at BMM layer .

1. Always use Start schema and minimize snow flex .

2. Create dimension hierarchies for every dimension in BMM

3. Define Key at each level of hierarchies

4. Combined all the attribute that describe single entity into single logical table.

5. Never delete logical column that map to key of physical DIM table

6. Do not keep unwanted phycal column in logical tables

7. Whenever do necessary consistency check right click on the changed business modal instead of going for full to avoid time.

8. Fix the warning if any don’t ignore

Best Practice at Physical layer .

1. Try to always import table instead of creating to avoid data type and name issue

2. For each DIM only one primary key is must but for fact no need.

3. If only composite key is available create a single physical key and add all the composite into it

4. Avoid using complex join in PH layer .

5. Always use number – number join

6. Facts should not be joined .

Friday, May 3, 2013

Oracle Business Intelligence Applications (OBIA 7.9.6.3) Details as of now from my knowledge

Oracle Business Intelligence Applications (OBIA) are complete, prebuilt BI solutions that deliver intuitive, role-based intelligence for everyone in an organization from front line employees to senior management that enable better decisions, actions, and business processes. Designed for heterogeneous environments, these solutions enable organizations to gain insight from a range of data sources and applications including Siebel, Oracle E-Business Suite, PeopleSoft, and third party systems such as SAP.

Oracle BI Applications are built on the Oracle BI Suite Enterprise Edition, a comprehensive, innovative, and leading BI platform. This enables organizations to realize the value of a packaged BI Application, such as rapid deployment, lower TCO, and built-in best practices, while also being able to very easily extend those solutions to meet their specific needs, or build completely custom BI applications, all on one common BI architecture.

Oracle BI Applications includes the following:
■ Oracle Financial Analytics
■ Oracle Human Resources Analytics
■ Oracle Supply Chain and Order Management Analytics
■ Oracle Procurement and Spend Analytics
■ Oracle Project Analytics
■ Oracle Sales Analytics
■ Oracle Service Analytics
■ Oracle Contact Center Telephony Analytics
■ Oracle Marketing Analytics
■ Oracle Loyalty Analytics
■ Oracle Price Analytics
and more

Oracle BI Applications is a prebuilt business intelligence solution.
Oracle BI Applications supports Oracle sources, such as Oracle E-Business Suite Applications, Oracle's Siebel Applications, Oracle's PeopleSoft Applications, Oracle's JD Edwards Applications, and non-Oracle sources, such as SAP Applications. If you already own one of the above applications, you can purchase Oracle Business Intelligence Enterprise Edition and Oracle BI Applications to work with the application.
Oracle BI Applications also provides complete support for enterprise data, including financial, supply chain, workforce, and procurement and spend sources. These enterprise applications typically source from both Oracle data sources, such as Oracle EBS and PeopleSoft and non-Oracle data sources, such as SAP

Topology for an Oracle BI Applications Deployment

Oracle BI Applications and Informatica PowerCenter can be deployed flexibly across a wide range of topologies on different platforms and combinations of platforms.

Machine A (Windows-only)
Machine A is a machine that has installed Oracle Business Intelligence Enterprise Edition, on which you run the Oracle BI Applications installer to install the Oracle BI Applications files.
Note: The instance of Oracle Business Intelligence Enterprise Edition does not need to be the functional version of Oracle Business Intelligence Enterprise Edition that you will use to deploy dashboards in your live system. This instance is only required to enable the Oracle BI Applications installer to install the Oracle BI Applications files on a machine
After the Oracle BI Applications files have been installed on Machine A, the DAC Client is installed on Machine B, and the DAC Server is installed on Machine C.
In addition, the following files are copied from the installation machine (Machine A) to the Business Intelligence Deployment Tier (Machine Group F) as follows:
- The OracleBI\Server\Repository\OracleBIAnalyticsApps.rpd file is copied from Machine A to the machine that runs the BI Server in Machine Group F.
- The OracleBIData\Web\Catalog\EnterpriseBusinessAnalytics\*.* files are copied from Machine A to the machine that runs the BI Presentation Services Catalog in Machine Group F.

• ETL Tier (Functional)
o Machine B (Windows-only)
Runs the DAC Client and Informatica PowerCenter Client Tools.
o Machine C (Windows, UNIX, Linux)
Runs the DAC Server and Informatica PowerCenter Services.
o Machine D (Windows, UNIX, Linux)
Hosts the transactional (OLTP) database.
o Machine E (Windows, UNIX, Linux)
Hosts the Oracle Business Analytics Warehouse database

• BI Deployment Tier (Functional)
The BI Deployment tier is used to deploy the business intelligence dashboards.
o Machine Group F (Windows, UNIX, Linux)
Machine Group F is a group of machines that runs the Oracle Business Intelligence Enterprise Edition components. For example, one machine might run the BI Server and another machine might run the BI Presentation Services

Oracle Business Analytics warehouse architecture when deployed with Informatica PowerCenter and DAC

High-level analytical queries, like those commonly used in Oracle Business Intelligence, scan and analyze large volumes of data using complex formulas. This process can take a long time when querying a transactional database, which impacts overall system performance.
For this reason, the Oracle Business Analytics Warehouse was constructed using dimensional modeling techniques to allow for fast access to information required for decision making. The Oracle Business Analytics Warehouse derives its data from operational applications and uses Informatica PowerCenter to extract, transform, and load data from various supported transactional database systems (OLTP) into the Oracle Business Analytics Warehouse.

• The Client tier contains the Informatica PowerCenter client tools and the Oracle BI Data Warehouse Administration Console (DAC). DAC is a command and control interface for the data warehouse to allow for set up, configuration, administration, and monitoring of data warehouse processes.

• The Server tier contains the following:
o DAC Server. Executes the instructions from the DAC Client. The DAC Server manages data warehouse processes, including scheduling, loading of the ETL, and configuring the subject areas to be loaded. It dynamically adjusts its actions based on information in the DAC Repository. Depending on your business needs, you might incrementally refresh the Oracle Business Analytics Warehouse once a day, once a week, once a month, or on another similar schedule.
o DAC Repository. Stores the metadata (semantics of the Oracle Business Analytics Warehouse) that represents the data warehouse processes.
o Informatica PowerCenter Services:
 Integration Services - The Integration Service reads workflow information from the repository. The Integration Service connects to the repository through the Repository Service to fetch metadata from the repository.
 Repository Services - The Repository Service manages connections to the PowerCenter Repository from client applications. The Repository Service is a separate, multi-threaded process that retrieves, inserts, and updates metadata in the repository database tables.
o Informatica Repository. Stores the metadata related to Informatica workflows.
• The Database tier contains the OLTP and OLAP databases.

The Informatica Repository stores all of the Informatica object definitions for the ETL mappings that populate the Oracle Business Analytics Warehouse. It is a series of repository tables that are stored in a database, which can be a transactional, analytical, or separate database

Oracle BI Applications Components (with Informatica/DAC)

Oracle Business Analytics Warehouse Overview
The Oracle Business Analytics Warehouse is a unified data repository for all customer-centric data, which supports the analytical requirements of the supported source systems.
The Oracle Business Analytics Warehouse includes the following:
• A complete relational enterprise data warehouse data model with numerous prebuilt star schemas encompassing many conformed dimensions and several hundred fact tables.

• An open architecture to allow organizations to use third-party analytical tools in conjunction with the Oracle Business Analytics Warehouse using the Oracle Business Intelligence Server

• A set of ETL (extract-transform-load) processes that takes data from a wide range of source systems and creates the Oracle Business Analytics Warehouse tables.

• The Oracle Business Intelligence Data Warehouse Administration Console (DAC), a centralized console for the set up, configuration, administration, loading, and monitoring of the Oracle Business Analytics Warehouse

Important points

The metadata for a source system is held in a container
The PowerCenter Services can be installed on UNIX or on Windows.

The PowerCenter Client Tools must be installed on Windows.
You must co-locate the DAC Client with the Informatica PowerCenter Client Tools.
■ You must co-locate the DAC Server with Informatica PowerCenter Services.
DAC produces parameter files that are used by Informatica. If an execution plan
fails in DAC and you want to debug the workflow by running it directly from
Informatica, then the parameter file produced by DAC should be visible to
Informatica. This is one reason for the requirement to co-locate the DAC and
Informatica components as stated above
The DAC installer installs the DAC Client and DAC Server on Windows.

DAC Client only runs on Windows.
DAC Servers runs on Windows, UNIX, and Linux.
The DAC Server can run on Linux, but it must first be installed on a Windows
machine, then copied over to a Linux machine. Oracle does not provides an installer
for DAC on UNIX

The DAC Client can only be installed and run on Windows.
■ The DAC Client must be installed on the machine where Informatica PowerCenter
Client Tools was installed.
■ The DAC Server must be installed on the machine where Informatica PowerCenter
Services was installed.
■ You must install Informatica PowerCenter Services before you install DAC.
■ The correct version of the JDK is installed by the DAC installer.
■ The DAC installer installs DAC in the DAC_HOME\bifoundation\dac directory.

The DAC Client uses the Informatica pmrep and pmcmd command line programs
when communicating with Informatica PowerCenter. The DAC Client uses pmrep to
synchronize DAC tasks with Informatica workflows and to keep the DAC task source
and target tables information up to date.
In order for the DAC Client to be able to use the pmrep and pmcmd programs, the
path of the Informatica Domain file 'domains.infa' must be defined in the environment
variables on the DAC Client machine.
When you install DAC using the DAC installer, the Informatica Domain file is defined
in the environment variables on the DAC Client machine.

INFA_DOMAINS_FILE
C:\Informatica\9.0.1\clients\PowerCenterClient\domains.infa
The DAC Server uses the following command line programs to communicate with
Informatica PowerCenter:
■ pmrep is used to communicate with PowerCenter Repository Services.
■ pmcmd is used to communicate with PowerCenter Integration Services to run the
Informatica workflows.

The pmrep and pmcmd programs are installed during the PowerCenter Services
installation in the INFA_HOME\server\bin directory on the Informatica PowerCenter
Services machine

Oracle Business Intelligence Applications V7.9.6.3 requires Oracle Business
Intelligence Enterprise Edition V11.1.1.5.0.
■ Oracle Business Intelligence Applications V7.9.6.3 requires Informatica
PowerCenter V9.0.1. Hotfix 2.
■ Oracle Business Intelligence Applications V7.9.6.3 requires Oracle Data Warehouse
Console V10.1.3.4.1.

The OBIEE server uses the metadata to generate the SQL queries. It is stored in the
Repository or often referred to as .rpd. The repository has three layer as shown below, the physical layer, logical layer and the presentation layer.

Metadata maps the OBAW physical tables to a generic business model and includes more than 100 presentation catalogs (aka subject areas) to allow queries using Oracle BI clients such as Answers, Dashboards

High Level Data Flow
• Source – eBS (Raw Data)
• ETL – Extraction Transform and Load (Informatica – PowerCenter or
ODI)
• OBAW - Business Analytics Warehouse
• OBIEE Metadata
• OBIEE Content – Reports and Dashboards

• A prebuilt Informatica repository which includes mappings (wrapped in workflows) to extract data from the supported source systems (various versions of standard applications such as Siebel CRM - yes it was first - , Oracle eBusiness Suite, Peoplesoft, JDEdwards and SAP (BI Apps version 7.8.4) ) and load the data into the Oracle Business Analysis Warehouse. (Note: Oracle BI Applications version 7.9.5.2 includes a repository for the Oracle Data Integrator instead of Informatica but supports only Oracle Financials).

• The Oracle Business Analysis Warehouse (OBAW), a prebuilt schema (stars that is) which serves as a turnkey data warehouse including dozens of stars and accompanying indexes, aggregates, time dimensions and slowly changing dimension handling (and yes, it can be optimized).
• A prebuilt repository for the Data Warehouse Administration Console (DAC) server which is the orchestration engine behind the ETL process. The DAC client and server were included until version 7.9.5. Since then it is a separate installer.
• A prebuilt Oracle BI Server repository (rpd file) which maps the OBAW physical tables to a generic business model and includes more than 100 presentation catalogs (aka subject areas) to allow queries and segmentation using Oracle BI clients such as Answers, Dashboards and Segment Designer. Did I mention that it also takes care of authentication and data security?
• A prebuilt presentation catalog (repository) containing hundreds of requests and ready-to-use dashboards which enable tight integration between the source applications and the BI infrastructure (example: Click the Service Analytics screen in Siebel CRM, work with the dashboard data, drill down to details and click an action button to navigate back to the Siebel CRM record).

Oracle BI EE 10g is the successor of Siebel Analytics and Oracle BI Applications is the successor of Siebel Analytics Applications.

Detailed Data flow

First, the DAC scheduler kicks off jobs to loadrefresh the OBAW at regular intervals or alternatively, these jobs could be kicked off manually from the DAC client.
The DAC server uses the object and configuration data stored in the DAC repository to issue commands to the informatica Server.
The informatica server executes the commands issued from DAC, and uses the objects and configuration data stored in the informatica repository.
Then the data are extracted, transferred and loaded from the transactional databases into the OBAW target tables.
After the ETL is complete and OBAW is online, an OBIEE end user runs a dashboard or report in the Answers or Interactive dashboard.
The request comes through the web server and interacts with the presentation server.
The presentation server interacts with OBI Server and OBI server will understand the requests and hits the OBAW if it’s not cashed and extracts the data and provides it to the presentation server.
The presentation server formats the data into the required format and through web server, the request is presented to the end user

ETL process

ETL mappings are split into two main mappings, SDE mappings and SIL mappings.
2 SDE mappings load the staging tables,then SIL mappings (SILOS, SIL_Vert, PLP) load the final physical warehouse tables
3 SILOS SIL mappings are for all sources except Siebel Verticals. SIL_Vert SIL mappings are for Siebel Verticals only
4 Staging tables are suffixed with S, so W_AP_XACTS_F is the final table and the staging table is W_AP_XACTS_FS