Lets Learn OBIEE & Oracle Fusion together................

R12 VS FUSION

2014-11-22T12:21:00.000-08:00

Component	Oracle e-Business Suite	Oracle Fusion Applications
Database	Oracle Database 10g	Oracle Database 11g
Application Server	Oracle application server 10.1.2 (Forms) Oracle Application server 10.1.3 (OC4J)	Oracle WebLogic
User Interface	Forms, JSPs	Oracle ADF + ADF Java Server Faces
Portal	Oracle Portal	Oracle Webcenter
Data Model	Oracle eBS Data Model	Oracle eBS Data Model + Siebel + PoepleSoft (Trees, Data Effectivity, person Model)
Attachments/Imaging	BLOBs	UCM/Stellant
Workflow/Approval	PL/SQL	BPEL
Reports	Reports (11i), Discoverer	BI Publisher
Analytics	Discoverer	OBIEE
Financial Reporting	Financial Statement Generator	Hyperion
Integration	AIA	AIA + BPEL + More web services
XML Gateway	XML Gateway	BPEL, B2B Adapter

Oracle Fusion Middleware Components:

Its technology framework includes the following Oracle Fusion Middleware components -

1.Oracle WebLogic Server- Java application server that helps to deploy multiple applications in a robust and scalable environment.

2.Oracle WebLogic Communication Services- provides click-to-dial functionality through contextual Actions which further helps users with information and actions.

3.Oracle Identity Management- automatically manages user access and provides services and interfaces for third party application developments.

4.Oracle WebCenter- combines user interface technologies for building social applications.

5.Oracle Business Intelligence-offers a complete to generates and delivers analyses for fusion application.

6.Oracle SOA Suite-a comprehensive software for building and deploying service-oriented architecture.

7.Oracle Application Development Framework- a java framework to simplify application development by minimizing code writing

8.Oracle JDeveloper- for building service-oriented applications using industry standards

Service-Oriented Architecture (SOA):

Oracle Fusion Application are based on SOA-Services oriented Architecture.

This is a significant technology change adapted by Oracle. SOA Suite, a middleware component of Oracle Fusion Middleware, provides infrastructure component for designing, deploying and managing SOA composite applications. Various services can be created, managed and helps to put together multiple technology components into one SOA composite application.

Overview on OBIEE11g Security Setup

2014-11-12T03:44:00.003-08:00

overview on OBIEE11g Security Setup

OBIEE security consists of 2 parts:
• Security Infrastructure setup (Done from WLS Console and EM)
Here we define the Application roles and assign them privileges(that is associate Application role to a Application Policy) Application Role is created based on our security need and is used for grouping users, so that a group security policy can be defined.
• Data and Object security setup (Done from the rpd file)
To restrict users from seeing tables/columns or filtering the data they see, we need to have the object and data security in place. This is done from rpd file. This is the most important step in security implementation

Let’s see the steps involved in security implementation.

Security Infra setup

We will define a user and a group in web logic server

