Process Analysis Services Objects with Integration Services - Page 5

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.

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

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

 ANSYS044_Adventure Works Full_Processing

SSAS_Processing Task Example

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

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.

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

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.

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.

We return to the Analysis Services Processing Task Editor – Analysis 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.

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

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.

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

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.

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.

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

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.