Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS
May 9, 2005
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: