B.2.79 How to Configure GL Account Hierarchies using FSG definitions for E-Business Suite

You must configure GL account hierarchies if you are deploying Oracle Financial Analytics, Oracle Procurement and Spend Analytics, and Oracle Supply Chain and Order Management Analytics.

For information on how to configure Hierarchies using GL Accounting flexfields value sets definitions, see Section B.2.20, "How to Configure GL Account and GL Segments for Oracle E-Business Suite".

If you need to define GL account hierarchies based on multiple segments within a chart of accounts, then you can use the Oracle FSG report definition in E-Business Suite to define them.

You should first use the Oracle FSG form to define a row set or a column set, then Oracle BI Applications will extract the row set or column set definition and convert them into hierarchies.

Oracle FSG hierarchies are extracted from following E-Business Suite source tables:

• RG_REPORT_AXIS_CONTENTS
  This table defines the relationship between the FSG report axis and GL code combinations. The GL code combinations with segment values within the value range defined for that axis are categorized as children of that axis.
• RG_REPORT_AXIS_SETS
  This table stores the information for each of the row set or column set you defined. There is one record in this table for each row or column set you defined. Each row includes an axis set identifier, a row or column set name, and a structure identifier to assign a specific chart of accounts to the row set or column set.
• RG_REPORT_CALCULATIONS
  This table stores formulas for calculating each row or column in the row or column set. An example of a row calculation might be to sum up the amount from the previous five rows. An example of a columns calculation might be to calculate column five by subtracting column four from column three.

For example, in Income Statement, 'Net Income' is the calculation result of 'Gross Profit from Revenue' minus 'Total Expense'. When converting to hierarchy, Net Income becomes the parent of 'Gross Profit from Revenue' and 'Total Expense'. Therefore, hierarchy can be derived based on the information in RG_REPORT_CALCULATION.

The following diagram shows an example hierarchy, with the top level Net Income node having two child nodes, Total Expense, and Gross Profit from Revn, and the Total Expense node having two child nodes, Operating Expense, and Depreciation Expense.

The following diagram shows how an income state is derived from a hierarchy:

This hierarchy would be converted into a flattened hierarchy and stored in W_HIERARCHY_D in the following format:

Table B-88 Example of Flattened Hierarchy Stored in W_HIERARCHY_D

HIER Name	HIER1	HIER2	HIER3	HIER4	HIER20
Income Statement	Net Income	Gross Profit...	Gross Profit...	Gross Profit...	Gross Profit...
Income Statement	Net Income	Total Expenses	Operating Expenses	Operating Expenses	Operating Expenses
Income Statement	Net Income	Total Expenses	Depreciation Expense	Depreciation Expense	Depreciation Expense

Fact tables join to the W_HIERARCHY_D table through the GL Account dimension table (W_GL_ACCOUNT_D).

The W_GL_ACCOUNT_D table contains six fields (HIER1_WID, HIER2_WID, HIER3_WID, ...., HIER6_WID), which are foreign keys to the W_HIERARCHY_D.row_wid. Therefore, each General Ledger Code combination can participate in up to six different hierarchies. You can decide which of the six hierarchies to drill on based on the column you use to join to W_HIERARCHY_D. For example, if you want to drill using the third hierarchy, you use W_GL_ACCOUNT_D.hier3_wid = W_HIERARCHY_D.row_wid.

Note:

Mathematical operators, such as '+', '-', '*', '/' (addition, subtraction, multiplication, division, and so on) are not extracted from the FSG definitions. For example, both A + B = C and A - B = C would give the same hierarchy, with a node C having two child nodes A and B, as shown in the following diagram:

About the ETL Process for Oracle FSG Report

Before you run the ETL process for GL accounts, you must specify the hierarchies that you want to reference. To specify the FSG hierarchies that you want to reference, use the file file_gl_hierarchy_assignment_ora.csv.

Note:

The configuration file or files for this task are provided on installation of Oracle BI Applications at one of the following locations:

Source-independent files: <Oracle Home for BI>\biapps\etl\data_files\src_files\.

Source-specific files: <Oracle Home for BI>\biapps\etl\data_files\src_files\<source adaptor>.

