Balance Fact–Modeling

With dragging, a Fact database is created in the Business Model. You must delete the Mapping layer and map the measures to the existing Fact-Fins Essbase Balance.

  To map the measures that were created in the physical layer:

  1. Drag the following measures from the physical layer:

    • Beginning Balance

    • Beginning Balance Cr

    • Beginning Balance Dr

    • Ending Balance

    • Ending Balance Cr

    • Ending Balance Dr

    • Period Activity

    • Period Activity Cr

    • Period Activity Dr

    into:

    • Fact-Fins-Essbase Balances

    • Existing Beginning Balance Credit

    • Beginning Balance Debit

    • Beginning Balance

    • Ending Balance

    • Ending Balance Credit

    • Ending Balance Debit

    • Period Net Activity

    • Period Net Activity Credit

    • Period Net Activity Debit

    An LTS called database is created in the Fact.

  2. Double-click the database and select the Content tab.

  3. In the Fragmentation content, select Ellipse.

    Note:

    To obtain the COA name and the Calendar name, query the COA ID and Period Set Name attributes of the GL_BALANCES_CUBES table.

    With the COA Id, you can get the COA name from fnd_kf_str_instances_b and fnd_kf_str_instances_TL tables.

    The COA name and the Fiscal Calendar name should exactly match the names that you mapped to Chart Of account attribute in Ledger Dimension and Fiscal Calendar Name in Dim - Date fiscal Calendar Dimension.

  4. In addition, for segment dimensions with a ragged hierarchy, select the Ragged option in the Hierarchy in the Logical Dimension-Balancing Segment dialog box.

  5. Redirect the query to a particular cube, because there can be many cubes, with each specific a chart of accounts and Accounting Calendar combination:

    1. Select Logical tables from the Type.

    2. Select Dim - Date Fiscal Calendar from the list of tables and Fiscal Calendar Name to get the condition

      Core.Dim-Date Fiscal Calendar.Fiscal Calendar Name=<Enter the Calendar name that is associated with the cube>AND Dim-Ledger.Chart Of Accounts=<Enter the COA name>.

  6. Click OK to return to the Content tab.

  7. Select These sources should be combined with other Sources at this level below the fragmentation Content.

  8. In the Where condition, select Ellipse and add the condition cubename.database.Scenraio.Gen2.Scenario=‘Actual’.

  9. In addition, set all Logical Levels of the Dimensions of the cube as follows:

    • Ledger-Detail

    • Date-Fiscal Calendar–Fiscal Period

    • Amount Type–Detail

    • Currency–Detail

    • Currency Type-Detail

    • All COA Segments–Level0 or Detail

    • Ledger Set–Detail

  10. Click OK.

    Note:

    In all of the Logical Facts and Dimensions where you map the Essbase cubes, set the priority of the database (cube) LTS to 5. The following is the list of Dimensions and Fact:

    • Dim-Ledger

    • Dim-Ledger Set

    • Dim-Date-Fiscal Calendar

    • Dim-Amount Type

    • Dim-Currency

    • Dim-Currency Type

    • All Segment dimensions

Sample COA Query

The following query can be executed to get the Chart of Account name:

Select Period_set_name, chart_of_accounts_id from gl_balances_cubes where Application_name =<Cube Name>
Select structure_instance_code from fnd_kf_str_instances_b where application_id = 101 and Key_flexfield_code = 'GL#' and structure_instance_number = <chart_of_accounts_id from Query1>.
Select name from fnd_kf_str_instances_tl where application_id = 101 and Key_flexfield_code = 'GL#' and structure_instance_code = < structure_instance_code From Query 2> and language = <UserLang>

For example,

Select Period_set_name, chart_of_accounts_id from gl_balances_cubes where Application_name =   'OperationsAccountingFlex2' This results in chart_of_accounts_id = 101
Select structure_instance_code from fnd_kf_str_instances_b where application_id = 101 and Key_flexfield_code = 'GL#' and structure_instance_number = 101 This results in structure_instance_code as OPERATIONS_ACCOUNTING_FLEX
Select name from fnd_kf_str_instances_tl where application_id = 101 and  Key_flexfield_code = 'GL#' and structure_instance_code = 'OPERATIONS_ACCOUNTING_FLEX'  and language = 'US