Log on to web logic server(WLS) console (URL http://localhost_ip:7001/console)

This is the home page of WLS console

Click on Security Realms

In Security realms page click on “myrealm”

Go to “Users and Groups” tab>”Groups”>New and add a new WLS group called DieselModelViewers_WLS

Go to “Users and Groups” tab>”User”>New and add a new user called diesel_user

;

Associate the diesel_user to DieselModelViewers_WLS group

Next log on to Enterprise Manager (URL http://localhost_ip:7001/em)

Navigate to Business Intelligence > coreapplication > Business Intelligence Instance >Security>Application Role

Create a new Application role to be used for our data and object security by clicking on new.

Call the Application Role and DieselModelViewers

The click on

Associate this application role with the WLS group DieselModelViewers_WLS created earlier

Object and Data Security Setup

Go to the Application Roles tab, check that the newly created Application role “DieselModelViewer” is visible

Click on Permissions button

We can now define a new data filter for the DieselModelViewers group, click on

Define a new security filter for the above application role as shown above.

Data security is done, next lets see object security.

We want to hide the presentation table WB_TEST from members of DieselModelViewers application role, like diesel_user. Double click on WB_TEST table and the above window opens up, click on permissionsDeny permission to DieselModelViewers by clicking NoAccess.This will restrict diesel_user from seeing WB_TEST table in his subject area.

Next log in to Answers/Analysis page.

Lets check a report that has all fuel types, since an admin user can see all fuel types we will choose to see it from weblogic user

As we see , here the report is not restricted based on fuel type.

Lets login using the diesel_user

Try to access the subject area (noe we can only access subject area, but cant create a report because of BIConsumer privilege

We dont see the WB_TEST Presentation table due to object security

We also don’t see the fuel types, other than DIESEL, due to data security

The nqquery.log shows that DIESEL filter is added on to the select statement, which confirms that data security is working.

OBIEE - Week First day and last day

2014-11-07T06:31:00.002-08:00

If we need to get week start date from a particular table,just use the below code:

TIMESTAMPADD(SQL_TSI_DAY,- DAYOFWEEK(MIN(“Dim – Date”.” Date Column”)) + 1, MIN(“Dim – Date”.” Date Column”))

For Week End Date,follow the below code:

TIMESTAMPADD(SQL_TSI_DAY, 7-DAYOFWEEK(MAX(“Dim – Date”.” Date Column”)), MAX(“Dim – Date”.”Date Column”))

DAYOFWEEK returns an integer in the range of 1 to 7. A value of 1 represents the Monday.

OBIEE -Month Start Date and Month End Date

2014-11-07T06:30:00.001-08:00

Use the below code:

Month Start Date:

TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(MIN(“Dim – Date”.”Date Column “))+1, MIN(“Dim – Date”.”Date Column”))

Here DAYOFMONTH returns an integer in the range of 1 to 31. This integer represent the count of days since beginning of the month.

Month End Date:

TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(MAX(“Dim – Date”.”Date Column”)) * -1 ,TIMESTAMPADD(SQL_TSI_MONTH, 1, MAX(“Dim – Date”.” Date Column”)))

From right to left the first TIMESTAMPADD returns next month.The second TIMESTAMPADD returns the date from current month.

2014-08-31T01:23:00.001-07:00

Chart of Accounts in Oracle Fusion Financials

Developement Project Management stages and details

2014-07-16T00:36:00.005-07:00

Project Start-up

Software Development Process Flow – Project Start-up
The Project Start-up step incorporates the following stages:
Entry Criteria
The Development Agreement/Proposal is received.
Tasks
Software estimation
Project Initiation and preparation/update
Project Management Plan (PMP)
Inspection of PMP
Phase-end Review
Validation and Verification
Approved Project Budget Sheet
Reviewed PMP
Project details made available using the Time Sheet System
Exit Criteria
Approval and release of Project Management Plan
Approved Project Budget Sheet
Phase Deliverables
Approval and release of Project Management Plan

Software Development Process Flow – Requirement Analysis
This step incorporates the following stages:
Entry Criteria
The Project Management Plan is made available to the client.
Tasks
Prepare Software Requirements Specifications (SRS).
Define Acceptance Criteria & prepare Acceptance Plan.
Provide the Phase-end Review.
Inspect the Software Requirements Specifications (SRS) Document.
Validation and Verification
In this stage, the SRS is completely reviewed and fine-tuned according to client requirements.
Exit Criteria
In this stage, the agreed SRS is made available to the client along with the Acceptance Plan.
Phase Deliverables
SRS

Software Development Process Flow – High-level Design
This step incorporates the following stages:
Entry Criteria
Agreed Software Requirement Specifications are available.
Project Management Plan is available.
Tasks
Prepare High-level Design (HLD).
Examine the HLD.
Initiate the Phase-end Review.
Validation and Verification
Review of High-level Design is validated, verified, and incorporated.
Exit Criteria
High-level Design documentation is made available.
Phase Deliverables
High-level Design document is delivered to the client with all feedbacks effectively incorporated.
Software Development Process Flow – Low-level Design
This step incorporates the following stages:
Entry Criteria
Project Management Plan is available.
HLD document is available.
Tasks
Prepare Program Specifications.
Provide a Structured Walk through.
Prepare the Test Plan and Test Cases.
Provide a Structured Walk through the Phase-end Review.
Validation and Verification
Review Test Plan.
Review Program Specification.
Review Unit Test Cases.
Review Functional Test Cases.
Review Acceptance Test Cases.
Exit Criteria
Program Specifications are released.
Unit Test Cases are released.
Functional Test Cases are released.
Acceptance Test Cases are released.
Phase Deliverables
Program Specifications
Unit Test Cases and Test Data
Functional Test Cases and Test Data
=============================

Software Development Process Flow – Testing
This step incorporates the following stages:
Entry Criteria
Project Management Plan is available.
Successfully Unit Tested Code is available.
High Level Design, Low Level Design, and Unit Test Cases are available.
Tasks
Conduct testing.
Prepare user documents.
Inspection of user documents.
Provide Phase-end Review.
Validation and Verification
Review functional test results.
Review User Documentation.
Exit Criteria
Tested System is released.
User Documentation is completed.
Phase Deliverables
Test results

Upgrade or reimplementation or Implementation initiation

2014-07-06T03:50:00.000-07:00

Whatever your Oracle Financials project, be it an R12 Upgrade , an R12 reimplementation, or a fresh greenfield Implementation. It is important that you select the right partner and this starts with the RFI, RFQ, RFP & RFT documents.

So what are they ?

RFI - Request for Information
RFQ - Request for Quotation
RFP - Request for Proposal
RFT - Request for Tender

RFI – Request for Information

This document is the starting point in the procurement process. You have identified a wide list of suppliers that you think can provide the product or services you require and need to qualify them through to the next stage of the procurement process. The purpose is simply to understand and qualify the potential suppliers, not the specific solutions. As this is the initial document it does not need to be a long one. Aim for 5-6 pages.

The RFI should include:

Company background and overview
High level explanation of
- Requirements giving rise to the RFI ( ie. Products or services giving rise to the RFI )
- Current systems and issues
- Where you want to be once the project has been completed.

RFI Should not include

Details of other suppliers the RFI has been submitted to.

What the customer wants from RFI Response	What the supplier wants from an RFI
To gain a general understanding of the competition in the market. Provide enough information for pre-qualification by procurement department. Provide the basis of short listed vendors for the next phase. Supplier references.	To Qualify you as a customer. Understanding of your business, its current issues and what you want to achieve from the project. Specifics on what products and technology the customer is interested in. Estimate of project budget.

A sample RFI Template document for free download can be found here.

What is Difference between E-R Modeling and Dimentional Modeling

2014-06-30T00:01:00.002-07:00

ER modeling the data is in normalised form. So more number of Joins, which may adversly affect the system performnace.Whereas in Dimensional Modelling the data is denormalised, so less number of joins, by which system performance will improve
ER modeling: a view of data from data processing. OLTP

MD modeling: a view of data from business porcessing.-- OLAP

AIM -- Normal practice we do

2014-06-06T10:04:00.001-07:00

MD020 - Add on solution

For each gap, a solution will be found. And each solution is recorded in this MD020 document. This will have a high level view of what the gap is and how it will be addressed in the customizations. Functional consultants will also prepare this.

MD050 - Functional design doc.

For each md020 there will be a MD050 doc..which is a functional design document. Its like an extension of the previous one with details like how the solution will be developed and all. which validations needs to be done and all.

MD070 - Technical design document

This is where you come into picture. you will be converting the functional doc to technical doc, like which API will be used, which tables will be used.. and all...based on this doc a developer will develop the code.

TE070 - Unit testing scripts and results. unit testing will be done by developer. you need to remember these terms and phases. in unit testing you will be testing components individually. like for instance in the interface development we tested the sqlloader scripts, table creation scripts, packages that call API's are tested seperately. the bugs that are found in this phase are fixed immediately then and there. The final results will be preserved and will be useful in future.

TE080 - Link test scripts and results

A separate testing team will do this. They will not look into the code but will be interested in input and out. This is like final testing. Any bugs found are notified to the development team for fixing.
=======================================================
AIM --

Project Deliverables I have used in my Implementation in sequential order

CR010 – Project Management Plan / Project Plan (WM020)

Overview Training

RD010 – Organization Structure

RD020 – Business Requirement Gathering

RD050 / BR030 – MAP Business Requirements

TA040 –Application Architecture Strategy

CRP Session I

BP080 – Future Business Model

BR010 – GAPAnalysis

CRP Session II

MD050 – Functional Design

MD070 –Technical Design

System IntegrationTesting

CV010 – Data conversion Strategy

CV060 – Data ConversionTemplates

PM010 –Transition Strategy

TE040 –Test Scripts

BR110 – Security Profiles (Roles and Responsibility Matrix)

UserAcceptanceTesting (UAT)

Cutover and Production Migration Plan

End UserTraining (End User Manual)

BR100 –Application Set up

useful XML publisher Report Syntaxes

2014-06-06T09:56:00.001-07:00

useful XML publisher Report Syntaxes

Sub Templates:

<?template:header?>

This is Last Page

<?end template?>

After that you can type in header or footer section,

<?call:header?>

Last Page:

Take the any one of the insert field type in the following syntax,

Suppose,

Field name : Last page body

Status bar: <?start@last-page-first:body?><?end body?>

This is last page

Page Break:

<?split-by-page-break:?>

Section Break:

<?for-each@section:G_CUSTOMER(This is group name)?>

Properties:

<?PASSWORD?>

In XML we have to write in,

<PASSWORD>welcome</PASSWORD>

Bar Code:

<?register-barcode-vendor:'oracle.apps.xdo.template.rtf.util.barcoder.BarcodeUtil';'XMLPBarVendor'?>

<?format-barcode:JOB;'code128a';'XMLPBarVendor'?>

Conditional Formating:

<?if:ACCTD_AMT(This is column name)<1000?><xsl:attribute xdofo:ctx="block" or “incontext” name="font-weight">bold</xsl:attribute><?end if?>

Incontext means: column level

Block means: row level

Multi Layout:

<?choose:?>

<?when:CF_CHOICE=’VENDOR’?>

VENDOR NUMBER	VENDOR NAME	LOOKUP CODE
oplv VENDNUM	VENDNAME	FLAG eloop

<?end when?>

<?when:CF_CHOICE=’INVOICE’?>

INVOICE NUMBER	DESCRIPTION	VENDOR ID
opli invoicenum	desc	FLAG eloop

<?end when?>

<?when:CF_CHOICE=’PO’?>

PO NUMBER	POVENDOR	STATUS
oplpo PONUM	POVENDOR	STATUS eloop

<?end when?>

<?end choose?>

Running Total:

<?xdoxslt:set_variable($_XDOCTX, 'RTotVar', xdoxslt:get_variable($_XDOCTX, 'RTotVar') + ACCTD_AMT(This is column name) )?><?xdoxslt:get_variable($_XDOCTX, 'RTotVar')?>

RTotVar means declare variable=0

Brought forward and carried Forward:

<xdofo: inline-total display-condition="exceptfirst" name="EntAmt"(This is column name)>Brought Forward: <xdofo:show-brought-forward name="EntAmt" (This is column name)format="99G999G999D00"/></xdofo:inline-total>

<xdofo:inline-total display-condition="exceptfirst" name="EntAmt"(This is column name)><xdofo:show-brought-forward name="AcctAmt" (This is column name)format="99G999G999D00"/></xdofo:inline-total>

<xdofo:inline-total display-condition="exceptlast" name="EntAmt"(This is column name)

>Carried Forward: <xdofo:show-carry-forward name="EntAmt" (This is column name)

format="99G999G999D00"/></xdofo:inline-total>

<xdofo:inline-total display-condition="exceptlast" name="AcctAmt"(This is column name)

><xdofo:show-carry-forward name="AcctAmt" (This is column name)

format="99G999G999D00"/></xdofo:inline-total>

<?show-page-total:EntAmt(This is column name)

;"99G999G999D00"?>

<?show-page-total:AcctAmt(This is column name)

;"99G999G999D00"?>

Page Total:

<?show-page-total:pt(This is parameter);'#,##0.00'?>

Repeating Headre:

for-each@section G_CUSTOMER

CUSTOMER_NAME

for-each G_CURRENCY

Trx Number	Trx Date	Trx Amount	Trx Amount Remaining
for-each G_INVOICESTRX_NUMBER	TRANSACTION_DATE	TRANS_AMOUNT	TRANS_AMOUNT_REMAININGend for-each

end for-each

The Three Most Common Mistakes Made in Oracle BI Application projects

2014-03-27T05:32:00.002-07:00

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 easier 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.

(ROI) Return on Investment for Business Intelligence

2014-03-09T03:06:00.004-07:00

How to justify a business intelligence system has occupied people ever since these were called decision support systems. Supposedly the numbers are “soft”. Reductions in IT costs virtually never cover the cost of replacing spreadsheets and Access databases with a proper BI system. Yet, organizations spend over a billion dollars a year on BI systems, and that’s just in the USA. In today’s environment, CFO’s will not let capital investments pass without some kind of a business case. So what do these companies do?

Let’s briefly dissect the formula for return on investment:

ROI = Expected value (NPV (cash flows from revenue increases or cost savings))
…………Expected value (NPV(cash outlays from spending on hardware, software, labor, and services))

This formula tells us what we have to look for and how we should maximize the value. We need to estimate:

Cash flows and their timings
Likelihood of these benefits occurring
Cash outlays for the BI system and their timings, including

Hardware and software costs
Implementation costs
Training costs
Administration, enhancement and upgrade costs

The likelihood of the cash outlays occurring

The formula also tells us that benefits and costs that occur in the near future are more valuable than those in the distant future because they are discounted less AND because the probability of them occurring is higher.

The classic definition of ROI looks at cash flows. Non cash accounting items do not count.

Someone who wants to be seen as delivering valuable BI projects, therefore will look for a project that delivers a set of benefits that drive value over a short period of time. If the scope is too small, the benefits will be minimal. If the scope is too large, the risk and time to deliver will be unacceptable.

I understand this is not a new finding. The formula just lays it out in black and white.

In future posts, I will discuss how to estimate these numbers, even when people feel they cannot be quantified.

Fundamental of Data and so called Big Data

2014-03-09T00:01:00.001-08:00

The hot topic that seems to be around every corner these days is “Big Data”. Most publications work under the premise that everyone already understands Big Data and the value it can bring to the organization. My experience shows that assumption is not always correct. Many folks are unclear how to recognize “Big Data” within their particular organizations. More importantly, folks may not understand the possible business value that can be extracted from it. Without both aspects of understanding, adoption and success of Big Data initiatives will face difficulties. This blog addresses those two aspects by identifying:

a) Typical “Big Data” examples within organizations

b) Real-world value propositions from harvesting “Big Data”

Simple Definition and Context

Let’s start with establishing a definition and context for “Big Data” since the name alone can be misleading. Big Data is a reference to the very large scale of data available or being created that cannot be easily handled using traditional processing, methodologies or technologies. Big Data can relate to structured or unstructured data. The challenges with Big Data can include how to capture it, the methods for storing it, how to understand it, how to analyze it, how to search it, or how to visualize it and correlate it to something familiar. Data can be tagged as “Big Data” by evaluating it against the above classifications and by considering at least two factors:

1) The scale and sheer volume of the data in comparison to what is reasonably expected

2) The speed at which it is created or is expected to grow

Since there is a lot of subjectivity in defining “Big Data”, let’s list some real-world examples to solidify the understanding.

Big Data within Organizations

Using the definition above as guidance, a growing number of possible examples may belong in the “Big Data” category. For the sake of being concise, the following represent the types of “Big Data” organizations commonly encounter and the value that can be derived from them.

Call & Contact Details

Definition

Value Proposition

Organizations that offer products and services directly to consumers will have significant call center operations with several platforms to support various methods of customer interaction and engagement. With each interaction comes information that collectively can be significant in quantity. While organizations have used some of this data for operational support, many have struggled with maximizing the analytical value.

Re-allocation of agents across centers and queues based on predictive demand planning using diverse criteria. Improve efficiency on agent utilization and load balancing of resources; enhance customer experience by reducing wait time and abandon rates.
Analytics against voice data for accurate tagging of reasons for call, reasons for transfers, etc. Optimizes understanding of customer satisfaction & response levels and improves call routing.
Mining of call characteristics (length, number, source, type, reason) to create stronger correlations for better insight
Correlations of contact metrics to recent marketing efforts to measure effectiveness and acceptance.
Creating correlations between account/customer details to offers made/accepted to increase upselling & cross-selling opportunities

Transaction Details

Definition

Value Proposition

Industries including financial firms, trading firms and large retailers surmount a continuous stream of transactional detail. This can include authorization details, trade transactions, and purchases. Most organizations deal with this vast amount of data by limiting the amount of detail data used during analysis or applying standard practices to summarize and aggregate for business intelligence.

Fraud detection techniques using transactions well beyond individual events; longer time spans, more criteria, un-related events. Leads to improved risk exposure management.
Trending of activity across various time periods to recognize patterns of behavior. Effective for identifying revenue opportunity or measuring strategy effectiveness.
Developing correlations between transactions and customer/account details (demographics, purchase history) to improve marketing strategies.
Correlations of transactions to external factors (marketing offers, news events, regional criteria) to understand behavior and measure marketing effectiveness.
Analyzing transactions using broad criteria across extensive time periods to improve forecasting accuracy.

Web Clicks & Logs

Definition

Value Proposition

Analyzing details of customers with similar interests and behavioral patterns to maximize the effectiveness of offers made to individual customers. Will improve and extend sales.Customers and prospects visiting the organization’s web sites have their own distinct behaviors and patterns. What they click, what they click next, what peaks their current interest, what peaks the interest of other visitors at this same moment are examples of behavior patterns that are valuable to better understand. Furthermore, each visit brings other interesting criteria such as originating source for visit, geographical tags, SEO tags, etc. Only a handful of companies are recognizing the strategic value in this treasure chest of information.

Correlating product sales to one another to understand buying patterns, i.e., what other products are bought along with a given product. Improve offers and up-selling opportunities.
Analyzing regional considerations for customers during a given experience to optimize target marketing and ensure relevance of offers.
Analyzing click and navigational patterns to improve customer experience, i.e., offer online chat or display tips to improve “stickiness” and overall experience.
Correlate traffic, interest and behavior to external factors including media efforts, regional criteria to measure strategy effectiveness.

Other interesting Big Data examples you may encounter include:

Application Logs

Informational, warning, error, monitoring and event messages are continuously produced by software systems, hardware devices and application platforms. Proactively recognizing potential issues from the patterns can help improve the quality of service and reliability that the IT groups need to ensure. Furthermore, it can be an element of a good risk mitigation strategy if the services and platforms are a critical part of your business. This content is often overlooked for the value it possesses.

Social Media

Tweets, Facebook and Google+ posts, blogs & responses have quickly become acceptable means of social interaction between people. The sheer number of people using these channels creates a “Big Data” problem. The data and growth is exceptionally large to deal with. The content is text-based and needs to be evaluated in context to derive at the right interpretation. Furthermore the relevance of the content (eliminating noise) is difficult to decipher. The jury is still in deliberation over the ROI for harvesting this information. Nonetheless, it’s difficult to ignore.

GPS Trace Records

Equipment, products and personnel are increasingly fitted with GPS technologies that can track every move from point A to point B. The ability to proactively analyze this movement can lead to supply chain efficiencies, human capital effectiveness, bottom-line cost reduction, fraud mitigation and allow for overall control and continuous visibility.

Technology & Instrument Output

Needless to say, there are countless unique examples within industries. Utility and communication companies produce incredible amounts of usage details that can be used to manage demand and optimize performance. Genomics and scientific organizations are deploying technologies producing ever granular bits of potentially important information.

Documents & Other Unstructured Data

Virtually every organization produces an immense amount of unstructured data, or in other words information that does not easily conform to a defined data model. This can include internal documentation, publications, correspondences, health records, audio recordings, etc. Not only is this a content management problem but it also requires unique analytical techniques to harvest value from the content. Increase the scale of it and it now becomes a Big Data challenge. Businesses can use this data for ensuring compliance, managing risk and achieving more complete records.

Many more organizations will have “Big Data” challenges over the coming years. Some of this can be attributed to their individual growth as a company but much of it is the result of technology advances and outside factors. It is safe to conclude that all organizations with Big Data will need to take some action to do something valuable with it, at least to remain competitive.

In future blogs I will talk in more specifics about individual approaches, technologies and business application around Big Data. In the meantime, please feel free to comment below or reach out to me to talk about “Big Data” challenges you are facing.

So why do successful IT implementations fail to achieve desired results?

2014-03-08T23:17:00.002-08:00

So why do successful IT implementations fail to achieve desired results?

Answer in almost all cases is lack of user adoption. I know we have heard it before but humans in general are creatures of habit. Once we are comfortable with a method – no matter how cumbersome it is – we like to stick with it. We build our comfort zone around it and resist any effort to change it. Your IT team can implement the nirvana system that solves all problems, but it will never gain the desired results if end-users don’t buy into it. The IT team needs to be both sales and marketing to get it. In reality, most IT projects focus heavily on the “technical” and completely miss the “human factor”. Oh, and the sales and marketing process doesn’t start at your launch party. Your end users need to be sold on the change before the project is even started. So put on your marketing and sales hat and get started.

Keep these 5 things in mind before you launch your next IT project:

1. Business Requirements: Business requirements come from business users, not IT. Sounds obvious but not always practiced. Usually the project team in all sincerity tries to address business problems without consulting the business users, involving them too late in the process, way after the technology/tool selection occurs. For a successful implementation, it is important that the users whose pain you are working to resolve are involved in defining the scope of the project.

2. The right tool for the job: A lot of time, the tool or technology is selected before the problem is completely defined. This is a recipe for disaster. You should not base your business needs around a tool’s capabilities. It should be chosen in response to your business needs. Sometimes- this will lead to a combination of various tools and technologies to get to the right solution.

3. Focus Groups: Once you have good understanding of business requirements and have decided on the technology, don’t try to solve everything at once. A phased approach is best to get buy in. Start with a small group of end users and walk them through the complete life cycle of the project. Get this group comfortable with the solution and you will now have end-user champions that will serve as an extended team to help promote within the organization.

4. Training and Rollout: Before you open the system for end users, make sure that you have proper training materials and a training schedule in place. Proper training is a must for successful end-user adoption. As I said earlier, people resist abrupt change. Make sure that your new implementation eases into their daily routine. End users will then realize the true value of the system and the problem it solves. Change will gradually occur.

5. BICC: Last but not least, is the implementation of proper processes and procedures. You should think about developing a BICC (Business Intelligence Competency Center). A BICC is comprised of members from both IT and business users. The days of IT producing data and end-users consuming it are gone. Now with fourth generation reporting tools like Oracle’s OBIEE, your data consumers are self enabled to access the information themselves when needed. This behooves us to make sure that the information that is being accessed is accurate and readily available. The BICC ensures that the right personnel are involved in the development, distribution and consumption of information in your organization.

Of course, there are many more factors involved in making sure the implementation of an IT project is successful, but following the above mentioned best practices will ensure that your IT project will result in a successfully utilized business solution. Stay tuned for more on this topic!

Art of BI RD

2014-01-19T06:01:00.002-08:00

Eliciting and creating requirements for an OBIEE project is a very important step in creating a successful, pervasive OBIEE system in an organization.

Throughout the requirements elicitation and creation process, you need to keep in mind that all requirements must be testable. The only way to verify if a requirement has been met is to successfully test it, and therefore, all requirements must be specific and detailed enough to allow for a QA person to verify it.

A huge and essential component of OBIEE projects is the reports being delivered in one form or another – and therefore, another set of characteristics to keep in mind are that the reports and their form of delivery need to be: accurate, relevant, timely, and actionable.

Typically an OBIEE project involves significant effort, and can take several months to complete, but visible progress can be made in a shorter time. Requirements may need to be prioritized to handle the most critical ones first (a phase 1 for example), and postpone some for later in the project – a phase 2 for example. However, it should not take months to see some results, because OBIEE is a great platform for an agile methodology – allowing the project to show some results early and ongoing, as the project becomes more and more completed.

To elicit requirements, there are a number of methods that can be used. You will need to choose the most appropriate method based on the particular scenario – who is the user, what area does the requirements cover, etc. Some of the methods used can include: interviews, observation, reviewing existing reports (from a previous system for example); soliciting information from colleagues in other companies; and developing/showing report concepts and getting feedback; brainstorming – from strategic goals and reporting needs to tactical/operational. However, you should try to learn as much as possible about the business, processes and people beforehand; and always try to be a good listener.

Requirements for an OBIEE project can be grouped into the following groups of questions:

What information does the business users need to see?

This is often driven by the company’s strategic goals. The data needs to be in aid of answering business questions that users will need to aid their decision making in order to realize operational and tactical goals that support the strategic goals.

The information could be enterprise wide, departmental, or specific subject matter.

The reporting requirements could also be classified as strategic, tactical, or operational. The strategic requirements are usually enterprise wide, while the tactical and operational requirements are usually relevant to a departmental, group or individual role. Strategic requirements can at times be monitored and tracked via Key Performance Indicators (KPIs) which can be developed and presented in OBIEE. Operational requirements at times will need Agents or iBots that trigger some action based on an event. And tactical requirements are usually satisfied using reports that display valuable metrics about the business operations.

What are your business objectives and what metrics will help you to monitor progress toward those objectives? What information do you wish you had to do your job better?

Where will the data be sourced from? In other words, what are the source systems?

