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

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

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 3, 2003

Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives - Page 3

By William Pearson

Enabling Drillthrough for an OLAP Cube

Let's go into the Cube Editor and enable drillthrough for the HR cube, a sample cube provided by the Typical MSSQL Server 2000 Analysis Services. We will start Analysis Services, and navigate to the HR cube, with the following steps:

1.      Start Analysis Manager (Start --> Programs --> Microsoft SQL Server --> Analysis Services --> Analysis Manager).

2.      Expand the Analysis Servers folder by clicking the "+" sign to its left.

3.      Expand your server (typically named the same as the host PC, but determined by the installation / setup), and then expand the FoodMart 2000 database.

Illustration 1: Navigate to the FoodMart2000 Database in Analysis Manager

4.      Expand the FoodMart2000 database by clicking on the "+" sign to its left.

5.      Expand the Cubes folder (seen in the Illustration 2 below), by clicking the "+" sign to its left.

The cubes appear, similar to those shown in Illustration 2 below.

Illustration 2: Sample Cubes provided with the Analysis Services Installation.

6.      Right-click the HR cube, and then click Edit from the context menu.

The Cube Editor appears.

Let's make sure that we have a common display showing at this stage.

7.      Click the Data tab (lower right half of the Analysis Manager screen).

8.      Drag the Department and Time dimensions to the row and column axes, respectively, to match the display shown below.

Illustration 3: The Data Viewing Pane, after Dimensions are Placed

9.      Select the Tools top menu item.

10.  Select Drillthrough Options on the cascading menu, as shown in Illustration 4 below:

Illustration 4: Select Drillthrough Options

The Cube Drillthrough Options dialog appears.

11.  Check the Enable Drillthrough box by clicking it.

12.  Select the following columns for display by clicking the checkboxes to the immediate left of each.

  • pay_date
  • salary_paid
  • overtime_paid
  • overtime_hours
  • full_name

The dialog appears as partially shown below:

Illustration 5: The Cube Drillthrough Options Dialog (partial view)

In our setpoints above, we have enabled drillthrough, and defined what fields from the actual underlying data source will be displayed within a drillthrough view.

We can also set filters on the drillthrough, to restrict the data returned, as follows:

13.  Click the Filter tab of the Cube Drillthrough Options dialog.

Here we can type in a filter to further restrict the data returned in the drillthrough presentation. We will leave this blank, as shown below, for this exercise.

Illustration 6: The Filter Tab of the Cube Drillthrough Options Dialog

14.  Click OK.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



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