Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube - Page 4February 14, 2005 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.
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.
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.
The Confirm Measure Delete dialog appears, as depicted in Illustration 15.
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.
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.
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.
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.
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.
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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