The answer to this question could include Data warehouses or data marts, ERP systems, Line of Business systems (LOBs), Flat files, External sources, OLTP, OLAP, etc.

The data sources need to be defined in the OBIEE BI Repository (RPD) via Connection Pools, and the metadata for the relevant tables imported. OBIEE data sources can be relational (OLTP), multi-dimensional (OLAP) or files (Excel, XML, ADF). The OLAP data sources supported by OBIEE are Oracle Essbase, Oracle OLAP, Microsoft SQL Server Analysis Services, and SAP BW.

However, for better performance, it is best if the data sources are multi-dimensional – either star-schema relational or OLAP.

What data is required from those systems? And what data needs to be calculated or derived?

Analysis needs to be done to determine what subset of data (if not all) is needed from each of the source systems. What measures, dimensions, hierarchies, and attributes are required? What lookup tables are required?

And it’s always a good idea to ask “Why?” Why is this data needed? How will it be used?

This involves reports, and it is important to keep in mind that all report/reporting data need to be accurate, relevant, timely, and actionable.

What data that is not in the source system but can be derived? Calculations, Associations, mappings, etc – these derived items can be created in the OBIEE repository BMM layer, and exposed to users as necessary.

What granularity of data is needed? Summary, Detail, both

What time range (including the time granularity) of data is needed? Historical, Current, Real-time, Day, Month, Quarter, Year

What KPI’s are required to track the state of the business?

What data needs to be filtered out/in from each data source tables in the various scenarios?

What are some of the frequently used filter criteria? à this could drive some of the repository variables created in OBIEE

What are some frequently used values for analysis? à this could drive dashboard prompts in OBIEE

Will the business users need to perform data mining or need the results of data mining?

How frequently does the data need to be updated?

If the data is not directly connected to the source, then how often should the data be updated – real-time, hourly, daily, weekly, monthly, on-demand, etc?

Who needs to see what data? And who needs access to what functionality?

This is in essence a security question. What are the various groups/roles that need access to data, and what data should each group/role have access to?

Can the reporting system be integrated with the company’s existing LDAP? This is typically the case for most modern reporting systems including OBIEE which integrates with popular LDAP systems including Active Directory.

Does row-level security need to be implemented? OBIEE allows for row-level security.

Can all users use all features of the reporting platform? Or will only specific users be granted access to specific functionality?

What dashboards and reports will each group of users be able to see?

How will the information be shared with business users? What modes of information delivery need to be used?

Will reports be shared? email, saved to a directory, web dashboard, file (pdf, word, excel, html)?

Do users need to be proactively notified of events? – for example, a user or group needs to be notified if stock levels fall below a threshold.

The answers to this question will drive the Agents/iBots that need to be created.

Will the reports be run on a predefined schedule or based on some predefined condition? Or will they be run on-demand? This will also drive Agents/iBots and Conditions.

Do users need to download information? This will drive the ‘report links’ that are placed on the dashboard pages.

Do report results need to be preserved or can/should they be overwritten?

Will users be allowed to create their own analyses or perform adhoc analysis? And if yes, how will that activity be monitored and supported?

What visualization features are required for each report or set of data? Dashboards, Scorecards, Charts, graphs, tables, pivots, gauges, icons, colors, fonts, etc.

Will the users need to be able to drill from summary to detail reports? Rollup from detail to summary?

Will the users be able to interact with the data? Prompts, View Selectors, Column Selectors, etc

What are some of the system level requirements?

What level of system performance is required?

Dashboard and report creation tools

Does the reporting system need to be able to access/connect to multiple data sources at a time? OBIEE allows for multiple data sources connected at the same time.
Does the reporting system need to be able to access/connect to relational, multi-dimensional, and file data sources?

Does the reporting system need data mining capabilities?

Does the system need to support drill-down, rollup functionality?

What are the critical usage times for the system? In other words, what are times when the system must be available? For example, during the month-end close process or during the holiday sales season. This will drive when changes can be made to the system.

What are the highest usage times for the system? What hardware do we need to support that usage?

How will changes be handled? In other words, what is the change control process?

It is very important that all the relevant players are included in the requirements process – business leaders and business users, SMEs, technical staff, database administrators, OBIEE Developers (report developers, rpd developers), OBIEE architect, ETL developers, ETL architect. Before development officially starts, it is important to get all relevant sign-offs on the requirements. This will ensure that everyone is on the same page, and that the business users are getting what they need.

R12: Master Troubleshooting Guide for vendor Payment for Oracle Payables

2014-01-08T05:15:00.001-08:00

1. What is a Payment Process Request (PPR), and How Are PPRs Created and Managed?

What is a PPR?

Ans) In R12, a Payment Process Request, or "PPR", is a payment batch. ThePayment Batch entry form used in R11i has been replaced in R12 by a new module called the Payments Manager module (IBY), which offers a number of new features related to payment batch processing.

How is a new PPR submitted?

Ans) Using the Submit a Single Request link on the Payments ManagerDashboard (or the button on theSearch PPRs window), users are taken to a PPR entry form made up of a header and several tabbed regions. Users can enter search criteria for the invoices they want to pay, and parameters surrounding the payment process itself using the fields on the tabbed regions.

Modifying Payment Process Requests

You can modify the batch of invoices that the system selected for you, and the proposed payments generated by the system, using "review" windows along the course of the PPR process. These review "stops" are optional. When creating a new PPR, you can set parameters on the Processing tab that will cause the PPR process to stop after: 1) the system initially selects the invoices to be paid by the batch, and/or 2) after the PPR process has compiled the "proposed payments". This gives you an opportunity to review (and if necessary, modify) the invoices and "proposed" payments before they are turned into formatted Payment Instructions.

Terminating Payment Instructions

You can terminate a set of Payment Instructions at any time prior to marking the payments "Complete" - even if they have been printed - by using the Terminateicon on the Payment Instructions tab on the Payments Manager Dashboard. When this action is taken, Oracle Payments sets the status of the payment instruction to "Terminated", and informs the source product of the terminated documents payable. Then for each payment in the payment instruction, Oracle Payments sets the status to "Canceled". The source product unlocks the documents and resets their status so they are ready for future selection.

Stopping & Voiding Completed Payments

You can record a "Stop Payment" action or a "Void" action on one or more completed payments using thePayments tab on the Payments ManagerDashboard.

PPR Templates

To make entry of a new single PPR faster and easier, you can create a PPR"Template" with the search criteria and parameters that you typically use on your PPRs. Then when creating a new PPR, simply enter the name of the template in the header, and the pre-defined settings on the template are automatically copied to the fields on the header of your new PPR for you! You can accept the defaulted values, or edit them for the new PPR.

Scheduling PPRs using PPR Templates

You can use the name of a PPR Template as the Request Name on theSchedule Payment Process Requestform in the Payments Manager module, allowing users to schedule PPRs to run automatically on a periodic basis, such as once/multiple times a day, once/multiple times a week, once/multiple times a month, etc. The scheduling parameters are generally the same as for scheduling Oracle Reports or other processes.

Required Setups

To maximize all the features available in creating Payment Process Requests (and to minimize the possibility of issues), you will want to become familiar with both the required and recommended setups that are necessary before running your first PPR. Some setups in the Payments module (IBY) are common to both theFunds Capture (receipts) and the Funds Disbursement (payments) roles that the module provides. They include:

aAccess control

APIs

servlets

transmission

security

Configurable setups include:

Payment Methods

Bank Instruction Codes

Delivery Channel Codes

Payment Reason Codes

Payment Process Profiles ("PPPs")

Disbursement System Options

Payment Formats

XML Publisher Payment Templates

Internal Banks, Branches, and Bank Accounts

Suppliers

External Bank Account

"Skipped" and "Spoiled" Check Numbers
Skipped Check Numbers: If you use pre-numbered paper check stock when running a Payment Process Request ("PPR") in R12, you may have experienced a common issue faced by most Payment Administrators -- skipped checks during the printing process (most often when the batch was printed using a laser printer). That is, one or more unused checks get "stuck" to the check above it in the feeder bin, and slip through the printing process unprinted. When that happens, the check numbers that were actually printed and the check numbers that appear on the related payment reports are no longer in-sync with one another. It's important that the system is informed of these "skipped" check numbers as soon as possible so your reports will reflect appropriate check numbers, and the Last Issued Document Numberfield on the Define/Update Payment Documents window for the associated Bank Account is properly updated as well. Use the Skipped Payment Documents region on the Record Print Status window to manually enter the skipped check numbers before you confirm the PPR.Spoiled Check Numbers: Another common issue that Payment Administrators often face is the dreaded "spoiled" check during a printing process. A "spoiled" check is when your printer "eats up" (physically destroys) a payment document as it goes through the printing process -- usually causing the printer to stop until you remove the mangled document, and re-start the printer. "Spoiled" checks can actually be defined as ANY unuseable paper payment documents (checks) that have become unuseable for ANY reason (for instance, perhaps one of your boxes of unused checks was destroyed due to a flood or fire). So printer jams aren't the only cause for "spoiled" checks -- but they are the most common. It's important that the system is informed of these "spoiled" check numbers as soon as possible, so your reports will reflect appropriate check numbers, and the Last Issued Document Number field on theDefine/Update Payment Documents is properly updated as well. Spoiled checks can be marked as "spoiled" in Oracle by either: 1) reprinting a replacement check for the supplier/creditor in the same PPR before confirming the PPR, or 2) manually marking the check number as "spoiled" on the Spoiled Payment Documents region of the Record Print Statuswindow, and then paying the supplier/creditor on a later PPR or Quick Payment.
2. How does the PPR Process work? Once the user has entered their desired parameters on the header of the PPR, and clicks on theSubmit button, the PPR process follows the following four (4) program steps:
DOCUMENT SELECTION ("AUTOSELECT")
(Code: AP_AUTOSELECT_PKG): The Selection process is handled by Payables (AP), the calling product.
When a PPR is submitted, a record is created in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name, which is the same as the PPR Name.

Selection: Invoices are then selected based on Due Date, Discount Date, Pay Group, and other criteria provided by the user while completing the PPR header.

