Nandoo's Blog

Visualizaton in OBIEE11g - eseminar

2011-02-19T11:52:00.001Z

Oracle Business Intelligence Blog: Replay of eseminar - Visualizations in OBIEE 11g

ORA-00845: MEMORY_TARGET

2010-07-22T20:28:00.004Z

I have installed Oracle11g on my laptop and have installed the database as well with mostly default parameters.

After installing the database, I started DBCA configure the database and as soon as I selected the database on DBA Config menu, I received the message "ORA-00845:MEMORY_TARGET Not supported on this system".

After googling for this error, came to know that this has something to do howmuch shared memory is allocated at OS level and the values set for MEMORY_TARGET at the database level for Automatic Memory Management.

when I checked for these two, my initial setting was like this -

[root@KirtiBIVM bin]# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
38471112 15607268 20878104 43% /
/dev/sda1 101086 12112 83755 13% /boot
tmpfs 517552 270252 247300 53% /dev/shm

whereas my MEMORY_TARGET and MEMORY_MAX_TARGET are set as 404m.

I changed the shared memory at OS level by using the command -

[root@KirtiBIVM bin]# mount -t tmpfs shmfs -o size=3G /dev/shm

after which, the details are -

[oracle@KirtiBIVM bin]$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
38471112 15608440 20876932 43% /
/dev/sda1 101086 12112 83755 13% /boot
tmpfs 3145728 620932 2524796 20% /dev/shm
shmfs 3145728 620932 2524796 20% /dev/shm
[oracle@KirtiBIVM bin]$

After this, I have amended the parameters MEMORY_TARGET and MEMORY_MAX_TARGET giving following commands -

logged in as SYS

ALTER SYTSEM SET MEMORY_MAX_TARGET=2G SCOPE=SPFILE;

ALTER SYSTEM SET MEMORY_TARGET=1G SCOPE=SPFILE;

Restarted the database as -

SHUTDOWN IMMEDIATE;

STARTUP;

And this has resolved the issue.

Oracle BI Intelligence 11g ( OBI 11g) Launch 07/07/2010

2010-07-07T17:13:00.004Z

I took time to-day to attend the launch event of much awaited OBIEE11g. After seeing the demos and presentations it is worth waiting for.

The OBI 11g release is total revamp of what we see currently on 10g release. For me, the most important bits are -

Server Adminstation
====================

- The OBI Server can be managed through the Enterprise Manager, systems management & deployment are much easier.
- Deployment to Production can be achieved from the Admin Tool ( GUI), I heard of version controlling as well. But not sure.
- Allows incremental deployments to Repository
- When OBI is installed it comes with preconfigured Scheduler (no need to do additional configurations as currently in 10g).
- Upgrade utility from 10g to 11g (Yes, really!!)
- Installation is much easier using OUI.
- Supports Relational modelling as well.

and much more.

Presentation Services
=====================

wow! where to start. the Dashboards are really Interactive, it really means.
- The Pivot Tables can be easily altered - drag and drops (like discoverer) to alter the view of the Pivot tables on the fly (not through design layer as in 10g)
- drilling ups and downs in the same page, similar to Discoverer
- The bit that stands out is " Chart Sliding" which provides a slider for Dashboard prompt values, and the user can travel through each value of the prompt and the chart changes accordingly. That was really cool.
- Chart Zooming (In / Out)
- Global filters, filters can be applied dynamically and if want for all the charts / reports on a single dashboard page.

Most of the interactive features are like as in Discoverer 10g, but the comparison just ends there, OBI 11g offers much more.

- ScoreCarding is really great! and is integrated into OBI.

Look & Feel
===========
- Look and feel is really good, much revamped and very intuitive.
- Search facility in the BI Catalog (Yes! it is).
- the presentation is much better

BI Publisher
============
The current version of BIP put me off, as the templates need to be designed from MS Office products and then use it in BIP etc etc.....

The new version allows to design the templates from BIP itself. I thought that is quite cool.

All in all, with the 11g release most of the bits can be achieved from single unitifed interface, rather than jumping back and forth between various interfaces.

Personally I can not wait to get my hands on the new version.

OWB11gR2 - SQL Server Connection

2010-06-24T14:02:00.014Z

After installing the Oracle11gR2 , started exploring the OWB11gR2. First thing I attempted is to establish a connection SQL Server as it was one of the requirements with my current client.

The steps that I have followed for this is -

1. Downloaded the SQL Server JDBC Driver from
here.
2. Install the ( unzip) the SQL Server JDBC Driver into /owb/lib/ext folder.
3. This will create sqljdbc_2.0 under the above directory.
4. I have manually copied the sqljdbc.jar from /owb/lib/ext/sqljdbc_2.0/enu folder onto /owb/lib/ext. do not ask why I did that? OWB11gR2 started recognising the driver only after I placed the jar file straight under "ext".
5. Shutdown the OWB services by running "ccashut".
6. Start the OWB services by running "ccastart".
7. Started OWB11gR2 client.
8. Created a new SQL Server Module as shown below -
Details
=======

Advanced
=========

Tested the connection successfully! There you go!!!! Bob's your uncle!!!!

Oracle DB11gR2 Installation on Enterprise Linux 5 - missing RPMs

2010-05-31T14:18:00.012Z

While I was trying to install Oracle 11gR2 on my laptop I stumbled across missing RPMs.

My environment
================
Dell Laptop with Windows Vista

Installation
==================

Downloaded and installed Enterprise Linux 5 from Oracle Edelivery site.

The initial attempt to install the Oracle DB11gR2 resulted in the warnings that some of the required RPMs are not installted.

I tried various google results to install missing RPMs. The best solution that I found required the following ( thanks to original post).

On Linux do the following -

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-el5.repo

The above will download the public-yum-el5.repo package into /etc/yum.repos.d

After this edit the file to set the enabled flag to 1 for the version of EL you are using.

Now using the above tool you can download and install any missing rpms.

# yum install unixODBC

# yum install unixODBC-devel
# yum install sysstat
# yum install libaio-0.3.106
# yum install libaio-devel-0.3.106

Using the above I had installed all the missing RPMs.
===============================================================================
Update 20/06/2010
=================
Post Installation:
==================

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=orcl ( make sure this matches the SID otherwise you might get LRM-00109 error when attempt to startup the database)
export PATH=$ORACLE_HOME/bin:$PATH

If you do not perform the above tasks, the SQLPLUS would not start and any attempt would throw up the error -
"sqlplus can not execute binary file".

Dashboard Prompt constraining the results

2010-04-15T18:38:00.005Z

In one of our dashboards, we had to use a dashboard prompt to allow users to pick a value and display the report accordingly.

The prompt in question is - Year Name and is part of our Time Dimension. The Fact table is properly connected to the Time Dimension.

When I dropped the TimeDim.YearName in to the Dashboard prompt, I was expecting the results to be constrained for those records in the Fact Table. Let me explain,

The Time Dimension has data ranging from 1990 through to 2099.

The Fact table has data starting from 2001. If i write a SQL selecting TimeDim.YearName, joining Fact to Time Dimension it will only show distinct years from 2001.

But in OBIEE, when I execute the prompt it was listing all the available Years (distinct) in Time Dimension ie 1990 through to 2099. This is not what I was expecting.

After mulling over different things, I finally got it working but I am not sure that is the way. What I done is -

In the RPD, in the BMM, I explicitly specified both TimeDim and Fact table as Sources, and explicitly created the Join condition. Once I have done this, the results are as I was expecting.

