Integration of Detail-Planning Cubes (Profit and Loss Cube)
Return to Profit and Loss Model Overview.
When running the Sales, Cost Center, and Profit and Loss models on the same OLAP database, detailed data from the Sales Planning
and the Cost Center
cubes can be automatically integrated into the Profit and Loss
cube. The integration is implemented as rules in the Profit and Loss
cube. These rules are defined as part of the KPI calculation.
The sample data package for the model contains some samples for the integration. Below are three examples.
Example 1: integrating a sales measure into profit and loss
The PnL Account
element 311320
(Total Revenue (entry)) integrates values from the measure Net Revenue
in the Sales Planning
cube.
- The
Article
andCustomer
dimensions exist only in theSales Planning
cube; the total value fromAll Articles
andAll Customers
is integrated. - All other dimensions are shared.
The rule expression for this is:
PALO.DATA("", "Sales Planning", !'Version', !'Month', !'Legal Entity', "All Articles", "All Customers", !'Currency', "Net Revenue")
The integration for planning versions (assigned to the P
calculation schema) and for the actual version (assigned to the A
calculation schema) are identical. The integration rule instances should be generated only if the source cube Sales Planning
exists.
The attributes for the PnL Account
element 311320
(Total Revenue (entry)) are:
Attribute |
Value |
Remarks |
|
PALO.DATA("", "Sales Planning" … |
The complete expression is shown above. |
|
Cube:Sales Planning |
Create rule instance only if the source cube |
|
PALO.DATA("", "Sales Planning" … |
Identical to the |
|
Cube:Sales Planning |
Create rule instance only if the source cube |
Example 2: integrating a single cost type into profit and loss
The PnL Account
element 311500
(Other Income) integrates values from the Cost Type
element 311010
(Other Income) from the Cost Center
cube.
- The
Cost Center
dimension exists only in theCost Center
cube; the total value fromAll Cost Centers
is integrated. - All other dimensions are shared.
The rule expression for this is:
PALO.DATA("", "Cost Center", !'Version', !'Month', !'Legal Entity', "All Cost Centers", !'Currency', "311010", "Input")
The integration for planning versions (assigned to the P
calculation schema) and for the actual version (assigned to the A
calculation schema) are identical. The integration rule instances should be generated only if the source cube Cost Center
exists.
The attributes for the PnL Account
element 311500
(Other Income) are:
Attribute |
Value |
Remarks |
|
PALO.DATA("", "Cost Center" … |
The complete expression is shown above. |
|
Cube:Cost Center |
Create rule instance only if the source cube |
|
PALO.DATA("", "Cost Center" … |
Identical to the |
|
Cube:Cost Center |
Create rule instance only if the source cube |
Example 3: integrating a complex combination of cost type and functional area into profit and loss
The two examples above covered simple one-to-one mappings of a figure in the source cube being mapped onto a single account in the Profit and Loss
cube. This third example shows a more complex transition picking multiple, more selective values from the source cube. The Cost Type
hierarchy of source cube Cost Center
holds amounts by nature of expense, while the chart of accounts in the destination cube Profit and Loss
shows amounts by function of expense. To transpose one view into the other, the combination of cost type and functional area must be used:
The PnL Account
element 311700
(Administrative Expenses) integrates values from the Cost Type
element 311020
(Administrative Expenses) as well as the cost types 311070
– 311120
into the functional area ADM
(General and Administration) from the Cost Center
cube.
- The
Cost Center
dimension exists only in theCost Center
cube. For administrative expenses, the total value fromAll Cost Centers
is used, and for the cost types311070
–311120
, the value from theADM
(General and Administration) element is used. - All other dimensions are shared.
The rule expression for this is:
PALO.DATA("", "Cost Center", !'Version', !'Month', !'Legal Entity', "All Cost Centers", !'Currency', "311020", "Input") +PALO.DATA("", "Cost Center", !'Version', !'Month', !'Legal Entity', "ADM", !'Currency', "311070", "Input") +PALO.DATA("", "Cost Center", !'Version', !'Month', !'Legal Entity', "ADM", !'Currency', "311080", "Input") +PALO.DATA("", "Cost Center", !'Version', !'Month', !'Legal Entity', "ADM", !'Currency', "311090", "Input") +PALO.DATA("", "Cost Center", !'Version', !'Month', !'Legal Entity', "ADM", !'Currency', "311100", "Input") +PALO.DATA("", "Cost Center", !'Version', !'Month', !'Legal Entity', "ADM", !'Currency', "311110", "Input") +PALO.DATA("", "Cost Center", !'Version', !'Month', !'Legal Entity', "ADM", !'Currency', "311120", "Input")
The integration for planning versions (assigned to the P
calculation schema) and for the actual version (assigned to the A
calculation schema) are identical. The integration rule instances should be generated only if the source cube Cost Center
exists.
The attributes for the PnL Account
element 311700
(Administrative Expenses) are:
Attribute |
Value |
Remarks |
|
PALO.DATA("", "Cost Center" … |
The complete expression is shown above. |
|
Cube:Cost Center |
Create rule instance only if the source cube |
|
PALO.DATA("", "Cost Center" … |
Identical to the |
|
Cube:Cost Center |
Create rule instance only if the source cube |
Updated September 25, 2024