Huge issue in BW: Calculated key figures from multicubes before aggregation

[Former Member]

Hi,

I am calculating a total using a calculated key figure

that has one key figure from a cube and two other key figures from another cube.

Also I factor the total using a variable with a replacement path on the dummy attribute (Constant) to "force" the calculation at the material level.

Calculated Cost = Cost1(from CUBE1) / Quantity1(from CUBE1) * Quantity2(from Cube2) * ZVRMATER1(Formula variable with replacement path on the dummy attribute from 0material)

Problem:

If I split the calculation this works before aggregation at the material level. In other words, this works fine using calculations that use key figures that must be calculated before aggregation if they all are from the same cube at the time. The required formula above will not work (zero divide) since during selection the OLAP processor retrieves in phases and with value 0 from the key figure of CUBE2.

We could use a restricted key figure for quantity2 in the calculation but then the calculation will not happen at the material level before aggregation.

Any suggestion on how to solve this without new back-end development?

Can we force OLAP to "wait" for values from CUBE2 before doing the calculation before aggregation?

I have seen the OLAP selection behavior chance depending on the multicube so I would like to understand more if this could be controlled somehow in this version.

The system is version BW 3.1c. Patch 12

Many Thanks.

[Former Member]

UPDATED DETAILS....

We are calculating a total using a calculated key figure in a multicube that has one key figure from a cube and two other key figures from another cube.

Also we factor the total using a variable with a replacement path on the dummy attribute (Constant) to "force" the calculation at the material level.

CUBE1 has Date, Business Unit, Material characteristics. also Cost1 and Quantity1 key figures.

CUBE2 has Date, Business Unit, Material characteristics. also Quantity2 key figure.

Calculated Cost = Cost1(from CUBE1) / Quantity1(from CUBE1) * Quantity2(from Cube2) * ZVRMATER1(Formula variable with replacement path on the dummy attribute from 0material)

Calculations must be done at the material-Business Unit-Date level and totals must be the same for the selected period at any level of drilldown .

Problem:

If I split the calculation this works before aggregation at the material level. In other words, this works fine using calculations that use key figures that must be calculated before aggregation if they all are from the same cube at the time. The required formula above will not work (zero divide) since during selection the OLAP processor retrieves the data in phases and Quantity2 has value 0 from key figure in CUBE2.(Key figures don't see each other before aggregation)

We changed the query to sequential processing but this didn't help. We could use a restricted key figure for quantity2 in the calculation but then the calculation will not happen at the material level before aggregation.

Any suggestion on how to solve this with the current multicube and/or BEX and without new back-end development?

Can we force OLAP to "wait" for values from CUBE2 before doing the calculation before aggregation? Is there a 3.1 patch for this?

Many times calculations are required at the characteristic level so every time that we have to do this in multicubes with a combination of key figures from different cubes we would have to resort to new/modify data target, update rules and loads/reloads sometimes for just one key, figure like in this case.

I have seen the OLAP selection behavior chance depending on the multicube so I would like to understand more if this could be controlled somehow in this version. The system is version BW 3.1c. Patch 13