Saturday, February 7, 2015

ORACLE BI APPS 11.1.1.8.1 – CUSTOMIZATIONS – HOW TO ADD NEW FACT TABLE

Why do we have to add new fact table?
It is often seen that certain key business requirements are not covered by the pre-built contents (Out Of The Box). For example, the OTTB Data Warehouse may not include some reporting metrics and you need to source it from the primary data source (OLTP database) or external secondary data stores (i.e. file, RDBMS, XML, and OLAP). There are number of situations where there is a need to modify the OOTB contents.
In these cases it is important to follow certain guidelines and techniques when developing non-standard BI elements. The idea is to follow the same design techniques and guidelines that BI Apps uses to build OOTB contents for customization. This approach would be helpful to maintain the Oracle’s high standard development practices.

This article describes how to do customizations in case we are required to build new Fact tables for the BI Apps 11.1.1.8.1.

How to add new fact to the Oracle Business Analytics Warehouse?
1.     Create custom fact datastores
Create custom Fact Staging and Fact tables in the database. Name all the newly created tables as WC_. These datastores include 2 following required columns:
·         INTEGRATION_ID: Stores the primary key or the unique identifier of a record as in the source table.
·         DATASOURCE_NUM_ID: Stores the data source from which the data is extracted.
In this document, we use WC_MASAN_PROC_FST_FS and WC_MASAN_PROC_FST_F which are the custom datastores of Procurement Analytics as sample templates.
Create 2 these custom tables in DW database, then import into ODI using the BI Apps RKM.
Note that the specific submodel that a table belongs to drives the table maintenance behavior. For example, tables in the ‘Fact Stage’ submodel will always be truncated during each ETL run while tables in the ‘Fact’ submodel are only truncated during a Full ETL run. 
2.     Create custom SDE and SIL tasks
Before creating the custom SDE and SIL tasks, you should create new SDE and SIL Adaptor folders named as CUSTOM_<Original Folder Name> (e.g. CUSTOM_SDE_ORAR122_Adaptor) and CUSTOM_SILOS then you can put the custom SDE and SIL tasks into.
In ODI Studio, create the SDE and SIL task in the Custom SDE and SIL adaptor folders. In this document, we use SDE_ORA_MasanProcForecastPriceFact and SIL_MasanProcForecastPriceFact as sample templates, as shown below:
These tasks include the logic required to populate the data to the custom columns.
Dummy Interface
If you just use the Procedures to create the tasks and no need to use the Interfaces, then you should create a Dummy Interface which the Source and Target Datastore are the same and added the filter as shown below:
The purpose of creating this Interface is OBI Apps will include automatically the Pre-Steps (Initialize) and Post-Steps (Finalize) (i.e. gather stats) in the generated Load Plan as shown below and adding filter is to not populate the data the target table using this interface.
Put the Interface is followed by the Procedure in the Package as following example:
Generate the custom scenario
Once you done the custom packages creation, generate the scenarios for those tasks named as <Folder Name>_<Package Name> (e.g. SDE_ORAR122_ADAPTOR_SDE_ORA_MASANPROCFORECASTPRICEFACT and SILOS_SIL_MASANPROCFORECASTPRICEFACT).
3.     Add foreign key constraint to all dimensions associated with custom fact
Add a foreign key constraint to all dimension tables associated with this fact. The foreign key constraint ensures the Dimension SIL task is included in the generated load plan. The Dimension SDE task will be included in the generated load plan because it populates the staging table that is used as a source for the Dimension SIL task.
·         Drill into the Fact datastore.
·         Right-click the ‘Constraints’ subfolder below the Fact datastore and select New Reference. The naming convention is FK_<Fact Table>_<Dimension Table><numeric suffix> (e.g. FK_WC_MASAN_PROC_FST_F_W_PRODUCT_D).
·         Set the Type to ‘User Reference’, select the dimension from the Table drop-down list and in the Column subtab, add new column, then select the custom WID column in the fact table and the ROW_WID column in the dimension table.
Include the custom facts step automatically in the Generated Load Plan
Add the SDE step to ‘3 SDE Fact X_CUSTOM_FG <Product Line Version Code>’ Load Plan Component:
·         In Designer, navigate to Load Plans and Scenarios à BIAPPS Load Plan à Load Plan Dev Components à SDE à <Product Line Version Code> and double-click the ‘3 SDE Fact X_CUSTOM_FG <Product Line Version Code>’ Load Plan Component.
·         Select the ‘X_CUSTOM_FG’ step.
·         Drag the custom scenarios from left pane to the right pane and arrange in serial or parallel steps
·         Provide the Scenario Version should be -1 (latest version) and Step Name should match the Task name (e.g. SIL_MasanProcForecastPriceFact). Set the Restart Type to ‘Restart from failed step’.
Add the SIL step to ‘3 SIL Fact X_CUSTOM_FG’ Load Plan Component:
·         In Designer, navigate to Load Plans and Scenarios à BIAPPS Load Plan à Load Plan Dev Components à SIL and double-click the ‘3 SDE Fact X_CUSTOM_FG’ Load Plan Component.
·         Select the ‘X_CUSTOM_FG’ step.
·         Drag & Drop the custom scenarios from left pane to the right pane and arrange in serial or parallel steps
·         Provide the Scenario Version should be -1 (latest version) and Step Name should match the Task name (e.g. SIL_MasanProcForecastPriceFact). Set the Restart Type to ‘Restart from failed step’.





Monday, December 29, 2014

Get Failed ODI Messages

The following script is to get some information of failed package when we run it in ODI. This information should be saved by an ODI variable and we can pass it to ETL process and to capture the error messages sent out by the ODI server. These messages are also good in case we want to show it in the alert email and we don't need to open ODI Operator.

--Create a Global Variable called “ GET_ERROR_MSG” and paste the following codes
SELECT ' The  '||CASE
WHEN SSS.STEP_TYPE='F' THEN 'INTERFACE'
WHEN SSS.STEP_TYPE='VD' THEN 'VARAIBLE DECLARATION'
WHEN SSS.STEP_TYPE='VS' THEN 'SET/INCREMENT VARAIBLE'
WHEN SSS.STEP_TYPE='VE' THEN 'EVALUATE VARIABLE'
WHEN SSS.STEP_TYPE='V' THEN 'REFRESH VARIABLE'
WHEN SSS.STEP_TYPE='T' THEN 'PROCEDURE'
WHEN SSS.STEP_TYPE='OE' THEN 'OS COMMAND'
WHEN SSS.STEP_TYPE='SE' THEN 'ODI TOOL'
WHEN SSS.STEP_TYPE='RM' THEN 'REVERSE ENGINEERING MODEL'
WHEN SSS.STEP_TYPE='CM' THEN 'CHECK MODEL'
WHEN SSS.STEP_TYPE='CS' THEN 'CHECK SUB MODEL'
WHEN SSS.STEP_TYPE='CD' THEN 'CHECK DATA STORE'
WHEN SSS.STEP_TYPE='JM' THEN 'JOURNALIZE MODEL'
WHEN SSS.STEP_TYPE='JD' THEN 'JOURNALIZE DATA STORE'
END||' '||SSS.STEP_NAME||
' , has failed due to - '||
SNET.TXT
FROM <%=odiRef.getObjectName("SNP_SESSION")%> SS,
<%=odiRef.getObjectName("SNP_SESS_STEP")%> SSS,
<%=odiRef.getObjectName("SNP_EXP_TXT")%> SNET
WHERE SSS.SESS_NO=SS.SESS_NO
AND SNET.I_TXT=SS.I_TXT_SESS_MESS
AND SNET.TXT_ORD=0
AND SS.PARENT_SESS_NO ='<%=odiRef.getPrevStepLog("SESS_NO")%>'

Get Oracle Database basic information

1. Name/ Versions



set heading off
set verify off
column today NEW_VALUE p_currdate noprint 
select TO_CHAR(SYSDATE,'fmMonth ddth, yyyy') today from   dual; 
clear breaks
clear computes
clear columns 
set heading off
column name heading '' justify center format a74 
select   'Database Name (SID): ' || name "name" from     v$database; 
prompt
prompt
prompt Version/SGA Information: 
set heading off
select * from v$version; 
column sgatot justify left format 9,999,999,999 
select   'Total System Global Area as of &p_currdate.:' hd1,          sum(value) sgatot, 'bytes' hd2 from     v$sga; 

  2. Tablespace/ Data files

prompt
prompt
prompt Tablespaces and Datafiles: 
clear breaks
clear computes
clear columns 
set heading on
column tablespace_name heading 'Tablespace' justify left format a15
column file_id heading 'File|ID' justify center format 9999
column file_name heading 'Datafile' justify center format a60 word_wrapped
column size Heading 'Size|in MB.' justify center format 9,99990.99 
break on tablespace_name skip 1 on report skip 2
compute sum label 'TS SIZE:' of size on tablespace_name
compute sum label 'DB SIZE:' of size on report 
select tablespace_name,        file_id,        file_name,       
bytes/1024/1024 "size" from dba_data_files order by tablespace_name, file_id, file_name; 

3. Space Usage

prompt Space usage (as of &p_currdate.):
prompt 
clear breaks
clear computes
clear columns 
set heading on
column tspace heading 'Tablespace' justify left format a20 truncated
column tot_ts_size heading 'Size|in MB.' justify left format 9,99999999990
column free_ts_size heading 'Used|in MB.' justify right format 9,9999999990
column tbusedpct heading '' justify left format a6
column tbfreepct heading '' justify left format a6 
break on report
compute sum label 'Totals:' of tot_ts_size free_ts_size on report 
col tspace form a25 Heading "Tablespace"
col tot_ts_size form 99999999999999 Heading "Size (Mb)"
col free_ts_size form 99999999999999 Heading "Free (Mb)"
col ts_pct form 999 Heading "% Free"
col ts_pct1 form 999 Heading "% Used"

SELECT df.tablespace_name tspace,
       df.bytes/(1024*1024) tot_ts_size,
       sum(fs.bytes)/(1024*1024) free_ts_size,
       round(sum(fs.bytes)*100/df.bytes) ts_pct,
       round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1
FROM dba_free_space fs,
     (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df
WHERE fs.tablespace_name = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes;


4. Redo Log Files

prompt
prompt
prompt Online Redo Logfiles: 
clear breaks
clear computes
clear columns 
column member heading 'Logfile' justify center format a60 word_wrapped
column group heading 'Group|Number' justify center format 9999
column size heading 'Size|in MB.' justify center format 999990.99 
select f.member "member",        f.group# "group",        l.bytes/1024/1024 "size" from v$logfile f, v$log l where f.group#=l.group# order by f.group#,f.member;


5. Control Files

prompt
prompt
prompt Control files: 
clear breaks
clear computes
clear columns 
column name heading 'File Name' format a60 word_wrapped 
select name from v$controlfile; 


6. Rollback Segments

prompt
prompt
prompt Rollback Segments (sizes as of &p_currdate.): 
clear breaks
clear computes
clear columns 
set heading on
column tablespace_name heading 'Tablespace' justify left format a15 truncated
column segment_name heading 'Seg|Name' justify center format a7
column status heading 'Status' justify center format a8
column initial_extent heading 'Initial|(in M)' justify center format 99990.9
column next_extent heading 'Next|(in M)' justify center format 99990.9
column min_extents heading 'Min|Ext' justify center format 99990
column max_extents heading 'Max|Ext' justify center format 999999999990
column pct_increase heading 'Pct|Inc' justify center format 99990
column rbsize heading 'Curr Size|(in M)' justify left format 9,99990 
break on tablespace_name skip 1 on report skip 2 
select    r.tablespace_name,           r.segment_name,           r.status,   
 r.initial_extent/1024/1024 "initial_extent", 
 r.next_extent/1024/1024 "next_extent",
 r.min_extents,           r.max_extents,     
 r.pct_increase,     
sum(e.bytes)/1024/1024 "rbsize"
from   dba_rollback_segs r, dba_extents e
 where     e.segment_name = r.segment_name
 group by  r.tablespace_name, r.segment_name, r.status,
           r.initial_extent/1024, r.next_extent/1024,
           r.min_extents, r.max_extents, r.pct_increase;


7. Parameters

prompt
prompt Parameters (non-defaults): 
clear breaks
clear computes
clear columns 
column name heading 'Name' format a35 word_wrapped
column pvalue heading 'Value' format a50 word_wrapped 
select name, rtrim(value) "pvalue" from v$parameter order by name; 


8. User Details


prompt
prompt user details : 
clear breaks
clear computes
clear columns 
select username,profile,default_tablespace,temporary_tablespace from dba_users;
select * from nls_database_parameters;
select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
select owner,object_type,count(1) from dba_objects group by owner,object_type order by owner;
SELECT file_name,tablespace_name,autoextensible,maxbytes/1048576 FROM dba_data_files;
spool off