Creating Cube Formulas

Creating Cube Formulas

General procedure for creating a cube formula

Here is the general procedure for creating Cube formulas in the Formulas dialog. We will practice this procedure by using the Build Range Reference and Build Cube Reference dialogs.

To build a cube formula:

  1. Open a database. A database must have at least one cube in order for you to define a formula.

  2. From the Model ribbon, click Cubes. The Cube dialog opens.

  3. Double-click the Cube name to open the Formulas Dialog.

  4. Enter the <<formula expression>> in the Formulas text box or use the Build Range and/or Cube Reference dialogs to define the formula (shown in the following examples).

  5. Click OK to return to the Cube dialog.

  6. Click OK and then press F9 to recalculate.

Build range and build cube reference dialogs

proCube helps you build formulas using the Build Range Reference and Build Cube Reference dialogs for the LHS and RHS respectively. The formula “wizard” is accessed using the following two Define Formula dialog buttons:

buttonbuildrangereference.png  Allows you to build a Range Reference for the left-hand side (LHS) of a formula (where data will appear);

buttonbuildcubereference.png  Allows you to build a Cube Reference for the right-hand side (RHS) of the formula (where data to be calculated by the formula will appear).

Both wizards allow you to specify exactly where data will appear in a Cube on LHS (including whether affected Members are Detail, Aggregate or All). In addition, assuming the RHS is not a constant, you can specify precisely what data is to be “operated on” in an originating Cube (which may be the same Cube).

We will proceed with examples of two different formulas that demonstrate how one might calculate the Margin % (a newly added Member of the Sales Account dimension) in the Sales Cube. The first example calculatesMargin % based on the data we already have in the Sales cube (we will refer to this as an “internal cube formula”). The second example is a cross-cube formula, making Margin % in the Sales cube equal to Margin % in theMargin cube where it is already calculated to equal Margin divided by Revenue.

Adding a new member to display results

Because proCube allows you to add new Members to an existing model, you can add Members for displaying results of formulas within existing Cubes. For example, a model containing the members Revenue and Cost of Sales (and Margin)—both of which may be figures from other applications—may be enhanced with a formula that calculates and displays it in the Margin % Member, created specifically for this purpose in proCube.

Therefore, for the following exercises, the first step is to add a new Member, Margin %, to the Sales Account dimension.

The following figure shows Margin % added to the Sales Accounts dimension. It is not part of the Margin aggregation.

margin_salesaccountdimension.png

Figure 1.    Margin% Member in the Sales Account Dimension Hierarchy

It is not always necessary to create a (new) Member to display formula results. There are instances when you may want a formula that “overwrites” the results of an Aggregate member whose value has no relevance (e.g., for Unit Price X Sales, in an Aggregate member; or formula Forecast results based on new Actuals, which would otherwise show zeros for upcoming months).

Internal cube formula

Our first example involves calculating figures for Margin % in the Sales cube from Margin and Revenue values, which are also in the Sales cube.

  1. From the Model ribbon, click Cubes to open the Cube dialog.

  2. Select the Sales cube, then click Formulas.

  3. Under the last formula written in the Formula text box, press the Enter key to put a space between the last and the new formula to be written.

  4. Enter the comment proceeded by two backslashes //Margin % and then press Enter to put the cursor on the next line.

formulasdialogslashslashmargincomment.png

Figure 2.    Formulas Dialog with //Margin%

  1. With the cursor on a new line in the Formula text box, click the Build Range Reference button, buttonbuildrangereference.png , to define the LHS range reference and open the Build Range Reference dialog.

  2. Select Sales Account from the Dimension list box on the left.

Now you can define a Qualifier to indicate whether the formula should affect All, Aggregates or Detail members.

  1. To define the Qualifier, select the All radio button, in which case all Members will be affected.

  2. On the right-hand side of the dialog is a list of Member names whose values will be determined by the formula. Select Selected to enable the Filter text box below the list. You can enter a letter or a word to narrow the list of Members shown (a helpful feature if it is a long list).

  3. Select Margin % from the Members list box on the right (note that the Selected radio button is now enabled).

buildrangereferencesalesaccount.png

Figure 3.    Build Reference Dialog

  1. Click OK. You are returned to the Formulas Dialog.

At this point, if you were simply making the LHS equal to a fixed number, you would type the “equals” sign and then the number. To build the RHS (right-hand side) of the formula, using the Build Cube Reference dialog:

  1. Enter the equal sign (=) after the LHS of the formula.

  2. Click the Build Cube Reference button, buttonbuildcubereference.png . The Build Cube Reference dialog opens again.

  3. Select the Sales Cube from the top drop-down menu (if you selected another Cube, you would be building a cross-cube formula).

  4. Select Sales Account from the Dimension text box.

  5. Select Margin from the Members text box for the first part of the equation.

  6. Click OK. You are returned to the Formula text box.

  7. Add in the “divide by” sign (/) after the Margin reference:

