BI APPS Customization Process

BI APPS Customization Process 

Scenario: Currently organization’s reporting model is based on Views based on EBS. It needs to be replaced with BI Apps model

Approach:

Perform GAP Analysis on views, performing it for a column

• Select a column from the view e.g. ‘Transaction_Date’
• Analyze the view script and identify the source column and table for this col
• The source column is ‘TRANSACTION_DATE_ENTERED’ from  ’FA_TRANSACTION_HEADERS’

                            

• Open ETL Lineage document for BI Apps that has the source and target details. Look for FA_TRANSACTION_HEADERS in source table and TRANSACTION_DATE_ENTERED in source column.

• Since we have four warehouse columns for the same source column, we need to identify the exact warehouse column by checking the logic in ODI.
• Find the SDE and SIL interfaces that are loading the warehouse tables ‘W_FA_XACT_F’ and ‘W_FIXED_ASSET_D’ using the below queries in warehouse schema.

Select * FromW_ETL_LOAD_DATES

Where (Target_Table_Name like 'W_FA_XACT_F%' or Target_table_name like 'W_FIXED_ASSET_D%') and Target_table_name not like '%TL';

• Check the SIL of W_FIXED_ASSET_D and backtrack the column SRC_EFF_FROM_DT
• Go to Designer àBIApps Project à Mappings à SILOS à SIL _FIXEDASSETDIMENSION. Check the source column for SRC_EFF_FROM_DT. In this case the source is W_FIXED_ASSET_DS.SRC_EFF_FROM_DT
• Now, open the SDE for the same dimension. Go to Designer àBIApps Project à Mappings --> SDE_ORAR1213_Adaptor àSDE_ORAR1213_ADAPTOR_SDE_ORA_FIXEDASSETDIMENSION. Open the interface and backtrack the column. It is TRANSACTION_DATE_ENTERED column from FA_TRANSACTION_HEADERS

Even though we are getting the TRANSACTION_DATE_ENTERED column as SRC_EFF_FROM_DT in W_FIXED_ASSET_D, the join condition through which FA_TRANSACTION_HEADERS is joined to other tables is different between Query and ODI interface for W_FIXED_ASSET_D.

In ODI:

• So, we will follow the steps 8 and 9 again to check the source for XACT_DT_WID column in W_FA_XACT_F.
• After checking, we have found that the source column is coming correct in Retirement folder and Utilization folder of W_FIXED_ASSET_DS. But, it is incorrect in Depreciation folder(See screenshot below).

• So, finally we have decided that this column is a GAP and we have taken the entry as shown below.

In this way, we have identified the GAPs between the Custom Queries and Out of Box BIApps.

• As we already have the FA_TRANSACTION_HEADERS as a source table in W_FA_XACT_F, we have decided to develop the new column in W_FA_XACT_F. For that, we have created new folders for all our customizations as shown below by including a word which is specific to client before the actual name.

• As we don’t want to modify the existing interfaces, we have copied the existing interfaces and pasted them in the custom folders and changed the names as “X_” which we follow for all the customizations.

• Now, as we have decided on our approach to add this column, we have prepared Functional Design Document for all the GAPs in a particular Custom View explaining the high level implementation steps. Find below a sample FDD
• The Development steps as mentioned in the above document include the following steps in a brief

                        • Add column X_XACT_DT_WID in physical table

                        • Add the column in Models in ODI

                        • Map the target column from source table/column.

                       • Apply the CALCULATE_DT_WID_DFLT function and calculate the WID and map                            it to X_XACT_DT_WID.

                      • Import the column in the physical layer of RPD.

                      • Create a new alias for W_DAY_D and join it to W_FA_XACT_F based on 

                        X_XACT_DT_WID = ROW_WID

                      • Expose the column to presentation layer as “Transaction Date”

     • Compare the data between Custom Query and BIApps and add the Test Case in Design Document.

This way we develop all the GAPs and make them available for Reporting.

