Enable Drillthrough in the FoodMart Warehouse Cube within
Analysis Services
Before we can leverage the Drillthrough
capabilities of an Analysis Services cube, we must enable the
feature from within the Cube Editor. When we enable Drillthrough,
we also select the tables and columns that are included in the result set
returned by a drillthrough operation. As many of us know, these columns
can be from any table in the cube's underlying data source, (and can include columns
that are not part of the cube's schema, if set up properly). We can also limit
resources consumed by Drillthrough operations (a potentially significant
concern with larger, more complex cubes), limiting the number of rows returned
for the columns we have selected, when appropriate.
Let's enable Drillthrough for the Warehouse
sample cube, taking the following steps.
1. Open
Analysis Manager, beginning at the Start menu.
2. Expand
the Analysis Servers folder by clicking the "+" sign to
its immediate left.
Our server(s) appear.
3. Expand
the desired server.
Our database(s) appear, in much the same manner as shown in Illustration
13.
Illustration 13: Example Databases Displayed within
Analysis Manager
NOTE: Your
databases / cube tree will differ, depending upon the activities you have
performed since the installation of Analysis Services (and the simultaneous
creation of the original set of sample cubes). 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.
4. Expand
the FoodMart 2000 database.
5. Expand
the Cubes folder.
The cubes appear as depicted in Illustration 14.
Illustration 14: Example Cubes within the FoodMart 2000
Analysis Services Database
6. Right-click
the Warehouse cube within the FoodMart 2000 Analysis Services
database.
7. Select
Edit... from the context menu that appears, the relevant portion of which
is shown in Illustration 15.
Illustration 15: Opening the Cube Editor
The Cube Editor opens.
8. Select
Tools -->
Drillthrough Options... from the main menu inside the Cube Editor, as
depicted in Illustration 16.
Illustration 16: Select Tools --> Drillthrough Options ...
The Cube Editor opens.
The Cube Drillthrough Options
dialog appears.
9. Click
the check box labeled Enable drillthrough (atop the dialog) to check it.
10. Within the selection
checklist, on the Columns tab, ensure that only the checkboxes presented
in Table 1 below are checked:
|
Column
|
Table
|
|
"warehouse_sales"
|
"inventory_fact_1997"
|
|
"day_of_month"
|
"time_by_day"
|
|
"warehouse_name"
|
"warehouse"
|
|
"warehouse_city"
|
"warehouse"
|
|
"warehouse_state_province"
|
"warehouse"
|
|
"warehouse_country"
|
"warehouse"
|
|
"product_id"
|
"product"
|
|
"brand_name"
|
"product"
|
|
"product_name"
|
"product"
|
Table 1: Select Settings for the Detail Drillthrough View...
The Cube Drillthrough Options
dialog appears as partially shown in Illustration 17.
Illustration 17: The Cube Drillthrough Options Dialog
(Partial View) with Settings
Our intent here is to establish a simple display
of dated transactions - to give us a feel that transactions within reasonable
date ranges are, indeed, underneath the totals we see in the value upon which
we can drill through, Warehouse Sales. The columns selected here could
obviously be varied to accomplish other specific needs, just as easily, and
would apply to any measure in the cube.
11. Click OK to accept
settings.
The Drillthrough Settings message
box appears, alerting us to the fact that our settings take effect only after
the cube is saved, as depicted in Illustration
18.
Illustration 18: Drillthrough Settings Message Box
12. Click OK to dismiss
the message box, and to close the Cube Drillthrough Options dialog.
13. In the upper left corner of
the Cube Editor (top of the cube tree), right-click the Warehouse
cube.
14. Select Process Cube ...
from the context menu that appears, as shown in Illustration 19.
Illustration 19: Select Process Cube ...
15. Click Yes on the Save
the cube dialog that appears, to save the cube, as depicted in Illustration
20.
Illustration 20: Save the Cube Before Processing ...
The Process a Cube dialog appears next. We will
select Full processing, simply to ensure that we are all in sync going
forward.
16. Click the radio button to
the left of the Full Process label, as shown in Illustration 21.
Illustration 21: Select Full Process ...
17. Click OK to begin Full
processing of the Warehouse cube.
Processing begins immediately, and the Process viewer
appears, displaying the various logged events of the processing cycle.
Processing completes, and the viewer presents the green Processing completed
successfully message, as depicted in Illustration 22.
Illustration 22: Processing Completes Successfully as
Indicated on the Process Viewer
18. Click Close to
dismiss the viewer.
Now that we have enabled drillthrough for the Warehouse
cube, we are ready to examine running queries against it that leverage the
capability. We will get some hands-on practice with this in the next section.
» 
Comment and Contribute
