Isolating
DISTINCT COUNT into a Separate Cube
We
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.
Illustration 4: A Sample
Set of Databases Displayed within Analysis Manager
4.
Expand the FoodMart
2000 database.
5.
Expand the Cubes
folder.
The
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.
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.
Illustration 6: Select
Copy from the Context Menu
8.
Right-click
the Cubes folder for FoodMart 2000.
9.
Select Paste
from the context menu that appears, as shown in Illustration 7.
Illustration 7: Select
Paste from the Context Menu
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.
Illustration 8: New Name
Appears in the Duplicate Name Dialog
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.
Illustration 9: Select
Edit from the Context Menu
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.
Illustration
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.
Illustration
11: Select Customer_Id from the Insert Measure Dialog
IE 7
Firefox 2.0