The table AP_SELECTED_INVOICES_ALL is populated with selected invoices.

The table AP_UNSELECTED_INVOICES_ALL is populated with the invoices that were not selected.

Locking:After selecting the documents, the invoices are locked to prevent other check runs from selecting the same invoices.

AP_PAYMENT_SCHEDULES_ALL.checkrun_id is populated on the selected documents (invoices).

Review:If the PPR has been setup to Stop Process for Review After Schedule Payment Selection (option available on the header of the PPR), the process stops for user review after the initial selection of payables documents has been completed. The status of the PPR is set to "Invoices Pending Review". After the user reviews and/or modifies the selected documents, and clicks on the Submitbutton, AP calls the IBYBUILD program.

If the Stop Process for Review After Schedule Payment Selectionparameter was not enabled, then at the end of invoice selection, the Build program is submitted automatically.

If no invoices met the selection criteria, the PPR is canceled automatically and the status of the PPR is set to "Canceled - No Invoices Selected".

BUILD PAYMENTS

(Code: IBY_DISBURSE_SUBMIT_PUB_PKG): The Build Payments process is handled by Oracle Payments (IBY).

The Build Payments program first creates a record in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name.

The Build Payments program goes on to populate the IBY_DOCS_PAYABLE_ALL table with the proposed payments.

The link to the payment service request table is through the PAYMENT_SERVICE_REQUEST_ID.

Internal Bank Account / Payment Process Profile Assignment(Code: IBY_ASSIGN_PUB): If the PPR has a default internal bank account and Payment Process Profile (PPP) assigned to it on the header of the PPR, the values are assigned to all of the selected documents in the PPR.

If a default internal bank account and PPP were not provided by the user on the header of the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all of the selected documents, the PPR status is set to "INFORMATION REQUIRED". The user display shows it as "Information Required - Pending Action". The user will need to use the Information Required window to provide the missing internal bank account(s) and PPP(s) for each selected document.

Document Validation (Code: IBY_VALIDATIONSETS_PUB): During this step, Oracle Payments validates all the documents (selected invoices & memos) using Pre-Defined and User-Defined Validations assigned to Payment Methods assigned to the selected documents. Afterward, the program validates all the documents again, using the Pre-Defined and User-Defined Validations assigned to Payment Formats associated with the PPPs specified on the PPR.

If all the documents pass validation, all the documents are set to a status of VALIDATED in the tables and the request status is displayed as "Documents Validated".

If there any document validation failures, Oracle Payments uses the parameter setting for "Documents" on the Validation Failure Results tab on the PPR header (the DOCUMENT_REJECTION_LEVEL_CODE) to determine the next action.

REQUEST: Reject all documents in this PPR
DOCUMENT: Reject only the document in error
PAYEE: Reject all the documents related to the supplier
NONE: Stop the PPR for review

Create Payments
(Code: IBY_PAYGROUP_PUB): The validated documents are then grouped into "proposed" payments based on the grouping rules - both User-Defined and hard-coded. It then numbers the proposed payments with an internal identifier (not "the" check number) and validates the payments.

Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents (invoices). The Build Payments program then updates the IBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values of the payment associated with each document.

If there any payment validation failures, Oracle Payments uses the parameter setting for "Payments" on the Validation Failure Results tab on the PPR header (the PAYMENT_REJECTION_LEVEL_CODE) to determine the next action.

REQUEST: Reject all documents in this PPR
DOCUMENT: Reject only the document in error
PAYEE: Reject all the documents related to the supplier
NONE: Stop the PPR for review

If the PPR setup Stop Process for Review After Creation of Proposed Payments is enabled on the Process tab of the PPR header, the displayed PPR status is set to "Pending Proposed Payment Review". This status prevents further processing until user takes action.

If this option to stop for a review is not enabled, the displayed status of the PPR is set to "Payments Created". In this status, payment instructions can be created for the PPR.

FORMAT PAYMENTS

(Codes: IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB): The Format Payments process is handled by Oracle Payments (IBY).
When a PPR is submitted, the program checks the setting for the Create Payment Instructions parameter on the Process tab of the PPR header to determine if the associated payment instruction(s) (PI) should be created automatically after the payments are created (the CREATE_PMT_INSTRUCTIONS_FLAG = Y), or if the program is to wait for a manual kick-off of the Format Payment Instructions program through the Standard Request Submission form (SRS) (the CREATE_PMT_INSTRUCTIONS_FLAG = N).

If the PPR is set up to automatically submit instruction(s), the payment_service_request_id will be populated in IBY_PAYMENT_INSTRUCTIONS_ALL because the instruction will be specific to the PPR. In this case, the instruction(s) can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID.

If the PPR is set up for the user to submit the instruction program manually on the SRS form, then when the instruction(s) is submitted, the instruction(s) is linked to the PPR through the payments selected by the instruction(s). The link in this case will be through the payment_instruction_id in IBY_PAYMENTS_ALL.

In addition, the following processing occurs during the Format Payments step (see The Extract and Format Operation Flow section in Note 1348102.1 "R12: Master Troubleshooting Guide for Oracle Payables Payment Formats & associated XML Publisher Templates"):

Number the payments (paper checks and possibly, electronic payments)

Create XML extracted message

Pass the extract to Oracle XML Publisher (also known as "BI Publisher")

XML Publisher applies the formatted template to the payments

XML Publisher formats and stores the output

Oracle Payments then updates the status of the Payment Instruction(s) and the PPR. If successful, the displayed status of the PPR is "Formatted", and the status of the Payment Instruction(s) will be "Formatted" for electronic payments and "Formatted - Ready for Printing" for check payments

Print checks:

Users can load stationery into the printer and print checks at this stage by clicking on the Take Action icon for the related Payment Instruction on the Search PPRs window.

Determine if the checks printed OK, and if so, click on the Take Action icon again to be taken to the "Record Print Status" window, and click on the Record Print Status button. If there were problems with the printing process (paper jams, skipped checks, etc.) -- especially if you are using pre-numbered check stock -- use the Reprint button to reprint the batch and record any spoiled (ruined) and/or skipped check numbers.

Transmit electronic payments:

Electronic payments can be transmitted at this point.

CONFIRM PAYMENTS

(Code: AP_PMT_CALLOUT_PKG): The Selection process is handled by Payables (AP).

In order to confirm the printing of paper checks, the user needs to use the Record Print Status window to confirm which pre-numbered paper stock printed OK, and which (if any) were skipped or were damaged beyond repair ("spoiled").

Oracle Payments calls ap_pmt_callout_pkg.payment_completed to confirm the payments. During this step, the program does the following:

Assigns sequence values for Document Sequencing (Vouchering).

Creates data in the AP_CHECKS_ALL table with the appropriate data from the IBY tables.

Inserts data into the AP_INVOICE_PAYMENTS_ALL table for the corresponding checks.

The documents (invoices) are updated in the AP_PAYMENT_SCHEDULES_ALL table to indicate in the Invoices Workbench the payment details and status.

The documents not paid in this PPR are released by setting the checkrun_id on the Payment Schedules to NULL.

The AP_INVOICES_ALL table is updated to show the payment status in the Invoices Workbench for those documents that were paid by the PPR.

Data for this PPR is deleted from the AP_SELECTED_INVOICES_ALL table.

Data for this PPR is deleted from the AP_UNSELECTED_INVOICES_ALL table.

"Completing" Electronic Payments: Electronic payments are not "confirmed" in the same way that paper documents are handled. The system will automatically mark electronic payments as "completed" based on the setting you chose for the "Completion Point" field on the header of the associated Payment Process Profile (PPP):

"Built" = the payments will be marked as "complete" when the Build process completes

"Payment Instruction is Created" = the payments will be marked as "complete" when the PI is created

Payment Instruction is Formatted" = the payments will be marked as "complete" when the PI has successfully completed the Formatting process

"Payment Instruction is Transmitted" = the payments will be marked as "complete" when they are transmitted"

3. PPR Reports: There are several reports related to PPR processing

Payment Process Request Status Report: The Payment Process Request Status Report is a report that you can run that displays proposed payment information. You can request the report to run automatically after proposed payments have been created and validated or run the report by standard report submission. The report provides parameters, such as the Payment Process Request name/identifier and runs if the Payment Process Request status is "Payments Created".

Payment Instruction Register: Once a payment instruction has been formatted, payments within that payment instruction can be reviewed in report format. The Payment Instruction Register can be run at any time after payment instruction creation. The report lists the various statuses of payments within the payment instructions, such as Formatted or Transmitted.

Separate Remittance Advice:The Separate Remittance Advice is a report sent to a payee that lists the documents payable paid as part of each payment. You can specify the format for the remittance advice document and the delivery method.

Positive Pay: A positive pay file is a security measure in the form of a document that the deploying company sends to its payment system or bank to inform it of payments made by check. When you print checks, then you can electronically transmit a list of payments to the bank or payment system that indicates the checks you printed, so the bank or payment system knows what checks to pay. This list prevents the payment system or bank from paying fraudulent checks, since such checks are not listed on the positive pay file.

R12 Oracle Payments offers two versions of the Positive Pay report:

the Positive Pay File program, and

the Positive Pay File with Additional Parameters program.These programs replace the R11i report called the Positive Pay Report.

5. Alternatives to using Payment Process Requests

Creating PPRs is the only method of creating payments in the Oracle Payments module (IBY), but you can still create single payments using thePayments Workbench form in Oracle Payables (AP). There are three types of single payments that you can create using the AP Payments Workbench form:

Quick Payment = a single computer-generated payment to be generated and printed or transmitted using Oracle

Manual Payment = a single payment already generated outside of Oracle (such as bank wires, hand-written checks, etc.)

Refund = a single payment to a supplier, usually to repay one or more overpayments received in Oracle Receivables, or to refund a credit account balance in Oracle Payables.

Remember that the Payments Workbench is limited to creating only single payments, and requires that the user manually select the desired invoices and/or memos to pay.
Payment Process Requests (PPRs), on the other hand, are by far the most popular and heavily used payment programs for Oracle users because the Oracle Payments disbursement engine enables you to greatly simplify your procedures for managing complex payment processes that span multiple payment methods, formats, check stocks, transmission protocols, currencies, organizations, and bank accounts.

Batch Payment STATUSES In R12

PPR Process STATUSES and What They Mean

As a Payment Process Request transitions through the different stages of processing, the PPR will display a "Status" to let you know where in the process the PPR has progressed to, and what's going on with it.

The list below is not a comprehensive list of EVERY possible Status that may appear on a PPR (or on its associated Payment Instruction file(s)), but it does include the statuses most commonly encountered:

NEW:
This status indicates that the PPR has been successfully submitted for processing, and the AutoSelect program is digesting the criteria provided by the user on the header of the PPR in preparation of the automatic selection the invoices and memos related to that criteria.

SELECTING INVOICES:
This status indicates that the AutoSelect program is selecting the eligible invoices/memos for the payment batch based on Due Date, Discount Date, Pay Group, and other criteria provided by the user on the header of the PPR.

CANCELLED - NO INVOICES SELECTED:
If no invoices or memos met the selection criteria provided by the user on the header of the PPR, the PPR is automatically terminated and the status changes to this status.

"MISSING..." STATUSES:
Other statuses may appear at this point in the process if the user failed to included required information on the PPR header, such as "Missing Exchange Rates", etc.

INVOICES SELECTED:
After selecting the documents (invoices/memos), they are locked to prevent other checkruns from selecting the same documents.

INVOICES PENDING REVIEW:
This status will only appear if you selected the Stop Process for Review After Scheduled Payment Selection option on the Processing tab of the PPR header. This status means that the PPR process has stopped, and is waiting for you to review the invoices and memos that were selected for payment (and make any changes to the batch, as needed). Click on the Take Actionicon to be taken to the Review Selected Scheduled Payments window.

CALCULATING SPECIAL AMOUNTS:
This status will only appear if you selected the Calculate Payment Withholding and Interest During the Scheduled Payment Selection option on the Processing tab of the PPR header. This status means that interest and withholding tax are being calculated and applied, as necessary, to the invoices and memos selected for this payment batch.

ASSEMBLING/ASSEMBLED PAYMENTS:
An "interim" status, it appears after the calculation for interest and withholding has been completed, and the Build Payments program is starting. It may appear again later after the user provides any required bank account and PPP information for the invoices/memos ("documents") selected.

INFORMATION REQUIRED - PENDING ACTION:
This status appears if you did not provide a default Internal (Disbursement) Bank Account and/or PPP on the header of the PPR. In that case, you need to click on the Take Action icon to be taken to a form where you can decide which internal bank account and PPP should be used for each invoice and memo selected for payment.

PENDING PROPOSED PAYMENT REVIEW:
This status will only appear if you selected the Stop Process for Review After Creation of Proposed Paymentsoption on the Processing tab of the PPR header. In this case, the system is waiting for you to review (and modify, if needed) the proposed payments for this batch. Click on the Take Action icon to be taken to the Review Proposed Payments window.

FORMATTING:
This status indicates that the proposed payments have been turned into payment instruction files. At this point, you will want to click on the Show link to view the new associated payment instruction file(s). Each payment instruction file with have their own PI Reference Number. If you have both electronic and paper ("check") payments involved in this payment batch, you will see a payment instruction file for each type of payment method.

PAYMENT INSTRUCTION STATUSES:
An "electronic" type of payment instruction file will usually be marked asFORMATTED at this stage, which means the PI has been created, (and based on your setups) may also have been transmitted, and even marked as "Complete".

A "check" type of payment instruction file will (based on your setups) usually be marked as FORMATTED - READY FOR PRINTING, which means the payment instruction file was created, and is waiting to be sent to your printer. Click on the Take Action icon to send the file to the printer.

Afterward, the Status will change to SUBMITTED FOR PRINTING. Click on the Take Action icon again to "confirm" the payments (Record Print Status). This is also where the user will have an opportunity to "Reprint" the payment instruction file, if there were problems during the first printing process. Once the payment instruction file has been printed, an internal Payment Reference Number is assigned to the payment, along with a Paper Document(Check)Number.

Once the user clicks on the Record Print Status button (and confirms it), the payment instruction file's Status changes again, this time to PRINTED.

CONFIRMED PAYMENT:
Once the payment instructions have been transmitted/printed and confirmed, the Status of the PPR changes to this status to indicate a successfully completed payment batch (PPR).

TERMINATED:
If the user terminates a PPR anytime prior to confirmation of the payments (using the Terminate icon), the status will change to "Terminated", and the PPR is permanently closed.

Data mart and dwh

2013-12-28T10:10:00.001-08:00

Datawarehouse and DataMart:Definition & Difference

Data warehousing and Data mart are tools used in data storage.With passage of time, small companies become big & this is when they realize that they have amassed huge amounts of data in various departments of the organization. Every department has its own database that works well for that department. But when organizations intend to sort data from various departments for sales, marketing or making plans for future, the process is referred to as Data Mining. Data Warehousing and Data Marts are two tools that help companies in this regard. Just what the difference between data warehousing and data marts is and how they compare with each other is what this article intends to explain.

Data Warehousing

A data warehouse is a collection of data marts representing historical data from different operations in the company. This data is stored in a structure optimized for querying and data analysis as a data warehouse. Table design, dimensions and organization should be consistent throughout a data warehouse so that reports or queries across the data warehouse are consistent. A data warehouse can also be viewed as a database for historical data from different functions within a company.This is the place where all the data of a company is stored. It is actually a very fast computer system having a large storage capacity. It contains data from all the departments of the company where it is constantly updated to delete redundant data. This tool can answer all complex queries pertaining data.

Data Mart

A data mart is a segment of a data warehouse that can provide data for reporting and analysis on a section, unit, department or operation in the company, e.g. sales, payroll, production. Data marts are sometimes complete individual data warehouses which are usually smaller than the corporate data warehouse.It is an indexing and extraction system. Instead of putting the data from all the departments of a company into a warehouse, data mart contains database of separate departments and can come up with information using multiple databases when asked.

IT managers of any growing company are always confused as to whether they should make use of data marts or instead switch over to the more complex and more expensive data warehousing. These tools are easily available in the market, but pose a dilemma to IT managers.

Difference between Data Warehousing and Data Mart

It is important to note that there are huge differences between these two tools though they may serve same purpose. Firstly, data mart contains programs, data, software and hardware of a specific department of a company. There can be separate data marts for finance, sales, production or marketing. All these data marts are different but they can be coordinated. Data mart of one department is different from data mart of another department, and though indexed, this system is not suitable for a huge data base as it is designed to meet the requirements of a particular department.

Data Warehousing is not limited to a particular department and it represents the database of a complete organization. The data stored in data warehouse is more detailed though indexing is light as it has to store huge amounts of information. It is also difficult to manage and takes a long time to process. It implies then that data marts are quick and easy to use, as they make use of small amounts of data. Data warehousing is also more expensive because of the same reason.

Summary

• Data mart and data warehousing are tools to assist management to come up with relevant information about the organization at any point of time

• While data marts are limited for use of a department only, data warehousing applies to an entire organization

• Data marts are easy to design and use while data warehousing is complex and difficult to manage

DWH Questions

2013-12-27T12:32:00.000-08:00

What is an ODS?
An Operational Data store is a construct, which has been designed to overcome the need for Information (EIS) in the Operational area. It is similar to a data warehouse but differs in many respects. Sitting in between the operational, transaction processing environment and the enterprise data warehouse is this structure called the ODS. The ODS provides foundation to achieve tangible integrated operational results in a short time frame. While the data warehouse offers no relief to the organization struggling with nonintegrated operational systems, the ODS offers immediate relief.

What is a Federated Data Warehouse?
Most techniques used by organizations to build a data warehousing system employ either a top-down or bottom-up development approach. In the top-down approach, an

Most techniques used by organizations to build a data warehousing system
employ either a top-down or bottom-up development approach.
In the top-down approach, an enterprise data warehouse (EDW) is built in an iterative manner
and underlying dependent data marts are created as required. In the bottom-up approach,
independent data marts are created with the view to integrating them into an enterprise
data warehouse at some time in the future. There are many pros and cons of the two approaches
but there is a steady trend toward the use of independent data marts, especially with the move toward
the use of turnkey analytic application packages.

A solution must offer low cost and rapid ROI advantages of the independent data mart approach
without problems of data integration in the future. Such a solution is called the federated data warehouse.
Two components of a federated data warehouse are the common business model and shared information
staging areas.

What is a Virtual Data Warehouse?

When end-users access the “system of record” (the OLTP system) directly and generate “summarized data” reports and thereby given the feel of a “data warehouse”, such a data warehouse is known as a “virtual data warehouse”.

What is Top-Down architecture of Warehouse development?

The interaction associated with the architecture begins with an Extraction, Transformation, Migration, and Loading (ETML) process working from legacy and/or external data sources. Extraction transformation, and migration, process data from these sources and output it to a centralized Data Staging Area. Following this, data and metadata are loaded into the Enterprise Data Warehouse and the centralized metadata repository. Once these are constituted, Data Marts are created from summarized data warehouse data and metadata.

What is Bottom-up architecture of Warehouse development?

The second data warehousing systems architecture, the "Bottom-up" architecture became popular because

the Top-down architecture took too long to implement, was often politically unacceptable,

and was too expensive.

What is Normalization, First Normal Form, Second Normal Form , Third Normal Form?

1.Normalization is process for assigning attributes to entities–Reduces data redundancies–

Helps eliminate data anomalies–Produces controlled redundancies to link tables

2.Normalization is the analysis of functional dependency between attributes /

data items of user views,It reduces a complex user view to a set of small and stable subgroups of fields / relations

1NF: Repeating groups must be eliminated, Dependencies can be identified,

All key attributes defined,No repeating groups in table

2NF: The Table is already in1NF, includes no partial dependencies–

No attribute dependent on a portion of primary key, Still possible to exhibit transitive dependency,

Attributes may be functionally dependent on non-key attributes.

3NF: The Table is already in 2NF, Contains no transitive dependencies

Why do we need a different schema for Data warehousing/Business Intelligence?

A data warehouse stores historical data that is collected from various sources and the data is time variant.

The data warehouse is updated in a scheduled and controlled manner. An online system usually does not

store historical data, however if history data is stored it would pose severe performance issues,

further being an Online environment 2 users accessing the same set of information are

likely to derive different results. An online system would provide data only from a single source,

whereas a data warehouse usually has data being put in from various sources.

What are the various features of Enterprise Data Warehouse?

The various features of an EDW are:

An EDW may or may not interact with Data Mart. The most important aspect of any

Data Warehouse is the consistency of information.

An EDW may have same level of granularity as a Data Mart.

This means that the data from the Data Mart will be dumped into an EDW.

An EDW may have more detailed level of data than a Data Mart.

This means that the data requirement

is at a more granular level that may not be catered by the Data Mart.

What are the primary differences between the OBIEE 10g and 11g security models

2013-12-17T00:21:00.002-08:00

What are the primary differences between the OBIEE 10g and 11g security models and what happens during upgrade?

Security Task/Object	OBIEE 10g	OBIEE 11g	What happens during upgrade from 10g to 11g?
Define Users and Groups in RPD file using OBIEE Admin Tool	Default	N/A. By default, users are defined in embedded WLS LDAP via FMW EM Console, or alternatively, in external LDAP.	By default, existing users and groups migrated to embedded WLS LDAP. Existing groups are automatically mapped to an Application role.
Defining security policies	Policies in the catalog and repository can be defined to reference groups within a directory.	Policies are defined in terms of application roles, which map to users and groups in a directory.	10g catalog groups are automatically migrated in the upgraded catalog and assigned the same privileges, access and membership.
“Administrator” user	Unique user with full administrative privileges.	No single user named tor full administrative privileges. Administration can be performed by any user who is member of BIAdministrators group.	“Administrator” user automatically added as member of “BIAdministrators” group in embedded WLS LDAP and granted Administrator role. The user specified during OBIEE 11g installation (i.e. “weblogic”, “biadmin”) is also a member of the BIAdministrators group.
Repository Encryption	Available on sensitive elements only - i.e. user passwords, connection pool passwords, etc.	Entire RPD encrypted via a password.	Prompted to set a repository password while running the upgrade assistant. Do not lose this password as there is no feature to recover a lost password.
External Authentication and OBIEE Initialization (Init) Blocks	Init blocks are requiredfor external PDAP or external table authentication.	Init blocks not required for WLS embedded LDAP. Init blocks are required for external LDAP or external table authentication.	Upgraded RPD will continue to point to 10g LDAP or external tables. Initblocks may need to be modified to ensure that depreciated, or reserved word, variable names are renamed. NOTE: If you intend to use another LDAP server, such at Oracle Identity Management (OID), then you must upgrade to the embedded LDAP server. Please see Upgrade Guide for further details.
Catalog Groups	Defined in Presentation Server Administration link	Available for backward compatibility. Use of Application Roles in FMW EM Console recommended.	Existing groups will be migrated. Recommendation is to use application roles instead. Privileges on catalog objects may be granted to an application role via BI Presentation server Administration link.
SA System Subject Area	Optional	Available for backward compatibility and requires init blocks and external tables. Use of Embedded LDAP is recommended.	Upgraded 10g RPD will point to external tables, Initblocks may need to be modified to ensure that depreciated, or reserved word, variable names are renamed.
“Everyone” Presentation Server Group	Default	Replaced with AuthenticatedUser role.	“Everyone” group migrated to AuthenticatedUser role.

DAC FAQ...

2013-12-11T04:59:00.000-08:00

Oracle DAC is an essential part of BI Apps, which is seldom being introduced in a systematic training course, although we use them all the time. There can be quite a lot of things to ask about when it comes to working with DAC, especially during interviews for BI Apps related projects. So I am going to gather some of the common interview questions with regard to DAC.

1. Name some of the DAC source system parameters:
TYPE2_FLAG, GLOBOL1_CURR_CODE, Initial_extract_date etc.. (The goal is just to name a few and of course, nobody remembers exactly the spelling)

2. To configure for initial full load, what are the things that needs to be done:
A, in DAC, set the value for initial_extract_date to avoid loading way too many data into target
B, to load base table W_DAY_D, nullify all of the refresh date to enable to full load. Do the same for all other aggregated time table like W_Week_D etc. At each task level where day dimension is being part of (SIL_daydimension), set the $$start date and $$end date parameter values at the task level to determine how long period your day dimension should store.
C. If your company does have multiple currency, then you need to configure currency in DAC by assigning currency code and exchange rate to DAC parameters like globol1 (2,3)_curr_code and globol1 (2,3)_curr_rate_type. BI Apps support up to 3 types of currency.
D. Configure GL Hierarchy so the info stores in W_Hierarchy_D. No DAC configuration needed
E. DATASOURCE_NUM_ID is a DAC parameters that determine which datasource system the extraction is taking place. In physical data source tab under 'setup' view, this field can be edited with integer number from 1 to 10 to represent different DB source.

3. Explain how to set up metadata in DAC to load data into the target

For basic intro on how DAC work in terms of executing the tasks, find out here

4. How to configure incremental loading in DAC
A. The refresh date under physical data source stores the last ETL run time, by nullifying this, the DAC will run full load or it will run incremental load based on the refresh date value.
B. Under task, there is 'incremental load' commend, by checking this, it will do either full load or incremental load regardless of refresh date.

-------------------------------------------------------------------------------

Below are the list of questions about DAC found through googling, since these questions have NOT been provided with answers, I have provided my answers, feel free to read it for your reference:

1. Over all architecture of DAC ?
DAC server and DAC Client. They must co-locate with Informatica Integration service, repository service and Informatica repository

2. Why we should use DAC and not control all execution through informatica ?
For better performance management, such as creating index, dropping index, truncating before load. Without DAC a custom ETL process will be needed, which has to survive the upgrate

3. Can we run multiple execution plan at the same time in DAC ?
Yes. only if the execution plan are not loading into the same table or using the same phyiscal table source

4. Explain DAC export/import
A way to import or export DAC repository metadata for upgrade or backup. Logica, System, runtime objects can be import/export

5. Have you change any of the DAC parameters ? If so which one and why ?
You have to understand what are the DAC parameters and the purpose of each. For example, Initial_extract_date can be modified when configure for initial full load, so the value for initial extract date will be used to filter out records from the source that are older than this date.

6. How do you Determine the Informatica Server Maximum Sessions Parameter Setting in DAC?
One you register informatica server in Dac client

7. Can dac send an email just in case of any failures ?
In DAC Client, toolbar, click email recipient, then in Tools--> DAC Server setup, Email configuration

8. Can you execute the sql scrip through DAC ? If yes how ?

Yes, at task level, in execution type, select SQL file. As a bonus to this answer, this article explains how to run store procedures in DAC.

9. in DAC How you can disable table indexes before loading and enable the index once load is complete ?
Just drop and recreate index

10.Let say you are running the normal incremental load. But just for today you want to extract data from AP_INVOCIES_ALL from 12/12/2011? How you can achieve this ?

Modify the refresh date to be 12/12/2011

11.How DAC Determines the Order of Task Execution within an Execution Plan ?
Based on tasks source/target table, Task phase (extract dim, load fact etc) and 'truncate always' properties, to run them in particular order, create task group

12.What are Micro ETL Execution Plans ? How can you Build and run them ?

According to Oracle document:
Micro ETL execution plans are ETL processes that you schedule at very frequent intervals, such as hourly or half-hourly. They usually handle small subject areas or subsets of larger subject areas. The DAC tracks refresh dates for tables in micro ETL execution plans separately from other execution plans and uses these refresh dates in the change capture process.

in design -- subject areas, create copy of subject area, inactive the unwanted tasks and create new execution plan for this subject area

13.From you past experience – explain scenario where Micro ETL Execution Plans produced wrong results on reports?

According to Oracle Document:
CAUTION: Micro ETL processes can cause issues with data inconsistencies, data availability, and additional load on the transactional database. Therefore, you should consider the following factors before implementing a micro ETL process:

For related star schemas, if one schema is omitted from a micro ETL execution plan, the cross-star reports may be inaccurate. For example, if the Person fact table is refreshed more frequently than the Revenue fact table, a report that spans the Person and Revenue dimensional schemas may produce inconsistent results.
If you omit dimension tables from a micro ETL execution plan, the foreign keys for the fact tables will point to Unspecified rows for the new dimension records. The foreign key references will be resolved when the Complete ETL execution plan is run, but users of the reports should be aware of such inconsistencies.
If you do not include aggregate tables in micro ETL execution plans, the reports that use data from these tables will be inconsistent with the reports that use data from the detailed fact tables. However, if aggregate tables are included in the micro ETL execution plan, the aggregate calculations are performed for each ETL process, which will take a constant amount of time and may be inefficient to perform at such frequent intervals.
Hierarchy tables are rebuilt during every ETL execution plan by querying the base dimension tables. This operation takes a constant amount of time. If the base tables are big, this operation may take a long time and may be inefficient if the micro ETL execution plan runs several times a day. However, if you avoid populating the hierarchy tables during micro ETL processes, data inconsistencies will occur.
With micro ETL execution plans, caching will occur more frequently, which may have performance implications.
Micro ETL execution plans will put more load on the transactional database because of the frequent extracts.

14. Let say you can not use DAC scheduler to schedule you execution plan. What other options do you have ? How you can achieve this ?

Use Informatica scheduler.

15.Does DAC keeps track of refresh dates for all the source/target tables ?

