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 May 9, 2005

Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS

By William Pearson

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services, with each installment progressively adding features and techniques designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

Note: Current Service Pack current updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, upon which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, in cases where MS Office components are presented in the article.


In this article, we will examine the processing of an Analysis Services cube via another of the integrated MSSQL Server components, Data Transformation Services ("DTS"). Virtually anyone that works with MSSQL Server in an RDBMS context, and often within a data warehouse or mart design perspective and related functions, has probably interacted in some way with DTS - if only as an Import / Export utility. Best known as the set of ETL (Extraction, Transformation and Load) utilities that accompany the integrated Microsoft BI Solution as a part of MSSQL Server, DTS does, indeed, perform well within the context of all of the stages of data transformation (examples include type conversions, scrubbing and validation, among others, to varying degrees. DTS also maintains a particularly high-profile role within the creation and maintenance of a data warehouse, mart, or other such source for business intelligence and organizational reporting.

A significant part of DTS' power within the Microsoft BI solution, among other combinations, is its inherent integration with the Microsoft Universal Data Access and ActiveX technologies. The resulting "expanded access" means that DTS works equally well in extracting, transforming and loading data from ODBC- and OLE DB compliant sources. DTS is the tool of choice for many other data "movement and manipulation" needs, and I like to think of its uses as belonging to either these sorts of activities, or the running of programs, scripts, etc., to act as an agent of automation of some sort or other - which will actually be the kind of thing we examine in this article.

There are, of course, many things that DTS can be used to accomplish that do not necessarily fit into neat classifications: For example, I used DTS in another article of this series, Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives, whereby I show how to perform cube drillthrough activity via DTS-mobilized MDX. I suggest the article as one of many uses of MDX that might not readily occur to the "casual user." Moreover, the flexible utility of DTS packages is perhaps nowhere more apparent than within the realm of automation: they can be used to perform all manner of actions, including the execution of scripts and programs written in other languages, to help us to accomplish virtually any requirement necessary to meeting objectives of data warehousing, and far, far beyond.

In this article, we will overview one of two custom DTS tasks that accompany the installation of Analysis Services, the Analysis Services Processing task. As a part of our examination of this useful task, we will:

  • Introduce the Analysis Services Processing task, and discuss its uses;
  • Perform a practice exercise, whereby we conduct full processing of a sample cube with the Analysis Services Processing task;
  • Discuss options that are available to us within the Analysis Services Processing task;
  • Note the benefits of using the Services Processing task, particularly in combination with scheduling.

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