EPM Tech Tips · EPCM · 12
The CFO points at one figure in a profitability report and asks where it came from. In Oracle Enterprise Profitability and Cost Management, that figure is usually the end of a chain of allocation rules. Trace Allocations draws the chain as a Sankey diagram, and Rule Balancing shows what each rule did to the balance. Used in a fixed order, they walk one number back to its source and drivers.
Before you start
Reproduce the number. Write down the full intersection the CFO saw: Years, Period, Scenario, Version and the member on every business dimension. A parent-level number is a sum, so find its level-0 members first.
Pick your route. If the question is "where did this cost come from", follow steps 1 to 5. If the question is "is this number right", start with step 6 and come back to the trace once you know which rule looks wrong.
Step 1 · Is it allocated money or loaded money?
What happens. EPCM keeps loaded data and rule results apart in the PCM_Balance dimension. PCM_Input holds what was loaded and is never overwritten. PCM_Allocation In holds what an allocation rule wrote to a destination, PCM_Allocation Out holds the offsetting entry at the source, and PCM_Adjustment In and PCM_Adjustment Out hold custom rule results. PCM_NetBalance is the sum of all of them. If the amount is mostly Input, there is nothing to trace.
How to check.
Open the figure in an ad hoc grid or in Smart View with the same POV and business members.
Put the PCM_Balance members in the columns: Input, Adjustment In, Adjustment Out, Allocation In, Allocation Out.
Put the level-0 PCM_Rule members in the rows to see which rules wrote to this cell.
Fix. If the amount is Input, send the question to the data load and the GL. If it is Allocation In, write down the rules that posted it and move on. If part of it is Adjustment In or Adjustment Out, that part comes from a custom rule and will not appear in the trace. Explain it from the custom rule's formula.
Step 2 · Is the POV defined and calculated?
What happens. Trace only runs for a point of view that already exists on the Calculation Control page. If it does not, Refresh returns the error "Select a valid Point of View." A POV that exists but was not recalculated after the last load or rule change will trace old results.
How to check.
Go to Home › Modeling › Calculation Control and confirm the Years, Period, Scenario and Version combination is listed.
Check the status and date of the last calculation for that POV, and compare it with the date of the last data load.
Fix. Create the POV, or rerun the calculation before you open the trace. Rerunning recalculates the whole POV and replaces its allocation results, so agree the timing with anyone reporting from it.
Step 3 · Does a trace form start at the right member?
What happens. Trace reads its layout from a data form. The first row dimension is the end point you are explaining, and each next row dimension is one step further back toward the source. The column is PCM_Allocation In in the PCM_Balance dimension. In the diagram, money flows from the rightmost dimension to the leftmost, so your starting member sits on the left.
How to check.
Open the form designer and open the trace form, or create one. Creating forms needs a Service Administrator or Power User role.
In the rows, put the CFO's member first, as one specific member (for example one product), not a parent.
Add the dimensions the money travels through, using level-0 descendants, for example Activity, then Entity, then Account.
Set the column to PCM_Allocation In.
Use the POV to narrow the scope, for example the no-customer member to see the money before it reaches customers.
Fix. Keep the first row to one member and the other rows to dimensions with few level-0 members. A top-level start gives an unreadable diagram.
Step 4 · What does the trace show for that one number?
What happens. The Sankey diagram shows how the amount was built: which activities fed the product, which entities performed those activities, and which accounts the money started in. Click View Data to see the amount behind each flow.
How to check.
From the Home page, select Intelligence, then Trace on the left navigation bar.
Select the cube, click Select Data Form and pick the trace form.
Select Years, Period, Scenario and Version, then click Refresh.
Select the nodes that matter to the question and click Isolate. Click Restore to bring back the full diagram.
Click View Data to see the numbers behind the current view, filtered to the isolated nodes.
Fix. A missing or unexpected node is your lead. Note the entity, activity or account and take it to step 5.
Step 5 · Which rule and which driver moved the money?
What happens. Each Allocation In amount on a given PCM_Rule member was written by that one rule, using its driver. In the rule definition, the allocation is either Allocate Evenly or Specify Driver Location. With a driver location, each destination gets its share in proportion to its driver value, and a destination with no driver value gets nothing. By default the driver is read Same As Destination, from PCM_Input and the NoRule member of PCM_Rule.
How to check.
Use the PCM_Rule rows from step 1 to identify the rule that wrote the amount.
Open the rule definition and read the Source/Destination tab and the driver section.
Note any fixed member set in the driver location, especially on POV dimensions, PCM_Balance and PCM_Rule.
Pull the driver values for every destination in a grid and recalculate the share of the CFO's member by hand: its driver value divided by the total driver value, times the source amount.
Fix. If the hand calculation matches, you have the explanation. If not, look for a driver read from the wrong POV or rule, or a destination with no driver value.
Step 6 · Does Rule Balancing tie the chain?
What happens. Rule Balancing shows every rule and its impact on the balance, and confirms that what left the source arrived at the destinations.
How to check.
From the Home page, select Modeling, then Rule Balancing.
Select the cube and a balancing form. A useful form has the level-0 rules in rows and Input, Adjustment In, Adjustment Out, Allocation In, Allocation Out and Allocation Offset Amount in the columns.
Select Years, Period, Scenario and Version and click Refresh.
Select the rule from step 5 and use View Rule Definition, View Rule Data (Ad-Hoc Grid) or View Rule Data Smart View to see which members it affected.
Use Export to Excel to keep the evidence.
Fix. If Allocation In does not equal the absolute value of Allocation Out for a rule, check destinations without driver values and the offset location. If your dimensions do not roll up to a single top member, build a custom balancing form, because the default view shows only top-level members.
Quick checklist
Input vs allocated amount · ad hoc grid with PCM_Balance and PCM_Rule
POV exists and is freshly calculated · Home › Modeling › Calculation Control
Trace form starts at one member, column Allocation In · form designer
Trace, isolate the nodes, view the data · Home › Intelligence › Trace
Rule, driver location and share recalculated by hand · Rule definition
Rule ties: In vs Out vs offset · Home › Modeling › Rule Balancing
Pedro San Martín · Founder & Principal, Asher & Company
