Enable
Drillthrough in the FoodMart Sales 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 Sales 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 14.
Illustration
14: 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
15.
Illustration 15: Example
Cubes within the FoodMart 2000 Analysis Services Database
6.
Right-click
the Sales 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
16.
Illustration
16: 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 17.
Illustration 17: Select
Tools --> Drillthrough Options ...
The Cube Drillthrough
Options dialog appears.
9.
Click the
check box labeled Enable drillthrough (atop the dialog) to check it, if
necessary.
10.
Within the
selection checklist, on the Columns tab, ensure that only the checkboxes
presented in Table 1 below are checked:
|
Column
|
Table
|
|
"store_sales"
|
"sales_fact_1997"
|
|
"store_name"
|
"store"
|
|
"store_city"
|
"store"
|
|
"store_state"
|
"store"
|
|
"store_country"
|
"store"
|
|
"the_date"
|
"time_by_day"
|
|
"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 18.
Illustration
18: 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, Store 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 19.
Illustration
19: 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 Sales
cube.
14.
Select Process
Cube ... from the context menu that appears, as shown in Illustration 20.
Illustration
20: Select Process Cube ...
15.
Click Yes
on the Save the cube dialog that
appears, to save the
cube, as depicted in Illustration
21.
Illustration
21: 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 22.
Illustration
22: Select Full Process ...
17.
Click OK
to begin Full processing of the Sales 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 23.
Illustration
23: Processing Completes Successfully, as Indicated on the Process Viewer
18.
Click Close
to dismiss the viewer.
19.
Select File
--> Exit to close the Cube Editor.
20.
Select File
--> Exit to exit Analysis Manager.
Now that
we have enabled drillthrough for the Sales cube, we are ready to
examine running a query against it that leverages the capability. In our case,
that query will support the Dataset for our drillthrough target
report. We will get some hands-on practice with construction of this report,
as soon as we complete preparation of the primary report (from which we will
launch drillthrough activity) in the next section.
» 
Comment and Contribute
