Process Analysis Services Objects with Integration Services

Monday Feb 13th 2006 by William Pearson
Share:

Process Analysis Services 2005 objects with an Integration Services package. Architect Bill Pearson leads hands-on exploration of one means of automating object processing in Analysis Services 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 presenting features and techniques designed to meet specific real - world needs. For more information on the series, please see my initial article, Creating Our First Cube.

Note: To follow along with the steps we undertake, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:

  • Microsoft SQL Server 2005 Database Engine
  • Microsoft SQL Server 2005 Analysis Services
  • Microsoft SQL Server 2005 Integration Services
  • Business Intelligence Development Studio
  • Microsoft SQL Server 2005 sample databases
  • The Analysis Services Tutorial sample project and other samples that are available with the installation of the above.

To successfully replicate the steps of the article, you also need to have:

  • Membership within one of the following:
    • the Administrators local group on the Analysis Services computer
    • the Server role in the instance of Analysis Services.
  • Read permissions within any SQL Server 2005 sample databases we access within our practice session, if appropriate.

Note: Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

Introduction

In this lesson we revisit an approach to automated / scheduled processing of an Analysis Services database (and related objects), a subject that we undertook in my article Process Analysis Services Cubes with DTS (Database Journal Introduction to MSSQL Server Analysis Services series), published in May of 2005. In the earlier article, we learned that, among several tools that Microsoft SQL Server 2000 Analysis Services offered to assist us in maintaining our cubes in a state of readiness, one of two custom Data Transformation Services ("DTS") tasks that accompanied the installation of Analysis Services 2000, the Analysis Services Processing task, provided us a prefabricated mechanism for performing Analysis Services processing functions. The DTS Analysis Services Processing task, we noted, was specifically designed for the purposes of handling processing of Analysis Services databases, dimensions, cubes, or other objects that we could manually process from Analysis Manager, but which we could not easily manage using "home made" (or other pre-packaged) DTS tasks.

In this article, we will examine the processing of an Analysis Services 2005 cube via another of the integrated MSSQL Server 2005 components, Integration Services ("SSIS"). Virtually anyone who works with MSSQL Server in an RDBMS context, and especially within a data warehouse or mart design perspective and related functions, will probably interact at least occasionally with SSIS – if only as an Import / Export utility. Just like its predecessor, DTS, SSIS is best known by early adopters primarily as a set of ETL (Extraction, Transformation and Load) utilities that accompany the integrated Microsoft BI Solution of which MSSQL Server 2005, Analysis Services, and Reporting Services are component parts. While SSIS 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), it also maintains a particularly high-profile role within the creation and maintenance of data warehouses, marts, or other such sources for business intelligence and organizational reporting, among many new additional features.

The flexible utility of SSIS 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 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;
  • Prepare for our practice exercise by creating an Integration Services project within Business Intelligence Development Studio;
  • Create an Integration Services package to house our Analysis Services Processing task, using SSIS Designer;
  • Add, and configure, an Analysis Services Processing task within the package;
  • Execute the package to ascertain its effectiveness.

Process an Analysis Services Database with Integration Services

Overview and Discussion

As we stated in Process Analysis Services Cubes with DTS, the recurring need to process our cubes is one of the most critical administrative tasks surrounding an Analysis Services implementation. 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 either the Business Intelligence Development Studio or the SQL Server Management Studio (at least initially, in the design and development of our cubes and other Analysis Services objects). We can use this prefabricated task to automate our processing cycles, and thus keep our cubes updated to reflect the latest data in their underlying sources, all with minimal manual intervention.

As most of us know, processing is the step or steps that turn data into information for business analysis. This is true for all objects that we process, although the processing steps differ among various object types. Analysis Services 2005 requires that we deploy to the server the objects or projects that we design and build locally before we can perform processing. When deployment takes place, the re-creation and execution of our project occur thanks to a script that is sent to the targeted Analysis Services instance. While deployment processes all database objects by default, we have the option to change several facets of post-deployment processing, including the objects that are processed. The Analysis Services components to which we can apply our Integration Services-driven capabilities include those detailed, hierarchically, in Table 1.

Component

Use This Option to:

Database

Process (full or default) all cubes, measure groups, partitions, dimensions, mining models and mining structures within the selected Analysis Services database.

... Cube

Perform various types of processing (see Table 2 below for details) with the selected cube(s).

.... Measure Group

Perform various types of processing (see Table 2 below for details) with the selected measure group(s).

.... Partition

Perform various types of processing (see Table 2 below for details) with the selected partition(s).

... Dimension

Perform various types of processing (see Table 2 below for details) with the selected dimension(s).

... Mining Model

Perform various types of processing (see Table 2 below for details) with the selected mining model(s).

.... Mining Structure

Perform processing (default, full or unprocess) with the selected Mining Structure(s).


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

Cascaded processing occurs when we select and process higher-level objects that contain subordinate objects. As an illustration, if we select a given cube for processing, execution will cause processing of the dimensions, measure groups and partitions that are contained within the cube.

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

Component

Use This Option to:

Database

  • Process Default
  • Process Full
  • Unprocess
  • ... Cube

  • Process Default
  • Process Full
  • Process Data
  • Process Structure
  • Unprocess
  • Process Index
  • Process Incremental
  • Process Script Cache
  • .... Measure Group

  • Process Default
  • Process Full
  • Process Data
  • Unprocess
  • Process Incremental
  • Process Index
  • .... Partition

  • Process Default
  • Process Full
  • Process Data
  • Unprocess
  • Process Incremental
  • Process Index
  • ... Dimension

  • Process Default
  • Process Full
  • Unprocess
  • Process Data
  • Process Index
  • Process Update
  • ... Mining Model

  • Process Default
  • Process Full
  • Unprocess
  • .... Mining Structure

  • Process Default
  • Process Full
  • Process Structure
  • Process Clear Structure
  • Unprocess

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

    NOTE: Properties for the Analysis Services Processing task can be set using the SSIS designer (this approach with which this article concerns itself) or programmatically.

    When we process multiple objects at the same time, we can define settings that apply to the processing of all the objects in the batch, as we shall see. When working with batches of this sort, we can process the constituent objects sequentially (in series) or in parallel (assuming that order of processing of the objects is not a consideration). Processing batch members in parallel offers speedier processing in general, and the Analysis Services Processing task affords us the flexibility of directing the number of objects that can process simultaneously.

    The Analysis Services Processing task offers us more options than its Analysis Services 2000 predecessor, even when we process sequentially. We can arrange grouping of all objects within a batch as a single transaction, or establish each as a separate transaction. Moreover, anytime we undertake to process Analysis Services objects, we can take advantage of the option to process objects that depend upon our selection, in addition to the selected objects themselves.

    Additional options that the Analysis Services Processing task provides include allowing us to manage error handling within dimension keys (an "ignore" or "stop processing" choice). We can substitute custom error configurations that we construct, whereby we can direct task error conditions and handling, as well as specifying an error log path, for default error configurations. We often employ the Analysis Services Processing task in conjunction with one or more other Integration Services tasks whereby we perform some or all of extraction, transformation and loading processes to populate the data warehouse / mart upon which a cube is based. An example might be a case where we use a Data Flow task (or group of tasks) to populate the tables of our star schema using data from an online transactional (OLTP) database, and then execute the Analysis Services Processing task to process a cube we have constructed that uses the star schema as a data source.

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

    Considerations and Comments

    For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005 Analysis Services. The samples with which we are concerned include, predominantly, the Adventure Works DW Analysis Services database (with member objects). The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.

    The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources, as well), provide guidance on samples installation. Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references, as well.

    Hands-On Procedure

    Let's get some hands-on practice with a basic Analysis Services Processing task. To prepare, we will create a new Integration Services project, and, simultaneously, a new Integration Services package, to which we will add our Analysis Services Processing task, and within which we will both configure and execute the task. We will accomplish the steps of our practice session within the SQL Server Business Intelligence Development Studio.

    Preparation

    Create an Integration Services Package within the SQL Server Business Intelligence Development Studio

    To begin, we will launch the SQL Server Business Intelligence Development Studio.

    1.  Click Start.

    2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

    The equivalent on my server appears as depicted in Illustration 1.

    Click for larger image

    Illustration 1: Launching SQL Server Business Intelligence Development Studio

    We briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

    3.  Close the Start page, if desired.

    4.  Select File --> New from the main menu.

    5.  Click Project ... from the cascading menu, as shown in Illustration 2.

    Click for larger image

    Illustration 2: Creating a Project ...

    The New Project dialog appears.

    6.  Select Business Intelligence Projects (the default) in the Project types pane of the dialog, if necessary.

    7.  Select Integration Services Project, a Visual Studio installed template, within the Templates pane of the dialog.

    8.  In the Name box of the dialog, type the following name:

    ANSYS044_SSAS_Processing

    9.  Navigate / provide a path for the new project within the Location box.

    10.  Leave the Solution box at its default (ANSYS044_SSAS_Processing).

    11.  Leave the remaining settings of the dialog at default.

    The New Project dialog appears, with our settings, similar to that depicted in Illustration 3.


    Illustration 3: The New Project Dialog, with Our Settings

    12.  Click OK to create the project.

    Our new Integration Services project appears in Solution Explorer, as shown in Illustration 4.


    Illustration 4: The Project Appears within BI Development Studio – Solution Explorer

    We are now prepared to add the Analysis Services Processing task to the package that has been automatically created within our new project. We will do so in the section that follows.

    Procedure

    Add the Analysis Services Processing Task to the Integration Services Package

    We need an Integration Services package within our new project before we can add the Analysis Services Processing task (which forms the focus of our lesson). While there are several ways to create a package within the Business Intelligence Development Studio, we will not have to create a package separately, in our present case – SSIS automatically added an empty "starter" package within the project when we created it. It is good to keep in mind, however, that, should we have already had a solution in place, containing an existing package (such as an ETL package) or packages, and we decided to combine the Analysis Services Processing task with the existing package, for reasons similar to those we noted in our introductory comments, we would have to create and add an Integration Services package to the solution before adding our Analysis Services Processing task.

    While we are adding a single task to the package for our present purposes, packages typically contain several tasks, with each task set to run within the context of the package control flow. Workflows that run in response to run-time events, called event handlers, can also have tasks. To add functionality to a package, we typically build the control flow and data flows, add connection managers, and implement other Integration Services features that a given package requires. In our present case, the steps involved will be minimal. We will begin by giving the automatically created package a more meaningful name.

    1.  Within the Solution Explorer, expand the SSIS Packages folder, if required.

    2.  Right-click the Package.dtsx file that appears underneath the folder in the tree.

    3.  Select Rename from the context menu that appears, as depicted in Illustration 5.


    Illustration 5: Select Rename from the Context Menu ...

    4.  Type the following in place of the now-highlighted package name:

    SSAS_Processing.dtsx

    NOTE: Be sure to include the .dtsx file extension.

    The package appears, with the proposed name, in Solution Explorer, as shown in Illustration 6.


    Illustration 6: Renaming the Package in Solution Explorer ...

    5.  Click outside the caption box, to "set" the new name.

    A message box appears, asking if we wish to rename the package object as well, as depicted in Illustration 7.


    Illustration 7: Message Box Asks If We Wish to Rename Package Object, As Well ...

    6.  Click OK to rename the package object, too, and to dismiss the message box.

    The SSIS Designer opens, defaulted to the Control Flow tab.

    7.  Click the Control Flow tab, if it does not already appear, to move to it.

    The Control Flow tab bears a message telling us to add objects, and related matters, from the Toolbox, as shown in Illustration 8.


    Illustration 8: Control Flow Tab, and Message Regarding Object Addition and Related Matters

    We will add the Analysis Services Processing task to the package at this point.

    8.  Select View --> Toolbox from the main menu, as depicted in Illustration 9.


    Illustration 9: Opening the Toolbox ...

    The Toolbox appears. In the upper half, we see Control Flow Items. It is here that we will obtain the Analysis Services Processing task object, one of two Analysis Services objects that appear within the Control Flow Items group atop the Toolbox.

    9.  Click and drag the Analysis Services Processing Task item from the Toolbox to the design surface of the Control Flow tab, as shown in Illustration 10.


    Illustration 10: Drag the Analysis Services Processing Task to the Control Flow Design Surface

    The Analysis Services Processing Task item appears on the design surface as depicted in Illustration 11. We note that a red circle, containing a white "X," appears on the right side of the box. Passing the mouse pointer over the item produces a rollover caption that reads "No connection manager is specified," as depicted in Illustration 11.


    Illustration 11: The Task Appears – and Reminds Us to Add a Connection Manager ...

    We will add an Analysis Services Connection Manager to the package in the section that follows.

    Add an Analysis Services Connection Manager to the Integration Services Package

    An Analysis Services Connection Manager enables our package to connect to the server that runs the Analysis Services database upon which (or upon whose member objects) we wish to use our Analysis Services Processing task. (We can also use the Connection Manager to connect to an Analysis Services project that provides "BI-development-Studio-only" access to cube and dimension data, to establish connections between the package and the server / database to which we later deploy the package).

    At run time, the Connection Manager is resolved as an Analysis Services connection. As part of the creation process, Integration Services sets the properties (one of which, ConnectionManagerType, is set to MSOLAP90), adding it to the package's Connections collection. We will add and configure the Analysis Services Connection Manager by taking the following steps.

    1.  Right-click a point within the Connection Managers pane, located below the Control Flow tab in the SSIS Designer.

    2. Select New Analysis Services Connection ... from the context menu that appears, as shown in Illustration 12.

    Click for larger image

    Illustration 12: Initiating the Creation of a New Analysis Services Connection in the Package

    3.  Right-click a point within the Connection Managers pane, located below the Control Flow tab in the SSIS Designer.

    4.  Click the Edit button on the Add Analysis Services Connection Manager dialog that immediately appears, as depicted in Illustration 13.

    Click for larger image

    Illustration 13: Click Edit on the Analysis Services Connection Manager Dialog

    The Connection Manager dialog appears next.

    5.  In the Server or file name box, enter the server (or server / instance) appropriate to your environment.

    6.  Select the appropriate radio button in the Log on to the server section of the Connection Manager dialog.

    7.  In the Initial catalog selector, select Adventure Works DW.

    8.  Click the Test Connection button to ascertain connectivity.

    A Connection Manager message box appears, indicating that the test connection has succeeded, as shown in Illustration 14.


    Illustration 14: Testing Positive for Connectivity to the Analysis Services Database

    9.  Click OK to dismiss the message box, and to return to the Connection Manager dialog.

    The Connection Manager dialog appears, with our input, similar to that depicted in Illustration 15.


    Illustration 15: The Connection Manager Dialog ...

    10.  Click OK to accept settings and close the dialog.

    We return to the Add Analysis Services Connection Manager dialog, which appears similar to that shown in Illustration 16.


    Illustration 16: The Add Analysis Services Connection Manager Dialog ...

    11.  Click OK to accept and dismiss the Add Analysis Services Connection Manager dialog.

    We arrive once more at the Integration Services Designer, where we see the new Connection Manager within the Connection Managers pane as depicted in Illustration 17.


    Illustration 17: The New Connection Manager Appears ...

    12.  Select File --> Save All from the main menu, as depicted in Illustration 18, to save our work to this point.


    Illustration 18: Saving Our Work to this Point ...

    We have now established and tested the Connection Manager we have added to the Analysis Services package. We will begin configuration of the Analysis Services Processing task we have added to the package in the next section.

    Configure the Analysis Services Processing Task within SSIS Designer

    We mentioned earlier that we can set properties for an Analysis Services Processing task through the SSIS Designer or programmatically. In this article, we will be working within the SSIS Designer to configure the Analysis Services Processing task with which we are working. We will set up the Analysis Services Processing task to process the Adventure Works cube. Once we have configured the task, and then created an Integration Services package to contain it, we will be ready to execute the task via the package, and to verify its operation.

    1.  On the Control Flow tab within the SSIS Designer, right-click the Analysis Services Processing task that we added earlier.

    2.  Select Edit... from the context menu that appears, as shown in Illustration 19.


    Illustration 19: Select Edit from the Context Menu ...

    The Analysis Services Processing Task Editor appears, defaulted to the General page.

    3.  Expand the General item in the right pane of the Editor by clicking the "+" sign to its left, if necessary.

    The Name and Description rows, General page of the Analysis Services Processing Task Editor appear.

    4.  Double-click the box containing Name to highlight the box to its right, where the default name of the Analysis Services Processing Task currently resides.

    5.  Replace the default name with the following:

     ANSYS044_Adventure Works Full_Processing

    6.  In like manner, replace the wording in the Description box with the following:

    SSAS_Processing Task Example

    The General page of the Analysis Services Processing Task Editor appears, with our modifications, as depicted in Illustration 20.


    Illustration 20: The General Page of the Analysis Services Processing Task Editor ...

    7.  In the left hand pane of the Analysis Services Processing Task Editor, click Analysis Services to move to the Analysis Services page.

    The Analysis Services page opens, and we see the Connection Manager we added earlier displayed in the box titled Analysis Services connection manager, atop the page. It is underneath this box, within the Object list (in the upper half of the Processing configuration section of the page), where we specify the analytic objects to process (as well as Process Options and Settings, as we shall see) within the Analysis Services Processing task.

    8.  Click the Add button under the Object list.

    The Add Analysis Services Object dialog appears, at this stage in our procedure, as shown in Illustration 21.


    Illustration 21: The Add Analysis Services Object Dialog

    9.  Expand the Cubes folder, by clicking the "+" sign to its immediate left.

    10.  Expand the Adventure Works cube.

    11.  Expand the Internet Sales measure group within the cube.

    We have "drilled down" on the various levels within a cube object to examine them in general. The results appear in Illustration 22, which represents the relevant components of the cube structure upon which we can perform processing within an Analysis Services Processing task.


    Illustration 22: The Processing Objects within a Cube

    For purposes of our practice exercise, we will process the entire cube through this Analysis Services Processing task, but it is important to reflect upon the wide range of choices that are available with regard to available processing objects. We can see most of those choices in the illustration above, including Analysis Services databases, cube, measures groups and partition objects. We can also configure the task to process dimensions and mining models and mining structures, as well.

    12.  Select the Adventure Works cube object, by clicking the checkbox to its immediate left (and thereby placing a check within the box), as depicted in Illustration 23.


    Illustration 23: Selecting the Adventure Works Cube for Processing ...

    13.  Click OK to accept our choice, and to close the Add Analysis Services Object dialog.

    We return to the Analysis Services Processing Task EditorAnalysis Services page, where we see our selection appear in the Objects list. Having selected the object we wish to process, we have the option, within the Objects list, of specifying Processing Options, which vary somewhat between the types of objects we select for processing (as we saw in Table 2 above). While we will leave the selection at Process Full for the purposes of our example, we have numerous options from which to select for processing a cube object, as is evidenced by clicking the selector button for the Processing Options field within the Objects list, as shown in Illustration 24.


    Illustration 24: Possible Processing Options Selections for a Cube Object

    The Analysis Services Processing Task EditorAnalysis Services page appears, with our input, as depicted in Illustration 25.


    Illustration 25: The Analysis Services Processing Task Editor – with Our Input

    It is also significant to note that the flexibility for customization of the Analysis Services Processing task is further enhanced by the last page in the Analysis Services Processing Task Editor, the Expressions page.

    14. In the left hand pane of the Analysis Services Processing Task Editor, click Expressions to move to the Expressions page.

    The Expressions page opens, and appears as shown in Illustration 26.


    Illustration 26: The Expressions Page of the Analysis Services Processing Task Editor

    The Expressions page allows us edit property expressions and to access the Property Expressions Editor and Property Expression Builder, both useful tools in creation and maintenance of property expressions within Integration Services tasks. A property expression is an expression that is assigned to a property to enable dynamic update of the property at run time. Expressions themselves can range from simple (consisting of a single constant, variable, or function) to complex (where they might use multiple operators and functions, and / or reference multiple columns and variables, and so forth). Expressions are a combination of symbols (identifiers, literals, functions, and operators) that yields a single data value. Many Integration Services elements, in addition to properties (where a common use for expressions is to set a property value) can use expressions.

    Property expressions can be updated in many different ways. While they are beyond the scope of this article, Property expressions afford us a means of reaching far beyond the "standard" options offered by the Analysis Services Processing task to enable us to achieve the specific business needs of our employers and clients. A good understanding of the use of Property expressions can be valuable when we come across requirements for which a simple Analysis Services Processing task cannot be devised.

    15. Click Analysis Services in the left hand pane of the Analysis Services Processing Task Editor, to return to the Analysis Services page once more.

    We can click the Impact Analysis button underneath the Object list, to perform impact analysis on any object we add to the list. While we have no need of this feature within our practice session, the Impact Analysis button allows us to display the Impact Analysis dialog, with which we can optionally process dependent objects that are affected if the objects listed are processed.

    We can also make any necessary modifications to the Batch Settings, if necessary. We will leave the settings, which are grouped into two types, Processing Options and Dimension Key Errors, at default, but this provides a convenient means of making batch settings modifications (such as Processing Order of parallel or sequential, or whether the default or a custom error configuration is used when dimension key errors are detected, both of which we touched upon earlier) across the entire processing batch.

    16.  Click OK at the bottom of the Analysis Services page, to finish editing the new Analysis Services Processing task.

    The Analysis Services Processing task dialog closes, and we are returned to the SSIS Designer, Control Flow tab.

    17.  Select File --> Save All from the main menu, once again.

    Having completed the configuration of the Analysis Services Processing task within the new Integration Services package we have created, we are ready to test the package. We will execute the package to verify its operation in the section that follows.

    Verification

    Execute the Integration Services Package Containing the New Analysis Services Processing Task

    Let's test drive our new Integration Services package from within the development environment, and ensure that the task we have assembled performs as expected.

    1.  On the Control Flow tab, right click the new ANSYS044_Adventure Works Full_Processing task.

    2.  Select Execute Task from the context menu that appears, as depicted in Illustration 27.


    Illustration 27: Select Execute Package from the Context Menu

    The ANSYS044_Adventure Works Full_Processing task box on the Control Flow tab turns yellow as execution (of the task as well as its parent package, SSIS_Processing.dtsx) begins.

    3.  Click the Progress tab (shown in Illustration 28), which appears once execution starts, to monitor the events encountered within the operation of the ANSYS044_Adventure Works Full_Processing task.

    Click for larger image

    Illustration 28: Access the Progress Tab to Monitor Processing Events

    Processing events are logged (we can click on any of these to access amplifying information, if desired) and appear on the Progress tab as partially depicted in Illustration 29.


    Illustration 29: Partial View of Events Logged on the Progress Tab

    Once processing successfully completes, the ANSYS044_Adventure Works Full_Processing task box turns green on the Control Flow tab.

    4.  Click the link below the Connection Managers pane, which tells us "Package execution completed. Click here to switch to design mode, or select Stop Debugging from the Debug menu," as shown in Illustration 30.


    Illustration 30: Stop Debugging via the Link Shown Circled Above

    We can, as we noted earlier, string multiple tasks together within our package, as well as provide for notification of completions / failures via e-mail and other ways, from an appropriately assembled Integration Services package. Moreover, Integration Services packages can be called from other processes, scheduled and so forth, to allow for automation of much of the essential cube refreshment process. (For more information on the steps required to deploy the project containing our package, see the MSSQL Server 2005 Books Online).

    5.  Select File -->Exit to leave the Business Intelligence Development Studio, saving if prompted, when desired.

    Conclusion

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

    After preparing a project within which to create an Integration Services package, we began a practice exercise, adding the Analysis Services Processing task, and then a Connection Manager, to the package. We discussed various processing options as we configured our Analysis Services task to specify full processing of a sample cube. Along the way, we discussed other considerations in taking advantage of the options that the task makes available to us. Finally, we executed the Integration Services package containing our Analysis Services Processing task to verify its operation.

    » 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