Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures - Page 4

August 16, 2004

13.  Click the Check button to validate syntax.

The confirmation dialog appears, indicating valid syntax within the new calculated measure, as shown in Illustration 6.

Illustration 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 Illustration 7.

Illustration 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 Illustration 8.

Illustration 8: Click "Yes" to Save the Cube

20.  Click No on the dialog that appears next, as shown in Illustration 9; we will not design aggregations at present.

Illustration 9: Click "No" to Designing Aggregations

The Process a Cube dialog appears, as depicted in Illustration 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.

Illustration 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.

22.  Click OK to begin processing.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Illustration 11.

Illustration 11: Indication of Successful Processing Appears (Compact View)

23.  Click Close to dismiss the viewer.

24.  Click the Data tab in the Cube Editor, if necessary.

Cube data is retrieved, and we are able to see the values that appear for all measures, including the new calculated measure, in the Data view.

25.  Drag the Store Type dimension in the upper half of the Data view to the top of the left-most column in the lower half - in my case, I am dragging the Store Type dimension bar over the Country column heading, but your row axis may be different, depending upon previous activities with the cube.

Store Type appears in the place of the previously existing dimension in the row axis.

26.  Drag the Promotions dimension to the immediate right of the newly placed Store Type column, to effect a "crossjoin." When the cursor appears as shown in Illustration 12, drop the Promotions dimension.

Illustration 12: Cursor Indicates Appropriate Drop Point

The final arrangement should appear as depicted in Illustration 13.

Illustration 13: Arrangement in the Data View - Partial Row Axis

We can now see the calculated member exactly as it was constructed by the newly appointed MSAS Administrator. We will leave the calculated measure in place until after constructing and testing its replacement, for comparison purposes.