Now you must finish the equation by building the last part of the statement so that: Margin % = Margin/Revenue.

  1. Again, click on the Build Cube Reference button, buttonbuildcubereference.png , and choose the Sales cube and Sales Account dimension.

  2. Select the member Revenue and click OK. The formula appears as below in the Formulas dialog:

  1. The RHS of the formula now needs to be completed by adding the semi-colon at the end. The completed formula appears as in the following figure (you can add a hard return after the “equals” sign for legibility):

  1. Now click Check Syntax in the Formulas dialog to check the syntax of the formula for errors. Your successful formula looks like this:

formualsdialogfinalformula.png

Figure 4.    Formulas Dialog - //Margin% Formula after Check Syntax

If an error exists in the formula syntax, a message providing a possible resolution appears.

  1. Click OK to return to the Cubes dialog.

  2. Click OK to exit the Cubes dialog.

Cross-cube formula

As noted earlier, a cross-cube formula can be used to calculate values in a Cube using data from another Cube or even two or more Cubes. For example, Cubes A and B contain sales data for years 1 and year 2, respectively. Cube C might contain forecasts for year 3. A cross-cube formula, created in Cube C, could use values in Cubes A and B to average their sales data and dynamically calculate a percentage-based increase in sales, showing results in Cube C. The fact that the calculations are dynamic (true for all proCube formula calculations) means that changing a variable will automatically recalculate the formula in Year 3 (Cube C), and therefore, forecasts are updated as new “Actual” are recorded for Year 2 (Cube B).

Another example worth mentioning—particularly for multinational companies—is the use of a Cube that stores monthly foreign exchange calculations. One could multiply the data in this Cube by the figures in a Financialscube, where company units report in local currency, to determine all figures, by month, in US dollars, back in the Financials cube.

The use of cross-cube formulas can dramatically improve performance because you can calculate values only for certain Member intersection points and show them in a “standalone” cube (rather than a single Cube that performs many calculations across all Member intersection points).

The following example is a cross-cube formula that will, again, yield figures for Margin % figures in the Sales cube—but this time based on a calculation from Margin % in the Margin cube.

In this case, we must account for the fact that the Reference cube (Margin) has more Dimensions than the Sales cube, where the formula results will be written. (This is instructive because the difference in dimensionality is often the case in cross-cube formulas.) In the Margin cube, the Dimensions are Version, Year, Region, Product, Month and Margin Account. In Sales, we only have Region, Product, Month and Sales Account (i.e., no Version orYear dimensions).

Our formula must identify exactly which Members of the “extra dimensions”—in Margin—to use in our calculation. Also, we need to indicate that Margin % in Sales will be based on Margin % in Margin.

In the Formula dialog (for the Sales cube), the formula will include the same LHS statement for Margin % in Sales that we used in the last formula (i.e., the destination of the formula results is the same):

(If you wish, you can enter // in front of the formula to keep it from being processed, since we have already calculated Margin %.)

To construct the RHS, referencing the Margin cube, we will now specify the Members from the Version and Year dimensions on which to base the calculation. (This will address the “dimensionality difference” we need to account for.) Since we want the Sales Cube’s calculations to be based on Actuals for 2009, we can make the appropriate selections in the Build Cube Reference dialog.

  1. Click on the Build Cube Reference button, buttonbuildcubereference.png.

  2. Select the Margin cube from the Cubes drop-down—since this is a cross-cube formula, with data originating in that Cube.

  3. Click on Version in the Dimension list box on the left, then select Actual in the Member list on the right.

  4. Next, click on Year in the Dimension list box, then select 2000 in the Member list. At this point, the grayed-out formula box at the bottom of the dialog will look as follows:

marginviersactyear2009.png

In the above figure, the “originating Cube”—in this case, Margin—is indicated first.

Now, logically, we need to specify that we want Margin % from the Margin Account dimension to be used for the calculation of the Sales Cube’s Margin %:.

  1. Click Margin Account in the Dimension list box, then select Margin % in the Member list.

The Dimensions that are common to the two cubes—Region, Product and Month—are not specified in the formula, signifying that all corresponding values apply between the two Cubes for these Dimensions.

  1. Click OK and return to the Formulas dialog. Enter a semi-colon (;) at the end of the formula. The contents appear as follows:

crosscubemarginexampleformula.png

Now the Margin % formula, which brings Margin % from one Cube to another, is complete. This is a simple example of a cross-cube formula because it brings over the same figures in one Cube, Margin, to another Cube,Sales (although it is illustrative of how to proceed when the two Cubes have different dimensional structures). From this example, though, you can easily grasp how you might perform other calculations on “originating” data (i.e., data in the first Cube).

Before proceeding to a discussion of Dependencies, remember that proCube Formula Cube Grammar is fully documented in Advanced Reference Materials.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk