Bookshelf Home | Contents | Index | PDF     

Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Configuring Siebel Financial Analytics > Process of Configuring Siebel Financial Analytics for Oracle 11i >

Loading Hierarchies for Siebel General Ledger Analytics

This task is a step in the Process of Configuring Siebel Financial Analytics for Oracle 11i.

The Siebel General Ledger Analytics application prepackages M_I_GL_HIER_DERIVE mapping to extract hierarchy definitions from Oracle 11i. To better assist you in loading your hierarchy information into the IA_HIERARCHIES table, Table 64 gives you descriptions of the columns presented in the table.

Table 64. Columns in the IA_HIERARCHIES Table
Column Name
Description

HIER_KEY

This surrogate key is generated for each hierarchy. This key must be linked to the HIERARCHY_KEY column in the IA_GL_ACCOUNTS table.

HIERARCHY_ID

This column uniquely identifies the hierarchy within a given category. For general ledger account hierarchies, the ID format is as follows:

'GL_HIER' || <top node of hierarchy> || HIER1_CODE... HIER10_CODE

HIER_CODE

This code represents a hierarchy. The hierarchy code defines the name of the hierarchy (for example, Balance Sheet, Profit and Loss, and so on).

HIER_NAME

This column provides the name of the entire hierarchy.

HIER_CAT_CODE

This code represents the category of the hierarchy. For general ledger account hierarchies, the category code is GL_HIER.

HIER_CAT_DESC

This is a description of the category to which the hierarchy belongs. For general ledger account hierarchies, the category description is General Ledger Hierarchy.

HIER_SUBCAT_CODE

This is the code of the subcategory of the hierarchy.

HIER_SUBCAT_DESC

This is a description of the subcategory of the hierarchy.

LEVEL_MIN_NUM

This is the starting number, which is set to 0. For general ledger accounts, this is the beginning account number that is associated with this hierarchy. All accounts including and between the LEVEL_MIN_NUM and LEVEL_MAX_NUM are included in this category.

LEVEL_MAX_NUM

This is the last number, which is set to 99999999. For general ledger accounts, this is the ending account number that is associated with this hierarchy. All accounts including and between the LEVEL_MIN_NUM and LEVEL_MAX_NUM are included in this category.

HIER[X]_CODE

Each of these code columns represents a level in the hierarchy, where [X] denotes the level. HIER1 is the highest level of the hierarchy, and HIER20 is the lowest. Each code name column (HIER[X]_NAME) corresponds to a code column (HIER[X]_CODE). The code of the highest level of the hierarchy is catenated with GL_ACCT to form the hierarchy of the IA_HIERARCHIES table.

HIER[X]_NAME

Each of these code name columns represents a level in the hierarchy, where X denotes the level. Hier1 is the highest level of the hierarchy, and HIER20 is the lowest. Each code name column (HIERX_NAME) corresponds to a code column (HIERX_CODE).

HIER_ATTR[X]_CODE and HIER_ATTR[X]_NAME

There are five sets of extension columns for code name pairs. The X represents the level, where the same level is shared by each code name pair, such as HIER_ATTR1_CODE and HIER_ATTR1_NAME

HIER_ATTR[X]_Text

These are extension columns to store additional text. There are three available for your use.

The Hierarchy ID is set for every unique hierarchy structure. The format is:

'GL_HIER' ~ HIER_CODE ~ HIER1_CODE... HIER20_CODE

where:

  • GL_HIER specifies that the hierarchy applies to general ledger accounts
  • HIER_CODE specifies the ID for a hierarchy structure
  • HIER1_CODE ~ HIER2_CODE... HIER20_CODE specifies each of the unique hierarchy levels in the given hierarchy structure

For example, if one of the Siebel General Ledger Analytics account hierarchies has the following structure:

Account (A)=> Current Asset (CA) => Fixed Asset (FA) => Balance Sheet (BS)

where the Balance Sheet hierarchy level is the highest level of the hierarchy, and BS denotes the hierarchy code, then set the Hierarchy ID to:

