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

Monday May 9th 2005 by William Pearson
Share:

Process a cube, as well as other Analysis Services database components, with a custom DTS task that accompanies an Analysis Services installation. MSAS Architect Bill Pearson leads hands-on exploration of the DTS Analysis Services Processing task.

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.

Introduction

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.

DTS' Analysis Services Processing Task

Overview and Discussion

One of the most critical administrative tasks surrounding an Analysis Services implementation is the recurring need to process our cubes. Processing is essential to keeping our OLAP data sources in sync with the data sources that they summarize. The Analysis Services Processing task exists to perform the processing options that we typically perform manually from within Analysis Manager, at least initially, in the design and development of our cubes. We can use it to automate our processing cycles, and thus keep our cubes updated to reflect the latest data in the cubes' underlying sources, all with minimal manual intervention.

Because some Analysis Services processing capabilities, including the processing of databases, dimensions, cubes, or other objects we can manually process from Analysis Manager, could not be easily managed using other DTS tasks, we are provided with a means of performing these activities with a task specifically designed for that purpose. The Analysis Services components to which we can apply our DTS-driven capabilities include those detailed, hierarchically, in Table 1.

Component

Use This Option to:

Database

Process all dimensions, cubes, virtual cubes, and partitions in the selected Analysis Services database.

.. Cubes Folder

Process all cubes and virtual cubes contained in the selected folder.

... Cubes

Process, refresh, or incrementally update the selected cube (see Table 2 below).

.... Cube Partitions

Process or incrementally update the selected partition.

.... Remote Cube Partitions

Process or incrementally update a selected partition on another machine.

... Linked Cubes

Process, refresh, or incrementally update a cube based upon a cube on another machine.

... Virtual Cubes

Process, refresh, or incrementally update the selected virtual cube.

.. Dimensions Folder

Process all dimensions contained in the selected folder.

... Shared Dimensions

Process or incrementally update the selected shared dimension.

... Virtual Dimensions

Process or incrementally update the selected virtual dimension.

.. Mining Models

Process all Mining Models contained in the selected folder.

... Relational Mining Models

Process the selected Relational Mining Model.


Table 1: Component Selection Options for the Analysis Services Processing Task

We also have the options depicted in Table 2 with regard to the manner in which we perform processing.

Component

Processing Options

