Formulas are written using a rich syntax that allows the straightforward specification of complex regions in multidimensional space. A formula assigns a portion of the specified Cube to the value of an equation. You create an ordered list of formulas in the Formulas dialog box.
In addition to standard mathematical and accounting functions, formulas may also make references to other areas of the same Cube, or to other Cubes (cross-cube formulas) in the same database. Formula expressions are always ended with a semi-colon (;).
Before proceeding with the following Cube formula exercises, some basic aspects of Cube formulas are as explained below. Note that the section on Advanced Reference Materials, has a detailed explanation of Cube Formula Grammar, as well as Dependencies Grammar Definition.
Cube formulas are created and associated with a specific Cube. Thus, your first step in creating a formula will be to select a Cube (from the Model menu). After creating the formula, PowerOLAP® will add it to an ordered list of formulas within that Cube—you will create the list in the Formulas dialog box. PowerOLAP® checks the list and applies the first formula it finds for a selected variable.
Formulas appear in order of priority in the Formulas dialog box. Since more than one formula can “overlap” within a given area of a Cube, there is a priority based on where a formula appears in the Formulas dialog box (and you will determine that priority in the dialog box). The simple rule is that the higher the formula in the dialog box, the greater the priority. Thus, the following priority rules apply:
- The first formula takes precedence over all other formulas.
- The second formula takes precedence over all that follow it.
- The third formula takes precedence over all that follow it; and so on.
You must keep this in mind as you create and list formulas in the dialog box.
A valuable rule of thumb applies for the priority order of formulas:
In general, the ordering of formulas takes the form of an inverted triangle with the more complex formulas at the top. Begin with the formula whose definition contains the greatest number of constraints and work through to the formula that will encompass most of the cells at the outcome.
Formulas override values of Detail and Aggregate members. Thus, Aggregate member values calculated in the Dimension hierarchy are replaced by formula results.
Formulas may be “intra-dimensional” or calculate across Dimensions.