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 Feb 14, 2005

Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube - Page 4

By William Pearson

17.  Click OK to accept the selection.

The Insert Measure dialog closes, and we see the new measure appear (default name of Customer Id: the underscore character is removed) in the Measures folder.

18.  Click-select Customer_Id in the Measures folder, if required.

19.  If necessary, click the downward arrow beneath the Cube Tree to open the Properties pane.

20.  Click the Basic tab.

21.  Modify the default Name of Customer Id to the following:

Distinct Customers

22.  Type the following into the empty Description box, just below the Name box:

Distinct Count - Customers

23.  Click the box to the right of the Aggregate Function property label (at the bottom of the Basic tab), to enable the selector.

24.  Select Distinct Count in the Aggregate Function selector.

The Basic tab of the Properties pane appears, with our modifications, as shown in Illustration 12.

Illustration 12: Distinct Customers Measure - Properties Pane - Basic Tab

25.  Click the Advanced tab of the Properties pane.

26.  Ensure that Big Integer is selected for the Data Type property, as depicted in Illustration 13.

Illustration 13: Distinct Customers Measure - Properties Pane - Data Type Tab

27.  Right-click the Unit Sales measure in the Measures folder.

28.  Select Delete from the context menu that appears, as shown in Illustration 14.

Illustration 14: Select Delete from the Context Menu

The Confirm Measure Delete dialog appears, as depicted in Illustration 15.

Illustration 15: Confirm Measure Delete Dialog

29.  Click Yes.

The Unit Sales measure disappears from the Measures folder.

30.  Perform steps 27 through 29 above for all members of the Measures folder, except the new one we created above, Distinct Customers.

Our DISTINCT_CUSTOMERS cube will have no measures except the DISTINCT COUNT measure we have created. The idea here is that, once we have finished designing our new cube, it will contain only this single measure and an identical set of dimensions to those found in the cube whose performance we are intent upon improving (in the case of our exercise, the Sales sample cube). It is far simpler, since our Sales cube has already been designed, to clone it, as we have, and eliminate the unwanted objects to achieve our ends, than to build a new cube from scratch. We are also assured, using this approach, that the dimensions will, indeed, precisely mirror those in the original Sales cube, a requirement important to our achieving success.

Let's clear out any remaining, unneeded objects in the DISTINCT cube.

31.  Expand the Calculated Members folder below the Measures folder in the cube tree, if necessary.

32.  Using the same approach taken with each of the measures we deleted above, delete each of the calculated members.

33.  Expand the Actions folder below the Measures folder in the cube tree, if necessary.

34.  Using the same approach taken with each of the measures we deleted above, delete any actions that appear.

The DISTINCT_CUSTOMERS cube is now prepared structurally for its intended use. Let's process the cube to activate our changes, and to finalize its preparation for its role in optimizing distinct count operations.

35.  Select Tools --> Process Cube to initialize the processing steps, as shown in Illustration 16.

Illustration 16: Initiating Cube Processing ...

The Save the Cube dialog appears, informing us that the cube must be saved before we can proceed.

36.  Click Yes, to allow processing to proceed, as depicted in Illustration 17.

Illustration 17: Save the Cube Dialog

A message box appears, stating that the cube has no aggregations, and asking if we wish to design them at this time, as shown in Illustration 18.

Illustration 18: Aggregations Message Box - Just Say "No"

37.  Click No to skip designing aggregations at present.

The Select the Processing Method dialog appears.

Full Processing is the default, and only, option, as the cloned cube has not been processed since the structural changes we have made to it.

The Select the Processing Method dialog appears, as depicted in Illustration 19.

Illustration 19: The Select the Processing Method Dialog

38.  Leaving settings at default, click OK.

Processing begins, and runs rapidly, as evidenced by the Process viewer's presentation of processing log events in real time. The Processing cycle ends and the success of the evolution is indicated by the appearance of the Processing Completed Successfully message (in green letters) at the bottom of the viewer, as shown in Illustration 20.

Illustration 20: Indication of Successful Processing

39.  Click Close.

We are returned to the Cube Editor. We can now browse the data and see our new Distinct Customers measure in action.

40.  Click the Data tab, if necessary.

On the refreshed Data View, data appears in the default formation, ready for manipulation and review, although we stop here only to view our handiwork. The Data View, depicting the Distinct Customers measure, appears in Illustration 21.

Illustration 21: Our New Distinct Customers Measure in the Data View

We now have a processed cube, consisting of dimensions identical to those that exist in the Sales cube, together with the single distinct count measure, Distinct Customers, that we have created to store our distinct customer count information. We are ready to leave the new cube, and to move to the next step of integrating it into our overall solution for enhancing performance of distinct counts in our Sales data.

41.  Select File --> Exit from the main menu.

The Cube Editor closes, and we are returned to Analysis Manager.

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