Changes to the business model and mapping layer are made in the Business Model and Mapping layer of the Oracle BI repository.
The following sections describe the mapping of the Dimensions and then the Facts.
You can map attributes from the Essbase cube to the existing model.
See:
After completing the attribute mapping or dragging procedure,
This section describes the mapping of each dimension in Essbase to the existing Business Model and Mapping Layer. From each Dimension in Essbase, the attributes must be dragged into the corresponding Dimension in the Business Model and Mapping layer.
Amount Type in the physical layer must be mapped to Dim-Amount Type in the logical layer.
Date-Fiscal Calendar in physical layer must be mapped to Dim-Date Fiscal Calendar in the logical layer. The mappings of Date-Fiscal Calendar Dimension are show below:
Note: | The Fiscal year attribute must be shown in the Expression column of the preceding figure. Here, VF_USA_Accounting_Flexfied is the name of the cube. In addition, the Fiscal Calendar Name should be determined as explained in section Balance Fact–Modeling and typed in as well. |
Ledger Dimension attributes are mapped to Dim-Ledger and Dim-Ledger Set in the Business Model and Mapping layer. Ledger dimension mapping is shown below:
Note: | Chart of Account should be determined as explained in Balance Fact–Modeling. |
Ledger Set dimension mapping is shown below:
The mapping starts from the top level in the Essbase Dimension hierarchy to the Business Model and Mapping layer. For example, in the Business Model and Mapping layer:
XSegment Tree Filter attribute from the Essbase Dimension maps to the XSegment Tree Filter attribute
Level X Code attribute of Essbase Dimension maps to Segment Level Xcode
Balancing Segment Level 31 Code in Essbase Dimension maps to Balancing Segment Level 31 Code attribute
Balancing Segment Level 0 Code in Essbase Dimension maps to Balancing Segment Code attribute
XSegment Level 0 Code in Essbase Dimension maps to the Leaf level attribute X Segment Code
Because the Essbase hierarchy does not have 32 levels, and the Logical Dimension has 32 levels, the intermediate levels must be mapped to the Leaf level from Essbase hierarchy. This is true for all the segment Dimensions. For example, if the Essbase Dimension Hierarchy has Level 31 code, Level 30 code, Level 29 Code, and Level 0 Code, then all the Level 0 code of Essbase Dimension must be mapped to all the attributes from Xsegment Level 28 Code until Leaf level as shown in the figure below.
Balancing Segment in the Essbase cube should be mapped to Dim - Balancing Segment in the Business Model and Mapping layer also as shown below:
Similar mappings must be done for Cost Center Dimension in the Essbase cube that gets mapped to Dim - Cost center in the logical layer and Account Segment in Essbase cube that gets mapped to the Dim - Natural account Segment.
For Non Qualified Segment dimensions, the Essbase dimensions can be mapped to any of the GL Segment dimensions Dim-GL Segment1 to Dim-GL Segment10. For example, Product Dimension in Essbase is mapped to Dim - GL Segment1 as shown below:
The currency dimension in Essbase maps to Dim - Currency in logical layer as shown below:
The Currency Type dimension in Essbase maps to Dim - Currency Type in logical layer as shown below:
Dragging the database (just below the Essbase cube) from the physical layer into the Business Model and Mapping layer model (Core). Drag and drop functionality maps to the existing dimensions of the cube if the Dimension is already present in the Business Model and Mapping layer. If not, it creates a Dimension.
If you have created a hierarchy with the same name as that of the renamed dimensions, you must delete the following hierarchies from the Business Model and Mapping layer:
Dim-Date Fiscal Calendar
Dim-Natural Account Segment
Dim-Balancing Segment
Dim-Currency
Dim-Cost Center
Dim-Amount Type
Currency Type
All the Non Qualified Segment Dimension hierarchies (Dim-Segment X)
Note: | Because of dragging, within each mapped dimension, additional attributes were created. You must delete them as well as the attributes with names similar to Dim-X. XX-member, xx-Member Key should be deleted also. |
For example, before dragging, Dim - Date Fiscal Calendar has the following attributes:
However, after dragging, the following attributes are created and must be deleted:
The lists of attributes created for each dimension, which must be deleted, are displayed in the following figures:
Currency:
Amount Type:
Segment Dimensions:
Note: | This example of Balancing Segment Hierarchy stops at Level 27 and then has the leaf level. However, there may be more or fewer attributes, depending on the hierarchy of the Segment Dimensions. |
Because of dragging, a dimension Currency Type is created in the Business Model and Mapping layer apart from the existing Dim - Currency Type.
Also, a new hierarchy is created inside the existing Currency Type dimensions hierarchy and must be deleted.
Attributes of Currency Type Dimension must be manually mapped. Gen2, Currency Type and Gen3, Currency Type (attributes) in the physical layer that are highlighted in the figure below must be mapped to Currency Type and Converted Currency Type attributes in Dim - Currency Type in the Business Model and Mapping layer.
After the cube is dragged and dropped, it creates a new dimension called Ledger and also creates a new hierarchy inside the Ledger Hierarchy starting from Gen1, Ledger. You must manually delete this new Dimension (Ledger) and its corresponding hierarchy (hierarchy inside Ledger starting from Gen1.Ledger) from the Business Model and Mapping layer, because this dimension is manually mapped.
In the physical layer, the Ledger Dimension present in the Essbase cube should be mapped to Ledger Dimension and LedgerSet Dimension in the Business Model and Mapping layer.
To map the Ledger and LedgerSet Dimensions in the Business Model and Mapping layer:
Drag the Gen2,Ledger(attribute), Gen2.Legder_Id_Alias from Gen2,Ledger (Generation) of the Essbase Cube to the LedgerSet Name, LedgerSet Key of the Dim - LedgerSet.
Drag the Gen3,Ledger (attribute), Gen3,Ledger_Id_Alias and Gen3, Ledger- Default from Gen3, Ledger (Generation) of the Essbase cube to the Ledger Name, Ledger Key, and Ledger Description attributes of Dim - Ledger.
You must map the Essbase Logical Table Source (LTS) to Chart of Account attribute in Ledger Dimension for redirection in the fact.
To map the Chart of Accounts attribute, in the Column Mapping tab of Essbase LTS, enter <COA name> for the cube. This is the same name used in the Fact’s Fragmentation content. See step 3 under the section Balance Fact–Modeling.
You must create additional mappings that need to be done in Dim - Date Fiscal Calendar for the proper redirection and federation to Essbase cube sources. Similar to the mapping for Chart Of Accounts attributes, you must do a few manual mappings for the Time dimension.
To perform additional mappings for the time dimension:
In the column mapping, for the Essbase cube LTS, the Calendar Name of the cube must be written directly. To obtain the Calendar Name of the cube, see The Business Model and Mapping Layer Changes.
Drag the Fiscal Year attribute from the physical layer of the cube into the Fiscal Year Number and Fiscal Year Wid. You must enter and map Fiscal Year as shown below.
A dimension called Scenario is created in the Business Model and Mapping layer by dragging the cube. You must manually delete this dimension and its hierarchy, because only Actual Balances is supported, and this condition is added in the Fact. See 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:
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.
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.
In addition, for segment dimensions with a ragged hierarchy, select the Ragged option in the Hierarchy in the Logical Dimension-Balancing Segment dialog box.
Redirect the query to a particular cube, because there can be many cubes, with each specific a chart of accounts and Accounting Calendar combination:
Select Logical tables from the Type.
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>.
Select These sources should be combined with other Sources at this level below the fragmentation Content.
In the Where condition, select Ellipse and add the condition cubename.database.Scenraio.Gen2.Scenario=‘Actual’.
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
In all Logical Facts and Dimensions in which you map Essbase cubes, set the priority of the database (cube) logical table sources (LTS) to 5. This guideline applies to the following Logical Facts and Dimensions:
Dim-Ledger
Dim-Ledger Set
Dim-Date-Fiscal Calendar
Dim-Amount Type
Dim-Currency
Dim-Currency Type
All Segment dimensions
In all dimensions containing both View Object (VO) sources and General Ledger balances cube sources, the VO source should be placed first among Oracle Fusion Transactional Business Intelligence logical table sources (LTS), followed by the General Ledger balances cube LTS. This is to ensure that in case of a dimension-only query, values are pulled from the VO source. This guideline applies to the following dimensions:
Dim-Ledger
Dim-Ledger Set
Dim-Date-Fiscal Calendar
Dim-Currency
All Segment Dimensions mapped both from VO and Essbase sources
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
Essbase Dimensions (Dim-Amount Type and Dim-Currency Type) and Facts (Fact-Fins-Essbase Balance), have BalancePVO as the source. This Logical Table Source (LTS) must be disabled so that querying the Fact queries the Essbase cube sources.
Note: | In all of the Logical Facts and Dimensions where Essbase cubes are mapped, the priority of the database (cube) LTS should be set to 5. The dimensions and facts are shown in the following list: |
Dim-Ledger
Dim-Ledger Set
Dim-Date-Fiscal Calendar
Dim-Amount Type
Dim-Currency
Dim-Currency Type
All Segment dimensions
In all dimensions where there are both view object sources and Essbase sources, the view object source should be placed first among the Transactional Business Intelligence LTS(s) followed by the Essbase cube LTS(s). This ensures that, in a dimension-only query, values are pulled from the view object source.
This guideline is applicable to the following dimensions:
Dim-Ledger
Dim-Ledger Set
Dim-Date-Fiscal Calendar
Dim-Currency
All Segment dimensions mapped from both view object and Essbase sources