Establishing connection between OBIEE and Essbase

Due to the feature of drilling to the detail reports in OBIEE, it would be an excellent thought to navigate from Hyperion reports to OBIEE reports and then to drill into the details of Hyperion reports.

But despite providing all credentials establishing a connection with ESSBASE from OBIEE is a nightmare and can consume a considerable amount of time in resolving the issue:

Perform the below steps sequentially to establish the connection with ESSBASE:

• Install ESSBASE client both at SERVER and LOCAL machine
• Set variables with path values ‘ARBORPATH’ and ‘ESSBASEPATH’ in both SERVER and LOCAL machine

         ARBORPATH = C:\oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient

         EssbaseClient = C:\oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient

         Path = %PATH%;%ESSBASEPATH%\bin;%ARBORPATH%\bin

• Restart OBIEE Server
• Update bi_init.bat in Local Machine as below:

@echo off

rem overwrite JAVA_HOME

set JAVA_HOME=C:\OBIEE\jre

if exist "%JAVA_HOME%\bin\java.exe" goto:doneJAVAHOME

rem abort

echo *******************       

echo ****** ERROR ******

echo ******************* 

set /p prompt=[Invalid JAVA_HOME: %JAVA_HOME%] Enter return to abort

exit 100

:doneJAVAHOME

set ORACLE_HOME=C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome

set ORACLE_INSTANCE=C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orainst

set ORACLE_BI_APPLICATION=coreapplication

set ARBORPATH=C:\oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient

set ESSBASEPATH=C:\oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient

rem call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\%ORACLE_BI_APPLICATION%\setup\user.cmd

if NOT "%1" == "" set COMPONENT_NAME=%1

set PATH=%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\bifoundation\web\bin;%ORACLE_HOME%\bin;%JAVA_HOME%\bin;%windir%;%windir%\system32;%ESSSBASEPATH%\bin;%ARBORPATH%\bin;%PATH%

if "%2" == "" goto:end

%2

:end

• Open RPD in online mode, save it to local machine and close it
• Open RPD in offline mode (not online), mind it this is very important point due to which RPD does not connects to ESSBASE Database
• Establish the connection and import the required objects
• Save the RPD and Close it
• Upload it to the OBIEE Server
• Open the RPD in Online Mode, now imported database objects are ready to use

Steps for developing XML Publisher report

Steps for developing XML Publisher report.

•  Develop the query according to the requirement.
• Open Report Builder and paste the query.

• Now click on OK, we will get a group, in this case G_ORDER_NUMBER.

• Since the data to be displayed is in Master detail form we need to change the group accordingly. Drag and drop out order number and name field.

•      Create a user parameter P_CONC_REQUEST_ID.

•     Write the below code in “Before report” trigger 

          begin       
             SRW.USER_EXIT('FND SRWINIT');

           return (TRUE);

           end;

•         Write the below code in “After report” trigger

         begin

            SRW.USER_EXIT('FND SRWEXIT');

           return (TRUE);

            end;

•    Now compile the form. Go to tools menu Program à Compile à All. If we get Compilation completed successfully then the report is compiled successfully without any errors.

•     Save the file as SALES_ORDER_REPORT.rdf.

•     Put the saved SALES_ORDER_REPORT.rdf file in Application top/reports/US folder
•     Now register the rdf report in apps. First create the executable.

•         Define the concurrent program, here the output format should be mentioned as XML.

•      Now add this program to a responsibility.
•      Now we have to build the layout in rtf. Open Microsoft Word document. In the header section give the heading details like (Report name, date, page number etc.) After developing the heading you can make the table border as ‘No Border’.

•     Create a table as follows.

•    Now design the layout as follows. In the first fe xml tag we have columns from the first group G_1 and in second fe xml tag we have columns from second group G_ORDER_NUMBER. After designing the layout save the file with extension as .rtf (SALES_ORDER_DETAILS.rtf)

