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 Feb 13, 2006

Process Analysis Services Objects with Integration Services - Page 5

By William Pearson

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.

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