GL_HIER~BS~FA~CA~A

After you load the IA_HIERARCHIES table, you then must update the IA_GL_ACCOUNT table with the hierarchy information. Updating the IA_GL_ACCOUNT table requires a two-step process.

To load hierarchies in the IA_GL_ACCOUNTS table

  1. You must first determine which hierarchies apply to each General Ledger Account.

    To make this determination, you must modify the S_M_I_STAGE_GL_HIER_CODE_COMB_REF_[X] sessions, ([X] denotes the hierarchy number in the IA_GL_ACCOUNTS table). Because there are a maximum of six possible hierarchy structures in the IA_GL_ACCOUNT table, there are six sessions, where each session locates information for exactly one hierarchy structure, as shown in the following figure.

    Each of these sessions derives the General Ledger Account references for a particular hierarchy, which is later used to update the HIER[X]_KEY column in the IA_GL_ACCOUNTS table. The 'HIER[X]_KEY' refers to the HIER_KEY surrogate key in the IA_HIERARCHIES table.

  2. Load the appropriate hierarchy structure in the HIER[X]_KEY columns in the IA_GL_ACCOUNT table.

    To load this hierarchy structure, you must modify the MPLT_SAI_GL_ACCOUNTS_HIER_UPDATE Source Adapter mapplet in the M_I_GL_ACCOUNTS_HIER_UPDATE mapping in Oracle 11i.

To configure hierarchy code in the session

  1. In PowerCenter Workflow Manager, open the Configuration for Oracle Applications v11i folder.
  2. Expand Oracle11i_Finance_Application_GL_ACCOUNT_Hierarchy/W_O_GL_HIERARCHY_GL_ACCOUNTS_UPDATE/W_O_GL_HIERARCHY_GL_ACCOUNTS_HIER[X]_UPDATE.
  3. In Worklet Designer, right-click the W_O_GL_HIERARCHY_GL_ACCOUNTS_HIER[X]_UPDATE, and click Open.
  4. Double-click the appropriate session (S_M_I_STAGE_GL_HIER_CODE_COMB_REF_[X]) to open the Edit Tasks window.
  5. In the Transformations tab, edit the SQL Query field by replacing the default hierarchy code.

    The last statement in the SQL statement contains the hierarchy code. The hierarchy code determines the hierarchy for which the mapping session calculates the Siebel General Ledger Analytics references.

    For example, in the S_M_I_STAGE_GL_HIER_CODE_COMB_REF_1 session, the statement:

    TI_STAGE_GL_HIERRG.HIER_CODE='1042'

    calculates all the Siebel General Ledger Analytics account references for the hierarchy code '1042'. This code is the AXIS_SET_ID defined in the Oracle Applications source table, RG_REPORT_AXIS_SETS. Depending on which hierarchy you want to store in the HIER1_KEY column in the IA_GL_ACCOUNTS table, the corresponding HIER_CODE must be set in the SQL statement.

To configure the hierarchy code in the update mapping

  1. In PowerCenter Designer, open the Configuration for Oracle Applications v11i folder.
  2. In Mapplet Designer, open the MPLT_SAI_GL_ACCOUNTS_HIER_UPDATE Source Adapter mapplet.
  3. Double-click the Expression transformation to open the Edit Transformations window, and click the Ports tab.
  4. Edit the EXT_HIER[X]_ID port.

    The value of each field must be the same as the set in the corresponding mapping—M_I_STAGE_GL_HIER_CODE_COMB_REF_X. For example, if you want to change the EXT_HIER1_ID port, then change the expression:

    IIF(VAR_HIER_CODE='1042',INP_HIERARCHY_ID,SUBSTR(OD_HIER1_ID,1,INSTR(OD_HIER1_ID,INP_GL_ACCOUNT_NUM)-2))

    In this case, you replace the 1042 with the applicable code used in the HIER_CODE column of the IA_HIERARCHIES table.

  5. Validate and save your changes to the repository.
    
Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide