DISTINCT COUNT into a Separate Cube
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
start Analysis Services and proceed with the following steps:
Expand the Analysis
Servers folder by clicking the "+" sign to its immediate
server(s) appear (my
server, MOTHER1, is depicted in some of the illustrations).
in much the same manner as depicted in Illustration 4.
Illustration 4: A Sample
Set of Databases Displayed within Analysis Manager
Expand the FoodMart
Expand the Cubes
sample cubes appear,
as shown in Illustration 5.
Illustration 5: The
Sample Cubes in the FoodMart2000 Database
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.
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:
the Sales sample cube.
from the context menu that appears, as depicted in Illustration 6.
Illustration 6: Select
Copy from the Context Menu
the Cubes folder for FoodMart 2000.
from the context menu that appears, as shown in Illustration 7.
Illustration 7: Select
Paste from the Context Menu
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.
with DISTINCT_CUSTOMERS in the Name box of the Duplicate Name
dialog, as depicted in Illustration 8.
Illustration 8: New Name
Appears in the Duplicate Name Dialog
to close the dialog, and to create the DISTINCT_CUSTOMERS cube.
cube appears in the cube tree.
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.
the new DISTINCT_CUSTOMERS cube.
from the context menu that appears, as shown in Illustration 9.
Illustration 9: Select
Edit from the Context Menu
The Cube Editor opens.
the Measures folder in the Tree View to the left of the Schema
single-line context menu appears, as depicted in Illustration 10.
Measure from the context menu.
10: 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.
11: Select Customer_Id from the Insert Measure Dialog