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 and Customer dimensions exist only in the Sales Planning cube; the total value from All Articles and All 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
PCalculation	PALO.DATA("", "Sales Planning" …	The complete expression is shown above.
PCalculationDependencies	Cube:Sales Planning	Create rule instance only if the source cube Sales Planning exists.
ACalculation	PALO.DATA("", "Sales Planning" …	Identical to the PCalculation attribute.
ACalculationDependencies	Cube:Sales Planning	Create rule instance only if the source cube Sales Planning exists.

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 the Cost Center cube; the total value from All 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
PCalculation	PALO.DATA("", "Cost Center" …	The complete expression is shown above.
PCalculationDependencies	Cube:Cost Center	Create rule instance only if the source cube Cost Center exists.
ACalculation	PALO.DATA("", "Cost Center" …	Identical to the PCalculation attribute.
ACalculationDependencies	Cube:Cost Center	Create rule instance only if the source cube Cost Center exists.

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 the Cost Center cube. For administrative expenses, the total value from All Cost Centers is used, and for the cost types 311070 – 311120, the value from the ADM (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
PCalculation	PALO.DATA("", "Cost Center" …	The complete expression is shown above.
PCalculationDependencies	Cube:Cost Center	Create rule instance only if the source cube Cost Center exists.
ACalculation	PALO.DATA("", "Cost Center" …	Identical to the PCalculation attribute.
ACalculationDependencies	Cube:Cost Center	Create rule instance only if the source cube Cost Center exists.

Updated July 8, 2024