Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 16, 2004

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

By William Pearson

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM