Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube - Page 3February 14, 2005 Isolating DISTINCT COUNT into a Separate CubeWe will begin performance enhancement efforts with the creation of a cube into which we will isolate the Distinct Count capabilities of the existing solution. We will manage this aspect of our solution from within the MSAS Analysis Manager. Let's start Analysis Services and proceed with the following steps: 1. Open Analysis Manager. 2. Expand the Analysis Servers folder by clicking the "+" sign to its immediate left. Our server(s) appear (my server, MOTHER1, is depicted in some of the illustrations). 3. Expand the desired server. Our database(s) appear, in much the same manner as depicted in Illustration 4.
4. Expand the FoodMart 2000 database. 5. Expand the Cubes folder. The sample cubes appear, as shown in Illustration 5.
NOTE: Your databases / cube tree may differ, depending upon the activities you have performed since the installation of MSAS (and the simultaneous creation of the original set of sample cubes). Moreover, some of the illustrations will occasionally depict measures, dimensions, and objects that do not appear in the pristine samples. If this happens, please ignore the differences, as they are unlikely to impact the steps we are undertaking as part of our practice exercise. Should you want or need to restore the cubes to their original state, simply restore the database under consideration. For instructions, see the MSSQL Server 2000 Books Online. Because we need a cube that matches the Sales cube, with which it will be working, as we shall see, with regard to dimensions, we will simply copy the Sales cube and rename it in the following steps: 6. Right-click the Sales sample cube. 7. Select Copy from the context menu that appears, as depicted in Illustration 6.
8. Right-click the Cubes folder for FoodMart 2000. 9. Select Paste from the context menu that appears, as shown in Illustration 7.
The Duplicate Name dialog appears, providing a means for the renaming of the Sales cube clone we are attempting to create. MSAS will not allow duplicate cube names within the same database. 10. Replace Sales with DISTINCT_CUSTOMERS in the Name box of the Duplicate Name dialog, as depicted in Illustration 8.
11. Click OK to close the dialog, and to create the DISTINCT_CUSTOMERS cube. The DISTINCT_CUSTOMERS cube appears in the cube tree. While the dimensions of the DISTINCT_CUSTOMERS cube must mirror those in the Sales cube, the new cube will require only one measure; that measure will be a Distinct Count measure, which we will add next. To prepare the new cube further, we will then dump the unneeded measures. 12. Right-click the new DISTINCT_CUSTOMERS cube. 13. Select Edit from the context menu that appears, as shown in Illustration 9.
The Cube Editor opens. 14. Right-click the Measures folder in the Tree View to the left of the Schema tab. A single-line context menu appears, as depicted in Illustration 10. 15. Select New Measure from the context menu.
The Insert Measure dialog appears. 16. Click-select customer_id. The Insert Measure dialog, selection circled in red, appears as shown in Illustration 11.
|
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
