Integration of Detail-Planning Cubes (Profit Center Cube)

Return to Profit Center Model Overview.

When running the Sales, Cost Center, and Profit Center models on the same OLAP database, detailed data from the Sales Planning and the Cost Center cubes can be automatically integrated into the Profit Center cube. The integration is implemented as rules in the Profit Center 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 Center

The PC Account element 311320 (Total Revenue (entry)) integrates values from the measure Net Revenue in the Sales Planning cube.

  • The Article dimension exists only in the Sales Planning cube and its elements match some of the values in the Article attribute of the Cost Object dimension; the matching values from Article are integrated.
  • The Customer dimension exists only in the Sales Planning cube; the total value from All Customers is integrated.
  • All other dimensions are shared.

The rule expression for this is (Note: indentations and line breaks added for clarity):

Copy
IF(
    PALO.DATA(  "",
                "#_Cost Object",
                "Article",
                !'Cost Object' ),
    PALO.DATA(  ""
                "Sales Planning"
                !'Version', 
                !'Month', 
                !'Legal Entity',
                PALO.DATA(  "",
                            "#_Cost Object",
                            "Article",
                            !'Cost Object' ), 
                "All Customers"
                !'Currency', 
                "Net Revenue" ),
    CONTINUE()
)

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 PC Account element 311320 (Total Revenue (entry)) are:

Attribute

Value

Remarks

PCalculation

IF(PALO.DATA("","#_Cost Object" …

The complete expression is shown above.

PCalculationDependencies

Cube:Sales Planning

Create rule instance only if the source cube Sales Planning exists.

PTargetRestriction

'Partner Entity':'~','Profit Center':'~','Partner Profit Center':'~','Partner Cost Object':'~' Restrict calculations on the Partner Entity, Profit Center, Partner Profit Center, and Partner Cost Object dimensions.

ACalculation

IF(PALO.DATA("","#_Cost Object" …

Identical to the PCalculation attribute.

ACalculationDependencies

Cube:Sales Planning

Create rule instance only if the source cube Sales Planning exists.

ATargetRestriction

'Partner Entity':'~','Profit Center':'~','Partner Profit Center':'~','Partner Cost Object':'~' Identical to the PTargetRestriction attribute.


Example 2: integrating a single cost type into Profit Center

The PC 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 and its elements match some of the values in the Cost Center attribute of the Profit Center dimension; the matching values from Cost Center are integrated.
  • All other dimensions are shared.

The rule expression for this is (Note: indentations and line breaks added for clarity):

Copy
IF(
    PALO.DATA(  "",
                "#_Profit Center",
                "Cost Center",
                !'Profit Center'  ),
    PALO.DATA(  "",
                "Cost Center",
                !'Version',
                !'Month',
                !'Legal Entity',
                PALO.DATA(  "",
                            "#_Profit Center",
                            "Cost Center",
                            !'Profit Center'  ),
                !'Currency',
                "311010",
                "Adjusted"  ),
    CONTINUE()
)

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 PC Account element 311500 (Other Income) are:

Attribute

Value

Remarks

PCalculation

IF(PALO.DATA("","#_Profit Center" …

The complete expression is shown above.

PCalculationDependencies

Cube:Cost Center

Create rule instance only if the source cube Cost Center exists.

PTargetRestriction

'Partner Entity':'~','Partner Profit Center':'~','Cost Object':'~','Partner Cost Object':'~'

Restrict calculations on the Partner Entity, Partner Profit Center, Cost Object, and Partner Cost Object dimensions.

ACalculation

IF(PALO.DATA("","#_Profit Center" …

Identical to the PCalculation attribute.

ACalculationDependencies

Cube:Cost Center

Create rule instance only if the source cube Cost Center exists.

ATargetRestriction

'Partner Entity':'~','Partner Profit Center':'~','Cost Object':'~','Partner Cost Object':'~'

Identical to the PTargetRestriction attribute.

Example 3: integrating a complex combination of cost type and functional area into Profit Center

The two examples above covered simple mappings of a figure in the source cube being mapped onto a single account in the Profit Center 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 the expense, while the chart of accounts in the destination cube Profit Center shows amounts by the function of an expense. To transpose one view into the other, the combination of cost type and the functional area must be used:

The PC Account element 311700 (Administrative Expenses) integrates values from the Cost Type element 311020 (Administrative Expenses) as well as the cost types 311070311120 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 311070311120, the value from the ADM (General and Administration) element is used.
  • All other dimensions are shared.

The rule expression for this is (Note: indentations and line breaks added for clarity):

Copy
IF(
    PALO.DATA(  "",
                "#_Profit Center",
                "Cost Center",
                !'Profit Center'  ),
    PALO.DATA(  "",
                "Cost Center",
                !'Version',
                !'Month',
                !'Legal Entity',
                PALO.DATA(  "",
                            "#_Profit Center",
                            "Cost Center",
                            !'Profit Center'  ),
                !'Currency',
                "311020",
                "Adjusted"  ) 
    +
    PALO.DATA(  "",
                "Cost Center",
                !'Version',
                !'Month',
                !'Legal Entity',
                PALO.DATA(  "",
                            "#_Profit Center",
                            "Cost Center",
                            !'Profit Center'  ),
                !'Currency',
                "311100",
                "Adjusted"  ) 
    +
    PALO.DATA(  "",
                "Cost Center",
                !'Version',
                !'Month',
                !'Legal Entity',
                PALO.DATA(  "",
                            "#_Profit Center",
                            "Cost Center",
                            !'Profit Center'  ),
                !'Currency',
                "311110",
                "Adjusted"  ) 
    +
    PALO.DATA(  "",
                "Cost Center",
                !'Version',
                !'Month',
                !'Legal Entity',
                PALO.DATA(  "",
                            "#_Profit Center",
                            "Cost Center",
                            !'Profit Center'  ),
                !'Currency',
                "311120",
                "Adjusted"  ),
    CONTINUE()
)

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 PC Account element 311700 (Administrative Expenses) are:

Attribute

Value

Remarks

PCalculation

IF(PALO.DATA("","#_Profit Center" …

The complete expression is shown above.

PCalculationDependencies

Cube:Cost Center

Create rule instance only if the source cube Cost Center exists.

PTargetRestriction

'Partner Entity':'~','Partner Profit Center':'~','Cost Object':'~','Partner Cost Object':'~'

Restrict calculations on the Partner Entity, Partner Profit Center, Cost Object, and Partner Cost Object dimensions.

ACalculation

IF(PALO.DATA("","#_Profit Center" …

Identical to the PCalculation attribute.

ACalculationDependencies

Cube:Cost Center

Create rule instance only if the source cube Cost Center exists.

ATargetRestriction

'Partner Entity':'~','Partner Profit Center':'~','Cost Object':'~','Partner Cost Object':'~'

Identical to the PTargetRestriction attribute.

Updated March 27, 2024