Steps to add new custom fact group in Biapps 11g

Below are the steps  for creating a new custom fact group , dimension group in OBIA10g and creating references between them.

Please take a backup of entire DEV_BIACOMP, DEV_BIA_ODIREPO  schemas before we update/insert any record in the metadata.

Assumptions:
i.    Developer must be aware of the below hierarchy  in BIACM.
Offering   Functional Area (FA)  Fact group(FG)  Dimension Group (DG)
ii.    Steps are given below for creating custom  SFDC fact group and dimension group.
iii.    Functional area : ‘SALESACCNT_FA’ , Fact group name : 'X_SFDC_FG', Dimension group : ‘X_SFDC_DG’


BIACM schema changes :
Login to DW_BIACOMP schema ( BIACM Schema )

1.    Creation of fact group

  insert into c_fact_group values
(0, 'X_SFDC_FG', 'X_SFDC_FG','Sales Analytics Functional Area - Fact Group 1',null,null
,'BIApps','10-APR-2015','BIApps','10-APR-2015',-1);

2.    Creation of Functional Area to Fact Group Relation

insert into c_funcarea_factgroup_rel values
(null, ' SALESACCNT_FA ', 'X_SFDC_FG','BIApps', '10-APR-2015','BIApps','10-APR-2015',-1);

3.    Similarly we need to create new custom dimension group.
         Insert a new record in  ‘ c_dimension_group’ table with new ‘DIMENGROUP_CODE’, ‘DIMENGROUP_KEY’  as  'X_SFDC_DG’.

4.    Create the relation between fact group and dimension group
by creating a new entry in  ‘c_fact_group_dimen_group_rel’   table.
insert into c_fact_group_dimen_group_rel (null, 'X_SFDC_FG', 'X_SFDC_DG’,'Biapps',null,null,null);

After adding all these records in the above mentioned tables, we should be able to see the new custom fact  group in BIACM under Sales Analytics Functional Area.

ODI Designer changes  (Model – Datastore changes):

5.    From ODI models, create the foreign key references between new fact and new dimension tables.
When creating new FACT/Dimension Model (Table) carry out the following:
Navigation – Designers --> Models --> Oracle BI Applications --> Oracle BI Applications --> Fact -->
navigate to Flexfields tab
- Uncheck the Default checkbox against OBI Fact Group
- In the value field enter   'X_SFDC_FG'.

To make sure that the flexfields are populated correctly so that the load plan can pick up the interfaces.
Run the below SQL to check if all the flex fields are populated correctly on ODI Repository schema.

select distinct t.i_table,t.res_name from snp_table t, snp_ff_valuew ff
where t.i_table=ff.i_instance and ff.ff_code='OBI_FACT_GROUP' and short_txt_value in ('X_SFDC_FG')



ODI Designer changes ( Project  - create custom SDE, SIL mappings).
6.    Create new custom fact and dimension SDE and SIL interfaces in BIAPPS project with proper release tags.
Eg :
Sub Folder :  Custom_SILOS
Job Folder: SIL_SFDCLeadFact
                
                                For SDE : SDE_SFDCLeadDimension

ODI Designer changes ( Load plan changes ):

7.    Load Plan Dev Components:
Create new load plan for each dimension and fact jobs.
Eg : 3 SIL  Fact X_SFDC_FG     Add the SIL scenario steps  under this load plan with root step name as ‘X_SFDC_FG’
Do similarly for Dimension jobs with X_SFDC_FG as root step name.

8.    Load plan System components:
Add new steps(Parallel/Serial) under   Load plan system components
 2 SDE Fact Group   ,      2 SIL Fact Group  as      

   -$${3 SDE Dim SFDCLEAD_DIM}   and   -$${3 SIL Fact X_SFDC_FG}

9.    After all these changes , once we create load plan from BIACM , the new custom facts and dimensions should be picked up by the new load plan.


For creating new Offering , similarly we need to add new entries in BIACOMP schema.

i.    c_bia_offering values  ( creation of custom offering).
ii.    c_bia_offering_ds_type_rel  ( creation of source for the new offering).
iii.    c_functional_area ( creation of custom functional area).
iv.    c_bia_offering_funcarea_rel ( Creation of relation between Offering and Functional Area).

And follow the above steps 1-9 to complete end to end set up.