Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 3, 2005

MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement - Page 5

By William Pearson

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.

NOTE: For more information on Drillthrough in Analysis Services, see my articles Drilling Through to Details: From Two Perspectives and Mastering Enterprise BI: Create Aging "Buckets" in a Cube (both members of the Introduction to MSSQL Server Analysis Services series) here at Database Journal.

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:





















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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM