Optimizing Microsoft SQL Server Analysis Services: Optimize Query Performance with a Derived Measure
13. Click the Check button to validate syntax.
The confirmation dialog appears, indicating valid syntax within the new calculated measure, as shown in Figure 6.
Figure 6: Confirmation Dialog Indicates Positive Syntax Validation
14. Click OK to dismiss the confirmation dialog.
15. Click OK to close the Calculated Member Builder.
The new calculated measure appears in the tree within the Calculated Members folder.
16. With the CM_Cost with Promo Alloc calculated measure selected, click the Advanced tab in the Properties pane beneath.
17. Ensure that the Format String is set as follows:
The CM_Cost with Promo Alloc calculated measure appears within the Calculated Members folder, with our selection of Advanced Properties – Format String setting, as depicted in Figure 7.
Figure 7: The New CM_Cost with Promo Alloc Calculated Measure
The other calculated members that appear in the illustration above may differ from your own, depending upon the activities that have been conducted with the Sales cube in your own environment. As another matter, although the actions we have performed to this point alone do not require it, let’s process the cube to make sure we are all in a “processed” state.
18. Select Tools —> Process Cube to process the Sales cube.
19. Click Yes on the Save the Cube dialog that appears next, as shown in Figure 8.
Figure 8: Click “Yes” to Save the Cube
20. Click No on the dialog that appears next, as shown in Figure 9; we will not design aggregations at present.
Figure 9: Click “No” to Designing Aggregations
The Process a Cube dialog appears, as depicted in Figure 10, with the processing method set to Full Process. Full processing for the Sales cube will be relatively quick, so we will perform it to ensure that all is refreshed.
Figure 10: Full Process Selected in the Process a Cube Dialog
21. Ensure that the Full Process radio button is selected on the Process a Cube dialog.