# How To Create a Cube Using the PowerOLAP Cube Modeler

###### Using Excel as a Front End Video

###### Using Excel as a Front Instructions

>==Populating== You have seen how creating Dimension hierarchies, and assigning Aggregate weights results in the natural “summing up” of values. Cube formulas represent a powerful extension of your ability to perform mathematical calculations throughout a PowerOLAP database. With Cube formulas, you can perform all manner of calculations to populate a cell, ranges of cells, even entirely different Cubes.

Presently your **Current Year Budget** cube contains data for **January** for all **Accounts** and **Regions**. The following Cube formulawill populate the month **February**:

- Select
**Model > Cube**. The Cube dialog box is opened. - Click on the
**Current Year Budget cube**to select it. - Click on the
**“Formulas”**button. The Formulas dialog box is opened:

Formula Editor Interface

We will now make use of some buttons in the [[[Formula Editor]]] window. These buttons will enable us to specify the area of the cube we want to populate with data, and where the data will come from.

1. Click on **“Build Range”** button to open the Range Reference dialog. **Months** is selected in this dialog at the top of the Dimension list; it is the Dimension we want to work with, so leave as is.

2. For the **“Qualifier”** (top left), select the radio button **“Details”**. This indicates that only Detail members are to be calculated by the Cube formula. Aggregate members will be calculated according to the Dimension hierarchy

NOTE: it is possible to “overwrite” hierarchy calculations via a Cube formula. A very important feature if you wish to calculate a “what if” or budgeted Aggregate data point, so that it contrasts to actual figures in Detail data points.

3. On the right, select member **February** from within the **Months** dimension. The **Selected** radio button is selected. The dialog appears as follows, with the formula as it exists so far, at the bottom:

4. Click **“OK”**.

5. Click on the **“Equals”** button in the Formula Editor, . The left-hand side of the formula is completed, and is shown in the content area.

6. Next click on the **“Square Brackets”** button . The **Build Cube Reference** dialog box appears. Again, **Months**is selected; it is the Dimension we want to work with, so leave as is.

7. Select **January** from the Member list on the right. The Selected radio button is selected. **NOTE: that at the top of the dialog, there is a Cubes drop-down.** This brings up an important feature: the ability to create [[**cross-cube formulas**]]. There is only one Cube in our database, **Current Year Budget.** In the formula we are creating, data will come from this Cube to populate another area of the same Cube. The Build Cube Reference dialog box appears as follows:

8. Click **“OK”**. The **Formula Editor** appears as follows (You can hit “Enter” after the “=” to show the formula on two lines).

9. Use the buttons in the **Formula Editor** — the “*” for multiplication, the numbers and the “;” — to complete the formula so that it appears like this:

**Following is a breakdown of the syntax of the Cube formula:**

Left of ‘=’ — Area of cube to populate

Right of ‘=’ — Formula

{“Months.February”} — Dimension and Member to populate

“Current Year Budget” — Source cube

[“Months.January”] — Range within source cube

1.5 — Value (in this case, +50%)

‘;’ — Ends formula statement

10. Click **“OK”** to save the formula. If you have mistyped the formula, you will receive a message indicating that there is syntax problem. You are returned to the Cubes dialog box.

11. Click **“OK”**.

12. Press **“F9”** in the **Accounts by Month** slice to recalculate values. Notice that the **February** column has been populated by the Cube formula defined in the previous steps.

###### Multi-Member Calculations

Next, you will create a Cube formula that calculates a ratio of two Members. You will first need to add a new Member—Margin %—to the Accounts dimension, and then modify the Accounts dimension hierarchy. This Cube formula exercise brings up two important strengths of PowerOLAP, in comparison to static modeling tools, OLAP or otherwise: the capability to create new, “on-the-fly” calculations (which can of course be subsequently saved) for precisely specified (even new) components of a business model, which themselves are created entirely within PowerOLAP [i.e., not dependent on any static model of business data].

1. Select **Model > Dimension**.

2. Double click **Accounts** in the Dimension list box. The **Accounts** Hierarchy dialog box is displayed.

3. Click on the **“Create New Member”** button .

4. Type “Margin %” so that it appears in the Members list box.To modify the Accounts dimension hierarchy:

5. Expand **Accounts** in the **“Hierarchy Definition”** box, on the right.

6. Select **Margin %** from the **Members list** box and drag it to the **Hierarchy list** box and release it just under**Accounts**. The completed hierarchy will appear as in the following figure:

7. Click **“OK”** to close the Dimension Hierarchy dialog box.

8. Click **“OK”** in the Dimensions dialog box. **Margin %** now appears as the top row in the Accounts by Month slice.

Define Cube formula

1. Select **Model > Cube**

2. Double-click the **Current Year Budget** cube.

3. Press **“Enter”** twice in the Content Area to move the previously written formula down two lines. Begin on the top line of the Formula Content area, i.e., place this formula above the other.

Priority, which is top-to-bottom in the Formula editor, is very important for determining data calculations — consult the PowerOLAP User Manual for detials.

4. Using the **Formula Editor** dialog box, create the following formula:

All and {“Accounts.Margin %”}= “Current Year Budget”.[“Accounts.Gross Profit”]/ “Current Year Budget”.[“Accounts.Net Sales”]*100;

5. Click **“OK”** to save the formula. You are returned to the Cubes dialog box.

6. Click **“OK”**.

7. Press **“F9”** to recalculate values in the **Accounts by Month** slice.

**Margin %** is now calculated for all **Months** in the **Current Year Budget** cube. The figure above shows the **Margin %** values for the Member **United States**. You can select other **Regions** members to verify that all members in the Regions dimension have been updated as well.

8. Select **File > Save All** to save the data and the Slice (which now includes **Margin %**) to disk.