This chapter describes the implementation steps specific to the Oracle Profitability Manager application.
This chapter covers the following topics:
Oracle Profitability Manager implementation is similar to that of other Oracle Financial Services applications. You need to follow all the common implementation steps. See: Implementing Oracle Financial Services Applications.
In addition, you need to follow these Oracle Profitability Manager implementation-specific steps:
Set Up Security Folders. See:Setting Up Security Folders.
Set Up Oracle Workflow. See: Configuring Workflow.
Set up Composite Dimensions. See: Working with Composite Dimensions (Activity and Cost Object).
See also: Working with Statistic Definitions.
Specify Column Population template. See: Working with Column Population Templates.
Prior to implementing Profitability Manager, give careful consideration to the following:
Data sources (both Oracle and non-Oracle).
Dimensions to be used. The application comes with a set of predefined dimensions and 10 user-defined dimensions. Note that if you intend to use Activity-Based Management functions, certain dimensions are required. For more information, see “Working with Composite Dimensions (Activity and Cost Object)”.
Note: Profitability Manager no longer requires the same dimensionality or processing keys on all user data tables. This means that it is possible that Transaction tables or Account tables may have dimension columns that are not populated with a value. Be mindful of this fact when designing processing keys for tables. When a user maps data from an Account table to the FEM_BALANCES table, he or she is responsible for supplying dimension values for dimension columns that are NULL in the source table but are part of the processing key in the target table and are designated NOT NULL.
Tables to be used. Profitability Manager leverages fact tables seeded in the Enterprise Performance Foundation, including a seeded ledger and other tables to support profitability analysis, activity-based management functions, and data loading. Some examples follow:
Ledger: FEM_BALANCES
Account: FEM_MORTGAGES, FEM_CONSUMER_LOANS
Transaction: FEM_TRANS_CREDIT_CARDS, FEM_TRANS_MORTGAGES
Lookup Statistic: FEM_ORG_STATS, FEM_CRNCY_STATS
Data Loading: FEM_BAL_INTEFACE_T, FEM_MORTGAGES_T
You can also define your own tables. Note that all tables must be properly registered. For more information, see “Register Tables and Columns”, Oracle Profitability Mamanger User's Guide.
Naming conventions. Users will be selecting objects such as folders, conditions, and business rules in the Profitability Manager interface. They must be able to clearly identify available options.
Processing frequency.
Calendar periods needed.
Financial reporting conventions.
Financial reporting requirements.
Mapping rules perform calculations for allocation of income statement and balance sheet items and enable cost object mapping to activity rates, direct expenses, and activity costs. As part of table registration, you must specify Input/Output columns for tables selected for mapping.
Input columns are used by mapping rules to identify which columns act as sources for dimension values. Output columns are used to identify which columns can act as targets for dimension values. Output columns must also appear in the processing key (The processing key is also the primary key for the table. Requiring output columns to be in the processing key guarantees that all rows written by the Common Calculation Engine capture all dimension values written for any output column).
When a column is defined as a Mapping Output column on a table, a line for that column will appear on any mapping rule Debit or Credit which uses that table (the column description is used, which normally corresponds to the name of the dimension attached to the column). That line minimally allows a user to set a specific dimension value for that column on output. If a column which corresponds to the same dimension is defined as a Input column on a table used in either a Source or Percentage mapping formula type, then the mapping rule Debit or Credit will also contain a Same As entry for the dimension.
For more information about mapping rules, see “About Mapping Rules”, Oracle Profitability Manager User's Guide.
Mapping input columns are columns which:
Exist on the registered table.
Are value set enabled (with the exception of currency code).
Are active and have not been disabled.
Are not special handling columns.
Mapping Input columns do not have to be included in the processing key for the table.
Special handling columns and non-value set enabled columns include the following:
Cal Period ID
Dataset Code
Ledger ID
Source System Code
ID Number
Created by Object ID
Activity ID
Cost Object ID
Created by Request ID
Creation row sequence
Currency type code
Last updated by object ID
Last updated by request ID
All balance or rate columns (You select the input column as part of the mapping rule.)
These rules are valid for Balance, Transaction and Account tables.
Mapping Output columns are columns which:
Exist on the registered table.
Are included in the processing key.
Are active.
Are not a special handling column, as previously defined.
All columns in the processing key for FEM_BALANCES must be defined as mapping output columns.
Mapping output columns are not applicable for Customer Account Profitability tables.
The additional columns in the processing key for Customer Transaction Profitability tables (i.e. the "Transaction" dimension) are also required to be a mapping output column.
Following are examples for mapping Input and Output columns.
Assume the following mappings for Input and Output columns:
Financial Element is defined as an Output column on FEM_BALANCES.
Financial Element is not defined as an Input column on Mortgages.
A Mapping rule is defined which specifies Mortgages in the Source and FEM_BALANCES in the Debit.
You would expect to see the following in the Debit of the Mapping rule:
A line for Financial Element.
The drop-down for Financial Element only allows the user to specify a specific dimension value for Financial Element.
Assume the following mappings for Input and Output columns:
Product is defined as an Output column on FEM_BALANCES.
Product is defined as an Input column on Mortgages.
A Mapping Rule is defined which specifies Mortgages in the Source, and FEM_BALANCES in the Debit.
You would expect to see this in the Debit of the Mapping rule:
A line for Product.
The drop-down for Product allows the user to specify either a specific dimension value for Product, or Same As Source for Product.
Assume the following mappings for Input and Output columns:
Financial Element is defined as an Output Column on FEM_BALANCES.
Financial Element is defined as an Input Column on FEM_BALANCES.
Financial Element is not defined as an Input Column on Mortgages.
A Mapping Rule is defined which specifies FEM_BALANCES in the Driver and Debit, and Mortgages in the Source.
You would expect to see this in the Debit of the Mapping Rule:
A line for Financial Element.
The drop-down for Financial Element allows the user to specify either a specific dimension value for Financial Element, or Same As Pct for Financial Element.
Activity and Cost Object are composite dimensions, comprised of other dimensions. If your organization will be using Activity-Based Management functions, you must set up these dimensions.
You must have the Profitability Manager with Activity-Based Management - Administrator responsibility to set up composite dimensions.
Give careful thought to composite dimension definitions. Once set up, they are frozen and cannot be edited.
The Activity dimension holds repeatable tasks in relation to other dimensions such as Organization, Channel, Customer, and so forth. For example, the activity of "depositing a check" is the act of "depositing" applied to an acted upon item — in this case, a check.
You set up the Activity dimension by specifying its component dimensions. Task is a required component. You can optionally include any of the other dimensions in the FEM_BALANCES table. For example, if a company performs the same task across many departments, you would specify the Task and Company Cost Center Organization dimensions as the definition for the Activity dimension.
Note: Ensure that the processing key of the FEM_BALANCES table includes all of the component dimensions that you specify for the Activity dimension definition. If they are not included, then Activity rules will fail.
Follow these steps to set up the Activity dimension.
Access Profitability Manager with the Profitability Manager with Activity-Based Management - Administrator responsibility.
Navigate to Administration > Registration.
On the Registration tab, choose Activity and Cost Object Definition.
The Composite Dimension Definition page opens.
Click the Activity link.
The Activity Dimension Definition page opens. Available component dimensions are listed in the Available box.
Click Update to enable dimension selection.
Select dimensions from the Available box and move them to the Selected box. At minimum, you must choose the Task dimension.
Click Freeze to finalize the dimension definition.
Cost Objects are multi-dimensional entities that describe a cost. The intersection of component dimensions such as Organization, Product, and Customer results in the creation of a composite dimension that uniquely identifies a Cost Object.
You define the Cost Object dimension by specifying its component dimensions. Financial Element and Ledger are required components. A third dimension of your choice is also required. For example, if you want to define cost objects based on your product master, you would include Financial Element, Ledger, and Product as the definition for the Cost Object dimension.
Once you are satisfied with the dimension definition, you freeze it. Thereafter, you can only view the definition.
Note: Ensure that the processing key of the FEM_BALANCES table includes all of the component dimensions that you specify for the Cost Object dimension definition. If they are not included, then the Cost Object Unit Cost rule will fail.
Follow these steps to set up the Cost Object dimension.
Access Profitability Manager with the Profitability Manager with Activity-Based Management - Administrator responsibility.
Navigate to Administration > Registration.
On the Registration tab, choose Activity and Cost Object Definition.
The Composite Dimension Definition page opens.
Click the Cost Object link.
The Cost Object Dimension Definition page opens. Available component dimensions are listed in the Available box.
Click Update to enable dimension selection.
Select dimensions from the Available box and move them to the Selected box. You must choose Financial Element, Ledger, and at least one other dimension.
To finalize the dimension definition, click Freeze.
The Profitability Manager mapping function includes a Retrieve Statistic formula type. This formula multiplies source data by reference values sourced from a statistic table. You must set up the statistics that will be used in this formula.
You define a statistic by designating a table as a lookup or statistics table, then designating the columns to be used as lookup fields. One or more dimensions from a FEM data table can be used in the lookup process, providing single or multiple dimension lookups.
For more information, see “Working with Statistic Definitions”.
When users define a Retrieve Statistic formula type for a mapping rule, they choose a Statistic. The Statistic definition is composed of a lookup table selection and a specification that joins the table's columns to columns in a Profitability table. The lookup columns can be joined directly. Alternatively, they can be set to a constant value to provide a way to define the lookup rows relevant to the statistic.
When the mapping rule runs, rows from the initial data source that match the lookup key columns will be processed, and the value in the row that is designated as the Lookup Return Column will be used as the Retrieve Statistic value.
To create a Statistic, you choose the source table, specify a Statistic lookup table, and join the source table to it.
Note: The source table must correspond to the Input Data Source for the mapping rule that will use the statistic (See “Mapping Rule Formula Type: Retrieve Statistic” , Oracle Profitability Manager User's Guide). The lookup table must be classified as a Statistic classification type using the Administration > Table Registration function.
You then specify how the columns in the lookup table will be used in the lookup. If a lookup table column is part of the lookup key, the Join With column is set to Source Table Column and the Value column contains the source table dimension that will be included in the lookup key. If a lookup table column is not part of the lookup key, you set the Join With column to Constant Value and enter the constant value that is common for all lookup rows in the lookup set within the lookup table.
Designate the Lookup Column name in the list of values. You can apply a condition to filter the rows in the lookup table that will be available to the Statistic lookup set. For example, if you want to match rows in the source table to rows in the lookup table by Ledger and by Company Cost Center Organization, you would set those two lookup table columns to join with the matching columns in the source table (Dataset and Company Cost Center Organization). Assuming that you gather statistics yearly, you would not want to look up Calendar Period, so you would set the Calendar Period lookup table column to the Constant Value that equals the date that the statistic rows were gathered in the lookup table.
Note: The lookup column on the statistic table must be classified as a CPM data type of Statistic, Monetary Balance or Rate.
Follow these steps to create a Statistic.
Access Profitability Manager with the Activity-Based Management responsibility or the Profitability Manager responsibility.
Navigate to Business Rules > Statistics.
The Statistics page opens.
Click Create.
In the Create Statistic area, identify the statistic.
Enter a name.
Choose the Lookup Table. Only tables that have been registered as Statistic classification type are available.
Choose the Source Table. Your selection should depend on the mapping rule that will use this statistic. Note that when a user chooses a Statistic, only those Statistics that have been defined for the initial data source selected for the rule will be available.
In the Join Tables area, specify how to join to each column in the lookup table to the source. Select a join option: Source Table Column or Constant Value.
Source Table Column — Specifies the source column that is part of the lookup key.
Constant Value — Specifies a value that is a constant and that helps define the lookup set.
Populate the Value field with either the Source Table Column name or the Constant Value.
In the Lookup Return column, choose the column that contains the value that will be returned to the mapping rule when the lookup key is found.
(Optional) Apply a condition to filter the data.
Click Apply.
Once a rule version that uses a Statistic has been run, the Statistic is locked for that rule. However, the Statistic can be used in new rules and versions. Note that you can also add data to the underlying lookup table. However, if the non-lookup columns were to change, you would have to duplicate the Statistic before you could include it in the new rule version.
Follow these steps to update a Statistic for a rule that has not been run.
Navigate to Administration > Registration > Statistics.
The Statistics page opens.
Search for a Statistic. You can search by Name, Source Table, Lookup Table, and Source column. Use % as a wildcard.
The list displays Statistics that match your criteria.
Identify the Statistic to update and click its Update icon.
You can modify the name and description. You can also modify information for join columns, lookup return columns, and condition.
Click Apply.
Duplicating a Statistic allows you to quickly create a Statistic definition based on an existing definition.
Follow these steps to duplicate a Statistic.
Navigate to Administration > Registration > Statistics.
The Statistics page opens.
Search for a Statistic. You can search by Name, Source Table, Lookup Table, and Source column. Use % as a wildcard.
The list displays Statistics that match your criteria.
Identify the Statistic to duplicate and click its Duplicate icon.
Enter a name and description for the new Statistic.
Click Apply.
You can delete a Statistic before a Retrieve Statistic mapping rule that contains the Statistic has run. Once a rule has run, the Statistic is locked and cannot be deleted.
Follow these steps to delete a Statistic.
Navigate to Administration > Registration > Statistics.
The Statistics page opens.
Search for a Statistic. You can search by Name, Source Table, Lookup Table, and Source column. Use % as a wildcard.
The list displays Statistics that match your criteria.
Identify the Statistic to delete and click its Delete icon.
You are prompted to confirm the deletion.
When users run an Account Consolidation rule or a Profit Aggregation rule, they select a Column Population template. For account consolidation, the Column Population template specifies how the columns in the source Account table will map to the target FEM_CUSTOMER_PROFIT table. For a Profit Aggregation rule, the Column Population template specifies how balance columns will aggregate; it is run from the FEM_CUSTOMER_PROFIT table to the FEM_CUSTOMER_PROFIT table itself.
You must define a Column Population template for each Account table. Provide a name and description that will make it clear to users which template they are choosing when building Account Consolidation and Profit Aggregation rules.
Some columns are assigned automatically and are designated as System Reserved Column Mappings. Other columns may be added as User Defined Column Mappings. You must populate all required columns in the template.
When specifying a Column Population template for account consolidation, you must include the Business Relationship column, which should be mapped as an Attribute Lookup. (Business Relationship is a required attribute for the Customer dimension.)
Note: Each table and column that you specify in the Column Population Template must be registered.
Follow these steps to create a Column Population template.
Access Profitability Manager with the Profitability Manager Administrator or the Profitability Manager with Activity-Based Management - Administrator responsibility.
Navigate to Administration > Registration.
On the Registration tab, choose Column Population Template.
The Column Population Template Rules selector opens.
On the Column Population Template Rules page, click Create.
A new Column Population Template Rule page opens.
Name and describe the Column Population template and specify the source table.
Choose a folder.
Enter a name.
(Recommended) Enter a description.
Specify the source table. If the template will be used within an Account Consolidation rule, this is an Account table. If the template will be used within a Profit Aggregation rule, this is the FEM_CUSTOMER_PROFIT table.
Specify the target table. This is always FEM_CUSTOMER_PROFIT.
Click Continue.
The Column Template Version page opens.
On the Column Population Template Version page, identify the template version.
Enter a version name.
Specify Effective Start Date.
Specify Effective End Date.
(Recommended) Enter a description.
Click Continue.
The Column Population Template Details page opens.
In the User Defined Column Mappings section, map source to target columns.
In the Population Method field, choose the population method for a column. Source columns and Lookup columns must match. You can choose one of the following:
Constant VARCHAR — Enables you to set a specific character value in the Target column. Selecting this option activates the Constant column where you enter the value you want to use in all rows transferred from the source table.
Constant Date — Enables you to set a specific date value in the Target column. Selecting this option activates the Constant column where you enter the value to use in all rows transferred from the source table.
Constant Number — Enables you to enter a specific numeric value in the Target column. Selecting this option activates the Constant column where you enter the value to use in all rows transferred from the source table.
Source Column — Enables you to identify the column that contains data to move to the Target column and specify an aggregation method (see Step 10).
Attribute Lookup — Enables you to identify a dimension attribute that contains the data to move to the Target column. Selecting this option activates three columns: Source Column, Attribute Name and Attribute Version. Choose the Source Column from the list of dimension columns in the source table. Then use the list of values to choose the attribute that contains the value that you want to migrate to the Target column. Choose the Attribute Version from the third list of values.
Unassigned — Designates a column that you do not want to map.
Note: When specifying a Column Population template for account consolidation, you must include the Business Relationship column, which is listed under Target column, and should be mapped as an Attribute Lookup. Choose Attribute Lookup as the Population Method.
If you chose Source Column (Step 9) and the template will be used for a Profit Aggregation rule, specify an aggregation method. Choose one of the following:
Min — Returns the lowest amount for a customer
Max — Returns the highest amount for a customer
Sum — Sums up all records for a customer
None — Does not aggregate
Note: The aggregation methods Average balance, Average Net Rate Over a Period, Average by Days Weighted, Beginning, and Last are not currently supported.
If the template will be used for an Account Consolidation Rule, choose None as the aggregation method.
Click Preview Population to view results.
Click Finish.
You can update a Column Population template. Follow these steps.
Navigate to Administration > Registration > Column Population Template.
Search for the Column Population Template to update. Choose a folder, then enter % to see all templates or enter text and use % as a wildcard. You can also search using Effective Date.
The list displays templates that match your criteria.
To modify the template name or description, click its Update icon.
To update a rule version, proceed as follows:
Display versions by clicking the + icon in the Rule Name column.
Identify the version to update and click its Update icon.
You can modify version details (Version Name, Effective Start Date, Effective End Date, description).
You can modify rule details.
To update a template version, proceed as follows:
Display versions by clicking the + icon in the Name column.
Identify the version to update and click its Update icon.
You can modify version details (Version Name, Effective Start Date, Effective End Date, description).
You can modify population information for one or more columns.
Click Apply.
Duplicating a Column Population Template allows you to quickly create a new template or template version.
When you duplicate a version, it is added to the list of versions for the template. When you duplicate the template and version, the template and version are displayed as new entities in the list of templates.
Note: When duplicating a version, you must specify an effective date range that does not conflict with the existing version.
Follow these steps to duplicate a template or template and template version.
Navigate to Administration > Registration > Column Population Template.
Search for the Column Population Template to duplicate. Choose a folder, then enter % to see all templates or enter text and use % as a wildcard. You can also search using Effective Date.
The list displays templates that match your criteria.
Search for the template to duplicate. Choose a folder, then enter % to see all rules or enter text and use % as a wildcard.
The list displays templates that match your criteria.
Display template versions by clicking the + icon in the Rule Name column.
Click the Duplicate icon for a version.
The Duplicate Options page opens.
Choose a duplication option.
Rule and Version – Enables you to create a new template and version based on the template that you chose in Step 3.
Version – Enables you to create a new version of the template that you chose in Step 3.
Click Continue.
If you chose Rule and Version as the duplication option (Step 6), identify the template and the version. For rule enter a unique name and description. For version, enter a unique name, specify the Effective Start Date, and Effective End Date. You can also enter a description.
If you chose Version as the duplication option (Step 6), identify the new version. Enter a unique name, specify the Effective Start Date, and Effective End Date. You can also enter a description.
Click Finish.
You can delete a Column Population template. Follow these steps.
Navigate to Administration > Registration > Column Population Template.
Search for the Column Population Template to delete. Choose a folder, then enter % to see all templates or enter text and use % as a wildcard. You can also search using Effective Date.
The list displays templates that match your criteria.
Display versions by clicking the + icon in the Name column.
Identify the version to delete and click its Delete icon.
You are prompted to confirm the deletion.
You can view a Column Population template. Follow these steps.
Navigate to Administration > Registration > Column Population Templates.
The Column Population Template page opens.
Search for Column Population templates. Choose a folder, then enter % to see all templates or enter text and use % as a wildcard. You can also search using Effective Date.
The list displays templates that match your criteria.
Proceed as follows:
Click the + icon for a rule to see rule versions.
Click the Update icon for a version to view template details.