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
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 8

By William Pearson

We next arrive at the DTS Import / Export Wizard - Select Table Copy or Query dialog. We are creating a destination database as part of the "export" (our query results) from our OLAP cube. We could have put the table (which is what we really want here) within an existing database, but that might not be a good idea without proper planning and security considerations.

One of the strengths of the DTS tool is that it is very comprehensive, not only in performing ETL functions similar to (and far more complex than) this, but in its capability to build structures for us that have yet to be created. Furthermore, the entire process of visually building the task, which we are doing now, needs not be treated as an ad hoc evolution. DTS allows us to save the "program" we are creating as VB, a database object that can be reused, and so forth.

We are not simply copying the source data here, but are using a query to extract results, the main purpose of which is to illustrate the output of an MDX query with a DRILLTHROUGH statement. Let's proceed with selecting the "query" option here and getting to the results we seek.

27.         Click the radio button to the left of the Use a Query to Specify the Data to Transfer option, to select it, as shown below.

Illustration 25: The DTS Import / Export Wizard - Select Table Copy or Query Dialog - Query Option Selected

28.         Click Next.

The Type SQL Statement dialog of the DTS Import / Export Wizard appears. We will type in a basic DRILLTHROUGH - enabled MDX query that will retrieve the data supporting a specific value in the HR cube.

29.         Type the following MDX syntax into the Query Statement box of the dialog:


SELECT{ [Measures].[Org Salary]} ON COLUMNS,

{[Department].[All Department].[Store Temporary Stockers]} ON ROWS



The Type SQL Statement dialog of the DTS Import / Export Wizard appears as shown in Illustration 26, complete with the new MDX Query.

Illustration 26: The DTS Import / Export Wizard - Type SQL Statement Dialog with MDX Query

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