• Go to XML Publisher Administrator responsibility à Home à Data Definitions. Click on Create Data Definition Button.

            Give Name  as Sales Order Details Report (PDF) (generally we give concurrent program                     name you can give other name also).

            Code as SALES_ORDER_DETAILS (Imp: This should be concurrent program short name)

            Application as Order Management. Click on Apply.

•      Go to Templates tab. Click on Create template button.

      Here give Name as Sales Order Details Report (PDF) (generally we give concurrent program               name you can give other name also).

     Code as SALES_ORDER_DETAILS ( You can give any code but we generally give  concurrent         program short name)

     Application as Order Management.

    Data Definition as Sales Order Details Report (PDF) (Imp: This should be name of data definition)

    In the File textbox browse the rtf file developed

    Language: English

    Territory: United States , Click on Apply.

•      Now the XML Publisher report has been registered in apps and run the program to see the output.

                                                                                                                                                                       

Steps for Developing an Oracle Form

Steps for Developing an Oracle Form

•      Create/Use a table on which you want to have the form data stored.

                     Example: Create Table XX_SUPPLIER_INFO with the following columns.

                     CREATE TABLE XX_SUPPLIER_INFO

                     (

                     SUPPLIER_NAME    VARCHAR2(100),                     ADDRESS         VARCHAR2(100),

                     PHONE_NUM       NUMBER,

                     FAX             VARCHAR2(50),

                     EMAIL           VARCHAR2(50),

                           );

•      Open Form builder à File à Connect.

              Connect to database.

•    Open template.fmb file (You can find in $AU_TOP/forms/US)
•     Click on Template and press F4 to change the name of module.

                   Example: XXSUPPLIER.

•     In Object Navigator Right click on Data Blocks à Data Block Wizard à Next à Select Table or view à Next à Give table name (Example: XX_SUPPLIER_INFO) and tab out.
•    List of columns will appear, move them to database items(right side) à next à next à Give data block name XXSUPPLIER à Next à Click on just create the data block à Finish.
•   Data block XXSUPPLIER will be created. Press F4 on XXSUPPLIER to open property palette and assign subclass information as follows.

•   For each column in data block provide the subclass information as below.

•      Create a new canvas. Example: XXSUPPLIER and give subclass information as CANVAS.

•      Create a new window by giving the above canvas and subclass information.

•     Double click on XXSUPPLIER canvas, a layout will open, place a frame in the layout and  press F4 on frame and give the following information. (This can be performed by the data wizard while creating the data block also)

Subclass information: FRAME_RECT

Layout Data Block: XXSUPPLIER

Layout Style: Tabular

Number of Records to be displayed: 4

•      Go to each item in Data block and give the canvas as XXSUPPLIER.
  
  
•        Now arrange the fields in the layout accordingly.

•   Press F4 on XXSUPPLIER canvas and give window as XXSUPPLIER.

•                 Press F4 on XXSUPPLIER module and give first navigation data block as                   XXSUPPLIER.

•    Go to PRE FORM trigger and give the set_window_position as XXSUPPLIER.

•    Go to APP_CUSTOM package body and give if (wnd = 'XXSUPPLIER') then

•    Now save the form as XXSUPPLIER.fmb and move it to application_top/form/us folder.
•   Compile the form using the code

frmcmp_batch module=<Folder Path> /XXSUPPLIER.fmb userid=apps/<pwd> output_file=<application top>/forms/US/XXSUPPLIER.fmx module_type=form batch=yes compile_all=special

•    Once the form is successfully compiled we will get XXSUPPLIER.fmx otherwise if any error occurs it will be mentioned in XXSUPPLIER.err
•   Create a Form in oracle Application. Go to Application Developer à Application à Form

•  Create a Function Go to Application à Function

•    Assign it to a Menu. System Administrator responsibility à Application à Menu

•    Go to that responsibility and open the form and we can enter the details and save and it will reflect in XX_SUPPLIER_INFO table.