Your system administrator will have copied these files to another location and configured ODI connections to read from this location. Work with your system administrator to obtain the files. When configuration is complete, your system administrator will need to copy the configured files to the location from which ODI reads these files.

Figure B-4 Example values in file_gl_hierarchy_assignment_ora.csv

In this file, for each chart of accounts, you can specify six FSG hierarchies, using axis_set_id, which is a column from the RG_REPORT_AXIS_SETS table. It is the unique ID of a row set or column set you want to store in the GL account dimension table for the code combinations that belong to that chart of accounts.

The DATASOURCE_NUM_ID field specifies the data source to which the configurations apply. If you have multiple source systems, there might be a chart of accounts across the multiple source systems with the same ID. Therefore, you must use the DATASOURCE_NUM_ID value to distinguish between them.

For example, suppose you have an income statement FSG report and a balance sheet FSG report and you want to input both of their hierarchy structures into the data warehouse. Oracle BI Applications assumes that both reports are derived from the same set of GL accounts with CHART_OF_ACCOUNTS=101. The axis_set_id of the income statement is 1001, and for the balance sheet, it is 1003. The DATASOURCE_NUM_ID for this application is 2.

In addition, for those GL accounts that belong to the two reports, assume you want to associate their HIER1 column (in GL_ACCOUNT_D) with the income statement hierarchy structure and HIER3 column with balance sheet hierarchy structure.

In this case, you would add one row into file_gl_hierarchy_assignment_ora.csv with fields set as follows:

CHART OF ACCOUNTS - 101

HIER1_AXIS_SET_ID - 1001

HIER3_AXIS_SET_ID - 1003

DATASOURCE_NUM_ID - 2

(Leave the other row values blank.)

This row indicates that for all of the GL accounts with CHART_OF_ACCOUNTS=101 and DATASOURCE_NUM_ID=2, assigning hierarchies with axis_set_id=1001, null, 1003, null, null, null to HIER1~HIER6 columns respectively. Therefore, after extraction and loading, for those affected GL account rows, HIER1 column will be the foreign key to the income statement hierarchy row ID in W_HIERARCHY_D, and HIER3 column will be the foreign key to the balance sheet hierarchy row ID in W_HIERARCHY_D.

Note: Axis_set_id must be specified in file_gl_hierarchy_assignment_ora.csv for Financial Analytics to load the hierarchies.

To set up hierarchies with FSG Report Definition:

1. Configure the file_gl_hierarchy_assignment_ora.csv file to specify the hierarchies you want to reference for each CHART_OF_ACCOUNTS, as follows:
   1. Edit the file file_gl_hierarchy_assignment_ora.csv.
      Note:

      The configuration file or files for this task are provided on installation of Oracle BI Applications at one of the following locations:

      Source-independent files: <Oracle Home for BI>\biapps\etl\data_files\src_files\.

      Source-specific files: <Oracle Home for BI>\biapps\etl\data_files\src_files\<source adaptor>.

      Your system administrator will have copied these files to another location and configured ODI connections to read from this location. Work with your system administrator to obtain the files. When configuration is complete, your system administrator will need to copy the configured files to the location from which ODI reads these files.
   2. Specify the segments that you want to analyze.
   3. Save and close the file.
2. Configuration provided by default in the BI metadata repository relating to GL Account Hierarchy using FSG:
   1. Physical table aliases for GL Account Hierarchy using FSG (6) are created and joins created to GL Account Dimension table (Dim_W_GL_ACCOUNT_D).
      
   2. Logical tables for the above six dimension hierarchy physical tables are created along with the BMM joins to relevant logical facts.
   3. Appropriate logical Levels and content filters have been set for the 6 FSG Logical dimensions that are provided by default.
      
   4. All relevant Logical Table sources of the Logical Fact tables have been set with necessary Aggregation content for the six Logical dimensions that are provided by default.
      
3. The following additional configuration might be needed by the users to expose the necessary attributes relating to FSG.
   1. Using Oracle BI EE Administration Tool, in the Presentation layer of the Oracle BI Repository, drag the new hierarchies from appropriate Logical dimensions into the Presentation folder.
   2. If required, then rename the hierarchies in the presentation layers.