Cube Dependencies

Cube Dependencies

Dependencies  are used to optimize calculations in Cubes that use formulas. The use of Dependencies can dramatically increase calculation speed. With Dependencies, you specify precise areas of a Cube so that aggregations will only execute on cells that have dependencies that refer to non-zero values. Dependencies in proCube are User-defined rather than automatically generated.  Such User-defined dependencies enable you to make decisions based on the known distribution of data across Members of Dimensions in a Cube (i.e., your knowledge of your database model).

In Cubes that have no formulas, the proCube aggregation engine does not “mark” a cell for aggregation if its value is zero. (Thus, if Canada, Mexico and U.S. aggregate to North America, there is no need to include Canadain the aggregation of, for example, Sales, if the Canada’s Sales value is zero.) When formulas are written for a Cube, this aggregation engine feature is canceled—because a formula may govern the cell, resulting in an ultimate value (i.e., a formula-driven value) that is not zero.

The following simple example illustrates how you can apply your knowledge of a model to reduce calculation “cost” in terms of time. Consider a Cube with an Accounts dimension whose Members include Sales, Price andUnits, and where Sales is governed by the formula: Sales = Price X Units.  Given the fact that a formula exists in the Cube, each cell in a given slice must be examined for the purpose of aggregations.  This examination can be “costly” because the aggregation engine cannot skip cells, as it can when formulas are not present.

In this case, proCube’s calculation engine can be optimized through the use of User-generated dependencies.  In the example, you could make the calculation of aggregations involving Sales depend on the value of Units, whose values can vary between zero (0) and other values.

To do so, you would set up a Dependencies relationship argument that states, “When a Units value is not zero and therefore will not yield a zero result in the formula governing Sales cells, put a marker to calculate aggregations for those cells.” By implication, this statement implies, “Do not perform the aggregation on the cells governed by the formula that will yield a zero result because Units is zero.” The example formula and its Dependency relationship is detailed below:

Formula: 

Details and {"Accounts.Sales"}=
SameCube.["Accounts.Price"]*SameCube.["Accounts.Units"];

Dependency:

SameCube.["Accounts.Units"] >> Aggregates and {"Accounts.Sales"};

[Note that although Details is specified for the calculation of the formula for Sales on the left-hand side, Aggregates for Sales is specified on the right-hand side of the Dependency relationship—which makes logical sense since the Dependency is governing the aggregation calculation.]

While the elimination of the examination of Sales cells for aggregations may seem minor in this simple example, one must remember that there are potentially hundreds of thousands of Sales cells in any given multidimensional model. When considering more complex formulas in larger, multidimensional spaces, Dependencies provide very large computational savings.

In order to take advantage of a User-defined Dependency optimization, you must perform two steps when composing a Cube formula that involves a “Dependency relationship”:

  • You must inform the calculation engine (via a check box, as illustrated in the following figure) that it will be explicitly told the set of cells for which it must execute aggregations in this dependency relationship.

  • You must specify the set of “precedent” non-zero cells on which formulas are executed.

The set of cells depends on a region in either the same Cube as that of the formula, or a different, external Cube (i.e., in a cross-Cube formula). The Dependency relationship is specified in the same Cube in which the formula is defined that subsequently requires the Dependency.

The Formulas Dialog contains two tabs: the Formulas tab and the Dependencies tab. Selecting the Formulas tab:

  • Enables a text box where, as you have seen, you can define formulas in a Cube.

  • Includes a Use Dependencies check box that determines whether Dependencies are to be used when calculating the aggregations for cells governed by formulas in this Cube. Checking “Use Dependencies” enablesdependencies.

Selecting the Dependencies tab switches to the following Dependencies view:

formulasdialogdependencies.png

Figure 1.    Formulas Dialog - Dependencies tab

When the Dependencies tab is selected, the  button from the Formulas tab changes into the  button. The function list is both emptied and disabled since these right-hand side functions cannot be used in Dependencies.  The remaining features, such as, “Define Left Hand Side” dialog, function as they did when the Formulas tab was active.

Dependencies grammar

A simple grammar, based on other grammars in the application, defines Dependencies. The grammar to describe Dependencies takes the following form:

PRECEDENT >> DEPENDENT;

Note that:

  • A DEPENDENT is an area reference similar to the references used in the left-hand side of formula definitions. [Recall, however, that in the example formula, Details and {"Accounts.Sales"} was specified; whereas in the Dependency, Aggregates and {"Accounts.Sales"} was specified.]  The quoting and naming conventions that apply to left-hand sides in formulas applies in Dependencies.

  • A PRECEDENT is a cell reference similar to the cell references used in the right-hand side of formula definitions—for example,  SameCube.[“Accounts.Units”] (with a possible cross-Cube specification, in which case, where you use SameCube, you would now use, for example,  DifferentCube.[“Accounts.Units”]). The same quoting and naming conventions that apply to right-hand sides in formulas also applies to Dependencies, except that functions, nested cube references, and dynamic lookups are not allowed.

Go to Advanced Reference Materials for information about dependencies grammar definitions.

User considerations

  • User-defined Dependencies do not represent an optimization technique for all uses of the calculation engine.  As with formulas, you should be aware that improper use of this feature can produce erroneous results in calculation.

  • Using Dependencies requires that you completely and exactly specify Dependency relationships for a given Cube. Partial or incorrect specification will cause values to be missed in aggregation and during cell calculation.

  • Enabling Dependencies greatly enhances performance in Transfer Cube and Export Cube when you are referencing a source Cube with the Skip Calculated Values deselected.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk