Creating Cube Formulas
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 formula will populate the month February.
1. From the PowerOLAP ribbon, go to Model tab then select Cube command. The Cubes dialog box is opened
2. Click on the Current Year Budget cube to select it.
3. Click on the Formulas button. The Formula dialog box is opened.
4. Click on the “squiggly brackets” or the Build Range Reference button, .
The Build Range Reference dialog box appears.
5. 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.)
6. Select Months from the Dimension list then from the Members list box on the right, select February.
7. Click OK.
8. 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.
9. Next click on the “square“ brackets or the Build Cube Reference button, .
The Build Cube Reference dialog box appears.
10. Select the Current Year Budget from the cube drop down.
11. Again, select Months in the Dimension list and then select January from the Member list. The Selected radio button is selected, as a consequence.
12. Click OK.
This return you to the Formula Editor (you can hit Enter after the “=” to show the formula on two lines)”
13. Use the buttons in the Formula Editor — the asterisk (for multiplication), the numbers and the semi-colon — to complete the formula so that it appears like so:
|
DESCRIPTION |
Left of Equal ‘=’ |
Area of Cube to populate |
RHS |
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 |
14. Click OK in the Formula Editor to save the formula then OK in the Cubes dialog (If you have mistyped the formula, you will receive a message indicating that there is syntax problem).
15. Back in the PowerOLAP main application window, go to Slice tab and click Recalculate command or simply press F9 in the Accounts by Months slice to recalculate values.
16. From the PowerOLAP ribbon, go to Model tab and select Dimension command.
17. Double click Accounts in the Dimension list box.
The ‘Accounts’ Hierarchy dialog box is displayed.
18. Click on the Create New Member button, , on the toolbar and type Margin % so that it appears in the Members list box.
19. Expand Accounts in the Hierarchy Definition box, on the right.
20. Select Margin % from the Members list box and drag it to the Hierarchy list box and release it just under Accounts.
21. Click the OK button to close the Dimension Hierarchy dialog box.
22. Click OK in the Dimensions dialog box.
Margin % now appears as the top row in the Accounts by Months slice.
23. From the PowerOLAP ribbon go to Model tab and select the Cube command. The Cubes dialog box appears.
24. Double-click the Current Year Budget cube to launch the Formula Editor.
25. 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.
Important
Priority, which is top-to-bottom in the Formula editor, is very important for determining data calculations—consult the PowerOLAP® User Manual.
26. Using the Formula Editor dialog box, create the formula:
All and {“Accounts.Margin %”}= “Current Year Budget”.[“Accounts.Gross Profit”] / “Current Year Budget”.[“Accounts.Net Sales”]*100; |
27. Click OK in the Formula Editor then OK in the Cubes dialog.
28. Press F9 to recalculate values in the Accounts by Months slice.
29. Click the File icon then Save Database command to save the data and slice (which now includes Margin %).