According to Oracle Document:
Refresh dates are tracked only for tables that are either a primary source or a primary target on tasks in a completed run of an execution plan. The DAC runs the full load command for tasks on which a table is a primary source or target if the refresh date against the table is null. When there are multiple primary sources, the earliest of the refresh dates will trigger a full load or an incremental load. If any one of the primary source tables has no refresh date, then the DAC will run the full load command.

16.Consider the scenario as below for task T1
Primary Source has not null last refresh date
Primary Target has null last refresh date
Will task T1 executes in full or incremental ?

Based on answers provided from question 15, what do you think?

17.Explain the upgrade/merge options for DAC 7.8.4 & below and new versions ?

Use upgrade/merge wizzard.
1. Repository Upgrade (DAC 784) --- upgrade Dac
2. Refresh Base --- For upgrading BI Apps
3. Simplified Refresh From Base -- This option is similar to the Refresh Base option. It allows you to upgrade the DAC Repository from an older release of Oracle BI Applications to a new release without comparing repositories and creating a Difference Report.
4. Replace Base --- Upgrade when phasing out older transaction system to newer one
5. Peer to Peer Merge --- Mergre different DAC instance of repository

18. Using DAC command line – write a script to check weather informatica services are up or not ?

use dacCmdLine InformaticaStatus. Below is the list of all commend lines according to Oracle:

19.Can we have two DAC server on the same machine ?
You can run two DAC servers on the same machine as long as they are listening on different ports and pointing to two different repositories

20.Explain briefly What kind of DAC Repository Objects Held in Source System Containers ?

Subject Areas -- A logical grouping of tables related to a particular subject or application context. It also includes the tasks that are associated with the tables, as well as the tasks required to load the tables. Subject areas are assigned to execution plans, which can be scheduled for full or incremental loads

tables -- Phsyical tables in DB

Indexes -- Just like your physical DB indexes

Tasks -- Unit of work for loading tables

Task groups ---- Grouping of tasks that can be bundled to run as a group

Execution plans -- A data transformation plans defined on subject areas that needs to be transformed at certain frequencies of time

Schedules -- Determine how often execution plan runs.

21.What is Authentication file ? If you have dac client installed can you access DAC repository without Authentication file ?

According to Oracle Document:
When you configure a connection to the DAC Repository, the configuration process includes creating a new authentication file or selecting an existing authentication file. The authentication file authenticates the database in which the repository resides. If you create a new authentication file, you will specify the table owner and password for the database.

22.Explain Index, Table and Task Actions in DAC ?

According to Oracle Document:
Index action: Override the default behavior for dropping and creating indexes

Table action: Override the default behavior for truncating and analyzing tables

Task action: Can add new functionality of task behavior, such as precedinf action, success action, failure action, upon failure restart

23.How DAC Handles Parameters at Runtime ?

According to Oracle Document:
During an ETL execution, DAC reads and evaluates all parameters associated with that ETL run, including static and runtime parameters defined in DAC, parameters held in flat files, and parameters defined externally to DAC. DAC consolidates all the parameters for the ETL run, deduplicates any redundant parameters, and then creates an individual parameter file for each Informatica session. This file contains the evaluated name-value pairs for all parameters, both static and runtime, for each workflow that DAC executes. The parameter file contains a section for each session under a workflow. DAC determines the sessions under a workflow during runtime by using the Informatica pmrep function ListObjectDependencies.

The naming convention for the parameter file is

....txt

DAC writes this file to a location specified in the DAC system property InformaticaParameterFileLocation. The location specified by the property InformaticaParameterFileLocation must be the same as the location specified by the Informatica parameter property $PMSourcefileDir.

24. How DAC Determines Tasks Required for any given subject area ?

According to Oracle Document:
You define a subject area by specifying a fact table or set of fact tables to be the central table or tables in the subject area. When a subject area is defined, DAC performs the following process to determine the relevant tasks:

DAC identifies the dimension tables associated with the facts and adds these tables to the subject area.

DAC identifies the related tables, such as aggregates, associated with the fact or dimension tables and adds them to the subject area definition.

DAC identifies the tasks for which the dimension and fact tables listed in the two processes above are targets tables and adds these tasks into the subject area.

Tasks that DAC automatically assigns to a subject area are indicated with the Autogenerated flag (in the Tasks subtab of the Subject Areas tab).

You can inactivate a task from participating in a subject area by selecting the Inactive check box (in the Tasks subtab of the Subject Areas tab). When the Inactive check box is selected, the task remains inactive even if you reassemble the subject area.

You can also remove a task from a subject area using the Add/Remove command in the Tasks subtab of the subject Areas tab, but when you remove a task it is only removed from the subject area until you reassemble the subject area.

DAC identifies the source tables for the tasks identified in the previous process and adds these tables to the subject area.

DAC performs this process recursively until all necessary tasks have been added to the subject area. A task is added to the subject area only once, even if it is associated with several tables in the subject area. DAC then expands or trims the total number of tasks based on the configuration rules, which are defined as configuration tags. This process can be resource intensive because DAC loads all of the objects in the source system container into memory before parsing.

25.Difference between Homogeneous and Heterogeneous execution plans.

According to Oracle Document:

Homogeneous

This type of execution plan extracts data from multiple instances of the same source system. For example, a business might have an instance of Oracle EBS 11i in one location and time zone and another instance of Oracle EBS 11i in another location and time zone. In such cases, the timing of data extraction from the different instances can be staggered to meet your business requirements.

Heterogeneous

This type of execution plan extracts data from one or more instances of dissimilar source systems. For example, a business might have an instance of Siebel 7.8 in one location, an instance of Oracle EBS 11i in another location, and a second instance of Oracle EBS 11i in yet a third location. You can also stagger the timing of data extraction when you use this type of execution plan.

OBIEE How to get started

2013-05-28T06:29:00.004-07:00

In this “sticky” article I want to give you some basic starting points.
Get familiar with the basic terminology:

http://en.wikipedia.org/wiki/Business_intelligence

Before you start with anything get up to speed with dimensional modelling.

http://www.kimballgroup.com/

(Get familiar with the works of Ralph Kimball)
Brush up your modelling capabilities, ADAPT is a good method to start with:

http://www.symcorp.com/downloads/ADAPT_white_paper.pdf

Read up on your Data Warehouse / BI basics:

http://blogs.oracle.com/bimadeez/2010/06/data_warehousing_architect_-_a_beginners_guide.html

Get a clean (virtual) machine and first install an Oracle database. The 10g express version is OK for starters.

http://www.oracle.com/technology/software/products/database/index.html

Maybe you want to brush up your DB skills:

http://www.oracle.com/technology/obe/11gr1_db/index.htm

Next get the latest version of OBIEE

http://www.oracle.com/technology/software/htdocs/devlic.html?url=/technology/software/products/ias/htdocs/101320bi.html

Download the documentation:

http://www.oracle.com/technology/documentation/bi_ee.html

If you have everything installed work trough the OBE tutorials on OBIEE.

http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/index.html

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

The Three Most Common Mistakes Made in Oracle BI Application projects

2013-05-18T22:40:00.004-07:00

1. Failing to follow the installation and configuration guides

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.

2. Developing dashboards without continuous user involvement

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

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:

Understanding the types of tables in Oracle Business Intelligence Data Warehouse

2013-05-18T22:39:00.000-07:00

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

Best Practice BI Admin tool

2013-05-05T03:32:00.003-07:00

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 .

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

2013-05-03T13:20:00.003-07:00

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

Lets Learn OBIEE & Oracle Fusion together................

R12 VS FUSION

Service-Oriented Architecture (SOA):

Overview on OBIEE11g Security Setup

overview on OBIEE11g Security Setup

OBIEE - Week First day and last day

OBIEE -Month Start Date and Month End Date

Developement Project Management stages and details

Upgrade or reimplementation or Implementation initiation

What is Difference between E-R Modeling and Dimentional Modeling

AIM -- Normal practice we do

useful XML publisher Report Syntaxes

useful XML publisher Report Syntaxes

The Three Most Common Mistakes Made in Oracle BI Application projects

The Three Most Common Mistakes Made in Oracle BI Application projects

1. Failing to follow the installation and configuration guides

2. Developing dashboards without continuous user involvement

3. Implementing the RPD without modification

Conclusion:

(ROI) Return on Investment for Business Intelligence

Fundamental of Data and so called Big Data

Simple Definition and Context

Big Data within Organizations

So why do successful IT implementations fail to achieve desired results?

Art of BI RD

R12: Master Troubleshooting Guide for vendor Payment for Oracle Payables

1. What is a Payment Process Request (PPR), and How Are PPRs Created and Managed?

What is a PPR?

How is a new PPR submitted?

Modifying Payment Process Requests

Terminating Payment Instructions

Stopping & Voiding Completed Payments

PPR Templates

Scheduling PPRs using PPR Templates

Required Setups

BUILD PAYMENTS

Create Payments(Code: IBY_PAYGROUP_PUB): The validated documents are then grouped into "proposed" payments based on the grouping rules - both User-Defined and hard-coded. It then numbers the proposed payments with an internal identifier (not "the" check number) and validates the payments.

FORMAT PAYMENTS

CONFIRM PAYMENTS

3. PPR Reports: There are several reports related to PPR processing

5. Alternatives to using Payment Process Requests

Batch Payment STATUSES In R12

PPR Process STATUSES and What They Mean

Data mart and dwh

Datawarehouse and DataMart:Definition & Difference

DWH Questions

What are the primary differences between the OBIEE 10g and 11g security models

DAC FAQ...

OBIEE How to get started

The Three Most Common Mistakes Made in Oracle BI Application projects

1. Failing to follow the installation and configuration guides

2. Developing dashboards without continuous user involvement

3. Implementing the RPD without modification

Conclusion:

Understanding the types of tables in Oracle Business Intelligence Data Warehouse

Best Practice BI Admin tool

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

Create Payments
(Code: IBY_PAYGROUP_PUB): The validated documents are then grouped into "proposed" payments based on the grouping rules - both User-Defined and hard-coded. It then numbers the proposed payments with an internal identifier (not "the" check number) and validates the payments.