Oracle^® Business Intelligence Applications Installation and Configuration Guide > Configuring Oracle Financial Analytics > Process of Configuring Oracle Financial Analytics for Oracle 11i >

Configuring General Ledger Account Hierarchies Using General Ledger Accounting Flexfield value sets definitions

Oracle EBS supports up to 30 segments in which to store accounting flex fields. Flex fields are flexible enough to support complex data configurations, for example:

• You can store data in any segments.
• You can use more or fewer segments per chart of account, as required.
• You can specify multiple segments for the same chart of account.

An Example Data Configuration for a Chart of Accounts

A single company might have a US Chart of Account and an APAC Chart of Account with the following data configuration:

The example shows that in Chart Of Account 4256, Company is stored in the segment3 column in the Oracle EBS table GL_CODE_COMBINATIONS_ALL. In Chart Of Account COA4257, Company is stored in segment1 column in GL_CODE_COMBINATIONS_ALL table. The objective of this configuration file is to make sure that when segment information is extracted into the warehouse table W_GL_ACCOUNT_D, segments with the same nature from different chart of accounts are stored in the same column in W_GL_ACCOUNT_D.

For example, we can store Company segments from COA 4256 and 4257 in segment1 column in W_GL_ACCOUNT_D; and Cost Center segments from COA 4256 and 4257 in segment2 column in W_GL_ACCOUNT_D, and so forth.

About Configuring the ETL Process for GL Accounting Flex fields

Before you run the ETL process for General Ledger Accounts, you need to specify the segments that you want to analyze. To specify the segments that you want to analyze, use the following ETL configuration file:

$PMSERVER/srcfile/file_glacct_segment_configur_ora11i.csv

In the file_glacct_segment_config_ora11i.csv file, you need to specify the segments of the same type in the same column. For example, you might store all Cost Center segments from all chart of accounts in one column, and all Company segments from all chart of accounts in another column.

For example, you might want to do the following:

• analyze GL account hierarchies using only Company, Cost Center, Natural Account, and LOB.

  You are not interested in using Geography for hierarchy analysis.

• store all Cost Center segments from all COAs in ACCOUNT_SEG2_CODE column in W_GL_ACCOUNT_D.
• store all Natural Account segments from all COAs in ACCOUNT_SEG3_CODE column in W_GL_ACCOUNT_D.
• store all LOB segments from all COAs in ACCOUNT_SEG4_CODE column in W_GL_ACCOUNT_D.
• in W_GL_BALANCE_A (where you store GL account balances at aggregated level), you want to store GL account balances at Company and Cost Center level instead of at GL Code Combination level.

The screenshot below shows how the file_glacct_segment_config_ora11i.csv would be configured to implement the business requirements specified above.

To Set Up Hierarchies With General Ledger Accounting Flex fields

1. Use the /srcfile/file_glacct_segment_configur_ora11i.csv file to specify the segments that you want to analyze.

   For more information about configuring the file_glacct_segment_configur_ora11i.csv file, see About Configuring the ETL Process for GL Accounting Flex fields.

2. In the DAC, do the following:
   1. for the Subject Area 'Financials - General Ledger', in the 'Configuration Tag' tab, make sure that:
      • the tag 'Oracle - Extract Value Set Hierarchies' is unchecked for the 'Inactive' checkbox.
      • the tag 'Oracle - Extract FSG Hierarchies' is checked in the 'Inactive' checkbox.
   2. Click Assemble to redesign the subject area.

      After the subject area has been redesigned, also redesign the execution plans which contain this subject area.

3. Run the ETL process for General Ledger Accounts.
4. In the RPD Physical Layer (using the Oracle BI Administration Tool), create additional aliases or change the names of the existing alias against the table W_HIERARCHY_D.

   For example, if the existing aliases are numbered 1 to 3, name the new alias Dim_W_HIERARCHY_D_ValueSetHierarchy4, and rename the existing aliases as described in the table below:

   Old alias name	New alias name
   Dim_W_HIERARCHY_D_ValueSetHierarchy1	Dim_Company_Hierarchy_D
   Dim_W_HIERARCHY_D_ValueSetHierarchy2	Dim_CostCenter_Hierarchy_D
   Dim_W_HIERARCHY_D_ValueSetHierarchy3	Dim_NaturalAccount_Hierarchy_D

5. Create a new alias against W_HIERARCHY_D and name the new alias Dim_LOB_Hierarchy_D.
6. In the Physical Layer of the Oracle BI Analytics Warehouse, use the Oracle BI Administration Tool to create joins in the physical layer from the new aliases that you created in the previous step, as follows:
   • Company Hierarchy will join to the segment column in W_GL_ACCOUNT_D that stores the Company segment.
     • Dim_Company_Hierarchy_D. HIER20_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG1_CODE
     • Dim_Compnay_Hierarchy_D.HIER_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG1_ATTRIB
   • Cost Center Hierarchy will join to the segment column in W_GL_ACCOUNT_D that stores the Cost Center segment.
     • Dim_CostCenter_Hierarchy_D. HIER20_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG2_CODE
     • Dim_CostCenter_Hierarchy_D.HIER_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG2_ATTRIB
   • Natural Account Hierarchy will join to the segment column in W_GL_ACCOUNT_D, that stores the Natural Account segment.
     • Dim_Naturalaccount_Hierarchy_D. HIER20_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG3_CODE
     • Dim_NaturalAccount_Hierarchy_D.HIER_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG3_ATTRIB
   • LOB Hierarchy will join to the segment column in W_GL_ACCOUNT_D that stores the LOB segment.
     • Dim_LOB_Hierarchy_D. HIER20_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG4_CODE
     • Dim_LOB_Hierarchy_D.HIER_CODE = W_GL_ACCOUNT_D.ACCOUNT_SEG4_ATTRIB

       NOTE:  Hierarchies are linked to HIER20_CODE, because it is leaf node of the hierarchy

7. In the Business Model Layer of the Oracle BI Analytics Warehouse, use the Oracle BI Administration Tool to create additional dimensions using the new alias.

   For more information about example dimensions that are installed with Oracle BI Applications, refer to the following dimensions in the Oracle Business Analytics Warehouse:

   • Dim - GL ValueSetHierarchy1
   • Dim - GL ValueSetHierarchy2
   • Dim - GL ValueSetHierarchy3
8. In the Business Model Layer of the Oracle BI Analytics Warehouse, use the Oracle BI Administration Tool to create additional hierarchies using the dimensions that you created in the previous step.

   For more information about example hierarchies that are installed with Oracle BI Applications, refer to the following hierarchies in the Oracle Business Analytics Warehouse:

   • GL ValueSetHierarchy1
   • GL ValueSetHierarchy2
   • GL ValueSetHierarchy3
9. In the Presentation Layer of the Oracle BI Analytics Warehouse, use the Oracle BI Administration Tool to drag the new hierarchies into the presentation folder.

   NOTE:  You can rename the hierarchies in the Presentation Layer if required.