Cube Building and Modifying Examples |
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.
Creating a Simple Calculation |
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, select 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, select 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 select Build Formula, the Build Formula dialog box opens.
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.
Creating a Time Analysis Calculation |
You can also create a Time analysis calculated member with the New Member wizard. On the Calculations for Cube dialog box, 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 Time analysis calculation. When you are finished, select 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, select Next.
Finish the New Member wizard by completing the General and Finish pages.
Creating a Custom Calculation |
You can also create a custom calculated member with the New Member wizard. On the Calculations for Cube dialog box, 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 Custom calculation. When you are finished, select Next.
On the Custom Calculations 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 select Build Formula, the Build Formula dialog box opens. 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, select Verify to check any MDX code that you may 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.
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.