Calculating the Quantities of Fish Required by Fishcake Type

To allocate costs for each fish type to individual cake types, you must start by calculating how much of each fish type is used for the daily production of fishcakes.

A FishRequired cube holds this information, derived by applying "recipes" from a cube called Ingredients to the fishcake production values in the Production cube.

FishRequired, Ingredients, and Production cubes

Ingredients is a two-dimensional cube that stores the percentage of each fish type used in the composition of fishcakes produced by Fishcakes International. For example, the following view of the Ingredients cube shows that the composition of the Ancient Mariner fishcake is 26% cod, 30% pollack, 10% trout, 10% herring, and 24% hake.

Caketype dimension from the Ingredients cube

The FishRequired cube is a workspace where the amount of fish going into each fishcake is calculated. (This cube will later be used to calculate the materials cost of each fishcake.) Its dimensions are CakeType, FishType, Date and FishRequiredMeasures. The only measure you'll work with here is Qty Required - Kgs.

In the FishRequired cube, Qty Required - Kgs varies by fishcake type, fish type, and date. For example, the following view shows the amount of flounder required in the production of several different cake types on January 20.

Amount of flounder required in the production of several different cake types on January 20

To correctly calculate the amount of each fish type required for a given fishcake type on a given day, you need to create a calculation statement for the FishRequired cube that multiplies the amount of each cake type produced (from the Production cube) by the percentage of each fish used in the cake type (from the Ingredients cube).

The statement that calculates Qty Required - Kgs values for the FishRequired cube therefore looks like this:

['Qty Required - Kgs']=N:DB('Production',!CakeType,!Date,'Quantity
Produced
- Kgs') * DB('Ingredients',!CakeType,!FishType);