Please feel free to comment if I had missed anything obvious.

Adding Foreign Key Constraints using OMB+

2010-04-13T13:17:00.005Z

To add a new foreign key to an existing table using OMB+, the syntax is

For Eg: Sales_Fact has a column Order_Date_Key and a new FK needs to be created. This Order_Date_Key is populated with the PK of Time_Dim. To implement this, the OMB+ syntaxt is -

OMBALTER TABLE 'SALES_FACT' ADD FOREIGN_KEY 'FK_SALFACT_TIME_DIM' \
SET REF COLUMMNS ('ORDER_DATE_KEY') \
SET REFERENCE PRIMARY_KEY 'DIMENSION_KEY' OF TABLE 'TIME_DIM'.

OBIEE Answers - Pivot Table / Chart Horizontal Layout - Multiline

2010-02-01T19:22:00.011Z

At the current client site, we had a requirement to show PIE charts one for each section. As we all know, by default OBIEE presents the sections vertically. If the number of sections is 3 or less this layout would look nice, but what if there are more sections, scrolling down vertically would not appeal to the end users to get a comparative view.

Joe Bertram had posted Javascript to present the sections horizontally and I thought we could use this script to rotate the PIE charts horizontally. We have duely lifted his script and use in ours and the result looked like below.

Which is not appealing as the users have to scroll across, so I thought there must be a way to present this on multiple rows with limited columns per rows. Am no expert on Java scripting, so another cry for help!!

This time another OTN Member John Minkjan replied with a variant of original script by Joe and the final desired output which looked like below.

.
Thanks to Joe & John for their effort.

Snow! Snow!

2009-12-18T18:44:00.014Z

Last night, we had high snow fall here in England, the view was so wonderful.

OWB 11g Getting Started Book

2009-11-01T19:30:00.004Z

I have been sent the book, Oracle Warehouse Builder 11g Getting Started by Bob Greisemer (Packt Publishing, ISBN 978-1-847195-74-6), to be reviewed.

I did a quick browse through the book and my initial impression is that this is a good reference to any body who wants to know how to use the OWB as a tool. This book talks about the OWB11gR2 version and has detailed steps, with pictures, how to get started with the tool. What this book does not do is teach you any Data Warehouse concepts or any thing about Dimensional Modelling etc.

In a nut shell, this book provides a good starting point to get to know about the tool.

2009-07-01T16:26:00.052Z

Oracle Application Express – End User Change Password Procedure

We have several applications built using Oracle Application Express, and are accessed by several end users. The Authentication mechanism that we have adopted is – “Database Account” at the global level and within each application, the access mode is set to “Restricted Access” and the users are listed within the application who needs access to it.

So every time a new user needs an access, or when the user forgets their password we had to reset the password and allow the user to change the password from the application. The procedure that I have adopted is split into 3 stages as detailed below.

STAGE 1 - Database Activities

For the end user to change their password they need “ALTER USER” privilege. We have created roles to control various privileges to the Application Express users and then granted the “ALTER USER” privilege to the role, the role is then assigned to the Application Express End Users.

Create a PLSQL procedure that performs the password validation & changing passwords for the End users.
The PLSQL procedure is defined as below –

CREATE OR REPLACE PACKAGE BODY BCFHPTL.APEX_UTILS_PKG
AS
PROCEDURE CHANGE_DB_PASSWORD_APEX (in_user IN VARCHAR2,
in_cur_pwd IN VARCHAR2,
in_new_pwd IN VARCHAR2)
AS
passwords_mismatch exception;
l_proc_name VARCHAR2 (200)
:= 'APEX_UTILS_PKG.CHANGE_DB_PASSWORD_APEX' ;
l_cur_db_pwd VARCHAR2 (200) DEFAULT NULL ;
l_new_db_pwd VARCHAR2 (200) DEFAULT NULL ;

BEGIN
--dbms_output.put_line ('1 'in_user'-'in_new_pwd'-'in_cur_pwd);
IF (in_user IS NOT NULL AND in_cur_pwd IS NOT NULL AND in_new_pwd IS NOT NULL )
THEN
-- dbms_output.put_line ('2 'in_user'-'in_new_pwd'-'in_cur_pwd);
-- Get the current password value from database
EXECUTE IMMEDIATE 'SELECT PASSWORD FROM SYS.DBA_USERS WHERE USERNAME = UPPER(:username) '
INTO l_cur_db_pwd
USING in_user;
--dbms_output.put_line('Current db password is 'l_cur_db_pwd);
-- Change password as specified by user for "Existing Password"
EXECUTE IMMEDIATE 'ALTER USER '
in_user
' IDENTIFIED BY '
in_cur_pwd
' ACCOUNT UNLOCK ';