Database

  • Full Process

  • .. Cubes Folder

  • Full Process

  • Refresh Data

  • ... Cubes

  • Full Process

  • Refresh Data

  • Incremental update

  • Incrementally Update Dimensions

  • .... Cube Partitions

  • Full Process

  • Incremental update

  • Incrementally Update Dimensions

  • .... Remote Cube Partitions

  • Full Process

  • Incremental update

  • Incrementally Update Dimensions

  • ... Linked Cubes

  • Full Process

  • ... Virtual Cubes

  • Full Process

  • .. Dimensions Folder

  • Rebuild the dimension structure

  • Incremental update

  • ... Shared Dimensions

  • Rebuild the dimension structure

  • Incremental update

  • ... Virtual Dimensions

  • Rebuild the dimension structure

  • .. Mining Models Folder

  • Full Process

  • Refresh Data

  • ... Relational Mining Models

    7     Full Process

  • Refresh Data

  • ... Relational Mining Models

  • Full Process

  • Refresh Data


  • Table 2: Processing Options and the Components to Which They Apply

    Other considerations affect the options that are available, as well. An example is illustrated in the selection of multiple objects in the tree structure provided within the Analysis Services Processing task dialog; anytime we select two or more objects within the nodes of the tree, full processing / rebuild is our only selection option.

    As we note in the tables above, the Analysis Services Processing task allows us the flexibility to leverage the time savings that accompany incremental updates. While, as I mentioned in the last paragraph, we are forced to do full processing in a scenario where we select a cubes folder to process multiple cubes, we can perform an incremental update on a cube group if we create an individual task for each cube (which we can then "chain" together within a DTS package). The same is true when we select a dimensions folder: we are forced to do a complete rebuild of these dimensions, or to create a separate task for each one. (In general, we can meet any processing sequencing need not specifically "built in" to the Analysis Services Processing task through chaining tasks in a larger DTS package.)

    NOTE: For more information on processing cubes and dimensions in general, see my other articles in my Database Journal series, Introduction to MSSQL Server Analysis Services, indexed at

    http://www.databasejournal.com/article.php/1459531

    We will gain some hands-on familiarity with the Analysis Services Processing task in the Practice section below. In walking through the creation of an Analysis Services task, and the subsequent execution of the DTS package we construct to house it, we will comment on various settings and approaches as we encounter them.

    Considerations and Comments

    For purposes of this exercise, we will be working with the Warehouse cube, within the FoodMart 2000 MSAS database. The Warehouse cube, as most of us are aware, is a member of a group of working samples that accompanies a typical installation of Analysis Services. If the samples are not installed in, or have been removed from, your environment, they can be obtained from the installation CD, from the Analysis Services section of the Microsoft website, and perhaps elsewhere.

    Hands-On Procedure

    Setting up the Analysis Services Processing Task within a DTS Package

    Let's get started by setting up the Analysis Services Processing task to process the Warehouse cube. Once we have constructed the task, and then created a DTS package to contain it, we will be ready to execute the task and to verify its operation.

    1.  Click Start.

    2.  Navigate to the Microsoft MSSQL Server Enterprise Manager shortcut in the Programs group, as appropriate.

    The equivalent on my PC appears as shown in Illustration 1.

    Click for larger image

    Illustration 1: Navigating to the Enterprise Manager

    3.  Select Enterprise Manager from the menu.

    The Enterprise Manager console opens.

    4.  Expand Microsoft SQL Servers by clicking the "+" sign to its immediate left, as required.

    5.  Expand SQL Server Group.

    6.  Expand the server upon which to create the DTS package that will house the Analysis Services Processing task. (I am choosing the Local server in my example.)

    The Enterprise Manager console appears similar to that depicted in Illustration 2.


    Illustration 2: Enterprise Manager (Compressed) View

    7.  Right-click the Data Transformation Services folder.

    8.  Select New Package from the context menu that appears, as shown in Illustration 3.


    Illustration 3: Select New Package ...

    DTS Designer opens, presenting a blank design sheet.

    9.  From the Task toolbar, click / drag the Analysis Services Processing task onto the design sheet. The icon appears circled in Illustration 4.


    Illustration 4: Drag the Analysis Services Processing Task Icon to the Design Sheet

    When we drop the icon onto the design sheet, the Analysis Services Processing task dialog appears, as partially depicted in Illustration 5.


    Illustration 5: The Analysis Services Processing Task Dialog Appears

    10.  Leaving the Name box at default, replace the wording in the Description box of the Analysis Services Processing task with the following:

    ANSYS035_Analysis Services Processing Task

    11.  Click the Local server checkbox in the lower left corner of the dialog to "filter" the Select the object to process pane to the local server only.

    NOTE: If you chose a server other than the Local server for this exercise, simply substitute the appropriate server in the steps that follow.

    Any other servers we saw, upon initial entry to the dialog, disappear, leaving the Select the object to process pane containing only the local server.

    12.  Click the "+" sign to the left of the Local server to expand the server tree.

    The Analysis Services Processing task dialog appears, at this stage in our procedure, as shown in Illustration 6.


    Illustration 6: The Analysis Services Processing Task Dialog, with Settings So Far ...

    13.  Click Foodmart 2000 Analysis Services database to highlight it.

    We note that the radio buttons appearing within the Select a processing option section (to the right of the Select the object to process pane) change to reflect the Full process option only (the others are disabled). In like manner, the options change depending upon our selections, in a context-sensitive way.

    14.  Expand the Foodmart 2000 Analysis Services database.

    The underlying Cubes, Dimensions, and Mining Models folders appear.

    15.  Click the Dimensions folder to highlight it.

    The processing options again change, to reflect those that are available for the cube dimensions as a group. Our options now include Rebuild the dimension structure and Incremental update, as we noted in the tables above, and as is depicted in Illustration 7.

    Click for larger image

    Illustration 7: Options for Processing the Dimensions Folder ...

    16.  Expand the Cubes folder.

    The underlying Cubes for the FoodMart 2000 database appear.

    17.  Click the Warehouse cube to highlight it.

    In addition to the corresponding change in the possible options (Full process, Refresh data, and Incrementally update dimensions) within the Select a processing option section, we note the appearance of a new object: the Cube Settings button appears as shown in Illustration 8.


    Illustration 8: The Cube Settings Button Appears

    18.  Click the Cube Settings button.

    The Cube Processing Settings dialog opens, revealing several options whereby we can establish recurring settings for cube processing. Our settings will be enforced anytime we process the cube or its partition(s). In effect, this is the same set of settings that we encounter from the Settings button in the standard Process a Cube dialog box, when we are manually processing a cube, as we have done in many articles of the Introduction to MSSQL Server Analysis Services series. Note that any settings we make here are stored, and override any previous processing settings made elsewhere.

    The Cube Processing Settings dialog includes the setting options detailed in Table 3.

    Setting

    Use This Setting to:

    Processing Optimization Mode:

    - After all aggregations are calculated

    (Default) Make data available for browsing only after all aggregations have been accomplished.

    - Immediately after data is loaded

    Make data available for browsing after it has been loaded, but before all aggregations have been accomplished. (New data is available sooner for querying; a potential cost is lessened responsiveness to query execution from the Analysis server.

    Missing Dimension Key Settings:

    - Stop processing after encountering missing dimension key errors

    Stop processing if dimension key errors are encountered.

    Processing will stop after [Number] errors

    (Default= zero)

    Here we can specify the number of errors after which processing is to stop.

    - Ignore all missing dimension key errors

    Continue processing, regardless of dimension key errors.

    Log dimension key errors to a file

    Affords a means of specifying a path to a text file.

    -     File path and name

    Specify where file will reside, along with its name

    -     Browse

    Click to navigate to a local or network location, for easy selection of a home for the file.

    NOTE: Best practices dictate that the path observe the Universal Naming Convention (UNC), as is the case in similar scenarios.


    Table 3: Selection Options within the Cube Processing Settings Dialog

    The Cube Processing Settings dialog appears (with default settings) as depicted in Illustration 9.


    Illustration 9: The Cube Processing Settings Dialog, with Defaults

    19.  Click OK to close the Cube Processing Settings dialog, without making any changes.

    We return to the Analysis Services Processing task dialog. We will leave the settings at default, which means Full process, among other default settings.

    NOTE: Had we chosen Incremental update for a given partition of the Warehouse cube (it is a two-partition cube, as we have discussed in other articles of the series), we would have been able to change the data source and fact table under consideration. We would also have been offered an opportunity to establish a filter limiting fact table records to be included in the incremental update.

    For an introduction to partitions within Analysis Services cubes, see my Database Journal article Partitioning a Cube in Analysis Services - An Introduction. For more information regarding incremental updates, see my article Performing Incremental Cube Updates - An Introduction.

    20.  Click OK on the Analysis Services Processing task dialog, to finish creating the new DTS task.

    The Analysis Services Processing task dialog closes.

    21.  Select Package --> Save on the Package menu, as shown in Illustration 10.


    Illustration 10: Saving the New Package, Containing Our Analysis Services Processing Task

    The Save DTS package dialog appears.

    22.  Type the following into the Package name box of the Save DTS package dialog:

     ANSYS035_FullyProcessWarehouseCube

    23.  Leaving the other settings in the dialog at default, input the correct authentication information for your local environment.

    The Save DTS package dialog appears, using my own local settings as an example, similar to those depicted in Illustration 11.


    Illustration 11: Save DTS Package Dialog, with My Local Settings

    24.  Click OK to save the package and to close the Save DTS package dialog.

    We are returned to the design sheet.

    25.  Select Close, under the "hammer and table" icon, as shown in Illustration 12.


    Illustration 12: Select Close to leave the DTS Design Environment

    We are returned to Enterprise Manager. From this point, we will execute the DTS package that contains our new Analysis Services Processing task.



    Verification: Execute the DTS Package Containing the New Analysis Services Processing Task



    Let's test drive our new DTS package from Enterprise Manager, and ensure that the task we have assembled performs as expected.


    1.  Expand the Data Transformation Services folder within the appropriate server, within Enterprise Manager.


    2.  Click Local Packages, to display the existing local packages (including our recently created one) in the right pane, as depicted in Illustration 13.


    Click for larger image

    Illustration 13: The Local Packages Appear


    3.  Right-click the package marked ANSYS035_FullyProcessWarehouseCube.

    4.  Select Execute Package from the context menu that appears, as shown in Illustration 14.


    Illustration 14: Select Execute Package from the Context Menu

    The DTS package begins executing, with the Executing Package viewer displaying the execution progress "meter," and listing the ANSYS035_Analysis Services Processing Task that we have placed inside the DTS package as the sole step of the package, as depicted in Illustration 15.


    Illustration 15: Execution Progress is Displayed ...

    Processing of the task and package completes. The Package Execution Results message box appears, confirming successful package execution, as shown in Illustration 16.


    Illustration 16: Successful Completion is Indicated ...

    NOTE: Upon notification of a failure here, or within any step of a DTS package, we can display error information about the task involved, by simply double-clicking any task(s) listed in the viewer as having not executed correctly.

    5.  Click OK to close the message box.

    6.  Click Done to close the Executing Package viewer.

    We can, as we noted earlier, string multiple tasks together, as well as provide for notification of completions / failures via e-mail and other ways, from an appropriately assembled DTS package. Moreover, DTS packages can be called from other processes, scheduled and so forth, to allow for automation of much of the essential cube refreshment processes.

    7.  Exit Enterprise Manager, when ready.

    Conclusion

    In this article, we examined the processing of an Analysis Services cube via the Analysis Services Processing task in Data Transformation Services, one of two that accompany the installation of Analysis Services. After introducing this custom DTS task, we discussed multiple processing operations that we can use it to perform, at multiple levels of an Analysis Services database structure.

    We then performed a practice exercise, discussing various processing options as we prepared to specify full processing of a sample cube within the Analysis Services Processing task. Along the way, we discussed other considerations in taking advantage of the options that the task makes available to us. We saved the task within a DTS package, which we then executed to verify operation of the task. Finally, we mentioned some of the options for using the Services Processing task, in conjunction with other tasks, and particularly in combination with scheduling.

    » See All Articles by Columnist William E. Pearson, III

    Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

    Share:
    Home
    Mobile Site | Full Site
    Copyright 2017 © QuinStreet Inc. All Rights Reserved