Adding Calculated Members to a Cube

The Calculated Members function enables you to add new members for the Measures dimension. A calculated member is a definition (for a dimension member) that you create and store with the cube. The calculated member value is generated at a later point, during query time. You can also define a calculated member as a measure. You can access the Calculated Members function from the SAS OLAP Cube Studio Actions menu or from the context menu for a cube.

The following display shows the Calculations for Cube page of the Calculated Members function. The Defined calculated members list displays all calculated members that are defined in the metadata for the selected cube. On the Calculations for Cube page, you can add new calculated members or modify existing members for the selected cube. Select Add to launch the New Member wizard and define a new calculated member.

On the Calculation Type page of the New Member wizard, select the type of calculation that you want to create: a simple calculation, time analysis calculation, or a custom calculation. In this example, a simple calculation is created.

On the Simple Calculations page, select the mathematical calculation that you want to use in the calculated member from the list of radio buttons. Next, select the measures that you want to use with the calculation. In this example, the predicted and actual measures for the cube are used in the formula. It is the difference between these measures that the formula will calculate. Select Next when you are finished.

On the General page of the New Member wizard, enter the measure name. In this example, the calculated measure is named PredAnDiff. Select the format and solve order for the measure. When you are finished, click Next.

On the Finish page of the New Member wizard, a display panel shows the metadata that is generated for the new calculated member. Review the metadata. When you are finished, click Finish to complete the New Member wizard.

The new calculated member PredAnDiff is now listed in the Defined calclulated members list.

If needed, you can now make changes to the PredAnDiff calculated member. On the Calculations for Cube page, select the calculated member and click Next. This opens the Edit a Calculated Member page. You can then modify the measure name and formula. Select Verify to check the MDX code for errors and validate the code against the OLAP server. If you click Build Formula, the Build Formula dialog box appears.

In the Build Formula dialog box, you can modify the MDX code with the help of an expression builder. You can also add MDX functions and Data Source elements to the expression. Select OK when you are finished editing the calculated member.

You can also create a Time analysis calculated member with the New Member wizard. On the Calculations for Cube dialog box, click Add to launch the New Member wizard and define a new calculated member.

On the Calculation Type page of the New Member wizard, click Time analysis calculation. When you are finished, click Next.

On the Time Calculations page, select a time calculation. Next, select an existing measure from the Formula panel. The Formula panel will change depending on the time calculation radio button that you select. The Existing measure drop-down list is populated with all measures, including calculated measures. The Time period drop-down list is populated with members from the Time dimension. When you are finished, click Next.

Finish the New Member wizard by completing the General and Finish pages.

You can also create a custom calculated member with the New Member wizard. On the Calculations for Cube dialog box, click Add to launch the New Member wizard and define a new calculated member.

On the Calculation Type page of the New Member wizard, click Custom calculation. When you are finished, click Next.

On the Custom Calculation page, you can select the parent dimension and parent member for the measure. You can also enter the MDX formula for the measure. Enter the name for the calculated member and select the format and solve order.

If you click Build Formula, the Build Formula dialog box appears. In this dialog box, you can modify the MDX code with the help of an expression builder. You can also add MDX functions and data source elements to the expression.

On the Custom Calculation page, click Verify to check any MDX code that you might have added for errors. This validates the code against the OLAP server. Select OK when you are finished editing the calculated member. Finish the New Member wizard by completing the General and Finish panels.