-- Retrieve the password value for Existing password
EXECUTE IMMEDIATE 'SELECT PASSWORD FROM SYS.DBA_USERS WHERE USERNAME = UPPER(:username) '
INTO l_new_db_pwd
USING in_user;
--dbms_output.put_line('Old password specified value is 'l_new_db_pwd);
IF l_cur_db_pwd != l_new_db_pwd
THEN
RAISE passwords_mismatch;
ELSE
-- dbms_output.put_line (in_user'-'in_new_pwd);
EXECUTE IMMEDIATE 'ALTER USER '
in_user
' IDENTIFIED BY '
in_new_pwd
' ACCOUNT UNLOCK ';
END IF;
ELSE
RAISE_APPLICATION_ERROR (
-20001,
'Error in ' l_proc_name ' - ' 'Username, Old Password, New Password and Confirm Password Must be Present'
);
END IF;
EXCEPTION
WHEN passwords_mismatch then
EXECUTE IMMEDIATE 'ALTER USER '
in_user
' IDENTIFIED BY VALUES '''
l_cur_db_pwd
''' ACCOUNT UNLOCK ';
RAISE_APPLICATION_ERROR (
-20005,
'Error in ' l_proc_name 'Old Password specified does not match the value in Database'
);
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR (
-20006,
'Error in ' l_proc_name SUBSTR (SQLERRM, 1, 132)
);
END CHANGE_DB_PASSWORD_APEX;
END APEX_UTILS_PKG;
/

STAGE 2 - Oracle Application Exporess

Create a global application, which accepts the username, current password ( which will be a default password from DBA’s) , new password , confirm new password. This application then executes a PL/SQL block to change the password.

First created an application which looks like this –

Create a new application with just a login page in it.

Inside the application, the following are defined in the Login Page.

ITEMS

P101_USERNAME - capture UserName

P101_X_OLD_PWD - capture Old Password declared as “Password”

P101_X_NEW_PWD - capture New Password declared as “Password”

P101_X_CNF_PWD - Capture Confirm New Password.

BUTTONS

Two buttons are added.

CANCEL - Close this application and redirects to the application from which the “Change Password” application has been called from.

SUBMIT - Submits the application and alters the User Password.

The details are as shown below.

VALIDATIONS

A validation process has been created to verify whether the values specified in the fields P101_X_NEW_PWD and P101_X_CNF_PWD are same, if not throws an error message.

The PLSQL block contains the following code to validate the inputs by user-

BEGIN
IF :p101_x_new_password = :p101_x_confirm_password
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;

If the above routine throws an error then the following message will be displayed –Both New and confirm Password fields must match.

The details of the validation process are as below –

PROCESSES

An “after Submit” process has been created which calls a database procedure to change the passwords.

This process has the following PLSQL block –

The implementation of the validation process is as shown below -

TCL Script to Deploy OWB Mappings from file

2009-06-26T10:24:00.003Z

This is another TCL script, which enables the developers to deploy OWB Mappings. The main function of this script is to deploy the mappings, with the list of the mappings to be deployed specified in a file.

This has been tested for OWB11g version.

puts " ******************* READ ME ********************"
puts ""
puts ""
puts " The proc owb_deploy_map_4m_file is Created"
puts " To execute the proc call the above proc name at OMB+ command prompt "
puts " DISCONNECT FROM THE DESIGN REPOSITORY BEFORE CALLING THE PROC"
puts ""
puts ""
puts " IF THIS IS THE FIRST TIME THE OBJECTS ARE BEING DEPLOYED"
puts " INTO THIS RUNTIME ENVIRONMENT"
puts " !!!!!!!!MAKE SURE THE LOCATIONS ARE REGISTERED!!!!!!!"
puts ""
puts ""
puts "**************************************************************"
puts ""
puts " This procedure reads the mappings from the file"
puts " Name & location of the file : C:/tmp/mappings_2_deploy.txt "
puts " "
puts "**************************************************************"
puts ""
puts ""
puts "Usage:"
puts "=============================================================="
puts ""
puts " owb_deploy_map_4m_file owbowner Password host port service ModuleName"
puts "=============================================================="
puts ""
puts ""
puts "**************************************************************"
#
proc owb_deploy_map_4m_file { desuser despwd host port srvc module } {
set fname [ open "c:/tmp/deploy_maps_4m_file.log" w]
puts $fname "----------------------------------------------------------"
puts $fname "Connecting to the Design Repository "
puts $fname "----------------------------------------------------------"
OMBCONNECT $desuser/$despwd@$host:$port:$srvc USE WORKSPACE 'ZZZZZ'
set projList [ OMBLIST PROJECTS '*']
foreach projName $projList {
OMBCC '$projName'
set projcon [OMBDCC]
puts $fname "current project context is $projcon"
puts $fname "CONNECTING TO RUNTIME REPOSITORY"
puts $fname "----------------------------------------------------------"
OMBCONNECT CONTROL_CENTER
#
set ModList [OMBLIST ORACLE_MODULES '$module' ]
set i 1
#
foreach ModName $ModList {
puts $fname " Working on : $ModName"
puts "$ModName"
OMBCC '$ModName'
set curcon [OMBDCC]
set mapList [OMBLIST MAPPINGS]
set j 1
#
#foreach mapName $mapList
set fhandle [open "C:/tmp/mappings_2_deploy.txt" r]
puts $fhandle
while {-1 != [gets $fhandle line]} {
set mapName $line
puts "Deploying ..........$mapName"
puts $fname " Creating Delpoyment Action Plans for :$mapName"
#
# CREATE THE DEPLOYMENT ACTIONS PLAN FOR EACH MAPPING
#

OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP.$mapName'
OMBALTER DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP.$mapName' ADD ACTION '$mapName.CREATE' SET PROPERTIES(OPERATION) VALUES ('REPLACE') SET REFERENCE MAPPING '$mapName'
puts $fname " Executing the Deployment Action for MAPPINGS"
#
# DEPLOY MAPPINGS
#

OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP.$mapName'
OMBCOMMIT
#
# DROP THE DEPLOYMENT ACTIONS PLANS
#

OMBDROP DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP.$mapName'
OMBCOMMIT
incr j
}
close $fhandle
OMBCC '..'
set curcon [OMBDCC]
puts $fname "context at the end is --- $curcon"
incr i

}
OMBCOMMIT
OMBCC '/'

}
close $fname
OMBCOMMIT
OMBDISC
#
#**********************************************************************************************
# END OF PROC owb_deploy_map
#**********************************************************************************************
}

Passing Parameters from OBIEE Dashboards to BI Publisher Report

2009-06-12T15:19:00.024Z

I have been working on a client assignment to implement OBIEE and design and develop dashboards and get them up and running.

One particular requirement was to bring a BI Publisher report into a dashboard. The BI Publisher Report was developed much before the OBIEE came into limelight. The BI Publisher Report is scheduled to run everyday and email the report to certain group of users. Since we have been playing around to set the BI Dashboard and have one dashboard up and running for a specific business area, and so decided to extend this for other areas as well.

For one such area where we have the BI Publisher Report has been developed, rather than re-developing the report we have decided to bring the BI Publisher report into the dashboard.

The BI Publisher report is
-SQL Based Report
-Has 2 date parameters.

This exercise is broke down into 2 pieces, one create report in BIPublisher and second placing the BIP Report into Dashboard and creating Dashboard prompts to pass values into BIP report parameters.

BI Publisher

After creating the datasources and the required SQL to pull the data from underlying database objects,

Declare the date parameters that will be used in the SQL to constrain the data, as shown below -

The above declared parameters are used in the SQL that pulls data from underlying table, as shown below -

Save the changes and run the report by clickion on "view".
The results are as shown below in the report -
That looks great!!! the report shows the data as per the specified parameters.

OBIEE Dashboard

The next step is to place the above BI Publisher report into a dashboard and define dashboard prompts to pass values into the BI Publisher report.

The first step is to define the Dashboard prompts which allow the end-users to send the values to the BIP Report parameters. One important thing to rember while creating the Dashboard prompts is that, the Presentation variable MUST BE same as the parameters defined in the BI Publisher. The creation of Dashboard prompts is as shown below.

Once the dashboard prompts are defined, the next step is to add the BIP report into the dashboard.

First add a section to the Dashboard page, and then drag and drop the dashboard prompts created earlier, which can be seen on the left pane.

And then, add another section and add the BI Publisher Report using the "BI Publisher Report" Dashboard Objects as shown below.

Save your changes by clicking on "Save". Navigate to the dashboard page, and you should see the BI Publisher Report added to it.

There you go!! Your BI Publisher Report has been added to the Dashboard, and you can pass values to the report.

Oracle Business Intelligence - OBIEE - 101: OBIEE making a custom Log On and Log Off screen

2009-03-25T11:28:00.001Z

I have come across this post regarding the personalization of OBIEE logon screens. Thanks to the original contributor.

Oracle Business Intelligence - OBIEE - 101: OBIEE making a custom Log On and Log Off screen

Concatenation in OBIEE

2009-03-17T20:31:00.004Z

I have been playing around with the OBIEE repository for a demo project at current client assignment.

I have been trying to use the "where" clause to filter out the data for a column with the condition as - column1 = (variable1||' '||variable2||' '||variable3). I thought this would be straight forward one as in normal SQL and I could use OBI Concate operator, but turned out to be a tricky one.

I tried to do this column1||' '||column2||' '||column3, which did not work apparently the concatenation operator '||' only works if only two fields are used. For more than one has to use -
concat(concat(concat(variable1,' '),variable2),' ') .

I am glad I could resolve the issues.

Oracle Business Intelligence - OBIEE - 101: OBIEE Directing a user to a default dashboard

2009-02-27T13:37:00.001Z

Oracle Business Intelligence - OBIEE - 101: OBIEE Directing a user to a default dashboard

Apex 3.0.1 Installation

2007-11-12T22:12:00.000Z

It has been a while, since I have updated this blog.

I wanted to work with Application Express and so wanted to install it on my desktop. I thought it would be a cakewalk, the installation. But it took me 3 days to get things sorted.

My configuration

Windows XP SP2, Oracle EE 10.2.1.0.

The steps I followed for the installation are -

1. Downloaded the Apex software from here - ApexDownload.

2. Unzipped the contents onto C:\Apex.

3. Going through the installation guide, reminded me that I have to install HTTP Server.

4. Downloaded 10.2 Companion CD from - 10gCompanion

5. Installed Oracle HTTP Server from the pack downloaded in step4. During this step, I got an error OUI-10066. But Once I used the OUI from the previously installed oracle DB home, the error went away.

6. After successful installation, could not get the HTTP Server running. Received the error "NLS Initialization Failed". I had to edit the the PATH variable and got rid of the "OralceinstantClient" path and this made the trick. The HTTP Server is up and running.

7. Followed the installtion guide of Apex. Installed Apex.

8. Configured HTTP Server.

- Copied the images directory from C:\Apex to "HTTPServerHome\Apache".
- Edited the "dads.conf" file in "HTTPServerHome\Apache\modplsql\conf" with the follwing

Alias /i/ "HTTPServerHome/Apache/images/"
AddType text/xml xbl
AddType text/x-component htc
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString myhost:1521:orcl2 ServiceNameFormat
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword admin
Allow from all

The ones hi-lited in RED are the ones I have edited to reflect my environment.

8. Stopped and started the HTTP Server.

9. There you go, my APEX is running.

Hope this will help you to do the installation.

using WHILE Loop - OWB10gR2

2007-07-22T14:25:00.002Z

The diagram below details the usage of WHILE loop operator in OWB10gR2.

The requirement is to check for the existence of a file on the server at a specified location. If the file exists, then complete with success and then execute the mapping. If the file does not exists then to loop through a specified period of time an hour, checking for the arrival of file for every 1 minute, and to end with Error status if the file has not arrived in the specified time.

The way I have achived this task is -

1. Defined a variable "CHK_FILE_STATUS" under START activity.
2. Assign a value 0 to the variable "CHK_FILE_STATUS"

Assigning Value "0"

3. Then, in the while loop put a condition to loop for an hour.

4. Use the File_Exists condition and specify the full path for the file to be checked for.

5. From File_Exists operator Use two transitions.

6. If File_Exists returns "Exists" then branch to Success.

7. If File_Exists returns anything other than "Exists" then loop through the logic to increment the variable "CHK_FILE_EXISTS" by 1.

8. Wait for a 60 seconds (delay) before checking for the arrival of file.

9. The while condition will be evaluated and the "FILE_EXISTS" will be checked until the variable "CHK_FILE_EXISTS" reaches the count 61 ( 1 hours).

10. If the file arrives before the count reaches 61, the WHILE loop will be broken to END_SUCCESS otherwise after 60mins, the loop breaks to END_ERROR.

OMB+(Tcl) script for copying attributes between groups

2007-07-19T21:18:00.001Z

At the current client site am working on, the source tables have morethan 500 columns. And the mappings involved SET operators to process the data, and it was a pain to type in all the attributes in SET operators. so I have written the below TCL script which copies the attributes from one group to other in the same as order as defined in group1.

The script is -

puts "proc add_GRP is created"
puts "==================="
puts "Input Parameters:"
puts "======================="
puts "PROJECT MODULES MAP OPR_NAME SRCGRP TGTGRP"
puts "========================="
proc add_grp { PROJ MOD MAP OPRNAME SRCGRP TGTGRP } {

OMBCC '/$PROJ/$MOD'
set cc [ OMBDCC ]
set ccon [ lindex [ split $cc " "] 1 ]

puts $ccon

set atrlst [ OMBRETRIEVE MAPPING '$ccon/$MAP' OPERATOR '$OPRNAME' GROUP '$SRCGRP' GET ATTRIBUTES ]

foreach aname $atrlst {
set dt ""
set ln ""
set pr ""
set sc ""

set prop [ OMBRETRIEVE MAPPING '$ccon/$MAP' OPERATOR '$OPRNAME' GROUP '$SRCGRP' ATTRIBUTE '$aname' GET PROPERTIES (DATATYPE, LENGTH, PRECISION, SCALE) ]
#puts "$aname ---- $prop"

#splitting the above into individual var

set dt [ lindex [ split $prop " " ] 0 ]
set ln [ lindex [ split $prop " " ] 1 ]
set pr [ lindex [ split $prop " " ] 2 ]
set sc [ lindex [ split $prop " " ] 3 ]
#puts "$aname -------- $dt"

set tgtatrlst [ OMBRETRIEVE MAPPING '$ccon/$MAP' OPERATOR '$OPRNAME' GROUP '$TGTGRP' GET ATTRIBUTES ]

# check whether the attribute already exists

if { [lsearch $tgtatrlst $aname] == -1 } {
puts "Adding $aname"
OMBALTER MAPPING '$MAP' ADD ATTRIBUTE '$aname' OF GROUP '$TGTGRP' OF OPERATOR '$OPRNAME'
OMBCOMMIT
# change attribute properties

if { $dt == "NUMBER" } {
puts "Changing length and precision for $aname -------- FOR $dt "
OMBALTER MAPPING '$MAP' MODIFY ATTRIBUTE '$aname' OF GROUP '$TGTGRP' OF OPERATOR '$OPRNAME' SET PROPERTIES (PRECISION, SCALE) VALUES ($pr,$sc)
} else {
if { $dt == "DATE" } {
OMBALTER MAPPING '$MAP' MODIFY ATTRIBUTE '$aname' OF GROUP '$TGTGRP' OF OPERATOR '$OPRNAME' SET PROPERTIES (DATATYPE) VALUES ('$dt')
} else {
puts "changing for $aname -------- $dt"
OMBALTER MAPPING '$MAP' MODIFY ATTRIBUTE '$aname' OF GROUP '$TGTGRP' OF OPERATOR '$OPRNAME' SET PROPERTIES (DATATYPE,LENGTH) VALUES ('$dt',$ln)
OMBCOMMIT
}
}

}
}
}

OWB SQL*Loader Mapping

2007-05-30T21:48:00.000Z

In response to a quesion raised on OTN Forum, http://forums.oracle.com/forums/thread.jspa?threadID=514132&tstart=0

The OWB Mapping for SQL*Loader is -

FILE MODULE CREATION

Create a File Module, if you have already created goto 2.
To create the File Module navigate to - Project -> Files.
Right Click on the FilesNode, and create a new Module.
During this step, you need specify the "local directory" from the source files need to imported and sampled. Usually this will be on your desktop.

IMPORT THE SOURCE FILE

Now Import the file into the File Module just created.
To do this, right click on FileModuleName and Select "Import". While importing you can sample the source file to define the field names, data types and record structure.

here, you can specify if you want to skip any records from the beginning of the file

Here, you have to specify whether the file is a delimited / fixed record format. If delimited, choose the appropriate delimitor. The field "Fixed Delimitor" is editable ie if you have a delimitor which is not listed in the drop down box, you can just type the delimitor.

Here, you can specify the data types and any SQL*Loader conversion functions. Also, you have a check box to say if the first record in the file specifies the field names.

Click Next and Finish.

This completes the "Import and Sampling " of source files.

BUILD OWB MAPPING

Expand the Oracle Module, where the mapping need to be created. Right click onf "Mappings" node and select "Create Mapping". Build the mapping as per your requirements.

CONFIGURING THE OWB MAP

The things that are important for SQL*Loader mapping are

specifying the source data file.

To specify a logical name for the soure file, Select "Source Data File" tab and click on the [...] button, which opens up a pop-up window where you can give a name , and click on "Add" and then on "Ok" button.

Now, expand the "Source Data File" tab, and you should see an entry with the name as mentioned in previous step.
Expand the Source File and you should see entries to specify the

Data File Location - This is the Location Name pointing to the directory path where the source file is residing on server

Data File Name - Name of the source file on server, the name should exactly match as on the server.

Bad File Location & Name - specify the location pointing to the directory where you want the bad file to be created on server.

Discard File Location & Name - specify the location pointing to the directory where you want the discard file to be created on server.

Log file location and file name - specify the location pointing to the directory where you want the Log file to be created.
Errors Allowed - Specify the number of errors that can be allowed before the OWB mapping to fail.
Control File Location and Name - specify the location pointing to the directory where you want the Sql*Loader control file to be created.
Expand the tab "Source and Targets", expand your target table, expand "Sql*Loader Parameters" and change "Trailing Nullcols" to "True".

with all the above configuration settings, the window should be as

Close the above window, commit the changes , Register the locations ( by specifying the access parameters, the directory path on the server where the source file is residing )Deploy the mapping and Run the mapping.

If everything is right, you should load data from you source file into target table.

FlexFields

2007-05-29T16:33:00.000Z

Concepts

These are Configurable windows, which enables to tailor Oracle Apps to suit the business environment of a given organization.

The flexfields allows to modify or extend Oracle Apps without programming. These are used to

structure certain identifiers required by Oracle Apps to suit the business environment
collect and display additional information in Oracle Apps that may be useful for business.

The flexfields helps you to

configure applications to support accounting, product and other codes.
construct intelligent keys.
configure applications to capture additional data.
use the applications to validate the values and value combinations entered by end users.
support multiple field structures .

FlexField Types

There are 2 types of FlexFields

Key FlexFields (KFF)

Used to identify individual business entities or classes of entities.
These can also be used to reflect the organizational structure of a business .
The code combinations of a KFF are internally used by Oracle Apps to retrive information.
These are Mandatory to set-up in Apps but optional to use.
Made up of multiple segments, which are concatenated by using segment separators.
The combinations of multiple segments then forms intelligent keys which will be used of Oracle Apps to access identification information.
Value sets to specify acceptable values for each segment in a flexfield.
cross-validation rules for the allowable combinations of segment values within a key can also be defined.
Eg for built-in FlexFields in Oracle Apps are - AssetKey FlexField in Assets, ServiceItem FexField in Services, Territory FlexField in Receivables.
These are owned by one Application, but can be used by many applications.

Descriptive FlexFields (DFF)

Used to collect and display additional specialized business information that is not readily available in Apps.
These are Optional.
The information in a DFF is not used internally by Oracle Apps.
Allows multiple structures to be defined.
These are associated with tables in a specific application.

Key FlexField (KFF)

Benefits

Dynamic insertion of new values
cross-validation of segment value combinations
Define security rules on accessible values
Define aliases

Implementation Steps

Before implementing the Key FlexFields, the following needs to be met -

support for Multiple Structures

check whether Oracle Apps supports multiple segment structures.
identify the number of segments required.

Resource availability

determine the number of segment columns in "Code Combinations" table.

Type of Qualifiers
Requirement for dynamic inserts
Requirement for cross-validation
Requirement for short-hand flexfield entry
Required value sets for value checking
Value Security for segments.

A FlexField can be defined to contain a segment or structure or both. It may use more than one structure and each structure can have more than one segment.

The following steps need to be followed for FlexField implementation -

Plan FlexField's structure, layout, ValueSets and segments.
Define the ValueSets ( these are used to set the acceptable range of values for the flexfield segments).
Define the FlexField Structure and Segments.
Define the values for the ValueSets defined in step 2.
Define any Security Rules ( Key FlexFields only).
Define any Cross-Validation Rules ( Key FlexFields only).

ValueSets

A ValueSet

enables you to control the value for a segment
defines the approved values for entry or display by a flexfield segment
a single value set can be shared by multiple FlexFields.
a single value set can be shared by different segments of a FlexField.
A user-defined ValueSet with the same name as the pre-defined ValueSet (by Oracle Apps) will be over-ridden by the pre-defined one.

The Planning of value set requires the following -

basic structure of the set
validation rules that must be met ( like datatype, maxlength etc.)
most appropriate validation strategy

The values provided for a ValueSet can be -

List Of Values

is used when LongList feature is not required in Oracle Forms Applications
Used when the avaialable values are between 10 to 20.

Long List of Values

to provide LongList feature in Oracle Forms Applications
User must enter partial segment value before LOV option retrieves all the available values
Is not available for ValueSet with Validation type as NONE .
Is used where the available list of values is morethan 200.

Poplist

is used when LongList feature is not required for Oracle Forms Applications
Used when the available values are less than 10.

A ValueSet can have 3 types of Security features -

Hierarchial
Non-Hierarchial
No Security

ValueSet Types

There are 8 types of ValueSets. They are -

None

No approved list of values.
only performs minimal checking on the values entered.

Independent

contains a valid list of approved values.

performs cross-validation of values entered with the list of approved values, in addition to minimal checking.

Dependent

Associated with Independent ValueSet
makes sure that the entered values are associated with a value in the associated Independent ValueSet.

Table

Retrieves approved value list from an associated Apps table.

Special

provides entire flexfield as a value set for a single segment.
uses special validation routines and are not used for normal flexfield segments.

Pair

provides a range flexfield as a value set for a pair of segments.
uses special validation routines and are not used for normal flexfield segments.

Translatable Independent

provides a specified list of values for a segment
contains display values that can be translated into different languages.

Translatable Dependent

associated with Translatable Independent ValuSet.

Value Sets Creation

To create a Value Set, one need to plan data format validation, which are -

Format Type - Value data type
Maximum allowable size for value
Precision
Numbers only
UpperCase only
Right-justify and Zero fill
Max/Min Values - Beginning and ending values of a range

Key FlexField (KFF) Structure

A given Key FlexField structure comprises of multiple segments divided by segment separators. The segments contains the values from the code combinations that are used by OracleApps as an intelligent key to identify a particular business entity or class of entities.

Each flexfield stores its key combinations in a database table called, "Code Combinations". This table holds one column for each flexfield segment. A unique ID is associated with each row in the table, which is stored in a Unique ID column which acts as the PK.
Planning of a Key FlexField involves -

Identifying the Target FlexField.
Plan the structure, appearance and behaviour of the FlexField.

Identify structure titles.
Identify the number and order of segments.
Identify the segment separator.
Choose the Value Sets and Values to be used.
Plan the window prompts.

Define the FlexField structure.
Define any Value Sets and their Values.
Define short-hand aliases.
Define Security rules where appropriate.
Define cross-validation rules where required.

Key FlexField Qualifiers

A KFF Qualifier is a label for a KFF segment, which Oracle Apps to locate the segment in KFF and access the information from that segment.

Two types of qualifiers -

flexfield qualifers

identifies a segment in a flexfield
These can be

Unique - should be only one of this type in a KFF
Required - Must exist for a KFF
Global - will be automatically assigned to every segment in a KFF

Must be assigned while defining the segments

segment qualifiers

identifies a value in a segment

Descriptive FlexField (DFF)

The presence of DFF on a form is identified by [ ]. There can be more than one DFF on a given form and the DFFs are not labeled.

DFFs are used to gather and display any additional information that is not maintained internally by Oracle Apps. To define a DFF structure, the following needs to be identified -

Additional information to be captured.
Frequency of capturing the information.
Information to be captured on an ad-hoc basis.
How much control the user requires on the processing window.
Identify whether the information to be captured on ad-hoc basis dependes on any value in base window.

Components

Descriptive FlexFields are made up of multiple individual segments that store different pieces of information. Each DFF can be used in more than one context, to support such a requirement one has design multiple layouts foreach DFF field.

A DFF has 4 components, which are -

Global Segment

common to all the contexts
should be planned first
Always displayed in the form

Context-Sensitive Segment

Unique to each context
dependent on the context of the DFF

Reference Field

used to distinguish between the contexts
a profile or existing field in the base form that sets the context for the flexfield.
For an existing field to be used as reference field

the field must have been defined as referenceable.
its values must be known and predictable.
the field must appear in all windows where DFF is displayed.
must have the same internal name in all the forms where DFF is used.

Context Field

allows users to manually select the contexts
does not exist in the base window, is a part of the DFF structure.

Multi-Org

2007-05-22T16:36:00.000Z

Concepts

The Multi-Org is an application & database enhancement functionality that enables multiple Business Units in an enterprise to use a single installation of Oracle Applications.
The basic business needs supported by Multi-Org feature are -

Support multiple Business Units even if they use different SOB(Set Of Books)

Secure access to data such that the users can access only the information relevant to them

Procure products and receive products from two different operating units that use two different SOBs

Support any number of business units within a single installation of Oracle Apps

Produce reports both across entities and within a single entity

Keeps Transaction data separate and secure
Provides data security by creating a separate security layer over Applications installation through database views.
The various products that support Multi-Org are -

Cash Management

Order Management

Release Management

Payables

Property Manager

Projects

Purchasing

Receivables etc.

The hierarchy that is maintained within Multi-Org is -

Business Group (BG)

This is the highest level of organisation structure without accounting impact

There can be only one Business Group

One BG can be related to one of many SOBs

Partitions HR and Purchasing Approval Hierarchy

Set Of Books (SOB)

Owned by General Ledger.

Maintains inter company entries to ensure that companies are never out of balance.

Enables to collect and quantify Financial data.

There major components - Chart Of Accounts (COA), Accounting Calendar and Currency.

One SOB can relate to only one BG.

One SOB can be related to one or more LEs.

Legal Entity(LE)

An organization to which Tax identifiers are assigned.

One LE is related to one SOB

One LE can be associated to one or more OUs.

Operating Unit (OU)

Secures information for applications such as Order Management or Payables

Naming conventions for naming Organizations in Multi-Org model are -

use country codes to identify the country of registration and location of an organization

Prefix SOB_ to the name of an operational book that obtains Journal Entries directly from a subledger system.

Prefix COB_ for consolidated SOBs.

Prefix ROB_ for SOB with Multiple Reporting Currency (MRC) feature.

Prefix IO_ to an IO intended to be a subledger in Oracle Apps.

Prefix VO_ to the name of an IO used for validation purposes.

Prefix GM_ to the name of IO that uses Global Item Master.

Prefix LE_ to the name of LE.

prefix PO_ to the name of an organization that is used only for planning purposes.

Implementation

The key concepts related to implementation of Multi-Org model are -

A BG is the highest level of the organization structure and does not impact the account.

Multiple SOBs can be associated to a single BG.

The Seed Data Replication request copies the applicable setup data for each defined OU.

Allows defining new business organizations while leaving the old structure and data untouched.

The additon of OU to the organizational structure requires defining organizations and relationships.

Each SOB may have different Chart Of Account strurcture, Accounting Calendar and Currency.

A BG determines which employees will be available to SOB and OU.

3 steps to implement Multi-Org Model

Define Organization Structure

Convert the entire Organization to Multi-Org Model using the tools supplied with the Oracle Applications. The tools are - ADADMIN and Seed Data Replication Programme

Perform the set-up operations on Individual Operating Units.

Security Model

Allows to customize Oracle Applications according to business needs
Users has Responsibilities
Responsibilites are attached to Operating Units or Inventory Organizations.
Reporting ability of an user dependes on his Responsibility.
Header information of customers and suppliers is stored in an unpartitioned table.

Shared Entities

2007-05-21T15:24:00.000Z

A Shared Entity in Oracle Apps is an object which can be used / referenced across multiple products.

Examples of Shared Entities are -

Items

Owned by Inventory
identifies the parts that are used, bought or sold by a transaction.

Location

Owned by Human Resources
Identifies multiple roles ie Ship-To, Bill-To etc.

Organizations

Owned by Human Resources
can be a physical site or representation of collection of sites sharing a common characteristics
Eg. Legal Entity, Operating Unit, Inventory Organization etc.

Set Of Books (SOB)

Owned by GL
enables to collect and quantify financial data
3 main components - Chart of Accounts (COA), Accounting Calendar and Currency.
All E-Business Applications utilize SOB information.

Architecture

2007-05-21T13:21:00.000Z

I have started preparing for my OCP in Oracle Financials. I have been working on Oracle Data Warehousing / ETL projects for quite a while now, and wants to get into Banking sector. So I have decided to get certified on Oracle Financials, with the hope that I can land into Banking sector which I have been dreaming for a while. So far I have been working on Oracle ( 8i to 10gR2), PLSQL Programming, Oracle Warehouse Builder ( 10gR1/R2), Discoverer and did a bit of BO Reporting / Universe creation as well. Of the late I have been thinking that I have reached a point in my career where I need to do something different / new, and hence this quest for getting into Financials side. Hopefully, fingers crossed, my preparation for this certification will open a gate to achieve my dream.

Here in this blog I am going to list down how I have been preparing for the exam 1z0-200 and what I have been learning. I have been going through the Oracle Documentation, which is available at oracle site - OracleAppsDocs, and the topics listed for this exam, which can be found at - 1Z0-200

Oracle Applications Business Architecture

- Oracle offers applications to automate business processes
- Technical Architecture of Oracle Applications should be based upon the individual business needs, not the other way round.

- The basic principles on which the Oracle Applications ( E-business Suite) is built are -

Modern Foundation

The E-Business Suite is built on Open & Scalable Architecture
It makes use of Technologies like Java/J2EE, HTML, JavaScript, Internet-accessibility and Centralised Management.

Complete E-Business System

Provides a complete enterprise-wide solution for manufacturing processes, Supply Chain Mgmt, Financial and Project Mgmt HRMS, Marketing and Sales Mgmt.

End-to-End Integraiton

Integration is built-in to the product, no need of any customisations or any additional system integration.

Globalization

Can be accessed through global networks.
supports many languages and currencies.
has built-in accounting and business localization.

Rapid Implementation

Reduced implementation time through the use of Rapid Implementation Tool , Patch Application Tool which are part of the E-Business suite.

Technical Architecture

Oracle E-Business suite supports 4 Architectural modes to support 4 common types of Users. They are Forms based, Self Service, Business Intelligence and Mobile Users.
The Architectural modes are accessed and controlled through Portals, thus minimising the admin tasks and operations costs
Technical Architecture comprises of

Desktop Tier - Displays data
Applications Tier -

Processes and stores business logic and provides all non-database specific operations required by an Oracle 11i instance.

Provides services such as Web Listeners

Database Tier

Manages data associated with Oracle 11i instance, uses Clustering technology
All PLSQL / SQL Queries are executed in Database Tier.

The Network is a critical component of Oracle Apps architecture.

Deployment Script

2007-05-21T13:02:00.000Z

OMB+ Script to Deploy Mappings

This script is tested for 10gR1 Release.
=====================================================================

puts " ******************* WARNING ********************"
puts ""
puts ""
puts " The proc owb_deploy_map is Created"
puts " To execute the proc call the above proc name at OMB+ command prompt "
puts ""
puts " ================================================="
puts "Requires the following parameters"
puts " Design Repos User"
puts " Design Repos Password"
puts " Host Name"
puts" Service Name"
puts " Port"
puts " Runtime Connection Name "
puts " Runtime User Password"
puts " ================================================"

puts "**************************************************************"
#
proc owb_deploy_map { desuser despwd host port srvc rtcon rtpwd } {
set fname [ open "c:/temp/deploy_maps.log" w]
puts $fname "----------------------------------------------------------"
puts $fname "Connecting to the Design Repository "
puts $fname "----------------------------------------------------------"
OMBCONNECT $desuser/$despwd@$host:$port:$srvc
set projList [ OMBLIST PROJECTS ]
#
foreach projName $projList {
OMBCC '$projName'
set projcon [OMBDCC]
puts $fname "current project context is $projcon"
puts $fname "CONNECTING TO RUNTIME REPOSITORY"
puts $fname "----------------------------------------------------------"
OMBCONNECT RUNTIME '$rtcon' USE PASSWORD '$rtpwd'
set ModList [OMBLIST ORACLE_MODULES ]
set i 1
OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP'
#
foreach ModName $ModList {
puts $fname " Working on : $ModName"
OMBCC '$ModName'
set curcon [OMBDCC]
set mapList [OMBLIST MAPPINGS]
set j 1
#
foreach mapName $mapList {
puts $fname " Creating Delpoyment Action Plans for :$mapName"

OMBALTER DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP' ADD ACTION '$mapName.DROP' SET PROPERTIES(OPERATION) VALUES ('DROP') SET REFERENCE MAPPING '$mapName'

OMBALTER DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP' ADD ACTION '$mapName.CREATE' SET PROPERTIES(OPERATION) VALUES ('CREATE') SET REFERENCE MAPPING '$mapName'

incr j
}

puts $fname " Executing the Deployment Action for MAPPINGS"

OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'DWH_DEPLOY_MAP'

OMBCOMMIT

OMBCC '..'
set curcon [OMBDCC]
puts $fname "context at the end is --- $curcon"
incr i

}
OMBCOMMIT
OMBCC '/'

}
#
# DROP THE DEPLOYMENT ACTIONS PLANS
#
OMBCC '$projval'
set depList [OMBLIST DEPLOYMENT_ACTION_PLANS]
set n 1

puts $fname "Dropping the Deployment Action Plans"

foreach specName $depList {

puts $fname "Dropped $specName"
OMBDROP DEPLOYMENT_ACTION_PLAN '$specName'
incr n

}
OMBCOMMIT
OMBDISC
#
#**********************************************************************************************
# END OF PROC owb_deploy_map
#**********************************************************************************************
}

Family OWB Mapping

2007-05-21T10:20:00.000Z

This post is in response to a question raised by a OWB Developer on OTN Forum, follow the link for full history - http://forums.oracle.com/forums/thread.jspa?threadID=509336&tstart=0.

The issue is -

Hi Mahesh,

Hope you have a nice weekend :)

That is what i don't know how to achive this through OWB mapping.

I will repeat again. I have data in this format.

ID | FamilyNum | RelationType
-------------------------------------------
100 1000 Head
850 1000 Wife
1100 1000 Son

and i need records in connection table as below:

ID | FamilyNum | RelationType
-------------------------------------------
100 850 Husband
850 100 Wife

100 110 Father
1100 100 Son

If you observe this is kind of opposite entry for each record.

This is very simple in PL/SQL coding. I thought use Cursor Loop in OWB, but don't find in help. If this is not possible then i implement this in SP and call it from OWB. No other option.

Thanks,
Danish

The Solution is -

OWB Map
-----------------

1. sources

HEAD FAMILY Table for Head of the Family
REST FAMILY Tale for rest of the family

2. The Join Condition is

HEAD.RELATION_TYPE IS NULL
AND HEAD.FAMILY_KEY = REST.FAMILY_KEY
AND REST.RELATION_TYPE IS NOT NULL

3. From the Output of Join To SET Operator (UNION ALL)

4. SET Operator has 2 Ingroups REST & REST_HEAD
The Ingroup REST takes input straight from OUTGRP of JOIN operator
The Ingroup REST_HEAD takes input from the OUTGRP of EXPRESSION.

5. The Expression ( this is to generate relation record for each family member ie Husband / Father ) has 3 input attributes

REST.RELATION_TYPE
REST.MEMBER_ID
HEAD.MEMBER_ID

From OUTGRP of JOIN.

6. The Output attributes and the business rules of Expression
FAMILY_NUM
(For this I am assuming that you are taking first 3 digits of the family member_id to get the Family Num of Father / Husband )

TransformationRule - TO_NUMBER(SUBSTR(TO_CHAR(INGRP1.R_MEMBER_ID,1,3 )))

MEMBER_ID

TransformationRule - INGRP1.H_MEMBER_ID

RELATION_TYPE

Transformation Rule -
CASE INGRP1.R_RELATION_TYPE
WHEN 'Wife' THEN 'Husband'
WHEN 'Son' THEN 'Father'
WHEN 'Daughter' THEN 'Father'
WHEN 'Husband' THEN 'Wife'
ELSE 'Unknown'
END

7. Map straight from the SET operator to Target Table.

Test Case

-----------------------
-- Source Table
-----------------------

desc family

MEMBER_ID NUMBER
FAMILY_KEY NUMBER
RELATION_TYPE VARCHAR2(30)

SELECT * FROM FAMILY ORDER BY FAMILY_KEY

100 1000 (null)
850 1000 Wife
1100 1000 Son

1500 2000 Son
500 2000 (null)
9000 2000 Husband
200 2000 Daughter

Two Set of records ( Family Key 1000, 2000) . One Family has 3 records and the other has 4 records including Head of Family.

----------------------------------------
--Before Running the OWB Map
-----------------------------------------

--Target Table structure

DESC TGT_FAM
FAMILY_NUM NUMBER
MEMBER_ID NUMBER
RELATION_TYPE VARCHAR2(30)
FAMILY_KEY NUMBER

4 rows selected

SELECT * FROM TGT_FAM

FAMILY_NUM MEMBER_ID RELATION_TYPE FAMILY_KEY
---------------------- ---------------------- ------------------------------ ----------------------

0 rows selected

--------------------------
--Run the OWB Map
-- Check the results
-----------------------------

SELECT * FROM TGT_FAM ORDER BY FAMILY_KEY

FAMILY_NUM MEMBER_ID RELATION_TYPE FAMILY_KEY
---------------------- ---------------------- ------------------------------ ----------------------
100 1100 Son 1000
100 850 Wife 1000
850 100 Husband 1000
110 100 Father 1000

150 500 Father 2000
200 500 Father 2000
900 500 Wife 2000
500 1500 Son 2000
500 200 Daughter 2000
500 9000 Husband 2000

10 rows selected

Unix

2007-05-19T15:26:00.000Z

Unix (officially trademarked as UNIX®) is a computer operating system originally developed in the 1960s and 1970s by a group of AT&T employees at Bell Labs including Ken Thompson, Dennis Ritchie and Douglas McIlroy. Today's Unix systems are split into various branches, developed over time by AT&T as well as various commercial vendors and non-profit organizations.

The present owner of the trademark UNIX® is The Open Group, an industry standards consortium. Only systems fully compliant with and certified to the Single UNIX Specification qualify as "UNIX®" (others are called "Unix system-like" or "Unix-like").

During the late 1970s and early 1980s, Unix's influence in academic circles led to large-scale adoption of Unix (particularly of the BSD variant, originating from the University of California, Berkeley) by commercial startups, the most notable of which is Sun Microsystems. Today, in addition to certified Unix systems, Unix-like operating systems such as Linux, Mac OS X and BSD derivatives are commonly encountered.

Overview

Unix operating systems are widely used in both servers and workstations. The Unix environment and the client-server program model were essential elements in the development of the Internet and the reshaping of computing as centered in networks rather than in individual computers.

Both Unix and the C programming language were developed by AT&T and distributed to government and academic institutions, causing both to be ported to a wider variety of machine families than any other operating system. As a result, Unix became synonymous with "open systems".

Unix was designed to be portable, multi-tasking and multi-user in a time-sharing configuration. Unix systems are characterized by various concepts: the use of plain text for storing data; a hierarchical file system; treating devices and certain types of inter-process communication (IPC) as files; and the use of a large number of small programs that can be strung together through a command line interpreter using pipes, as opposed to using a single monolithic program that includes all of the same functionality. These concepts are known as the Unix philosophy.

Under Unix, the "operating system" consists of many of these utilities along with the master control program, the kernel. The kernel provides services to start and stop programs, handle the file system and other common "low level" tasks that most programs share, and, perhaps most importantly, schedules access to hardware to avoid conflicts if two programs try to access the same resource or device simultaneously. To mediate such access, the kernel was given special rights on the system and led to the division between user-space and kernel-space.

The microkernel tried to reverse the growing size of kernels and return to a system in which most tasks were completed by smaller utilities. In an era when a "normal" computer consisted of a hard disk for storage and a data terminal for input and output (I/O), the Unix file model worked quite well as most I/O was "linear". However, modern systems include networking and other new devices. Describing a graphical user interface driven by mouse control in an "event driven" fashion didn't work well under the old model. Work on systems supporting these new devices in the 1980s led to facilities for non-blocking I/O, forms of inter-process communications other than just pipes, as well as moving functionality such as network protocols out of the kernel.

Components

The Unix system is composed of several components that are normally packaged together. By including — in addition to the kernel of an operating system — the development environment, libraries, documents, and the portable, modifiable source-code for all of these components, Unix was a self-contained software system.

The following are the main components , which forms a Unix System.

Kernel — source code in /usr/sys, composed of several sub-components:
- conf — configuration and machine-dependent parts, including boot code
- dev — device drivers for control of hardware (and some pseudo-hardware)
- sys — operating system "kernel", handling memory management, process scheduling, system calls, etc.
- h — header files, defining key structures within the system and important system-specific invariables
Development Environment — Early versions of Unix contained a development environment sufficient to recreate the entire system from source code:
- cc — C language compiler
- as — machine-language assembler for the machine
- ld — linker, for combining object files
- lib — object-code libraries (installed in /lib or /usr/lib) libc, the system library with C run-time support, was the primary library, but there have always been additional libraries for such things as mathematical functions (libm) or database access.
- make - build manager for effectively automating the build process
- include — header files for software development, defining standard interfaces and system invariants.
Commands — Unix makes little distinction between commands (user-level programs) for system operation and maintenance (e.g. cron), commands of general utility (e.g. grep), and more general-purpose applications such as the text formatting and typesetting package. Nonetheless, some major categories are:
- sh — The "shell" programmable command-line interpreter, the primary user interface on Unix before window systems appeared, and even afterward (within a "command window").
- Utilities — the core tool kit of the Unix command set, including cp, ls, grep, find and many others. Subcategories include:
  - System utilities — administrative tools such as mkfs, fsck, and many others
  - User utilities — environment management tools such as passwd, kill, and others.
- Document formatting — Unix systems were used from the outset for document preparation and typesetting systems, and included many related programs such as nroff, troff, tbl, eqn, refer, and pic. Some modern Unix systems also include packages such as TeX and GhostScript.
- Graphics — The plot subsystem provided facilities for producing simple vector plots in a device-independent format, with device-specific interpreters to display such files. Modern Unix systems also generally include X11 as a standard windowing system and GUI, and many support OpenGL.
- Communications — Early Unix systems contained no inter-system communication, but did include the inter-user communication programs mail and write. V7 introduced the early inter-system communication system UUCP, and systems beginning with BSD release 4.1c included TCP/IP utilities.
Documentation — Unix was the first operating system to include all of its documentation online in machine-readable form. The documentation included:
- man — manual pages for each command, library component, system call, header file, etc.
- doc — longer documents detailing major subsystems, such as the C language and troff

Basic Unix Commands

2006-11-15T12:54:00.000Z

Basic Unix Commands

Navigational Commands

To nativage around in Unix environment, the following commands will be useful.

cd		Change Directory
pwd		Prints Current working directory to the screen
cd $HOME		Changes to your Home Directory
cd ./mydir		Changes to directory named "mydir" from current working directory
mkdir mydir		Creates a new sub directory called mydir under the current directory
ls		Lists the contents of the files from the current directory

File Related Commands

Commonly used File operation commands.

cat ?myfile?		displays the contents of the file named "myfile" onto screen. This command will be used if one wants to see the contents of a given file but not for editing the file.
more ?file name?		Same as the command "cat", except that this command displays the contents on the screen pausing for each screen. Pressing the spacebar displays the next screen. Is useful to check large files.
vi ?file name?		Opens up "vi" editor and opens the named file for editing. If the named file does not exists then creates a one.
cd ./mydir		Changes to directory named "mydir" from current working directory

Nandoo's Blog

Visualizaton in OBIEE11g - eseminar

ORA-00845: MEMORY_TARGET

Oracle BI Intelligence 11g ( OBI 11g) Launch 07/07/2010

OWB11gR2 - SQL Server Connection

Oracle DB11gR2 Installation on Enterprise Linux 5 - missing RPMs

Dashboard Prompt constraining the results

Adding Foreign Key Constraints using OMB+

OBIEE Answers - Pivot Table / Chart Horizontal Layout - Multiline

Snow! Snow!

OWB 11g Getting Started Book

Oracle Application Express – End User Change Password Procedure

TCL Script to Deploy OWB Mappings from file

Passing Parameters from OBIEE Dashboards to BI Publisher Report

Oracle Business Intelligence - OBIEE - 101: OBIEE making a custom Log On and Log Off screen

Concatenation in OBIEE

Oracle Business Intelligence - OBIEE - 101: OBIEE Directing a user to a default dashboard

Apex 3.0.1 Installation

using WHILE Loop - OWB10gR2

OMB+(Tcl) script for copying attributes between groups

OWB SQL*Loader Mapping

FlexFields

Multi-Org

Shared Entities

Architecture

Deployment Script

Family OWB Mapping

Unix

Basic Unix Commands

Basic Unix Commands

Navigational Commands

File Related Commands