Stage 4 : Calculation on the fly
At the moment our OLAP database is comprised of two data (or measure) cubes, these are the quantity of shoes sold and the value tax exclusive. These measures are dimensioned by Time, Style and Outlet.
From these two measures we can create others which won't be stored by the database but will be calculated dynamically each time the user requests them. These measures are sometimes called formulas in reference to the text that defines them.
From the user's point of view there is no difference between a stored measure and a formula. Both are defined by dimensions and by type. The formula Average Price, representing the average price of a pair of shoes, is dimensioned according to Time, Style and Outlet and has a decimal type. The formula is the simplest possible and reads:
Average Price = Total Value Tax Exclusive / Quantity
As soon as a user requires this variable, the database engine performs the required divisions to create the information.
It is not necessary for all of the elements of a formula to be dimensioned in the same way. For example we could define a new formula:
Total Value tax Inclusive = Total Value Tax Exclusive x 1.206
Here all the cells are individually multiplied by 1.206.
As the value added tax (VAT) rate changes (too) frequently, it is possible to create a new variable VAT, dimensioned only by time. This variable will indicate the VAT rate to apply to each position in the Time dimension. We therefore have, still very intuitively:
Total Value Tax Inclusive = Total Value Tax Exclusive x (100+VAT)/100
Thus, the correct total will be automatically applied to every cell to which this formula applies.
In the same way, if the VAT rate changes for the different products, the dimensions of the VAT variable must be Time and Product. Graphically, this variable will be a plane instead of a line.